Java批量插入与获取自增ID-优快云博客

//-------------------
Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/bbs?user=root&password=****";
		Connection conn = DriverManager.getConnection(url);
//------------------------
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection("jdbc:odbc:dataBase")
//--------------------
Class.forName("oracle.jdbc.driver.OracleDriver");
		String url = "url=jdbc:oracle:thin:@localhost:1521:XE";
		Connection conn = DriverManager.getConnection(url,username,pwd);
//-----------------------

	//Batch;;;数据库，专门大量数插;;
	public void method3(){
		Connection conn = null;
		PreparedStatement stmt = null;
		DBHelper db = null;
		long start = System.currentTimeMillis();
		try {
			db = new DBHelper();
			conn = db.getConn();
			//jdbc默认自动提交;;;insert commit
			// 下面这句可别创建for
			conn.setAutoCommit(false);
			String sql = "insert into t_sal values(?,'kaka',?)";

			stmt = conn.prepareStatement(sql);
			for (int i = 1; i < 1000; i++) {
				stmt.setInt(1, i);
				stmt.setInt(2, i*100);
				stmt.addBatch();
				if(i % 100 == 0){
					stmt.executeBatch();
					conn.commit();
					stmt.clearBatch();
					
				}
				//stmt.executeUpdate();
			}
			//conn.commit();
			//提交方式，改原来自动提交,,一句一提交..
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			db.close(null, stmt, conn);
		}
		long end = System.currentTimeMillis();
		//ps bach一共花了578毫秒
		System.out.println("ps bach一共花了"+(end - start)+"毫秒");
			
	}

 //-----取出刚insert记录的自增id-----第一种方法...
    public int getInsert_id1(){
    	DBHelper db = new DBHelper();
    	Connection conn = db.getConn();
    	PreparedStatement pstmt_insert = null;
    	PreparedStatement pstmt_select = null;
    	ResultSet rs = null;
    	int row = 0;
    	
    	String insertSQL = "insert into pro_stu values(pro_stu_id.NEXTVAL,'tom','13912345678','重庆西永')";
    	//第二种方法...selectSQL = "select pro_stu_id.CURRVAL from dual";  
    	String selectSQL = "select max(stu_id) from pro_stu";
    	try{
        //执行插入语句...
    	pstmt_insert = conn.prepareStatement(insertSQL);
    	pstmt_insert.executeUpdate();
    	//执行查询语句...
    	pstmt_select = conn.prepareStatement(selectSQL);
    	rs = pstmt_select.executeQuery();
    	while(rs.next()){
    		row = rs.getInt(1);
    	}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}
    	db.close(rs, pstmt_select, null);
    	db.close(null, pstmt_insert, conn);
  
    	return row;
    }
    //----------取出刚insert记录通用方法.............
    public int getInsert_id2(){
    	DBHelper db = new DBHelper();
    	
    	Connection conn = db.getConn();
    	
    	PreparedStatement pstmt_insert = null;
    	//不曾见,,数据库封装对象;;知道..数据库版;
    	DatabaseMetaData dbmd = null;
    	ResultSet rs = null;
    	int row = 0;
    	
    	/*首先你要知道，你的jdbc是不是 3.0是否支持功能
		System.out.println("product name"+dmd.getDatabaseProductName());
		System.out.println("product version"+dmd.getDatabaseProductVersion());
		System.out.println("support generate keys?"+dmd.supportsGetGeneratedKeys());
    	*/
    	
    	String sql = "insert into pro_stu values" +
    			"(pro_stu_id.NEXTVAL,'jack','13812345678','广东深圳')";
    	try{
    		dbmd = conn.getMetaData();
    		
    	    pstmt_insert = conn.prepareStatement(sql,new String[]{"stu_id"});
    	    pstmt_insert.executeUpdate();
    	    
    	    if(dbmd.supportsGetGeneratedKeys()){   
    	    	rs = pstmt_insert.getGeneratedKeys();
    	    	if(rs.next()){
    	    		row = rs.getInt(1);
    	    	}
    	    }
    	}catch(SQLException e){
    		e.printStackTrace();
    	}
    	db.close(rs, pstmt_insert, conn);
    	return row;
    }

 //事务-----------------
    public void inTimeRun(){
    	DBHelper db = new DBHelper();
    	Connection conn = db.getConn();
    	PreparedStatement pstmt1 = null;
    	PreparedStatement pstmt2 = null;
    	try{
    	//取消自动提交更新设置为手动...
    	conn.setAutoCommit(false);
    	
    	String sql1 = "update pro_stu set stu_name = 'jarry' where stu_id = 6";
    	String sql2 = "delete from pro_stu where stu_id = 10";
    	
    	pstmt1 = conn.prepareStatement(sql1);
    	pstmt2 = conn.prepareStatement(sql2);
    	
    	pstmt1.executeUpdate();
    	pstmt2.executeUpdate();
    	
    	//System.out.println("正在更新...");
    	
    	conn.commit();
    	
    	
    	}catch(SQLException e){
    		e.printStackTrace();
    		try{
    			System.out.println("更新失败...已回滚....");
    			//设置回自动提交更新...
    	    	conn.rollback();
    		    conn.setAutoCommit(true);
    		}catch(SQLException e1){
    			e1.printStackTrace();
    		}
    	}
    	db.close(null, pstmt2, null);
    	db.close(null, pstmt1, conn);
    }

