JDBC

概念

• Java DataBase Connectivity(Java数据库连接)：Java语言操作数据库
• 本质：其实是官方（SUN公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动和jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码时驱动jar包中的实现类。

步骤

1. 导入驱动jar包
   • 复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下
   • 右键–>Add As Library
2. 注册驱动 DriverManager
3. 获取数据库连接对象 Connection
4. 定义SQL
5. 获取执行SQL语句的对象 Statement
6. 执行SQL，接收返回结果
7. 处理结果
8. 释放资源

对象

• DriverManager：驱动管理对象
• Connection：数据库连接对象
• Statement：执行SQL的对象
• ResultSet：结果集对象
• PreparedStatement：执行SQL的对象

DriverManager驱动管理对象

• 注册驱动：告诉程序该使用哪一个数据库驱动jar

  • 方法：static void registerDriver(Driver driver):注册与给定的驱动程序DriverManager

  • 写代码时使用：Class.forName("com.mysql.jdbc.Driver");

    // 通过查看源码发现：在com.mysql.jdbc.Driver类中存在静态代码块
    static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }

  • 注意：mysql5之后的驱动jar包可以省略注册驱动的步骤

• 获取数据库连接（DriverManager）

  • 方法：static Connection getConnection(String url,String user,String password)
  • 参数：
    • url：指定连接的路径
    • 语法：jdbc:mysql://ip地址(域名):端口号/数据库名称

      如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称

    • user：用户名
    • password：密码

Connection数据库连接对象

• 功能：
  • 获取执行SQL的对象
    • Statement createStatement()
    • PreparedStatement prepareStatement(String sql)
  • 管理事务
    • 开启事务：void setAutoCommit(boolean autoCommit):调用改方法设置参数为false。即开启事务
    • 提交事务：void commit()
    • 回滚事务：void rollback()

Statement执行静态SQL的对象

• 执行SQL
  • boolean execute(String sql):可以执行任意的sql
  • int executeUpdate(String sql):执行DML(insert、update、delete)语句，DDL(create、alter、drop)语句
    • 返回值：影响的行数。可以通过这个影响的行数判断DML语句是否执行成功，返回值>0则执行成功。
  • ResultSet executeQuery(String sql):执行DQL(select)语句
• 样例

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.sql.Statement;
  
  public class JdbcDemo01 {
      public static void main(String[] args) {
          // 导入驱动
          Connection conn = null;
          Statement smt = null;
  
  
          try {
              // 静态SQL语句
              String s1 = "insert into emp values ()";
              String s2 = "delete from emp where salary>8000";
              String s3 = "update emp set gender='女' where name='jdbc'";
              // 注册驱动
              Class.forName("com.mysql.jdbc.Driver");
              // 获取数据库连接对象Connection
              conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo",
                      "root", "root");
              // 获取Statement对象
              smt = conn.createStatement();
              // 执行sql语句
              int r1 = smt.executeUpdate(s1);
              int r2 = smt.executeUpdate(s2);
              int r3 = smt.executeUpdate(s3);
              System.out.println(r1);
              System.out.println(r2);
              System.out.println(r3);
  
          } catch (ClassNotFoundException e) {
              e.printStackTrace();
          } catch (SQLException e) {
              e.printStackTrace();
          } finally {
              // 释放资源
              // 避免空指针异常
              if (smt != null) {
                  try {
                      smt.close();
                  } catch (SQLException e) {
                      e.printStackTrace();
                  }
              }
              if (conn != null) {
                  try {
                      conn.close();
                  } catch (SQLException e) {
                      e.printStackTrace();
                  }
              }
          }
      }
  
  }

ResultSet结果集对象

• 封装查询结果
• boolean next()：游标向下移动一行，判断当前行是否是最后一行末尾（是否有数据）。如果是，则返回false，如果不是返回true；
• XXX getXXX(参数)：获取一列数据
  • XXX：代表数据类型， 如：int getInt()
  • 参数：
    • int：代表列的编号，如： getString(1)
    • String：代表列名称，如： getString(“name”)
