JDBC操作数据库的一种套路
preparedStatement
+批处理+手动事务提交(速度快,安全)
工具类
package jdbc;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* 练习编写读取配置文件方式来使用jdbc连接,操作数据库的工具类 v1.0
*
* @author admin
*/
public class PropertiesUtilExercise {
//Declare Connection variable and PreparedStatement variable which will be used in Database operation
private static Connection conn=null;
private static String url;
private static String user;
private static String password;
//Define a static code block to initialize the database link
static{
try {
//Build an <code>InputStream</code> instance by using reflection.
InputStream is=PropertiesUtilExercise.class.getClassLoader().getResourceAsStream("jdbc/DBConfig.properties");
Properties properties=new Properties();
properties.load(is);
String database=properties.getProperty("database");
String driver=properties.getProperty(database+".driverClass");
Class.forName(driver);
url=properties.getProperty(database+".url");
user=properties.getProperty(database+".username");
password=properties.getProperty(database+".password");
} catch (Exception e) {
e.printStackTrace();
System.out.println("不能建立数据库链接");
}
}
/**
* 返回数据库连接对象
* @return Connection
*/
public static Connection getConnection(){
try{
conn=DriverManager.getConnection(url, user, password);
}catch(Exception e){
e.printStackTrace();
System.out.println("数据库链接对象实例化失败");
}
return conn;
}
/**
* 返回preparedStatement实例
* @return PreparedStatement
*/
public static PreparedStatement getPreparedStatement(Connection conn,String sql){
PreparedStatement psm=null;
try{
psm=conn.prepareStatement(sql);
}catch(Exception e){
e.printStackTrace();
System.out.println("获取preparedStatement实例失败");
}
return psm;
}
/**
* 返回Statement实例
*
* @return Statement
*/
public static Statement getStatement(Connection conn){
Statement state=null;
try{
state=conn.createStatement();
}catch(SQLException e){
e.printStackTrace();
System.out.println("Statement创建失败");
}
return state;
}
/**
* 关闭statement
*/
public static void closeStatement(Statement state){
try {
if(state!=null)
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 关闭connection
*/
public static void closeConnection(Connection conn){
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 关闭ResultSet
*/
public static void closeResultSet(ResultSet rs){
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 关闭全部
*/
public static void closeAll(Connection conn, Statement state, ResultSet rs){
try{
if(rs!=null)
rs.close();
if(state!=null)
state.close();
if(conn!=null)
conn.close();
}catch(SQLException e){
e.printStackTrace();
System.out.println("关闭失败");
}
}
}
测试类
package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
public class PropertiesUtilTest {
private static Connection conn=null;
public static void main(String[] args) throws InterruptedException{
long startTime=new Date().getTime();
insertStuByStatement("玛丽",21,1000);//自动提交事务:13.1s,手动提交事务:0.8s
long endTime=new Date().getTime();
System.out.println("Statement执行时间"+(endTime-startTime));
long startTime1=new Date().getTime();
insertStuByPreparedStatement("玛丽",21,1000);//自动提交事务:12.5s, 手动提交事务:0.2s
long endTime1=new Date().getTime();
System.out.println("preparedStatement执行时间"+(endTime1-startTime1));
}
public static void insertStuByStatement(String name, int age, int range){
String sql ="INSERT INTO student(name, age) VALUES ('"+name+"',"+age+");";
Statement sm=null;
try {
conn=PropertiesUtilExercise.getConnection();
//关闭事务自动提交
conn.setAutoCommit(false);
sm=PropertiesUtilExercise.getStatement(conn);
//添加到批处理
for(int i=0;i<range;i++){
sm.addBatch(sql);
}
//执行批处理
sm.executeBatch();
//手动提交事务
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}finally{
PropertiesUtilExercise.closeAll(conn, sm, null);
}
}
public static void insertStuByPreparedStatement(String name, int age, int range){
String sql ="INSERT INTO student(name, age) VALUES (?,?);";
PreparedStatement psm=null;
try {
conn=PropertiesUtilExercise.getConnection();
//关闭事务自动提交
conn.setAutoCommit(false);
psm=PropertiesUtilExercise.getPreparedStatement(conn, sql);
for(int i=0;i<range;i++){
psm.setString(1, "玛丽");
psm.setInt(2, i);
psm.addBatch();
}
psm.executeBatch();
//手动提交事务
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}finally{
PropertiesUtilExercise.closeAll(conn, psm, null);
}
}
}
配置文件
##这是注释 创建这个配置文件的目的是方便以后更换数据库
#用来标识现在使用的数据库,可以是mysql,也可以是其他数据库
database=mysql
#mysql的配置信息
mysql.driverClass=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://127.0.0.1:3306/JAVAEETest
mysql.username=root
mysql.password=123456
#oracle的配置信息,省略