1、JDBC本质

JDBC本质.jpg

  • JDBC：Java DataBase Connectivity（Java语言连接数据库）
  • JDBC的本质：JDBC是SUN公司制定的一套接口（interface）

java.sql.*; (这个软件包下有很多接口)

面向接口编程： 解耦合：降低程序的耦合度，提高程序的扩展力。 多态机制就是非常典型的：面向抽象编程。（不要面向具体编程）

  • 思考：为什么SUN制定一套JDBC接口呢？

因为每一个数据库的底层实现原理都不一样，Oracle数据库有自己的原理，MySQL数据库也有自己的原理，MS SqlServer数据库也有自己的原理，每一个数据库产品都有自己独特的实现原理

2、JDBC开发前的准备工作

先从官网下载对应的驱动jar包，然后将其配置到环境变量classpath当中
classpath=.;D:\course\06-JDBC\resources\MySql Connector Java 5.1.23\mysql-connector-java-5.1.23-bin.jar

以上的配置是针对于文本编辑器的方式开发；使用IDEA工具的时候，不需要配置以上的环境变量，IDEA有自己的配置方式

3、JDBC编程六步

  1. 第一步：注册驱动（作用：告诉Java程序，即将要连接的是哪个品牌的数据库）

第二步：获取连接（表示JVM的进程和数据库进程之间的通道打开了，这属于进程之间的通信，重量级的，使用完之后一定要关闭通道）

  1. 第三步：获取数据库操作对象（专门执行sql语句的对象）
  3. 第四步：执行SQL语句（DQL DML....）
  5. 第五步：处理查询结果集（只有当第四步执行的是select语句的时候，才有这第五步处理查询结果集）

第六步：释放资源（使用完资源之后一定要关闭资源，Java和数据库属于进程间的通信，开启之后一定要关闭）

4、JDBC代码实现

  1. public class JdbcTest04 {
  3.     public static void main(String[] args) {
  5.         Connection connection = null;
  6.         Statement statement =null;
  7.         try {
  8.             //1.注册驱动
  9.             DriverManager.registerDriver(new com.mysql.jdbc.Driver());
  10.             //2.测试连接
  11.             /*
  12.             url:协议          jdbc:mysql://
  13.                 IP地址        localhost（127.0.0.1）
  14.                 端口号        3306
  15.                 数据库名称    employeemanagementsystem
  16.              */
  17.             String url = "jdbc:mysql://localhost:3306/employeemanagementsystem";
  18.             String user = "root";
  19.             String password= "123";
  20.             connection = DriverManager.getConnection(url,user,password);
  21.             //3.创建数据库对象
  22.             statement = connection.createStatement();
  23.             //4.执行sql语句，executeUpdate方法返回值是受影响行数，用于增删改等操作
  24.             String sql ="insert into department(DEPARTMEN_ID,DEPARTMENT_NAME,DEPARTMEMT_DESCRIBE) values (7,'哈哈部','有关哈哈的部门')";
  25.             int count = statement.executeUpdate(sql);
  26.             //5.处理查询结果集（当为select语句时需要）
  28.         } catch (SQLException e) {
  29.             e.printStackTrace();
  30.         }finally {
  31.             //6.关闭资源，要遵循从小到大关闭
  32.             if(null != statement){
  33.                 try {
  34.                     statement.close();
  35.                 } catch (SQLException e) {
  36.                     e.printStackTrace();
  37.                 }
  38.             }
  39.             if (null != connection){
  40.                 try {
  41.                     connection.close();
  42.                 } catch (SQLException e) {
  43.                     e.printStackTrace();
  44.                 }
  45.             }
  46.         }
  47.     }
  48. }

4.1 jdbc注册驱动第二种写法：

  1. try {
  2.     //1.注册驱动
  3.     //常用，参数是一个字符串，字符串可以写到.properties文件中
  4.     Class.forName("com.mysql.jdbc.Driver");
  5. }catch (ClassNotFoundException e) {
  6.     e.printStackTrace();
  7. }

