JDBC技术

最新推荐文章于 2024-09-27 11:31:35 发布

77.star

最新推荐文章于 2024-09-27 11:31:35 发布

阅读量894

点赞数 20

文章标签： oracle 数据库 java mysql

本文链接：https://blog.csdn.net/weixin_67804833/article/details/142498945

版权

一 JDBC的简介

1.1 ODBC的出现

早期的数据库应用程序开发，因为没有通用的针对与数据库的编程接口，所以，开发人员需要学习相关数据库的API，才可以进行应用程序，这样增加了学习成本和开发周期。因此整个开发市场一直在呼吁有一套通用的编程接口

因为有市场需要，微软定义了一组用于数据库应用程序的编程接口ODBC(open database connectivity)。这一套方案大大缩短了程序的开发周期，可以让开发人员只需要调用同一套编程接口，无需考虑具体实现。

ODBC分为四个部分：

应用程序：开发人员所写的代码，ODBC提供的调用接口
驱动程序管理器：用于管理驱动程序的。
驱动程序：对接口的实现部分，各个数据库厂商来完成的。
数据源：就是连接数据库的一些参数：url,username,password

1.2 JDBC简介

Sun公司参考了ODBC方案，制定了一组专门为java语言连接数据库的通用接口JDBC。方便了java开发人员，开发人员不需要考虑特定的数据库的DBMS。JDBC不直接依赖于DBMS，而是通过驱动程序将sql语句转发给DBMS，由DBMS进行解析并执行，处理结果返回。

简单点说，它为Java开发者提供了一种标准的方法来连接和操作各种关系型数据库。

注意：驱动程序：由数据库厂商自己实现，程序员只需要拿来使用即可。

1.4 JDBC中常用API

JDBC API包含了一组类和接口，这些类和接口使得Java程序能够连接到数据库，执行SQL语句，并处理结果。

<span style="background-color:#f8f8f8"><span style="color:#333333">java.sql.DriverManager
java.sql.Connection
java.sql.Statement
java.sql.Result</span></span>

1）DriverManager

JDBC的驱动管理类，负责加载和注册驱动，会根据所提供的连接信息（如URL、用户名和密码）自动选择合适的驱动程序。其提供了用于连接数据库的方法getConnection(…)

常用方法：

<span style="background-color:#f8f8f8"><span style="color:#333333">getConnection(String url, String user, String password)</span></span>

方法参数解析：

<span style="background-color:#f8f8f8"><span style="color:#333333">- url:  连接指定数据库的地址            (比如，jdbc:mysql://ip:port/dbname） 
- user:  连接用户名 
- password:  密码</span></span>

2）Connection。

是一个接口，代表了与数据库的一个会话；通过DriverManager的getConnection方法，程序可以建立与数据库的连接，返回该接口的一个实现类对象。可以用来获取Statement、PreparedStatement和CallableStatement等对象。

常用方法：

<span style="background-color:#f8f8f8"><span style="color:#333333">Statement createStatement();
作用：用于获取Statement对象</span></span>

3）Statement相关

Statement：也是一个接口，用于执行静态SQL语句。每次执行都会解析、编译和执行SQL语句，效率较低，但灵活性高。

常用方法：

<span style="background-color:#f8f8f8"><span style="color:#333333">execute(String sql):通常用于DDL 
executeUpdate(String sql):通常用于DML 
executeQuery(String sql):用于DQL</span></span>

PreparedStatement：用于执行预编译的SQL语句。预编译的SQL语句只需要解析、编译一次，之后可以多次执行，提高了执行效率。适用于需要多次执行相同或类似SQL语句的场景。

常用方法：

<span style="background-color:#f8f8f8"><span style="color:#333333">execute() ;------用于DDL和DML 
executeUpdate();-----用于DML 
executeQuery();-----用于DQL</span></span>

CallableStatement：用于执行存储过程和函数。它可以接收参数、返回结果集和处理输出参数。

4）ResultSet

是一个接口，表示从数据库执行DQL语句时返回的结果集。其内部维护了一个指针，该指针默认指向的是第一行之前的位置。next方法用于移动指针到下一行。指针指向某一行时，就可以调用相关的方法获取这一行上的所有列数据。

常用方法：

<span style="background-color:#f8f8f8"><span style="color:#333333">next():光标方法，向下移动一行，

getDate(int columnIndex)
getDate(String columnLabel)

getString(int columnIndex) 
getString(String columnLabel)

getInt(int columnIndex) 
getInt(String columnLabel)

getDouble(int columnIndex) 
getDouble(String columnLabel)</span></span>

二原生JDBC入门编程

2.1 编写步骤

在编写JDBC的原生代码时，先创建好项目，加载好相应的静态资源，如图片、第三方jar包等，

==注意==：jar包要add到Library里

然后编写步骤如下：

注册驱动
建立连接
获取执行对象
处理结果集
关闭连接

2.2 案例1：JDBC更新数据

<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">public</span> <span style="color:#770088">static</span> <span style="color:#008855">void</span> <span style="color:#0000ff">main1</span>(<span style="color:#008855">String</span>[] <span style="color:#000000">args</span>) <span style="color:#770088">throws</span> <span style="color:#000000">ClassNotFoundException</span>, <span style="color:#000000">SQLException</span> {
    <span style="color:#aa5500">//第一步：加载驱动</span>
    <span style="color:#000000">Class</span>.<span style="color:#000000">forName</span>(<span style="color:#aa1111">"com.mysql.jdbc.Driver"</span>);
    <span style="color:#aa5500">//第二步：建立连接</span>
    <span style="color:#000000">Connection</span> <span style="color:#000000">conn</span> <span style="color:#981a1a">=</span><span style="color:#000000">DriverManager</span>
    .<span style="color:#000000">getConnection</span>(<span style="color:#aa1111">"jdbc:mysql://localhost:3306/bd1901"</span>,<span style="color:#aa1111">"root"</span>,<span style="color:#aa1111">"123456"</span>);
    <span style="color:#aa5500">//第三步：获取执行对象Statement</span>
    <span style="color:#000000">Statement</span> <span style="color:#000000">stat</span> <span style="color:#981a1a">=</span> <span style="color:#000000">conn</span>.<span style="color:#000000">createStatement</span>();
    <span style="color:#008855">String</span> <span style="color:#000000">sql</span> <span style="color:#981a1a">=</span> <span style="color:#aa1111">"update emp set sal = 10000,comm = 500 where empno = 9000"</span>;
    <span style="color:#aa5500">//第四步：调用方法执行sql，即发送sql</span>
    <span style="color:#008855">int</span> <span style="color:#000000">num</span> <span style="color:#981a1a">=</span> <span style="color:#000000">stat</span>.<span style="color:#000000">executeUpdate</span>(<span style="color:#000000">sql</span>);
    <span style="color:#000000">System</span>.<span style="color:#000000">out</span>.<span style="color:#000000">println</span>(<span style="color:#aa1111">"受影响的记录数目："</span><span style="color:#981a1a">+</span><span style="color:#000000">num</span>);
    <span style="color:#aa5500">//第五步：关闭连接</span>
    <span style="color:#000000">conn</span>.<span style="color:#000000">close</span>();
}</span></span>

2.3 案例2：JDBC查询数据

