JavaWEB—JDBC学习及案例

概述

Java DataBase Connectivity，Java数据库连接

• JDBC是Java访问数据库的标准规范，真正怎么操作数据库还需要具体的实现类，也就是数据库驱动

• 每个数据库厂商根据自家数据库的通信格式编写好自己数据库的驱动，提供数据库驱动jar包

• 所以我们只需要会调用JDBC接口中的方法即可，数据库驱动由数据库厂商提供

使用步骤

1. 导入驱动jar包，mysql-connector-java-5.1.37-bin.jar
2. 注册驱动，mysql5之后可以省略
3. 获取数据库连接对象Connection
4. 定义sql，参数使用?作为占位符，如select * from user where username=? and password=?;
5. 获取执行sql语句的对象Statement或PreparedStatement对象，Connection.prepareStatement(String sql)
6. 给?赋值： setXxx(arg1, arg2)，arg1表示从1开始？的位置编号 ，arg2表示?的值
7. 执行sql，接受返回结果
8. 处理结果
9. 释放资源
   • 需要释放的对象：ResultSet结果集，Statement语句，Connection连接
   • 释放顺序：先开的后关，后开的先关，ResultSet -> Statement -> Connection
   • 放在哪个代码块中：finally块

五大对象

接口或类	作用
DriverManager类	①管理和注册数据库驱动 ②得到数据库连接对象Connection
Connection接口	连接对象，可用于创建Statement和PreparedStatement对象
Statement接口	SQL语句对象，用于将Sql语句发送给数据库服务器
PreparedStatement接口	SQL语句对象，是Statement的子接口
ResultSet接口	用于封装数据库查询的结果集，返回给客户端Java程序

DriverManager

加载和注册驱动

• Class.forName(数据库驱动实现类)：比如mysql厂商的"com.mysql.jdbc.Driver"

DriverManager类

• 加载注册驱动：告诉程序该使用哪一个数据库驱动jarstatic 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包可以省略注册驱动的步骤，即省略Class.forName(Xxx)

获取数据库连接

• Connection getConnection(String url, String user, String password)
  • url：连接的路径
    • 语法：jdbc:mysql://ip地址(域名):端口号/数据库名称?参数1=参数值1&参数2=参数值2...
    • 示例：jdbc:mysql://localhost:3306/db3
    • 如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称
  • user：用户名
  • password：密码

String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, username, password);

Connection

获取sql执行对象

• Statement createStatement()：创建一条SQL语句对象
• PreparedStatement prepareStatement(String sql)

管理事务

• 开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务
• 提交事务：commit()
• 回滚事务：rollback()

Statement

Statement对象作用

代表一条语句对象，发送SQL语句给服务器，用于执行静态SQL语句并返回它所生成结果的对象

方法

• boolean execute(String sql) ：可以执行任意的sql
• int executeUpdate(String sql) ：执行DML和DDL语句；返回影响的行数，>0的则执行成功，反之则失败
• ResultSet executeQuery(String sql) ：执行DQL语句，返回查询的结果集

执行DDL

