JDBC基础教程

JDBC

JDBC介绍

• JDBC全称为：Java DataBase Connectivity（java数据库连接）。
• SUN公司为了简化、统一对数据库的操作，定义了一套Java操作数据库的规范，称之为JDBC。
• 没有jdbc之前：
  
• 有了jdbc之后:
  

  JDBC API常用类与接口

• Jdbc操作的相关api如下：
  • DriverManger:数据区驱动类,不同的数据库有不同的数据库驱动,是各数据库厂商对sun公司接口规范的实现。
  • Connection:数据库连接，建立一个操作数据库的连接。
  • Statement:数据库操作，向数据库发送sql语句.
  • ResultSet:结果集，Statement执行完sql返回的结果。
• 图中是几个核心类的关系。
  

  入门代码：

  public class JdbcDemo {
      @Test
      public void query() throws Exception {
          // 1.加载数据库驱动
          Class.forName("com.mysql.jdbc.Driver");
          // 2.建立数据库连接
          // jdbc:mysql:连接mysql数据库的固定写法，localhost：ip地址，3306：端口号，jdbcDemo：数据库名称
          String url = "jdbc:mysql://localhost:3306/lxy?characterEncoding=utf-8&useSSL=false";
          // 第一个参数：连接mysql数据库,第二和第三个参数分别是数据库的用户名和密码
          Connection con = DriverManager.getConnection(url, "root", "***");
          // 3.获取statement对象
          Statement st = con.createStatement();
          // 4.执行sql语句，并且获取结果
          String sql = "select * from user";
          ResultSet res = st.executeQuery(sql);
          // 5.处理结果
          while (res.next()) {
              System.out.println(res.getString("name"));
          }
          // 6.释放资源
          res.close();
          st.close();
          con.close();
      }
  }

• jdbc编程步骤:
  

  Connection详解

• url介绍：指定一个具体的数据库
  
  • Oracle的URL：jdbc:oracle:thin:@localhost:3306:jdbcDemo
  • MySql的URL：jdbc:mysql://localhost:3306/jdbcDemo
  • MySql的URL简写方式：jdbc:mysql:///jdbcDemo（注意：简写方式必须是本地连接-localhost，并且需要端口是3306的）
    • jdbc:mysql:///jdbcDemo ===== jdbc:mysql://localhost:3306/jdbcDemo
    • 要求是localhost，并且端口是3306； 才可以进行简写
    • 前面是后面的简写。
• Connection
  • 获取数据库连接Connction的方法：
    • DriverManager.getConnection(url, user, password);
      • url：连接到某一个具体的数据库
      • user：数据库的用户名
      • password：数据库用户名对应的密码。
      • 注意：获取连接时导入的包是java.sql.Connection;
      • 虽然导入com.mysql.jdbc.Driver;然后进行强转也可以使用，但是这样就与代码耦合了，不方便更换数据库。
  • Jdbc程序中的Connection，它用于代表数据库的链接，Connection是数据库编程中最重要的一个对象，客户端与数据库所有交互都是通过connection对象完成的，这个对象的常用方法.
    • Statement createStatement();:创建一个Statement对象来将SQL语句发送到数据库。

      Statement 详解

• 需要掌握的方法：
  • stmt.executeUpdate(sql)：执行增删改
  • stmt.executeQuery(sql)：执行查询