<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">public</span> <span style="color:#008855">void</span> <span style="color:#0000ff">test2</span>(){
    <span style="color:#000000">Connection</span> <span style="color:#000000">conn</span> <span style="color:#981a1a">=</span> <span style="color:#221199">null</span>;
    <span style="color:#000000">Statement</span> <span style="color:#000000">stat</span> <span style="color:#981a1a">=</span> <span style="color:#221199">null</span>;
    <span style="color:#000000">ResultSet</span> <span style="color:#000000">rs</span> <span style="color:#981a1a">=</span> <span style="color:#221199">null</span>;
    <span style="color:#770088">try</span>{
        <span style="color:#aa5500">//注册驱动</span>
        <span style="color:#000000">Class</span>.<span style="color:#000000">forName</span>(<span style="color:#aa1111">"com.mysql.jdbc.Driver"</span>);
        <span style="color:#aa5500">//建立连接</span>
        <span style="color:#000000">conn</span> <span style="color:#981a1a">=</span> <span style="color:#000000">DriverManager</span>
        .<span style="color:#000000">getConnection</span>(<span style="color:#aa1111">"jdbc:mysql://localhost:3306/bd1901"</span>,<span style="color:#aa1111">"root"</span>,<span style="color:#aa1111">"123456"</span>);
        <span style="color:#aa5500">//获取执行对象，执行sql</span>
        <span style="color:#000000">stat</span> <span style="color:#981a1a">=</span> <span style="color:#000000">conn</span>.<span style="color:#000000">createStatement</span>();
        <span style="color:#aa5500">//返回结果集</span>
        <span style="color:#000000">rs</span> <span style="color:#981a1a">=</span> <span style="color:#000000">stat</span>.<span style="color:#000000">executeQuery</span>(<span style="color:#aa1111">"select * from emp"</span>);
        <span style="color:#770088">while</span>(<span style="color:#000000">rs</span>.<span style="color:#000000">next</span>()){
            <span style="color:#008855">int</span> <span style="color:#000000">empno</span> <span style="color:#981a1a">=</span> <span style="color:#000000">rs</span>.<span style="color:#000000">getInt</span>(<span style="color:#116644">1</span>);
            <span style="color:#008855">String</span> <span style="color:#000000">ename</span> <span style="color:#981a1a">=</span> <span style="color:#000000">rs</span>.<span style="color:#000000">getString</span>(<span style="color:#aa1111">"ename"</span>);
            <span style="color:#000000">Date</span> <span style="color:#000000">hiredate</span> <span style="color:#981a1a">=</span> <span style="color:#000000">rs</span>.<span style="color:#000000">getDate</span>(<span style="color:#aa1111">"hiredate"</span>);
            <span style="color:#008855">double</span> <span style="color:#000000">salary</span> <span style="color:#981a1a">=</span> <span style="color:#000000">rs</span>.<span style="color:#000000">getDouble</span>(<span style="color:#aa1111">"sal"</span>);
            <span style="color:#000000">System</span>.<span style="color:#000000">out</span>.<span style="color:#000000">println</span>(<span style="color:#000000">empno</span><span style="color:#981a1a">+</span><span style="color:#aa1111">","</span><span style="color:#981a1a">+</span><span style="color:#000000">ename</span><span style="color:#981a1a">+</span><span style="color:#aa1111">","</span><span style="color:#981a1a">+</span><span style="color:#000000">hiredate</span><span style="color:#981a1a">+</span><span style="color:#aa1111">","</span><span style="color:#981a1a">+</span><span style="color:#000000">salary</span>);
        }
    }<span style="color:#770088">catch</span> (<span style="color:#000000">Exception</span> <span style="color:#000000">e</span>){
        <span style="color:#000000">e</span>.<span style="color:#000000">printStackTrace</span>();
    }<span style="color:#770088">finally</span> {
        <span style="color:#770088">try</span> {
            <span style="color:#000000">rs</span>.<span style="color:#000000">close</span>();
            <span style="color:#000000">stat</span>.<span style="color:#000000">close</span>();
            <span style="color:#000000">conn</span>.<span style="color:#000000">close</span>();
        } <span style="color:#770088">catch</span> (<span style="color:#000000">SQLException</span> <span style="color:#000000">e</span>) {
            <span style="color:#000000">e</span>.<span style="color:#000000">printStackTrace</span>();
        }
    }
}</span></span>

2.4 练习：JDBC插入和删除数据

<span style="background-color:#f8f8f8"><span style="color:#333333">1.完成向表中插入一条记录的代码
2.完成从表中删除一条记录的代码</span></span>

2.5 DBUtil工具类的封装

第一步：编写配置文件properties

<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#aa5500">#低版本的mysql的驱动和url</span>
<span style="color:#aa5500">#driver=com.mysql.jdbc.Driver</span>
<span style="color:#aa5500">#url=jdbc:mysql://localhost:3306/bd1906?useUnicode=true&characterEncoding=utf8</span>

<span style="color:#aa5500">#高版本的mysql的驱动和url</span>
<span style="color:#0000ff">driver</span>=<span style="color:#009900">com.mysql.cj.jdbc.Driver</span>
<span style="color:#0000ff">url</span>=<span style="color:#009900">jdbc</span>:<span style="color:#009900">mysql</span>:<span style="color:#009900">//localhost</span>:<span style="color:#009900">3306/mydb?serverTimezone</span>=<span style="color:#009900">Asia/Shanghai&useTimezone</span>=<span style="color:#009900">true</span>
<span style="color:#0000ff">username</span>=<span style="color:#009900">root</span>
<span style="color:#0000ff">passwd</span>=<span style="color:#009900">mmforu</span></span></span>

第二步：定义工具类DBUtil，读取配置文件，定义连接方法，关闭方法等

<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#000000">`</span><span style="color:#aa5500">/**</span>
 <span style="color:#aa5500">* 封装了一些对数据库的连接和关闭操作</span>
 <span style="color:#aa5500">*/</span>
