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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 1.PreparedStatement对象PreparedStatement对象继承Statement对象,它比Statement对象更强大,使用起来更简单Statement对象编译SQL语句时,如果SQL语句有变量,就需要使用分隔符来隔开,如果变量非常多,就会使SQL变得非常复杂。

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

更多的文章可往: 文章的目录导航
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
存储 SQL 数据库
面试题20: 存储过程和函数的区别
面试题20: 存储过程和函数的区别
|
2月前
|
存储 数据库
MySQL-函数和存储过程区别
MySQL-函数和存储过程区别
40 0
|
6月前
|
存储 SQL Oracle
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
55 0
|
6月前
|
存储 SQL 关系型数据库
MySQL存储过程与函数精讲
MySQL从5.0版本开始支持存储过程和函数。存储过程和函数能够将复杂的SQL逻辑封装在一起,应用程序无须关注存储过程和函数内部复杂的SQL逻辑,而只需要简单地调用存储过程和函数即可。
66 0
|
25天前
|
存储 SQL 关系型数据库
MySQL技能完整学习列表7、存储过程和函数——1、存储过程(Stored Procedures)的创建和执行——2、函数(Functions)的创建和使用
MySQL技能完整学习列表7、存储过程和函数——1、存储过程(Stored Procedures)的创建和执行——2、函数(Functions)的创建和使用
27 0
|
2月前
|
存储 Java 数据库
JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数(二)
JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数
27 0
|
2天前
|
存储 SQL 关系型数据库
Msql第四天,存储过程和函数
Msql第四天,存储过程和函数
24 0
Msql第四天,存储过程和函数
|
6天前
|
存储 数据库
【数据库】分支与循环&函数&存储过程
【数据库】分支与循环&函数&存储过程
14 1
|
2月前
|
存储 SQL Java
JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数(一)
JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数
26 0
|
2月前
|
存储 SQL 安全
函数(Function)和存储过程(Stored Procedure)的区别(小白情感版)
函数(Function)和存储过程(Stored Procedure)的区别(小白情感版)
13 0