• Statement：向数据库发送sql的对象，并且得到执行sql之后的结果。是接口
• Jdbc程序中的Statement对象用于向数据库发送SQL语句，Statement对象常用方法：
  • 1、ResultSet executeQuery(String sql)：执行给定的SQL语句，该语句返回单个ResultSet对象
    • 相当于执行select查询语句，当然查询语句就是executeQuery(String sql)中的参数，查询语句可以自定义。
    • 将查询到的数据封装到ResultSet中并进行返回。
  • 2、int executeUpdate(String sql)：执行给定SQL语句，该语句可能为INSTERT，UPDATE，或DELETE语句，或者不返回任何内容的SQL语句(如SQL DDL语句)
    • 相当于执行update,delete,insert等语句，当然具体的语句就是executeQuery(String sql)中的参数，语句可以自定义。
    • 返回值int:代表执行增删改的时候总共影响到了几条记录。
  • 3、boolean execute(String sql):执行给定的SQL语句，该语句可能返回多个结果。
    • 如果第一个结果为ResultSet对象，则返回true；如果其为更新计数或者不存在任何结果，则返回 false
    • 可以通过Result getResultSet(); 获取当前sql执行之后得到的结果集。

      @Test
      public void execute () throws Exception{
          //注册驱动
          Class.forName("com.mysql.jdbc.Driver");
          //后去连接
          Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root");
          //定义sql
          //String sql = "select * from employee";
          //更新貂蝉的年龄为18岁
          String sql = "update employee set age= 18 where name = '貂蝉'";
          //创建sql操作对象
          Statement st = con.createStatement();
          //执行查询,并且返回boolean
          boolean flag = st.execute(sql);
          //判断flag是否为true来判断到底是不是查询语句
          if (flag) {
              //如果是true，就证明是一个查询语句，需要获取结果集
              ResultSet res = st.getResultSet();
              while(res.next()){
                  System.out.println(res.getInt("id")+"..."+res.getString("name")+"..."+res.getInt("age"));
              }
              res.close();
          }else{
              //如果是false，就证明不是查询语句
              System.out.println("sql语句执行成功！一共操作了"+st.getUpdateCount()+"条sql语句");
          }
          // 释放资源
          st.close();
          con.close();
      }

      ResultSet 详解

• 结果集：我们查询所有数据的时候，得到结果集
• 如何去遍历ResultSet:
  • boolean next():将光标从当前位置向前移一行。
  • boolean absolute(int row):将光标移动到此ResultSet对象的给定行编号。
  • void afterLast():将光标移动到此ResultSet对象的末尾，正好位于最后一行之后。
  • void beforeFirst():将光标移动到此ResultSet对象的开头，正好位于第一行之前。
  • boolean previous():将光标移动到此ResultSet对象的上一行。
    
• 如何取出数据:
  • Resulset getXxx(String columnLabel):xxx表示的是具体的数据类型。
    • 数据库表中的每个列（字段），有自己的数据类型。
    • 从结果集中取出数据是从列中取出数据。
    • 获取数据库中数据的类型需要和getXxx(XXX)方法的类型需要一一对应。
  • getXxx（String 列的名字）；—常用
    • 如果从列的名字是name的取出数据。
    • getString(“name”);
  • getXxx(int 列的顺序)—-注意：列的顺序从1开始。
    • 如果想要获取name
    • getString(2);
  • 建议使用按照列名去获取数据。如果有别名，也可以按照别名去获取。
    

    资源释放

• 连接资源是珍贵的，是有数目限制的。如果只有打开没有关闭，那么当达到一个限定之后，其他人将无法继续连接数据库。所以需要关闭连接。因此，在使用完资源以后，就必须将资源释放掉，节省资源。

  JDBC 工具（JdbcUtil）类抽取（★★★★★）