<span style="color:#770088">public</span> <span style="color:#770088">class</span> <span style="color:#0000ff">DBUtil</span> {
    <span style="color:#770088">private</span> <span style="color:#770088">static</span> <span style="color:#008855">String</span> <span style="color:#000000">driver</span>;
    <span style="color:#770088">private</span> <span style="color:#770088">static</span> <span style="color:#008855">String</span> <span style="color:#000000">url</span>;
    <span style="color:#770088">private</span> <span style="color:#770088">static</span> <span style="color:#008855">String</span> <span style="color:#000000">username</span>;
    <span style="color:#770088">private</span> <span style="color:#770088">static</span> <span style="color:#008855">String</span> <span style="color:#000000">password</span>;
    <span style="color:#770088">static</span>{

        <span style="color:#770088">try</span>{
            <span style="color:#aa5500">//读取配置文件：</span>
            <span style="color:#000000">InputStream</span> <span style="color:#000000">is</span> <span style="color:#981a1a">=</span> <span style="color:#000000">DBUtil</span>.<span style="color:#770088">class</span>.<span style="color:#000000">getClassLoader</span>()
            .<span style="color:#000000">getResourceAsStream</span>(<span style="color:#aa1111">"db.properties"</span>);
            <span style="color:#aa5500">//获取Properties对象</span>
            <span style="color:#000000">Properties</span> <span style="color:#000000">p</span> <span style="color:#981a1a">=</span> <span style="color:#770088">new</span> <span style="color:#000000">Properties</span>();
            <span style="color:#aa5500">//把流里的内容加载到p对象中</span>
            <span style="color:#000000">p</span>.<span style="color:#000000">load</span>(<span style="color:#000000">is</span>);
            <span style="color:#aa5500">//取value值</span>
            <span style="color:#000000">driver</span> <span style="color:#981a1a">=</span> <span style="color:#000000">p</span>.<span style="color:#000000">getProperty</span>(<span style="color:#aa1111">"driver"</span>);
            <span style="color:#000000">url</span> <span style="color:#981a1a">=</span> <span style="color:#000000">p</span>.<span style="color:#000000">getProperty</span>(<span style="color:#aa1111">"url"</span>);
            <span style="color:#000000">username</span> <span style="color:#981a1a">=</span> <span style="color:#000000">p</span>.<span style="color:#000000">getProperty</span>(<span style="color:#aa1111">"username"</span>);
            <span style="color:#000000">password</span> <span style="color:#981a1a">=</span> <span style="color:#000000">p</span>.<span style="color:#000000">getProperty</span>(<span style="color:#aa1111">"pwd"</span>);
            <span style="color:#aa5500">//注册驱动</span>
            <span style="color:#000000">Class</span>.<span style="color:#000000">forName</span>(<span style="color:#000000">driver</span>);
        }<span style="color:#770088">catch</span> (<span style="color:#000000">Exception</span> <span style="color:#000000">e</span>){
            <span style="color:#000000">e</span>.<span style="color:#000000">printStackTrace</span>();
        }
    }
    <span style="color:#aa5500">/**</span>
     <span style="color:#aa5500">* 连接数据库的方法</span>
     <span style="color:#aa5500">* @return Connection对象</span>
     <span style="color:#aa5500">*/</span>
    <span style="color:#770088">public</span> <span style="color:#770088">static</span> <span style="color:#000000">Connection</span> <span style="color:#000000">getConnection</span>() <span style="color:#770088">throws</span> <span style="color:#000000">SQLException</span>{
        <span style="color:#000000">Connection</span> <span style="color:#000000">conn</span> <span style="color:#981a1a">=</span> <span style="color:#000000">DriverManager</span>.<span style="color:#000000">getConnection</span>(<span style="color:#000000">url</span>,<span style="color:#000000">username</span>,<span style="color:#000000">password</span>);
        <span style="color:#770088">return</span> <span style="color:#000000">conn</span>;
    }

    <span style="color:#aa5500">/**</span>
     <span style="color:#aa5500">* 关闭连接操作</span>
     <span style="color:#aa5500">*  关闭ResultSet对象</span>
     <span style="color:#aa5500">*  关闭Statement对象</span>
     <span style="color:#aa5500">*  关闭Connection对象</span>
     <span style="color:#aa5500">*/</span>
    <span style="color:#770088">public</span> <span style="color:#770088">static</span> <span style="color:#008855">void</span> <span style="color:#000000">closeConnection</span>(<span style="color:#000000">Connection</span> <span style="color:#000000">conn</span>, <span style="color:#000000">Statement</span> <span style="color:#000000">stat</span>, <span style="color:#000000">ResultSet</span> <span style="color:#000000">rs</span>){
        <span style="color:#770088">try</span> {
            <span style="color:#770088">if</span>(<span style="color:#000000">rs</span><span style="color:#981a1a">!=</span><span style="color:#221199">null</span>){
                <span style="color:#000000">rs</span>.<span style="color:#000000">close</span>();
            }
            <span style="color:#770088">if</span>(<span style="color:#000000">stat</span><span style="color:#981a1a">!=</span><span style="color:#221199">null</span>){
                <span style="color:#000000">stat</span>.<span style="color:#000000">close</span>();
            }
            <span style="color:#770088">if</span>(<span style="color:#000000">conn</span> <span style="color:#981a1a">!=</span><span style="color:#221199">null</span>){
                <span style="color:#000000">conn</span>.<span style="color:#000000">close</span>();
            }
        } <span style="color:#770088">catch</span> (<span style="color:#000000">SQLException</span> <span style="color:#000000">e</span>) {
            <span style="color:#000000">e</span>.<span style="color:#000000">printStackTrace</span>();
        }
    }

    <span style="color:#770088">public</span> <span style="color:#770088">static</span> <span style="color:#008855">void</span> <span style="color:#000000">main</span>(<span style="color:#008855">String</span>[] <span style="color:#000000">args</span>) <span style="color:#770088">throws</span> <span style="color:#000000">SQLException</span> {
        <span style="color:#000000">Connection</span> <span style="color:#000000">conn</span> <span style="color:#981a1a">=</span> <span style="color:#000000">getConnection</span>();
        <span style="color:#000000">System</span>.<span style="color:#000000">out</span>.<span style="color:#000000">println</span>(<span style="color:#000000">conn</span>);
        <span style="color:#000000">closeConnection</span>(<span style="color:#000000">conn</span>,<span style="color:#221199">null</span>,<span style="color:#221199">null</span>);
    }
}<span style="color:#000000">`</span></span></span>

第三步：调用DBUtil工具类，进行测试

1）update代码的修改

<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">public</span> <span style="color:#770088">static</span> <span style="color:#008855">void</span> <span style="color:#0000ff">main</span>(<span style="color:#008855">String</span>[] <span style="color:#000000">args</span>) <span style="color:#770088">throws</span> <span style="color:#000000">ClassNotFoundException</span>, <span style="color:#000000">SQLException</span> {
        <span style="color:#000000">Connection</span> <span style="color:#000000">conn</span> <span style="color:#981a1a">=</span> <span style="color:#000000">DBUtil</span>.<span style="color:#000000">getConnection</span>();
        <span style="color:#aa5500">//第三步：获取执行对象Statement</span>
        <span style="color:#000000">Statement</span> <span style="color:#000000">stat</span> <span style="color:#981a1a">=</span> <span style="color:#000000">conn</span>.<span style="color:#000000">createStatement</span>();
        <span style="color:#008855">String</span> <span style="color:#000000">sql</span> <span style="color:#981a1a">=</span> <span style="color:#aa1111">"update emp set sal = 10000,comm = 500 where empno = 9000"</span>;
        <span style="color:#aa5500">//第四步：调用方法执行sql，即发送sql</span>
        <span style="color:#008855">int</span> <span style="color:#000000">num</span> <span style="color:#981a1a">=</span> <span style="color:#000000">stat</span>.<span style="color:#000000">executeUpdate</span>(<span style="color:#000000">sql</span>);
        <span style="color:#000000">System</span>.<span style="color:#000000">out</span>.<span style="color:#000000">println</span>(<span style="color:#aa1111">"受影响的记录数目："</span><span style="color:#981a1a">+</span><span style="color:#000000">num</span>);
        <span style="color:#aa5500">//第五步：关闭连接</span>
        <span style="color:#000000">DBUtil</span>.<span style="color:#000000">closeConnection</span>(<span style="color:#000000">conn</span>,<span style="color:#000000">stat</span>,<span style="color:#221199">null</span>);
}</span></span>

2）DQL代码的修改

<span style="background-color:#f8f8f8"><span style="color:#333333"><span style="color:#770088">public</span> <span style="color:#008855">void</span> <span style="color:#0000ff">test1</span>(){
        <span style="color:#000000">Connection</span> <span style="color:#000000">conn</span> <span style="color:#981a1a">=</span> <span style="color:#221199">null</span>;
        <span style="color:#000000">Statement</span> <span style="color:#000000">stat</span> <span style="color:#981a1a">=</span> <span style="color:#221199">null</span>;
        <span style="color:#000000">ResultSet</span> <span style="color:#000000">rs</span> <span style="color:#981a1a">=</span> <span style="color:#221199">null</span>;
        <span style="color:#770088">try</span>{
            <span style="color:#000000">conn</span> <span style="color:#981a1a">=</span> <span style="color:#000000">DBUtil</span>.<span style="color:#000000">getConnection</span>();
            <span style="color:#aa5500">//获取执行对象，执行sql</span>
            <span style="color:#000000">stat</span> <span style="color:#981a1a">=</span> <span style="color:#000000">conn</span>.<span style="color:#000000">createStatement</span>();
            <span style="color:#aa5500">//返回结果集</span>
            <span style="color:#000000">rs</span> <span style="color:#981a1a">=</span> <span style="color:#000000">stat</span>.<span style="color:#000000">executeQuery</span>(<span style="color:#aa1111">"select * from emp"</span>);
            <span style="color:#770088">while</span>(<span style="color:#000000">rs</span>.<span style="color:#000000">next</span>()){
               <span style="color:#008855">int</span> <span style="color:#000000">empno</span> <span style="color:#981a1a">=</span> <span style="color:#000000">rs</span>.<span style="color:#000000">getInt</span>(<span style="color:#116644">1</span>);
               <span style="color:#008855">String</span> <span style="color:#000000">ename</span> <span style="color:#981a1a">=</span> <span style="color:#000000">rs</span>.<span style="color:#000000">getString</span>(<span style="color:#aa1111">"ename"</span>);
               <span style="color:#000000">Date</span> <span style="color:#000000">hiredate</span> <span style="color:#981a1a">=</span> <span style="color:#000000">rs</span>.<span style="color:#000000">getDate</span>(<span style="color:#aa1111">"hiredate"</span>);
               <span style="color:#008855">double</span> <span style="color:#000000">salary</span> <span style="color:#981a1a">=</span> <span style="color:#000000">rs</span>.<span style="color:#000000">getDouble</span>(<span style="color:#aa1111">"sal"</span>);
               <span style="color:#000000">System</span>.<span style="color:#000000">out</span>.<span style="color:#000000">println</span>(<span style="color:#000000">empno</span><span style="color:#981a1a">+</span><span style="color:#aa1111">","</span><span style="color:#981a1a">+</span><span style="color:#000000">ename</span><span style="color:#981a1a">+</span><span style="color:#aa1111">","</span><span style="color:#981a1a">+</span><span style="color:#000000">hiredate</span><span style="color:#981a1a">+</span><span style="color:#aa1111">","</span><span style="color:#981a1a">+</span><span style="color:#000000">salary</span>);
           }
        }<span style="color:#770088">catch</span> (<span style="color:#000000">Exception</span> <span style="color:#000000">e</span>){
            <span style="color:#000000">e</span>.<span style="color:#000000">printStackTrace</span>();
        }<span style="color:#770088">finally</span> {
           <span style="color:#000000">DBUtil</span>.<span style="color:#000000">closeConnection</span>(<span style="color:#000000">conn</span>,<span style="color:#000000">stat</span>,<span style="color:#000000">rs</span>);
        }
}<span style="color:#000000">`</span></span></span>

