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();
	}
}

}