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");