4.2 从属性资源文件中读取连接数据库信息

4.2.1 .java文件部分内容

  1. //通过绑定资源配置文件（jdbc.properties），来获取连接数据库所需的信息
  2. ResourceBundle resourceBundle = ResourceBundle.getBundle("jdbc");
  3. String driver = resourceBundle.getString("driver");
  4. String url = resourceBundle.getString("url");
  5. String user = resourceBundle.getString("user");
  6. String password = resourceBundle.getString("password");
  7. try {
  8.     Class.forName(driver);
  9.     Connection connection = DriverManager.getConnection(url,user,password);
  10.     Statement statement = connection.createStatement();
  11. } catch (ClassNotFoundException e) {
  12.     e.printStackTrace();
  13. } catch (SQLException e) {
  14.     e.printStackTrace();
  15. }

4.2.2 .properties文件内容

  1. # jdbc.properties文件内容：
  3. driver=com.mysql.jdbc.Driver
  4. url=jdbc:mysql://localhost:3306/employeemanagementsystem
  5. user=root
  6. password=123

4.3 jdbc查询代码实现

遍历结果集.jpg

  1. Class.forName("com.mysql.jdbc.Driver");
  2. Connection connection = null;
  3. connection = DriverManager.getConnection(url,user,password);
  4. Statement statement = connection.createStatement();
  5. String sql = "select * from table_name";
  6. //查询语句使用executeQuery方法
  7. ResultSet rs = statement.executeQuery(sql);
  8. //rs.next()，当结果为true，则表示有内容；getString的特点是：
  9. //无论数据库中的数据类型是啥，都以String类型取出；jdbc所有下标都以1开始
  10. while(rs.next()){
  11. system.out.println(rs.getString("columns")); //也可以使用rs.getString(1)，1代表的是第几列；
  12. //存在的问题会混乱列（数据库中的列可能会交换顺序），不建议；columns不是表中的列名称（表中列名称可能会起别名），
  13. //是查询结果集的列名称
  14. }

5、idea配置驱动

1、在project或module中右键，Open Module Setting
图片.png
2、libraries，点击+号，选中Java
图片.png
3、找到jar包的位置
图片.png
4、选择jar包所要添加的module
图片.png

6、SQL注入

6.1 通过一个登录操作演示SQL注入

  1. import java.sql.*;
  2. import java.util.HashMap;
  3. import java.util.Map;
  4. import java.util.Scanner;
  6. public class Login {
  8.     public static Map<String,String> init(){
  10.         Map<String,String> map = new HashMap<>();
  11.         Scanner input = new Scanner(System.in);
  12.         System.out.println("请输入用户名");
  13.         String user = input.nextLine();
  14.         System.out.println("请输入密码");
  15.         String password = input.nextLine();
  16.         map.put("user",user);
  17.         map.put("password",password);
  18.         return map;
  19.     }
  21.     public static boolean yanzheng(Map<String,String> loginxinxi){
  23.         Connection connection =null;
  24.         Statement statement = null;
  25.         ResultSet resultSet = null;
  27.         try {
  28.             Class.forName("com.mysql.jdbc.Driver");
  29.             String url = "jdbc:mysql://localhost:3306/employeemanagementsystem";
  30.             String user ="root";
  31.             String password = "123";
  32.             String wUser = loginxinxi.get("user");
  33.             String wPassword = loginxinxi.get("password");
  34.             connection = DriverManager.getConnection(url,user,password);
  35.             statement = connection.createStatement();
  36.             String sql ="select * from login where LoginName='" +wUser +" ' and Password='"+wPassword+"'";
  37.             resultSet = statement.executeQuery(sql);
  38.             if (resultSet.next()){
  39.                 return true;
  40.             }
  41.         } catch (ClassNotFoundException e) {
  42.             e.printStackTrace();
  43.         } catch (SQLException e) {
  44.             e.printStackTrace();
  45.         } finally {
  46.             if (null != resultSet){
  47.                 try {
  48.                     resultSet.close();
  49.                 } catch (SQLException e) {
  50.                     e.printStackTrace();
  51.                 }
  52.             }
  53.             if (null != statement){
  54.                 try {
  55.                     statement.close();
  56.                 } catch (SQLException e) {
  57.                     e.printStackTrace();
  58.                 }
  59.             }
  60.             if (null != connection){
  61.                 try {
  62.                     connection.close();
  63.                 } catch (SQLException e) {
  64.                     e.printStackTrace();
  65.                 }
  66.             }
  67.         }
  69.         return false;
  70.     }
  72.     public static void main(String[] args) {
  74.         Map<String,String> loginxinxi = init();
  75.         boolean s = yanzheng(loginxinxi);
  76.         System.out.println(s ? "登陆成功" : "登陆失败");
  77.     }
  78. }
  1.   - 当我们随便写一个用户名，密码随便写在后面加上** ' or '1' = '1**；加上这个后缀之后，随便一个用户名和密码都可以登录成功,原因是SQL语句被篡改成：

