批处理:
1)想发送多条SQL,又要降低与数据库系统的交互,这时使用批处理2)Statement对象:适合对不同结构的SQL做批处理操作
3)PreparedStatement对象:适合对相同结构的SQL做批处理操作
采用PreparedStatement.addBatch()实现批处理
优点:发送的是预编译后的SQL语句,执行效率高。缺点:只能应用在SQL语句相同,但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数 据,或批量更新表的数据。
案例:
//Statement和PreparedStatment的批处理
public class Demo3 {
public static void statementBatch() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String insertSQL = "insert into user(username,password,birthday,salary) values('jack','000111','2011-10-26',5000)";
String updateSQL = "update user set username='杰克' where username='jack'";
try {
conn = JdbcUtil.getMySqlConnection();
stmt = conn.createStatement();
//将需要执行的多条命令加入到批对象中
stmt.addBatch(insertSQL);
stmt.addBatch(updateSQL);
//一次性发送批对象到数据库端执行,返回每条SQL的结果
int[] is = stmt.executeBatch();
//将批对象清空
stmt.clearBatch();
//显示结果
System.out.println(is[0]+":"+is[1]);
} catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtil.close(rs);
JdbcUtil.close(stmt);
JdbcUtil.close(conn);
}
}
public static void preparedBatch() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String insertSQL = "insert into user(username,password,birthday,salary) values(?,?,?,?)";
try {
conn = JdbcUtil.getMySqlConnection();
pstmt = conn.prepareStatement(insertSQL);
long begin = System.currentTimeMillis();
for(int i=1;i<=1000;i++){
pstmt.setString(1,"jack"+i);
pstmt.setString(2,"111111");
pstmt.setDate(3,new java.sql.Date(12345));
pstmt.setFloat(4,5000);
//加入到批对象中
pstmt.addBatch();
if(i%100==0){
//执行批对象
pstmt.executeBatch();
//清空批对象
pstmt.clearBatch();
}
}
//执行批对象
pstmt.executeBatch();
//清空批对象
pstmt.clearBatch();
long end = System.currentTimeMillis();
System.out.println((end-begin)/1000+"秒");
} catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
}
}
public static void main(String[] args) {
//statementBatch();
preparedBatch();
}
}
获取数据库主键值
1)当需要获取刚插入主键信息的时候,需要使用获取主键值方法2)关键代码:
pstmt = conn.prepareStatement(sqlA,Statement.RETURN_GENERATED_KEYS);
rs = pstmt.getGeneratedKeys();
if(rs.next()){
Long temp = (Long) rs.getObject(1);
pid = temp.intValue();
}
public class Dao {
public void save(Person p) throws SQLException {
//思路
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
conn = JdbcUtil.getMySqlConnection();
String sqlA = "insert into person(name) values(?)";
String sqlB = "insert into card(location,pid) values(?,?)";
//NO1,向person表插入一条记录
pstmt = conn.prepareStatement(sqlA,Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1,p.getName());
pstmt.executeUpdate();
int pid = 0;
//NO2,取得插入记录的主键值
rs = pstmt.getGeneratedKeys();
if(rs.next()){
Long temp = (Long) rs.getObject(1);
pid = temp.intValue();
}
//NO3,向card表插入一条记录
pstmt = conn.prepareStatement(sqlB);
pstmt.setString(1,p.getCard().getLocation());
pstmt.setInt(2,pid);
pstmt.executeUpdate();
//非空关闭流
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
}
}