没保存。
package com.db2;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
//import sun.jdbc.rowset.CachedRowSet;
public class SQL2UtilJdbc {
Connection conn = null; // Hold the connection to the database
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// ServletContext ctext = null;
public static boolean isTest = true;//是否测试，这个最后一定要改成false
public static Properties props =null;
public static Properties getProp(){
if(props==null){
props=getCommonProp();
}
return props;
}
public SQL2UtilJdbc() {
}
// public SQL2UtilJdbc(ServletContext ctext) {
// this.ctext = ctext;
// }
// public SQL2Util(ServletContext ctext, String str) {
// this.ctext = ctext;
// this.dbsource = str;
// }
///2014-05-07增加方法start/
/**
* 这个方法返回的是结果集，所以不能在这个方法里关闭结果集（因为外面还要遍历）。
* 所以在调用完这个方法遍历万结果集后，记得关闭外面的结果集。
*
* pstmt.executeUpdate();//INSERT, UPDATE or DELETE
*
* pstmt.execute();//true if the first result is a ResultSet object;
* false if the first result is an update count or there is no result
*
* pstmt.executeQuery();//returns the ResultSet object generated by the query.
*/
public ResultSet selectPrep(String sqlStr, Object[] params) throws Exception {
if(sqlStr==null||sqlStr.trim().equals("")){
String info1="sql语句sqlStr不能为空！ ";
System.out.println(info1);
throw new Exception(info1);
}
if(params==null||params.length<=0){
String info1="sql语句参数列表params不能为空！ ";
System.out.println(info1);
throw new Exception(info1);
}
System.out.println("方法selectPrep====="+sqlStr);
getconnwait();
ResultSet rsn=null;
try {
pstmt = conn.prepareStatement(sqlStr);
// pstmt = conn.prepareStatement(sqlStr,
// ResultSet.TYPE_SCROLL_INSENSITIVE,
// ResultSet.CONCUR_READ_ONLY);
// java.sql.Types
for (int i = 0; i < params.length; i++) {
// prestmt.setObject(parameterIndex, x, targetSqlType);
Object para=params[i];
pstmt.setObject(i+1, para);
}
rsn=pstmt.executeQuery();//returns the ResultSet object generated by the query.
} catch (Exception e) {
System.out.println(sqlStr);
e.printStackTrace();
}finally{
// freeconn();//查询需要对结果集操作所以必须在外面关闭。使用freeconn(Object obj)
}
return rsn;
}
/**
* 该方法没有参数，即它只执行一个完整的，不带问号的sql语句。
* 为了和有参数的分开，防止参数不合法。和selectTrue作用相同。
*
* 这个方法返回的是结果集，所以不能在这个方法里关闭结果集（因为外面还要遍历）。
* 所以在调用完这个方法遍历万结果集后，记得关闭外面的结果集。
*
* pstmt.executeUpdate();//INSERT, UPDATE or DELETE
*
* pstmt.execute();//true if the first result is a ResultSet object;
* false if the first result is an update count or there is no result
*
* pstmt.executeQuery();//returns the ResultSet object generated by the query.
*/
public ResultSet selectPrepNoParam(String sqlStr) throws Exception {
if(sqlStr==null||sqlStr.trim().equals("")){
String info1="sql语句sqlStr不能为空！ ";
System.out.println(info1);
throw new Exception(info1);
}
System.out.println("方法selectPrepNoParam====="+sqlStr);
getconnwait();
ResultSet rsn=null;
try {
pstmt = conn.prepareStatement(sqlStr);
rsn=pstmt.executeQuery();//returns the ResultSet object generated by the query.
} catch (Exception e) {
System.out.println(sqlStr);
e.printStackTrace();
}finally{
// freeconn();//查询需要对结果集操作所以必须在外面关闭。使用freeconn(Object obj)
}
return rsn;
}
/**
*
* pstmt.executeUpdate();//INSERT, UPDATE or DELETE
*
* pstmt.execute();//true if the first result is a ResultSet object;
* false if the first result is an update count or there is no result
*
* pstmt.executeQuery();//returns the ResultSet object generated by the query.
*/
public boolean othersqlPrep(String sqlStr, Object[] params) throws Exception {
if(sqlStr==null||sqlStr.trim().equals("")){
String info1="sql语句sqlStr不能为空！ ";
System.out.println(info1);
throw new Exception(info1);
}
if(params==null||params.length<=0){
String info1="sql语句参数列表params不能为空！ ";
System.out.println(info1);
throw new Exception(info1);
}
System.out.println("方法othersqlPrep====="+sqlStr);
boolean flag = false;
getconnwait();
pstmt = conn.prepareStatement(sqlStr);
if (pstmt != null) {
try {
// java.sql.Types
for (int i = 0; i < params.length; i++) {
// prestmt.setObject(parameterIndex, x, targetSqlType);
Object para=params[i];
pstmt.setObject(i+1, para);
}
/*either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing*/
int reNum=pstmt.executeUpdate();//INSERT, UPDATE or DELETE
// pstmt.execute();
flag = true;
} catch (Exception e) {
System.out.println("方法othersqlPrep异常："+sqlStr+"\n"+e.getMessage());
throw e;
}finally{
freeconn();
}
} else {
throw new Exception("获得数据库连接失败！");
}
System.out.println("方法othersqlPrep执行结果====="+flag+",sql:"+sqlStr);
return flag;
}
public void freeconn(Object obj) {
try {
if(obj instanceof Statement){
Statement stmt=(Statement)obj;
if(stmt!=null){
stmt.close();
stmt=null;
}
}else if(obj instanceof PreparedStatement){
PreparedStatement pstmt=(PreparedStatement)obj;
if(pstmt!=null){
pstmt.close();
pstmt=null;
}
}else if(obj instanceof Connection){
Connection conn=(Connection)obj;
if(conn!=null){
conn.close();
conn=null;
}
}else if(obj instanceof ResultSet){
ResultSet rs=(ResultSet)obj;
if(rs!=null){
rs.close();
rs=null;
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
///2014-05-07增加方法end///
public void getconnwait() throws Exception {
try {
conn = getweblogicConnection();
stmt = conn.createStatement();
try {
stmt.execute("SET LOCK MODE TO WAIT 10");
stmt.execute("SET ISOLATION TO DIRTY READ");
} catch (Exception e) {
System.out.println("方法getconnwait异常：\n"+e.getMessage());
}
} catch (Exception e) {
System.out.println(e.getMessage());
freeconn();
throw e;
}
}
public void freeconn() {
try {
if(rs!=null){
rs.close();
}
if(stmt!=null){
stmt.close();
}
if(pstmt!=null){
pstmt.close();
}
if(conn!=null){
conn.close();
}
rs = null;
stmt = null;
pstmt=null;
conn = null;
} catch (Exception e) {
}
}
// public CachedRowSet select(String SqlStr) throws Exception {
// getconnwait();
// if (stmt != null) {
// try {
// rs = stmt.executeQuery(SqlStr);
// } catch (Exception e) {
// System.err.println("查询失败：" + SqlStr);
// freeconn();
// throw e;
// }
// } else {
// throw new Exception("获得数据库连接失败！");
// }
// CachedRowSet crs = new CachedRowSet();
// crs.populate(rs);
// freeconn();
// return crs;
// }
// // 此函数不关闭数据库连接，在调用函数出关闭
// public oracle.jdbc.rowset.OracleCachedRowSet select(String SqlStr,
// Connection tmpConn) throws Exception {
// ResultSet rsTmp = null;
// Statement stmTmp = tmpConn.createStatement();
// // CachedRowSet crs =null;
// oracle.jdbc.rowset.OracleCachedRowSet crs = null;
// try {
// rsTmp = stmTmp.executeQuery(SqlStr);
// // crs=new CachedRowSet();
// crs = new oracle.jdbc.rowset.OracleCachedRowSet();
// crs.populate(rsTmp);
// } catch (Exception e) {
// System.out.println("异常：" + e.toString());
// stmTmp.close();
// throw e;
// } finally {
// stmTmp.close();
// }
// return crs;
// }
public ResultSet selectTrue(String SqlStr) throws Exception {
getconnwait();
if (stmt != null) {
try {
rs = stmt.executeQuery(SqlStr);
} catch (Exception e) {
freeconn();
e.printStackTrace();
throw e;
}
} else {
throw new Exception("获得数据库连接失败！");
}
return rs;
}
public boolean otherOracleSql(String SqlStr, Connection tmpConn)
throws Exception {
boolean flag = false;
Statement tmpstat = tmpConn.createStatement();
try {
flag = tmpstat.execute(SqlStr);
} catch (Exception e) {
tmpstat.close();
throw e;
} finally {
tmpstat.close();
return flag;
}
}
public boolean othersql(String SqlStr) throws Exception {
boolean flag = false;
getconnwait();
if (stmt != null) {
try {
flag = stmt.execute(SqlStr);
} catch (Exception e) {
System.err.println("sqlstr:" + SqlStr);
freeconn();
throw e;
}
} else {
throw new Exception("获得数据库连接失败！");
}
freeconn();
return flag;
}
public String getBlob(InputStream is) {
// InputStream is = rs.getBinaryStream(1);
String rStr = "";
try {
int bytesRead = 0;
byte[] buffer = new byte[8 * 1924];
if (is != null) {
while ((bytesRead = is.read(buffer)) != -1) {
// byteSum += bytesRead;
// fis2.write(buffer, 0, bytesRead);
String temp = new String(buffer, 0, bytesRead);
rStr = rStr + temp;
}
is.close();
}
} catch (Exception e) {
e.printStackTrace();
}
return rStr;
}
public Connection getweblogicConnection() throws SQLException {
Connection conn = null;
String old_db2_url="jdbc:db2://10.125.190.63:60000/agent";
String old_db2_username="agent";
String old_db2_password="agent";
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
conn = DriverManager.getConnection(old_db2_url, old_db2_username, old_db2_password);
} catch (Exception e) {
conn = null;
e.printStackTrace();
}
return conn;
}
public static Properties getCommonProp(){
String pah=SQL2UtilJdbc.class.getClassLoader().getResource("").getFile();
File pf=new File(pah);
String parentpath=pf.getParent();
String propFilePath=parentpath+"\\common.properties";
System.out.println(propFilePath);
Properties props = new Properties();
InputStream in = null;
try {
in =new FileInputStream(new File(propFilePath));
props.load(in);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(in!=null){
in.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return props;
}
public static String getPropValue(String key){
String res="";
if(key==null||key.trim().equals("")||key.trim().equals("null")){
return "";
}
Properties props = getProp();
if(props!=null){
res=props.getProperty(key.trim());
if(res==null||res.trim().equals("null")){
res="";
}
}else{
System.out.println("props is null");
}
return res;
}
public static void synAMDatxxxxxxxxxxxxx() {
String am_tablename="ACL_OPER";
String am_table_prikey="ID";
String kb_tablename=am_tablename;
if(isTest){
kb_tablename=am_tablename+"_XXX";//测试的情况留不用整2个数据源了，在一个库里测就行，表名区分开，跑通代码后这个标志改成false
}
SQL2UtilJdbc sql2util=null;//这个是本系统的数据源
try {
sql2util = new SQL2UtilJdbc();
} catch (Exception e1) {
e1.printStackTrace();
}
Connection conb=null;//同步到本系统
Statement stmtb=null;
try {
///查询存量数据/
conb=sql2util.getweblogicConnection();
conb.setAutoCommit(false);//进制自动提交
stmtb=conb.createStatement();
for(int i=0;i<5;i++){
String insert1="insert into ACL_OPER_XXX(\"ID\",\"OID\",\"NAME\",\"WORKID\",\"LOGINCODE\",\"LOGINPASS\",\"LOGINSTAT\",\"REGDATE\",\"MODDATE\",\"MOBILETEL\",\"HOMETEL\",\"OFFICETEL\",\"EMAIL\",\"GROUPID\",\"GROUPPOS\",\"NOTESADDR\",\"ISAVAILABLE\",\"CRMLEVEL\",\"PASSWORDUPDATETIME\",\"RECEIVENOTIFICATIONS\",\"DEPTTYPE\",\"DEPTCODE\",\"INSERTTIME\",\"INSERTOPER\",\"UPDATETIME\",\"UPDATEOPER\",\"ACLGRPID\",\"ERRORTIMES\") " +
"values ("+i+",1,'林玉贤','agent2','10001 ','888999 ',0, '2010-08-20 18:05:26' , '2015-04-10 08:16:16' ,'null','null','null','yjtuujeep1@163.com ','1 ','1 ','null',1,3,null ,0,'null','001'," +
"null,'null',null,'null','1',0) ";
//加入批处理任务
stmtb.addBatch(insert1);
System.out.println(insert1);
}
int[] resArr=stmtb.executeBatch();
conb.commit();
stmtb.clearBatch();
} catch (Exception e) {
e.printStackTrace();;
if(conb!=null){
try{
conb.rollback();
conb.setAutoCommit(true);
System.out.println("synAMData_Axxxxxxxxxx出现异常，事务回滚xxxxxxx");
}catch(Exception ee1){
ee1.printStackTrace();
}
}
}finally{
try{
if(stmtb!=null){
stmtb.close();
}
if(conb!=null){
conb.close();
}
stmtb=null;
conb=null;
}catch(Exception ee2){
ee2.printStackTrace();
}
}
}
public static void main(String[] ddd){
// synAMDatxxxxxxxxxxxxx();
// Map resultMap1=new HashMap();
// resultMap1=synAMData_ACL_OPER();
// String res1=(String)resultMap1.get("isFinishSuccess");
// String printInfo1=(String)resultMap1.get("printInfo");
//测试批处理
oldDataMoveAndDeal1();
}
/**
* 批处理的例子,这个做了一些优化，可以统计失败的记录。
*
* @author lushuaiyin
* @param request
* @param response
* @return
*
* 测试sql:
* update A_LSY_TEST set BAK1='' where 1=1
* select * from A_LSY_TEST
* update A_LSY_TEST set BAK1=to_char(current timestamp,'yyyy-mm-dd hh24:mi:ss') where AID='lsy111111'
*/
public static Map oldDataMoveAndDeal1() {
//除了要记录结果标志外，还要记录统计信息，方便在页面显示.
//(失败的时候就不记统计信息了，结果标志就有简短的错误信息，成功后才需要记录批处理的记过，因为批处理即使成功也会有个别数据和文件处理失败，要记录并显示)
Map resultMap=new HashMap();
String isFinishSuccess="no";//是否完成本方法。成功的只有yes，其他值都是失败，可以写失败的原因。
resultMap.put("isFinishSuccess", isFinishSuccess);
resultMap.put("printInfo", "");
SQL2UtilJdbc sql2util=null;//这个是本系统的数据源
try {
sql2util = new SQL2UtilJdbc();
} catch (Exception e1) {
e1.printStackTrace();
isFinishSuccess="failed";
resultMap.put("isFinishSuccess", isFinishSuccess);
resultMap.put("printInfo", "");
return resultMap;
}
Connection cona=null;//查询存量数据
Statement stmta=null;
ResultSet rsa=null;
Connection conb=null;//处理存量数据
Statement stmtb=null;
double allNum=0;//记录数量
double failNum=0;//失败数量
int batchNum=3;//每批次数量
StringBuffer batchResult=new StringBuffer("处理任务：oldDataMoveAndDeal1 ,处理结果：\n"); //结果信息日志
StringBuffer batchFailIDs=new StringBuffer(""); //失败的ID记录
String select1=" select * from A_LSY_TEST ";
try {
///查询存量数据/
conb=sql2util.getweblogicConnection();
conb.setAutoCommit(false);//进制自动提交
cona=sql2util.getweblogicConnection();
stmta=cona.createStatement();
rsa=stmta.executeQuery(select1);
///处理存量数据/
stmtb=conb.createStatement();
int bat=0;//本批条数
String[] idArrTemp=new String[batchNum];//本批次中的id记录
int testnum=0;
while(rsa.next()){
testnum++;
//update TB_AGENT_KDATA set OFFICEFILE='init(19800101000000000).doc' where ID=471
String update1="";
//处理存量逻辑
String id=rsa.getString("AID");
String BAK1=rsa.getString("BAK1");
if(id==null||id.trim().equals("")){
System.out.println("出现ID位空的情况，不作处理，继续下一条数据处理。。。");
continue;
}
idArrTemp[bat]=id;
update1="update A_LSY_TEST set BAK1=to_char(current timestamp,'yyyy-mm-dd hh24:mi:ss') where AID='"+id+"'";//正确的sql
if(testnum==2 ||testnum==3){
//测试失败，故意写错sql，测试批处理异常
update1="update A_LSY_TEST set adate='嚯哈哈,这局sql肯定报错' where AID='"+id+"'";//失败的sql（自己弄一个，比如插入数据类型不一致）
}
//加入批处理任务
stmtb.addBatch(update1);
System.out.println("添加一个sql；【"+update1+"】");
allNum++;
bat++;
//执行批处理(整批)
if(bat==batchNum){
int[] resArr=null;
//记录失败的记录
String faildIDs="";
/
try{
/*executeBatch这里要说明下，结果数组里可以查看执行成功和失败，成功是1，失败是其他。我最初的设计就是用这个统计成功和失败的sql。
但是 我在实际测试中（db2数据库）发现无论成功还是失败
这个数组中记得都是1，也就是说，这个接口说明和实际的效果不一致。那么我就不能用这个返回结果了。那么获取这个批次里执行失败的sql，网上说用
getNextException，但是这个只能查看信息，不能知道具体哪个sql除了问题。为了统计精确的数字。我就在执行异常的时候，回滚事务。
这样的话，这个批次就全部失败了，那么我也就能统计出失败的具体sql了。
这是好处，坏处也有，那就是一个批次里哪些本来能执行成功的sql也会因为某一条失败的sql全部回滚，即殃及池鱼了。
*/
resArr=stmtb.executeBatch();
System.out.println("处理一次结果标志==["+Arrays.toString(resArr)+"]");
conb.commit();
stmtb.clearBatch();
}catch(SQLException ee1){
// ee1.printStackTrace();
System.out.println(ee1.getNextException());
//记录失败的记录
faildIDs=getFailIdsDirect(idArrTemp);
System.out.println("处理一次失败id==["+faildIDs+"]");
if(faildIDs!=null && !(faildIDs.trim().equals("")) ){
failNum+=faildIDs.split(",").length;
batchFailIDs.append(faildIDs+"\n");
}
System.out.println("Exception1 oldDataMoveAndDeal1,批处理[TB_AGENT_KDATA]失败id==["+faildIDs+"]");
bat=0;//清零
idArrTemp=new String[batchNum];//id记录清空
if(conb!=null){//本批次回滚,如果不回滚，成功的和失败的sql你就没法区别出来，如果你不在意哪些sql失败了，那么就把下面几行注掉。
try{
conb.rollback();
System.out.println("出现异常，事务回滚。。。");
}catch(Exception e2){
e2.printStackTrace();
}
}//end 回滚
continue;//继续下次循环
}
//
// //记录失败的记录
// faildIDs=getFailIds(resArr,idArrTemp);//这是最初的设计，但是实际测试结果和接口说明不一致，就不用这个方法统计了
//
// if(faildIDs!=null && !(faildIDs.trim().equals("")) ){
// failNum+=faildIDs.split(",").length;
// batchFailIDs.append(faildIDs+"\n");
// }
// System.out.println("oldDataMoveAndDeal1,批处理[TB_AGENT_KDATA]失败id==["+faildIDs+"]");
bat=0;//清零
idArrTemp=new String[batchNum];//id记录清空
}
}//end while end while end while end while
//执行批处理(最后一个不完整的批次)
if(bat>0){
int[] resArr=null;
//记录失败的记录
String faildIDs="";
/
try{
resArr=stmtb.executeBatch();
System.out.println("处理一次结果标志==["+Arrays.toString(resArr)+"]");
conb.commit();
stmtb.clearBatch();
}catch(Exception ee1){
System.out.println(ee1.getMessage());
//记录失败的记录
faildIDs=getFailIdsDirect(idArrTemp);
if(faildIDs!=null && !(faildIDs.trim().equals("")) ){
failNum+=faildIDs.split(",").length;
batchFailIDs.append(faildIDs+"\n");
}
System.out.println("Exception2 oldDataMoveAndDeal1,批处理[TB_AGENT_KDATA]失败id==["+faildIDs+"]");
bat=0;//清零
idArrTemp=new String[batchNum];//id记录清空
if(conb!=null){
try{
conb.rollback();
System.out.println("出现异常，事务回滚。。。");
}catch(Exception e2){
e2.printStackTrace();
}
}
//continue;//这是最后一次了
}
//
// //记录失败的记录
// faildIDs=getFailIdsDirect(idArrTemp);
//
// if(faildIDs!=null && !(faildIDs.trim().equals("")) ){
// failNum+=faildIDs.split(",").length;
// batchFailIDs.append(faildIDs+"\n");
// }
// System.out.println("oldDataMoveAndDeal1,批处理[TB_AGENT_KDATA]失败id==["+faildIDs+"]");
bat=0;//清零
idArrTemp=new String[batchNum];//id记录清空
}
isFinishSuccess="yes";
} catch (Exception e) {
System.out.println(e.getMessage());
if(conb!=null){
try{//这里的回滚其实没啥用了，但是留着也没错的
conb.rollback();
conb.setAutoCommit(true);
System.out.println("出现异常，事务回滚。。。");
isFinishSuccess="出现异常，事务回滚。。。";
}catch(Exception ee1){
ee1.printStackTrace();
}
}
}finally{
try{
if(rsa!=null){
rsa.close();
}
if(stmtb!=null){
stmtb.close();
}
if(conb!=null){
conb.close();
}
if(stmta!=null){
stmta.close();
}
if(cona!=null){
cona.close();
}
rsa=null;
stmtb=null;
conb=null;
stmta=null;
cona=null;
}catch(Exception ee2){
ee2.printStackTrace();
}
}
batchResult.append(isFinishSuccess+"\n");
batchResult.append("数据处理总条数：["+formatDouble(allNum)+"],失败总条数:["+formatDouble(failNum)+"]"+"\n");
batchResult.append("失败数据的id记录：["+batchFailIDs+"]\n");
System.out.println(batchResult.toString());
resultMap.put("isFinishSuccess", isFinishSuccess);
resultMap.put("printInfo", batchResult.toString());
return resultMap;
}
//判断并获取失败的批处理id
public static String getFailIds(int[] resArr,String[] idArrTemp){
String fials="";
if(resArr==null||idArrTemp==null||(idArrTemp.length)<resArr.length){
System.out.println("getFailIds参数不合法。。。");
return null;
}
for(int i=0;i<resArr.length;i++){
if( 1!=resArr[i]){
if(idArrTemp[i]!=null){
fials+=idArrTemp[i]+",";
}
}
}
return fials;
}
//判断并获取失败的批处理id
public static String getFailIdsDirect(String[] idArrTemp){
String fials="";
if(idArrTemp==null){
System.out.println("getFailIdsDirect参数不合法。。。");
return null;
}
for(int i=0;i<idArrTemp.length;i++){
if(idArrTemp[i]!=null){
fials+=idArrTemp[i]+",";
}
}
return fials;
}
public static String formatDouble(double dd){
String res="";
res=String.valueOf(dd);
res=res.substring(0,res.indexOf("."));
return res;
}
public static void fff1343(){
SQL2UtilJdbc sQL2UtilJdbc = new SQL2UtilJdbc();
ResultSet rs=null;
try {
String sSql = "select count(*) from ACL_OPER t where t.logincode=? ";
Object[] params=new Object[]{
"xx7004"
};
rs = sQL2UtilJdbc.selectPrep(sSql, params);
if (rs.next()) {
String logggg=rs.getString("1");
System.out.println("counttt==["+logggg+"]");
}else{
System.out.println("hreeeeee");
}
} catch (Exception e) {
e.printStackTrace();
}finally{
sQL2UtilJdbc.freeconn(rs);
sQL2UtilJdbc.freeconn();
}
}
public static void fff111(){
SQL2UtilJdbc sQL2UtilJdbc = new SQL2UtilJdbc();
Timestamp tt=new Timestamp(116, 1, 15, 16, 8, 1, 3);
System.out.println(tt.toString());
Date dd=new Date(tt.getTime());
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss ");
String ddstr=sdf.format(dd);
System.out.println("---"+ddstr );
try {
// String sSql = "UPDATE ACL_OPER_XXX set REGDATE=TIMESTAMP('2016-10-10 12:11:11') where id="+16388;
// String sSql = "UPDATE ACL_OPER_XXX set REGDATE='2015-10-10 12:11:11' where id="+16388;
String sSql = "UPDATE ACL_OPER_XXX set REGDATE='"+ddstr+"' where id="+16388;
sQL2UtilJdbc.othersql(sSql);
} catch (Exception e) {
e.printStackTrace();
}finally{
sQL2UtilJdbc.freeconn();
}
}
public static void fff(){
String sssss=getPropValue("old_dssssssssssssb2_url");
System.out.println("rs=["+sssss+"]");
String fff=getPropValue("old_db2_username");
System.out.println("rs=["+fff+"]");
SQL2UtilJdbc sQL2UtilJdbc = new SQL2UtilJdbc();
ResultSet rs=null;
try {
String sSql = "select * from ACL_OPER t where t.logincode=? ";
// String sSql = "select count(*) from ACL_OPER t where t.logincode <> ? ";
Object[] params=new Object[]{
"7004"
};
rs = sQL2UtilJdbc.selectPrep(sSql, params);
if (rs.next()) {
// String logggg=rs.getString("logincode");
// String logggg=rs.getString("1");
Timestamp tttt=rs.getTimestamp("REGDATE");
// System.out.println("logggg==["+logggg+"]");
System.out.println("tttt==["+tttt+"]");
System.out.println("tttt==["+tttt.toString()+"]");
}else{
System.out.println("hreeeeee");
}
} catch (Exception e) {
e.printStackTrace();
}finally{
sQL2UtilJdbc.freeconn(rs);
sQL2UtilJdbc.freeconn();
}
}
///
/*
* 时间戳的null不能是'' 或者是'null'，只能是null.
* 如果有值那就必须是'2010-08-20 18:05:26'
* 所以单引号也在这个方法内处理，不在sql拼装那边处理了
*
UPDATE ACL_OPER_XXX
SET
REGDATE = '2010-08-20 18:05:26',
INSERTTIME = null
WHERE ID = 16388
*/
public static String dealStrForSql(String str){
String res="null";
if(str==null){
return null;
}else{
return "'"+str+"'";
}
}
public static String timestampToStr(Timestamp tt){
String res="null";
if(tt==null){
return "null";
}
Date dd=new Date(tt.getTime());
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
res=sdf.format(dd);
if(res==null||res.trim().equals("")){
res="null";
}else{
res=" '"+res+"' ";
}
return res;
}
/*
* 只有下面两种方式才是对的
* UPDATE ACL_OPER_XXX SET PASSWORDUPDATETIME = DATE('2012-11-11')
WHERE ID=16388
UPDATE ACL_OPER_XXX SET PASSWORDUPDATETIME = null
WHERE ID=16388
*/
public static String DateToStrForDb2(Date dd){
String res="null";//db2只认 null，不认 ''
if(dd==null){
return "null";
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
res=sdf.format(dd);
if(res==null||res.trim().equals("")){
res="null";
}else{
res="DATE('"+res+"')";
}
return res;
}
public static String DateToStr(Date dd){
String res="";
if(dd==null){
return "";
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
res=sdf.format(dd);
return res;
}
/*
* 这个方法是比较两个库的表数据可数是否一样，不一样就说明柜员系统的数据有被删除的。
* 这个方法必须在数据同步之后才能调用，数据同步是指：把柜员系统里的数据遍历，查看本系统是否有，有就update，没有就insert。
* 这个逻辑无法保证本系统有但是柜员系统却没有的数据也能被删掉。所以才有这个方法。
* 本系统的数据只可能和柜员系统的数据一样多，或者更多。
* 这个方法就是处理多出来的数据，这些数据在柜员系统被删除了，那么本系统也要删除。
*
* 这个方法也是只针对有唯一主键的表（或者有字段可以确定唯一记录的表）.
*
* 其他同步方法调用完成后还要判断是否需要调用本方法，为了减少这个判断，就统一决定都调用，是否要走这个批量的逻辑，在本方法内判断。
* 这样保证减少很多冗余代码。
*/
public static Map synAMDataDelete(Map param) {
String isFinishSuccess="no";//是否完成本方法。成功的只有yes，其他值都是失败，可以写失败的原因。
Map resultMap=new HashMap();
if(param==null){
isFinishSuccess="param is null";
resultMap.put("isFinishSuccess", isFinishSuccess);
resultMap.put("printInfo", "synAMDataDelete 参数param空");
return resultMap;
}
String am_tablename=(String)param.get("am_tablename");
String am_table_prikey=(String)param.get("am_table_prikey");
if(am_tablename==null||am_tablename.trim().equals("")
||am_table_prikey==null||am_table_prikey.trim().equals("")){
isFinishSuccess="param中变量null";
resultMap.put("isFinishSuccess", isFinishSuccess);
resultMap.put("printInfo", "param中变量有空值");
return resultMap;
}else{
am_tablename=am_tablename.toUpperCase();
am_table_prikey=am_table_prikey.toUpperCase();
}
String kb_tablename=am_tablename;
if(isTest){
kb_tablename=am_tablename+"_XXX";//测试,在一个库里测就行，表名区分开，跑通代码后这个标志改成false
}
//判断两个库中这张表的数据是否一致，不一致才需要走下面的逻辑
String selectCount1=" select count(*) from "+kb_tablename;
String selectCountAm=" select count(*) from "+am_tablename;
int count1=getSqlCount(selectCount1);
int countAm=getSqlCountAm(selectCountAm);
if(count1==countAm){//无需走下面的删除逻辑(不可能小于，因为在走完同步后，本库数据量肯定>=柜员库)
isFinishSuccess="yes";
resultMap.put("isFinishSuccess", isFinishSuccess);
resultMap.put("printInfo", "表"+am_tablename+"在两个库里数据量一致，无需再走删除垃圾数据逻辑。");
return resultMap;
}
SQL2UtilJdbc sql2util=null;//这个是本系统的数据源
try {
sql2util = sql2util = new SQL2UtilJdbc();
} catch (Exception e1) {
e1.printStackTrace();
isFinishSuccess="sql2util=null";
resultMap.put("isFinishSuccess", isFinishSuccess);
resultMap.put("printInfo", "");
return resultMap;
}
Connection con=null;//查询本系统
Statement stmt=null;
ResultSet rs=null;
Connection conb=null;//也是操作本系统，为了批量，再起一个链接
Statement stmtb=null;
String select1=" select * from "+kb_tablename;
try {
con=sql2util.getweblogicConnection();
stmt=con.createStatement();
rs=stmt.executeQuery(select1);
conb=sql2util.getweblogicConnection();
conb.setAutoCommit(false);//进制自动提交
stmtb=conb.createStatement();
double allNum=0;//记录数量
int batchNum=10;//每批次数量
int bat=0;//本批条数
//遍历知识库系统数据，每条去柜员系统查看有没有，如果柜员系统没有，那么说明这是一条垃圾数据，要删掉。
while(rs.next()){
//处理存量逻辑
String id=rs.getString(am_table_prikey);
if(id==null||id.trim().equals("")){
continue;
}
String select2=" select count(*) from "+am_tablename+" where "+am_table_prikey+"="+id;//去柜员系统查看有没有这条
int ishaveAm=getSqlCountAm(select2);
if(ishaveAm==0){//没有就说明是垃圾数据，本系统也要删除
String delete1=" DELETE FROM "+kb_tablename+" WHERE "+am_table_prikey+"="+id;
//加入批处理任务
stmtb.addBatch(delete1);
allNum++;
bat++;
}else if(ishaveAm==-1){//sql执行失败
//这种情况一般不出现，出现的话，我就过，不做处理，结果也就是漏了这条数据，但是绝不能删
System.out.println("synAMDataDelete查询柜员系统的一条数据sql执行失败["+select2+"],这条数据没有去删除，是否垃圾数据还需要确认。");
}
//执行批处理(整批)
if(bat==batchNum){
//执行批量
int[] resArr=stmtb.executeBatch();
conb.commit();
stmtb.clearBatch();
bat=0;//清零
}
}//end while end while end while end while
//执行批处理(最后一个不完整的批次)
if(bat>0){
int[] resArr=stmtb.executeBatch();
conb.commit();
stmtb.clearBatch();
bat=0;//清零
}
isFinishSuccess="yes";
} catch (Exception e) {
e.printStackTrace();
if(conb!=null){
try{
conb.rollback();
conb.setAutoCommit(true);
System.out.println("出现异常，事务回滚。。。");
isFinishSuccess="failed";
}catch(Exception ee1){
ee1.printStackTrace();
}
}
}finally{
try{
if(rs!=null){
rs.close();
}
if(stmtb!=null){
stmtb.close();
}
if(conb!=null){
conb.close();
}
if(stmt!=null){
stmt.close();
}
if(con!=null){
con.close();
}
rs=null;
stmtb=null;
conb=null;
stmt=null;
con=null;
}catch(Exception ee2){
ee2.printStackTrace();
}
}
// System.out.println(batchResult.toString());
resultMap.put("isFinishSuccess", isFinishSuccess);
// resultMap.put("printInfo", batchResult.toString());
return resultMap;
}
/*
* ACL_OPER
insert into "ACL_OPER"("ID","OID","NAME","WORKID","LOGINCODE","LOGINPASS","LOGINSTAT","REGDATE","MODDATE","MOBILETEL","HOMETEL","OFFICETEL","EMAIL","GROUPID","GROUPPOS","NOTESADDR","ISAVAILABLE","CRMLEVEL","PASSWORDUPDATETIME","RECEIVENOTIFICATIONS","DEPTTYPE","DEPTCODE","INSERTTIME","INSERTOPER","UPDATETIME","UPDATEOPER","ACLGRPID","ERRORTIMES")
values (24613,829,'杨恩德','a00007','10000 ','8888 ',null,'2011-04-25 15:17:22','2011-08-04 09:15:17',null,null,null,null,'1 ','1 ',null,1,-1,null,null,null,'00005',null,null,null,null,'1',0);
*/
/*
* ACL_ROLE
insert into "ACL_ROLE"("ID","EXCODE","NAME","RGID","ISAVAILABLE","HQCARDFLAG") values (39980,'000001 ','通用知识库','ims',1,null);
*/
/*
* ACL_ORG
insert into "ACL_ORG"("ID","FID","ALGCODE","NAME","ADDRESS","OFLAG","EXCODE","ISAVAILABLE","HQCARDFLAG")
values (1,1,'00001 ','昆仑银行','昆仑银行','1 ','00001 ',1,1);
*/
/*
* ACL_AG_GRP
insert into "ACL_AG_GRP"("ID","NAME","HQCARDFLAG") values ('10 ','企业业务咨询组 ',1);
*/
/*
* ACL_O_R_MAP
insert into "ACL_O_R_MAP"("ID","USERID","RID") values (337099,24007,13001);
*/
/**
*
* 这个方法实现两个库之间的某张表倒数。这个只针对有单个主键的表，对多主键的关联表不使用。(就是能根据一个字段就唯一确定这条记录的表)
* 就是把某一张表的数据库A中的数据同步到数据库B中（这个方法还不完全，只能同步新增和修改的数据，还要专门增加一个方法，实现同步删除的数据）
*
*
* 逻辑：查询数据库A的数据，遍历结果集，去查数据库B的数据，查到就更新，查不到就插入。
* （数据库A中有，但是数据库B中没有的数据，是因为A中被删除了，这个需要另外的方法实现，这里不实现）
*
* 初期测试还在同一个库，表名用_XXX区分。因为没有2个好的测试库，所以先这么测，ACL_OPER和ACL_OPER_XXX认为是不同的两个库里的同一张表。
*
* 因为在不同的两个数据源中，所以update和insert只能是解析字段值，然后拼装，不能用delete加insert into select方式做了。这样就导致每张表都要
* 单独写个方法去解析sql字段。
*
* @param request
* @param response
* @param param
* @return
*/
public static Map synAMData_ACL_OPER() {
//除了要记录结果标志外，还要记录统计信息，方便在页面显示.
//(失败的时候就不记统计信息了，结果标志就有简短的错误信息，成功后才需要记录批处理的记过，因为批处理即使成功也会有个别数据和文件处理失败，要记录并显示)
Map resultMap=new HashMap();
String am_tablename="ACL_OPER";
String am_table_prikey="ID";
String kb_tablename=am_tablename;
if(isTest){
kb_tablename=am_tablename+"_XXX";//测试的情况留不用整2个数据源了，在一个库里测就行，表名区分开，跑通代码后这个标志改成false
}
String isFinishSuccess="no";//是否完成本方法。成功的只有yes，其他值都是失败，可以写失败的原因。
SQL2UtilJdbc sQL2UtilJdbc = new SQL2UtilJdbc();//这个是柜员系统的数据源，使用jdbc方式连接
SQL2UtilJdbc sql2util=null;//这个是本系统的数据源
try {
sql2util = new SQL2UtilJdbc();
} catch (Exception e1) {
e1.printStackTrace();
isFinishSuccess="failed";
resultMap.put("isFinishSuccess", isFinishSuccess);
resultMap.put("printInfo", "");
return resultMap;
}
Connection am_con=null;//查询柜员系统
Statement am_stmt=null;
ResultSet am_rs=null;
Connection conb=null;//同步到本系统
Statement stmtb=null;
String select1=" select * from "+am_tablename;
try {
am_con=sQL2UtilJdbc.getweblogicConnection();
am_stmt=am_con.createStatement();
am_rs=am_stmt.executeQuery(select1);
///查询存量数据/
conb=sql2util.getweblogicConnection();
conb.setAutoCommit(false);//进制自动提交
stmtb=conb.createStatement();
double allNum=0;//记录数量
int batchNum=1;//每批次数量
int bat=0;//本批条数
//遍历柜员系统数据
while(am_rs.next()){
//处理存量逻辑
String id=am_rs.getString(am_table_prikey.toUpperCase());
if(id==null||id.trim().equals("")){
continue;
}
String select2=" select count(*) from "+kb_tablename+" where id="+id;
int ishave=getSqlCount(select2);
if(ishave>0){//有就更新数据
/*
UPDATE AGENT.ACL_OPER
SET ID = ID , OID = OID, NAME = 'NAME', WORKID = 'WORKID', LOGINCODE = 'LOGINCODE'
, LOGINPASS = 'LOGINPASS', LOGINSTAT = LOGINSTAT, REGDATE = 'REGDATE', MODDATE = 'MODDATE'
, MOBILETEL = 'MOBILETEL', HOMETEL = 'HOMETEL', OFFICETEL = 'OFFICETEL', EMAIL = 'EMAIL', GROUPID = 'GROUPID'
, GROUPPOS = 'GROUPPOS', NOTESADDR = 'NOTESADDR', ISAVAILABLE = ISAVAILABLE, CRMLEVEL = CRMLEVEL
, PASSWORDUPDATETIME = PASSWORDUPDATETIME, RECEIVENOTIFICATIONS = RECEIVENOTIFICATIONS
, DEPTTYPE = 'DEPTTYPE', DEPTCODE = 'DEPTCODE', INSERTTIME = 'INSERTTIME', INSERTOPER = 'INSERTOPER'
, UPDATETIME = 'UPDATETIME', UPDATEOPER = 'UPDATEOPER', ACLGRPID = 'ACLGRPID', ERRORTIMES = ERRORTIMES
WHERE -- Please complete
;
*/
String update1=" UPDATE "+kb_tablename+" SET " +
" OID = " +am_rs.getInt("OID")+ //int
" , NAME = '" +am_rs.getString("NAME")+ "' "+
" , WORKID = '" +am_rs.getString("WORKID")+ "' "+
" , LOGINCODE = '" +am_rs.getString("LOGINCODE")+ "' "+
" , LOGINPASS = '" +am_rs.getString("LOGINPASS")+ "' "+
" , LOGINSTAT = " +am_rs.getInt("LOGINSTAT")+
" , REGDATE = " +timestampToStr(am_rs.getTimestamp("REGDATE"))+" "+ //Timestamp
" , MODDATE = " +timestampToStr(am_rs.getTimestamp("MODDATE"))+ " "+
" , MOBILETEL = '" +am_rs.getString("MOBILETEL")+ "' "+
" , HOMETEL = '" +am_rs.getString("HOMETEL")+ "' "+
" , OFFICETEL = '" +am_rs.getString("OFFICETEL")+ "' "+
" , EMAIL = '" +am_rs.getString("EMAIL")+ "' "+
" , GROUPID = '" +am_rs.getString("GROUPID")+ "' "+
" , GROUPPOS = '" +am_rs.getString("GROUPPOS")+ "' "+
" , NOTESADDR = '" +am_rs.getString("NOTESADDR")+ "' "+
" , ISAVAILABLE = " +am_rs.getInt("ISAVAILABLE")+
" , CRMLEVEL = " +am_rs.getInt("CRMLEVEL")+
" , PASSWORDUPDATETIME = " +DateToStrForDb2(am_rs.getDate("PASSWORDUPDATETIME"))+" "+ //Date,不许带单引号
" , RECEIVENOTIFICATIONS = " +am_rs.getInt("RECEIVENOTIFICATIONS")+
" , DEPTTYPE = '" +am_rs.getString("DEPTTYPE")+ "' "+
" , DEPTCODE = '" +am_rs.getString("DEPTCODE")+ "' "+
" , INSERTTIME = " +timestampToStr(am_rs.getTimestamp("INSERTTIME"))+ " "+
" , INSERTOPER = '" +am_rs.getString("INSERTOPER")+ "' "+
" , UPDATETIME = " +timestampToStr(am_rs.getTimestamp("UPDATETIME"))+ " "+
" , UPDATEOPER = '" +am_rs.getString("UPDATEOPER")+ "' "+
" , ACLGRPID = '" +am_rs.getString("ACLGRPID")+ "' "+
" , ERRORTIMES = " +am_rs.getInt("ERRORTIMES")+
" WHERE ID="+id;
//加入批处理任务
stmtb.addBatch(update1);
System.out.println(update1);
}
else{
//没有就插入(插入采用insert into select * from xx where id= 的方式，这样能实现方法的公用，不要去解析具体的字段)
String insert1="insert into "+kb_tablename+"(\"ID\",\"OID\",\"NAME\",\"WORKID\",\"LOGINCODE\",\"LOGINPASS\"" +
",\"LOGINSTAT\",\"REGDATE\",\"MODDATE\",\"MOBILETEL\",\"HOMETEL\",\"OFFICETEL\",\"EMAIL\",\"GROUPID\"" +
",\"GROUPPOS\",\"NOTESADDR\",\"ISAVAILABLE\",\"CRMLEVEL\",\"PASSWORDUPDATETIME\",\"RECEIVENOTIFICATIONS\"" +
",\"DEPTTYPE\",\"DEPTCODE\",\"INSERTTIME\",\"INSERTOPER\",\"UPDATETIME\",\"UPDATEOPER\",\"ACLGRPID\",\"ERRORTIMES\")"
+"values ("+id+","+am_rs.getInt("OID")
+",'" +am_rs.getString("NAME")+ "'" +
",'" +am_rs.getString("WORKID")+ "'" +
",'" +am_rs.getString("LOGINCODE")+ "'" +
",'" +am_rs.getString("LOGINPASS")+ "'" +
"," +am_rs.getInt("LOGINSTAT")+
"," +timestampToStr(am_rs.getTimestamp("REGDATE"))+"" +
"," +timestampToStr(am_rs.getTimestamp("MODDATE"))+"" +
",'" +am_rs.getString("MOBILETEL")+ "'" +
",'" +am_rs.getString("HOMETEL")+ "'" +
",'" +am_rs.getString("OFFICETEL")+ "'" +
",'" +am_rs.getString("EMAIL")+ "'" +
",'" +am_rs.getString("GROUPID")+ "'" +
",'" +am_rs.getString("GROUPPOS")+ "'" +
",'" +am_rs.getString("NOTESADDR")+ "'" +
","+am_rs.getInt("ISAVAILABLE")+"" +
","+am_rs.getInt("CRMLEVEL")+"" +
"," +DateToStrForDb2(am_rs.getDate("PASSWORDUPDATETIME"))+" " +
","+am_rs.getInt("RECEIVENOTIFICATIONS")+ "" +
",'" +am_rs.getString("DEPTTYPE")+ "'" +
",'" +am_rs.getString("DEPTCODE")+ "'" +
"," +timestampToStr(am_rs.getTimestamp("INSERTTIME"))+ "" +
",'" +am_rs.getString("INSERTOPER")+ "'," +timestampToStr(am_rs.getTimestamp("UPDATETIME"))+ "" +
",'" +am_rs.getString("UPDATEOPER")+ "'" +
",'" +am_rs.getString("ACLGRPID")+ "',"+am_rs.getInt("ERRORTIMES")+ ") ";
//注意insert的sql在最后不可以有分号！否则会报错，但是在toad里可以成功执行，发现这个错耗费了我一下午，别再犯了。
//加入批处理任务
stmtb.addBatch(insert1);
System.out.println(insert1);
}
allNum++;
bat++;
//执行批处理(整批)
if(bat==batchNum){
//执行批量
int[] resArr=stmtb.executeBatch();
conb.commit();
// stmtb.clearBatch();
bat=0;//清零
}
}//end while end while end while end while
//执行批处理(最后一个不完整的批次)
if(bat>0){
int[] resArr=stmtb.executeBatch();
conb.commit();
stmtb.clearBatch();
bat=0;//清零
}
isFinishSuccess="yes";
} catch (Exception e) {
e.printStackTrace();;
if(conb!=null){
try{
conb.rollback();
conb.setAutoCommit(true);
System.out.println("synAMData_ACL_OPER出现异常，事务回滚xxxxxxx");
isFinishSuccess="failed";
}catch(Exception ee1){
ee1.printStackTrace();
}
}
}finally{
try{
if(am_rs!=null){
am_rs.close();
}
if(stmtb!=null){
stmtb.close();
}
if(conb!=null){
conb.close();
}
if(am_stmt!=null){
am_stmt.close();
}
if(am_con!=null){
am_con.close();
}
am_rs=null;
stmtb=null;
conb=null;
am_stmt=null;
am_con=null;
}catch(Exception ee2){
ee2.printStackTrace();
}
}
// System.out.println(batchResult.toString());
//下面走删除本系统垃圾数据的判断,是否成功也是本方法的最后一步。
// Map paramDel=new HashMap();
// paramDel.put("am_tablename", am_tablename);
// paramDel.put("am_table_prikey", am_table_prikey);
// Map delResMap=synAMDataDelete(paramDel);
// String resD=(String)delResMap.get("isFinishSuccess");
// String printInfoD=(String)delResMap.get("printInfo");
// if(resD!=null&&resD.equals("yes")){
// isFinishSuccess="yes";
// }else{
// isFinishSuccess=resD;
// resultMap.put("printInfo", printInfoD);
// }
resultMap.put("isFinishSuccess", isFinishSuccess);
return resultMap;
}
/*
* 必须是count查询语句，否则报错
* 并且不能有别名，只能是select count(*) from 。。。。。
*/
public static int getSqlCount(String sql){
int res=0;
if(sql==null||sql.trim().equals("")){
res=-1;//查询结果集肯定>=0，这里是为了告诉sql有问题
return res;
}
SQL2UtilJdbc sql2util=null;//这个是本系统的数据源
try {
sql2util = new SQL2UtilJdbc();
} catch (Exception e1) {
e1.printStackTrace();
}
Connection cona=null;
Statement stmta=null;
ResultSet rsa=null;
try {
cona=sql2util.getweblogicConnection();
stmta=cona.createStatement();
rsa=stmta.executeQuery(sql);
if(rsa.next()){
String count=rsa.getString("1");
res=Integer.valueOf(count);
}
} catch (Exception e) {
System.out.println(e.getMessage());
}finally{
try{
if(rsa!=null){
rsa.close();
}
if(stmta!=null){
stmta.close();
}
if(cona!=null){
cona.close();
}
rsa=null;
stmta=null;
cona=null;
}catch(Exception ee2){
ee2.printStackTrace();
}
}
return res;
}
/*
* 必须是count查询语句，否则报错
* 并且不能有别名，只能是select count(*) from 。。。。。
*/
public static int getSqlCountAm(String sql){
int res=0;
if(sql==null||sql.trim().equals("")){
res=-1;//查询结果集肯定>=0，这里是为了告诉sql有问题
return res;
}
SQL2UtilJdbc sql2util = new SQL2UtilJdbc();//这个是柜员系统的数据源，使用jdbc方式连接
Connection cona=null;
Statement stmta=null;
ResultSet rsa=null;
try {
cona=sql2util.getweblogicConnection();
stmta=cona.createStatement();
rsa=stmta.executeQuery(sql);
if(rsa.next()){
String count=rsa.getString("1");
res=Integer.valueOf(count);
}
} catch (Exception e) {
System.out.println(e.getMessage());
}finally{
try{
if(rsa!=null){
rsa.close();
}
if(stmta!=null){
stmta.close();
}
if(cona!=null){
cona.close();
}
rsa=null;
stmta=null;
cona=null;
}catch(Exception ee2){
ee2.printStackTrace();
}
}
return res;
}
}
扫一扫
702
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