select * from login where LoginName=’sdwds’ and Password=’dsdsd ‘ or ‘1’ = ‘1’；1=1恒成立

6.2 解决SQL注入

  1.   - 通过preparedStatement对象，preparedStatement的原理是：预先对SQL语句的框架进行编译，然后再给SQL语句传值。
  1. public class JdbcTest02 {
  3.     public static void main(String[] args) {
  5.         Connection conn =null;
  6.         PreparedStatement ps =null;
  7.         ResultSet rs = null;
  9.         try {
  10.             Class.forName("com.mysql.jdbc.Driver");
  11.             String url ="jdbc:mysql://localhost:3306/employeemanagementsystem";
  12.             String user ="root";
  13.             String password = "123";
  14.             conn =DriverManager.getConnection(url,user,password);
  15.             String sql="select * from department where DEPARTMEN_ID=?";
  16.             ps = conn.prepareStatement(sql);
  17.             //给占位符赋值
  18.             ps.setString(1,"1");
  20.             rs = ps.executeQuery();
  21.             while (rs.next()){
  22.                 System.out.println(rs.getString("DEPARTMEN_ID")+"\t"+
  23.                                    rs.getString("DEPARTMENT_NAME")+
  24.                                    "\t"+rs.getString("DEPARTMEMT_DESCRIBE"));
  25.             }
  26.         } catch (ClassNotFoundException e) {
  27.             e.printStackTrace();
  28.         } catch (SQLException e) {
  29.             e.printStackTrace();
  30.         }finally {
  31.             if (null != rs){
  32.                 try {
  33.                     rs.close();
  34.                 } catch (SQLException e) {
  35.                     e.printStackTrace();
  36.                 }
  37.             }
  38.             if (null != ps){
  39.                 try {
  40.                     ps.close();
  41.                 } catch (SQLException e) {
  42.                     e.printStackTrace();
  43.                 }
  44.             }
  45.             if (null != conn){
  46.                 try {
  47.                     conn.close();
  48.                 } catch (SQLException e) {
  49.                     e.printStackTrace();
  50.                 }
  51.             }
  52.         }
  54.     }
  55. }

当一条SQL语句已经编译运行了，当再次使用该语句（即该语句和原来的那条语句完全一模一样）时，将直接运行

6.3 preparedStatement和Statement的区别

  1.   1. preparedStatement会在编译阶段做类型检查
  2.   1. Statement存在SQL注入；preparedStatement解决了SQL注入问题
  3.   1. Statement编译一次执行一次；preparedStatement编译一次，可执行N次，preparedStatement效率较高一些
  4.   1. preparedStatement使用较多，但是statement也有需求，当需求里需要SQL注入时（比如升序降序）