2.6 jdbc的批处理

每一次的sql操作都会占用数据库的资源。如果将N条操作先存储到缓存区中，然后再一次性刷到数据库中，这就减少了与数据库的交互次数。因此可以提高效率。

Statement 提供了以下两个常用的方法，用做批处理

<span style="background-color:#f8f8f8"><span style="color:#333333">addBatch(String sql):将sql语句添加到缓存中
executeBatch():将缓存中的sql一次性刷到数据库中</span></span>

案例演示：

<span style="background-color:#f8f8f8"><span style="color:#333333">create  table  testbatch(
id int primary key auto_increment,
name varchar(20),
gender char(1)
);</span></span>

<span style="background-color:#f8f8f8"><span style="color:#333333">@Test
public void testBatch(){
    Connection conn = null;
    Statement stat = null;
    try{
        conn = DBUtil.getConnection();
        stat = conn.createStatement();
        int num = 0;
        while(num<1003){
            String sql = "insert into testbatch values (null,'zs"+num+"','f')";
            stat.addBatch(sql);//将sql语句添加到缓存中，
            if(num%50==0){
                stat.executeBatch();//缓存中每有50条都刷新一次。
            }
            num++;
        }
        stat.executeBatch();//循环结束后，将缓存中剩余的不足50条的全都刷新出去
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        DBUtil.closeConnection(conn,stat,null);
    }
}</span></span>

三 SQL注入问题

3.1 登陆案例

3.1.1 需求分析以及数据准备

<span style="background-color:#f8f8f8"><span style="color:#333333">1.需求：实现输入用户名和密码后，实现跳转到主页面的功能
2.逻辑分析：
	- 客户端:接收用户名和密码，并将这些信息发送到服务端
	- 服务端：接收到客户端传过来的用户名和密码后，进行数据库校验是否存在这样的数据，如果存在，就将
			用户名对应的这一条记录返回，并封装成一个User对象。返回给客户端。
	- 客户端收到返回信息后，判断Account对象是否存在，如果存在，就实现跳转.....</span></span>

数据如下：

<span style="background-color:#f8f8f8"><span style="color:#333333">drop table if exists bank_account;
create table bank_account (
   id int primary key auto_increment comment '主键',
   account_id varchar(18) not null comment '用户账号',
   account_balance double(10,2) comment '账户余额',
   user_name varchar(20) not null comment '用户名称',
   user_pwd varchar(18) unique comment '用户密码',
   user_idcard varchar(18) unique comment '身份证',
   oper_time timestamp comment '操作日期',
   gender char(1) check(gender in('f','m')) comment 'f 表示女性，m表示男性'
);

insert into bank_account values (null,'6225113088436225',200000,'zhugeliang','zgl123456','100000100010101000','2019-01-01 13:10:10','m');
insert into bank_account values (null,'6225113088436226',1000,'zhouyu','zy123456','100000100010101001','2019-03-01 14:10:10','m');
insert into bank_account values (null,'6225113088436227',210000,'caocao','cc123456','100000100010101002','2019-04-01 14:10:10','m');
insert into bank_account values (null,'6225113088436228',500,'niumo','nm123456','100000100010101003','2019-03-01 10:10:10','m');

commit;
select * from banK_account;</span></span>

3.1.2 Account类型的定义

<span style="background-color:#f8f8f8"><span style="color:#333333">package com.jdbc.day02.login;

import java.sql.Date;
import java.util.Objects;

/**
 * Account类型 对应bank_account表的字段。
 * 类型的属性对应表的字段
 * 这样做的目的是，可以把表中的每一条记录封装成一个Account对象
 */
public class Account {
    private int id;
    private String accountId;
    private double balance;
    private String username;
    private String password;
    private String idcard;
    private Date operationTime;
    private String gender;

    public Account(){}

    public Account(int id, String accountId, double balance, String username, String password, String idcard, Date operationTime, String gender) {
        this.id = id;
        this.accountId = accountId;
        this.balance = balance;
        this.username = username;
        this.password = password;
        this.idcard = idcard;
        this.operationTime = operationTime;
        this.gender = gender;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getAccountId() {
        return accountId;
    }

    public void setAccountId(String accountId) {
        this.accountId = accountId;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getIdcard() {
        return idcard;
    }

    public void setIdcard(String idcard) {
        this.idcard = idcard;
    }

    public Date getOperationTime() {
        return operationTime;
    }

    public void setOperationTime(Date operationTime) {
        this.operationTime = operationTime;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Account account = (Account) o;
        return id == account.id &&
                Double.compare(account.balance, balance) == 0 &&
                Objects.equals(accountId, account.accountId) &&
                Objects.equals(username, account.username) &&
                Objects.equals(password, account.password) &&
                Objects.equals(idcard, account.idcard) &&
                Objects.equals(operationTime, account.operationTime) &&
                Objects.equals(gender, account.gender);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, accountId, balance, username, password, idcard, operationTime, gender);
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", accountId='" + accountId + '\'' +
                ", balance=" + balance +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", idcard='" + idcard + '\'' +
                ", operationTime=" + operationTime +
                ", gender='" + gender + '\'' +
                '}';
    }
}</span></span>

3.1.3 服务端的代码

<span style="background-color:#f8f8f8"><span style="color:#333333">package com.jdbc.day02.login;

import com.jdbc.util.DButil;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * 服务端：设置一个静态方法，用于检查数据库中是否存在含有
 *  客户端发送过来的username和password这样的账号信息
 *  返回值：为Account账号对象
 */
public class AppServer {
    public static Account checkLogin(String username,String password){
        //将形式参数放到一个sql语句中，发送到数据库中，进行检验
        Connection conn =null;
        Statement stat = null;
        ResultSet rs = null;
        Account account = null;
        try{
            conn = DButil.getConnection();
            stat = conn.createStatement();
            String sql = "select * from bank_account where user_name='"+username+"' and user_pwd='"+password+"'";
            rs = stat.executeQuery(sql);
            if(rs.next()){
                int id = rs.getInt("id");
                String accountId = rs.getString("account_id");
                double balance = rs.getDouble("account_balance");
                String idcard = rs.getString("user_idcard");
                Date operationTime = rs.getDate("oper_time");
                String gender = rs.getString("gender");
                account = new Account(id,accountId,balance,username,password,idcard,operationTime,gender);
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DButil.closeConnection(conn,stat,rs);
        }
        return account;
    }
}
</span></span>

3.1.4 客户端的代码

<span style="background-color:#f8f8f8"><span style="color:#333333">package com.jdbc.day02.login;

import java.util.Scanner;

/**
 * 模拟银行客户端程序
 */
public class Client {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username = sc.next();
        System.out.println("请输入密码：");
        String password = "haha' or '1' = '1";
        /**
         * 将数据发送到服务端：（调用服务端的相关方法）
         * 即调用服务端的静态方法checkLogin().
         * 如果能查询到信息，就返回一个Account类型的对象
         */
        Account account = AppServer.checkLogin(username,password);
        if(account!=null){
            System.out.println("--正在跳转到主页面--");
        }else{
            System.out.println("----用户名或密码错误，请重写输入----------");
        }
    }
}
</span></span>

3.2 SQL注入问题(安全隐患)

Statament对象发送的语句可以被改变结构，即如果之前在where中设置的是两个条件，那么可以通过一些参数比如添加or 后面再跟其他条件。此时，where子句中是三个条件。

这种情况就叫做SQL注入。有安全隐患问题。

3.3 PreparedStatement类

3.3.1 预编译类的简介

<span style="background-color:#f8f8f8"><span style="color:#333333">- PreparedStatement是Statement的子类型
- 此类型可以确定SQL语句的结构，无法通过其它方式来增减条件。
- 此类型还通过占位符 "？"来提前占位,并确定语句的结构。
- 提供了相应的赋值方式：
    ps.setInt(int index,int value) 
    ps.setString(int index,String value) 
    ps.setDouble(int index,double value) 
    ps.setDate(int index,Date value)

	index:表示sql语句中占位符？的索引。从1开始 
	value:占位符所对应的要赋予的值
- 执行方法： 
    ps.execute() ;------用于DDL和DML 
    ps.executeUpdate();-----用于DML 
    ps.executeQuery();-----用于DQL</span></span>

3.3.2 修改了Server里的代码

<span style="background-color:#f8f8f8"><span style="color:#333333">public class PrepareServer {
    public static User checkLogin(String username,String pwd){
        if(username==null||username.length()==0){
            return null;
        }
        if(pwd==null||pwd.length()==0){
            return null;
        }
        //将数据传入持久层
        Connection conn = null;
        // 将Statement 换成 PreparedStatement
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        try{
            conn = DBUtil.getConnection();
            //sql语句放在获取预编译执行对象前，同时可以使用它占位符
            String sql = "select * from emp where ename = ? and empno = ?";
            //获取预编译执行对象，同时传入sql参数
            ps = conn.prepareStatement(sql);
            //给占位符赋值
            ps.setString(1,username);
            ps.setInt(2,Integer.valueOf(pwd));

            //调用执行查询的方法，不带参数。
            rs = ps.executeQuery();
            if(rs.next()){
                int empno = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString(3);
                int mgr = rs.getInt(4);
                Date hiredate = rs.getDate(5);
                double salary = rs.getDouble(6);
                double comm = rs.getDouble(7);
                int deptno = rs.getInt(8);
                user = new User(empno,ename,job,mgr,hiredate,salary,comm,deptno);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn,ps,rs);
        }
        return user;
    }
}</span></span>

3.3.3 客户端的代码

<span style="background-color:#f8f8f8"><span style="color:#333333">public class Client {
    public static void main(String[] args) {
        //        Scanner sc = new Scanner(System.in);
        //        System.out.println("请输入用户名：");
        //        String username = sc.next();
        //        System.out.println("请输入密码：");
        //        String password = sc.next();

        String username = "smith";
        String password = "222 or 1=1";
        //调用服务端的代码
        //User user = Server.checkLogin(username,password);
        //换成修改后的服务端的代码
        User user = PrepareServer.checkLogin(username,password);
        if(user!=null){
            System.out.println("登陆成功，页面跳转中..........");
        }else{
            System.out.println("登陆失败，请检查用户名或者密码");
        }
    }
}</span></span>

四 JDBC的事务支持

4.1 银行转账案例演示

4.4.1 案例分析：

<span style="background-color:#f8f8f8"><span style="color:#333333">1.需求：一个账号fromAccount向另一个账号toAccount转入money元钱
2.分析：
	- 检查两个账号是否存在，不存在的话，结束转账行为
	- 检查转出账号的里金额是否充足，不充足，结束转账行为,充足的话，进行扣款money元
	- 转入账号进行增加money元</span></span>

4.4.2 代码实现：

<span style="background-color:#f8f8f8"><span style="color:#333333">public class AccountTest {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入要出账的账号和进账的账号");
        String fromAccount = sc.next();
        String toAccount = sc.next();
        System.out.println("请输入转账金额：");
        double money = sc.nextDouble();
        boolean flag = oneToOne(fromAccount,toAccount,money);
        if(flag){
            System.out.println("success");
        }else{
            System.out.println("fail");
        }
    }

