要实现对数据库中数据的操作，首先要获取数据库的连接，关于连接，有做过详细总结：JDBC连接Mysql数据库
在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：
Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
PrepatedStatement：SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
CallableStatement：用于执行 SQL 存储过程
由于Statement存在SQL注入问题，所以使用PreparedStatement实现CRUD操作
1.PreparedStatement介绍
可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取
PreparedStatement 对象
PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句
PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，
第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，
第二个是设置的 SQL 语句中的参数的值
public class JDBCUtils { /** * @Description 获取数据库的连接 */ public static Connection getConnection() throws Exception { // 1.读取配置文件中的4个基本信息 InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties"); Properties pros = new Properties(); pros.load(is); String user = pros.getProperty("user"); String password = pros.getProperty("password"); String url = pros.getProperty("url"); String driverClass = pros.getProperty("driverClass"); // 2.加载驱动 Class.forName(driverClass); // 3.获取连接 Connection conn = DriverManager.getConnection(url, user, password); return conn; } /** * * @Description 关闭连接和Statement的操作 */ public static void closeResource(Connection conn,Statement ps){ try { if(ps != null) ps.close(); } catch (SQLException e) { e.printStackTrace(); } try { if(conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * * @Description 关闭资源操作 */ public static void closeResource(Connection conn,Statement ps,ResultSet rs){ try { if(ps != null) ps.close(); } catch (SQLException e) { e.printStackTrace(); } try { if(conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } try { if(rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); } } }
2.使用PreparedStatement实现增、删、改操作
增删改可以总结出一个通用代码：
公共部分：
数据库连接，资源的关闭。
对于变动的：
sql语句：作为参数传入；
占位符：个数，不确定，可以通过可变形参，类型为Object；占位符的个数与可变形参的个数相同
填充占位符：使用循环；
主要步骤
1.获取数据库的连接
2.预编译sql语句
3.填充占位
4.执行
5.资源的关闭
代码如下
public void update(String sql,Object ... args){ Connection conn = null; PreparedStatement ps = null; try { //1.获取数据库的连接 conn = JDBCUtils.getConnection(); //2.获取PreparedStatement的实例 (或：预编译sql语句) ps = conn.prepareStatement(sql); //3.填充占位符 for(int i = 0;i < args.length;i++){ ps.setObject(i + 1, args[i]); } //4.执行sql语句 ps.execute(); } catch (Exception e) { e.printStackTrace(); }finally{ //5.关闭资源 JDBCUtils.closeResource(conn, ps); } }
3.使用PreparedStatement实现查询操作
查询操作会有结果集的返回；得专门处理结果集
一般将其封装为一个对象
针对于不同表的操作：
表也可以通过反射获取
sql语句，占位符；
查询的内容是可变的,可作为形参传入；
查询返回一个对象，返回值类型即为该对象的类型
主要步骤
1.获取数据库的连接
2.预编译sql语句，得到PreparedStatement对象
3.填充占位符
4.执行executeQuery(),得到结果集：ResultSet
5.得到结果集的元数据：ResultSetMetaData
6.通过ResultSetMetaData得到columnCount,columnLabel；通过ResultSet得到列值，使用反射，给对象的相应属性赋值
7.关闭资源
代码如下
public <T> T getInstance(Class<T> clazz, String sql, Object... args) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { // 1.获取数据库连接 conn = JDBCUtils.getConnection(); // 2.预编译sql语句，得到PreparedStatement对象 ps = conn.prepareStatement(sql); // 3.填充占位符 for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } // 4.执行executeQuery(),得到结果集：ResultSet rs = ps.executeQuery(); // 5.得到结果集的元数据：ResultSetMetaData ResultSetMetaData rsmd = rs.getMetaData(); // 6.1通过ResultSetMetaData得到columnCount,columnLabel；通过ResultSet得到列值 int columnCount = rsmd.getColumnCount(); if (rs.next()) { T t = clazz.newInstance(); for (int i = 0; i < columnCount; i++) {// 遍历每一个列 // 获取列值 Object columnVal = rs.getObject(i + 1); // 获取列的别名:列的别名，使用类的属性名充当 String columnLabel = rsmd.getColumnLabel(i + 1); // 6.2使用反射，给对象的相应属性赋值 Field field = clazz.getDeclaredField(columnLabel);//获取类的的属性值 field.setAccessible(true);//属性值有可能是私有的 field.set(t, columnVal); } return t; } } catch (Exception e) { e.printStackTrace(); } finally { // 7.关闭资源 JDBCUtils.closeResource(conn, ps, rs); } return null; }
细节注意：
当数据库的列名与类的属性名不一样时，可以给表的列名起别名为类的属性名
获取列的别名的方法：getColumLabel();
4.使用PreparedStatement操作BLOB类型字段
- MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。
- 插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。
向数据表中插入大数据类型
//获取连接 Connection conn = JDBCUtils.getConnection(); String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)"; PreparedStatement ps = conn.prepareStatement(sql); // 填充占位符 ps.setString(1, "徐海强"); ps.setString(2, "xhq@126.com"); ps.setDate(3, new Date(new java.util.Date().getTime())); // 操作Blob类型的变量 FileInputStream fis = new FileInputStream("xhq.png"); ps.setBlob(4, fis); //执行 ps.execute(); fis.close(); JDBCUtils.closeResource(conn, ps);
修改数据表中的Blob类型字段
Connection conn = JDBCUtils.getConnection(); String sql = "update customers set photo = ? where id = ?"; PreparedStatement ps = conn.prepareStatement(sql); // 填充占位符 // 操作Blob类型的变量 FileInputStream fis = new FileInputStream("coffee.png"); ps.setBlob(1, fis); ps.setInt(2, 25); ps.execute(); fis.close(); JDBCUtils.closeResource(conn, ps);
从数据表中读取大数据类型
String sql = "SELECT id, name, email, birth, photo FROM customer WHERE id = ?"; conn = getConnection(); ps = conn.prepareStatement(sql); ps.setInt(1, 8); rs = ps.executeQuery(); if(rs.next()){ Integer id = rs.getInt(1); String name = rs.getString(2); String email = rs.getString(3); Date birth = rs.getDate(4); Customer cust = new Customer(id, name, email, birth); System.out.println(cust); //读取Blob类型的字段 Blob photo = rs.getBlob(5); InputStream is = photo.getBinaryStream(); OutputStream os = new FileOutputStream("c.jpg"); byte [] buffer = new byte[1024]; int len = 0; while((len = is.read(buffer)) != -1){ os.write(buffer, 0, len); } JDBCUtils.closeResource(conn, ps, rs); if(is != null){ is.close(); } if(os != null){ os.close(); } }
5.批量插入
JDBC的批量处理语句包括下面三个方法：
- addBatch(String)：添加需要批量处理的SQL语句或是参数；
- executeBatch()：执行批量处理语句；
- clearBatch():清空缓存的数据
通常我们会遇到两种批量执行SQL语句的情况：
- 多条SQL语句的批量处理；
- 一个SQL语句的批量传参；
高效的批量插入(案例）
举例：向数据表中插入20000条数据
数据库中提供一个goods表。创建如下：
CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
实现层次一：使用Statement
Connection conn = JDBCUtils.getConnection(); Statement st = conn.createStatement(); for(int i = 1;i <= 20000;i++){ String sql = "insert into goods(name) values('name_' + "+ i +")"; st.executeUpdate(sql); }
实现层次二：使用PreparedStatement
Connection conn = JDBCUtils.getConnection(); String sql = "insert into goods(name)values(?)"; PreparedStatement ps = conn.prepareStatement(sql); for(int i = 1;i <= 20000;i++){ ps.setString(1, "name_" + i); ps.executeUpdate(); } JDBCUtils.closeResource(conn, ps);
实现层次三
/* * 修改1： 使用 addBatch() / executeBatch() / clearBatch() * 修改2：mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。 * ?rewriteBatchedStatements=true 写在配置文件的url后面 * 修改3：使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar * */ @Test public void testInsert1() throws Exception{ Connection conn = JDBCUtils.getConnection(); String sql = "insert into goods(name)values(?)"; PreparedStatement ps = conn.prepareStatement(sql); for(int i = 1;i <= 1000000;i++){ ps.setString(1, "name_" + i); //1.“攒”sql ps.addBatch(); if(i % 500 == 0){ //2.执行 ps.executeBatch(); //3.清空 ps.clearBatch(); } } JDBCUtils.closeResource(conn, ps); }
实现层次四
/* * 层次四：在层次三的基础上操作 * 使用Connection 的 setAutoCommit(false) / commit() */ @Test public void testInsert2() throws Exception{ Connection conn = JDBCUtils.getConnection(); //1.设置为不自动提交数据 conn.setAutoCommit(false); String sql = "insert into goods(name)values(?)"; PreparedStatement ps = conn.prepareStatement(sql); for(int i = 1;i <= 1000000;i++){ ps.setString(1, "name_" + i); //1.“攒”sql ps.addBatch(); if(i % 500 == 0){ //2.执行 ps.executeBatch(); //3.清空 ps.clearBatch(); } } //2.提交数据 conn.commit(); JDBCUtils.closeResource(conn, ps); }
