JDBC操作数据库的高性能方式

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的配置信息,省略