    /**
     * 封装了两个人之间的转账逻辑
     *  true:表示转账成功
     *  false：表示转账失败
     */
    public static boolean oneToOne(String fromAccount,String toAccount,double money){
        /*第一步：先校验账户信息是否有效*/
        if(fromAccount==null||fromAccount.length()==0){
            return false;
        }
        if(toAccount==null||toAccount.length()==0){
            return false;
        }
        if(money<=0){
            return false;
        }
        Connection conn = null;
        //预编译sql语句执行对象
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            
            //验证转出账号是否存在
            conn = DBUtil.getConnection();
            String sql1 = "select * from bank_account where account_id=?";
            ps = conn.prepareStatement(sql1);
            ps.setString(1,fromAccount);
            rs = ps.executeQuery();
            boolean flag = rs.next();
            if(!flag){
                System.out.println("出账账号不存在");
                return false;
            }
            
            //，如果存在，先提取出余额，保存到一个变量中
            double balance = rs.getDouble("account_balance");

            //验证进账账号是否存在
            sql1 = "select * from bank_account where account_id=?";
            ps = conn.prepareStatement(sql1);
            ps.setString(1,toAccount);
            rs = ps.executeQuery();
            flag = rs.next();
            if(!flag){
                System.out.println("进账账号不存在");
                return false;
            }
            
          
			/*第二步：检查formAccount账户内的余额，足够的前提下，修改余额*/
            //判断出账账号的余额是否充足
            if(money>balance){
                System.out.println("余额不足");
                return false;
            }

            //此时，来到此处后就可以进行转账业务了
            //先扣除出账账号的money
            String sql2 = "update bank_account set account_balance=account_balance-? where account_id = ?";
            ps = conn.prepareStatement(sql2);
            ps.setDouble(1,money);
            ps.setString(2,fromAccount);
            ps.executeUpdate();

            /*第三步：修改toAccount账户内的余额*/
            //进账账号再+money
            String sql3 = "update bank_account set account_balance=account_balance+? where account_id = ?";
            ps = conn.prepareStatement(sql3);
            ps.setDouble(1,money);
            ps.setString(2,toAccount);
            ps.executeUpdate();
            return true;
        }catch (Exception e){
            e.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn,ps,rs);
        }
        return false;
    }
}</span></span>

4.2 转账异常演示

<span style="background-color:#f8f8f8"><span style="color:#333333">在转出账户转出金额之后 和  转入账户收入金额之前模拟 空指针异常

String str = null;
System.out.println(str.length());</span></span>

4.3 JDBC的事务支持

4.3.1 事务的概念：

<span style="background-color:#f8f8f8"><span style="color:#333333">当一个业务需求涉及到N个DML操作时，这个业务(或者时N个DML操作)当成一个整体来处理。在处理的过程中，如果有失败或异常，我们要回到业务开始时。如果成功处理，我们再将数据持久化到磁盘中。这样一个过程我们称之为一个事务。具有原子性。不可切割。


关键字：
commit
rollback
savepoint</span></span>

4.3.2 事务的特性：

1.原子性

2.一致性

3.隔离性

4.持久性

4.3.3 MySQL事务

<span style="background-color:#f8f8f8"><span style="color:#333333">- 默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。
- 如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。
	开启事务：start transaction；
	结束事务：commit或rollback；</span></span>

回滚情况

<span style="background-color:#f8f8f8"><span style="color:#333333">START TRANSACTION;
UPDATE account SET balance=balance-10000 WHERE id=1;
SELECT * FROM account;
UPDATE account SET balance=balance+10000 WHERE id=2;
ROLLBACK;</span></span>

提交情况

<span style="background-color:#f8f8f8"><span style="color:#333333">START TRANSACTION;
UPDATE account SET balance=balance-10000 WHERE id=1;
SELECT * FROM account;
UPDATE account SET balance=balance+10000 WHERE id=2;
COMMIT;</span></span>

4.3.4 JDBC的事务支持

<span style="background-color:#f8f8f8"><span style="color:#333333">Connection.setAutoCommit(boolean flag):此方法可以取消事务的自动提交功能，值为false。  
Connection.commit()：进行事务提交   。  
Connection.rollback():进行事务回滚。  </span></span>

4.3.5 隔离机制

修改当前会话的隔离机制：

<span style="background-color:#f8f8f8"><span style="color:#333333">set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level read uncommitted;


查询mysql的当前会话的隔离机制：
select @@tx_isolation;</span></span>

4.4 修改转账代码：改为手动提交

<span style="background-color:#f8f8f8"><span style="color:#333333">public static boolean oneToOne(String fromAccount, String toAccount, double money) {
    /*第一步：先校验账户信息是否有效*/
    if (fromAccount == null || fromAccount.length() == 0) {
        return false;
    }
    if (toAccount == null || toAccount.length() == 0) {
        return false;
    }
    if (money <= 0) {
        return false;
    }
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBUtil.getConnection();
        
        //取消事务自动提交功能，改为手动提交
        conn.setAutoCommit(false);
        
        String sql1 = "select * from bank_account where account_id=?";
        ps = conn.prepareStatement(sql1);
        ps.setString(1, fromAccount);
        rs = ps.executeQuery();
        boolean flag = rs.next();
        if (!flag) {
            System.out.println("出账账号不存在");
            return false;
        }
        /*第二步：检查formAccount账户内的余额，足够的前提下，修改余额*/
        //取出余额
        double balance = rs.getDouble("account_balance");

        //sql1 = "select * from bank_account where account_id=?";
        //ps = conn.prepareStatement(sql1);
        ps.setString(1, toAccount);
        rs = ps.executeQuery();
        flag = rs.next();
        if (!flag) {
            System.out.println("进账账号不存在");
            return false;
        }
		
        //判断出账账号的余额是否充足
        if (money > balance) {
            System.out.println("余额不足");
            return false;
        }

        //此时，来到此处后就可以进行转账业务了
        //先扣除出账账号的money
        String sql2 = "update bank_account set account_balance=account_balance-? where account_id = ?";
        ps = conn.prepareStatement(sql2);
        ps.setDouble(1, money);
        ps.setString(2, fromAccount);
        ps.executeUpdate();

        //模拟一个空指针异常
        String str = null;
        System.out.println(str.length());
		/*第三步：修改toAccount账户内的余额*/
        //进账账号再+money
        String sql3 = "update bank_account set account_balance=account_balance+? where account_id = ?";
        ps = conn.prepareStatement(sql3);
        ps.setDouble(1, money);
        ps.setString(2, toAccount);
        ps.executeUpdate();

        //手动提交事务
        conn.commit();
        return true;
    } catch (Exception e) {
        e.printStackTrace();
        //当出现异常，我们才需要回滚事务
        try{
            conn.rollback();
        }catch (Exception e1){
            e1.printStackTrace();
        }
    } finally {
        DBUtil.closeConnection(conn, ps, rs);
    }
    return false;
}</span></span>

五数据库连接池技术

5.1 连接池技术简介：

在与数据库连接过程中，会非常消耗内存，性能大打折扣。如果每次请求都去重新连接数据库。那么，宕机的几率很高。

因此，我们可以使用连接池技术。

连接池的工作原理：

<span style="background-color:#f8f8f8"><span style="color:#333333">连接池对象在初始化阶段 一次性创建N个连接对象，这些连接对象存储在连接池对象中。当有请求过来时，先从连接池中寻找空闲连接对象并使用，当使用完后，将连接对象归还给连接池，而不是真正意义上断开连接。这样也可以满足成千上万个请求，同时并提高了数据库的性能。</span></span>

常用的连接池技术

<span style="background-color:#f8f8f8"><span style="color:#333333">- dbcp		:是apache组织旗下的一个数据库连接池技术产品
- c3p0		:是一个开源的连接池技术
- druid		:是阿里的数据库连接池技术</span></span>

5.2 dbcp

5.2.1 资源jar包:

<span style="background-color:#f8f8f8"><span style="color:#333333">commons-dbcp2-2.6.0.jar
commons-pool2-2.4.3.jar
commons-logging.jar</span></span>

5.2.2 配置文件dbcp.properties

此配置文件请放在src目录下

<span style="background-color:#f8f8f8"><span style="color:#333333">driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bd1906
username=root
pwd=123456
maxTotal=50
maxIdle=10
minIdle=3
initialSize=5
maxWaitMillis=60000</span></span>

5.2.3 DBUtildbcp类型的编写

<span style="background-color:#f8f8f8"><span style="color:#333333">import org.apache.commons.dbcp2.BasicDataSource;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 使用DBCP连接池对象
 */