• 配置文件：

  # jdbc.properties放置在src目录下
  driverClass=com.mysql.jdbc.Driver
  url=jdbc:mysql://47.101.175.143:3306/lxy?characterEncoding=utf-8&useSSL=false
  user=root
  password=naraka47

  public class JdbcUtils {
      static String url;
      static String user;
      static String password;
      static String driverClass;
  
      // 数据库驱动只需要加载一次
      // static静态代码块在类加载的时候会执行，并且只执行一次
      static {
          try {
              Properties pro = new Properties();
              InputStream inputStream = new FileInputStream("src/jdbc.properties");
              //加载properties文件
              pro.load(inputStream);
              //读取properties文件中的数据
              driverClass = pro.getProperty("driverClass");
              url = pro.getProperty("url");
              user = pro.getProperty("user");
              password = pro.getProperty("password");
              Class.forName(driverClass);
          } catch (Exception e) {
              e.printStackTrace();
          }
      }
  
      // 对外提供获取连接的方法
      // 异常可以直接抛出，因为在调用该方法处还会执行try-catch.
      public static Connection getConnection() throws SQLException {
          Connection con = DriverManager.getConnection(url, user, password);
          return con;
      }
  
      //释放资源方法2
      //其他增删改释放资源
      public static void release(Connection con, Statement st) {
          try {
              if (st != null) {
                  st.close();
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
          try {
              if (con != null) {
                  con.close();
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  
      //释放资源方法1
      //查询的时候释放资源
      public static void release(Connection con, Statement st, ResultSet res) {
          try {
              if (res != null) {
                  res.close();
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
          try {
              if (st != null) {
                  st.close();
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
          try {
              if (con != null) {
                  con.close();
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  }

SQL注入问题

• 什么是sql注入问题？
  • 用户在页面提交数据的时候人为的添加一些特殊字符，使得sql语句的结构发生了变化，最终可以在没有用户名或者密码的情况下进行登录。
• sql注入分析与解决方案
  • Sql注入的原因分析：
    • 之所以有sql注入的问题，无非是在参数中设置了一些特殊字符，使sql语句在拼接这些参数的时候因为特殊字符的原因改变了sql语句原来的规则。
  • 解决方案：
    • 使用PreparedStatement解决SQL注入问题，运行在SQL中参数以’?’占位符的方式表示
    • 举例：
      • String sql = “select * from user where username = ‘admin’ and password = ‘admin’ “;
        使用？进行站位后的语句
        String sql = “select * from user where username = ? and password = ? “;
        PreparedStatement将带有?的SQL发送给数据库完成预编译，预编译的sql语句由于缺少两个参数，因此无法执行，需要通过PreparedStatement将预编译中被？占位的参数传递进来。
    • 而且由于SQL已经编译过，参数中特殊字符不再当做特殊字符编译，因此无法达到SQL注入的目的。
• PreparedStatement API介绍
  • PreparedStatment —sql只被编译一次，可以防止sql的注入。sql语句格式良好方便阅读。
  • statement —-调用几次编译几次。
  • 使用方式基本和statement的使用方式相同。
  • 获取PreparedStatement对象：通过connection获取
  • PreparedStatement prepareStatement(String sql):创建一个PreparedStatement对象来将参数化的SQL语句发送到数据库。
  • 注意：上述方法在创建PreparedStatement对象的时候需要先将sql语句传递进去并进行预编译，因此sql需要提前创建好。sql语句中需要参数。使用？进行占位。
  • 然后获取到PreparedStatement对象后，在执行sql语句之前需要先用参数把？替换掉。
    • 可以使用setXxx(int parameterIndex, Xxx x)进行传递：Xxx为参数类型，如String、int、double等，传递什么类型的参数就需要使用什么类型的setXxx(int parameterIndex, Xxx x)方法。
• PreparedStatement案例（★★★★★）

  @Test
  public User login(User user) {
      Connection con = null;
      PreparedStatement ps = null;
      ResultSet res = null;
      try {
          //获取连接
          con = JdbcUtils.getConnection();
          //创建数据库操作对象
          String sql = "select * from User where username = ? and password = ?";
          //执行预编译
          ps = con.prepareStatement(sql);
          //传递参数
          ps.setString(1, user.getUsername());
          ps.setString(2, user.getPassword());
          //执行查询
          res = ps.executeQuery();
          //获取数据并封装
          if (res.next()) {
              User queryUser = new User();
              queryUser.setId(res.getInt("id"));
              queryUser.setUsername(res.getString("username"));
              queryUser.setPassword(res.getString("password"));
              return queryUser;
          }
      } catch (Exception e) {
          e.printStackTrace();
      } finally {
          JdbcUtils.release(con, ps, res);
      }
      return null;
  }

• PreparedStatement优点:
  • PreparedStatement是Statement的子接口，它的实例对象可以通过调用Connection.preparedStatement(sql)方法获得，相对于Statement对象而言：
    • PreperedStatement可以避免SQL注入的问题。
    • Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率。
    • 并且PreperedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。方便阅读，使代码的可读性更高。

      JDBC批处理

• 批处理介绍和优点
  • 在实际开发中，经常需要向数据库发送多条SQL语句，这时，如果逐条执行这些SQL语句，效率会很低。为此，JDBC提供了批处理提供了批处理机制，即同时执行多条SQL语句。
  • 在jdbc中提供两种批处理的方式：
    • 1、 Statement批处理
    • 2、 PrepareStatement批处理
    • 两种批处理的方式都可以批量的执行sql语句，以达到提升效率的目的。
• statement批处理
  • 常用方法：
    • Statement.addBatch(sql) ：添加批处理的sql语句
    • Statement.executeBatch()：执行批处理命令
    • Statement.clearBatch()：清除批处理命令
  • 总结:
    • 采用Statement.addBatch(sql)方式实现批处理：
      • 优点：可以向数据库发送多条不同类型的sql语句。
      • 缺点：SQL语句没有预编译，有注入风险。当向数据库发送多条语句相同，但参数不同的SQL语句时，需重复写上很多条SQL语句并且编译多次.
• preparestatement批处理
  • 常用方法:
    • Connection.prepareStatement(sql): 预编译sql语句
    • PreparedStatement.addBatch() : 增加批处理
    • PreparedStatement.executeBatch() : 执行批处理命令
    • PreparedStatement.clearBatch(); 清除批处理的命令
  • 总结:
    • PrepareStatement批处理的优缺点：
      • 优点：发送的是预编译后的SQL语句，执行效率高。
      • 缺点：只能在SQL语句相同，当参数不同的批处理中使用,因此这种批处理的方式经常适用于在同一个表中批量插入或者更新数据。

        事务（★★★★★）

• 什么是事务？
  • 一组sql语句(insert、update、delete)，要么全部执行成功，要么全部执行失败，不能成功或者失败其中一部分。
• 事务的使用步骤：
  • 1、开启事务
  • 2、执行一组sql语句
  • 3、提交事务/回滚事务
• Mysql中的事务管理
  • mysql的事务默认自动打开，自动提交。
  • 每一条sql就是一个单独的事务，所以不需要事务开启、事务回滚、事务提交。
  • Mysql中事务的使用：
    • start transaction; —开启事务。以后的sql都在一个事务中。更改的内容不会自动提交。
    • rollback; —回滚事务(都失败的情况)。事务结束，全部失败，数据恢复到事务未开启之前的状态。
    • commit; —提交事务(都成功的情况)。事务结束，全部成功。
• JDBC中的事务管理（★★★★★）
  • Jdbc的事务介绍：
    • JDBC的事务管理，是通过Connection对象来完成的。
    • 当JDBC程序向数据库获得一个Connection对象时，默认情况下这个Connection对象会自动向数据库提交在它上面的发送的sql语句。
    • 若想关闭这种默认的提交方式，需要使用以下三个方法：
      • void setAutoCommit(boolean autoCommit):将此连接的自动提交模式设置为给定状态。
        • 参数为false，表示禁用自动提交模式，相当于strart transaction–开启事务。
        • 参数为true，表示自动提交。默认就是true。–一条sql语句就是一个事务.
        • 开启事务：con.setAutoCommit(false);
      • void rollback():取消在当前事务中进行的所有更改，并释放此Connection对象当前持有的所有数据库锁。
        • 相当于rollback(), 回滚事务。表示事务结束，取消当前事务的所有更改。
        • 注意：con.rollback()应该只在开启事务时使用，即con.setAutoCommit(false); 时才有效。
      • void commit():使所有上次提交/回滚后进行的更改成为持久更改，并释放此Connection对象当前持有的所有数据库锁。
        • 相当于commit(),提交事务。表示事务结束，更改有效。
        • 注意：con.commit()应该只在开启事务时使用，即conn.setAutoCommit(false); 时才有效。
  • Jdbc中事务的使用:
    • 1、开启事务：connection.setAutoCommit(false);
    • 2、执行一组sql语句;
    • 3、提交事务：connection.commit()/回滚事务：connection.rollback();
• 事务的回滚点
  • 事务的回滚点有什么作用？类似于单机游戏的存档和读档：
    • 1、如果没有游戏的存档功能，每次玩单机游戏都会从第一关重新开始。
    • 2、如果使用了游戏的存档功能，下次在玩游戏时，就会从存档处满血复活。
• 事务的回滚点介绍：
  • 接口 Savepoint:
    • public interface Savepoint
    • 保存点的表示形式，保存点是可以从Connection.rollback方法引用的当前事务中的点。将事务回滚到保存点时，在该保存点之后所作的全部更改都将被撤消。
    • 作用：JDBC使用接口Savapoint表示事务回滚点，当出现异常事务回滚时，使用savapoint更新回滚点之前的数据。
    • 如何设置事务的回滚点？connection.setSavepoint()
      • Savepoint setSavepoint():在当前事务中创建一个未命名的保存点(savepoint)，并返回表示它的新Savepoint对象。
    • 如何使用事务的回滚点把数据回滚到指定的位置？
      • void rollback(Savepoint savepoint):取消所有设置给定Savepoint对象之后进行的更改。
      • 把数据会滚到给定回滚点的位置。
  • 注意点：
    • 1、设置回滚点，必须事务开启之后。
    • 2、事务回滚，需要设置一个回滚点。
    • 3、事务回滚到回滚点之后，还需要提交事务，才能将回滚点之前的数据持久化到数据库。

事务的特性–ACID

• 在执行一组sql语句的操作过程中，这些操作要么都执行，要么都不执行，他是一个不可分割的工作单元。
• 数据库的事务必须具备ACID特性，ACID是指:
  • Atomicity（原子性）
    • 一个事务中所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行一样。
  • Consistensy（一致性）
    • 一个事务在执行之前和执行之后，数据库都必须处于一致性状态。如果事务成功的完成，那么执行成功的sql语句会将数据库中所有涉及到的数据进行更新。如果事务执行出现错误，那么数据库的所有变化将会被回滚（撤销），返回到原始状态。
  • Isolation（隔离性）
    • 多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离。
    • 多个事务之间是相互独立的，事务和事务之间不能相互干扰。
    • 所谓事务的隔离性是指两个操作的两个事务不能同时去修改数据库中的数据。必须等待一个事务执行完毕之后，另外一个事务再去执行。
  • Durability（持久性）
    • 持久性是指当数据库系统出现故障了，要确保已经提交的事务的更新是不会丢失的。即数据库中的数据的修改是永久性的。就算系统出现了故障，我们也可以使用数据库的备份和恢复来保证数据的修改。

      事务的隔离级别

• 如果不考虑事务的隔离性，由于事务的并发，将会出现以下问题：
  • 1、脏读
    • 指一个事务读取了另外一个事务 未提交的数据。
    • 一个事务读取了另一个事务没有提交的数据，非常严重。应当尽量避免脏读。
  • 2、不可重复读
    • 在一个事务内多次读取表中的数据，多次读取的结果不同。
    • 不可重复读强调数据内容的更新，一个事务内的多次查询结果不同。
    • 在一个事务内的两次查询得到了不同的结果，这叫做不可重复读。
    • 常用的sql语句的类型为update
  • 3、幻读（虚读）
    • 一个事务中多次读取的记录数不一致.
    • 指在一个事务中读取另一个事务插入或删除数据记录，导致当前事务读取的数据的记录数前后不一致。
    • 注意：mysql数据库本身，已经对虚读(幻读)做了优化处理。
  • 虚读和不可重复读的区别：
    • 虚读强调的是数据表记录数的变化，主要是insert和delete语句。
    • 不可重复读强调的是数据表内容的变化，主要是update语句。
• 数据库的隔离级别的设置
  • 数据库共定义了4种隔离级别（限制由高到低, 性能从低到高）：
    • serializable（串行化）：可避免脏读、不可重复读、虚读情况的发生。
    • repeatable read（可重复读）：可避免脏读、不可重复读，不可避免虚读。Mysql默认采用可重复读。
    • read committed（读已提交）：可避免脏读，不可避免不可重复读、虚读。Oracle默认采用读已提交。
    • read uncommitted（读未提交）：不可避免脏读、不可重复读、虚读。
  • 查询当前数据库的隔离级别：select @@tx_isolation;
  • 设置事务的隔离级别：set session transaction isolation level 事务隔离级别;
  • 为什么串行可以解决所有的问题？
    • 所有的问题其实都是由不同的事务并行执行引起的，所以改成所有事务依次执行（串行），当一个事务执行完毕之后再去执行另外一个事务，这样就能解决所有问题。
• 隔离级别的性能问题
  • 性能比较：
    • serializable 性能最差，多个事务排队执行。
    • serializable<repeatable read<read committed<read uncommitted;
  • 安全性比较：
    • serializable 安全性能最好，所有问题都可以避免。
    • serializable>repeatable read>read committed>read uncommitted;
  • 分析：
    • serializable 性能太差
    • read uncommitted 无法避免脏读，问题严重
  • 总结：不同的数据库厂商默认的隔离级别不同
    • mysql 的默认隔离级别 – repeatable read
    • oracle 的默认隔离级别 – read committed

      连接池

• 建立连接产生的问题：Jdbc的每次操作都要和数据库建立连接。
• 创建连接是非常耗费资源的，每次建立连接的时间都比执行CRUD的时间要长很多，频繁的创建和释放连接会引起大量的性能开销。
• 什么是连接池？
  • 创建一个连接的池子，然后在池子中先存放多个连接，比如5个，每次使用的时候去池子中获取，使用完毕之后再放回池子中，这样就避免了多次建立连接和释放连接造成的资源浪费。
• 这样做的好处是：可能有10000个人需要使用到连接，但是只需要在连接池中放置10个连接来回利用就可以了。

  传播行为

• 传播行为是方法之间调用事务采取的策略问题。