JDBC相关知识

一、连接数据库

1. 步骤

//1.创建一个Driver实现类的对象
Driver driver = new com.mysql.jdbc.Driver();//注意抛异常

//2.准备 url 和 info
String url = "jdbc:mysql://localhost:3306/test";
//Oracle:"jdbc:oracle:thin:@localhost:1512:sid"
//SQLServer:"jdbc:microsoft:sqlserver//localhost:1433:DatabaseName=sid"
//MySql:"jdbc:mysql://localhost:3306/sid"

Properties info = new Properties();
info.put("user", "root");
info.put("password","1230");

//3.获取连接
Connection connection = driver.connect(url, info);//注意抛异常

 

2. 获取Driver的方式

• 通过new新建  ：Driver driver = new com.mysql.jdbc.Driver();
• 通过反射创建类的实例  ：Driver driver = (Driver)Class.forName(driverClass).newInstance();　　//driverClass为全类名，即 com.mysql.jdbc.Driver
  
• 通过DriverManager获取数据库连接（加载数据库驱动程序 即 注册驱动）  ：DriverManager.registerDriver(Class.forName(driverClass).newInstance());   可简化为 Class.forName(driverClass); 可加载多个驱动

  //加载数据库驱动程序 (可加载多个驱动程序)（注册驱动）
  //DriverManager.registerDriver(Class.forName(driverClass).newInstance());
  Class.forName(driverClass);
  Class.forName(driverClass2);
  
  //通过getConnection方法获得连接
  //getConnection(String url, String user, String password) throws SQLException
  Connection connection = DriverManager.getConnection(jdbcUrl, user, password);

 

3. 提取方法来获得Connection

实现方式：把URL、账号、密码等信息放入 jdbc.properties 文件，通过反射获取

public class Utils {
    public Connection getConnection() throws Exception {
        String driverClass = null;
        String jdbcUrl = null;
        String user = null;
        String password = null;

        // 读取类路径下的jdbc.properties文件
        InputStream in = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(in);
        driverClass = properties.getProperty("driver");
        jdbcUrl = properties.getProperty("jdbcUrl");
        user = properties.getProperty("user");
        password = properties.getProperty("password");

/*        // 通过反射创建类的实例
        Driver driver = (Driver) Class.forName(driverClass).newInstance();

        Properties info = new Properties();
        info.put("user", user);
        info.put("password", password);
        Connection connection = driver.connect(jdbcUrl, info);*/
        
        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(jdbcUrl, user, password);

        return connection;
    }

}

 

二、数据库操作

前提：连接数据库的前提下，才能进行操作

1. 执行更新操作 （插入、修改、删除 ）

//1.获得连接
Connection conn = driver.connect(url, info);
String sql="...";     //sql语句

//2.获取statement对象
Statement statement = conn.createStatement();

//3.执行
statement.executeUpdate(sql);

//4.关闭
statement.close();
conn.close();

 

2. 查询操作

//1.通过连接获得statement对象
Statement statement = conn.createStatement();
String sql = "...";

//2.执行
ResultSet rs = statement.executeQuery(sql);

//3.遍历输出
while(rs.next()){
     int id = rs.getInt(1);
     String name = rs.getString("name");
}

//4.关闭
rs.close();
statement.close();
conn.close();

 

3. 给SQL语句设置参数——PreparedStatement

父类：Statement

//1.通过连接获得preparedStatement对象
String sql = "insert into customers (name, email, birth) values(?,?,?)";
preparedStatement = conn.prepareStatement(sql);　　//与无参的Statement对象不同，获得对象时就得传入sql语句，执行语句时不用再传入sql语句

//2.设置参数
preparedStatement.setString(1, "zhangsan");
preparedStatement.setString(2, "77777@qq.com");
preparedStatement.setDate(3, new Date(new java.util.Date().getTime()));

//3.执行
preparedStatement.executeUpdate();

//4.获得ResultSet
ResultSet rs = preparedStatement.getResultSet();

 

获取插入记录的主键值

实现方式：使用重载的preparedStatement(sql, flag)来生成preparedStatement对象

preparedStatement = conn.preparedStatement(sql, Statement.RETURN_GENERATED_KEYS);

//生成的结果集里只有一列，即主键编号
ResultSet rs = preparedStatement.getGeneratedKeys();
if(rs.next()){

//获得主键
    String generatedKey = rs.getObject(1);

}

 

