klenq's blog HomeArchivesTagsCategories
JDBC 学习笔记
|Notes

JDBC

Java Database Connectivity

JDBC

JDBC的本质:

是SUN公司制定的一套接口

接口都有调用者和实现者, 面向接口调用, 面向借口写实现类, 这都属于面向接口编程.

  • 解耦合

  • 多态机制就是典型的面向抽象编程, 而不是面向具体编程

    • 建议

      Animal a = new Cat();

    • 不建议

      Cat c = new Cat();

image-20211025154934161

程序模拟JDBC本质

JDBC编程六步:

  1. 注册驱动
  2. 获取连接
  3. 获取数据库操作对象
  4. 执行SQL语句
  5. 处理查询结果集 (只有第四步使用的是select语句时才有这一步)
  6. 释放资源 (使用完之后一定要关闭)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
import java.sql.*;
import java.util.ResourceBundle;

public class JDBCTest1 {


    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;

        try {
            //注册驱动
            Driver driver = new com.mysql.jdbc.Driver();
            DriverManager.registerDriver(driver);

            //获取连接
            ResourceBundle resourceBundle = ResourceBundle.getBundle("jdbc");
            String url = resourceBundle.getString("url");
            String user = resourceBundle.getString("user");
            String password = resourceBundle.getString("password");

            connection = DriverManager.getConnection(url,user,password);
            System.out.println(connection);

            //创建数据库操作对象
            statement = connection.createStatement();

            //查询语句
            String sql = "insert into dept(deptno, dname, loc) value(50,'人事部', '北京')";
            //专门执行DML语句的
            int count = statement.executeUpdate(sql);
            System.out.println("");

            //处理返回数据

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放资源
            try {
                if(statement!=null){
                    statement.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
            //释放资源
            try {
                if(connection!=null){
                    connection.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        }




    }

}

注册驱动的另一种方式– 使用反射机制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
import java.sql.*;
import java.util.ResourceBundle;

public class JDBCTest3 {
    public static void main(String[] args) {


        Connection connection = null;
        Statement statement = null;
        try {
            //注册驱动的第一种写法
            Driver driver = new com.mysql.jdbc.Driver();
            DriverManager.registerDriver(driver);

            //注册驱动的第二种写法
            //字符串参数可以写到properties配置文件当中
            Class.forName("com.mysql.jdbc.Driver");

            //获取连接
            ResourceBundle resourceBundle = ResourceBundle.getBundle("jdbc");
            String url = resourceBundle.getString("url");
            String user = resourceBundle.getString("user");
            String password = resourceBundle.getString("password");
            connection = DriverManager.getConnection(url,user,password);

            System.out.println(connection);
        }catch (SQLException e){
            e.printStackTrace();
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }

    }
}

使用配置文件 jdbc.properties

1
2
3
4
url = jdbc:mysql://localhost:3306/bjpowernode
user = root
password = 333
driver = com.mysql.jdbc.Driver

用ResourceBundle.getBundle() 获取

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
import java.sql.*;
import java.util.ResourceBundle;

public class JDBCTest4 {


    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;

        try {

            ResourceBundle resourceBundle = ResourceBundle.getBundle("jdbc");
            //注册驱动
//            Driver driver = new com.mysql.jdbc.Driver();
//            DriverManager.registerDriver(driver);

            String dirver  = resourceBundle.getString("driver");
            Class.forName(dirver);
            //获取连接

            String url = resourceBundle.getString("url");
            String user = resourceBundle.getString("user");
            String password = resourceBundle.getString("password");

            connection = DriverManager.getConnection(url,user,password);
            System.out.println(connection);

            //创建数据库操作对象
            statement = connection.createStatement();

            //查询语句
            String sql = "insert into dept(deptno, dname, loc) value(50,'人事部', '北京')";
            //专门执行DML语句的
            int count = statement.executeUpdate(sql);
            System.out.println("");

            //处理返回数据

        } catch (SQLException e) {
            e.printStackTrace();
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }finally {
            //释放资源
            try {
                if(statement!=null){
                    statement.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
            //释放资源
            try {
                if(connection!=null){
                    connection.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        }




    }
}
处理返回数据
1
2
3
4
5
6
7
8
//专门执行DML语句的
int count = statement.executeUpdate(sql);
System.out.println("");

//处理返回数据

//int executeUpdate(insert/delete/update);
//ResultSet executeQuery(sql);
1
String sql = "select empno, ename, sal from emp";
1
2
3
4
5
6
7
8
9
10
11
//专门执行DQL语句
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
    //JDBC中下标从0开始
    //第1列
    String empno = resultSet.getString("ename");
    String ename = resultSet.getString("ename");
    String sal = resultSet.getString("sal");
    System.out.println(empno+"::"+ename+"::"+sal);

}

如果重命名了, 就需要使用alias

1
select empno as a, ename, sal from emp
1
String empno = resultSet.getString("a");

还可以已特定类型取出

1
int empno = resultSet.getInt(1);

使用PowerDesigner之类的工具

用MySQL WorkBench 中的Model 模式设计表格, 然后拿到建表sql代码

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS `bjpowernode`.`User` (
  `id` INT NOT NULL,
  `username` VARCHAR(255) NULL,
  `password` VARCHAR(255) NULL,
  `realname` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB

使用此数据库编写一个JDBC程序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
public class JDBCTest6 {

    public static void main(String[] args) {
        Map<String, String> userLoginInfo=initUI();
        Boolean loginSuccess=login(userLoginInfo);
        System.out.println(loginSuccess?"success":"fail");
    }

    private static boolean login(Map<String, String> userLoginInfo) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Boolean flag = false;
        try {
            ResourceBundle re = ResourceBundle.getBundle("jdbc");
            String url = re.getString("url");
            String user = re.getString("user");
            String password = re.getString("password");
            String driver = re.getString("driver");
            Class.forName(driver);
            //establish connection
            connection = DriverManager.getConnection(url,user,password);
            statement = connection.createStatement();
            String sql = "select * from t_user where `username`='"+userLoginInfo.get("username")+"' and `password` = '"+userLoginInfo.get("password")+"'";

            resultSet=statement.executeQuery(sql);
            if(resultSet.next()){
                flag = true;
            }


        }catch (SQLException e){
            e.printStackTrace();
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }finally {
            //释放资源
            try {
                if(statement!=null){
                    statement.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
            //释放资源
            try {
                if(connection!=null){
                    connection.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
            //释放资源
            try {
                if(resultSet!=null){
                    resultSet.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        return flag;
    }

    private static Map<String, String> initUI() {

        Scanner scanner = new Scanner(System.in);
        System.out.print("用户名: ");
        String username  = scanner.nextLine();
        System.out.print("密码: ");
        String password = scanner.nextLine();
        Map<String,String> map= new HashMap<String,String>();
        map.put("username",username);
        map.put("password",password);
        return map;


    }


}

但此程序存在问题

SQL注入

fsad

fsad’ or ‘1’=’1

还有SQL关键字并且被编译了进去, 导致语句原意扭曲

1
2
3
4
* 解决SQL注入
* 只要用户提供的信息不参与SQL语句的编译过程, 问题就解决了
* 即使信息中含有SQL语句关键字, 但是没参与编译就不起作用
* 使用java.sql.PreparedStatement
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.Scanner;
/**
 * 解决SQL注入
 * 只要用户提供的信息不参与SQL语句的编译过程, 问题就解决了
 * 即使信息中含有SQL语句关键字, 但是没参与编译就不起作用
 * 使用java.sql.PreparedStatement
 */
public class JDBCTest7 {
    public static void main(String[] args) {
        Map<String, String> userLoginInfo=initUI();
        Boolean loginSuccess=login(userLoginInfo);
        System.out.println(loginSuccess?"success":"fail");
    }
    private static boolean login(Map<String, String> userLoginInfo) {
        Connection connection = null;
//        Statement statement = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        Boolean flag = false;
        try {
            ResourceBundle re = ResourceBundle.getBundle("jdbc");
            String url = re.getString("url");
            String user = re.getString("user");
            String password = re.getString("password");
            String driver = re.getString("driver");
            Class.forName(driver);
            //establish connection
            connection = DriverManager.getConnection(url,user,password);
            String sql = "select * from t_user where username= ? and password = ? ";
//            statement = connection.createStatement();
            ps = connection.prepareStatement(sql);
//            resultSet=ps.executeQuery(sql);
            //给占位符传值, 不能再编译一次
            ps.setString(1,userLoginInfo.get("username"));
            ps.setString(2,userLoginInfo.get("password"));
            resultSet = ps.executeQuery();
            if(resultSet.next()){
                flag = true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }finally {
            //释放资源
            try {
                if(ps!=null){
                    ps.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
            //释放资源
            try {
                if(connection!=null){
                    connection.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
            //释放资源
            try {
                if(resultSet!=null){
                    resultSet.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        return flag;
    }

    private static Map<String, String> initUI() {

        Scanner scanner = new Scanner(System.in);
        System.out.print("用户名: ");
        String username  = scanner.nextLine();
        System.out.print("密码: ");
        String password = scanner.nextLine();
        Map<String,String> map= new HashMap<String,String>();
        map.put("username",username);
        map.put("password",password);
        return map;
    }
}

Statement 编译一次执行一次

PrepareStatement 编译一次执行N次, 所以效率也高一些

PrepareStatement还会在编译阶段做类型安全检查

综上所述, PrepareStatement 用的较多, 除非业务方面要求SQL注入的时候才用Statement

使用占位符进行增删改

1
2
3
4
5
         connection = DriverManager.getConnection(url,user,password);
         String sql = "delete from dept where deptno=?";
ps = connection.prepareStatement(sql);
         ps.setInt(1,70);
         int result = ps.executeUpdate();

事务

三行代码

connection.setAutoCommit(false);

connection.commit();

connection.rollBack();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
            connection = DriverManager.getConnection(url,user,password);
            //  关闭事务自动提交
            connection.setAutoCommit(false);

            String sql = "update t_act set balance = ? where actno = ?";
//            statement = connection.createStatement();
            ps = connection.prepareStatement(sql);
//            resultSet=ps.executeQuery(sql);
            //给占位符传值, 不能再编译一次
            ps.setDouble(1,10000);
            ps.setInt(2,111);

            int count = ps.executeUpdate();

            ps.setDouble(1,10000);
            ps.setInt(2,222);

            count += ps.executeUpdate();

            System.out.println(count==2?"success":"fail");
            connection.commit();

        }catch (Exception e){
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }

封装DBUtil

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
package util;

import java.net.URL;
import java.sql.*;
import java.util.ResourceBundle;

public class DBUtil {


    private String url;
    private String user;
    private String password;

    /**
     * 工具类中的构造方法都是私有的
     * 因为工具类中的方法都是静态的, 不需要new对象, 直接采用类名调用
     */
    private DBUtil(){}

    static {
        try {
            Class.forName(ResourceBundle.getBundle("jdbc").getString("driver"));

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws Exception{
        ResourceBundle resourceBundle = ResourceBundle.getBundle("jdbc");

        return DriverManager.getConnection(resourceBundle.getString("url")
            , resourceBundle.getString("user"), resourceBundle.getString("password"));

    }

    /**
     * 关闭资源
     * @param connection
     * @param statement
     * @param resultSet
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        //释放资源
        try {
            if(statement!=null){
                statement.close();
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        //释放资源
        try {
            if(connection!=null){
                connection.close();
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        //释放资源
        try {
            if(resultSet!=null){
                resultSet.close();
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
}

测试DBUtil 同时使用模糊查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCTest12 {

    /**
     * 测试模糊查询
     * 测试DBUtil
     *
     */
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs= null;
        try {
            connection = DBUtil.getConnection();


            String sql = "select ename from emp where ename like ?";
            ps = connection.prepareStatement(sql);
            ps.setString(1,"_A%");
            rs=ps.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("ename"));
            }


        }catch (Exception e){



            e.printStackTrace();
        }finally {
            DBUtil.close(connection,ps,rs);
        }
    }
}

锁

悲观锁: 事务必须排队执行, 数据锁住了, 不允许并发———行级锁(for update)

乐观锁: 支持并发, 事务也不需要排队, 只不过需要一个版本号

事务1修改了, 版本号从1.1变成1.2

事务2提交时候发现变成了1.2, 与最初版本号不一致, 回滚

1
select ename, dept from emp where dept ='MANAGER' for update
Author: klenq
Link: https://klenq.github.io/2021/11/09/JDBC学习笔记/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
入门JavaDatabase