• 使用步骤：
  1. 游标向下移动一行
  2. 判断是否有数据
  3. 获取数据

     ResultSet resultSet = smt.executeQuery(s1);
     while(resultSet.next()){
         System.out.println(resultSet.getString("name"));
     }

PreparedStatement执行SQL语句的对象

• SQL注入问题：在拼接SQL时，有一些SQL的特殊关键字参与字符串的拼接，会造成安全性问题

  1. 输入用户随便，输入密码：a’ or ‘a’ = ‘a’
  2. sql：select * from user where username = ‘fhdsjkf’ and password = ‘a’ or ‘a’ = ‘a’

• 解决方案：使用PreparedStatement对象

• 预编译的SQL：参数使用？作为占位符
• 使用步骤：
  1. 导入驱动jar包
  2. 注册驱动
  3. 获取数据库连接对象Connection
  4. 定义SQL

     sql的参数使用？作为占位符。 如：select * from user where username = ? and password = ?;

  5. 获取执行SQL语句的对象PreparedStatement
     • Connection.prepareStatement(String sql)
  6. 给？赋值：

     方法：setXXX(参数1，参数2)

     • 参数1：?的位置编号，从1开始
     • 参数2：?的值

  7. 执行SQL：接受返回值结果，不需要传递SQL语句
  8. 处理结果
  9. 释放资源
• 注意：使用PreparedStatement来完成增删改查的所有操作
  1. 可以防止SQL注入
  2. 效率更高
  3. 语句中使用了占位符，规定了sql语句的结构。用户可以设置”?”的值，但是不能改变sql语句的结构，因此解决了SQL注入

JDBC工具类：JDBCUtils

• 目的：简化书写

