JDBC【PreparedStatment、批处理、处理二进制、自动主键、调用存储函数】

太过爱你忘了你带给我的痛 2021-09-14 19:20 258阅读 0赞

# 1.PreparedStatement对象 #

**PreparedStatement对象继承Statement对象，它比Statement对象更强大，使用起来更简单**

1.  Statement对象编译SQL语句时，如果SQL语句有变量，就需要使用分隔符来隔开，如果变量非常多，就会使SQL变得非常复杂。**PreparedStatement可以使用占位符，简化sql的编写**
2.  Statement会频繁编译SQL。**PreparedStatement可对SQL进行预编译，提高效率，预编译的SQL存储在PreparedStatement对象中**
3.  **PreparedStatement防止SQL注入**。【Statement通过分隔符’++’,编写永等式，可以不需要密码就进入数据库】

//模拟查询id为2的信息
            String id = "2";
    
            Connection connection = UtilsDemo.getConnection();
    
            String sql = "SELECT * FROM users WHERE id = ?";
            PreparedStatement preparedStatement = connection.preparedStatement(sql);
    
            //第一个参数表示第几个占位符【也就是?号】，第二个参数表示值是多少
            preparedStatement.setString(1,id);
    
            ResultSet resultSet = preparedStatement.executeQuery();
    
            if (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }
    
            //释放资源
            UtilsDemo.release(connection, preparedStatement, resultSet);

--------------------

# 2.批处理 #

当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条发送执行，**采用批处理以提升执行效率**

批处理有两种方式：

1.  Statement
2.  PreparedStatement

**通过executeBath()方法批量处理执行SQL语句，返回一个int\[\]数组，该数组代表各句SQL的返回值**

以下代码是以Statement方式实现批处理

/* * Statement执行批处理 * * 优点： * 可以向数据库发送不同的SQL语句 * 缺点： * SQL没有预编译 * 仅参数不同的SQL，需要重复写多条SQL * */
            Connection connection = UtilsDemo.getConnection();
    
            Statement statement = connection.createStatement();
            String sql1 = "UPDATE users SET name='zhongfucheng' WHERE id='3'";
            String sql2 = "INSERT INTO users (id, name, password, email, birthday)" +
                    " VALUES('5','nihao','123','ss@qq.com','1995-12-1')";
    
            //将sql添加到批处理
            statement.addBatch(sql1);
            statement.addBatch(sql2);
    
            //执行批处理
            statement.executeBatch();
    
            //清空批处理的sql
            statement.clearBatch();
    
            UtilsDemo.release(connection, statement, null);

以下方式以PreparedStatement方式实现批处理

/* * PreparedStatement批处理 * 优点： * SQL语句预编译了 * 对于同一种类型的SQL语句，不用编写很多条 * 缺点： * 不能发送不同类型的SQL语句 * * */
            Connection connection = UtilsDemo.getConnection();
    
            String sql = "INSERT INTO test(id,name) VALUES (?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
    
            for (int i = 1; i <= 205; i++) {
                preparedStatement.setInt(1, i);
                preparedStatement.setString(2, (i + "zhongfucheng"));
    
                //添加到批处理中
                preparedStatement.addBatch();
    
                if (i %2 ==100) {
    
                    //执行批处理
                    preparedStatement.executeBatch();
    
                    //清空批处理【如果数据量太大，所有数据存入批处理，内存肯定溢出】
                    preparedStatement.clearBatch();
                }
    
            }
            //不是所有的%2==100，剩下的再执行一次批处理
            preparedStatement.executeBatch();
    
            //再清空
            preparedStatement.clearBatch();
    
            UtilsDemo.release(connection, preparedStatement, null);

--------------------

# 3.处理大文本和二进制数据 #

**clob和blob**

*  clob用于存储大文本
 *  blob用于存储二进制数据

--------------------

### MYSQL ###

**MySQL存储大文本是用Test【代替clob】**，Test又分为4类

*  TINYTEXT
 *  TEXT
 *  MEDIUMTEXT
 *  LONGTEXT

**同理blob也有这4类**

--------------------

下面用JDBC连接MySQL数据库去操作大文本数据和二进制数据

