emp.zip_eclipse+emp_emp.jar_java EMP_javaweb_jdbc

package com.neusoft.services.support; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import com.neusoft.system.db.DBUtils; public abstract class JdbcSupport { protected boolean isNotNull(Object val) { return val!=null && !val.equals(""); } protected String[] getIdList(Object val) throws Exception { if(val instanceof java.lang.String[]) { return (String[])val; } else if(val instanceof java.lang.String) { return new String[]{val.toString()}; } else { //强行引发异常 throw new Exception("参数类型未知,无法处理......."); } } /** * SQL批处理方法: * < * 适合数据批量删除 * SQL: * delete from table where id = ? * > * @param Object idlist[]={"135","136","137","140","141"}; * @return * @throws Exception */ protected boolean batchUpdate(String sql,Object...idlist)throws Exception { //1.定义JDBC变量 PreparedStatement pstm=null; try { //3.编译SQL语句 pstm=DBUtils.prepareStatement(sql); //4.参数赋值 for(Object param:idlist) //136 { pstm.setObject(1, param); //5.将准备好的SQL语句放入数据库内存缓冲区 pstm.addBatch(); } /** * 6.以事务方式执行缓冲区中的所有SQL语句 */ //6.1定义事务返回值 boolean tag=false; //6.2开启事务 DBUtils.beginTransaction(); try { //6.3执行缓冲区的语句-----SQL批处理 pstm.executeBatch(); //6.4提交事务 DBUtils.commit(); tag=true; } catch(Exception ex) { DBUtils.rollback(); ex.printStackTrace(); } finally { DBUtils.endTransaction(); } return tag; } finally { DBUtils.close(pstm); } } /** * SQL批处理方法 * < * 单一状态更新 * sql格式如下: * update table * set c=newState * where id=idlist[i] * ===> * update table * set c=? * where id=? * * > * @param sql --- sql语句 * @param newState --- 目标状态 * @param idlist --- 主键列表 * @return * @throws Exception */ protected boolean batchUpdate(String sql,Object newState,Object...idlist)throws Exception { //1.定义JDBC接口变量 PreparedStatement pstm=null; try { //2.编译SQL语句 pstm=DBUtils.prepareStatement(sql); //3.参数赋值 for(Object param:idlist) { pstm.setObject(1, newState); pstm.setObject(2, param); pstm.addBatch(); } boolean tag=false; DBUtils.beginTransaction(); try { pstm.executeBatch(); DBUtils.commit(); tag =true; } catch(Exception ex) { DBUtils.rollback(); ex.printStackTrace(); } finally { DBUtils.endTransaction(); } return tag; } finally { DBUtils.close(pstm); } } /** * SQL批处理方法 * < * 多状态更新 * sql格式如下: * update table * set c1=paramVal[0],c2=paramVal[1],c2=paramVal[2],..... * where id=idlist[i] * ===> * update table * set c1=?, c2=?, c3=?...... * where id=? * * > * @param sql update student set sex=?,snumber=?,memo=? where sid=? * @param paramVal ={"x","1234","这是备注"} * @param idlist ={"142","145","142"} * @return * @throws Exception */ protected boolean batchUpdate(String sql,Object paramVal[],Object...idlist)throws Exception { PreparedStatement pstm=null; try { pstm=DBUtils.prepareStatement(sql); for(Object id:idlist) //id:142 { int index=1; for(Object param:paramVal) //param:"这是备注" { pstm.setObject(index++, param); //index:4 } pstm.setObject(index, id); pstm.addBatch(); } /** * 6.以事务方式执行缓冲区中的所有SQL语句 */ //6.1定义事务返回值 boolean tag=false; //6.2开启事务 DBUtils.beginTransaction(); try { //6.3执行缓冲区的语句-----SQL批处理 pstm.executeBatch(); //6.4提交事务 DBUtils.commit(); tag=true; } catch(Exception ex) { DBUtils.rollback(); ex.printStackTrace(); } finally { DBUtils.endTransaction(); } return tag; } finally { DBUtils.close(pstm); } } protected final boolean update(String sql,Object...val)throws Exception { PreparedStatement pstm=null; try { //1.编译SQL语句 pstm=DBUtils.prepareStatement(sql); //2.参数赋值 int index=1; for(Object param:val) { pstm.setObject(index++, param); } //3.执行SQL语句 return pstm.executeUpdate()>0; } finally { DBUtils.close(pstm); } } protected final Map<String,String> queryForMap(String sql,Object...val)throws Exception { PreparedStatement pstm=null; ResultSet rs=null; try { //1.编译SQL语句 pstm=DBUtils.prepareStatement(sql.toString()); //2.参数赋值 int index=1; for(Object param:val) { pstm.setObject(index++, param); } //3.执行SQL rs=pstm.executeQuery(); //4.定义装载查询结果的Map变量 Map<String,String> ins=null; //5.解析rs获取数据 if(rs.next()) { //5.1获取rs描述对象 ResultSetMetaData rsmd=rs.getMetaData(); //5.2计算列数 int count=rsmd.getColumnCount(); //5.3计算初始容量 int size=((int)(count/0.75))+1; //5.4实例化Map对象 ins=new HashMap<>(size); //5.5.读取数据填充Map for(int i=1;i<=count;i++) { ins.put(rsmd.getColumnLabel(i),rs.getString(i)); } } return ins; } finally { DBUtils.close(rs); DBUtils.close(pstm); } } protected final List<Map<String,String>> queryForList(String sql,Object...val)throws Exception { PreparedStatement pstm=null; ResultSet rs=null; try { pstm=DBUtils.prepareStatement(sql); int index=1; for(Object param:val) { pstm.setObject(index++, param); } rs=pstm.executeQuery(); //.获取描述对象 ResultSetMetaData rsmd=rs.getMetaData(); //.计算列数 int count=rsmd.getColumnCount(); //.计算初始容量 int size=((int)(count/0.75))+1; //.定义装载全部查询结果的List对象 List<Map<String,String>> rows=new ArrayList<>(); //.定义装载当前行数据的Map Map<String,String> ins=null; //.解析rs获取数据 while(rs.next()) { //.1.实例化装载当前行数据的HashMap ins=new HashMap<>(size); //.2.循环当前行数据的所有列,放入Map for(int i=1;i<=count;i++) { ins.put(rsmd.getColumnLabel(i), rs.getString(i)); } //.3.将代表当前行数据的Map放入List rows.add(ins); } //.返回数据 return rows; } finally { DBUtils.close(rs); DBUtils.close(pstm); } } }