2024-9-4学习笔记规整【使用JDBC进行自接连查询、调用存储过程、事务、批处理】

最新推荐文章于 2024-10-08 15:23:06 发布

asdfggmj

最新推荐文章于 2024-10-08 15:23:06 发布

阅读量1.2k

点赞数 7

文章标签：学习笔记 java

本文链接：https://blog.csdn.net/asdfggmj/article/details/141910401

版权

自连接查询

先说说MySQL的表连接：应该都知道它的意思，就是利用A表与B表甚至更多的表去进行连接，每张表都有一列公共列，公共列是作为表连接的条件；理解了这个那么自连接就好理解了，就是一张表跟它自己进行一个连接

例如我有一张员工emp表：

表字段有员工编号empno，员工姓名ename，操作员job，领导编号mgr，入职日期hiredate，薪资sal，奖金comm，部门编号deptno

表与其他表之间的连接：

select * from emp,dept where emp.deptno=dept.deptno

表与自己连接：

select * from emp a,emp b where a.empno=b.mgr

注意：别名a和b是必须的，否则MySQL会区分不出来哪个是哪个：

Java使用JDBC进行连接查询：

        Connection con = null;
        PreparedStatement pst = null;
        String sql = "select * from emp yg,emp ld,dept where yg.empno=ld.mgr and yg.deptno=dept.deptno";
        try {
            //加载jdbc驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获取连接对象，与数据库建立连接
            con= DriverManager.getConnection("jdbc:mysql://localhost:3306/empDB?serverTimezone=UTC","mysql名","mysql密码");
            pst = con.prepareStatement(sql);
            //返回结果集
            ResultSet rs = pst.executeQuery();
            System.out.println("empno\tename\tjob\tmgrName\tsal\tdeptName");
            //循环输出内容
            while (rs.next()) {
                System.out.println(
                        rs.getInt("yg.empno")+"\t"+
                        rs.getString("ename")+"\t"+
                        rs.getString("job")+"\t"+
                        rs.getString("ld.ename")+"\t"+
                        rs.getDouble("sal")+"\t"+
                        rs.getString("dept.dname")
                );
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //关闭连接
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                pst.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

执行结果：

Connection	连接对象，用于与数据库取得连接
PreparedStatement	接口、预编译sql语句对象

调用存储过程

首先得先在Mysql定义一个存储过程

定义存储过程的基本语法：

CREATE Procedure proc_1(IN dno int,OUT eno int,OUT enam VARCHAR(20),OUT esal DOUBLE)
begin
select empno,ename,sal
INTO eno,enam,esal
from emp where deptno=dno ORDER BY sal ASC LIMIT 1;
end;

IN--输入的内容

OUT--输出的内容

INTO--放到输出内容对应位置

在Java中调用执行：

        System.out.print("输入部门编号：");
        Scanner scanner = new Scanner(System.in);
        int deptNo= scanner.nextInt();
        //定义连接对象
        Connection con = null;
        CallableStatement callableStatement = null;
        //1.准备SQL语句
        String sql = "{call proc_1(?,?,?,?)}";
        try {
            //2. 加载jdbc驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3. 获取连接对象，与数据库建立连接
            con= DriverManager.getConnection("jdbc:mysql://localhost:3306/empDB?serverTimezone=UTC","root","");
            //4.获取CallableStatement命令对象，发送存储过程sql语句
            callableStatement =con.prepareCall(sql);
            //5.如果输入参数，设置占位符的值
            callableStatement.setInt(1,deptNo);
            //6.如果输出参数，注册输出参数，指定输出参数类型
            callableStatement.registerOutParameter(2,java.sql.Types.INTEGER);
            callableStatement.registerOutParameter(3,java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(4,java.sql.Types.DOUBLE);
            //7.执行存储过程sql语句
            callableStatement.execute();
            //8.获取输出参数的值
            int eno=callableStatement.getInt(2);
            String ename=callableStatement.getString(3);
            double sal =callableStatement.getDouble(4);
            System.out.println("员工编号："+eno+"员工姓名："+ename+"员工薪资："+sal);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //关闭连接
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                callableStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

以上是根据输入的部门编号查询部门最低薪资的一个员工编号，姓名，薪资，所以在存储过程中使用了order by和limit ，当然可以改进更合理的判断，例如：没有部门编号给予提示，输入类型与其不符给予提示等

事务

事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中，事务是一组SQL语句的执行，它们被视为一个单独的工作单元。

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert、update、delete 语句

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

使用JavaJDBC进行模拟转账：

未执行之前：

//定义连接对象
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        //1.准备SQL语句
        String sql1="update bank set balance=balance-1000 where name='JJQ'";
        String sql2="update bank set balance=balance+1000 where name='STRING'";
        try {
            //2.加载jdbc驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3.获取连接对象，与数据库建立连接
            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/bankdb?serverTimezone=UTC","root","");
            //设置事务提交方式为手动提交
            connection.setAutoCommit(false);
            //4.获取PreparedStatement命令对象，发送sql语句
            preparedStatement=connection.prepareStatement(sql1);
            //5.执行sql语句
            int row1=preparedStatement.executeUpdate();
            //4.获取PreparedStatement命令对象，发送sql语句
            preparedStatement=connection.prepareStatement(sql2);
            //5.执行sql语句
            int row2=preparedStatement.executeUpdate();
            //如果两条sql语句都成功，提交事务
            if(row1>0 && row2>0){
                System.out.println("转账成功");
                connection.commit();
            }else{
                System.out.println("转账失败");
                connection.rollback();
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
//            throw new RuntimeException(e);
            System.out.println("转账失败");
            //抛出数据库异常回滚事务
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }finally {
            //关闭连接
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

执行成功后：

思路：修改两条sql语句，一条发起转账的，一条接收转账的，转账成功和失败都给予对应的提示，成功则提交事务双方数据发生改变，失败反回滚数据不发生改变

批处理

使用批处理的作用：大批量处理数据，提高效率。(减少程序和数据库的交互)

主要代码三件套：

      psmt.addBatch(); //添加批处理，数据放到批处理

      psmt.executeBatch(); //执行批处理

      psmt.clearBatch(); //清除批处理

以添加十万条数据进行测试

不使用批处理运行：

使用批处理运行之后：

上面两个形成了鲜明对比，使用了批处理要比未使用的快了一倍多的速度

全代码：

        Connection connection=null;
        PreparedStatement preparedStatement=null;
        //1.准备sql语句
        String sql="insert into TESTAA values(null,?,default)";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/empDB?serverTimezone=UTC","root","");
            //获取开始时间
            long start=System.currentTimeMillis();
            //设置事务提交为手动
            connection.setAutoCommit(false);
            for(int i=0;i<100000;i++){
                //获取PreparedStatement命令对象，发送sql语句
                preparedStatement=connection.prepareStatement(sql);
                //设置占位符的值，因为主键有自增了所以我把名字改成了i
                preparedStatement.setString(1,""+i);
//                int row = preparedStatement.executeUpdate();
                //添加到批处理
                preparedStatement.addBatch();
            }
            //一次性执行批处理的数据
            preparedStatement.executeUpdate();
            //提交事务
            connection.commit();
            //获取结束的时间
            long end=System.currentTimeMillis();
            //计算耗时
            System.out.println("执行时间："+(end-start)+"ms");
            //清除批处理
            preparedStatement.clearBatch();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }