JDBC

JDBC，操作关系型数据库的标准规范（接口），通过这个接口调用各个数据库厂商提供的数据库驱动jar包，实现对各个数据库类的实现，增强了代码的可复用性，不依赖于单个数据库。

一、JDBC开发

注册驱动在主函数入口调用Class.forName()，连接com.mysql.jdbc.Driver;

　　　　从jdbc3开始可以省略注册驱动，直接使用mysql。

//Driver是mysql数据库提供的数据库驱动类。实现了java.sql.Driver的接口
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }
    //静态代码块，随着类的加载只加载一次，注册驱动
    static {
        try {
            //DriverManager驱动管理器
            //registerDriver()注册驱动的实现方法
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

　　2. 获取连接

　　调用DriverManager.getConnection静态方法，获取连接。getConnection是一个重载方法，可以填入URL，用户名，密码。

　　mysql 的URL由三部分组成： jdbc:mysql:(协议名) //localhost：3306/jdbc(数据库端口号和使用的数据库) ? 参数名=参数值（例如characterEncoding=utf-8）

　　3. 获取资源，调用connection类的createStatement() 方法创建statement 对象

　　4. 执行数据库语句，获取结果集

执行数据库增删改语句： excuteUpdate();
执行数据库查询结果集： excuteQuery();
- 结果集是否存在下一行： boolean next();
- 获取结果集数据：Xxx getXxx(String or Int) //对应列名或列号

　　5. 关闭流，释放资源

finally依次释放的对象包括：resultSet结果集、statement对象、connection对象

import jdk.swing.interop.SwingInterOpUtils;

import java.sql.*;

public class jdbc01 {
    public static void main(String[] args)  {
        Connection con=null;
        Statement statement=null;
        ResultSet resultSet=null;
        //1.注册驱动，从jdbc3开始可以直接使用，不用注册
        try {
            Class.forName("com.mysql.jdbc.Driver");

            //2. 获取连接， 利用DriverManager.getConnection 静态方法
            String url= "jdbc:mysql://localhost:3306/jdbc";
            //输入域名，用户名和密码
            con = DriverManager.getConnection(url, "root", "123456");

            //3. 获取资源，调用createStatement 方法创建statement 对象
           statement = con.createStatement();

            //4. 执行数据库语句，获取结果集等
            //4.1 数据库语句执行
            String sql="CREATE TABLE if not exists test  (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT);";
            int num = statement.executeUpdate(sql);//返回的是受影响的行数
            System.out.println(num);
            //4.2 获取数据库结果集
            resultSet = statement.executeQuery("select * from jdbc_user;");
            //依次输出
            while (resultSet.next()) {  //返回的是布尔类型，表示是否还有下一行数据
                int id = resultSet.getInt("id");  //根据字符串获取对应列名下的数据，
                String username = resultSet.getString("username");// 也可以通过int类型获取对应列号下的数据
                String password = resultSet.getString("password");
                Date birthday = resultSet.getDate("birthday");
                System.out.println(id+" : "+username+" : "+password+" : "+birthday);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5. 关闭流，释放资源
            try {
                resultSet.close();
                statement.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

二、创建jdbcUtils 工具类

1. 创建的jdbcUtils包含的内容有：数据库信息，驱动注册与连接，关闭，增强了代码的复用性

import java.sql.*;

public  class jdbcUtils {
    public static String DRIVERNAME="com.mysql.jdbc.Driver";
    public static String URL="jdbc:mysql://localhost:3306/jdbc?characterEncoding=utf-8";
    public static String USER="root";
    public static String PASSWORD="123456";
    //1.注册驱动
    static {
        try {
            Class.forName(DRIVERNAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //2. 获取连接connection
    public static Connection getConnection(){
        try {
            Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    //3.关闭流，方法重载
    public static void close(Connection connection, Statement statement){
        if (connection!=null && statement!=null){
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        close(connection,statement);
    }
}

2. 对工具类的调用实现数据库的增删改查

import org.junit.Test;

import java.sql.*;
import java.util.concurrent.ConcurrentNavigableMap;

public class jdbc02 {
    //测试类
    @Test
     public void insert() throws SQLException {
        //1.获取连接
         Connection connection = jdbcUtils.getConnection();
         //2. 创建statement对象
         Statement statement = connection.createStatement();
         //3. 执行增加语句
         String sql="INSERT INTO `jdbc_user` VALUES(null,'rf','11928','2019-11-02');";
         int i = statement.executeUpdate(sql);
         System.out.println(i);
         //4. 关闭流
         jdbcUtils.close(connection,statement);
     }
     @Test
    public void update() throws SQLException {
         //1.获取连接
         Connection connection=jdbcUtils.getConnection();
         //2. 创建statement对象
         Statement statement= connection.createStatement();
         //3. 执行增加语句
         String sql= "update jdbc_user set username='pc' where username='rf';";
         int i = statement.executeUpdate(sql);
         System.out.println(i);
         //4. 关闭流
         jdbcUtils.close(connection,statement);
     }
     @Test
    public void delete() throws SQLException {
         //1.获取连接
         Connection connection=jdbcUtils.getConnection();
         //2. 创建statement对象
         Statement statement = connection.createStatement();
         //3. 执行增加语句
         String sql="delete from jdbc_user where username ='pc';";
         int i = statement.executeUpdate(sql);
         System.out.println(i);
         //4. 关闭流
        jdbcUtils.close(connection,statement);
     }
     @Test
    public void select() throws SQLException {
        //1.连接
        Connection connection= jdbcUtils.getConnection();
        //2. 获取结果集
        Statement statement = connection.createStatement();
         ResultSet resultSet = statement.executeQuery("select * from jdbc_user;");
         //3.遍历
         while (resultSet.next()){
             int id = resultSet.getInt("id");
             String username = resultSet.getString("username");
             String password = resultSet.getString("password");
             Date birthday = resultSet.getDate("birthday");
             System.out.println(id+" : "+username+" : "+password+" : "+birthday);
         }
         //4.关闭
         jdbcUtils.close(connection,statement,resultSet);
     }
}

三、预处理对象防止SQL注入

SQL注入问题是利用程序员数据库语句拼接漏洞实现对数据库的错误操作，实现无账号登录和篡改，用户可以改变数据库执行语句，如拼接：123 ' or '1'='1 使得判断用户数据永远为真
举例

import java.sql.*;
import java.util.Scanner;

public class jdbc03 {
    //演示SQL注入问题
    public static void main(String[] args) throws SQLException {
        //1.获取连接
        Connection connection = jdbcUtils.getConnection();
        //2.创建statement对象
        Statement statement = connection.createStatement();
        //3. 获取用户名和密码
        Scanner sc= new Scanner(System.in);
        System.out.println("请输入用户名");
        String user = sc.nextLine();
        System.out.println("请输入密码");
        String pass= sc.nextLine();
        //4.拼接SQL语句
        String sql="select * from jdbc_user where username='"+user+"' and password='"+pass+"';";
        //5. 执行SQL语句
        ResultSet resultSet = statement.executeQuery(sql);
        //6.遍历显示结果
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            Date birthday = resultSet.getDate("birthday");
            System.out.println(id+" : "+username+" : "+password+" : "+birthday);
        }
        //7.关闭流
        jdbcUtils.close(connection,statement,resultSet);
    }
}

显示结果

解决方案：采用prepareStatement对象，利用占位符的方式编写数据库语句，prepareStatement是statement的子接口，可以有效防止SQL注入
区别：
- preparestatement 可以进行预编译，通过占位符只需要编译一次，通过设置参数便可以进行多次执行，而statement每次都需要发给数据库进行编译在执行、
- statement用于执行静态SQL语句，拼接SQL语句会引发SQL注入问题；preparestatement可以利用？代替动态参数，执行动态SQL语句，执行效率高
- preparestatement是statement的子接口，继承了statement的实现类

import java.sql.*;
import java.util.Scanner;

public class jdbc04 {
    public static void main(String[] args) throws SQLException {
        //1.获取连接
        Connection connection = jdbcUtils.getConnection();
        //2、创建预处理对象,需要传入一个String参数，通常是SQL语句
        String sql="select * from jdbc_user where username = ? and password = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //3.获取用户名和密码
        Scanner sc= new Scanner(System.in);
        System.out.println("请输入用户名");
        String user = sc.nextLine();
        System.out.println("请输入密码");
        String pass= sc.nextLine();
        //4.利用占位符编写数据库语句设置参数
        preparedStatement.setString(1,user);
        preparedStatement.setString(2,pass);
        //5.获取结果集并显示
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            Date birthday = resultSet.getDate("birthday");
            System.out.println(id+" : "+username+" : "+password+" : "+birthday);
        }else System.out.println("登录失败");
        //6.关闭流
        jdbcUtils.close(connection,preparedStatement,resultSet );
    }
}

四、JDBC控制事务

三个方法：更改事务自动提交属性，成功则提交事务，否则进行回滚
setautocommit(false); commit(); rollback();
举例

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class jdbc05 {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            //1. 注册驱动获取数据库连接
            connection = jdbcUtils.getConnection();
            //2. 设置提交属性，改为false则代表手动提交事务
            connection.setAutoCommit(false);
            //3. 创建preparestatement对象，进行事务操作
            preparedStatement=connection.prepareStatement("update account set money=money-? where name =?;");
            preparedStatement.setDouble(1,500.0);
            preparedStatement.setString(2,"tom");
            preparedStatement.executeUpdate();
            // System.out.println(1 / 0);
            preparedStatement=connection.prepareStatement("update account set money=money+? where name=?;");
            preparedStatement.setDouble(1,500.0);
            preparedStatement.setString(2,"jack");
            preparedStatement.executeUpdate();

            //4. 成功则提交事务
            connection.commit();
            System.out.println("转账成功");

        } catch (Exception e) {
            e.printStackTrace();
            try {
                //5.失败则回滚
                connection.rollback();
                System.out.println("转账失败");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            //6. 关闭流
            jdbcUtils.close(connection,preparedStatement);
        }


    }
}

五、连接池技术

实际开发中“获得连接”或“释放资源”是非常消耗系统资源的两个过程，每一次对数据库进行操作都需要创建数据库连接，并最终释放资源，不利于多个对象对数据库进行使用
采用连接池技术，来共享连接Connection。提高数据库使用效率，可以重复使用池内connection。关闭connection不代表真的关闭数据库流，而是把connection归还到池内，不代表销毁连接池
使用数据库连接池： java提供了数据库连接池接口 javax.sql.dataSource,需要各个数据库厂商的连接池去实现接口，方便java切换不同的连接池
常见的连接池： DBCP连接池、C3P0连接池、Druid连接池

1. DBCP连接池

开源连接池，是Apache成员之一,专门为运作一个开源软件项目的Apache 的团体提供支持的非盈利性组织, 配置方便，是tomcat内置连接池

　1) 导入DBCP连接池jar包

　2) 编写DBCP连接池工具类，包括常量，连接池的创建以及释放资源归还给连接池，主要用到DBCP连接池提供的BasicDataSource();

public static BasicDataSource dataSource=new BasicDataSource();  通过DataSource的setXXX()设置数据库属性

import org.apache.commons.dbcp.BasicDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Stack;

public class DBCPUtils {
    //1. 创建常量，用于连接登录数据库,保存数据库连接的相关信息
    public static final String DRIVERNAME= "com.mysql.jdbc.Driver";
    public static final String URL="jdbc:mysql://localhost:3306/db5?characterEncoding=UTF-8";
    public static final String USERNAME="root";
    public static final String PASSWORD="123456";

    //2. 创建连接池，由dbcp提供的实现类BasicDatasource
    public static BasicDataSource dataSource=new BasicDataSource();

    //3. 使用静态代码块进行设置
    static {
        dataSource.setDriverClassName(DRIVERNAME);
        dataSource.setUrl(URL);
        dataSource.setUsername(USERNAME);
        dataSource.setPassword(PASSWORD);
    }
    //4. 获取连接方法
    public static Connection getConnection() throws SQLException {
        Connection connection = dataSource.getConnection();
        return connection;
    }
    //5. 关闭资源归还连接池
    public static void close(Connection connection, Statement statement){
        if (connection!=null && statement!=null){
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
}
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if (resultSet!=null ){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        close(connection,statement);
    }
}

　3) 使用DBCP连接池执行数据库操作

import java.sql.*;
public class dbcpTest {
    public static void main(String[] args) {
        Connection connection= null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            //1.获取连接池
          connection  = DBCPUtils.getConnection();
            //2. 创建statement对象
            statement=connection.createStatement();
            //3. 执行数据库语句
            String sql="select * from employee";
            resultSet = statement.executeQuery(sql);
            //4. 处理结果集
            while (resultSet.next()){
                int id = resultSet.getInt("eid");
                String ename = resultSet.getString("ename");
                int age = resultSet.getInt("age");
                String sex = resultSet.getString("sex");
                double salary = resultSet.getDouble("salary");
                Date empdate = resultSet.getDate("empdate");
                System.out.println(id+" : "+ename+" : "+age+" : "+sex+" : "+salary+" : "+empdate);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //5. 归还连接池
            DBCPUtils.close(connection,statement,resultSet);
        }

    }

}

　4) 常见的DBCP配置项

　　DBCP可以设置数据库连接池的多个属性，包括最大连接池数量（默认为8），最大空闲连接池（最多有多少个连接池处于空闲状态，默认8），最小空闲连接（最小空闲连接池不够用时，不创建数据库连接池满足最小空闲所需，默认0），初始化连接池（初始连接池数量，默认0）

属性	描述
driverClassName	数据库驱动名称
url	数据库地址
username	用户名
password	密码
maxActive	最大连接数量
maxIdle	最大空闲连接
minIdle	最小空闲连接
initialSize	初始化连接

2. C3P0连接池

开源连接池，支持jdbc3 规范和jdbc2的标准扩展，被支持JDBC3规范和JDBC2的标准扩展。被Hibernate、Spring等开源项目广泛使用。

　　1). 导入jar包和配置文件xml，xml包含了mysql的配置信息

<c3p0-config>
  
  <!--默认配置-->
    <default-config>  
    
        <!-- initialPoolSize：初始化时获取三个连接，
              取值应在minPoolSize与maxPoolSize之间。 --> 
        <property name="initialPoolSize">3</property>  
        
        <!-- maxIdleTime：最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。-->
        <property name="maxIdleTime">60</property>  
        
        <!-- maxPoolSize：连接池中保留的最大连接数 -->
        <property name="maxPoolSize">100</property>  
        <!-- minPoolSize: 连接池中保留的最小连接数 -->
        <property name="minPoolSize">10</property>  
        
    </default-config>  
  
   <!--配置连接池mysql-->

    <named-config name="mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/db5?characterEncoding=UTF-8</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
    </named-config>
    <!--配置连接池2,可以配置多个-->

</c3p0-config>

　　2) 编写c3p0连接池工具类

　　主要用到ComboPooledDataSource("mysql")无参情况下使用xml中的默认配置，使用mysql则填写配置名，修改配置文件即可实现代码的多次使用，方便代码复用

package C3P0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class C3P0Utils {
    //1. 获取配置信息，创建DataSource对象,无参情况下使用默认配置，连接mysql数据库适用mysql配置
    public  static ComboPooledDataSource dataSource=new ComboPooledDataSource("mysql");
    //2. 获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    //3.关闭资源，归还连接池
    public static void close(Connection connection, Statement statement){
        if (connection!=null && statement!=null){
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if (resultSet!=null ){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        close(connection,statement);
    }
}

　　3) 使用c3p0连接池执行数据库操作

package C3P0;

import java.sql.*;

public class C3P0Test {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            //1.获取连接
            connection=C3P0Utils.getConnection();
            //2. 创建预处理对象
            preparedStatement = connection.prepareStatement("select  * from employee where ename = ?;");
            //3.执行动态数据库语句
            preparedStatement.setString(1,"李清照");
            resultSet= preparedStatement.executeQuery();
            //4.处理结果集
            while (resultSet.next()){
                int id = resultSet.getInt("eid");
                String ename = resultSet.getString("ename");
                int age = resultSet.getInt("age");
                String sex = resultSet.getString("sex");
                double salary = resultSet.getDouble("salary");
                Date empdate = resultSet.getDate("empdate");
                System.out.println(id+" : "+ename+" : "+age+" : "+sex+" : "+salary+" : "+empdate);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //5.释放资源
            C3P0Utils.close(connection,preparedStatement,resultSet);
        }
    }
}

　　4) 常见的c3p0配置项

3. Druid连接池

Druid德鲁伊连接池是阿里巴巴开发的含监控能力的数据库连接池，在功能、性能、拓展性方面都极佳，加入了日志监控功能，可以很好的监控DB连接池和SQL语句的执行情况

　　1) 导入jar包和配置文件，配置文件以properties为后缀，记录了数据库的配置信息

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db5?characterEncoding=UTF-8
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000

　　2)编写Druid连接池工具类

定义连接池成员变量
Druid不能主动读取pproperties中的配置文件，借助属性集的load方法和文件流的读取，要创建属性集properties
要通过文件流获取配置文件信息
通过properties中的load方法读取文件流中的配置信息
通过工厂类的方法获取连接池对象 dataSource= DruidDataSourceFactory.createDataSource(p)

package Druid;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

public class DruidUtils {
    //1. 定义成员变量
    public static DataSource dataSource;
    //2.定义静态代码快，加载配置文件
    static {
        // Druid不能主动读取pproperties中的配置文件，借助属性集的load方法和文件流的读取
        Properties p= new Properties();
        InputStream resourceAsStream = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            //3. 使用properties中的load方法从字节流中读取配置信息
            p.load(resourceAsStream);
            //4. 通过工厂类获取连接池对象
            dataSource= DruidDataSourceFactory.createDataSource(p);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    //4. 获取连接方法
    public static Connection getConnection() throws SQLException {
        Connection connection = dataSource.getConnection();
        return connection;
    }
    //5. 关闭资源归还连接池
    public static void close(Connection connection, Statement statement){
        if (connection!=null && statement!=null){
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if (resultSet!=null ){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        close(connection,statement);
    }
}

　　3)使用Druid连接池执行数据库操作

package Druid;
import java.sql.*;
public class DruidTest {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            //1.建立连接
            connection=DruidUtils.getConnection();
            //2.获取statement对象
            statement=connection.createStatement();
            //3.执行SQL语句
            resultSet=statement.executeQuery("select * from employee where salary between 3000 and 5000;");
            //4.处理结果集
            while (resultSet.next()){
                int id = resultSet.getInt("eid");
                String ename = resultSet.getString("ename");
                int age = resultSet.getInt("age");
                String sex = resultSet.getString("sex");
                double salary = resultSet.getDouble("salary");
                Date empdate = resultSet.getDate("empdate");
                System.out.println(id+" : "+ename+" : "+age+" : "+sex+" : "+salary+" : "+empdate);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //5. 关闭数据流
            DruidUtils.close(connection,statement,resultSet);
        }
    }
}

六、DBUtils工具类包

Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能，使用前需要导入commons-dbutils-1.6.jar 包
常见的核心功能类：
- QueryRunner 中提供对sql语句操作的API，线程安全
- ResultSetHandler接口，用于定义select操作后，怎样封装结果集.
- DbUtils类,他就是一个工具类,定义了关闭资源与事务处理相关方法，线程安全
使用DBUtils获取连接
- 创建QueryRunner，可以有手动模式和自动模式，手动模式选用无参构造，自动模式则传入写好的连接池DataSource对象，由相应的连接池技术自身维护连接池并关闭
- 使用手动模式创建的QueryRunner需要获取连接connection，并释放资源DBUtils.closeQuiety(Connection)
- 占位符方式编写SQL进行增删改操作时，主要利用QueryRunner的update(Connection con,Sql 语句，Object[] param)，Object[] 用于设置占位符参数，与占位符位置一一对应

package DBUtils.service;

import Druid.DruidUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
import java.sql.Connection;
import java.sql.SQLException;

public class dbUtilsQueryRunner {

   /* public static void main(String[] args) {
        //手动模式创建QueryRunner
        QueryRunner qr1= new QueryRunner();
        //自动模式创建QueryRunner，传入数据库连接池，Druid自动维护连接connection
        QueryRunner qr2=new QueryRunner(DruidUtils.getDataSource());

    }*/

    //1. 实现插入操作
    @Test
  public  void  insert() throws SQLException {
      //手动模式创建QueryRunner 需要获取连接对象并关闭连接池
      QueryRunner qr=new QueryRunner();
      //SQL插入语句
      String sql= "insert into employee values(?,?,?,?,?,?);";
      //利用Object数组设置传入参数
      Object[] param={null,"孟浩然",27,"女",2000,"1990-08-22"};
      //获取连接对象
      Connection con= DruidUtils.getConnection();
      //执行SQL语句
      qr.update(con,sql,param);
      //关闭连接池
      DbUtils.closeQuietly(con);

  }
    // 2.实现修改操作
    @Test
    public void  update() throws SQLException {
        //自动创建QueryRunner 对象，Druid自动维护连接池并关闭
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        //SQL修改语句
        String sql= "update employee set ename=? where eid =?;";
        //利用Object数组设置传入参数
        Object[] param={"晏殊",5};
        //不需要获取连接对象
        //执行SQL语句
        qr.update(sql,param);
        //Druid自动维护连接池并关闭，不需关闭连接池

    }
    // 3. 实现删除操作
    @Test
    public void  delete() throws SQLException {
        //手动创建QueryRunner 对象
        QueryRunner qr=new QueryRunner();
        //SQL删除语句
        String sql= "delete from employee where eid=?";
        //获取连接对象并执行SQL语句
        Connection connection = DruidUtils.getConnection();
        qr.update(connection,sql,5);
        //关闭连接池
        DbUtils.closeQuietly(connection);
    }
}

对结果集的处理主要用到queryRunner的query(Connection con,String sql,handler,Object[] param)，其中connection， param可省略，handler是对结果集的处理方法，是ResulSetHandler接口的处理方式，主要包括

ResultSetHandler实现类	说明
ArrayHandler	将结果集中的第一条记录封装到一个Object[]数组中，数组中的每一个元素就是这条记录中的每一个字段的值
ArrayListHandler	将结果集中的每一条记录都封装到一个Object[]数组中，将这些数组在封装到List集合中。
BeanHandler	将结果集中第一条记录封装到一个指定的javaBean中.
BeanListHandler	将结果集中每一条记录封装到指定的javaBean中，再将这些javaBean在封装到List集合中
ColumnListHandler	将结果集中指定的列的字段值，封装到一个List集合中
KeyedHandler	将结果集中每一条记录封装到Map<String,Object>,在将这个map集合做为另一个Map的value,另一个Map集合的key是指定的字段的值。
MapHandler	将结果集中第一条记录封装到了Map<String,Object>集合中，key就是字段名称，value就是字段值
MapListHandler	将结果集中每一条记录封装到了Map<String,Object>集合中，key就是字段名称，value就是字段值，在将这些Map封装到List集合中。
ScalarHandler	它是用于封装单个数据。例如 select count(*) from 表操作

package DBUtils.service;

import DBUtils.entity.Employee;
import Druid.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class dbUtilsResultSetHandler {

    //查询id为5的记录,封装到数组中，利用ArrayHandle 对结果集的第一条数据进行封装
    @Test
    public void findById() throws SQLException {
        //创建QueryRunner
        QueryRunner qr= new QueryRunner(DruidUtils.getDataSource());
        //SQL语句
        String sql= "select * from employee where eid =?;";
        //执行查询语句，获取结果
        Object[] query = qr.query(sql, new ArrayHandler(), 5);
        //打印输出
        System.out.println(Arrays.toString(query));
    }

    //查询所有数据,封装到List集合中,利用ArrayListHandle 对结果集的第一条数据进行封装
    @Test
    public void findAll() throws SQLException {
        //创建QueryRunner
        QueryRunner qr= new QueryRunner(DruidUtils.getDataSource());
        //SQL语句
        String sql= "select * from employee;";
        //执行查询语句，获取结果
        List<Object[]> query = qr.query(sql, new ArrayListHandler());
        //打印输出
        for (Object[] objects : query) {
            System.out.println(Arrays.toString(objects));
        }
    }

    //查询id为5的记录,封装到指定JavaBean中,利用BeanHandle 对结果集的第一条数据进行封装
    @Test
    public void findBeanById() throws SQLException {
        //创建QueryRunner
        QueryRunner qr= new QueryRunner(DruidUtils.getDataSource());
        //SQL语句
        String sql= "select * from employee where eid = ?;";
        //执行查询语句，获取结果,泛型要设置传递的Javabean类型，类.class 用于反射
        Employee query = qr.query(sql, new BeanHandler<Employee>(Employee.class), 5);
        //打印输出
            System.out.println(query);
    }

    //查询薪资大于 3000 的所员工信息,封装到JavaBean中再封装到List集合中,BeanListHandler对javabean进行封装
    @Test
    public void findBeanList() throws SQLException {
        //创建QueryRunner
        QueryRunner qr= new QueryRunner(DruidUtils.getDataSource());
        //SQL语句
        String sql= "select * from employee where salary > 3000";
        //执行查询语句，获取结果,泛型要设置传递的Javabean类型，类.class 用于反射
        List<Employee> query = qr.query(sql, new BeanListHandler<Employee>(Employee.class));
        //打印输出
        for (Employee employee : query) {
            System.out.println(employee);
        }
    }

    //查询姓名是 李清照的员工信息,将结果封装到Map集合中,利用MapHandler
    @Test
    public void findMap() throws SQLException {
        //创建QueryRunner
        QueryRunner qr= new QueryRunner(DruidUtils.getDataSource());
        //SQL语句
        String sql= "select * from employee where ename=?";
        //执行查询语句，获取结果,泛型要设置传递的Javabean类型，类.class 用于反射
        Map<String, Object> map = qr.query(sql, new MapHandler(), "李清照");
        //打印输出
        Set<Map.Entry<String, Object>> entrySet = map.entrySet();
        for (Map.Entry<String, Object> stringObjectEntry : entrySet) {
            System.out.println(stringObjectEntry.getKey()+":"+stringObjectEntry.getValue());
        }
    }

    //KeyedHandler 将结果集中每一条记录封装到Map<String,Object>,在将这个map集合做为另一个Map的value,另一个Map集合的key是指定的字段的值
    @Test
    public void KeyedMap() throws SQLException {
        //创建QueryRunner
        QueryRunner qr= new QueryRunner(DruidUtils.getDataSource());
        //SQL语句
        String sql= "select * from employee where eid= 5";
        //执行查询语句，获取结果,一个序号对应一条employee数据
        Map<Object, Map<String, Object>> query = qr.query(sql, new KeyedHandler<>());
        //打印输出
        Set<Map.Entry<Object, Map<String, Object>>> entrySet = query.entrySet();
        for (Map.Entry<Object, Map<String, Object>> entry : entrySet) {
            System.out.println(entry.getKey()+":"+entry.getValue());
        }
    }

    //ColumnListHandler 将结果集中指定的列的字段值，封装到一个List集合中
    //查询所有姓名
    @Test
    public void ColumnList() throws SQLException {
        //创建QueryRunner
        QueryRunner qr= new QueryRunner(DruidUtils.getDataSource());
        //SQL语句
        String sql= "select ename from employee; ";
        //执行查询语句，获取结果,一个序号对应一条employee数据
        List<Object> query = qr.query(sql, new ColumnListHandler<>());
        //打印输出
        for (Object ename : query) {
            System.out.println(ename);
        }
    }

    //查询所有员工信息,将结果封装到Map集合中,利用MapHListandler
    @Test
    public void findALLMap() throws SQLException {
        //创建QueryRunner
        QueryRunner qr= new QueryRunner(DruidUtils.getDataSource());
        //SQL语句
        String sql= "select * from employee ";
        //执行查询语句，获取结果,泛型要设置传递的Javabean类型，类.class 用于反射
        List<Map<String, Object>> query = qr.query(sql, new MapListHandler());
        //打印输出
        for (Map<String, Object> map : query) {
            Set<Map.Entry<String, Object>> entrySet = map.entrySet();
            for (Map.Entry<String, Object> stringObjectEntry : entrySet) {
                System.out.print(stringObjectEntry.getKey()+":"+stringObjectEntry.getValue()+"  ");
            }
            System.out.println();
        }
    }

    //查询所有员工的薪资总额,ScalarHandler 用于封装单个数据。例如 select count(*) from 表操作。
    @Test
    public void getSum() throws SQLException {
        //创建QueryRunner
        QueryRunner qr= new QueryRunner(DruidUtils.getDataSource());
        //SQL语句
        String sql= "select sum(salary) from employee";
        //执行查询语句，获取结果,泛型要设置传递的Javabean类型，类.class 用于反射
        Object query = qr.query(sql, new ScalarHandler<>());
        //打印输出
        System.out.println(query);
    }
}

七、批量预处理技术

对需要多次重复的操作进行打包处理，实现对数据库添加大量数据时能够通过一次操作写入多条数据
主要用到Statement和PreparedStatement批处理操作，使用的方法有addBatch()和executeBatch()方法

方法	说明
void addBatch()	将给定的 SQL 命令添加到此 Statement 对象的当前命令列表中。通过调用方法 executeBatch 可以批量执行此列表中的命令。
int[] executeBatch()	每次提交一批命令到数据库中执行，如果所有的命令都成功执行了，那么返回一个数组，这个数组是说明每条命令所影响的行数

使用步骤：设置mysql配置参数，开启mysql批处理，添加rewriteBatchedStatements=true ，将URL改为url=jdbc:mysql://localhost:3306/db5?characterEncoding=UTF-8&rewriteBatchedStatements=true

package Batch;

import Druid.DruidUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class AddBatch {

    public static void main(String[] args) {
        //创建连接
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
           connection= DruidUtils.getConnection();
            //sql语句,表名区分大小写
         preparedStatement= connection.prepareStatement("insert into testBatch(uname) values(?)");
            //设置输入参数将其添加到批处理队列
            for (int i = 0; i <10000 ; i++) {
                //设置输入参数将其添加到批处理队列
                preparedStatement.setString(1,"liba"+i);
                preparedStatement.addBatch();
            }
            long start = System.currentTimeMillis();
            //执行批处理
            preparedStatement.executeBatch();
            long end = System.currentTimeMillis();
            System.out.println("插入10000条语句耗时"+(end-start)+"毫秒");

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtils.close(connection,preparedStatement);
        }
    }
}

八、mysql元数据

除了数据库表之外的数据都是元数据，包括：查询结果信息（update或delete受影响的语句条数），数据库和数据表的信息（名称、结构等），mysql服务器信息（版本，权限等）
常见的mysql元数据处理命令

-- 1.查看服务器当前状态
SHOW STATUS; -- 可以查看链接客户端，接受字节数等信息
-- 2.查看MySQl的版本信息
SELECT VERSION(); -- 5.7.28-log
-- 3.查询表中的详细信息
SHOW COLUMNS FROM employee;
-- 4.显示数据表的详细索引信息
SHOW INDEX FROM employee;
-- 5.列出所有数据库
SHOW DATABASES;
-- 6.显示当前数据库的所有表
SHOW TABLES ;
-- 7.获取当前的数据库名
SELECT DATABASE();

JDBC获取元数据信息
- connection中的getMetaData () 方法,获取的是DatabaseMetaData 数据库元数据对象，获取数据库的结构信息
- preparestatement中的getMetaData () 方法,获取的是ResultSetMetaData 数据库元数据对象,获取表中的结构信息

package metadata;
import Druid.DruidUtils;
import org.junit.Test;
import java.sql.*;
public class Metadata {

    //利用connection中的 getMetaData () 方法,获取的是DatabaseMetaData 数据库元数据对象
    @Test
    public void conMetadata() throws SQLException {
        //1.建立连接
        Connection con= DruidUtils.getConnection();
        //2.获取数据库元数据
        DatabaseMetaData metaData = con.getMetaData();
        //3.查询相关信息
        String url = metaData.getURL();
        System.out.println("数据库URL为"+url);
        String userName = metaData.getUserName();
        System.out.println("数据库用户为"+userName);
        String version = metaData.getDatabaseProductVersion();
        System.out.println("数据库产品版本为"+version);
        String productName = metaData.getDatabaseProductName();
        System.out.println("数据库产品名称为"+productName);
        String driverName = metaData.getDriverName();
        System.out.println("数据库驱动名称为"+driverName);
        boolean b = metaData.isReadOnly();
        System.out.println("数据库是否只读"+b);
        //4.关闭连接
        con.close();
    }

    //preparestatement中的getMetaData () 方法,
    // 获取的是ResultSetMetaData 数据库元数据对象,获取表中的结构信息
    @Test
    public void stateMetadata() throws SQLException {
        //1.建立连接
        Connection con= DruidUtils.getConnection();
        //2.创建预处理对象,获取结果集
        PreparedStatement ps = con.prepareStatement("select * from employee;");
        ResultSet resultSet = ps.executeQuery();
        //3. 获取数据库元数据
        ResultSetMetaData metaData = ps.getMetaData();
        //3.查询相关信息
        int count = metaData.getColumnCount(); //获取列数
        for (int i = 1; i <=count ; i++) {
            String name = metaData.getColumnName(i); //获取第i列的列名
            String type = metaData.getColumnTypeName(i); //获取第i列的类型
            System.out.println("第"+i+"列的列名:"+ name+" 类型:"+type);
        }
        //4.关闭连接
        DruidUtils.close(con,ps,resultSet);
    }
}

posted @ 2020-12-22 11:34 forever_fate 阅读(117) 评论(0) 收藏举报

刷新页面返回顶部