"Java SE 07 目录 [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] [链接] JdbcTemplate 执行 DML 语句 JdbcTemplate ...."

someone1
AI专业者开发社区 1 号成员
AI开发者社区 • 0 回帖 • 2 浏览 • 4 个月前

Java SE 07(JDBC、数据库连接池、Spring中的JdbcTemplate类)

Java SE 07

一、JDBC

JDBC 概述

JDBC(Java Database Connectivity)，全称 Java 数据库连接，就是使用 Java 语言操作数据库
JDBC 本质：是官方定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动 jar 包。开发者可以使用这套接口 (JDBC) 编程，真正执行的代码是驱动 jar 包中的实现类

JDBC 使用步骤：

导入驱动 jar 包 mysql-connector-java-5.1.37-bin.jar

idea 中操作步骤：

1 复制 jar 包到项目目录下的自定义文件夹下
2 右键这个文件夹 add as library
注册驱动
获取数据库连接对象 Connection
定义 sql
获取执行 sql 语句的对象 Statement
执行 sql，接收返回结果
处理结果
释放资源

public class JdbcDemo01 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        // 注册驱动 jar 包，在 MySQL5 之后可省略不写, 原因：在 jar 包中 META-INF 文件夹中 services 下 Driver 类中有注册驱动 jar 包 com.mysql.jdbc.Driver
        Class.forName("com.mysql.jdbc.Driver");
    <span class="token class-name">Connection</span> cnn <span class="token operator">=</span> <span class="token class-name">DriverManager</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token string">"jdbc:mysql://localhost:3306/db2"</span><span class="token punctuation">,</span> <span class="token string">"root"</span><span class="token punctuation">,</span> <span class="token string">"root"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token class-name">Statement</span> stmt <span class="token operator">=</span> cnn<span class="token punctuation">.</span><span class="token function">createStatement</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">"UPDATE emp SET dept_id = 10 WHERE id = 1001"</span><span class="token punctuation">;</span>
    <span class="token keyword">int</span> count <span class="token operator">=</span> stmt<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>count<span class="token punctuation">)</span><span class="token punctuation">;</span>
    stmt<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    cnn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

}

代码中的各个对象：

DriverManager：驱动管理对象

功能：
1. 注册驱动
  
  DriverManager 中的静态方法
  
  static void registerDriver(Driver driver)使用 DriverManager 注册给定的驱动程序
  在 com.mysql.jdbc.Driver 的源码中有静态代码块，使用Class.forName会将该类加载，同时执行这个静态代码块，从而完成驱动的注册
```
static {
 try {
     java.sql.DriverManager.registerDriver(new Driver());
 } catch (SQLException E) {
     throw new RuntimeException("Can't register driver!");
 }
}
```
2. 获取数据库连接
  
  DriverManager 中的静态方法
  
  static Connection getConnection(String url, String user, String password)
  
  方法参数：url: 指定连接的路径， user：用户名，password：密码
  
  url 的格式：jdbc:mysql://ip地址(域名):端口号/数据库名称
  
  如果连接的是本机 mysql 服务器，并且 mysql 服务默认端口是 3306，
  
  url 可以简写为jdbc:mysql:///数据库名称
Connection：数据库连接对象

Connection 类中的方法
1. 获取执行 sql 的对象
  
  Statement createStatement()
  
  PreparedStatement prepareStatement(Stirng sql)
2. 管理事务
  - 开启事务
    
    setAutoCommit(boolean autocommit)
    
    调用该方法设置参数为 false，就是开启事务
  - 提交事务
    
    commit()
  - 回滚事务
    
    rollback()
Statement：执行 sql 的对象

Statement 类中的方法
1. boolean execuate(String sql)可以执行任意的 sql
2. int execuateUpdate(String sql)执行 DML(常用)、DDL(不常用)
  
  若传入 DML 的返回值：sql 语句执行后影响的行数，可以通过这个影响的行数判断 DML 语句是否执行成功，返回值 >0 执行成功，否则执行失败。若传入的是 DDL 则 return nothing
3. ResultSet executeQuery(String sql)执行 DQL 语句 (select)
ResultSet：结果集对象
PreparedStatement：执行 sql 的对象

JDBC 中使用 insert 语句

示例 Code