4. 调用函数&存储过程——CallableStatement

父类：Statement

// 1. 通过 Connection 对象的 prepareCall()方法创建一个 CallableStatement 对象的实例。
//sql字符串指明如何调用存储过程：?=call name(param, ..., param) 或 callname(param, ..., param)
String sql = "{?= call sum_salary(?, ?)}";
CallableStatement callableStatement = connection.prepareCall(sql);

// 2. 通过 CallableStatement 对象的 reisterOutParameter() 方法注册 OUT 参数。
callableStatement.registerOutParameter(1, Types.NUMERIC);
callableStatement.registerOutParameter(3, Types.NUMERIC);
            
// 3. 通过 CallableStatement 对象的 setXxx() 方法设定 IN 或 IN OUT 参数。
//若想将参数默认值设为null, 可以使用 setNull() 方法。
callableStatement.setInt(2, 80);
            
// 4. 通过 CallableStatement 对象的 execute() 方法执行存储过程。
callableStatement.execute();
            
// 5. 如果所调用的是带返回参数的存储过程, 还需要通过 CallableStatement 对象的 getXxx() 方法获取其返回值。
double sumSalary = callableStatement.getDouble(1);
long empCount = callableStatement.getLong(3);

 

三、JDBC元数据

1. ResultSetMetaData

方法签名：ResultSetMetaData getMetaData()       //通过 resultSet.getMetaData() 方法获得 ResultSetMetaData 对象

                  int getColumnCount()                            //查询结果集中的列数

                  String getColumnName(int colum)        //获取查询结果集中指定的列别名，从1开始

                  String getColumnLabel(int colum)         //获取查询结果集中指定的列别名，从1开始

    //在数据库中查询记录，转换成与表相对应的实体类对象
public <T> T get(Class<T> clazz, String sql, Object... args) throws Exception {
        T entity = null;

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        //1.获得connection
        connection = getConnection();
        
        //2.设置参数
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            preparedStatement.setObject(i + 1, args[i]);
        }
        
        //3.执行，并获得resultSet
        resultSet = preparedStatement.executeQuery();

        //4.通过resultSet获得resultSetMetaData
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

        Map<String, Object> values = new HashMap<>();
        if (resultSet.next()) {
            //5.把结果放入哈希表中
            for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                String columnLabel = resultSetMetaData.getColumnLabel(i + 1);
                Object columnValue = resultSet.getObject(i + 1);

                values.put(columnLabel, columnValue);
            }
            
//6.为相应实体类对象设置属性值
            if(values.size() > 0){
                entity = clazz.newInstance();
                for(Map.Entry<String, Object> entry: values.entrySet()){
                    String fieldName = entry.getKey();
                    Object value = entry.getValue();
                    
                    BeanUtils.setProperty(entity, fieldName, value);//需要使用beanutils工具包
                }
            }
        }
        return entity;
    }

 

2. DatabaseMetaData

方法签名：DatabaseMetaData getMetaData()      //通过 connection.getMetaData() 方法获得

作用：可以得到数据库本身的一些信息

DatabaseMetaData data = connection.getMetaData();
            //1. 得到数据库的版本号
int version = data.getDatabaseMajorVersion();
            
//2. 得到连接到数据库的用户名
String user = data.getUserName();
            
//3. 得到 MySQL 中有哪些数据库
ResultSet resultSet = data.getCatalogs();

 

四、处理Blob大对象数据

LOB：大对象，存储大量数据的一种数据类型。

BLOB：二进制大对象。

1. 读取 Blob 数据

 实现方式：使用 getBlob 方法读取到 Blob 对象

                   调用 Blob 的 getBinaryStream() 方法得到输入流。再使用 IO 操作即可。

...
resultSet = preparedStatement.executeQuery();
          
if(resultSet.next()){
    int id = resultSet.getInt(1);
    String name = resultSet.getString(2);
    String email = resultSet.getString(3);
                
    //1.获得Blob对象
    Blob picture = resultSet.getBlob(5);
    
    //2.获得输入流
    InputStream in = picture.getBinaryStream();
    
    //3.输出到flower.jpg
    OutputStream out = new FileOutputStream("flower.jpg");
    byte [] buffer = new byte[1024];
    int len = 0;
    while((len = in.read(buffer)) != -1){
        out.write(buffer, 0, len);
    }
                
    in.close();
    out.close();
}

 

2. 写入Blob数据

作用：实现将本地图片写入数据库

String sql = "INSERT INTO customers(name, email, birth, picture) VALUES(?,?,?,?)";
//通过连接 获得preparedStatement对象
preparedStatement = connection.prepareStatement(sql);

//设置参数
preparedStatement.setString(1, "ABCDE");
preparedStatement.setString(2, "abcde@atguigu.com");
preparedStatement.setDate(3, new Date(new java.util.Date().getTime()));

//1.创建输入流
InputStream inputStream = new FileInputStream("Hydrangeas.jpg");
//2.设置Blob参数
preparedStatement.setBlob(4, inputStream);
            
preparedStatement.executeUpdate();

 

五、事务

1. 方法

//取消默认提交后才能进行事务

connection.setAutoCommit(false);　　//取消默认提交并开始事务

//提交事务

connection.commit();

//回滚

 connection.rollback();

 

2. 数据库的隔离级别

• READ UNCOMMITTED　　：读未提交数据，允许事务读取未被其他事务提交的变更，脏读、不可重复读、幻读都会出现。
• READ COMMITTED　　     ：读已提交数据，只允许事务读取已经被其他事务提交的变更。可避免脏读，不可重复读和幻读仍然存在。
• REPEATABLE READ　　    ：可重复读，确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新。可避免脏读和不可重复读，幻读问题仍然存在。
• SERIALIZABLE　　　　      ：串行化，确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行操作，所有并发问题都可避免，但性能低下。

通过Connection的setTransactionIsolation来设置事务的隔离级别。

例：connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

注：mysql的默认隔离级别是REPEATABLE READ。

 

六、批量处理

方法签名：preparedStatement.addBatch();　　       //加入Batch

                  preparedStatement.executeBatch();　　//批量处理

                  preparedStatement.clearBatch();　　    //清除当前的积累量

//批量插入十万条数据
sql = "INSERT INTO customers VALUES(?,?,?)";
preparedStatement = connection.prepareStatement(sql);
Date date = new Date(new java.util.Date().getTime());
for(int i = 0; i < 100000; i++){
    preparedStatement.setInt(1, i + 1);
    preparedStatement.setString(2, "name_" + i);
    preparedStatement.setDate(3, date);
                
    //"积攒" SQL 
    preparedStatement.addBatch();　　//Statement写法：statement.addBatch(String sql);
                
    //当 "积攒" 到一定程度, 就统一的执行一次. 并且清空先前 "积攒" 的 SQL
    if((i + 1) % 300 == 0){
        preparedStatement.executeBatch();
        preparedStatement.clearBatch();
    }
}
            
//若总条数不是批量数值的整数倍, 则还需要再额外的执行一次. 
if(100000 % 300 != 0){
    preparedStatement.executeBatch();
    preparedStatement.clearBatch();
}

 

七、数据库连接池

必要性

传统连接使用DriverManager获取，再验证用户名密码，执行完毕再断开连接。数据库的连接资源没有得到很好的重复利用。

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。

数据库连接池是通过实现DataSource接口来实现的。

 

1. DBCP

需要2 个jar 包：

• dbcp
• pool

注：dbcp依赖于 pool

final BasicDataSource dataSource = new BasicDataSource();
        
//1. 为数据源实例指定必须的属性
dataSource.setUsername("root");
dataSource.setPassword("1230");
dataSource.setUrl("jdbc:mysql://localhost:3306/test");
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        
//2. 指定数据源的一些可选的属性
//1). 指定数据库连接池中初始化连接数的个数
dataSource.setInitialSize(5);
        
//2). 指定最大的连接数: 同一时刻可以同时向数据库申请的连接数
dataSource.setMaxActive(5);
        
//3). 指定小连接数: 在数据库连接池中保存的最少的空闲连接的数量 
dataSource.setMinIdle(2);
        
//4).等待数据库连接池分配连接的最长时间. 单位为毫秒. 超出该时间将抛出异常. 
dataSource.setMaxWait(1000 * 5);
        
//3. 从数据源中获取数据库连接
Connection connection = dataSource.getConnection();

 

通过DataSourceFactory创建连接池（DataSource），这样可以直接从配置文件中获取信息

//从Properties文件里获得数据
Properties properties = new Properties();
InputStream inStream = DBCPTest.class.getClassLoader().getResourceAsStream("dbcp.properties");
properties.load(inStream);

//使用工厂创建连接池
DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);

 

 

2. C3P0

@Test
public void testC3P0() throws Exception{
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver            
    cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
    cpds.setUser("root");
    cpds.setPassword("1230");
        
    System.out.println(cpds.getConnection()); 
}

 

通过加载配置文件来获得信息

DataSource dataSource = new ComboPooledDataSource("helloc3p0");

dataSource.getConnection();

<!-- 配置文件helloc3p0.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <named-config name="helloc3p0">
        
        <!-- 指定连接数据源的基本属性 -->
        <property name="user">root</property>
        <property name="password">1230</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///atguigu</property>
        
        <!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化数据库连接池时连接的数量 -->
        <property name="initialPoolSize">5</property>
        <!-- 数据库连接池中的最小的数据库连接数 -->
        <property name="minPoolSize">5</property>
        <!-- 数据库连接池中的最大的数据库连接数 -->
        <property name="maxPoolSize">10</property>

        <!-- C3P0 数据库连接池可以维护的 Statement 的个数 -->
        <property name="maxStatements">20</property>
        <!-- 每个连接同时可以使用的 Statement 对象的个数 -->
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
</c3p0-config>

 

八、DBUtils

注：dbutils只是对底层的封装

       QueryRunner可能会有线程安全问题，逐一不要创建多个实例

1. update方法

//1.创建QueryRunner实现类
QueryRunner queryRunner = new QueryRunner();
String sql = "DELETE FROM customers WHERE id IN (?,?)";

//2.使用update方法
queryRunner.update(connection, sql, 12, 13);

 

2. query方法

方法签名：queryRunner.query(Connection connection, String sql, ResultSetHandler rs);

使用：在实现ResultSetHandler接口的类的handle方法中编写对结果集的处理方法。

Object object = queryRunner.query(conn, sql, new ResultSetHandler() {
    @Override
    public Object handle(ResultSet rs) throws SQLException {
        List<Customer> customers = new ArrayList<>();

        while (rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            String email = rs.getString(3);
            Date birth = rs.getDate(4);

            Customer customer = new Customer(id, name, email, birth);
            customers.add(customer);
        }
 //也是query方法的返回值
        return customers;
    }
});

 

以下Handler类实现了ResultSetHandler接口

1）BeanHandler

功能：返回查询的第一条记录对应的那个实体类对象

原理：BeanHandler类的handle方法中通过反射创建传入的Class类对应的类的对象，并用set方法为该对象设置属性，然后返回该对象。

注意：查询语句中的列名要和实体类的属性相对应，若不对应则需要使用别名   如下name：

String sql = "SELECT id, name customerName, email, birth FROM customers WHERE id >= ?";

// 1. 创建 QueryRunner 对象
QueryRunner queryRunner = new QueryRunner();

// 2. 调用query方法
Object object = queryRunner.query(conn, sql, new BeanHandler<>(Customer.class)，5);

 

2）BeanListHandler

作用：可以通过查询记录获得实体类对象集合

           若可以查询到记录，则返回传入的Class对象对应的对象集合。

String sql = "SELECT id, name customerName, email, birth FROM customers";
QueryRunner queryRunner = new QueryRunner();

// 返回List<Customer> 对象
Object object = queryRunner.query(conn, sql, new BeanListHandler<>(Customer.class));

 

3）MapHandler

作用：返回查询的第一条记录对应的Map对象  键：列名（非别名）；值：列的值

QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT id, name customerName, email, birth FROM customers WHERE id = ?";

Map<String, Object> map = queryRunner.query(connection, sql, new MapHandler(), 4);

 

4）MapListHandler

作用：返回查询的所有记录对应的Map对象的集合

QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT id, name, email, birth FROM customers";

List<Map<String, Object>> mapList = queryRunner.query(connection, sql, new MapListHandler());

 

5）ScalarHandler

作用：返回结果集中的第一列对应的数据

QueryRunner queryRunner = new QueryRunner();
String sql = "SELECT name FROM customers WHERE id = ?";

Object count = queryRunner.query(connection, sql, new ScalarHandler(), 6);

 

3. QueryLoader

作用：可以用来加载存放着 SQL 语句的资源文件。

          使用该类可以把 SQL 语句外置化到一个资源文件中. 以提供更好的解耦。

// / 代表类路径的根目录
Map<String, String> sqls = QueryLoader.instance().load("/sql.properties");
        
String updateSql = sqls.get("UPDATE_CUSTOMER");