• 分析：

  1. 注册驱动抽取

  2. 抽取一个方法获取连接对象

     • 需求：不想传递参数（麻烦），还得保证工具类的通用性。
     • 解决：配置文件jdbc.properties（文件的读取，只需要读取一次即可拿到这些值。使用静态代码块）

       url=
       user=
       password=
       driver=

  3. 抽取一个方法释放资源

     package com.nogizaka.jdbc;
     
     import java.io.FileReader;
     import java.io.IOException;
     import java.net.URL;
     import java.sql.*;
     import java.util.Properties;
     
     public class JDBCUtils {
         private static String url;
         private static String user;
         private static String password;
         private static String driver;
         /*
         文件的读取，只需要读取一次即可拿到这些值。使用静态代码块
         * */
         static {
             // 读取资源文件
             try {
                 // 1 创建Properties类
                 Properties prop = new Properties();
                 // 获取src路径下的文件的方式：ClassLoader 类加载器
                 ClassLoader classLoader = JDBCUtils.class.getClassLoader();
                 URL res = classLoader.getResource("jdbc.properties");
                 // 获取字符串路径
                 String path = res.getPath();
     
                 // 2 加载文件
                 prop.load(new FileReader(path));
                 // 3 获取数据 赋值
                 url = prop.getProperty("url");
                 user = prop.getProperty("user");
                 password = prop.getProperty("password");
                 driver = prop.getProperty("driver");
                 // 4 注册驱动
                 Class.forName(driver);
             } catch (IOException e) {
                 e.printStackTrace();
             } catch (ClassNotFoundException e) {
                 e.printStackTrace();
             }
         }
     
         // 获取数据库连接
         public static Connection getConnection() throws SQLException {
     
             return DriverManager.getConnection(url, user, password);
         }
     
         /**
          * 释放资源
          *
          * @param stmt
          * @param conn
          */
         public static void close(Statement stmt, Connection conn) {
             if (stmt != null) {
                 try {
                     stmt.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }
             if (conn != null) {
                 try {
                     conn.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }
         }
     
         public static void close(ResultSet res, Statement stmt, Connection conn) {
             if (res != null) {
                 try {
                     res.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }
             if (stmt != null) {
                 try {
                     stmt.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }
             if (conn != null) {
                 try {
                     conn.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }
         }
     }

• 注意：

  • properties文件放在src目录下
  • 获取src路径下的文件的方式：ClassLoader 类加载器

    ClassLoader classLoader = JDBCUtils.class.getClassLoader();
    URL res = classLoader.getResource("jdbc.properties");
    // 获取字符串路径
    String path = res.getPath();

JDBC事务控制

• 使用Connection对象来管理事务
  • 开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务

    在执行sql之前开启事务

  • 提交事务：commit()

    当所有sql都执行完提交事务

  • 回滚事务：rollback()

    在catch中回滚事务

数据库连接池

• 概念：其实就是一个容器（集合），存放数据库连接的容器。当系统初始化好后，容器被创建，容器中会申请一些连接对象当用户来访问数据库时，从容器中获取连接对象，用户访问完以后，会将连接对象归还给容器。
• 好处：节约资源，提供访问效率
• 实现：
  • 标准接口：javax.sql.DataSource
  • 方法：
    • 获取连接：getConnection()
    • 归还连接：Connection.close() 如果连接对象Connection是从连接池中获取的，那么调用Connection.close()方法，则不会再关闭连接，而是归还连接
  • 一般由数据库厂商实现
    • C3P0:数据库连接池技术
    • Druid:数据库连接池实现技术（阿里）

C3P0 数据库连接池技术

• 步骤：
  1. 导入jar包
     • c3p0-0.9.5.2.jar
     • mchange-commons-java-0.2.12.jar
  2. 定义配置文件：
     • 名称：c3p0.properties 或者 c3p0-config.xml
     • 路径：src目录下
  3. 创建核心对象 数据率连接池对象 new ComboPooledDataSource()
  4. 获取连接：getConnection()

// 创建数据库连接池对象
DataSource ds = new ComboPooledDataSource();
// 获取连接对象,getConnection()可以传递配置名，不同配置效果不同，不传使用默认配置
Connection conn = ds.getConnection();
// 归还连接
conn.close();

• 配置文件：c3p0-config.xml

  <!-- 连接池参数 -->
  <!--初始化申请的连接数-->
  <property name="initialPoolSize">5</property>
  <!--最大连接数量-->
  <property name="maxPoolSize">10</property>
  <!--超时时间-->
  <property name="checkoutTimeout">3000</property>

Druid 数据库连接池实现技术

• 步骤：
  1. 导入jar包
     • druid-1.0.9.jar
  2. 定义配置文件
     • Properties
     • 可以放在任意目录下
  3. 加载配置文件
  4. 获取数据库连接池对象：通过工厂来获取 DruidDataSourceFactory.createDataSource()
  5. 获取连接：getConnection()

// 定义配置文件
// 加载配置文件
Properties prop = new Properties();
// 将properties文件转换为字节输入流
InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
// 读取属性列表(键和元素对)从输入字节流。
prop.load(is);
// 获取连接池对象,通过工厂来获取，参数为配置文件
DataSource ds = DruidDataSourceFactory.createDataSource(prop);
// 获取连接
Connection conn = ds.getConnection();
System.out.println(conn);

• 定义工具类
  1. 定义一个类 JDBCUtils
  2. 提供静态代码块加载配置文件，初始化连接池对象
  3. 提供方法
     1. 获取连接方法：通过数据库连接池获取连接
     2. 释放资源
     3. 获取连接池的方法

public class JDBCUtils {
    //1.定义成员变量 DataSource
    private static DataSource ds ;

    static{
        try {
            //1.加载配置文件
            Properties pro = new Properties();
            pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            //2.获取DataSource
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
        * 获取连接
        */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
        * 释放资源
        */
    public static void close(Statement stmt,Connection conn){

        close(null,stmt,conn);
    }


    public static void close(ResultSet rs , Statement stmt, Connection conn){

        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();//归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /**
        * 获取连接池方法
        */

    public static DataSource getDataSource(){
        return  ds;
    }
}

Spring JDBC:JDBC Template

• Spring框架对JDBC的简单封装，提供了一个JDBCTemplate对象简化JDBC的开发

• 步骤

  1. 导入jar包

     • commons-logging-1.2.jar
     • spring-beans-5.0.0.RELEASE.jar
     • spring-core-5.0.0.RELEASE.jar
     • spring-jdbc-5.0.0.RELEASE.jar
     • spring-tx-5.0.0.RELEASE.jar

  2. 创建JdbcTemplate对象，依赖于数据源DataSource

     • JdbcTemplate template = new JdbcTemplate(ds);

  3. 调用JdbcTemplate的方法来完成CRUD的操作

     • update()：执行DML语句。增、删、改语句

     • queryForMap()：查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合

       注意：这个方法查询的结果集长度只能是1

     • queryForList()：查询结果将结果集封装为list集合

       注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中

     • query()：查询结果，将结果封装为JavaBean对象

       • query的参数：RowMapper
       • 一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装
       • new BeanPropertyRowMapper<类型>(类型.class)

     • queryForObject()：查询结果，将结果封装为对象

       一般用于聚合函数的查询

/* 需求：
1. 修改1号数据的 salary 为 10000
2. 添加一条记录
3. 删除刚才添加的记录
4. 查询id为1的记录，将其封装为Map集合
5. 查询所有记录，将其封装为List
6. 查询所有记录，将其封装为Emp对象的List集合
7. 查询总记录数
*/

public class JdbcTemplateDemo2 {

    //Junit单元测试，可以让方法独立执行

    //1. 获取JDBCTemplate对象
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    /**
        * 1. 修改1号数据的 salary 为 10000
        */
    @Test
    public void test1(){

        //2. 定义sql
        String sql = "update emp set salary = 10000 where id = 1001";
        //3. 执行sql
        int count = template.update(sql);
        System.out.println(count);
    }

    /**
        * 2. 添加一条记录
        */
    @Test
    public void test2(){
        String sql = "insert into emp(id,ename,dept_id) values(?,?,?)";
        int count = template.update(sql, 1015, "郭靖", 10);
        System.out.println(count);

    }

    /**
        * 3.删除刚才添加的记录
        */
    @Test
    public void test3(){
        String sql = "delete from emp where id = ?";
        int count = template.update(sql, 1015);
        System.out.println(count);
    }

    /**
        * 4.查询id为1001的记录，将其封装为Map集合
        * 注意：这个方法查询的结果集长度只能是1
        */
    @Test
    public void test4(){
        String sql = "select * from emp where id = ? or id = ?";
        Map<String, Object> map = template.queryForMap(sql, 1001,1002);
        System.out.println(map);
        //{id=1001, ename=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}

    }

    /**
        * 5. 查询所有记录，将其封装为List
        */
    @Test
    public void test5(){
        String sql = "select * from emp";
        List<Map<String, Object>> list = template.queryForList(sql);

        for (Map<String, Object> stringObjectMap : list) {
            System.out.println(stringObjectMap);
        }
    }

    /**
        * 6. 查询所有记录，将其封装为Emp对象的List集合
        */

    @Test
    public void test6(){
        String sql = "select * from emp";
        List<Emp> list = template.query(sql, new RowMapper<Emp>() {

            @Override
            public Emp mapRow(ResultSet rs, int i) throws SQLException {
                Emp emp = new Emp();
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept_id = rs.getInt("dept_id");

                emp.setId(id);
                emp.setEname(ename);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setJoindate(joindate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(dept_id);

                return emp;
            }
        });


        for (Emp emp : list) {
            System.out.println(emp);
        }
    }

    /**
        * 6. 查询所有记录，将其封装为Emp对象的List集合
        */

    @Test
    public void test6_2(){
        String sql = "select * from emp";
        List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
        for (Emp emp : list) {
            System.out.println(emp);
        }
    }

    /**
        * 7. 查询总记录数
        */

    @Test
    public void test7(){
        String sql = "select count(id) from emp";
        Long total = template.queryForObject(sql, Long.class);
        System.out.println(total);
    }

}