xiaohuihui
for me
FRIENDS
friendAfriendBfriendC

JDBC

2020-09-25 19:13:53
Word count: 2.4k | Reading time: 12min

JDBC

JDBC(Java DataBase Connectivity)（Java连接数据库）

JDBC的本质是一套接口。

模拟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
package JDBC;

import java.util.ResourceBundle;

public class JDBCTest04 {
  public static void main(String[] args) {
    try {
      ResourceBundle bundle = ResourceBundle.getBundle("JDBC/jdbc");
      String className = bundle.getString("jdbc");
      Class c = Class.forName(className);
      JDBC jdbc = (JDBC)c.newInstance();
      jdbc.getConnection();			//Oracle数据库连接成功
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (IllegalAccessException e) {
      e.printStackTrace();
    } catch (InstantiationException e) {
      e.printStackTrace();
    }
  }
}


interface JDBC{
  void getConnection();
}

class Mysql implements JDBC{
  @Override
  public void getConnection() {
    System.out.println("Mysql数据库连接成功");
  }
}

class Oracle implements JDBC{
  @Override
  public void getConnection() {
    System.out.println("Oracle数据库连接成功");
  }
}

JDBC步骤

  1. 注册驱动（选择要连接的数据库）
  2. 获取连接
  3. 获取数据库操作对象
  4. 执行sql语句
  5. 处理查询结果集
  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
package JDBC.test;

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

public class JDBCTest01 {
  public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    try {
      //1.注册驱动
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //2.获取连接
      String url = "jdbc:mysql://localhost:3306/bjpowernode";
      String user = "root";
      String password = "admin@123";
      conn = DriverManager.getConnection(url,user,password);
      //3.获取数据库操作对象
      stmt = conn.createStatement();
      //4.执行sql语句
      String sql = "insert into dept(deptno,dname,loc) values(50,'人事部','newYork')";
      int count = stmt.executeUpdate(sql);
      System.out.println(count == 1?"插入成功":"插入失败");
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    } finally {
      if(conn != null){
        try {
          conn.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
      if (stmt !=null){
        try {
          stmt.cancel();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
    }

  }
}

类加载的方式注册驱动

jdbc配置文件:

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

java文件：

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
package JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

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

    ResourceBundle bundle = ResourceBundle.getBundle("JDBC/jdbc");
    String driver = bundle.getString("driver");
    String url = bundle.getString("url");
    String user = bundle.getString("user");
    String password = bundle.getString("password");

    Connection conn = null;
    Statement stmt = null;
    try {
      //1.注册驱动
      Class.forName(driver);
      //2.获取连接
      conn = DriverManager.getConnection(url,user,password);
      //3.获取数据库操作对象
      stmt = conn.createStatement();
      //4.执行sql语句
      String sql = "insert into dept(deptno,dname,loc) values(60,'技术部','newYork')";
      int count = stmt.executeUpdate(sql);
      System.out.println(count == 1?"插入成功":"插入失败");
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } finally {
      if(conn != null){
        try {
          conn.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
      if (stmt !=null){
        try {
          stmt.cancel();
        } catch (SQLException throwables) {
          throwables.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
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
package JDBC.test2;

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

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

    ResourceBundle bundle = ResourceBundle.getBundle("JDBC/jdbc");
    String driver = bundle.getString("driver");
    String url = bundle.getString("url");
    String user = bundle.getString("user");
    String password = bundle.getString("password");

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      //1.注册驱动
      Class.forName(driver);
      //2.获取连接
      conn = DriverManager.getConnection(url,user,password);
      //3.获取数据库操作对象
      stmt = conn.createStatement();
      //4.执行sql语句
      String sql = "select empno,ename,sal from emp";
      rs = stmt.executeQuery(sql);    //执行DQL语句的语法
      //5.处理查询结果集
      while (rs.next()){
        int empno = rs.getInt("empno");
        String ename = rs.getString("ename");
        double sal = rs.getDouble("sal");
        System.out.println(empno+","+ename+","+sal);
      }
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } finally {
      //先释放查询结果集
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      //再释放数据库连接对象
      if(conn != null){
        try {
          conn.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
      //最后释放连接对象
      if (stmt !=null){
        try {
          stmt.close();
        } catch (SQLException throwables) {
          throwables.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
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
90
91
92
93
94
95
96
97
package JDBC;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

public class JDBCTest05 {
  public static void main(String[] args) {
    //初始化一个界面
    Map<String,String> userLoginInfo = initUI();
    //验证用户名和密码
    boolean loginSuccess = login(userLoginInfo);
    //最后输出结果
    System.out.println(loginSuccess?"登录成功":"登录失败");
  }

  /**
   *
   * @param userLoginInfo   用户登录信息
   * @return    false表示登录失败,true表示登录成功
   */
  private static boolean login(Map<String, String> userLoginInfo) {
    boolean loginSuccess = false;

    String loginName = userLoginInfo.get("loginName");
    String loginPwd = userLoginInfo.get("loginPwd");
    //JDBC
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      //1.注册驱动
      Class c = Class.forName("com.mysql.jdbc.Driver");
      //2.获取连接
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","admin@123");
      //3.获取数据库操作对象
      stmt = conn.createStatement();
      //4.执行sql
      String sql = "select * from t_user where loginName = '"+loginName+"' and " +
              " loginPwd = '"+loginPwd+"' ";
      //5.处理结果集
      rs = stmt.executeQuery(sql);
      if(rs.next()){
        loginSuccess = true;
      }

    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    } finally {
      //6.释放资源
      //释放结果集
      if(rs != null){
        try {
          rs.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
      //释放数据库操作对象
      if(stmt != null){
        try {
          stmt.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
      //释放与数据库的连接
      if(conn != null){
        try {
          conn.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
    }
    return loginSuccess;
  }

  /**
   * 初始化用户界面
   * @return  用户输入的账号和密码等登录信息
   */
  private static Map<String,String> initUI() {
    Scanner s = new Scanner(System.in);
    System.out.println("请输入用户名:");
    String loginName = s.nextLine();
    System.out.println("请输入密码:");
    String loginPwd = s.nextLine();
    Map<String,String> userLoginInfo = new HashMap<>();
    userLoginInfo.put("loginName",loginName);
    userLoginInfo.put("loginPwd",loginPwd);
    return userLoginInfo;
  }
}

但是此方法会涉及到SQL注入的问题，所以使用PreparedStatement接口来避免sql注入。

避免sql注入

使用PreparedStatement接口来避免sql注入。

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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
package JDBC;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

/**
 * @Author coderYang
 * @Date 2020/10/5 19:02
 *
 * 解决sql注入的问题:
 *  用户输入的信息不参与sql语句的编译过程,就解决了
 *  使用java.sql.PreparedStatement接口,此接口是预编译的数据库操作对象。
 *  原理是预先对sql语句的框架进行编译,然后再给sql传值
 */
public class JDBCTest06 {
  public static void main(String[] args) {
    //初始化一个界面
    Map<String,String> userLoginInfo = initUI();
    //验证用户名和密码
    boolean loginSuccess = login(userLoginInfo);
    //最后输出结果
    System.out.println(loginSuccess?"登录成功":"登录失败");
  }

  /**
   *
   * @param userLoginInfo   用户登录信息
   * @return    false表示登录失败,true表示登录成功
   */
  private static boolean login(Map<String, String> userLoginInfo) {
    boolean loginSuccess = false;

    String loginName = userLoginInfo.get("loginName");
    String loginPwd = userLoginInfo.get("loginPwd");
    //JDBC
    Connection conn = null;
    PreparedStatement ps = null;      //这里使用PreparedStatement(预编译的数据库操作对象)
    ResultSet rs = null;
    try {
      //1.注册驱动
      Class c = Class.forName("com.mysql.jdbc.Driver");
      //2.获取连接
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","admin@123");
      //3.获取预编译的数据库操作对象,一个问号表示一个占位符,一个问号接收一个值,占位符不能使用单引号括起来
      String sql = "select * from t_user where loginName = ? and + loginPwd = ? ";
      //程序执行到此处,会发送sql语句给DBMS,然后DBMS进行sql语句的预编译
      ps = conn.prepareStatement(sql);
      //给占位符传值,第一个占位符下标为1,第二个占位符下标为2,依此类推
      ps.setString(1,loginName);
      ps.setString(2,loginPwd);
      //执行sql语句
      rs = ps.executeQuery();
      //5.处理结果集
      if(rs.next()){
        loginSuccess = true;
      }

    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    } finally {
      //6.释放资源
      //释放结果集
      if(rs != null){
        try {
          rs.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
      //释放数据库操作对象
      if(ps != null){
        try {
          ps.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
      //释放与数据库的连接
      if(conn != null){
        try {
          conn.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
    }
    return loginSuccess;
  }

  /**
   * 初始化用户界面
   * @return  用户输入的账号和密码等登录信息
   */
  private static Map<String,String> initUI() {
    Scanner s = new Scanner(System.in);
    System.out.println("请输入用户名:");
    String loginName = s.nextLine();
    System.out.println("请输入密码:");
    String loginPwd = s.nextLine();
    Map<String,String> userLoginInfo = new HashMap<>();
    userLoginInfo.put("loginName",loginName);
    userLoginInfo.put("loginPwd",loginPwd);
    return userLoginInfo;
  }
}

Statement和PreparedStatement区别

  • Statement存在sql注入问题，PreparedStatement解决了sql注入问题
  • Statement是编译一次执行一次，效率较低。
  • PreparedStatement是编译一次，可执行n次，效率较高。
  • PreparedStatement会在编译阶段做类型的安全检查

Statement用途

有时候Statement也是要用到，就是用户输入的语句，不能进行预编译,需要进行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
package JDBC;

import java.sql.*;
import java.util.Scanner;

/**
 * @Author coderYang
 * @Date 2020/10/5 22:49
 */
public class JDBCTest07 {
  public static void main(String[] args) {
    Scanner s = new Scanner(System.in);
    System.out.println("请输入desc或asc,desc是降序输出,asc是升序输出");
    System.out.println("请输入: ");
    String keyWords = s.nextLine();

    //执行sql
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
      //注册驱动
      Class.forName("com.mysql.jdbc.Driver");
      //获取连接
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","admin@123");
      //获取数据库操作对象
      stmt = conn.createStatement();
      //执行sql
      String sql = "select ename from emp order by ename "+keyWords;
      rs = stmt.executeQuery(sql);
      //遍历结果集
      while (rs.next()){
        System.out.println(rs.getString("ename"));
      }
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    } finally {
      if(rs != null){
        try {
          rs.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
      if(stmt != null){
        try {
          stmt.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
      if(conn != null){
        try {
          conn.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
    }
  }
}

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
package JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @Author coderYang
 * @Date 2020/10/6 13:55
 */
public class JDBCTest08 {
  public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
      //1.注册驱动
      Class.forName("com.mysql.jdbc.Driver");
      //2.获取连接
      conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","admin@123");
      //3.获取预编译的数据库操作对象
//      String sql = "insert into dept(deptno,dname,loc) values(?,?,?)";
//      ps = conn.prepareStatement(sql);
//      ps.setInt(1,50);
//      ps.setString(2,"销售部");
//      ps.setString(3,"上海部");

//      String sql = "update dept set dname = ?,loc = ? where deptno = ?";
//      ps = conn.prepareStatement(sql);
//      ps.setString(1,"研发一部");
//      ps.setString(2,"北京");
//      ps.setInt(3,50);

      String sql = "delete from dept where deptno = ?";
      ps = conn.prepareStatement(sql);
      ps.setInt(1,50);
      //4.执行sql
      int count = ps.executeUpdate();
      System.out.println(count==1?"操作成功":"操作失败");
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    } finally {
      if(ps != null){
        try {
          ps.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
      if(conn != null){
        try {
          ps.close();
        } catch (SQLException throwables) {
          throwables.printStackTrace();
        }
      }
    }
  }
}

Author: 小灰灰

Link: http://xhh460.github.io/2020/09/25/JDBC/

Copyright: All articles in this blog are licensed.

< PreviousPost
koaNextPost >
javaSE
CATALOG
  1. 1. JDBC
    1. 1.0.1. 模拟JDBC
    2. 1.0.2. JDBC步骤
      1. 1.0.2.1. 类加载的方式注册驱动
      2. 1.0.2.2. 处理查询结果集
      3. 1.0.2.3. 避免sql注入
      4. 1.0.2.4. Statement和PreparedStatement区别
      5. 1.0.2.5. Statement用途
      6. 1.0.2.6. JDBC的增删改