/* *用JDBC操作MySQL数据库去操作大文本数据 * *setCharacterStream(int parameterIndex,java.io.Reader reader,long length) *第二个参数接收的是一个流对象，因为大文本不应该用String来接收，String太大会导致内存溢出 *第三个参数接收的是文件的大小 * * */
    public class Demo5 { 
    
        @Test
        public void add() {
    
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
    
            try {
                connection = JdbcUtils.getConnection();
                String sql = "INSERT INTO test2 (bigTest) VALUES(?) ";
                preparedStatement = connection.prepareStatement(sql);
    
                //获取到文件的路径
                String path = Demo5.class.getClassLoader().getResource("BigTest").getPath();
                File file = new File(path);
                FileReader fileReader = new FileReader(file);
    
                //第三个参数，由于测试的Mysql版本过低，所以只能用int类型的。高版本的不需要进行强转
                preparedStatement.setCharacterStream(1, fileReader, (int) file.length());
    
                if (preparedStatement.executeUpdate() > 0) {
                    System.out.println("插入成功");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.release(connection, preparedStatement, null);
            }
    
    
        }
    
        /* * 读取大文本数据，通过ResultSet中的getCharacterStream()获取流对象数据 * * */
        @Test
        public void read() {
    
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
                connection = JdbcUtils.getConnection();
                String sql = "SELECT * FROM test2";
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
    
                if (resultSet.next()) {
    
                    Reader reader = resultSet.getCharacterStream("bigTest");
    
                    FileWriter fileWriter = new FileWriter("d:\\abc.txt");
                    char[] chars = new char[1024];
                    int len = 0;
                    while ((len = reader.read(chars)) != -1) {
                        fileWriter.write(chars, 0, len);
                        fileWriter.flush();
                    }
                    fileWriter.close();
                    reader.close();
    
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.release(connection, preparedStatement, resultSet);
            }
    
        }

--------------------

/* * 使用JDBC连接MYsql数据库操作二进制数据 * 如果我们要用数据库存储一个大视频的时候，数据库是存储不到的。 * 需要设置max_allowed_packet，一般我们不使用数据库去存储一个视频 * */
    public class Demo6 { 
    
        @Test
        public void add() {
    
    
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
    
    
            try {
                connection = JdbcUtils.getConnection();
                String sql = "INSERT INTO test3 (blobtest) VALUES(?)";
                preparedStatement = connection.prepareStatement(sql);
    
                //获取文件的路径和文件对象
                String path = Demo6.class.getClassLoader().getResource("1.wmv").getPath();
                File file = new File(path);
    
                //调用方法
                preparedStatement.setBinaryStream(1, new FileInputStream(path), (int)file.length());
    
                if (preparedStatement.executeUpdate() > 0) {
    
                    System.out.println("添加成功");
                }
    
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.release(connection, preparedStatement, null);
            }
    
        }
    
        @Test
        public void read() {
    
    
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
    
    
            try {
                connection = JdbcUtils.getConnection();
                String sql = "SELECT * FROM test3";
                preparedStatement = connection.prepareStatement(sql);
    
                resultSet = preparedStatement.executeQuery();
    
    
                //如果读取到数据，就把数据写到磁盘下
                if (resultSet.next()) {
                    InputStream inputStream = resultSet.getBinaryStream("blobtest");
                    FileOutputStream fileOutputStream = new FileOutputStream("d:\\aa.jpg");
    
                    int len = 0;
                    byte[] bytes = new byte[1024];
                    while ((len = inputStream.read(bytes)) > 0) {
    
                        fileOutputStream.write(bytes, 0, len);
    
                    }
                    fileOutputStream.close();
                    inputStream.close();
    
                }
    
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.release(connection, preparedStatement, null);
            }
    
        }

### Oracle ###

下面用JDBC连接Oracle数据库去操作大文本数据和二进制数据

//使用JDBC连接Oracle数据库操作二进制数据
    
    /* * 对于Oracle数据库和Mysql数据库是有所不同的。 * 1.Oracle定义了BLOB字段，但是这个字段不是真正地存储二进制数据 * 2.向这个字段存一个BLOB指针，获取到Oracle的BLOB对象,把二进制数据放到这个指针里面,指针指向BLOB字段 * 3.需要事务支持 * * */
    public class Demo7 { 
        @Test
        public void add() {
    
    
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
    
            try {
                connection = UtilsDemo.getConnection();
    
                //开启事务
                connection.setAutoCommit(false);
    
                //插入一个BLOB指针
                String sql = "insert into test4(id,image) values(?,empty_blob())";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, 1);
                preparedStatement.executeUpdate();
    
                //把BLOB指针查询出来,得到BLOB对象
                String sql2 = "select image from test4 where id= ? for update";
                preparedStatement = connection.prepareStatement(sql2);
                preparedStatement.setInt(1, 1);
                resultSet = preparedStatement.executeQuery();
    
                if (resultSet.next()) {
                    //得到Blob对象--当成是Oracle的Blob,不是JDBC的,所以要强转[导的是oracle.sql.BLOB包]
                    BLOB  blob = (BLOB) resultSet.getBlob("image");
    
                    //写入二进制数据
                    OutputStream outputStream = blob.getBinaryOutputStream();
    
                    //获取到读取文件读入流
                    InputStream inputStream = Demo7.class.getClassLoader().getResourceAsStream("01.jpg");
    
                    int len=0;
                    byte[] bytes = new byte[1024];
                    while ((len = inputStream.read(bytes)) > 0) {
    
                        outputStream.write(bytes, 0, len);
                    }
                    outputStream.close();
                    inputStream.close();
                    connection.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                UtilsDemo.release(connection, preparedStatement, null);
            }
    
        }
    
        @Test
        public void find() {
    
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
    
            try {
                connection = UtilsDemo.getConnection();
                String sql = "SELECT * FROM test4 WHERE id=1";
    
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
    
                if (resultSet.next()) {
    
                    //获取到BLOB对象
                    BLOB blob = (BLOB) resultSet.getBlob("image");
    
                    //将数据读取到磁盘上
                    InputStream inputStream = blob.getBinaryStream();
                    FileOutputStream fileOutputStream = new FileOutputStream("d:\\zhongfucheng.jpg");
                    int len=0;
                    byte[] bytes = new byte[1024];
    
                    while ((len = inputStream.read(bytes)) > 0) {
    
                        fileOutputStream.write(bytes, 0, len);
                    }
    
                    inputStream.close();
                    fileOutputStream.close();
    
                }
    
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                UtilsDemo.release(connection, preparedStatement, null);
            }
        }
    }

对于JDBC连接Oracle数据库操作CLOB数据,我就不再重复了,操作跟BLOB几乎相同

--------------------

# 4.获取数据库的自动主键列 #

### 为什么要获取数据库的自动主键列数据? ###

应用场景:

有一张老师表，一张学生表。**现在来了一个新的老师，学生要跟着新老师上课。**

我首先要知道老师的id编号是多少，学生才能知道跟着哪个老师学习【**学生外键参照老师主键**】。

--------------------

@Test
        public void test() {
    
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
    
            try {
                connection = JdbcUtils.getConnection();
    
                String sql = "INSERT INTO test(name) VALUES(?)";
                preparedStatement = connection.prepareStatement(sql);
    
                preparedStatement.setString(1, "ouzicheng");
    
                if (preparedStatement.executeUpdate() > 0) {
    
                    //获取到自动主键列的值
                    resultSet = preparedStatement.getGeneratedKeys();
    
                    if (resultSet.next()) {
                        int id = resultSet.getInt(1);
                        System.out.println(id);
                    }
                }
    
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.release(connection, preparedStatement, null);
            }

--------------------

# 5.调用数据库的存储过程 #

**调用存储过程的语法：**

{call <procedure-name>[(<arg1>,<arg2>, ...)]}

**调用函数的语法：**

{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

**如果是Output类型的，那么在JDBC调用的时候是要注册的**。如下代码所示：

/* jdbc调用存储过程 delimiter $$ CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam varchar(255)) BEGIN SELECT CONCAT('zyxw---', inputParam) into inOutParam; END $$ delimiter ; */
    //我们在JDBC调用存储过程,就像在调用方法一样
    public class Demo9 { 
    
        public static void main(String[] args) {
            Connection connection = null;
            CallableStatement callableStatement = null;
    
            try {
                connection = JdbcUtils.getConnection();
    
                callableStatement = connection.prepareCall("{call demoSp(?,?)}");
    
                callableStatement.setString(1, "nihaoa");
    
                //注册第2个参数,类型是VARCHAR
                callableStatement.registerOutParameter(2, Types.VARCHAR);
                callableStatement.execute();
    
                //获取传出参数[获取存储过程里的值]
                String result = callableStatement.getString(2);
                System.out.println(result);
    
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                try {
                    connection.close();
                    callableStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
    
        }

参考资料：

----------------------------------------------------------------------------------过程
    
    #修改mysql语句的结果符为//
    mysql > delimiter //
    
    #定义一个过程，获取users表总记录数，将10设置到变量count中
    create procedure simpleproc(out count int)
    begin
        select count(id) into count from users;
    end
    //
    
    #修改mysql语句的结果符为;
    mysql > delimiter ;
    
    #调用过程，将结果覆给变量a，@是定义变量的符号
    call simpleproc(@a);
    
    #显示变量a的值
    select @a;
    
    //以下是Java调用Mysql的过程
        String sql = "{call simpleproc(?)}";
        Connection conn = JdbcUtil.getConnection();
        CallableStatement cstmt = conn.prepareCall(sql);
        cstmt.registerOutParameter(1,Types.INTEGER);
        cstmt.execute();
        Integer count = cstmt.getInt(1);
        System.out.println("共有" + count + "人");
    
    ----------------------------------------------------------------------------------函数
    
    #修改mysql语句的结果符为//
    mysql > delimiter //
    
    #定义一个函数，完成字符串拼接
    create function hello( s char(20) ) returns char(50) 
    return concat('hello，',s,'!');
    //
    
    #修改mysql语句的结果符为;
    mysql > delimiter ;
    
    #调用函数
    select hello('world');
    
    //以下是Java调用Mysql的函数
        String sql = "{? = call hello(?)}";
        Connection conn = JdbcUtil.getConnection();
        CallableStatement cstmt = conn.prepareCall(sql);
        cstmt.registerOutParameter(1,Types.VARCHAR);
        cstmt.setString(2,"zhaojun");
        cstmt.execute();
        String value = cstmt.getString(1);
        System.out.println(value);
        JdbcUtil.close(cstmt);
        JdbcUtil.close(conn);

--------------------

> 如果文章有错的地方欢迎指正，大家互相交流。习惯在微信看技术文章的同学，**可以关注微信公众号:Java3y**