【JavaWeb阶段学习】三步学会JDBC知识点

🥂(❁´◡`❁)您的点赞👍➕评论📝➕收藏⭐是作者创作的最大动力🤞

💖📕🎉🔥 支持我：点赞👍+收藏⭐️+留言📝

1、JDBC基础

JDBC数据库连接

JDBC(JAVA DataBase Connection) JAVA数据库连接，用JAVA语言操作数据库。

Java连接数据库：

• 导jar包：驱动；
• 加载驱动：Class.forName("类名")；
• 给出url，username，password；
• 使用DriverManager类得到Connection对象.

JAVA连接数据库：

public class Jdbc {
    @Test
    public void fun1() throws ClassNotFoundException, SQLException {
            /*
            * jdbc四大配置参数：
            * > driveClassname:com.mysql.jdbc.Driver
            * > (MySQL 8.0 以上: com.mysql.cj.jdbc.Driver)
            * > url:jdbc:mysql://localhost:3306/mydb1
            * > (MySQL 8.0 以上: jdbc:mysql://localhost:3306/mydb1?useSSL=false&
                                                             serverTimezone=UTC)
            * > username:root
            * > password:123
            */
            Class.forName("com.mysql.cj.jdbc.Driver"); //加载驱动类，注册驱动
            //com.mysql.cj.jdbc.Driver driver = new com.mysql.cj.jdbc.Driver();
            //DriverManager.registerDriver(driver);
        
            String url = "jdbc:mysql://localhost:3306/mydb1?useSSL=false&
                                                            serverTimezone=UTC";
            String username = "root";
            String password = "123";
        
            Connection con = DriverManager.getConnection(url, username, password);
            System.out.println(con);
    }
}

所有java.sql.Driver实现类都提供了static块，块内代码就是把自己注册到DriverManager中。在jdbc4.0之后，每个驱动jar包中，在 META-INF/services 目录下提供名为 java.sql.Driver 的文件，文件内容为该接口的实现类名称。

为了兼容4.0之前的mysql版本，Class.forName 语句不要省略。

JDBC实现增删改查

package ss273.jdbc.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.jupiter.api.Test;

public class Jdbc {
    // 增删改实现
    @Test
    public void fun1() throws ClassNotFoundException, SQLException {
        /* 一、得到Connection
         * jdbc四大配置参数：
         * > driveClassname:com.mysql.jdbc.Driver
         * > (MySQL 8.0 以上: com.mysql.cj.jdbc.Driver)
         * > url:jdbc:mysql://localhost:3306/mydb1
         * > (MySQL 8.0 以上: jdbc:mysql://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC)
         * > username:root
         * > password:123
         */

        // 准备四大参数
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "123";//123
        // 加载驱动类
        Class.forName(driverClassName); //加载驱动类，注册驱动
        // 使用DriverManager得到Connection
        Connection con = DriverManager.getConnection(url, username, password);
        System.out.println(con);

        /* 二、对数据库做增删改
         * 1.通过Connection对象创建Statement(向数据库发送SQL语句)
         * 2.调用int executeUpdate(String sql)，可发送DML,DDL
         */

        // 通过Connection对象创建Statement
        Statement stmt = con.createStatement();
        // 使用Statement发送SQL语句
        //String sql = "INSERT INTO student VALUES(NULL,'刘备')";
        //String sql = "UPDATE student SET sname='诸葛亮' WHERE sname='庞统'";
        String sql = "DELETE FROM stu_tea";
        int r = stmt.executeUpdate(sql);
        System.out.println(r);
    }
    // 查询实现
    @Test
    public void fun2() throws ClassNotFoundException, SQLException {
        /* 一、得到Connection
         * 二、得到Statement,发送select语句
         * 三、对查询返回的表格进行解析
         */

        // 一、得到Connection
        // 1.准备四大参数
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "123";
        // 加载驱动类
        Class.forName(driverClassName); //加载驱动类，注册驱动
        // 使用DriverManager得到Connection
        Connection con = DriverManager.getConnection(url, username, password);
        System.out.println(con);

        // 二、得到Statement,发送select语句
        // 1.得到Statement对象：Connection的createStatement()方法
        Statement stmt = con.createStatement();
        // 2.调用Statement的ResultSet rs=executeQuerry(String querySql)
        ResultSet rs = stmt.executeQuery("SELECT * FROM student");

        // 三、解析ResultSet
        // 1. 把行光标移动到第一行，可以调用next()方法完成（光标起始位置在BeforeFirst）
        while(rs.next()) {//光标移到下一行并判断是否存在
            int sid = rs.getInt(1); //通过列编号获取该列值
            String sname = rs.getString("sname"); //通过列名称获取该列值            
            System.out.println(sid + "," + sname);

            // 四、关闭资源（倒关）
            rs.close();
            stmt.close();
            con.close(); //必须关闭

        }
    }
}

JDBC代码规范化

package ss273.jdbc.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.jupiter.api.Test;

public class Jdbc {
    // 增删改实现
    @Test
    public void fun1() throws ClassNotFoundException, SQLException {
        /* 一、得到Connection
         * jdbc四大配置参数：
         * > driveClassname:com.mysql.jdbc.Driver
         * > (MySQL 8.0 以上: com.mysql.cj.jdbc.Driver)
         * > url:jdbc:mysql://localhost:3306/mydb1
         * > (MySQL 8.0 以上: jdbc:mysql://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC)
         * > username:root
         * > password:123
         */

        // 准备四大参数
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "123";
        // 加载驱动类
        Class.forName(driverClassName); //加载驱动类，注册驱动
        // 使用DriverManager得到Connection
        Connection con = DriverManager.getConnection(url, username, password);
        System.out.println(con);

        /* 二、对数据库做增删改
         * 1.通过Connection对象创建Statement(向数据库发送SQL语句)
         * 2.调用int executeUpdate(String sql)，可发送DML,DDL
         */

        // 通过Connection对象创建Statement
        Statement stmt = con.createStatement();
        // 使用Statement发送SQL语句
        //String sql = "INSERT INTO student VALUES(NULL,'刘备')";
        //String sql = "UPDATE student SET sname='诸葛亮' WHERE sname='庞统'";
        String sql = "DELETE FROM stu_tea";
        int r = stmt.executeUpdate(sql);
        System.out.println(r);
    }
    // 查询实现
    @Test
    public void fun2() throws ClassNotFoundException, SQLException {
        /* 一、得到Connection
         * 二、得到Statement,发送select语句
         * 三、对查询返回的表格进行解析
         */

        // 一、得到Connection
        // 1.准备四大参数
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "123";
        // 加载驱动类
        Class.forName(driverClassName); //加载驱动类，注册驱动
        // 使用DriverManager得到Connection
        Connection con = DriverManager.getConnection(url, username, password);
        System.out.println(con);

        // 二、得到Statement,发送select语句
        // 1.得到Statement对象：Connection的createStatement()方法
        Statement stmt = con.createStatement();
        // 2.调用Statement的ResultSet rs=executeQuerry(String querySql)
        ResultSet rs = stmt.executeQuery("SELECT * FROM student");

        // 三、解析ResultSet
        // 1. 把行光标移动到第一行，可以调用next()方法完成（光标起始位置在BeforeFirst）
        while(rs.next()) {//光标移到下一行并判断是否存在
            int sid = rs.getInt(1); //通过列编号获取该列值
            String sname = rs.getString("sname"); //通过列名称获取该列值            
            System.out.println(sid + "," + sname);
        }
        // 四、关闭资源（倒关）
        rs.close();
        stmt.close();
        con.close(); //必须关闭
    }
    //规范化
    public void fun3() throws Exception {

        Connection con = null;//定义引用
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 一、得到连接
            String driverClassName = "com.mysql.jbdc.Driver";
            String url = "jcdc:mysal://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC";
            String username = "root";
            String password = "123";
            Class.forName(driverClassName);
            con = DriverManager.getConnection(url);

            //二、创建statement
            stmt = con.createStatement();
            String sql = "select * from student";
            rs = stmt.executeQuery(sql);

            //三、循环遍历rs，打印其中数据
            while(rs.next()) {
                System.out.println(rs.getObject(1) + "," + rs.getObject(2));
            }

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭
            if(rs != null) rs.close();
            if(stmt != null) stmt.close();
            if(con != null) con.close();
        }
    }
}

2、结果集

2.1、结果集光标与元数据

( ResultSet 表示结果集，是二维表格，内部维护行光标（游标），Result提供了一系列方法移动游标：

• void beforeFirst():把游标放到第一行前面，是光标默认位置；
• void afterLast():把光标放到最后一行后面；
• boolean first():把光标放到第一行的位置上，返回值表示调控光标是否成功；
• boolean last():把光标放到最后一行的位置上；
• boolean isBeforeFirst():判断光标是否在第一行前；
• boolean isAfterLast():判断光标是否在最后一行后；
• boolean isFirst():光标位置是否在第一行；
• boolean isLast():光标位置是否在最后一行上；
• boolean previous():光标向上挪一行；
• boolean next():光标向下挪一行；
• boolean relative(int row):相对位移，当row为正数时表示向下移动row行，为负数时表示向上移动row行；
• boolean absolute(int row):绝对位移，把光标移动到指定行；
• int getRow():返回光标所有行。

获取结果集元数据：

• 得到元数据：rs.getMetaData();
• 获取结果集列数：int getColumnCount();
• 获取指定列的列名：String getColumnName(int colindex).

package ss273.jdbc.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.jupiter.api.Test;

public class Jdbc {
    // 增删改实现
    @Test
    public void fun1() throws ClassNotFoundException, SQLException {
        /* 一、得到Connection
         * jdbc四大配置参数：
         * > driveClassname:com.mysql.jdbc.Driver
         * > (MySQL 8.0 以上: com.mysql.cj.jdbc.Driver)
         * > url:jdbc:mysql://localhost:3306/mydb1
         * > (MySQL 8.0 以上: jdbc:mysql://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC)
         * > username:root
         * > password:123
         */

        // 准备四大参数
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "123";
        // 加载驱动类
        Class.forName(driverClassName); //加载驱动类，注册驱动
        // 使用DriverManager得到Connection
        Connection con = DriverManager.getConnection(url, username, password);
        System.out.println(con);

        /* 二、对数据库做增删改
         * 1.通过Connection对象创建Statement(向数据库发送SQL语句)
         * 2.调用int executeUpdate(String sql)，可发送DML,DDL
         */

        // 通过Connection对象创建Statement
        Statement stmt = con.createStatement();
        // 使用Statement发送SQL语句
        //String sql = "INSERT INTO student VALUES(NULL,'刘备')";
        //String sql = "UPDATE student SET sname='诸葛亮' WHERE sname='庞统'";
        String sql = "DELETE FROM stu_tea";
        int r = stmt.executeUpdate(sql);
        System.out.println(r);
    }
    // 查询实现
    @Test
    public void fun2() throws ClassNotFoundException, SQLException {
        /* 一、得到Connection
         * 二、得到Statement,发送select语句
         * 三、对查询返回的表格进行解析
         */

        // 一、得到Connection
        // 1.准备四大参数
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "123";
        // 加载驱动类
        Class.forName(driverClassName); //加载驱动类，注册驱动
        // 使用DriverManager得到Connection
        Connection con = DriverManager.getConnection(url, username, password);
        System.out.println(con);

        // 二、得到Statement,发送select语句
        // 1.得到Statement对象：Connection的createStatement()方法
        Statement stmt = con.createStatement();
        // 2.调用Statement的ResultSet rs=executeQuerry(String querySql)
        ResultSet rs = stmt.executeQuery("SELECT * FROM student");

        // 三、解析ResultSet
        // 1. 把行光标移动到第一行，可以调用next()方法完成（光标起始位置在BeforeFirst）
        while(rs.next()) {//光标移到下一行并判断是否存在
            int sid = rs.getInt(1); //通过列编号获取该列值
            String sname = rs.getString("sname"); //通过列名称获取该列值            
            System.out.println(sid + "," + sname);
        }
        // 四、关闭资源（倒关）
        rs.close();
        stmt.close();
        con.close(); //必须关闭
    }
    //规范化
    public void fun3() throws Exception {

        Connection con = null;//定义引用
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 一、得到连接
            String driverClassName = "com.mysql.jbdc.Driver";
            String url = "jcdc:mysal://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC";
            String username = "root";
            String password = "123";
            Class.forName(driverClassName);
            con = DriverManager.getConnection(url);

            //二、创建statement
            stmt = con.createStatement();
            String sql = "select * from student";
            rs = stmt.executeQuery(sql);

            //三、循环遍历rs，打印其中数据
            int count = rs.getMetaData().getColumnCount();
            while(rs.next()) {//遍历行
                for(int i = 1; i<=count; i++) {//遍历列
                    System.out.print(rs.getString(i));
                    if (i<count) {
                        System.out.print(",");
                    }
                }
                System.out.println();
            }

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭
            if(rs != null) rs.close();
            if(stmt != null) stmt.close();
            if(con != null) con.close();
        }
    }
}

结果集特性：

• 是否可滚动；
• 是否敏感；
• 是否可更新.

con.createStatement()生成的结果集不滚动/不敏感/不更新。

con.createStatement(int,int):

• 第一个参数： - Result.TYPE_FORWARD_ONLY:不滚动结果集；- Result.TYPE_SCOROLL_INSENSITIVE：滚动结果集，滚动结果集不会随数据库变化；- Result.TYPE_SCOROLL_SENSITIVE:滚动结果集，且滚动结果集随数据库变化（实际没有支持该功能的数据库）.

• 第二个参数： - CONCUR_READ_ONLY:结果是只读的，不能通过修改结果集影响数据库；- CONCUR_UPDATABLE:结果集可更新，对结果集的更新可反向影响数据库.

获取列数据：

• String getString(int columnIndex):获取指定列的String类型数据；
• int getInt(int columnIndex):获取指定列的int类型数据；
• double getDouble(int columnIndex):获取指定类的double类型数据；
• boolean getBoolean(int columnIndex):获取指定列的boolean类型数据；
• Object getObject(int columnIndex):获取指定列的Object类型数据

通过列名称获取列数据：

• String getString(int columnName):获取指定列的String类型数据；
• int getInt(int columnName):获取指定列的int类型数据；
• double getDouble(int columnName):获取指定类的double类型数据；
• boolean getBoolean(int columnName):获取指定列的boolean类型数据；
• Object getObject(int columnName):获取指定列的Object类型数

3、预处理

PreparedStatement:Statement接口的子接口。其强大之处在于：

• 防SQL攻击；
• 提高代码的可读性、可维护性；
• 提高效率。

如何获得PreparedStatement对象：

• 给出SQL模板；
• 调用Connection的PreparedStatement(String sql模板)；
• 调用pstmt的setXXX()系列方法为SQL模板中的？赋值；
• 调用pstmt的executeUpdate()或executeQuery()，但其方法都没有参数.

package ss273.jdbc.demo2;

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 org.junit.jupiter.api.Test;

public class Demo2 {
    /**
     * 登录，使用username和password查询数据
     * 若查出结果集返回true，否则返回false
     * @param username
     * @param password
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public boolean login(String username, String password) throws SQLException, ClassNotFoundException {
        /* 一、得到Connection
         * 二、得到statement
         * 三、得到ResultSet
         * 四、rs.next()返回值
         */
        // 准备四大参数
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC";
        String mysqlUsename = "root";
        String mysqlPassword = "123";
        //加载驱动类
        Class.forName(driverClassName);
        //得到connection
        Connection con = DriverManager.getConnection(url, mysqlUsename, mysqlPassword);
        //得到statement
        Statement stmt = con.createStatement();
        //给出SQL语句，调用stmt的executeQuery()，得到ResultSet
        String sql = "select * from t_user where username='"+username+"' and password='"+password+"'";
        ResultSet rs = stmt.executeQuery(sql);
        return rs.next();
    }
    @Test
    public void fun1() throws ClassNotFoundException, SQLException {
        String username = "a' or 'a'='a"; //结果为TRUE
        String password = "a' or 'a'='a";
        //select * from t_user where username='a' or 'a'='a' and password='a' or 'a'='a'
        boolean bool = login(username,password);
        System.out.println(bool);
    }

    public boolean login2(String username, String password) throws SQLException, ClassNotFoundException {
        /* 一、得到Connection
         * 二、得到statement
         * 三、得到ResultSet
         * 四、rs.next()返回值
         */
        // 准备四大参数
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/mydb1?useSSL=false&serverTimezone=UTC";
        String mysqlUsename = "root";
        String mysqlPassword = "123";
        //加载驱动类
        Class.forName(driverClassName);
        //得到connection
        Connection con = DriverManager.getConnection(url, mysqlUsename, mysqlPassword);

        /* 一、得到PreparedStatement
         * 1.给出SQL模板：所有参数用？代替
         * 2.调用connection方法，得到PreparedStatement
         */
        String sql = "select * from t_user where username=? and password=?";
        PreparedStatement pstmt = con.prepareStatement(sql);

        /* 二、为参数赋值
         *
         */
        pstmt.setString(1,username); //给第1个问号赋值，值为username
        pstmt.setString(2,password); //给第2个问号赋值，值为password

        ResultSet rs = pstmt.executeQuery(); //调用查询方法，向数据库发送查询语句
        return rs.next();
    }
    @Test
    public void fun2() throws ClassNotFoundException, SQLException {
        String username = "a' or 'a'='a"; //结果为false
        String password = "a' or 'a'='a";
        //select * from t_user where username='a' or 'a'='a' and password='a' or 'a'='a'
        boolean bool = login2(username,password);
        System.out.println(bool);
    }
}

预处理原理（mysql预处理默认关闭，需手动开启）：

• 服务器工作： - 校验SQL语句的语法；- 编译：一个与函数相似的东西；- 执行：调用函数；

• PreparedStatement: - 前提：连接的数据库必须支持预处理！（几乎没有不支持的）- 每个pstmt都与一个SQL模板绑定在一起，先把SQL模板给数据库，数据库先进行校验，再进行编译，执行时只是把参数传递过去；- 二次执行时就不用再次校验语法，也不用再次编译，直接执行。

4、sql查询继续巩固练习

JDBC最主要还是要写sql，如果上一篇文章没有练习够，这里又准备了十几条sql练习，继续巩固练习

1.查出至少有一个员工的部门，显示部门编号/部门名称/部门位置/部门人数

列：d* , z1.cnt

表：emp e, dept d -> z1

条件：e.deptno = z1.deptno

SELECT d*, z1.cnt 
FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno)z1
WHERE d.deptno = z1.deptno
;

2.列出所有员工的姓名及其直接上级的姓名

列：e.ename, m.ename

表：emp e, emp m

条件：e.mgr = m.empno

SELECT e.ename, IFNULL(m.ename, 'BOSS') leader
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr = m.empno

3.列于受雇日期早于直接上级的所有员工的编号/姓名/部门名称

列：e.empno, e.ename, d.dname

表：emp e, emp m, dept d

条件：e.mgr = m.empno, e.hiredate > m.hiredate, e.deptno=d.deptno

SELECT e.empno, e.ename, d.dname
FROM emp e, emp m, dept d
WHERE e.mgr = m.empno AND e.hiredate > m.hiredate AND e.deptno=d.deptno

4.列出部门名称和员工信息，以及没有员工的部门

列：*

表：emp e, dept d

条件：e.deptno = d.deptno

SELECT * 
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno

5.列出最低薪资大于15000的各种工作及从事这种工作的员工人数

列：job, count( * )

表：emp p

条件： min(sal)>15000

分组：job

SELECT job, COUNT(*)
FROM emp p
GROUP BY job
HAVING MIN(sal)>15000

6.列出在销售部工作的员工姓名，假定不知道销售部的部门编号

列：e.ename

表：emp

条件：e.deptno=(select deptno from dept where dname = '销售部')

SELECT * 
FROM emp p
WHERE e.deptno = (SELECT deptno FROM dept WHERE dname = '销售部')

7.列出薪资高于公司平均薪资的所有员工信息/所在部门名称/上级领导/工资等级

列：e.* , d.dname, m.ename, s.grade

表：emp e, emp m, dept d, salgrade s

条件：E.sal>(SELECT AVG(sal) from emp), e.deptno = d.deptno, e.mgr = m.empno, e.sal BETWEEN s.losal AND s.hisal

SELECT e.*, d.dname, m.ename, s.grade
FROM 
    emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno
          LEFT OUTER JOIN emp m ON e.mgr = m.empno
          LEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE     E.sal>(SELECT AVG(sal) from emp)        

8.列出与庞统从事相同工作的所有员工及部门名称

列：e.* , d.dname

表：emp e, dept d

条件：e.job=(SELECT job FROM emp WHERE ename = '庄周'), e.deptno = d.deptno

SELECT e.* d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND e.job=(SELECT job FROM emp WHERE ename = '庄周')

9.列出薪资高于部门30所有员工工资的员工姓名/薪资/部门名称

列：e.ename, e.sal, d.dname

表：emp e, dept d

条件：e.deptno = d.deptno, e.sal > ALL(SELECT sal FROM emp WHERE deptno=30)

SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND e.sal > ALL(SELECT sal FROM emp WHERE deptno=30)

10.列出年份/利润/年度增长比

SELECT y1.*, IFNULL(CONCAT((y1.zz-y2.zz)/y2.zz*100,'%'),'0%') 增长比
FROM tb_year y1 LEFT OUTER JOIN tb_year y2
ON y1.year = y2.year + 1