6.4 jdbc查询代码完善

  1.         Connection connection = null;
  2.         PreparedStatement preparedStatement = null;
  3.         ResultSet resultSet = null;
  4.         try {
  5.             //1.注册驱动
  6.             Class.forName("com.mysql.jdbc.Driver");
  7.             //2.获取连接
  8.             String url ="jdbc:mysql://localhost:3306/employeemanagementsystem";
  9.             String user = "root";
  10.             String password  = "123";
  11.             connection = DriverManager.getConnection(url,user,password);
  12.             //3.获取预先操作数据库对象，预执行SQL语句
  13.             String sql = "select * from department where DEPARTMEN_ID=?";
  14.             preparedStatement = connection.prepareStatement(sql);
  15.             //4.给SQL语句赋值，调用executeQuery方法进行查询
  16.             preparedStatement.setInt(1,1);
  17.             resultSet = preparedStatement.executeQuery();
  18.             //5.处理查询结果集
  19.             if (resultSet.next()){
  20.                 System.out.println(resultSet.getString("DEPARTMEN_ID")+"\t"
  21.                 +resultSet.getString("DEPARTMENT_NAME")+"\t"
  22.                 +resultSet.getString("DEPARTMEMT_DESCRIBE")+"\t");
  23.             }
  24.         } catch (ClassNotFoundException e) {
  25.             e.printStackTrace();
  26.         } catch (SQLException e) {
  27.             e.printStackTrace();
  28.         }finally {
  29.             //6.关闭资源
  30.             if (null != resultSet){
  31.                 try {
  32.                     resultSet.close();
  33.                 } catch (SQLException e) {
  34.                     e.printStackTrace();
  35.                 }
  36.             }
  37.             if (null != preparedStatement){
  38.                 try {
  39.                     preparedStatement.close();
  40.                 } catch (SQLException e) {
  41.                     e.printStackTrace();
  42.                 }
  43.             }
  44.             if (null != connection){
  45.                 try {
  46.                     connection.close();
  47.                 } catch (SQLException e) {
  48.                     e.printStackTrace();
  49.                 }
  50.             }
  51.         }

7. jdbc事务

7.1 jdbc事务自动提交机制

  1.   - jdbc事务是自动提交的，只要任意执行一条DML语句，则自动提交一次
  3.   - 代码演示：
  1. Connection connection = null;
  2. PreparedStatement preparedStatement = null;
  3. ResultSet resultSet = null;
  4. try {
  5.     //1.注册驱动
  6.     Class.forName("com.mysql.jdbc.Driver");
  7.     //2.获取连接
  8.     String url ="jdbc:mysql://localhost:3306/bank";
  9.     String user = "root";
  10.     String password  = "123";
  11.     connection = DriverManager.getConnection(url,user,password);
  12.     //3.获取预先操作数据库对象，执行第一条SQL语句
  13.     String sql = "update carddata set money=? where card_id=?";
  14.     preparedStatement = connection.prepareStatement(sql);
  15.     preparedStatement.setDouble(1,5000);
  16.     preparedStatement.setInt(2,111);
  17.     preparedStatement.executeUpdate();
  19.     //执行第二条SQL语句
  20.     String sql2 = "update carddata set money=? where card_id=?";
  21.     //在下面这行代码中加一个断点，debug，会发现上一条语句执行完之后，数据库中的内容就已经修改了
  22.     preparedStatement = connection.prepareStatement(sql2);
  23.     preparedStatement.setDouble(1,5000);
  24.     preparedStatement.setInt(2,222);
  25.     preparedStatement.executeUpdate();
  26. } catch (ClassNotFoundException e) {
  27.     e.printStackTrace();
  28. } catch (SQLException e) {
  29.     e.printStackTrace();
  30. }finally {
  31.     //6.关闭资源
  32.     if (null != resultSet){
  33.         try {
  34.             resultSet.close();
  35.         } catch (SQLException e) {
  36.             e.printStackTrace();
  37.         }
  38.     }
  39.     if (null != preparedStatement){
  40.         try {
  41.             preparedStatement.close();
  42.         } catch (SQLException e) {
  43.             e.printStackTrace();
  44.         }
  45.     }
  46.     if (null != connection){
  47.         try {
  48.             connection.close();
  49.         } catch (SQLException e) {
  50.             e.printStackTrace();
  51.         }
  52.     }
  53. }

