JavaWeb学习心得之JDBC批处理

一、Statement批处理

步骤

1. statement.addBatch(sql)
2. statement.executeBatch()
3. statement.clearBatch()

范例

1.创建表

  create table testbatch (      id int primary key,      name varchar(20) );

2.测试代码

import java.sql.Connection;import java.sql.Statement;public class JDBCDemo01 {public static void main(String[] args) {Connection conn = null;Statement st = null;try{conn = JdbcUtils.getConnection();st = conn.createStatement();String sql1 = "insert into testbatch(id,name) values(1,'aaa')";String sql2 = "insert into testbatch(id,name) values(2,'bbb')";String sql3 = "insert into testbatch(id,name) values(3,'ccc')";String sql4 = "insert into testbatch(id,name) values(4,'ddd')";String sql5 = "insert into testbatch(id,name) values(5,'eee')";//添加比处理语句st.addBatch(sql1);st.addBatch(sql2);st.addBatch(sql3);st.addBatch(sql4);st.addBatch(sql5);//返回处理结果数组int[] result = st.executeBatch();int count = 0;for(int i=0;i<result.length;i++){if(result[i]>0){count++;}}System.out.println("插入"+count+"条数据");//清空批处理语句st.clearBatch();}catch(Exception e){e.printStackTrace();}finally {JdbcUtils.release(conn, st, null);}}}

        statment可以向数据库发送不同的SQL语句，但是没有预编译，效率较低。

二、PreparedStatement批处理

范例

import java.sql.Connection;import java.sql.PreparedStatement;public class JDBCDemo02 {public static void main(String[] args) {long start = System.currentTimeMillis();Connection conn = null;PreparedStatement st = null;try{conn = JdbcUtils.getConnection();String sql = "insert into testbatch(id,name) values(?,?)";st = conn.prepareStatement(sql);for(int i=0;i<100000;i++){st.setInt(1, i);st.setString(2, "hanxin"+i);st.addBatch();if(i%1000==0){st.executeBatch();st.clearBatch();}}st.executeBatch();st.clearBatch();}catch(Exception e){e.printStackTrace();}finally {JdbcUtils.release(conn, st, null);}long end = System.currentTimeMillis();System.out.println("程序花费时间："+(end-start));}}

       preparedStatment发送预编译后的SQL代码，效率高，但是只能发送相同语句不同参数的SQL。

三、MySql自动生成主键

数据库：

 create table test1(      id int primary key auto_increment,     name varchar(20) );

测试代码：

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import com.mysql.jdbc.Statement;public class JDBCDemo03 {public static void main(String[] args) {Connection conn = null;PreparedStatement st = null;ResultSet rs = null;try{conn = JdbcUtils.getConnection();String sql1 = "insert into test1(name) values(?)";//添加Statement.RETURN_GENERATED_KEYS,否则报错st = conn.prepareStatement(sql1,Statement.RETURN_GENERATED_KEYS);st.setString(1, "阿里");st.executeUpdate();rs = st.getGeneratedKeys();if(rs.next()){System.out.println(rs.getInt(1));}}catch(Exception e){e.printStackTrace();}finally {JdbcUtils.release(conn, st, rs);}}}