/**
* 创建一张学生表
*/
Connection conn = null;
Statement statement = null;
try {
    // 1.创建连接
    String url = "jdbc:mysql://localhost:3306/test";
    String username = "root";
    String password = "root";
    conn = DriverManager.getConnection(url, username, password);
    // 2.通过连接对象得到语句对象
    statement = conn.createStatement();
    // 3.通过语句对象发送 SQL 语句给服务器
    // 4.执行 SQL
    statement.executeUpdate("create table student (id int PRIMARY key auto_increment, name varchar(20) not null, gender boolean, birthday date)");
    // 5.返回影响行数(DDL 没有返回值)
    System.out.println("创建表成功");
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    // 6.释放资源
    // 关闭之前要先判断
    if (statement != null) {
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

执行DML

/**
* 向学生表中添加 4 条记录，主键是自动增长
*/
// 1) 创建连接对象
Connection conn = DriverManager.getConnection(url, username, password);
// 2) 创建 Statement 语句对象
Statement statement = conn.createStatement();
// 3) 执行 SQL 语句：executeUpdate(sql)
int count = 0;
// 4) 返回影响的行数
count += statement.executeUpdate("insert into student values(null, '孙悟空', 1, '1993-03-24')");
count += statement.executeUpdate("insert into student values(null, '白骨精', 0, '1995-03-24')");
count += statement.executeUpdate("insert into student values(null, '猪八戒', 1, '1903-03-24')");
count += statement.executeUpdate("insert into student values(null, '嫦娥', 0, '1993-03-11')");
System.out.println("插入了" + count + "条记录");
// 5) 释放资源
statement.close();
conn.close();

ResultSet

作用：封装数据库查询的结果集，对结果集进行遍历，取出每一条记录

方法

• boolean next()：游标向下移动一行，判断当前行是否是最后一行末尾(是否没有数据)，如果是，则返回false，否则返回true
• getXxx(col)：获取Xxx类型数据
  • Xxx：代表数据类型 如getInt、getString、getLong
  • col：
    • int：代表列的编号，从1开始，如getString(1)
    • String：代表列名称， 如getDouble("balance")

注意

• 如果光标在第一行之前，使用rs.getXxx()获取列值，报错：Before start of result set
• 如果光标在最后一行之后，使用rs.getXxx()获取列值，报错：After end of result set
• 使用完毕以后要关闭结果集ResultSet，再关闭Statement，再关闭Connection

使用步骤

1. 游标向下移动一行

2. 判断是否有数据

3. 获取数据

   // 循环判断游标是否是最后一行末尾。
   while (rs.next()) {
       // 获取数据
       int id = rs.getInt(1);
       String name = rs.getString("name");
       double balance = rs.getDouble(3);
       System.out.println(id + "---" + name + "---" + balance);
   }
   

常用数据类型转换表

执行DQL

//1) 得到连接对象
Connection conn = DriverManager.getConnection(url, username, password);
//2) 得到语句对象
Statement statement = connection.createStatement();
//3) 执行 SQL 语句得到结果集 ResultSet 对象
ResultSet rs = statement.executeQuery("select * from student");
//4) 循环遍历取出每一条记录
while (rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");
    boolean gender = rs.getBoolean("gender");
    Date birthday = rs.getDate("birthday");
    //5) 输出的控制台上
    System.out.println("编号：" + id + ", 姓名：" + name + ", 性别：" + gender + ", 生日：" + birthday);
}
//6) 释放资源
rs.close();
statement.close();
connection.close();

PreparedStatement

SQL注入问题

• 在拼接sql时，有一些sql的特殊关键字参与字符串的拼接，会造成安全性问题
  • 输入用户随便，输入密码'a' or 'a' = 'a'
  • sql：select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a'
• 解决sql注入：使用PreparedStatement对象来解决，不能让用户输入的密码和SQL语句进行简单的字符串拼接
• PreparedStatement是Statement的子接口，继承于父接口中所有的方法，它是一个预编译的 SQL 语句，参数使用?作为占位符
• 后期都会使用PreparedStatement来完成增删改查的所有操作，原因如下：
  • prepareStatement会先将SQL语句发送给数据库预编译，PreparedStatement会引用着预编译后的结果
  • 可以多次传入不同的参数给PreparedStatement对象并执行，减少SQL编译次数，提高效率
  • 安全性更高，没有SQL注入的隐患
  • 提高了程序的可读性

执行原理

API

• Connection创建PreparedStatement对象：PreparedStatement prepareStatement(String sql)，指定预编译的SQL语句，SQL语句中使用占位符?
• int executeUpdate()：执行DML和DDL，增删改的操作，返回影响的行数
• ResultSet executeQuery()：执行DQL，查询的操作，返回结果集

执行DQL：

/** 列表查询 */
List<Student> studentList = new ArrayList<>();
Connection connection = JdbcUtils.getConnection();
PreparedStatement ps = connection.prepareStatement("select * from student");
// 没有参数替换
ResultSet resultSet = ps.executeQuery();
while (resultSet.next()) {
    // 每次循环是一个学生对象
    Student student = new Student();
    // 封装成一个学生对象
    student.setId(resultSet.getInt("id"));
    student.setName(resultSet.getString("name"));
    student.setGender(resultSet.getBoolean("gender"));
    student.setBirthday(resultSet.getDate("birthday"));
    // 把数据放到集合中
    studentList.add(student);
}
// 关闭连接
JdbcUtils.close(connection, ps, resultSet);
// 使用数据
for (Student stu : students) {
    System.out.println(stu);
}

执行DML：

// 插入记录
private static void insert() throws SQLException {
    Connection connection = JdbcUtils.getConnection();
    PreparedStatement ps = connection.prepareStatement("insert into student values(null,?,?,?)");
    ps.setString(1, "小白龙");
    ps.setBoolean(2, true);
    ps.setDate(3, java.sql.Date.valueOf("1999-11-11"));
    int row = ps.executeUpdate();
    System.out.println("插入了" + row + "条记录");
    JdbcUtils.close(connection, ps);
}
// 更新记录: 换名字和生日
private static void update() throws SQLException {
    Connection connection = JdbcUtils.getConnection();
    PreparedStatement ps = connection.prepareStatement("update student set name=?, birthday=? where id=?");
    ps.setString(1, "黑熊怪");
    ps.setDate(2, java.sql.Date.valueOf("1999-03-23"));
    ps.setInt(3, 5);
    int row = ps.executeUpdate();
    System.out.println("更新" + row + "条记录");
    JdbcUtils.close(connection,ps);
}

// 删除记录: 删除id=5的记录
private static void delete() throws SQLException {
    Connection connection = JdbcUtils.getConnection();
    PreparedStatement ps = connection.prepareStatement("delete from student where id=?");
    ps.setInt(1, 5);
    int row = ps.executeUpdate();
    System.out.println("删除了" + row + "条记录");
    JdbcUtils.close(connection,ps);
}

封装JdbcUtils

分析

• 如果一个功能经常要用到，建议把这个功能做成一个工具类，可以在不同的地方重用
• 抽取：注册驱动的方法
• 抽取：获取连接对象的方法
  • 需求：不想传递参数，还得保证工具类的通用性
  • 解决：配置文件jdbc.properties配置连接属性(driver,url,username,password等)
• 抽取：释放资源的方法

代码

public class JdbcUtils {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    
    /** 文件的读取: 只需要读取一次即可拿到这些值,使用静态代码块 */
    static{
        // 读取资源文件，获取值。
        try {
            // 1.创建Properties集合类。
            Properties properties = new Properties();
            // 获取src路径下的文件的方式 -> ClassLoader类加载器
            ClassLoader classLoader = JdbcUtils.class.getClassLoader();
            URL res = classLoader.getResource("jdbc.properties");
            // 2.加载文件
            properties.load(new FileReader(res.getPath()));
            // 3.获取数据，赋值
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            // 4.注册驱动
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return 连接对象
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

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

用户登录案例

/**
 1. 通过键盘录入用户名和密码
 2. 判断用户是否登录成功
*/
public static void main(String[] args) {
    // 1.键盘录入，接受用户名和密码
    Scanner sc = new Scanner(System.in);
    System.out.println("请输入用户名：");
    String username = sc.nextLine();
    System.out.println("请输入密码：");
    String password = sc.nextLine();
    // 2.调用方法
    boolean flag = login(username, password);
    // 3.判断结果，输出不同语句
    System.out.println(flag?"登录成功":"用户名或密码错误");
}

/** 登录 */
public static boolean login(String username, String password) {
    if(username == null || password == null){
        return false;
    }
    // 连接数据库判断是否登录成功
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    // 1.获取连接
    try {
        conn =  JDBCUtils.getConnection();
        /*** 可能存在SQL注入的风险，因此改进为下面的ps
        // 2.定义sql
        String sql = "select * from user where username = '"+username+"' and password = '"+password+"' ";
        // 3.获取执行sql的对象
        stmt = conn.createStatement();
        // 4.执行查询
        rs = stmt.executeQuery(sql);
        */
        // 写成登录SQL语句，没有单引号
        String sql = "select * from user where name=? and password=?";
        // 得到语句对象
        PreparedStatement ps = connection.prepareStatement(sql);
        // 设置参数
        ps.setString(1, name);
        ps.setString(2, password);
        rs = ps.executeQuery();
        //5.判断
        return rs.next();// 如果有下一行则返回true
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.close(rs,stmt,conn);
    }
    return false;
}

JDBC事务

事务：一个包含多个步骤的业务操作，如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败

操作：①开启事务 ②提交事务 ③回滚事务

Connection对象管理事务

• void setAutoCommit(boolean autoCommit)：开启事务，调用该方法设置参数为false，相当于开启事务
• void commit()：提交事务，当所有sql都执行完提交事务
• void rollback()：回滚事务，在catch中回滚事务

步骤

1. 获取连接
2. 开启事务
3. 获取PreparedStatement
4. 使用PreparedStatement执行两次更新操作
5. 正常情况下提交事务，出现异常回滚事务
6. 关闭资源

示例

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement ps = null;

    try {
        // 1.获取连接
        conn = JDBCUtils.getConnection();
        // 开启事务
        conn.setAutoCommit(false);
        
        // 2.定义sql
        //  张三 - 500
        String sql = "update user set balance=balance-? where id=?";
        // 3.获取执行sql对象
        ps = conn.prepareStatement(sql);
        // 4.设置参数
        ps.setDouble(1, 500);
        ps.setInt(2, 1);
        // 5.执行sql
        ps.executeUpdate();
        
        // 手动制造异常
        // int i = 3/0;
        
        // 2.定义sql
        //  李四 + 500
        sql = "update user set balance=balance+? where id=?";
        // 3.获取执行sql对象
        ps = conn.prepareStatement(sql);
        // 4.设置参数
        ps.setDouble(1, 500);
        ps.setInt(2, 2);
        // 5.执行sql
        ps.executeUpdate();
        
        // 提交事务
        conn.commit();
        System.out.println("转账成功");
    } catch (Exception e) {
        // 事务回滚
        try {
            if(conn != null) {
                conn.rollback();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        e.printStackTrace();
    } finally {
        JDBCUtils.close(ps, conn);
    }
}

数据连接池

概述

概念：其实就是一个集合，存放数据库连接的容器。当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器

好处：①节约资源 ②用户访问高效

语法

• 获取连接：getConnection()
• 归还连接：Connection.close()，如果连接对象Connection是从连接池中获取的，那么调用该方法就不再关闭连接，而是归还连接到连接池

一般我们不去实现它，有数据库厂商来实现

• C3P0：数据库连接池技术
• Druid：数据库连接池实现技术，由阿里巴巴提供

C3P0

数据库连接池技术

0. 导入jar包 (两个) c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar，也不要忘记导入数据库驱动jar包

1. 定义c3p0.properties或c3p0-config.xml的配置文件，放在src目录下

2. 创建数据库连接池对象ComboPooledDataSource

3. 获取连接 getConnection()

// 1.创建数据库连接池对象
DataSource dataSource = new ComboPooledDataSource();
// 2.获取连接对象
Connection conn = dataSource.getConnection();

Druid

步骤：

0. 导入jar包druid-1.0.9.jar

1. 定义配置文件：任意名称任意目录下的properties格式配置文件

2. 加载配置文件：Properties对象

3. 通过工厂获取数据库连接池对象： DruidDataSourceFactory

4. 获取连接：getConnection()

// 3.加载配置文件
Properties properties = new Properties();
InputStream is = DruidDemo.class.getClassLoader()
    .getResourceAsStream("druid.properties");
properties.load(is);
// 4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
// 5.获取连接
Connection conn = dataSource.getConnection();

封装DataSourceUtils

/**	0. 定义一个类 JDBCUtils
	1. 提供静态代码块加载配置文件，初始化连接池对象
	2. 提供方法
   	 - 获取连接方法：通过数据库连接池获取连接
   	 - 释放资源
   	 - 获取连接池的方法
*/
public class DataSourceUtils {
    // 1.定义成员变量 DataSource
    private static DataSource dataSource;

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

    /** 获取连接 */
    public static Connection getConnection() throws SQLException {
        return dataSource.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 dataSource;
    }
}

SpringJDBC

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

步骤

1. 导入jar包

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

   JdbcTemplate template = new JdbcTemplate(dataSource);
   

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

   • update()：执行DML语句
   • queryForMap()：查询结果并将结果集封装为map集合，将列名作为key，将值作为value将这条记录封装为一个map集合；注意这个方法查询的结果集长度只能是1，超过1报错
   • queryForList()：查询结果将结果集封装为list集合；注意将每一条记录封装为一个Map集合，再将Map集合装载到List集合中
   • query()：查询结果，将结果封装为JavaBean对象，RowMapper参数：
     • 一般使用BeanPropertyRowMapper实现类，可以实现数据到JavaBean的自动封装
     • new BeanPropertyRowMapper<类型>(类型.class)
   • queryForObject：查询结果，将结果封装为对象，一般用于聚合函数的查询

示例

public class JdbcTemplateDemo {
    // 1.获取JDBCTemplate对象
    private final JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    
    // 修改1号数据的 salary 为 10000
    @Test
    public void test1() {
        String sql = "update emp set salary=10000 where id=1001";
        int count = jdbcTemplate.update(sql);
        System.out.println(count);
    }

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

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

    /** 查询id为1001的记录，将其封装为Map集合
		注意: 这个方法查询的结果集长度只能是1 */
    @Test
    public void test4() {
        String sql = "select * from emp where id = ? or id = ?";
        Map<String, Object> map = jdbcTemplate.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}
    }

    // 查询所有记录，将其封装为List
    @Test
    public void test5() {
        String sql = "select * from emp";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        for (Map<String, Object> stringObjectMap : list)
            System.out.println(stringObjectMap);
    }

    // 查询所有记录，将其封装为Emp对象的List集合
    @Test
    public void test6() {
        String sql = "select * from emp";
        List<Emp> list = jdbcTemplate.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);
        }
    }

    // 查询所有记录，将其封装为Emp对象的List集合
    @Test
    public void test6_2() {
        String sql = "select * from emp";
        List<Emp> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
        for (Emp emp : list) {
            System.out.println(emp);
        }
    }

    // 查询总记录数
    @Test
    public void test7() {
        String sql = "select count(id) from emp";
        Long total = jdbcTemplate.queryForObject(sql, Long.class);
        System.out.println(total);
    }
}