7.2 jdbc事务代码实现：

  1.   - 步骤：

1、在SQL语句执行之前将自动提交机制变为手动提交机制
connection.setAutoCommit(false);
2、SQL语句执行之后，手动提交事务
connection.commit();
3、在catch语句块中手动回滚事务
connection.rollback();

  1.   - 代码实现：
  1. Connection connection = null;
  2. PreparedStatement preparedStatement = null;
  3. ResultSet resultSet = null;
  4. try {
  5.     //1.注册驱动
  6.     Class.forName("com.mysql.jdbc.Driver");
  7.     //2.获取连接
  8.     String url ="jdbc:mysql://localhost:3306/bank";
  9.     String user = "root";
  10.     String password  = "123";
  11.     connection = DriverManager.getConnection(url,user,password);
  12.     //3.获取预先操作数据库对象，执行第一条SQL语句
  13.     String sql = "update carddata set money=? where card_id=?";
  14.     //将自动提交事务关闭
  15.     connection.setAutoCommit(false);
  16.     preparedStatement = connection.prepareStatement(sql);
  17.     preparedStatement.setDouble(1,5000);
  18.     preparedStatement.setInt(2,111);
  19.     preparedStatement.executeUpdate();
  20.     //即使发生异常，也不会导致money丢失
  21.     Object o = null;
  22.     o.toString();
  23.     //执行第二条SQL语句
  24.     String sql2 = "update carddata set money=? where card_id=?";
  25.     preparedStatement = connection.prepareStatement(sql2);
  26.     preparedStatement.setDouble(1,5000);
  27.     preparedStatement.setInt(2,222);
  28.     preparedStatement.executeUpdate();
  30.     //手动提交事务
  31.     connection.commit();
  32. } catch (ClassNotFoundException e) {
  33.     //回滚事务
  34.     try {
  35.         connection.rollback();
  36.     } catch (SQLException e1) {
  37.         e1.printStackTrace();
  38.     }
  39.     e.printStackTrace();
  40. } catch (SQLException e) {
  41.     e.printStackTrace();
  42. }finally {
  43.     //6.关闭资源
  44.     if (null != resultSet){
  45.         try {
  46.             resultSet.close();
  47.         } catch (SQLException e) {
  48.             e.printStackTrace();
  49.         }
  50.     }
  51.     if (null != preparedStatement){
  52.         try {
  53.             preparedStatement.close();
  54.         } catch (SQLException e) {
  55.             e.printStackTrace();
  56.         }
  57.     }
  58.     if (null != connection){
  59.         try {
  60.             connection.close();
  61.         } catch (SQLException e) {
  62.             e.printStackTrace();
  63.         }
  64.     }
  65. }

8. 将JDBC进行封装