public class JdbcDemo02 {
    public static void main(String[] args) {
        Statement stat = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String sql = "INSERT INTO emp VALUES (1016,'Jeff', 3, 1004,'2021-7-5', 9000, null, 30)";
            conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");
            stat = conn.createStatement();
            int num = stat.executeUpdate(sql);
            if(num > 0) {
                System.out.println("insert successfully!");
            } else {
                System.out.println("fail to insert!");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
          // 这里使用 finally 来释放资源，不管 try 中的程序是否正常执行，最终到这里会释放资源
        } finally {
			// 若 try 中的程序没有正常释放资源，这里的引用类型的对象值仍为 null，所以先判断防止空指针异常
            if(stat != null) {
                try {
                    stat.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        <span class="token keyword">if</span><span class="token punctuation">(</span>conn <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token keyword">try</span> <span class="token punctuation">{</span>
                conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
                throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
        <span class="token punctuation">}</span>

    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

}
/*output: 在数据库 db2 的 emp 表中成功地插入了一条指定的数据
insert successfully!
*/

JDBC 中使用 update 语句

示例 Code

// 方法同上，只是改变了 sql 语句
public class JdbcDemo03 {
    <span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">main</span><span class="token punctuation">(</span><span class="token class-name">String</span><span class="token punctuation">[</span><span class="token punctuation">]</span> args<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token class-name">Statement</span> stat <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
        <span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            <span class="token class-name">Class</span><span class="token punctuation">.</span><span class="token function">forName</span><span class="token punctuation">(</span><span class="token string">"com.mysql.jdbc.Driver"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">"UPDATE emp SET ename = 'Jack' WHERE id = 1016"</span><span class="token punctuation">;</span>
            conn <span class="token operator">=</span> <span class="token class-name">DriverManager</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token string">"jdbc:mysql:///db2"</span><span class="token punctuation">,</span> <span class="token string">"root"</span><span class="token punctuation">,</span> <span class="token string">"root"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            stat <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">createStatement</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token keyword">int</span> count <span class="token operator">=</span> stat<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token keyword">if</span><span class="token punctuation">(</span>count <span class="token operator">&gt;</span> <span class="token number">0</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span><span class="token string">"update successfully!"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span> <span class="token keyword">else</span> <span class="token punctuation">{</span>
                <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span><span class="token string">"fail to update!"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">ClassNotFoundException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span> <span class="token keyword">finally</span> <span class="token punctuation">{</span>

            <span class="token keyword">if</span><span class="token punctuation">(</span>stat <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                <span class="token keyword">try</span> <span class="token punctuation">{</span>
                    stat<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
                    throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                <span class="token punctuation">}</span>
            <span class="token punctuation">}</span>

            <span class="token keyword">if</span><span class="token punctuation">(</span>conn <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                <span class="token keyword">try</span> <span class="token punctuation">{</span>
                    conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
                    throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                <span class="token punctuation">}</span>
            <span class="token punctuation">}</span>

        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>

}
/*output: 在数据库 db2 的 emp 表中成功地修改了一条指定的字段值
update successfully!
*/

JDBC 中使用 delete 语句

示例 Code

// 方法同上，只是改变了 sql 语句
public class JdbcDemo04 {
    public static void main(String[] args) {
        Statement stat = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String sql = "DELETE FROM emp WHERE id = 1016";
            conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");
            stat = conn.createStatement();
            int count = stat.executeUpdate(sql);
            if(count > 0) {
                System.out.println("delete successfully!");
            } else {
                System.out.println("fail to delete!");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        <span class="token keyword">if</span><span class="token punctuation">(</span>stat <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token keyword">try</span> <span class="token punctuation">{</span>
                stat<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
                throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
        <span class="token punctuation">}</span>

        <span class="token keyword">if</span><span class="token punctuation">(</span>conn <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token keyword">try</span> <span class="token punctuation">{</span>
                conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
                throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
        <span class="token punctuation">}</span>

    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

}
/*output: 在数据库 db2 的 emp 表中成功地删除了一条指定的数据
delete successfully!
*/

JDBC 中使用 DDL 语句

示例 Code

// 方法几乎与上面例子，只是改变了 sql 语句
// 去除了 executeUpdate 方法的返回值判断，因为此时传入的参数是 DDL 的 sql，返回 nothing
public class JdbcDemo05 {
    public static void main(String[] args) {
        Statement stat = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String sql = "CREATE TABLE tab_test(id INT, name VARCHAR(32))";
            conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");
            stat = conn.createStatement();
            int count = stat.executeUpdate(sql);
            System.out.println("count =" + count);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        <span class="token keyword">if</span><span class="token punctuation">(</span>stat <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token keyword">try</span> <span class="token punctuation">{</span>
                stat<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
                throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
        <span class="token punctuation">}</span>

        <span class="token keyword">if</span><span class="token punctuation">(</span>conn <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token keyword">try</span> <span class="token punctuation">{</span>
                conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
                throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
        <span class="token punctuation">}</span>

    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

}
/*output: 在数据库 db2 中创建新表 tab_test
count = 0
*/

ResultSet 类

通过 Statement 类的 executeQuery 方法返回 ResultSet 类的对象

ResultSet executeQuery(String sql)执行 DQL 语句 (select)
ResultSet 类中的方法
1. boolean next()：游标向下移动一行，判断当前行是否是最后一行的末尾 (是否有数据)，如果是返回 false，如果不是返回 true
  使用步骤：
  1. 游标向下移动一行
  2. 判断是否有数据
  3. 获取数据
2. getXxx(args)：获取数据
  
  Xxx 代表数据类型，如int getInt()，String getString()
  
  args 参数：
  
  (1) int：代表列的编号，从 1 开始，如：getString(1)
  (2) String：代表列的名称，如：getDouble(balance)

示例 Code

// 代码几乎同上面的例子，使用 executeQuery 方法返回 ResultSet 对象
public class JdbcDemo06 {
    public static void main(String[] args) {
            Statement stat = null;
            Connection conn = null;
            ResultSet rs = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                String sql = "SELECT * FROM emp";
                conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");
                stat = conn.createStatement();
                rs = stat.executeQuery(sql);
            <span class="token comment">//使用ResultSet类的方法，获取表中的数据</span>
            rs<span class="token punctuation">.</span><span class="token function">next</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token keyword">int</span> id <span class="token operator">=</span> rs<span class="token punctuation">.</span><span class="token function">getInt</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token class-name">String</span> name <span class="token operator">=</span> rs<span class="token punctuation">.</span><span class="token function">getString</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span><span class="token string">"id = "</span> <span class="token operator">+</span> id <span class="token operator">+</span> <span class="token string">", name = "</span> <span class="token operator">+</span> name<span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">ClassNotFoundException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span> <span class="token keyword">finally</span> <span class="token punctuation">{</span>
			<span class="token comment">//释放创建ResultSet类对象所需的资源</span>
            <span class="token keyword">if</span><span class="token punctuation">(</span>rs <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                <span class="token keyword">try</span> <span class="token punctuation">{</span>
                    rs<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
                    throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                <span class="token punctuation">}</span>
            <span class="token punctuation">}</span>

            <span class="token keyword">if</span><span class="token punctuation">(</span>stat <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                <span class="token keyword">try</span> <span class="token punctuation">{</span>
                    stat<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
                    throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                <span class="token punctuation">}</span>
            <span class="token punctuation">}</span>

            <span class="token keyword">if</span><span class="token punctuation">(</span>conn <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                <span class="token keyword">try</span> <span class="token punctuation">{</span>
                    conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
                    throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                <span class="token punctuation">}</span>
            <span class="token punctuation">}</span>

        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>

}
/*output:
显示查询到的表中的一行信息
*/

public class JdbcDemo07 {
    public static void main(String[] args) {
        Statement stat = null;
        Connection conn = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String sql = "SELECT * FROM emp";
            conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "root");
            stat = conn.createStatement();
            rs = stat.executeQuery(sql);

            while(rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                System.out.println("id =" + id + ", name =" + name);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

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

            if(stat != null) {
                try {
                    stat.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }
    }
}
/*output:
显示查询到的表中的多行信息（指定的几列，循环遍历打印）
*/

JDBC 实例

定义一个方法，查询 emp 表的数据将其封装为对象，然后装载集合，返回
实现步骤：

1 定义 Emp 类
2 定义方法public List<Emp> getAllInfo(){}
3 使用的 sql 语句select * from emp;

实例 Code

// 将数据库表 emp 中的字段，作为 Emp 表这个类的各个成员变量
public class Emp {
    private int id;
    private String eName;
    private int job_id;
    private int mgr;
    private Date date;
    private double salary;
    private double bonus;
    private int dept_id;
<span class="token keyword">public</span> <span class="token class-name">Emp</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token punctuation">}</span>

<span class="token annotation punctuation">@Override</span>
<span class="token keyword">public</span> <span class="token class-name">String</span> <span class="token function">toString</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">return</span> <span class="token string">"emp{"</span> <span class="token operator">+</span>
            <span class="token string">"id="</span> <span class="token operator">+</span> id <span class="token operator">+</span>
            <span class="token string">", eName='"</span> <span class="token operator">+</span> eName <span class="token operator">+</span> <span class="token char">'\''</span> <span class="token operator">+</span>
            <span class="token string">", job_id="</span> <span class="token operator">+</span> job_id <span class="token operator">+</span>
            <span class="token string">", mgr="</span> <span class="token operator">+</span> mgr <span class="token operator">+</span>
            <span class="token string">", date="</span> <span class="token operator">+</span> date <span class="token operator">+</span>
            <span class="token string">", salary="</span> <span class="token operator">+</span> salary <span class="token operator">+</span>
            <span class="token string">", bonus="</span> <span class="token operator">+</span> bonus <span class="token operator">+</span>
            <span class="token string">", dept_id="</span> <span class="token operator">+</span> dept_id <span class="token operator">+</span>
            <span class="token char">'}'</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">int</span> <span class="token function">getId</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">return</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setId</span><span class="token punctuation">(</span><span class="token keyword">int</span> id<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">this</span><span class="token punctuation">.</span>id <span class="token operator">=</span> id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token class-name">String</span> <span class="token function">geteName</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">return</span> eName<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">seteName</span><span class="token punctuation">(</span><span class="token class-name">String</span> eName<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">this</span><span class="token punctuation">.</span>eName <span class="token operator">=</span> eName<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">int</span> <span class="token function">getJob_id</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">return</span> job_id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setJob_id</span><span class="token punctuation">(</span><span class="token keyword">int</span> job_id<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">this</span><span class="token punctuation">.</span>job_id <span class="token operator">=</span> job_id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">int</span> <span class="token function">getMgr</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">return</span> mgr<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setMgr</span><span class="token punctuation">(</span><span class="token keyword">int</span> mgr<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">this</span><span class="token punctuation">.</span>mgr <span class="token operator">=</span> mgr<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token class-name">Date</span> <span class="token function">getDate</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">return</span> date<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setDate</span><span class="token punctuation">(</span><span class="token class-name">Date</span> date<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">this</span><span class="token punctuation">.</span>date <span class="token operator">=</span> date<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">double</span> <span class="token function">getSalary</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">return</span> salary<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setSalary</span><span class="token punctuation">(</span><span class="token keyword">double</span> salary<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">this</span><span class="token punctuation">.</span>salary <span class="token operator">=</span> salary<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">double</span> <span class="token function">getBonus</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">return</span> bonus<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setBonus</span><span class="token punctuation">(</span><span class="token keyword">double</span> bonus<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">this</span><span class="token punctuation">.</span>bonus <span class="token operator">=</span> bonus<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">int</span> <span class="token function">getDept_id</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">return</span> dept_id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">setDept_id</span><span class="token punctuation">(</span><span class="token keyword">int</span> dept_id<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">this</span><span class="token punctuation">.</span>dept_id <span class="token operator">=</span> dept_id<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

}

public class JdbcDemo08 {
    public static void main(String[] args) {
        JdbcDemo08 jdbcDemo08 = new JdbcDemo08();
        List<Emp> allInfo = jdbcDemo08.getAllInfo();
        //System.out.println(allInfo);
        for(Emp ele : allInfo) {
            System.out.println(ele);
        }
    }

    public List<Emp> getAllInfo() {
        List<Emp> list = new ArrayList<>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root", "root");
            stmt = conn.createStatement();
            String sql = "SELECT * FROM emp";
            resultSet = stmt.executeQuery(sql);
            Emp emp = null;
            while(resultSet.next()) {
                emp = new Emp();
                int id = resultSet.getInt("id");
                emp.setId(id);
                String eName = resultSet.getString("ename");
                emp.seteName(eName);
                int jobID = resultSet.getInt("job_id");
                emp.setJob_id(jobID);
                int mgr = resultSet.getInt("mgr");
                emp.setMgr(mgr);
                Date joinDate = resultSet.getDate("joindate");
                emp.setDate(joinDate);
                Double salary = resultSet.getDouble("salary");
                emp.setSalary(salary);
                Double bonus = resultSet.getDouble("bonus");
                emp.setBonus(bonus);
                int deptID = resultSet.getInt("dept_id");
                emp.setDept_id(deptID);
                list.add(emp);// 将表中每一行信息添加到集合中
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if(resultSet != null) {
                try {
                    resultSet.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 list;
    }
}

JDBC 工具类

可以设计一个工具类 JdbcUtilities，其中有以下方法
1. 注册驱动
2. 获取连接对象
  
  可以使用配置文件，读取参数，为getConnection(String url, String username, String password)方法传参
3. 释放资源

示例 Code

public class JdbcUtilities {
    private static String url = null;
    private static String username = null;
    private static String password = null;
    private static String driver =null;
    static {
        try {
            ClassLoader classLoader = JdbcUtilities.class.getClassLoader();
        <span class="token comment">//InputStream is = classLoader.getResourceAsStream("JDBC.properties");</span>
        <span class="token class-name">URL</span> resource <span class="token operator">=</span> classLoader<span class="token punctuation">.</span><span class="token function">getResource</span><span class="token punctuation">(</span><span class="token string">"JDBC.properties"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token class-name">String</span> path <span class="token operator">=</span> resource<span class="token punctuation">.</span><span class="token function">getPath</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token class-name">Properties</span> properties <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Properties</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token comment">//properties.load(is);</span>
        properties<span class="token punctuation">.</span><span class="token function">load</span><span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">FileReader</span><span class="token punctuation">(</span>path<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

        url <span class="token operator">=</span> properties<span class="token punctuation">.</span><span class="token function">getProperty</span><span class="token punctuation">(</span><span class="token string">"url"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        username <span class="token operator">=</span> properties<span class="token punctuation">.</span><span class="token function">getProperty</span><span class="token punctuation">(</span><span class="token string">"username"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        password <span class="token operator">=</span> properties<span class="token punctuation">.</span><span class="token function">getProperty</span><span class="token punctuation">(</span><span class="token string">"password"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        driver <span class="token operator">=</span> properties<span class="token punctuation">.</span><span class="token function">getProperty</span><span class="token punctuation">(</span><span class="token string">"driver"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token class-name">Class</span><span class="token punctuation">.</span><span class="token function">forName</span><span class="token punctuation">(</span>driver<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">IOException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">ClassNotFoundException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">Connection</span> <span class="token function">getConn</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">throws</span> <span class="token class-name">SQLException</span><span class="token punctuation">,</span> <span class="token class-name">IOException</span> <span class="token punctuation">{</span>

    <span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token class-name">DriverManager</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span>url<span class="token punctuation">,</span> username<span class="token punctuation">,</span> password<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">return</span> conn<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">close</span><span class="token punctuation">(</span><span class="token class-name">ResultSet</span> rs<span class="token punctuation">,</span> <span class="token class-name">Statement</span> stmt<span class="token punctuation">,</span> <span class="token class-name">Connection</span> conn<span class="token punctuation">)</span><span class="token punctuation">{</span>
    <span class="token keyword">if</span><span class="token punctuation">(</span>rs <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            rs<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>

    <span class="token keyword">if</span><span class="token punctuation">(</span>stmt <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            stmt<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>

    <span class="token keyword">if</span><span class="token punctuation">(</span>conn <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>

<span class="token punctuation">}</span>

}

// 使用 JDBC 工具类重做之前的 JDBC 实例
public class JdbcDemo09 {
    public static void main(String[] args) {
        JdbcDemo08 jdbcDemo08 = new JdbcDemo08();
        List<Emp> allInfo = jdbcDemo08.getAllInfo();
        //System.out.println(allInfo);
        for(Emp ele : allInfo) {
            System.out.println(ele);
        }
    }

    public List<Emp> getAllInfo() {
        List<Emp> list = new ArrayList<>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet resultSet = null;

        try {
            //Class.forName("com.mysql.jdbc.Driver");
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root", "root");
            conn = JdbcUtilities.getConn();

            stmt = conn.createStatement();
            String sql = "SELECT * FROM emp";
            resultSet = stmt.executeQuery(sql);
            Emp emp = null;
            while(resultSet.next()) {

                int id = resultSet.getInt("id");
                String eName = resultSet.getString("ename");
                int jobID = resultSet.getInt("job_id");
                int mgr = resultSet.getInt("mgr");
                Date joinDate = resultSet.getDate("joindate");
                Double salary = resultSet.getDouble("salary");
                Double bonus = resultSet.getDouble("bonus");
                int deptID = resultSet.getInt("dept_id");

                emp = new Emp();
                emp.setId(id);
                emp.seteName(eName);
                emp.setJob_id(jobID);
                emp.setMgr(mgr);
                emp.setDate(joinDate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(deptID);

                list.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            JdbcUtilities.close(resultSet, stmt, conn);
        }

        return list;
    }
}

JDBC 登录案例

实现需求：
1. 通过键盘输入用户名和密码
2. 判断用户是否登录成功

示例 Code

#该案例使用的表
CREATE TABLE user(
	id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(32),
	password VARCHAR(32)
);
INSERT INTO user VALUES (null, "33", "a3b3");
INSERT INTO user VALUES (null, "34", "qwer");

public class JdbcDemo10 {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        String str1 = scanner.next();
        String str2 = scanner.next();
        boolean returnValue = new JdbcDemo10().userLogin(str1, str2);
        if(returnValue) {
            System.out.println("successfully login");
        } else {
            System.out.println("fail to login");
        }
    }

    public boolean userLogin(String name, String password) {
        if(name == null || password == null) {
            return false;
        }
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            conn = JdbcUtilities.getConn();
            statement = conn.createStatement();
            // 字符串拼接，使用传入的参数 name 和 password
            String sql = "select * from user where username ='"+name+"' and password ='"+password+"'";
            resultSet = statement.executeQuery(sql);
            //next 方法返回值为 boolean，若有 sql 中查询的一行则返回 true，否则返回 false
            return resultSet.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            JdbcUtilities.close(resultSet, statement, conn);
        }

        return false;
    }
}
// 存在 sql 注入问题

/*input:
dhuf
dbv'or'ac'='ac
*/

/*output:
select * from user where username = 'dhuf' and password = 'dbv' or 'ac' = 'ac' 
successfully login
*/

PreparedStatement 类

PreparedStatement 类是 Statement 类的子类
作用：作用同 Statement 类都是执行 sql 的对象，但同时解决了 sql 注入问题

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

在以上的例程中都使用了 Statement 类方法来执行 sql 的对象，但是输入特殊字符拼接，可以导致强行登录的问题

//JDBC 登录案例，控制台打印信息
ahsdjh// 输入任意用户名
abcd'or'ghgh'='ghgh// 输入密码
select * from user where username = 'ahsdjh' and password = 'abcd' or 'ghgh' = 'ghgh' //or 右边的比较是恒成立的,or 优先级高于 and，所以 where 后面表达式为 true，相当于 where 失效
successfully login// 成功登录

使用 PreparedStatement 对象解决 sql 注入的问题
- 使用预编译的 sql，格式：参数使用?作为占位符
- 使用步骤：
  1. 导入驱动 jar 包 mysql-connector-java-5.1.37-bin.jar
  2. 注册驱动
  3. 获取数据库连接对象 Connection
  4. 定义 sql
    
    注意：sql 的参数使用?作为占位符
    
    select * from user where username = ? and password = ?;
  5. 获取执行 sql 语句的对象 PreparedStatement
    
    Connection类对象.preparedStatement(String sql)
  6. 给?赋值
    
    使用 PreparedStatement 类的方法：setXxx(参数1，参数2)
    
    参数 1：?的位置索引从 1 开始
    
    参数 2：?的值
  7. 执行 sql，接收返回结果
    
    PreparedStatement 类中 execute 之类的方法无参数
    
    PreparedStatement 中继承自 Statement 中的方法有参数，但此时不用这些方法，用子类中的无参数方法
  8. 处理结果
  9. 释放资源
- 注意：
  
  后期都会使用 PreparedStatement 来完成增删改查的所有操作
  1. 可以防止 sql 注入
  2. 效率更高

示例 Code

public class JdbcDemo11 {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        String str1 = scanner.nextLine();
        String str2 = scanner.nextLine();
        boolean returnValue = new JdbcDemo11().userLogin(str1, str2);
        if(returnValue) {
            System.out.println("successfully login");
        } else {
            System.out.println("fail to login");
        }
    }
<span class="token keyword">public</span> <span class="token keyword">boolean</span> <span class="token function">userLogin</span><span class="token punctuation">(</span><span class="token class-name">String</span> name<span class="token punctuation">,</span> <span class="token class-name">String</span> password<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">if</span><span class="token punctuation">(</span>name <span class="token operator">==</span> <span class="token keyword">null</span> <span class="token operator">||</span> password <span class="token operator">==</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token keyword">return</span> <span class="token boolean">false</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
    <span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">PreparedStatement</span> pStmt <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">ResultSet</span> resultSet <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        conn <span class="token operator">=</span> <span class="token class-name">JdbcUtilities</span><span class="token punctuation">.</span><span class="token function">getConn</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">"select * from user where username = ? and password = ? "</span><span class="token punctuation">;</span>
        pStmt <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">//System.out.println(sql);</span>
        pStmt<span class="token punctuation">.</span><span class="token function">setString</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> name<span class="token punctuation">)</span><span class="token punctuation">;</span>
        pStmt<span class="token punctuation">.</span><span class="token function">setString</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> password<span class="token punctuation">)</span><span class="token punctuation">;</span>
        resultSet <span class="token operator">=</span> pStmt<span class="token punctuation">.</span><span class="token function">executeQuery</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">return</span> resultSet<span class="token punctuation">.</span><span class="token function">next</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">IOException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">finally</span> <span class="token punctuation">{</span>
        <span class="token class-name">JdbcUtilities</span><span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span>resultSet<span class="token punctuation">,</span> pStmt<span class="token punctuation">,</span> conn<span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">//这里pStmt传参，父类引用指向子类对象，多态</span>
    <span class="token punctuation">}</span>

    <span class="token keyword">return</span> <span class="token boolean">false</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

}
/*input:
dhfjd
ap'or'aaa'='aaa
*/
/*output：
select * from user where username = ? and password = ?
fail to login
*/

JDBC 管理事务

事务：一个包含多个步骤的业务操作，如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败
管理事务的操作：
1. 开启事务
2. 提交事务
3. 回滚事务
使用 Connection 类中的方法来管理事务
- 开启事务：在代码中，执行 sql 之前开启事务
  
  setAutoCommit(boolean autocommit)
  
  调用该方法设置参数为 false，就是开启事务
- 提交事务：在代码中，当所有 sql 都执行完提交事务
  
  commit()
- 回滚事务：在代码中，在异常处理的 catch 中回滚事务
  
  rollback()

示例 Code

public class JdbcDemo12 {
    public static void main(String[] args) {
        new JdbcDemo12().transfer();
    }
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">transfer</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">PreparedStatement</span> pStmt1 <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">PreparedStatement</span> pStmt2 <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        conn <span class="token operator">=</span> <span class="token class-name">JdbcUtilities</span><span class="token punctuation">.</span><span class="token function">getConn</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        conn<span class="token punctuation">.</span><span class="token function">setAutoCommit</span><span class="token punctuation">(</span><span class="token boolean">false</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token class-name">String</span> sql1 <span class="token operator">=</span> <span class="token string">"UPDATE account SET balance = balance - ? WHERE id = ?"</span><span class="token punctuation">;</span>
        <span class="token class-name">String</span> sql2 <span class="token operator">=</span> <span class="token string">"UPDATE account SET balance = balance + ? WHERE id = ?"</span><span class="token punctuation">;</span>
        pStmt1 <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql1<span class="token punctuation">)</span><span class="token punctuation">;</span>
        pStmt2 <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql2<span class="token punctuation">)</span><span class="token punctuation">;</span>
        pStmt1<span class="token punctuation">.</span><span class="token function">setDouble</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">500</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        pStmt2<span class="token punctuation">.</span><span class="token function">setDouble</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">500</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        pStmt1<span class="token punctuation">.</span><span class="token function">setInt</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        pStmt2<span class="token punctuation">.</span><span class="token function">setInt</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        pStmt1<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token comment">/*在两次转账操作间设置异常操作
        String str = null;
        str.toLowerCase();*/</span>

        pStmt2<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        conn<span class="token punctuation">.</span><span class="token function">commit</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
   <span class="token comment">/* } catch (SQLException throwables) {
        throwables.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();  */</span>
    <span class="token punctuation">}</span><span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            <span class="token comment">//catch中设置回滚事务</span>
            <span class="token keyword">if</span><span class="token punctuation">(</span>conn <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                conn<span class="token punctuation">.</span><span class="token function">rollback</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">finally</span> <span class="token punctuation">{</span>
        <span class="token class-name">JdbcUtilities</span><span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span>pStmt1<span class="token punctuation">,</span> conn<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token class-name">JdbcUtilities</span><span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span>pStmt2<span class="token punctuation">,</span> <span class="token keyword">null</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

}

二、数据库连接池

数据库连接池概述

数据库连接池就是一个容器（集合），存放数据库连接的容器。

当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器
数据库连接池的好处
1. 节约资源
2. 用户访问高效
数据库连接池的实现
- 标准接口DataSource，在 javax.sql 包下的
  - 方法：
    
    获取连接：getConnection()
    
    归还连接：如果连接对象 Connection 是从连接池中获取的，那么调用Connection.Close()方法，则不会再关闭连接了，而是归还连接
- 一般普通开发者不去实现它，由数据库厂商实现
  - c3p0：数据库连接池技术
  - Druid：数据库连接池实现技术，由阿里巴巴提供

数据库连接池 c3p0

使用步骤
1. 导入 jar 包c3p0-0.9.5.2.jar和依赖 jar 包mchange-commons-java-0.2.12.jar
  
  同时需要有导入的数据库驱动 jar 包mysql-connector-java-5.1.37-bin.jar
2. 定义配置文件：
  
  配置文件名称：c3p0.properties或者c3p0-config.xml
  
  路径：直接将文件放在 src 目录下即可
3. 创建核心对象，即数据库连接池对象ComboPooledDataSource
  
  该构造方法中无参是读取默认配置，若有 String 参数是传入指定配置的配置名称来使用指定配置
4. 获取连接：getConnection

示例 Code

// 示例 1：使用连接池
public class Demo01c3p0 {
    public static void main(String[] args) throws SQLException {
        // 创建数据库连接池对象
        DataSource ds = new ComboPooledDataSource();
        // 获取连接对象
        Connection connection = ds.getConnection();
        System.out.println(connection);
    }
}
/*output: 打印日志信息 和 connection 对象的信息
Jul 08, 2021 10:57:59 PM com.mchange.v2.log.MLog
INFO: MLog clients using java 1.4+ standard logging.
Jul 08, 2021 10:58:00 PM com.mchange.v2.c3p0.C3P0Registry
INFO: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
Jul 08, 2021 10:58:00 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 3000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1b60699ai11cqj5idnrclk|525b461a, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1b60699ai11cqj5idnrclk|525b461a, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/db3, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
com.mchange.v2.c3p0.impl.NewProxyConnection@11bd0f3b [wrapping: com.mysql.jdbc.JDBC4Connection@c333c60]
*/

// 示例 2：根据 XML 配置文件中的信息测试最大连接
public class Demo02c3p0 {
    public static void main(String[] args) throws SQLException {
//        DataSource ds = new ComboPooledDataSource();
//        for(int i = 0; i < 10; i++) {
//            Connection connection = ds.getConnection();
//            System.out.println(i + ":" + connection);
//        }

        testMaxPoolSize1();
        //testMaxPoolSize2();
    }

    public static void testMaxPoolSize1() throws SQLException {
        // 这里使用无参数构造创建对象，使用默认配置的配置信息，XML 中最大连接数为 10
        DataSource ds = new ComboPooledDataSource();
        for(int i = 0; i < 11; i++) {
            Connection connection = ds.getConnection();
            System.out.println(i + ":" + connection);

            // 这里第 6 次连接后关闭，这样可以总共连接 11 个
            if(i == 5) {
                connection.close();
            }
        }
    }

    public static void testMaxPoolSize2() throws SQLException {
        // 使用指定的配置 "otherc3p0" 中的配置信息，XML 中最大连接数为 8
        DataSource ds = new ComboPooledDataSource("otherc3p0");
        for(int i = 0; i < 9; i++) {
            Connection connection = ds.getConnection();
            System.out.println(i + ":" + connection);

            // 这里第 6 次连接后关闭，这样可以总共连接 9 个
            if(i == 5) {
                connection.close();
            }
        }
    }

}

<c3p0-config>
  <!-- 使用默认的配置读取连接池对象 -->
  <default-config>
  	<!--  连接参数 -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/db3</property>
    <property name="user">root</property>
    <property name="password">root</property>
    
    <!-- 连接池参数 -->
    <property name="initialPoolSize">5</property>
    <property name="maxPoolSize">10</property>
    <property name="checkoutTimeout">3000</property>
  </default-config>

  <named-config name="otherc3p0"> 
    <!--  连接参数 -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/db3</property>
    <property name="user">root</property>
    <property name="password">root</property>
    
    <!-- 连接池参数 -->
    <property name="initialPoolSize">5</property>
    <property name="maxPoolSize">8</property>
    <property name="checkoutTimeout">1000</property>
  </named-config>
</c3p0-config>

数据库连接池 Druid

使用步骤
1. 导入 jar 包druid-1.0.9.jar
2. 定义配置文件：
  
  druid.properties
  
  是 properties 格式的，可以放在任意目录下
3. 加载配置文件
  
  通过Properties中的load方法
4. 获取数据库连接池对象：
  
  通过工厂类DruidDataSourceFactory中的静态方法createDataSource
5. 获取连接：
  
  通过DataSource类中的getConnection方法

示例 Code

public class Demo03Druid {
    public static void main(String[] args) throws Exception {
        // 加载配置文件
        InputStream is = Demo03Druid.class.getClassLoader().getResourceAsStream("druid.properties");
        Properties prop = new Properties();
        prop.load(is);
    <span class="token comment">//获取连接池对象</span>
    <span class="token class-name">DataSource</span> dataSource <span class="token operator">=</span> <span class="token class-name">DruidDataSourceFactory</span><span class="token punctuation">.</span><span class="token function">createDataSource</span><span class="token punctuation">(</span>prop<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">//获取连接</span>
    <span class="token class-name">Connection</span> connection <span class="token operator">=</span> dataSource<span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">//打印连接对象</span>
    <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>connection<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

}
/*output:
Jul 09, 2021 3:35:42 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
com.mysql.jdbc.JDBC4Connection@6c40365c
*/

Druid 工具类

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

示例 Code

public class JDBCUtils {
    // 定义成员变量 DataSource
    private static DataSource ds;
<span class="token keyword">static</span> <span class="token punctuation">{</span>

    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token comment">//加载配置文件</span>
        <span class="token class-name">Properties</span> prop <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Properties</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        prop<span class="token punctuation">.</span><span class="token function">load</span><span class="token punctuation">(</span><span class="token class-name">JDBCUtils</span><span class="token punctuation">.</span><span class="token keyword">class</span><span class="token punctuation">.</span><span class="token function">getClassLoader</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">getResourceAsStream</span><span class="token punctuation">(</span><span class="token string">"druid.properties"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">//获取DataSource</span>
        ds <span class="token operator">=</span> <span class="token class-name">DruidDataSourceFactory</span><span class="token punctuation">.</span><span class="token function">createDataSource</span><span class="token punctuation">(</span>prop<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

<span class="token comment">//获取连接池对象ds</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">DataSource</span> <span class="token function">getDataSource</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">return</span> ds<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token comment">//获取连接</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">Connection</span> <span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">throws</span> <span class="token class-name">SQLException</span> <span class="token punctuation">{</span>
    <span class="token keyword">return</span> ds<span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token comment">//释放资源，使用executeQuery会返回ResultSet对象</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">close</span><span class="token punctuation">(</span><span class="token class-name">Connection</span> conn<span class="token punctuation">,</span> <span class="token class-name">Statement</span> stmt<span class="token punctuation">,</span> <span class="token class-name">ResultSet</span> rs<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">if</span><span class="token punctuation">(</span>rs <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            rs<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>

    <span class="token keyword">if</span><span class="token punctuation">(</span>stmt <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            stmt<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>

    <span class="token keyword">if</span><span class="token punctuation">(</span>conn <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
<span class="token comment">//释放资源，使用executeUpdate</span>
<span class="token comment">//这里方法体直接调用了上面定义的3个参数的方法重载形式</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">close</span><span class="token punctuation">(</span><span class="token class-name">Connection</span> conn<span class="token punctuation">,</span> <span class="token class-name">Statement</span> stmt<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token function">close</span><span class="token punctuation">(</span>conn<span class="token punctuation">,</span> stmt<span class="token punctuation">,</span> <span class="token keyword">null</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

}

测试 Druid 工具类

// 为数据库 db3 中的表 account 添加一条数据
public class Demo04TestDruid {
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">main</span><span class="token punctuation">(</span><span class="token class-name">String</span><span class="token punctuation">[</span><span class="token punctuation">]</span> args<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token class-name">Connection</span> connection <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">PreparedStatement</span> pstmt <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        connection <span class="token operator">=</span> <span class="token class-name">JDBCUtils</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">"INSERT INTO account VALUES (null, ?, ?)"</span><span class="token punctuation">;</span>
        pstmt <span class="token operator">=</span> connection<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
        pstmt<span class="token punctuation">.</span><span class="token function">setString</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">"Lucy"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        pstmt<span class="token punctuation">.</span><span class="token function">setDouble</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3500</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">int</span> count <span class="token operator">=</span> pstmt<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>count<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> throwables<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        throwables<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">finally</span> <span class="token punctuation">{</span>
        <span class="token class-name">JDBCUtils</span><span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span>connection<span class="token punctuation">,</span> pstmt<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>

<span class="token punctuation">}</span>

}
/*output: 返回 1 表示表中有一条改动
Jul 09, 2021 4:57:44 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
1
*/

三、Spring 中的 JdbcTemplate 类

JdbcTemplate 概述

Spring JDBC 是 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
  同时需要有导入的数据库驱动 jar 包mysql-connector-java-5.1.37-bin.jar
2. 创建JdbcTemplate对象，这个对象依赖于数据库连接池（数据源）DataSource
  
  JdbcTemplate template = new JdbcTemplate(ds);
3. 调用JdbcTemplate的方法来完成 CRUD 的操作
  - update()：执行 DML 语句（增删改语句）
  - queryForMap()：将查询到的结果集封装为 Map 集合，其中列名为 key，值作为 value
    
    注意：这个方法查询的结果集长度只能是 1, 例如queryForMap(sql语句字符串, arg参数)
  - queryForList()：将查询到的结果集封装为 List 集合
    
    注意：将每一条记录封装为一个 Map 集合，再将 Map 结合装载到 List 集合中
  - query()：查询结果，将结果封装为 JavaBean 对象
    
    方法query的参数：RowMapper接口类型的对象
    
    一般使用BeanPropertyRowMapper实现类，可以完成数据到 JavaBean 的自动封装
    
    格式：new BeanPropertyRowMapper<类型>(类型.class)
  - queryForObject()：查询结果，将结果封装为对象
    
    多用于使用了聚合函数的查询

示例 Code

public class TestJdbcTemplate {
    public static void main(String[] args) throws Exception {
        // 创建 JdbcTemplate 对象
        DataSource dataSource = JDBCUtils.getDataSource();
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    <span class="token comment">//调用JdbcTemplate对象中的方法</span>
    <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">"UPDATE account SET balance = 100 WHERE id = ?"</span><span class="token punctuation">;</span>
    <span class="token comment">//update方法中传入sql语句和sql语句中的参数</span>
    <span class="token keyword">int</span> update <span class="token operator">=</span> jdbcTemplate<span class="token punctuation">.</span><span class="token function">update</span><span class="token punctuation">(</span>sql<span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>update<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

}
/*output: 返回 1 表示表中有一条改动
Jul 09, 2021 5:36:58 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
1
*/

JdbcTemplate 执行 DML 语句

Jdbc Template 执行 DML 语句的方法：

update()：执行 DML 语句（增删改语句）

示例 Code

// 使用 Junit 单元测试，单独测试 JdbcTemplate 类中的增、删、改方法
public class JdbcTemplateTest {
<span class="token keyword">private</span> <span class="token class-name">JdbcTemplate</span> jdbcTemplate <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">JdbcTemplate</span><span class="token punctuation">(</span><span class="token class-name">JDBCUtils</span><span class="token punctuation">.</span><span class="token function">getDataSource</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">//更改一条数据</span>
<span class="token annotation punctuation">@Test</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">testUpdate1</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">"UPDATE emp SET salary = 10000 WHERE id = 1001"</span><span class="token punctuation">;</span>
    <span class="token keyword">int</span> count <span class="token operator">=</span> jdbcTemplate<span class="token punctuation">.</span><span class="token function">update</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>count<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token comment">/*output:
Jul 09, 2021 9:50:29 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
1
*/</span>

<span class="token comment">//插入一条数据</span>
<span class="token annotation punctuation">@Test</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">testUpdate2</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">"INSERT INTO emp (id, ename, dept_id) VALUES(?, ?, ?)"</span><span class="token punctuation">;</span>
    <span class="token keyword">int</span> count <span class="token operator">=</span> jdbcTemplate<span class="token punctuation">.</span><span class="token function">update</span><span class="token punctuation">(</span>sql<span class="token punctuation">,</span> <span class="token number">1016</span><span class="token punctuation">,</span> <span class="token string">"Jake"</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>count<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token comment">/*output:
Jul 09, 2021 9:51:40 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
1
*/</span>

<span class="token comment">//删除一条数据</span>
<span class="token annotation punctuation">@Test</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">testUpdate3</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">"DELETE FROM emp WHERE ename = ?"</span><span class="token punctuation">;</span>
    <span class="token keyword">int</span> count <span class="token operator">=</span> jdbcTemplate<span class="token punctuation">.</span><span class="token function">update</span><span class="token punctuation">(</span>sql<span class="token punctuation">,</span> <span class="token string">"Jake"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>count<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token comment">/*output:
Jul 09, 2021 9:52:39 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
1
*/</span>

}

JdbcTemplate 执行 DQL 语句

Jdbc Template 执行 DQL 语句的方法：
- queryForMap()：将查询到的结果集封装为 Map 集合，其中列名为 key，值作为 value
  
  注意：这个方法查询的结果集长度只能是 1, 例如queryForMap(sql语句字符串, arg参数)
- queryForList()：将查询到的结果集封装为 List 集合
  
  注意：将每一条记录封装为一个 Map 集合，再将 Map 结合装载到 List 集合中
- query()：查询结果，将结果封装为 JavaBean 对象，此方法需要使用一个预定义的类（成员变量为表中的字段，类中包含 getter 和 setter 方法、toString 方法的重写）
  
  方法query的参数：需要传入RowMapper接口类型的对象，可以自定义实现类（或者用匿名内部类），也可以使用一些给定的实现类
  
  一般使用BeanPropertyRowMapper实现类，可以完成数据到 JavaBean 的自动封装
  
  格式：new BeanPropertyRowMapper<类型>(类型.class)
  
  注意：预定义的类型中，类的成员变量必须是引用类型，若有基本数据类型要使用其对应的包装类
  
  否则会出现 org.springframework.beans.TypeMismatchException
- queryForObject()：查询结果，将结果封装为对象
  
  一般用于使用了聚合函数的查询

示例 Code

//queryForMap(): 将查询到的结果集封装为 Map 集合，其中列名为 key，值作为 value
// 这个方法查询的结果集长度只能是 1,  例如：queryForMap(sql 语句字符串, arg 参数)，预编译 sql 参数只能有一个
public class TestJdbcTemplate2 {
<span class="token keyword">private</span> <span class="token class-name">JdbcTemplate</span> jdbcTemplate <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">JdbcTemplate</span><span class="token punctuation">(</span><span class="token class-name">JDBCUtils</span><span class="token punctuation">.</span><span class="token function">getDataSource</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token annotation punctuation">@Test</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">testQueryForMap</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">"SELECT * FROM emp WHERE id = ?"</span><span class="token punctuation">;</span>
    <span class="token comment">//这里只能是查询一个id中的记录</span>
    <span class="token class-name">Map</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">String</span><span class="token punctuation">,</span> <span class="token class-name">Object</span><span class="token punctuation">&gt;</span></span> map <span class="token operator">=</span> jdbcTemplate<span class="token punctuation">.</span><span class="token function">queryForMap</span><span class="token punctuation">(</span>sql<span class="token punctuation">,</span> <span class="token number">1001</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token comment">//打印map</span>
    <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>map<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">//遍历打印map中的key和value</span>
    <span class="token keyword">for</span><span class="token punctuation">(</span><span class="token class-name">String</span> ele <span class="token operator">:</span> map<span class="token punctuation">.</span><span class="token function">keySet</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>ele <span class="token operator">+</span> <span class="token string">":"</span> <span class="token operator">+</span> map<span class="token punctuation">.</span><span class="token function">get</span><span class="token punctuation">(</span>ele<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

}
/*output: 打印日志信息 + 测试类中测试方法执行输出结果
Jul 09, 2021 11:14:58 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
{id=1001, ename=George, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=10}
id:1001
ename:George
job_id:4
mgr:1004
joindate:2000-12-17
salary:10000.00
bonus:null
dept_id:10
*/

//queryForList(String sql)：将查询到的结果集封装为 List 集合
public class TestJdbcTemplate2 {
    @Test
    public void testQueryForList() {
        String sql = "SELECT * FROM emp";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

        // 打印这个 list
        System.out.println(list);
        System.out.println("=============");
        // 遍历打印这个 list 中的元素
        for(Map<String, Object> ele : list) {
            System.out.println(ele);
        }
    }
}
/*output: 打印日志信息 + 测试类中测试方法执行输出结果
Jul 09, 2021 11:10:26 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
[{id=1001, ename=George, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=10}, {id=1002, ename= 卢俊义, job_id=3, mgr=1006, joindate=2001-02-20, salary=16000.00, bonus=3000.00, dept_id=10}, {id=1003, ename= 林冲, job_id=3, mgr=1006, joindate=2001-02-22, salary=12500.00, bonus=5000.00, dept_id=30}, {id=1004, ename= 唐僧, job_id=2, mgr=1009, joindate=2001-04-02, salary=29750.00, bonus=null, dept_id=20}, {id=1005, ename= 李逵, job_id=4, mgr=1006, joindate=2001-09-28, salary=12500.00, bonus=14000.00, dept_id=30}, {id=1006, ename= 宋江, job_id=2, mgr=1009, joindate=2001-05-01, salary=28500.00, bonus=null, dept_id=30}, {id=1007, ename= 刘备, job_id=2, mgr=1009, joindate=2001-09-01, salary=24500.00, bonus=null, dept_id=10}, {id=1008, ename= 猪八戒, job_id=4, mgr=1004, joindate=2007-04-19, salary=30000.00, bonus=null, dept_id=20}, {id=1009, ename= 罗贯中, job_id=1, mgr=null, joindate=2001-11-17, salary=50000.00, bonus=null, dept_id=10}, {id=1010, ename= 吴用, job_id=3, mgr=1006, joindate=2001-09-08, salary=15000.00, bonus=0.00, dept_id=30}, {id=1011, ename= 沙僧, job_id=4, mgr=1004, joindate=2007-05-23, salary=11000.00, bonus=null, dept_id=20}, {id=1012, ename= 李逵, job_id=4, mgr=1006, joindate=2001-12-03, salary=9500.00, bonus=null, dept_id=30}, {id=1013, ename= 小白龙, job_id=4, mgr=1004, joindate=2001-12-03, salary=30000.00, bonus=null, dept_id=20}, {id=1014, ename= 关羽, job_id=4, mgr=1007, joindate=2002-01-23, salary=13000.00, bonus=null, dept_id=10}, {id=1015, ename= 悟, job_id=null, mgr=1004, joindate=2000-12-17, salary=8000.00, bonus=null, dept_id=20}]
=============
{id=1001, ename=George, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=10}
{id=1002, ename= 卢俊义, job_id=3, mgr=1006, joindate=2001-02-20, salary=16000.00, bonus=3000.00, dept_id=10}
{id=1003, ename= 林冲, job_id=3, mgr=1006, joindate=2001-02-22, salary=12500.00, bonus=5000.00, dept_id=30}
{id=1004, ename= 唐僧, job_id=2, mgr=1009, joindate=2001-04-02, salary=29750.00, bonus=null, dept_id=20}
{id=1005, ename= 李逵, job_id=4, mgr=1006, joindate=2001-09-28, salary=12500.00, bonus=14000.00, dept_id=30}
{id=1006, ename= 宋江, job_id=2, mgr=1009, joindate=2001-05-01, salary=28500.00, bonus=null, dept_id=30}
{id=1007, ename= 刘备, job_id=2, mgr=1009, joindate=2001-09-01, salary=24500.00, bonus=null, dept_id=10}
{id=1008, ename= 猪八戒, job_id=4, mgr=1004, joindate=2007-04-19, salary=30000.00, bonus=null, dept_id=20}
{id=1009, ename= 罗贯中, job_id=1, mgr=null, joindate=2001-11-17, salary=50000.00, bonus=null, dept_id=10}
{id=1010, ename= 吴用, job_id=3, mgr=1006, joindate=2001-09-08, salary=15000.00, bonus=0.00, dept_id=30}
{id=1011, ename= 沙僧, job_id=4, mgr=1004, joindate=2007-05-23, salary=11000.00, bonus=null, dept_id=20}
{id=1012, ename= 李逵, job_id=4, mgr=1006, joindate=2001-12-03, salary=9500.00, bonus=null, dept_id=30}
{id=1013, ename= 小白龙, job_id=4, mgr=1004, joindate=2001-12-03, salary=30000.00, bonus=null, dept_id=20}
{id=1014, ename= 关羽, job_id=4, mgr=1007, joindate=2002-01-23, salary=13000.00, bonus=null, dept_id=10}
{id=1015, ename= 悟, job_id=null, mgr=1004, joindate=2000-12-17, salary=8000.00, bonus=null, dept_id=20}
/

//query(String sql, RowMapper<T> rowMapper)：查询结果，将结果封装为 JavaBean 对象
// 这里使用的是给定的实现类 BeanPropertyRowMapper
public class TestJdbcTemplate2 {
    @Test
    public void testQuery() {
        String sql = "SELECT * FROM emp";
        List<Emp> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
        System.out.println(query);
    }
}
/*output: 打印日志信息 + 测试类中测试方法执行输出结果
Jul 09, 2021 11:44:01 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
[Emp{id=1001, eName='George', job_id=4, mgr=1004, date=null, salary=10000.0, bonus=null, dept_id=10}, Emp{id=1002, eName='卢俊义', job_id=3, mgr=1006, date=null, salary=16000.0, bonus=3000.0, dept_id=10}, Emp{id=1003, eName='林冲', job_id=3, mgr=1006, date=null, salary=12500.0, bonus=5000.0, dept_id=30}, Emp{id=1004, eName='唐僧', job_id=2, mgr=1009, date=null, salary=29750.0, bonus=null, dept_id=20}, Emp{id=1005, eName='李逵', job_id=4, mgr=1006, date=null, salary=12500.0, bonus=14000.0, dept_id=30}, Emp{id=1006, eName='宋江', job_id=2, mgr=1009, date=null, salary=28500.0, bonus=null, dept_id=30}, Emp{id=1007, eName='刘备', job_id=2, mgr=1009, date=null, salary=24500.0, bonus=null, dept_id=10}, Emp{id=1008, eName='猪八戒', job_id=4, mgr=1004, date=null, salary=30000.0, bonus=null, dept_id=20}, Emp{id=1009, eName='罗贯中', job_id=1, mgr=null, date=null, salary=50000.0, bonus=null, dept_id=10}, Emp{id=1010, eName='吴用', job_id=3, mgr=1006, date=null, salary=15000.0, bonus=0.0, dept_id=30}, Emp{id=1011, eName='沙僧', job_id=4, mgr=1004, date=null, salary=11000.0, bonus=null, dept_id=20}, Emp{id=1012, eName='李逵', job_id=4, mgr=1006, date=null, salary=9500.0, bonus=null, dept_id=30}, Emp{id=1013, eName='小白龙', job_id=4, mgr=1004, date=null, salary=30000.0, bonus=null, dept_id=20}, Emp{id=1014, eName='关羽', job_id=4, mgr=1007, date=null, salary=13000.0, bonus=null, dept_id=10}, Emp{id=1015, eName='悟', job_id=null, mgr=1004, date=null, salary=8000.0, bonus=null, dept_id=20}]
*/

//query(String sql, RowMapper<T> rowMapper)：查询结果，将结果封装为 JavaBean 对象
// 使用匿名内部类实现 RowMapper 接口，需要重写 mapRow 方法，效果同上个例程
// 这种写法类似于之前的“JDBC 实例”和“JDBC 工具类”中的写法
public class TestJdbcTemplate2 {
    @Test
    public void testQuery2() {
        String sql = "SELECT * FROM emp";
        List<Emp> query = jdbcTemplate.query(sql, new RowMapper<Emp>() {
            @Override
            public Emp mapRow(ResultSet resultSet, int i) throws SQLException {
                Emp emp = new Emp();
                int id = resultSet.getInt("id");
                String eName =  resultSet.getString("ename");
                int job_id = resultSet.getInt("job_id");
                int mgr = resultSet.getInt("mgr");
                Date date = resultSet.getDate("joindate");
                Double salary = resultSet.getDouble("salary");
                Double bonus = resultSet.getDouble("bonus");
                int dept_id = resultSet.getInt("dept_id");

                emp.setId(id);
                emp.seteName(eName);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setDate(date);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(dept_id);
                return emp;
            }
        });
        System.out.println(query);
    }
}
/*output: 打印日志信息 + 测试类中测试方法执行输出结果
Jul 10, 2021 12:14:08 AM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
[Emp{id=1001, eName='George', job_id=4, mgr=1004, date=2000-12-17, salary=10000.0, bonus=0.0, dept_id=10}, Emp{id=1002, eName='卢俊义', job_id=3, mgr=1006, date=2001-02-20, salary=16000.0, bonus=3000.0, dept_id=10}, Emp{id=1003, eName='林冲', job_id=3, mgr=1006, date=2001-02-22, salary=12500.0, bonus=5000.0, dept_id=30}, Emp{id=1004, eName='唐僧', job_id=2, mgr=1009, date=2001-04-02, salary=29750.0, bonus=0.0, dept_id=20}, Emp{id=1005, eName='李逵', job_id=4, mgr=1006, date=2001-09-28, salary=12500.0, bonus=14000.0, dept_id=30}, Emp{id=1006, eName='宋江', job_id=2, mgr=1009, date=2001-05-01, salary=28500.0, bonus=0.0, dept_id=30}, Emp{id=1007, eName='刘备', job_id=2, mgr=1009, date=2001-09-01, salary=24500.0, bonus=0.0, dept_id=10}, Emp{id=1008, eName='猪八戒', job_id=4, mgr=1004, date=2007-04-19, salary=30000.0, bonus=0.0, dept_id=20}, Emp{id=1009, eName='罗贯中', job_id=1, mgr=0, date=2001-11-17, salary=50000.0, bonus=0.0, dept_id=10}, Emp{id=1010, eName='吴用', job_id=3, mgr=1006, date=2001-09-08, salary=15000.0, bonus=0.0, dept_id=30}, Emp{id=1011, eName='沙僧', job_id=4, mgr=1004, date=2007-05-23, salary=11000.0, bonus=0.0, dept_id=20}, Emp{id=1012, eName='李逵', job_id=4, mgr=1006, date=2001-12-03, salary=9500.0, bonus=0.0, dept_id=30}, Emp{id=1013, eName='小白龙', job_id=4, mgr=1004, date=2001-12-03, salary=30000.0, bonus=0.0, dept_id=20}, Emp{id=1014, eName='关羽', job_id=4, mgr=1007, date=2002-01-23, salary=13000.0, bonus=0.0, dept_id=10}, Emp{id=1015, eName='悟', job_id=0, mgr=1004, date=2000-12-17, salary=8000.0, bonus=0.0, dept_id=20}]
*/

//queryForObject(String sql, Class<T> requiredType)：查询结果，将结果封装为对象
public class TestJdbcTemplate2 {
    @Test
    public void testQueryForObject() {
        String sql = "SELECT COUNT(id) FROM emp";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(count);
    }
}
/*output: 打印日志信息 + 测试类中测试方法执行输出结果
Jul 09, 2021 11:34:00 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
15
*/

Java SE 07(JDBC、数据库连接池、Spring中的JdbcTemplate类)

Java SE 07

一、JDBC

JDBC 概述

JDBC 中使用 insert 语句

JDBC 中使用 update 语句

JDBC 中使用 delete 语句

JDBC 中使用 DDL 语句

ResultSet 类

JDBC 实例

JDBC 工具类

JDBC 登录案例

PreparedStatement 类

JDBC 管理事务

二、数据库连接池

数据库连接池概述

数据库连接池 c3p0

数据库连接池 Druid

Druid 工具类

三、Spring 中的 JdbcTemplate 类

JdbcTemplate 概述

JdbcTemplate 执行 DML 语句

JdbcTemplate 执行 DQL 语句

}
/*output: 打印日志信息 + 测试类中测试方法执行输出结果
Jul 09, 2021 11:14:58 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
{id=1001, ename=George, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=10}

相关帖子

随便看看

Java SE 07(JDBC、数据库连接池、Spring中的JdbcTemplate类)

Java SE 07(JDBC、数据库连接池、Spring中的JdbcTemplate类)

Java SE 07

一、JDBC

JDBC 概述

JDBC 中使用 insert 语句

JDBC 中使用 update 语句

JDBC 中使用 delete 语句

JDBC 中使用 DDL 语句

ResultSet 类

JDBC 实例

JDBC 工具类

JDBC 登录案例

PreparedStatement 类

JDBC 管理事务

二、数据库连接池

数据库连接池概述

数据库连接池 c3p0

数据库连接池 Druid

Druid 工具类

三、Spring 中的 JdbcTemplate 类

JdbcTemplate 概述

JdbcTemplate 执行 DML 语句

JdbcTemplate 执行 DQL 语句

}/*output: 打印日志信息 + 测试类中测试方法执行输出结果Jul 09, 2021 11:14:58 PM com.alibaba.druid.pool.DruidDataSource infoINFO: {dataSource-1} inited{id=1001, ename=George, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=10}

相关帖子

随便看看

Java SE 07(JDBC、数据库连接池、Spring中的JdbcTemplate类)

}
/*output: 打印日志信息 + 测试类中测试方法执行输出结果
Jul 09, 2021 11:14:58 PM com.alibaba.druid.pool.DruidDataSource info
INFO: {dataSource-1} inited
{id=1001, ename=George, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=10}