public class SaveFile {
	public void saveF(){
		DBHelper db = new DBHelper();
		Connection conn = db.getConn();
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement("insert into saveImg values(?,?)");
			
			pstmt.setString(1,"e:/西湖醋鱼.jpg");
			InputStream in = new FileInputStream("e:/西湖醋鱼.jpg");
			pstmt.setBinaryStream(2,in,in.available());
			pstmt.executeQuery();
			System.out.println("图片保存成功...");
		}catch(SQLException e){
			e.printStackTrace();
		}catch(FileNotFoundException e1){
			e1.printStackTrace();
		}catch(IOException e2){
			e2.printStackTrace();
		}
	}
	public void getF(){
		DBHelper db = new DBHelper();
		Connection conn = db.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = conn.prepareStatement("select * from saveImg where filename = ?");
			pstmt.setString(1,"e:/西湖醋鱼.jpg");
			rs = pstmt.executeQuery();
			rs.next();
			InputStream in = rs.getBinaryStream("filecontent");
			FileOutputStream fos = new FileOutputStream("e:/index.jpg");
			byte[] buff = new byte[1024];
			int length = 0;
			while((length = in.read(buff)) != -1){
				fos.write(buff,0,length);
				fos.flush();
			}
			fos.close();
			in.close();
			System.out.println("数据写入成功...");
		}catch(SQLException e){
			e.printStackTrace();
		}catch(FileNotFoundException e1){
			e1.printStackTrace();
		}catch(IOException e2){
			e2.printStackTrace();
		}
	}
    public static void main(String[] args){
    	SaveFile save = new SaveFile();
    	save.getF();
    }
}

1:在conf/context.xml中加入<resource>元素
<Resource name="OracleDataSource"   
             auth="Container"   
             type="javax.sql.DataSource"     
             maxActive= "10"     
             maxIdle= "2"     
             maxWait="5000"   
             username="scott"     
             password="tiger"     
             driverClassName="oracle.jdbc.driver.OracleDriver"     
             url="jdbc:oracle:thin:@localhost:1521:myOracle"/> 

2:
在web.xml中加入<resource-ref>元素
<resource-ref>元素表示在Web应用中引用JNDI资源
<resource-ref> 
<description>DB Connection</description> 
<res-ref-name>OracleDataSource</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth> 
</resource-ref> 

3:
由于数据源由Servlet容器创建并维护，所以必须把JDBC驱动程序拷贝到Tomcat安装目录下的lib目录下，确保Servlet容器能够访问驱动程序

4:
获得对数据源的引用：
	Context ctx = new InitialContext();
	DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/OracleDataSource");
获得数据库连接对象：
	Connection con = ds.getConnection();
返回数据库连接到连接池：
      con.close();

Java_jdbc