使用conn.createStatement()获取Statement;
使用Statement.addBatch方法,执行批处理statement.executeBatch();
Statement.addBatch拥有更好的性能
package com.baidu;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class BatchTest {
public static void main(String[] args) {
Connection conn =null;
try {
conn = JdbcUtils.getConn();
Statement statement = conn.createStatement();
statement.addBatch("insert into t_user VALUES(111,'Jay',30)");
statement.addBatch("insert into t_user VALUES(222,'Jay',30)");
statement.addBatch("insert into t_user VALUES(null ,'Jay',30)");
statement.addBatch("insert into t_user VALUES(444,'Jay',30)");
statement.executeBatch();
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.close(null,null,conn);
}
}
}
conn.setAutoCommit(false);
使用conn.prepareStatement()获取PreparedStatement ps; 使用ps.executeUpdate()执行多条语句,最后conn.commit();
package com.baidu;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
public class test {
public static void main(String[] args) throws SQLException {
Connection conn = JdbcUtils.getConn();
conn.setAutoCommit(false);
PreparedStatement ps = null;
try {
//ps执行多条语句 只能用同一个ps
ps = conn.prepareStatement("insert into t_user VALUES(222,'Jay',30)");
ps.executeUpdate("insert into t_user VALUES(222,'Jay',30)");
ps.executeUpdate("insert into t_user VALUES(null,'Jay',30)");
ps.executeUpdate("insert into t_user VALUES(null,'Jay',30)");
conn.commit();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
JdbcUtils.close(null,ps,conn);
}
}
JdbcUtils
package com.baidu;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JdbcUtils {
public static String driverer = "com.mysql.jdbc.Driver";
public static String url = "jdbc:mysql://127.0.0.1:3306/ssm";
public static String user = "root";
public static String password = "123456";
//有try catch 方便重复使用定位 全局静态
static Connection conn;
static PreparedStatement ps;
static ResultSet rs;
//1.加载驱动
static {
try {
Class.forName(driverer);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* DriverManager.getConnection(url, user, password);
* @return Connection
*/
public static Connection getConn() {
//等于null的目的是因为 添加的trycatch后 无法在return Connection
try {
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return conn;
}
//最后关闭链接
/**
* Connection PreparedStatement ResultSet 的close()
* @param rs
* @param ps
* @param conn
*/
public static void close(ResultSet rs, PreparedStatement ps,Connection conn){
if(rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(ps != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//通用设置参数的方法 设置参数需要调用 PreparedStatement
/**
* 通用设置参数的方法 设置参数需要调用
* 1.判断有无params 或params为空
* 2.循环取值 执行PreparedStatement的setObject(i+1,i)
* 注意setObject从1开始,Object[]从0开始
* @param ps
* @param params
*/
public static void setParams(PreparedStatement ps,Object[]params){
//没有设置参数, 或则参数为空就方法结束先判断 有无在查看值是否为空字符串
if(params == null || params.length == 0 )
return;
//否则 setObject循环设置参数,参数是从1开始 , 而数字从0开始
for (int i = 0; i < params.length; i++) {
try {
ps.setObject(i+1,params[i]);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//通用增 删 改 ,
/**
* * 通用的查询方法 select ? from ? where ?
* 1.getConn()已经执行 Class.forName() getConnection()
* 2.prepareStatement(需要传递参数)
* 3.调用自己的 传参方法setParams(ps,params)
* 4.执行 executeUpdate 返回值为int(受影响行数)
* 5.关闭 ps,conn
* 6.返回受影响行数
* @param sql
* @param params
* @return
*/
public static int myUpdate(String sql,Object [] params){
try {
conn = getConn();
ps = conn.prepareStatement(sql);
setParams(ps,params);
int i = ps.executeUpdate();
return i;
} catch (SQLException throwables) {
throwables.printStackTrace();
return -1;
}finally {
close(null,ps,conn);
}
}
/**
* 通用的查询方法 select ? from ? where ?
* 1.getConn()已经执行 Class.forName() getConnection()
* 2.prepareStatement(需要传递参数)
* 3.调用自己的 传参方法setParams(ps,params)
* 4.执行 executeQuery 返回值为ResultSet
* 5.获取ResultSet的列属性metaData=getMetaData()
* 6.获取ResultSet的列数 columnCount = getColumnCount()
* 7.获取ResultSet下一列的值 Object = getObject()
* 8.用List<List> 包装 6-7步
* 9.返回List<List>
* 10.关闭ResultSet、PreparedStatement、Connection
* @param sql
* @param params
* @return
*/
public static List<List> MyQuery(String sql, Object [] params){
//rs.getMetaData获取行数使用
// ResultSetMetaData metaData =null;
List<List> tableList = new ArrayList<List>();
try {
conn = getConn();
ps = conn.prepareStatement(sql);
setParams(ps,params);
rs = ps.executeQuery();
//Retrieves the number, types and properties of this Resultsetobject's columns.
//检索这个Resultseobject列的数目、类型和属性。
ResultSetMetaData metaData = rs.getMetaData();
//Returns the number of columns in thisResultset object.
//返回这个Resultset对象中的列数。<List>
int columnCount = metaData.getColumnCount();
while (rs.next()){
List rowList=new ArrayList();
for (int i = 0; i < columnCount; i++) {
//获取单元格的数据
//此方法将将给定列的值作为Java对象返回
rowList.add(rs.getObject(i+1));
}
tableList.add(rowList);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
close(null,ps,conn);
}
return tableList;
}
}