本文主要是介绍1_基础版JDBC-DBHelper扩展(事务及大数据操作)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
JDBC - 基础版DBHelper
/MyProperties.java/
public class MyProperties extends Properties{
private static MyProperties myproperties;
private MyProperties(){
InputStream iis = MyProperties.class.getClassLoader().getResourceAsStream(“db.properties”);
try{
super.load( iis );
}catch( IOException e ){
e.printStackTrace();
}
}
public static MyProperties getInstance(){if( myproperties == null ){myproperties = new MyProperties();}return myproperties;
}
}
/**************************************************************************/
/DBHelper.java/
public class DBHelper{
// 静态块 , 读取一次数据库驱动,
static{try{Class.forName( MyProperties.getInstance().getProperty("driver") );}catch( ClassNotFoundException e ){e.printStackTrace();}
}//获取数据库联接
public Connection getCon() throws SQLException {MyProperties mp = MyProperties.getInstance();Connection con = DriverManager.getConnection( mp.getProperty("url") , mp.getProperty ("username") , mp.getProperty("password") );return con;
}//查询
public List<Map<String , String >> findAll( String sql ,List<Object> params )throws SQLException{Connection con = getCon();PreparedStatement pstmt = con.prepareStatement( sql );setParams( pstmt , params );ResultSet rs = pstmt.executeQuery();ResultSetMetaData rsmd = rs.getMetaData(); //获取关于 ResultSet 对象中列的类型和属性信息的对象List<String> cnlist = new ArrayList<String>();for( int i = 1; i <= rsmd.getColumnCount() ; i++ ){cnlist.add( rsmd.getColumnName( i ) );}List<Map<String , String >> list = new ArrayList<Map<String ,String >>();while( rs.next() ){Map<String , String > map = new HashMap<String , String >();for( String cn :cnlist ){map.put( cn , rs.getString(cn) );}list.add( map );}closeAll(pstmt ,con ,rs );return list;
}//查询聚合函数
public double findDouble( String sql , List<Object> params ) throws SQLException{double result = 0;Connection con = getCon();PreparedStatement pstmt = con.prepareStatement( sql );setParams( pstmt ,params );ResultSet rs = pstmt.executeQuery();while( rs.next() ){rs.getDouble( 1 );}closeAll( pstmt ,con ,rs );return result;
}//封装增删改
public int doUpdate( String sql , List<Object> params ) throws SQLException{Connection con = getCon();PreparedStatement pstmt = con.prepareStatement( sql );setParams( pstmt ,params );int result = pstmt.executeUpdate();closeAll( pstmt ,con ,null );return result;
}//封装带事务的增删改
public void doUpdateTran( List<String> sqls ,List< List<Object> > listparams ){Connection con = getCon();PreparedStatement pstmt = null;con.setAutoCommit( false ); //关闭隐式事务( 一句SQL语句提交一次 )try{if( sqls != null && sqls.size() > 0 ){for( int i = 0; i < sqls.size() ; i++ ){String sql = sqls.get( i ); // 取出每一条sql语句.pstmt = con.prepareStatement( sql );setParams( pstmt , listparams( i ) );pstmt.executeUpdate();}}con.commit();}catch( Exception e ){if( con != null ){con.rollback; // 回滚}}finally{con.setAutoCommit( true ); //恢复隐式事务closeAll( pstmt ,con ,null );}}//大数据的操作 , 批处理// 集合嵌套集合.
public void doBigDataUpdate( String sql , List<List<Object>> params ) throws SQLException{Connection con = getCon();con.setAutoCommit( false ); PreparedStatement pstmt = null;try{pstmt = con.prepareStatement( sql );for( int i = 0; i < params.size() ; i++ ){List<Object> paramsList = params.get( i );setParams( pstmt , paramsList );pstmt.addBatch(); //添加一次预定义参数if( i % 1000 == 0 ){pstmt.executeBatch(); //批量执行预定义sql ,这个返回值里面存的是每条语句执行的结果}}pstmt.executeBatch(); // 多余的余数在执行.con.commit();}catch( Exception e){e.printStackTrace();con.rollback(); // 异常 ====>>>> 回滚。}finally{con.setAutoCommit( true );closeAll( pstmt ,con ,null );}
}//设置参数 占位符
private void setParams( PreparedStatement pstmt , List<Object> params ) throws SQLException{if( params != null && params.size() > 0 ){for( int i = 1; i <= params.size() ; i++ ){pstmt.setObject( i , params.get( i-1 ));}}
}// 释放资源
private sttaic void clossAll( PreparedStatement pstmt , Connection con , ResultSet rs ){if( pstmt != null ){pstmt.close();}if( con !=null ){con.close();}if( rs != null ){rs.close();}
}
}
这篇关于1_基础版JDBC-DBHelper扩展(事务及大数据操作)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