public class DBUtildbcp {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    private static int maxTotal;
    private static int maxIdle;
    private static int minIdle;
    private static int initialSize;
    private static long maxWaitMillis;

    //声明一个dbcp连接池变量
    private static BasicDataSource pool;
    static{
        try{
            pool = new BasicDataSource();//连接池对象

            //使用类加载器中提供的方法来获取字节流对象，同时指定配置文件
            InputStream is = DBUtildbcp.class.getClassLoader()
                .getResourceAsStream("db.properties");
            Properties prop = new Properties();
            prop.load(is);//将配置文件里的内容封装到prop对象内

            driver = prop.getProperty("driver");
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("pwd");
            maxTotal= Integer.parseInt(prop.getProperty("maxTotal"));
            maxIdle= Integer.parseInt(prop.getProperty("maxIdle"));
            minIdle= Integer.parseInt(prop.getProperty("minIdle"));
            initialSize= Integer.parseInt(prop.getProperty("initialSize"));
            maxWaitMillis= Long.parseLong(prop.getProperty("maxWaitMillis"));

            pool.setDriverClassName(driver);
            pool.setUrl(url);
            pool.setUsername(username);
            pool.setPassword(password);
            //连接池支持的最大连接数
            pool.setMaxTotal(maxTotal);
            //连接池支持的最大空闲数
            pool.setMaxIdle(maxIdle);
            //支持的最小空闲数
            pool.setMinIdle(minIdle);
            //连接池对象创建时初始化的连接数
            pool.setInitialSize(initialSize);
            //空闲等待时间
            pool.setMaxWaitMillis(maxWaitMillis);
            //注册驱动
            Class.forName(driver);

        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        //从连接池中获取空闲对象
        return pool.getConnection();
    }
    public static void closeConnection(Connection conn, Statement stat, ResultSet rs){
        try {
            if(rs!=null){
                rs.close();
            }
            if(stat!=null){
                stat.close();
            }
            if(conn !=null){
                conn.close();   //会将连接对象归还给连接池内
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException {
        Connection conn = getConnection();
        System.out.println(conn);
        conn.close();
    }
}</span></span>

5.3 c3p0

5.3.1 资源jar包

<span style="background-color:#f8f8f8"><span style="color:#333333">c3p0-0.9.5-pre8.jar
mchange-commons-java-0.2.7.jar</span></span>

5.3.2 配置文件c3p0-config.xml

配置文件请放在src目录下

<span style="background-color:#f8f8f8"><span style="color:#333333"><?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/bd1901</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="acquireIncrement">10</property>
        <property name="maxPoolSize">50</property>
        <property name="minPoolSize">2</property>
        <property name="initialPoolSize">5</property>
        <property name="maxIdleTime">600</property>
    </default-config>
</c3p0-config></span></span>

5.3.3 DBUtilc3p0类型的编写

<span style="background-color:#f8f8f8"><span style="color:#333333">import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtilC3p0 {
    //构造器会自动检索src下有没有指定文件名称的配置文件，然后会自动赋值给其相应的属性
    private static ComboPooledDataSource pool = new ComboPooledDataSource("c3p0-config");

    public static Connection getConnection() throws SQLException {
        //从连接池中获取空闲对象
        return pool.getConnection();
    }
    public static void closeConnection(Connection conn, Statement stat, ResultSet rs){
        try {
            if(rs!=null){
                rs.close();
            }
            if(stat!=null){
                stat.close();
            }
            if(conn !=null){
                conn.close();   //会将连接对象归还给连接池内
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException {
        Connection conn = getConnection();
        System.out.println(conn);
        conn.close();

    }
}</span></span>

5.4 druid

5.4.1 资源jar包

<span style="background-color:#f8f8f8"><span style="color:#333333">druid-1.1.18.jar</span></span>

5.4.2 配置文件druid.properties

放在src目录下。注意，前面的key值是固定写法

<span style="background-color:#f8f8f8"><span style="color:#333333">driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bd1901
username=root
password=123456
maxActive=20
minIdle=3
initialSize=5
maxWait=60000</span></span>

5.4.3 DBUtildruid类型的编写

<span style="background-color:#f8f8f8"><span style="color:#333333">import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DBUtil_druid {
    //创建连接池对象
    private static DataSource pool = null;
    static {
        try {
            //使用类加载器提供的方法读取db.properties,返回一个字节流对象
            InputStream is = DBUtil_druid.class.getClassLoader().
                getResourceAsStream("druid.properties");
            //创建Properties对象，用于加载流内部的数据
            Properties prop = new Properties();
            prop.load(is); //加载流内部的信息，以key-value的形式进行加载
            //调用静态方法，会自动给自己的属性赋值
            pool = DruidDataSourceFactory.createDataSource(prop);    
        } catch (Exception e) {
            System.out.println("注册驱动失败");
            e.printStackTrace();
        }
    }
    /**
     * 获取连接对象
     *
     * @return 连接对象
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static Connection getConnection() throws SQLException, 
    ClassNotFoundException {

        //return DriverManager.getConnection(url, username, password);
        //从连接池中获取连接对象
        return pool.getConnection();
    }

    /**
     * 关闭数据库连接
     *
     * @param rs   结果集对象
     * @param stat 处理sql的执行对象Statement
     * @param conn 连接对象
     */
    public static void closeConnection(ResultSet rs, Statement stat, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stat != null) {
                stat.close();
            }
            if (conn != null) {
                conn.close();//释放连接，归还给连接池
            }
        } catch (Exception e) {
            System.out.println("数据库连接关闭失败");
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection conn = getConnection();
        System.out.println(conn);
        closeConnection(null, null, conn);
    }
}</span></span>

六 DAO设计模式

6.1 DAO简介

<span style="background-color:#f8f8f8"><span style="color:#333333">- DAO是数据访问对象(Data Access Object)的简写。
- 建立在数据库与业务层之间，封装所有对数据库的访问操作，我们也可称之为持久层。
- 目的: 将数据访问逻辑和业务逻辑分开。</span></span>

一个DAO设计模式包含以下内容

定义实体类: 通过对象关系映射(ORM)将数据库的表结构映射成java类型;表中的每一条记录映射成类的实例。用于数据的传递。
定义一个接口：在此接口中，定义应用程序对此表的所有访问操作，如增，删，改、查，等方法。
定义接口的实现类实现接口中的所有抽象方法。
定义一个DAO工厂类型用于返回接口实例这样，开发人员只需要使用DAO接口即可，具体逻辑就变得透明了，无需了解内部细节。

扩展：项目的包名命名规则

<span style="background-color:#f8f8f8"><span style="color:#333333">规范： com.域名.项目名称.模块名称

com.qianfeng.jdbc03.util
com.qianfeng.jdbc03.entity
com.qianfeng.jdbc03.test
com.qianfeng.jdbc03.dao
com.qianfeng.jdbc03.dao.impl
com.qianfeng.jdbc03.service</span></span>

6.2 DAO的案例示范

6.2.1 创建项目，导入相关资源

6.2.2 编写工具类DBUtil

6.2.3 编写实体类

<span style="background-color:#f8f8f8"><span style="color:#333333">import java.sql.Date;
import java.util.Objects;
/**
 * 以orm关系将数据库中的emp表映射成java中的Emp类型
 *  表的字段映射成类的属性
 */
public class Emp {
    private int empno;
    private String ename;
    private String job;
    private int mgr;
    private Date hiredate;
    private double salary;
    private double comm;
    private int deptno;
    public Emp(){}

    public Emp(int empno, String ename, String job, int mgr, Date hiredate, double salary, double comm, int deptno) {
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.salary = salary;
        this.comm = comm;
        this.deptno = deptno;
    }

    public int getEmpno() {
        return empno;
    }

    public void setEmpno(int empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public int getMgr() {
        return mgr;
    }

    public void setMgr(int mgr) {
        this.mgr = mgr;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public double getComm() {
        return comm;
    }

    public void setComm(double comm) {
        this.comm = comm;
    }

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Emp emp = (Emp) o;
        return empno == emp.empno &&
            mgr == emp.mgr &&
            Double.compare(emp.salary, salary) == 0 &&
            Double.compare(emp.comm, comm) == 0 &&
            deptno == emp.deptno &&
            Objects.equals(ename, emp.ename) &&
            Objects.equals(job, emp.job) &&
            Objects.equals(hiredate, emp.hiredate);
    }

    @Override
    public int hashCode() {
        return Objects.hash(empno, ename, job, mgr, hiredate, salary, comm, deptno);
    }

    @Override
    public String toString() {
        return "Emp{" +
            "empno=" + empno +
            ", ename='" + ename + '\'' +
            ", job='" + job + '\'' +
            ", mgr=" + mgr +
            ", hiredate=" + hiredate +
            ", salary=" + salary +
            ", comm=" + comm +
            ", deptno=" + deptno +
            '}';
    }
}</span></span>

6.2.4 定义接口

<span style="background-color:#f8f8f8"><span style="color:#333333">import com.qianfeng.jdbc03.entity.Emp;
import java.util.List;

/**
* 设计针对于实体类Emp和数据库里的emp表设计对数据库操作的接口
* 提供相应操作的抽象方法
*/
public interface EmpDao {
    /**
	* 提供向数据库中插入数据的方法，
	* @param e   面向对象思想可以使用实体类的实例
	*/
    void addEmp(Emp e);
    /**
	* 提供删除数据库内的一条记录方法，通过id进行删除
	* @param empno   数据库表中的主键
	*/
    void deleteById(int empno);

    /**
	* 修改方法。
	* @param e 传入前先设置成要修改的数据，然后传入方法中进行update语句赋值
	*/
    void modifyEmp(Emp e);

    /**
	* 通过唯一键查询一条记录
	* @param empno
	* @return  封装成实体类实例
	 */
    Emp findById(int empno);

    /**
	* 查询所有的记录。
 	* @return  封装成类的实例，并存入集合
	*/
    List<Emp> findAll();

    /**
	* 分页查询
	* @param page    要查询的页数
	* @param pageSize  每页显示的条数
	* @return  一页的所有记录，封装到集合中
	*/
    List<Emp> findByPage(int page,int pageSize);
}
</span></span>

6.2.5 编写实现类

<span style="background-color:#f8f8f8"><span style="color:#333333">public class EmpDaoImpl implements EmpDao {
    @Override
    public void addEmp(Emp e) {
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = DBUtil.getConnection();
            String sql = "insert into emp values (?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,e.getEmpno());
            ps.setString(2,e.getEname());
            ps.setString(3,e.getJob());
            ps.setInt(4,e.getMgr());
            ps.setDate(5,e.getHiredate());
            ps.setDouble(6,e.getSalary());
            ps.setDouble(7,e.getComm());
            ps.setInt(8,e.getDeptno());
            ps.executeUpdate();

        }catch (Exception e1){
            e1.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn,ps,null);
        }
    }

    @Override
    public void deleteById(int empno) {

    }

    @Override
    public void modifyEmp(Emp e) {

    }

    @Override
    public Emp findById(int empno) {
        return null;
    }

    @Override
    public List<Emp> findAll() {
        return null;
    }

    @Override
    public List<Emp> findByPage(int page, int pageSize) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Emp> emps = new ArrayList<Emp>();
        try{
            conn = DBUtil.getConnection();
            String sql = "select * from emp order by empno limit ?,?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,(page-1)*pageSize);
            ps.setInt(2,pageSize);

            rs = ps.executeQuery();
            Emp e = null;
            while(rs.next()){
                int empno = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString("job");
                int mgr = rs.getInt("mgr");
                Date hiredate = rs.getDate("hiredate");
                double salary = rs.getDouble("sal");
                double comm = rs.getDouble("comm");
                int deptno = rs.getInt("deptno");
                e = new Emp(empno,ename,job,mgr,hiredate,salary,comm,deptno);
                emps.add(e);
            }
        }catch (Exception e1){
            e1.printStackTrace();
        }finally{
            DBUtil.closeConnection(conn,ps,rs);
        }
        return emps;
    }
}</span></span>

6.2.6 编写DAO工厂类

<span style="background-color:#f8f8f8"><span style="color:#333333">public class DaoFactory{
	//定义属性EmpDao属性
    private static EmpDao empdao = new EmpDaoImpl();
    //让构造函数为 private，这样该类就不会被实例化
    private DaoFactory(){}
    public synchronized static EmpDao getInstance(){
        return empdao;
    }
}</span></span>

6.2.7 编写测试类

<span style="background-color:#f8f8f8"><span style="color:#333333">public class TestDao {
    @Test
    public void testAddEmp(){
        EmpDao dao = DaoFactory.getInstance();
        Emp e = new Emp(9007,"huanghua","manager",7369, 
                        Date.valueOf("2019-1-1"),3000.0,200.0,20);
        dao.addEmp(e);
    }
    @Test
    public void testFindByPage(){
        EmpDao dao = DaoFactory.getInstance();
        List<Emp> emps = dao.findByPage(3,5);
        for(Emp e:emps){
            System.out.println(e);
        }
    }
}
</span></span>

七 dbutils第三方工具类的使用

7.1 简介

<span style="background-color:#f8f8f8"><span style="color:#333333">- 此工具封装了DAO层(持久层)的逻辑。减少了开发周期。
- jar包：commons-dbutils-1.7.jar
- 常用API: 
1. QueryRunner类型:可以直接使用连接池技术来操作数据库，进行增删改查
   构造器：QueryRunner(DataSource ds)
   		  返回一个指定数据库连接池得QueryRunner对象
   非静态方法：query(String sql, ResultSetHandler<T> rsh)
			通过sql，及其ReusltSetHandler的子类型来获取数据并封装成相应对象
2. ResultSetHandler：关于结果集的一个接口。
	其实现类如下：
	BeanHandler:将查询到的数据的第一条封装成实体类对象
	BeanListHandler:将查询到的数据的第一条封装成实体类对象的集合</span></span>

7.2 代码测试：

<span style="background-color:#f8f8f8"><span style="color:#333333">public class Testdbutils {
    @Test
    public void testFindOne() throws SQLException {
        QueryRunner qr = new QueryRunner(DBUtil.getPool());
        Emp emp = qr.query("select * from emp",new BeanHandler<Emp>(Emp.class));
        System.out.println(emp);
    }
    @Test
    public void testFindOneParam() throws SQLException {
        QueryRunner qr = new QueryRunner(DBUtil.getPool());
        Emp emp = qr.query("select * from emp where empno =?",
                           new BeanHandler<Emp>(Emp.class),9007);
        System.out.println(emp);
    }
    @Test
    public void testFindAll() throws SQLException {
        QueryRunner qr = new QueryRunner(DBUtil.getPool());
        List<Emp> emp = qr.query("select * from emp",
                                 new BeanListHandler<Emp>(Emp.class));
        System.out.println(emp);
    }
}</span></span>