工具类构造方法建议私有化，因为工具类一般都是静态的，不需要调用构造方法； 在下面的封装代码中，Class.forName();可以写在静态代码块中，当类加载时调用一次就可以了 close关闭时，建议statement，而不是preparedStatement，面向父类编程

  • 简单代码实现： ```java import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException;

/**

  • 操作数据库的基类—静态类

  • / public class BaseDao {

    //驱动 private static final String driver=”com.mysql.jdbc.Driver”; //数据库链接URL private static final String url=”jdbc:mysql://localhost:3306/employeemanagementsystem?useUnicode=true&characterEncoding=utf8”; //数据库账号 private static final String user=”root”; //数据库密码 private static final String password=”123”; //数据库链接对象 static Connection connection = null; //数据库操作对象 static PreparedStatement preparedStatement=null; //查询返回的结果集 static ResultSet resultSet=null;

    /**

    • 获取数据库连接

    • @return 返回数据库链接对象 */ public static Connection getConnection(){

      try {

      1.  Class.forName(driver);
      2.  connection = DriverManager.getConnection(url, user, password);

      } catch (Exception e) {

      1.  // TODO Auto-generated catch block
      2.  e.printStackTrace();

      }

      return connection; } /**

    • 查询操作
    • @param sql 执行语句
    • @param params 需要的参数
    • @return 返回查询的结果集(java.sql.ResultSet) */ public static ResultSet executeQuery(String sql,Object[] params) throws Exception{ connection=getConnection(); preparedStatement = connection.prepareStatement(sql); if(params!=null){
      1.  for(int i = 0; i < params.length; i++){
      2.      preparedStatement.setObject(i+1, params[i]);
      3.  }
      } resultSet = preparedStatement.executeQuery(); return resultSet; } /**
    • 更新操作
    • @param sql 操作语句
    • @param params 需要的参数

    • @return 返回受影响行数 */ public static int executeUpdate(String sql,Object[] params) throws Exception{ int updateRows = 0; connection=getConnection(); preparedStatement = connection.prepareStatement(sql); for(int i = 0; i < params.length; i++){

      1.  preparedStatement.setObject(i+1, params[i]);

      } updateRows = preparedStatement.executeUpdate(); return updateRows; }

      /**

    • 释放资源 */ public static void closeResource(){

      if(resultSet != null){

      1.  try {
      2.      resultSet.close();
      3.      resultSet = null;//GC回收
      4.  } catch (SQLException e) {
      5.      // TODO Auto-generated catch block
      6.      e.printStackTrace();
      7.  }

      } if(preparedStatement != null){

      1.  try {
      2.      preparedStatement.close();
      3.      preparedStatement = null;//GC回收
      4.  } catch (SQLException e) {
      5.      // TODO Auto-generated catch block
      6.      e.printStackTrace();
      7.  }

      } if(connection != null){

      1.  try {
      2.      connection.close();
      3.      connection = null;//GC回收
      4.  } catch (SQLException e) {
      5.      // TODO Auto-generated catch block
      6.      e.printStackTrace();
      7.  }

      } } } ```

9. 模糊查询

  • 占位符传值里面需要有模糊查询的符号；
  1. String sql = "select * from department where DEPARTMENT_NAME like ?";
  2. preparedStatement = connection.prepareStatement(sql);
  3. //给SQL语句赋值，调用executeQuery方法进行查询
  4. preparedStatement.setString(1,"%事%");
  5. resultSet = preparedStatement.executeQuery();

10. 悲观锁（行级锁）和乐观锁

悲观锁（行级锁for update）和乐观锁机制.jpg

  • 部分主要代码实现：
    1. //关闭自动提交
    2. connection.setAutoCommit(false);
    3. //3.获取预先操作数据库对象，预执行SQL语句
    4. String sql = "select * from department where DEPARTMEN_ID=? for update";
    5. preparedStatement = connection.prepareStatement(sql);
    6. //4.给SQL语句赋值，调用executeQuery方法进行查询
    7. preparedStatement.setString(1,"2");
    8. resultSet = preparedStatement.executeQuery();
    9. connection.commit();
    1. connection.setAutoCommit(false);
    2. //3.获取预先操作数据库对象，预执行SQL语句
    3. String sql = "update department set DEPARTMENT_NAME=? where DEPARTMEN_ID=?";
    4. preparedStatement = connection.prepareStatement(sql);
    5. preparedStatement.setString(1,"人事部");
    6. preparedStatement.setString(2,"2");
    7. int num = preparedStatement.executeUpdate();
    8. connection.commit();

说明：
在第一部分的代码的commit那里加断点，debug；在运行第二部分的代码；由于第一部分的代码给该行设置了行级锁，导致第二部分的代码无法对该行进行修改操作，当继续debug第一部分剩下的代码时，第二部分的代码也可以继续执行。

11. jdbc使用中出现的一些错误

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

错误解决：
SQL语句存在错误：
String sql = “delete from department where departmen_id”;
纠正：departmen_id=？