【JDBC-2】JDBCUtils，Blob字段的操作

package JDBC.util;

import day01.ConnectionTest;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    //获取数据库连接
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        //1.读取基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros=new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //2.加载驱动
        Class.forName(driverClass);
        //3.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }
    //关闭数据库连接
    public static void CloseResource(Connection connection, PreparedStatement ps)
    {
        try {
            if(ps!=null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(connection!=null)
                connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //关闭数据库连接
    public static void CloseResource(Connection connection, PreparedStatement ps, ResultSet rs)
    {
        try {
            if(ps!=null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(connection!=null)
                connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(rs!=null)
                rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

package JDBCBlob;

import JDBC.util.JDBCUtils;
import org.junit.Test;

import java.io.*;
import java.sql.*;

/**
 * 使用PreparedStatement操作Blob类型的数据
 * PreparedStatement可以实现Blob数据的操作，而Statement不可以
 * PreparedStatement可以实现更高效的批量操作
 */
public class BlobTest {
//向customers表中插入Blob类型的字段
    @Test
    public void testInsert() throws SQLException, IOException, ClassNotFoundException {
        Connection connection = JDBCUtils.getConnection();
        String sql="Insert into customers(name,email,birth,photo)values (?,?,?,?)";

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setObject(1,"我妻善逸");
        ps.setObject(2,"shanyi@163.com");
        ps.setObject(3,"2003-08-09");
        FileInputStream is=new FileInputStream(new File("C:\\Users\\haiyanguo\\Desktop\\绘画\\getImage.jpg"));
        ps.setBlob(4,is);

        ps.execute();
        JDBCUtils.CloseResource(connection,ps);
    }

    //查询customers表格中的Blob字段
    @Test
    public void testQuery() throws SQLException, IOException, ClassNotFoundException {
        Connection connection = JDBCUtils.getConnection();
        String sql="select photo from customers where id = ?";
        InputStream is = null;
        FileOutputStream fos= null;
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1,21);
        ResultSet rs = ps.executeQuery();
        Blob photo = rs.getBlob(1);
        is=photo.getBinaryStream();
        fos= new FileOutputStream("C:\\Users\\haiyanguo\\Desktop\\绘画\\getImage1.jpg");
        byte[] buffer= new byte[1024];
        int len;
        while((len=is.read(buffer))!=-1){
            fos.write(buffer,0,len);
        }


        JDBCUtils.CloseResource(connection,ps,rs);
        is.close();
        fos.close();
    }
}

package JDBCBlob;

import JDBC.util.JDBCUtils;
import org.junit.Test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 使用PreparedStatement实现批量数据的操作
 * update,delete本身就具有批量操作的效果
 * insert
 * 此时的批量操作，主要指的是批量插入
 * 使用PreparedStatement实现批量数据插入（高效）的操作?
 * Statement会有SQL注入的问题，PS有预编译，执行代码被缓存下来
 *
 * 题目：
 * create table goods(
 *      id int primary key auto_increment,
 *      name varchar(25)
 * );
 * 方式一：statement
 * Connection conn = JDBCUtils.getConnection();
 * Statement statement = conn.createStatement();
 * for(int i = 1; i<=20000 ; i++){
 *      String sql = "insert into goods(name) values('name_" + i + "');
 *      st.execute(sql);
 * }
 */
public class InsertTest {

    //批量插入的方式2：使用PreparedStatement进行插入操作
    @Test
    public void testInsert1(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql="Insert into goods (name) values (?)";
            ps = connection.prepareStatement(sql);
            for(int i=1;i<=20000;i++){
                ps.setObject(1,"name_"+i);
                ps.execute();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.CloseResource(connection,ps);
        }
    }

    //批量插入的方式3：
    /*
    mysql默认条件下是不支持批处理的，需要通过一个参数来开启批处理的支持
    ?rewriteBatchedStatements=true 写在配置文件url的后面
    使用更新的mysql驱动：mysql-connector-java-5.1.37-bin.jar

     */
    public void testInsert2(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql="Insert into goods (name) values (?)";
            ps = connection.prepareStatement(sql);
            for(int i=1;i<=20000;i++){
                ps.setObject(1,"name_"+i);

                ps.addBatch();
                if(i%500==0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
                ps.execute();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.CloseResource(connection,ps);
        }
    }

//终极版：
    public void testInsert3(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = JDBCUtils.getConnection();
            //设置不允许自动提交数据
            connection.setAutoCommit(false);
            String sql="Insert into goods (name) values (?)";
            ps = connection.prepareStatement(sql);
            for(int i=1;i<=20000;i++){
                ps.setObject(1,"name_"+i);

                ps.addBatch();
                if(i%500==0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
                ps.execute();
            }
            connection.commit();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.CloseResource(connection,ps);
        }
    }
}