有则更新，无则插入
需求：批量设置数据时，数据库中有数据则进行更新操作、没有则进行插入操作。
实现：MERGE INTO 语法
使用示例：
MERGE INTO TABLENAME1 T1 USING(SELECT * FROM DUAL) T2 ON (T1.F_DWBH = ? AND T1.F_DATE = ?)
WHEN MATCHED THEN UPDATE SET T1.F_COLUMN1 = ?,T1.F_COLUMN2=?
WHEN NOT MATCHED THEN INSERT (F_COLUMN1, F_COLUMN2, F_COLUMN3, F_COLUMN4, F_COLUMN5)
VALUES (?,?,?,?,?));行转列后、动态组织显示列
需求：查询展示中动态组织显示某些列。
实现：通过一个或者两个临时表进行数据组织展示。
本例实现思路：
/**
* 根据统计分类"银行"统计每个单位在每个银行账户的数量。
* 查询视图结果：单位编号、单位名称、合计、各银行列...
* 1、统计每个单位账户得银行列动态创建临时表。
* <p>
* 2、根据单位编号和银行编号更新合计和银行下的账户数量
* 2-1、查询每个单位在每个银行的账户数量。
* 2-2、查询每个单位银行账户数量的合计。
* <p>
* 3、更新临时表每个单位账户的合计，每个单位各个银行下的账户数量。
* 3-1、根据 2-1 统计的每个银行的账户数量更新临时表。
* 3-2、根据 2-2 统计的每个银行的账户数量更新临时表。
*
* @return
*/效果展示：
查询结果银行列动态显示
查询结果币种列动态显示
package com.pansoft.esp.skxt.queryview.wbzh;
import com.pansoft.pub.util.StringFunction;
import com.pansoft.esp.skxt.tools.TempTableManager;
import com.pansoft.report.table.jxml.*;
import jfoundation.bridge.classes.JResponseObject;
import jfoundation.dataobject.classes.JParamObject;
import jfoundation.sql.classes.JConnection;
import jframework.MtoDB.BofJfc.DBO.CommonDBObject;
import jservice.jbof.classes.GenerQueryObject.JQueryStubObject;
import jservice.jdal.classes.query.dataset.FormatDataManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
/**
* 外币账户数量统计查询
* @author pengguozhen
*/
public class ZJAccountTJViewQuery extends CommonDBObject {
private String msTableName = "";
private JConnection conn = null;
private JParamObject PO = null;
private String[] mViewID = null;
/*统计分类：YH:银行;BZ:币种;GJ:国家;QY:区域;*/
private static String TJFL_YH = "YH";
private static String TJFL_BZ = "BZ";
private static String TJFL_GJ = "GJ";
private static String TJFL_QY = "QY";
String dwbh = "";
String vsTjfl = "", caption = "";
LinkedHashMap<String, String> dynamicsCol = new LinkedHashMap<String, String>();//动态列
Statement stmt = null;
ResultSet rs = null;
public JResponseObject QueryObject(JParamObject PO, JQueryStubObject QO, JConnection conn) throws Exception {
JResponseObject RO = null;
this.conn = conn;
this.PO = PO;
/*根据统计分类创建不同的临时表（动态创建列）*/
dwbh = PO.GetValueByParamName("DWBH", "");
vsTjfl = PO.GetValueByParamName("TJFL", "");
if (vsTjfl.equals(TJFL_BZ)) {
getBZColInfo();
caption = "币种名称";
} else if (vsTjfl.equals(TJFL_GJ)) {
getGJColInfo();
caption = "国家名称";
} else if (vsTjfl.equals(TJFL_QY)) {
getQYColInfo();
caption = "区域名称";
} else {//统计分类：银行
getYHColInfo();
caption = "银行名称";
}
/**
* 返回查询结果：
* 1、组织查询格式。
* 2、获取查询数据
*/
TableManager tm = queryFormatObject();//1、组织查询格式。
stmt = conn.createStatement();
String data = getData(stmt, conn);//2、获取查询数据
String[] formatData = new String[2];
formatData[0] = tm.printDOMTree(); //格式串
formatData[1] = data; // 数据串
RO = new JResponseObject(formatData, 0, null);
TempTableManager.dropTable(conn, PO, msTableName);
return RO;
}
/**
* 根据统计分类"区域"进行分类时，获取区域名称的动态展示列信息。
*/
private void getQYColInfo() {
String vsSql = "SELECT MAX(ZJZHAREA.F_BH) AS F_BH,MAX(ZJZHAREA.F_MC) AS F_MC FROM ZJWBZHXX,ZJWHYHZD,ZJZHAREA " +
"WHERE ZJWBZHXX.F_YHBH=ZJWHYHZD.F_YHBH "
+ " AND ZJWHYHZD.F_DQBH=ZJZHAREA.F_BH"
+ " AND F_SQDWBH LIKE '" + dwbh + "%' GROUP BY ZJWHYHZD.F_GJBH";
stmt = conn.createStatement();
try {
rs = stmt.executeQuery(vsSql);
while (rs.next()) {
dynamicsCol.put(rs.getString("F_BH"), rs.getString("F_MC"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JConnection.BackStatement(stmt, rs);
}
}
/**
* 根据统计分类"国家"进行分类时，获取国家名称的动态展示列信息。
*/
private void getGJColInfo() {
String vsSql = "SELECT MAX(ZJCOUNTRY.F_BH) AS F_BH,MAX(ZJCOUNTRY.F_MC) AS F_MC FROM ZJWBZHXX,ZJWHYHZD,ZJCOUNTRY " +
"WHERE ZJWBZHXX.F_YHBH=ZJWHYHZD.F_YHBH "
+ " AND ZJWHYHZD.F_GJBH=ZJCOUNTRY.F_BH"
+ " AND F_SQDWBH LIKE '" + dwbh + "%' GROUP BY ZJCOUNTRY.F_BH";
stmt = conn.createStatement();
try {
rs = stmt.executeQuery(vsSql);
while (rs.next()) {
dynamicsCol.put(rs.getString("F_BH"), rs.getString("F_MC"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JConnection.BackStatement(stmt, rs);
}
}
/**
* 根据统计分类"币种"进行分类时，获取币种名称的动态展示列信息。
*/
private void getBZColInfo() {
String vsSql = "SELECT MAX(ZJBZZD.F_WBBH) AS F_BZBH,MAX(ZJBZZD.F_WBMC) AS F_BZMC FROM ZJWBZHXX,ZJBZZD " +
"WHERE ZJWBZHXX.F_BZBH=ZJBZZD.F_WBBH " +
"AND F_SQDWBH LIKE '" + dwbh + "%' GROUP BY ZJWBZHXX.F_BZBH,ZJBZZD.F_WBMC";
stmt = conn.createStatement();
try {
rs = stmt.executeQuery(vsSql);
while (rs.next()) {
dynamicsCol.put(rs.getString("F_BZBH"), rs.getString("F_BZMC"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JConnection.BackStatement(stmt, rs);
}
}
/**
* 根据统计分类"银行"进行分类时，获取银行名称的动态展示列信息。
*/
private void getYHColInfo() {
String vsSql = "SELECT MAX(ZJWHYHZD.F_YHBH) AS F_YHBH,MAX(ZJWHYHZD.F_YHMC) AS F_YHMC FROM ZJWBZHXX,ZJWHYHZD " +
"WHERE ZJWBZHXX.F_YHBH=ZJWHYHZD.F_YHBH " +
"AND F_SQDWBH LIKE '" + dwbh + "%' GROUP BY ZJWBZHXX.F_YHBH,ZJWHYHZD.F_YHMC";
stmt = conn.createStatement();
try {
rs = stmt.executeQuery(vsSql);
while (rs.next()) {
dynamicsCol.put(rs.getString("F_YHBH"), rs.getString("F_YHMC"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JConnection.BackStatement(stmt, rs);
}
}
private String getData(Statement stmt, JConnection conn) throws Exception {
/*创建临时表*/
msTableName = createTmpTable();
StringBuffer sbSql = new StringBuffer();
/**
*根据统计分类参数执行不同的查询 sql，获取不同的结果集插入临时表：
* 银行：根据外币账户表 ZJWBZHXX 所属总行分组查询。
* 币种：根据外币账户币种分组查询 ZJWBZHXX。
* 国家：根据外币账户开户行国家分组查询，需关联金融机构字典。ZJWBZHXX.F_YHBH=ZJWHYHZD.F_YHBH ZJWHYHZD.F_GJBH 分组
* 区域：根据开户行国家分组查询，关联区域中心选用表 ZJAREA_RLGL。
*/
if (vsTjfl.equals(TJFL_BZ)) {
sbSql = getDataByBZ();
} else if (vsTjfl.equals(TJFL_GJ)) {
sbSql = getDataByGJ();
} else if (vsTjfl.equals(TJFL_QY)) {
sbSql=getDataByQY();
} else {//统计分类：银行
sbSql = getDataByYH();
}
/*查询临时表结果集，展示数据*/
rs = stmt.executeQuery(sbSql.toString());
String[] data = new String[3 + dynamicsCol.size()];
data[0] = "F_DWBH";
data[1] = "F_DWMC";
data[2] = "F_HJ";
int indexData = 3;
for (String key : dynamicsCol.keySet()) {
data[indexData] = "F_" + key;
indexData++;
}
FormatDataManager formatDataManager = new FormatDataManager();
/*m_flStru：列表数据根据单位进行分级展示，若不分级，传参空串 */
String m_flStru = PO.GetValueByEnvName("ZJ_XJDWSTRU", "2444");
formatDataManager.formatData(PO, false, m_flStru, mViewID, data, rs);
return formatDataManager.getFormatedDate();
}
/**
* 1、组织查询格式：两种方式
* 第一种方式：直接xml组织好格式模板
* 第二种方式：代码组织想要的格式
*
* @return
* @throws Exception
*/
private TableManager queryFormatObject() throws Exception {
/*第一种方式：直接xml组织好格式模板*/
TableManager tm = new TableManager();
String xmlFileName = "/ZJAccountTJViewFormat.xml";
tm = CommonDBObject.createTableManagerTemplate(xmlFileName, PO);
/*内容定义*/
XmlColumn xmlColumn = null;// 实际列
XmlViewCol xmlViewCol = null;//视图列
mViewID = new String[3 + dynamicsCol.size()];
mViewID[0] = "c0";
mViewID[1] = "c1";
mViewID[2] = "c2";
/*动态创建表格的 Column、Group（Item）*/
XmlItem xmlitem;
XmlGroup group;
String[] lmclist = new String[dynamicsCol.size()];//列名称数组
String[] lbhlist = new String[dynamicsCol.size()];//列编号数组
int lmclistIndex = 0;
for (String key : dynamicsCol.keySet()) {
/*取 dynamicsCol 中的 value 作为列名称*/
lmclist[lmclistIndex] = dynamicsCol.get(key);
/*取 dynamicsCol 中的 key 作为列编号*/
lbhlist[lmclistIndex] = key;
lmclistIndex++;
}
int id = 2;
Vector vec;
for (int j = 0; j < 1; j++) {//外层循环控制动态新增的 Group，此处只显示一个 Group
vec = new Vector();
for (int m = 0; m < lmclist.length; m++) {//内层循环控制新增 Group 内的 Item
/*设置列信息*/
id++;
mViewID[id] = "c" + id;
xmlColumn = new XmlColumn();
xmlColumn.setId("c" + id);// xml 格式中 col 标签的 id 属性
xmlColumn.setAlign("right");
xmlColumn.setCaption(lmclist[m]);
xmlColumn.setDatatype("C");
xmlColumn.setFontname("宋体");
xmlColumn.setFontsize("12");
xmlColumn.setName("F_" + lbhlist[m]);//xml 格式中 col 标签的 name 属性
xmlColumn.setNo("" + (j + 2));
xmlColumn.setViewctrl("ctrl");
xmlColumn.setEditctrl("Combox");
xmlColumn.setLock("1");
xmlColumn.setChange("0");
xmlColumn.setFontstyle("");
xmlColumn.setWidth("80");
/*将 xml 的 Column 添加到表格管理器*/
tm.addColumn(xmlColumn);
/*创建 Group 的 item*/
xmlitem = new XmlItem();
xmlitem.setId("c" + id);
xmlitem.setName("c" + id);
xmlitem.setType("col");
vec.add(xmlitem);//Group 的 item 放入集合
}
/*创建 Group 并将 item 集合添加到 Group */
group = new XmlGroup();
group.setId("g" + j);
group.setCaption(caption);//动态显示。币种、国家、区域、银行名称、
group.setFontname("宋体");
group.setFontsize("12");
group.setFontstyle("");
group.setName("g" + j);
group.setNo("1");
group.setItems(vec);
/*将 Group 添加到表格管理器*/
tm.addGroup(group);
/* 将 group 添加到 xml 视图*/
xmlViewCol = new XmlViewCol();
xmlViewCol.setId("g" + j);
xmlViewCol.setType("group");
tm.addViewCol(xmlViewCol);
}
System.out.print(tm.printDOMTree());
return tm;
}
/**
* 创建临时表
*
* @return
* @throws Exception
*/
public String createTmpTable() throws Exception {
msTableName = "#YHZHSLTJCX" + StringFunction.getTempStr(6);
String sql = "(F_DWBH varchar(100) null,"
+ " F_DWMC varchar(200) null," +
" F_HJ varchar(100) null,";
/*列动态增加：dynamicsCol 的 key 作为临时表的列名。 */
for (String key : dynamicsCol.keySet()) {
sql += " F_" + key + " varchar(8) null,";
}
sql = sql.substring(0, sql.length() - 1);
sql += ")";
System.out.println("--------------1" + msTableName);
msTableName = TempTableManager.createTmpTable(conn, PO, msTableName,
sql, "", "");
System.out.println("--------------2" + msTableName);
return msTableName;
}
/*****************************************************以下方法根据不同得统计分类，组织不同得数据返回前台**************************************************************/
/**
* 根据统计分类"区域"统计每个单位在每个区域上账户的数量。
*
* @return
*/
private StringBuffer getDataByQY() {
StringBuffer sb = new StringBuffer();
if (this.conn.getAutoCommit()) {
this.conn.setAutoCommit(false);
}
/*更新临时表单位、单位名称字段*/
updateTempTableDwInfo();
/*2、根据单位编号更新合计和区域列下的账户数量*/
String tempTable = "tempTable" + StringFunction.getTempStr(6);
try {
stmt = this.conn.createStatement();
/*更新合计*/
String vsSql = "CREATE TABLE " + tempTable + " AS " +
"SELECT ZH.F_SQDWBH F_DWBH,MAX(ZH.F_SQDWMC) F_DWMC,ZJZHAREA.F_BH AS F_BH,MAX(ZJZHAREA.F_MC) F_MC," +
"COUNT(ZH.F_ZH) F_ZHCOUNT " +
"FROM ZJWHYHZD YH,ZJWBZHXX ZH ,ZJZHAREA " +
"WHERE YH.F_YHBH = ZH.F_YHBH " +
"AND YH.F_DQBH=ZJZHAREA.F_BH " +
"AND ZH.F_SQDWBH LIKE '" + dwbh + "%' " +
"GROUP BY ZH.F_SQDWBH,YH.F_DQBH ORDER BY F_SQDWBH";
stmt.execute(vsSql);
vsSql = "UPDATE " + msTableName + " A SET A.F_HJ=(SELECT SUM(F_ZHCOUNT) FROM " + tempTable + " B WHERE A" +
".F_DWBH=B.F_DWBH " +
" group by F_DWBH ) " +
" where exists ( select 1 from " + tempTable + " B where A.F_DWBH=B.F_DWBH ) ";
stmt.executeUpdate(vsSql);
/*更新国家列账户数量*/
stmt = this.conn.createStatement();
for (String key : dynamicsCol.keySet()) {
vsSql = "UPDATE " + msTableName + " A SET A.F_" + key + "=(select F_ZHCOUNT FROM " + tempTable + " " +
"B where A.F_DWBH=B.F_DWBH AND B.F_BH='" + key + "' ) " +
" WHERE exists ( select 1 from " + tempTable + " B where A.F_DWBH=B.F_DWBH AND B.F_BH='" + key + "')";
stmt.executeUpdate(vsSql);
}
vsSql = "DROP TABLE " + tempTable + "";
stmt.execute(vsSql);
this.conn.commit();
this.conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
conn.rollback();
} finally {
JConnection.BackStatement(stmt, rs);
}
/*该处查询临时表结果集时需要罗列出所有列，否则有可能导致某些列的数据不显示*/
sb.append("SELECT F_DWBH,F_DWMC,F_HJ ");
String s = "";
for (String key : dynamicsCol.keySet()) {
s += " F_" + key + ",";
}
if (s.equals("")) {
sb.append(" FROM " + msTableName + " ORDER BY F_DWBH");
} else {
s = s.substring(0, s.length() - 1);
sb.append("," + s);
sb.append(" FROM " + msTableName + " ORDER BY F_DWBH");
}
return sb;
}
/**
* 根据统计分类"国家"统计每个单位在每个国家上账户的数量。
*
* @return
*/
private StringBuffer getDataByGJ() {
StringBuffer sb = new StringBuffer();
if (this.conn.getAutoCommit()) {
this.conn.setAutoCommit(false);
}
/*更新临时表单位、单位名称字段*/
updateTempTableDwInfo();
/*2、根据单位编号更新合计和国家列下的账户数量*/
String tempTable = "tempTable" + StringFunction.getTempStr(6);
try {
stmt = this.conn.createStatement();
/*更新合计*/
String vsSql = "CREATE TABLE " + tempTable + " AS " +
"SELECT ZH.F_SQDWBH F_DWBH,MAX(ZH.F_SQDWMC) F_DWMC,ZJCOUNTRY.F_BH AS F_BH,MAX(ZJCOUNTRY.F_MC) F_MC," +
"COUNT(ZH.F_ZH) F_ZHCOUNT " +
"FROM ZJWHYHZD YH,ZJWBZHXX ZH ,ZJCOUNTRY " +
"WHERE YH.F_YHBH = ZH.F_YHBH " +
"AND YH.F_GJBH=ZJCOUNTRY.F_BH " +
"AND ZH.F_SQDWBH LIKE '" + dwbh + "%' " +
"GROUP BY ZH.F_SQDWBH,ZJCOUNTRY.F_BH ORDER BY F_SQDWBH";
stmt.execute(vsSql);
vsSql = "UPDATE " + msTableName + " A SET A.F_HJ=(SELECT SUM(F_ZHCOUNT) FROM " + tempTable + " B WHERE A" +
".F_DWBH=B.F_DWBH " +
" group by F_DWBH ) " +
" where exists ( select 1 from " + tempTable + " B where A.F_DWBH=B.F_DWBH ) ";
stmt.executeUpdate(vsSql);
/*更新国家列账户数量*/
stmt = this.conn.createStatement();
for (String key : dynamicsCol.keySet()) {
vsSql = "UPDATE " + msTableName + " A SET A.F_" + key + "=(select F_ZHCOUNT FROM " + tempTable + " " +
"B where A.F_DWBH=B.F_DWBH AND B.F_BH='" + key + "' ) " +
" WHERE exists ( select 1 from " + tempTable + " B where A.F_DWBH=B.F_DWBH AND B.F_BH='" + key + "')";
stmt.executeUpdate(vsSql);
}
vsSql = "DROP TABLE " + tempTable + "";
stmt.execute(vsSql);
this.conn.commit();
this.conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
conn.rollback();
} finally {
JConnection.BackStatement(stmt, rs);
}
/*该处查询临时表结果集时需要罗列出所有列，否则有可能导致某些列的数据不显示*/
sb.append("SELECT F_DWBH,F_DWMC,F_HJ ");
String s = "";
for (String key : dynamicsCol.keySet()) {
s += " F_" + key + ",";
}
if (s.equals("")) {
sb.append(" FROM " + msTableName + " ORDER BY F_DWBH");
} else {
s = s.substring(0, s.length() - 1);
sb.append("," + s);
sb.append(" FROM " + msTableName + " ORDER BY F_DWBH");
}
return sb;
}
/**
* 根据统计分类"币种"统计每个单位在每个币种上账户的数量。
*
* @return
*/
private StringBuffer getDataByBZ() {
StringBuffer sb = new StringBuffer();
if (this.conn.getAutoCommit()) {
this.conn.setAutoCommit(false);
}
/*临时表已在 getData() 方法中创建*/
/*更新临时表单位、单位名称字段*/
updateTempTableDwInfo();
/*2、根据单位编号更新合计和银行下的账户数量*/
stmt = this.conn.createStatement();
String tempTable = "tempTable" + StringFunction.getTempStr(6);
try {
/*更新合计*/
String vsSql = "CREATE TABLE " + tempTable + " AS " +
"SELECT ZH.F_SQDWBH F_DWBH,MAX(ZH.F_SQDWMC) F_DWMC,ZH.F_BZBH AS F_BZBH,MAX(YH.F_WBMC) F_BZMC,COUNT(ZH.F_ZH) F_ZHCOUNT " +
"FROM ZJBZZD YH,ZJWBZHXX ZH " +
"WHERE YH.F_WBBH = ZH.F_BZBH " +
"AND ZH.F_SQDWBH LIKE '" + dwbh + "%' " +
"GROUP BY ZH.F_SQDWBH,ZH.F_BZBH ORDER BY F_SQDWBH";
stmt.execute(vsSql);
vsSql = "UPDATE " + msTableName + " A SET A.F_HJ=(SELECT SUM(F_ZHCOUNT) FROM " + tempTable + " B WHERE A" +
".F_DWBH=B.F_DWBH " +
" group by F_DWBH ) " +
" where exists ( select 1 from " + tempTable + " B where A.F_DWBH=B.F_DWBH ) ";
stmt.executeUpdate(vsSql);
/*更新银行列账户数量*/
stmt = this.conn.createStatement();
for (String key : dynamicsCol.keySet()) {
vsSql = "UPDATE " + msTableName + " A SET A.F_" + key + "=(select F_ZHCOUNT FROM " + tempTable + " " +
"B where A.F_DWBH=B.F_DWBH AND B.F_BZBH='" + key + "' ) " +
" WHERE exists ( select 1 from " + tempTable + " B where A.F_DWBH=B.F_DWBH AND B.F_BZBH='" + key + "')";
stmt.executeUpdate(vsSql);
}
vsSql = "DROP TABLE " + tempTable + "";
stmt.execute(vsSql);
this.conn.commit();
this.conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
conn.rollback();
} finally {
JConnection.BackStatement(stmt, rs);
}
JConnection.BackStatement(stmt, rs);
/*该处查询临时表结果集时需要罗列出所有列，否则有可能导致某些列的数据不显示*/
sb.append("SELECT F_DWBH,F_DWMC,F_HJ ");
String s = "";
for (String key : dynamicsCol.keySet()) {
s += " F_" + key + ",";
}
if (s.equals("")) {
sb.append(" FROM " + msTableName + " ORDER BY F_DWBH");
} else {
s = s.substring(0, s.length() - 1);
sb.append("," + s);
sb.append(" FROM " + msTableName + " ORDER BY F_DWBH");
}
return sb;
}
/**
* 根据统计分类"银行"统计每个单位在每个银行账户的数量。
* 视图：单位编号、单位名称、合计、各银行列...
* 1、统计每个单位账户得银行列动态创建临时表。
* <p>
* 2、根据单位编号和银行编号更新合计和银行下的账户数量
* 2-1、查询每个单位在每个银行的账户数量。
* 2-2、查询每个单位银行账户数量的合计。
* <p>
* 3、更新临时表每个单位账户的合计，每个单位各个银行下的账户数量。
* 3-1、根据 2-1 统计的每个银行的账户数量更新临时表。
* 3-2、根据 2-2 统计的每个银行的账户数量更新临时表。
*
* @return
*/
private StringBuffer getDataByYH() {
StringBuffer sb = new StringBuffer();
if (this.conn.getAutoCommit()) {
this.conn.setAutoCommit(false);
}
/*临时表已在 getData() 方法中创建*/
/*更新临时表单位、单位名称字段*/
updateTempTableDwInfo();
/*2、根据单位编号更新合计和银行下的账户数量*/
stmt = this.conn.createStatement();
String tempTable = "tempTable" + StringFunction.getTempStr(6);
try {
/*更新合计*/
String vsSql = "CREATE TABLE " + tempTable + " AS " +
"SELECT ZH.F_SQDWBH F_DWBH,MAX(ZH.F_SQDWMC) F_DWMC,YH.F_YHBH,MAX(YH.F_YHMC) F_YHMC,COUNT(ZH.F_ZH) F_ZHCOUNT " +
"FROM ZJWHYHZD YH,ZJWBZHXX ZH " +
"WHERE YH.F_YHBH = ZH.F_YHBH " +
"AND ZH.F_SQDWBH LIKE '" + dwbh + "%' " +
"GROUP BY ZH.F_SQDWBH,YH.F_YHBH ORDER BY F_SQDWBH";
stmt.execute(vsSql);
vsSql = "UPDATE " + msTableName + " A SET A.F_HJ=(SELECT SUM(F_ZHCOUNT) FROM " + tempTable + " B WHERE A" +
".F_DWBH=B.F_DWBH " +
" group by F_DWBH ) " +
" where exists ( select 1 from " + tempTable + " B where A.F_DWBH=B.F_DWBH ) ";
stmt.executeUpdate(vsSql);
/*更新银行列账户数量*/
stmt = this.conn.createStatement();
for (String key : dynamicsCol.keySet()) {
vsSql = "UPDATE " + msTableName + " A SET A.F_" + key + "=(select F_ZHCOUNT FROM " + tempTable + " " +
"B where A.F_DWBH=B.F_DWBH AND B.F_YHBH='" + key + "' ) " +
" WHERE exists ( select 1 from " + tempTable + " B where A.F_DWBH=B.F_DWBH AND B.F_YHBH='" + key + "')";
stmt.executeUpdate(vsSql);
}
vsSql = "DROP TABLE " + tempTable + "";
stmt.execute(vsSql);
this.conn.commit();
this.conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
conn.rollback();
} finally {
JConnection.BackStatement(stmt, rs);
}
JConnection.BackStatement(stmt, rs);
/*该处查询临时表结果集时需要罗列出所有列，否则有可能导致某些列的数据不显示*/
sb.append("SELECT F_DWBH,F_DWMC,F_HJ ");
String s = "";
for (String key : dynamicsCol.keySet()) {
s += " F_" + key + ",";
}
if (s.equals("")) {
sb.append(" FROM " + msTableName + " ORDER BY F_DWBH");
} else {
s = s.substring(0, s.length() - 1);
sb.append("," + s);
sb.append(" FROM " + msTableName + " ORDER BY F_DWBH");
}
return sb;
}
/**
* 此处若账户对应得银行编号在 ZJWBYHZD 无关联，该单位仍会组织进临时表，待处理。。。
* 先以 ZJWBZHXX 表中得数据做测试，以后修改为 ZJWBZHXX 表
*/
private void updateTempTableDwInfo() {
stmt = this.conn.createStatement();
String vsSql = "INSERT INTO " + msTableName + " " +
" (F_DWBH, F_DWMC)" +
" SELECT F_SQDWBH, MAX(F_SQDWMC) " +
" FROM ZJWBZHXX " +
" WHERE F_SQDWBH LIKE '" + dwbh + "%' " +
" GROUP BY F_SQDWBH ";
try {
stmt.execute(vsSql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JConnection.BackStatement(stmt, rs);
}
}
}