JDBC (连接、基础的增删改查、PrepareStatement和Statement)

面向接口编程

java为连接不同的数据库提供了统一的接口，这样每个数据库厂商需要提供针对这个接口的具体实现，称之为数据库驱动。而由于接口是一种规范，因此就算对于不同的数据库，对他们的操作也是相同的，例如使用Driver driver = new 具体数据库驱动();就能得到特定数据库的驱动对象，然后通过driver.connect(参数1, 参数2)即可获取数据库连接。不管是什么数据库，对他们操作都是这些代码，大大提升了开发效率。

程序编写步骤

获取连接

在这之前，要获取mysql的驱动，你要把相应的jar包放在项目下：

方式一

import org.junit.Test; import java.sql.Connection; import java.sql.Driver; import java.sql.SQLException; import java.util.Properties; public class ConnectionTest { @Test public void test1() throws SQLException { Driver driver = new com.mysql.jdbc.Driver(); // jdbc:mysql: 协议，相当于 http: String url = "jdbc:mysql://localhost:3306/test"; Properties info = new Properties(); info.setProperty("user", "root"); info.setProperty("password", "root"); Connection connection = driver.connect(url, info); System.out.println(connection); } } com.mysql.jdbc.JDBC4Connection@21213b92

举个例子来理解上述操作，比如你要去旅游，这个Driver就相当于车，url就相当于目的地，properties就相当于你的车钥匙，只有这些都准备好了才能出发，这便是connection。

方式二 (基于方式一)

上述方式出现了第三方类：

Driver driver = new com.mysql.jdbc.Driver();

为了有更好的可移植性，我们可以用反射的方式，这样就可以动态的创建不同数据库的对象：

// 方式2 对方式1的迭代 @Test public void test2() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException { Class clazz = Class.forName("com.mysql.jdbc.Driver"); Driver driver = (Driver) clazz.newInstance(); String url = "jdbc:mysql://localhost:3306/test"; Properties info = new Properties(); info.setProperty("user", "root"); info.setProperty("password", "root"); Connection connection = driver.connect(url, info); System.out.println(connection); }

方式三 使用DriverManager

//方式3 使用DriverManager @Test public void test3() throws Exception { Class clazz = Class.forName("com.mysql.jdbc.Driver"); Driver driver = (Driver) clazz.newInstance(); String url = "jdbc:mysql://localhost:3306/test"; String user = "root"; String password = "root"; DriverManager.registerDriver(driver); Connection connection = DriverManager.getConnection(url, user, password); System.out.println(connection); }

方式四 (基于方式三)

//方式4 基于方式3 @Test public void test4() throws Exception { String url = "jdbc:mysql://localhost:3306/test"; String user = "root"; String password = "root"; Class.forName("com.mysql.jdbc.Driver"); // Driver driver = (Driver) clazz.newInstance(); // DriverManager.registerDriver(driver); Connection connection = DriverManager.getConnection(url, user, password); System.out.println(connection); }

为什么注释掉那两行也能成功？看下图：

因为当反射把Driver类加载到内存中时，就会自动执行类中的静态代码块，自动的帮我们new了Driver对象。

方式五 (不以硬编码的方式写参数)

将连接数据库需要的配置信息(url,user啥的)写到文件中，通过读文件的方式连接。

@Test public void test5() throws Exception { // 默认识别路径是src下 InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties"); Properties properties = new Properties(); properties.load(is); String user = properties.getProperty("user"); String password = properties.getProperty("password"); String url = properties.getProperty("url"); String driverClass = properties.getProperty("driverClass"); Class.forName(driverClass); Connection connection = DriverManager.getConnection(url, user, password); System.out.println(connection); }

操作和访问数据库

statement

这个方式存在如下弊端：

需要拼接字符串sql注入问题

https://www.bilibili.com/video/BV1eJ411c7rf?p=12

关于拼串： 关于sql注入：

PreparedStatement

增

package preparestatement.java; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.text.SimpleDateFormat; import java.util.Properties; public class PrepareStatementTest { @Test public void test1() throws Exception { InputStream is = PrepareStatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties"); Properties properties = new Properties(); properties.load(is); // 读取参数 String user = properties.getProperty("user"); String password = properties.getProperty("password"); String url = properties.getProperty("url"); String driverClass = properties.getProperty("driverClass"); Class.forName(driverClass); // 获取连接 Connection connection = DriverManager.getConnection(url, user, password); System.out.println(connection); // 预编译sql语句，返回preparedStatement的实例 String sql = "insert into customers(name,email,birth)value(?,?,?)"; PreparedStatement ps = connection.prepareStatement(sql); // 填充占位符 注意这里索引是从1开始的 ps.setString(1, "秦岚"); ps.setString(2, "qinlan@gmail.com"); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); java.util.Date date = simpleDateFormat.parse("1979-7-17"); ps.setDate(3, new Date(date.getTime())); // 执行sql操作 ps.execute(); // 关闭资源 ps.close(); connection.close(); } }

我们可以将创建连接/关闭连接这种代码封装到一个工具类中，以便其他的类调用：

package utils; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Properties; public class JDBCUtils { public static Connection getConnection() throws Exception{ // InputStream stream = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"); InputStream stream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties"); Properties properties = new Properties(); properties.load(stream); String user = properties.getProperty("user"); String password = properties.getProperty("password"); String url = properties.getProperty("url"); String driverClass = properties.getProperty("driverClass"); Class.forName(driverClass); Connection connection = DriverManager.getConnection(url, password, user); return connection; } public static void closeResource(Connection conn, PreparedStatement ps) { try { if (conn!=null) conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } try { ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } }

改

@Test public void test2() { Connection connection = null; PreparedStatement ps = null; try { // 获取数据库连接 connection = JDBCUtils.getConnection(); // 预编译sql语句，返回prepareStatement实例 String sql = "update customers set name = ? where id = ?"; ps = connection.prepareStatement(sql); // 填充占位符 ps.setString(1, "巴赫"); ps.setInt(2, 18); // 执行 ps.execute(); } catch (Exception e) { e.printStackTrace(); } finally { // 关闭资源 JDBCUtils.closeResource(connection, ps); } }

把更新功能抽象出来

我们可以把增、删、改封装到一个方法中：

public void update(String sql, Object ... args) { Connection connection = null; PreparedStatement ps = null; try { connection = JDBCUtils.getConnection(); ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { System.out.println(args[i]); ps.setObject(i+1, args[i]); } ps.execute(); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, ps); } }

测试：

@Test public void test3() { // String sql = "delete from customers where id = ?"; // update(sql, "12"); String sql2 = "update `order` set order_name = ? where order_id = ?"; update(sql2, "QQ", "4"); }

这里要注意的是，order这个表的名字 “order” 是关键字，因此直接写order会报错，如果想让程序知道你想更新order是个表而不是关键字，就要向上述代码一样加个反引号

查

和增删改不同，查询是有结果返回的，因此获取连接、sql语句等步骤都是和增删改相同的，但要处理结果。

我们创建一个类来保存查到的数据(这里只写属性，方法略去了)：

public class Customer { private int id; private String name; private String email; private Date birth; } @Test public void test1() { Connection connection = null; PreparedStatement ps = null; ResultSet resultSet = null; try { connection = JDBCUtils.getConnection(); String sql = "select id,name,email,birth from customers where id = ?"; ps = connection.prepareStatement(sql); ps.setObject(1, 20); resultSet = ps.executeQuery(); System.out.println(resultSet); if (resultSet.next()) { int id = resultSet.getInt("id"); String name = resultSet.getString(2); String email = resultSet.getString(3); Date birth = resultSet.getDate(4); Customer customer = new Customer(id, name, email, birth); System.out.println(customer); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, ps, resultSet); } }

next的作用是看看后面还有没有元素，如果有就指针下移，但不返回结果。

注意，resultSet也需要关闭资源，因此在JDBCUtils中添加一个重载的方法：

public static void closeResource(Connection conn, PreparedStatement ps, ResultSet result) { try { if (conn!=null) conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } try { ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } try { result.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } }

把查询操作也弄成通用的

上面的代码只能查固定的属性，能不能写一个通用的方法，让我们想查几个属性就查几个属性呢？

// 查询的通用写法 public Customer query(String sql, Object... args) { Connection connection = null; PreparedStatement ps = null; ResultSet rs = null; try { connection = JDBCUtils.getConnection(); ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i + 1, args[i]); } rs = ps.executeQuery(); // 获取元数据 ResultSetMetaData metaData = rs.getMetaData(); // 根据元数据获取查询结果的列数 int columnCount = metaData.getColumnCount(); if (rs.next()) { Customer c = new Customer(); // 写在这里最好，如果写在if外面，那么当rs为空的时候仍然会创建对象 for (int i = 0; i < columnCount; i++) { // 获取每列的值 Object columnValue = rs.getObject(i + 1); // 获取每列的列名 String columnName = metaData.getColumnName(i + 1); // 给Customer对象的columnName属性赋值为columnValue，利用反射 Field field = Customer.class.getDeclaredField(columnName); field.setAccessible(true); // 属性是私有的，不要忘了设置权限 field.set(c, columnValue); } return c; } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, ps, rs); } return null; }

说明：

由于我们想查可变数量的属性，所以sql中的属性个数是不定的我们还是先获取连接，把占位符设置好，然后执行查询如何知道查询结果中有几列呢？因此要获取结果集的列数(通过元数据(就是描述结果信息的数据)，已在代码中标明)有了列数，就能通过循环来得到当前结果每一列的值，由于我们想把查询结果保存到对象中，因此还要知道当前列的属性名，同样通过元数据获得现在知道了列名和值，要给对象赋值了，如何赋值？通过反射！(已标注)

调用：

@Test public void test2() { // String sql = "select id, name, birth, email from customers where id = ?"; String sql = "select id, name, email from customers where id = ?"; Customer customer = query(sql, "4"); System.out.println(customer); } 结果： Customer{id=4, name='汤唯', email='tangw@sina.com', birth=null}

当Java类中属性名和数据库中属性名不匹配的情况

上面的代码是Java类中的属性名和数据库中的字段名完全相同的情况，但有时这二者并不会完全相同，比如下面这个表：

我们可能在对应的类里起的名字是：

public class Order { private int orderId; private String orderName; private Date orderDate; }

如果还像前面那样查询，就会报错：

// Java类的属性名和数据库中属性名不相同，例如Order类 public Order orderQuery(String sql, Object... args) { Connection connection = null; PreparedStatement ps = null; ResultSet rs = null; try { connection = JDBCUtils.getConnection(); ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i+1, args[0]); } rs = ps.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); if (rs.next()) { Order order = new Order(); for (int i = 0; i < columnCount; i++) { Object columnValue = rs.getObject(i+1); String columnName = metaData.getColumnName(i + 1); Field field = Order.class.getDeclaredField(columnName); field.setAccessible(true); field.set(order, columnValue); } return order; } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, ps, rs); } return null; } @Test public void orderTest() { String sql = "select order_id, order_name, order_date from `order` where order_id = ?"; Order order = orderQuery(sql, 2); System.out.println(order); }

结果：

这是因为Java类中的属性和数据库的字段名不匹配。

解决方案是起别名，在sql查询的时候可以起别名，让这个别名和Java类中的属性相同即可：

@Test public void orderTest() { String sql = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id = ?"; Order order = orderQuery(sql, 2); System.out.println(order); }

但是此时运行还是报错，因为metaData.getColumnName得到的是列名而不是别名，要使用getColumnLabel：

这个getColumnLabel是你起了别名他就返回别名，没起名字就返回列名。

小结

不同表的查询操作

上述代码可以针对一个表的若干属性进行查询，现在想对不同表的多个属性来进行查询。

// 多表不同属性的查询 public <T> T queryMulti(Class<T> clazz, String sql, Object ... args) { Connection connection = null; PreparedStatement ps = null; ResultSet rs = null; try { connection = JDBCUtils.getConnection(); ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i+1, args[i]); } rs = ps.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); if (rs.next()) { T t = clazz.newInstance(); for (int i = 0; i < columnCount; i++) { Object columnValue = rs.getObject(i + 1); String columnLable = metaData.getColumnLabel(i + 1); Field field = clazz.getDeclaredField(columnLable); field.setAccessible(true); field.set(t, columnValue); } return t; } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, ps, rs); } return null; } @Test public void multiTable() { String sql = "select name, birth, email from customers where id = ?"; Customer customer = queryMulti(Customer.class, sql, 3); System.out.println(customer); String sql2 = "select order_id orderId, order_name orderName from `order` where order_id = ?"; Order order = queryMulti(Order.class, sql2, 4); System.out.println(order); } 结果： Customer{id=0, name='林志玲', email='linzl@gmail.com', birth=1984-06-12} Order{orderId=4, orderName='QQ', orderDate=null}

说明：

这里的改动主要就是使用了泛型，在调用查询方法的时候指定要查询的表名注意泛型方法的声明：public <T> T queryMulti(Class<T> clazz, String sql, Object … args)，不能直接写成public T queryMulti(Class<T> clazz, String sql, Object … args)，因为这么写编译器会认为这个T是一个类而不是泛型。

查询多条记录

现在我们想让查询结果中包含多条记录。

只需用集合即可：

// 查询结果包含多条记录 public <T> List<T> queryMultiItems(Class<T> clazz, String sql, Object ... args) { Connection connection = null; PreparedStatement ps = null; ResultSet rs = null; try { connection = JDBCUtils.getConnection(); ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i+1, args[i]); } rs = ps.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); ArrayList<T> list = new ArrayList<>(); while (rs.next()) { T t = clazz.newInstance(); for (int i = 0; i < columnCount; i++) { Object columnValue = rs.getObject(i + 1); String columnLable = metaData.getColumnLabel(i + 1); Field field = clazz.getDeclaredField(columnLable); field.setAccessible(true); field.set(t, columnValue); } list.add(t); } return list; } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, ps, rs); } return null; } @Test public void multiItems() { String sql = "select email, id, name from customers where id < ?"; List<Customer> customerList = queryMultiItems(Customer.class, sql, 10); customerList.forEach(s -> System.out.println(s)); } Customer{id=1, name='汪峰', email='wf@126.com', birth=null} Customer{id=2, name='王菲', email='wangf@163.com', birth=null} Customer{id=3, name='林志玲', email='linzl@gmail.com', birth=null} Customer{id=4, name='汤唯', email='tangw@sina.com', birth=null} Customer{id=5, name='成龙', email='Jackey@gmai.com', birth=null} Customer{id=6, name='迪丽热巴', email='reba@163.com', birth=null} Customer{id=7, name='刘亦菲', email='liuyifei@qq.com', birth=null} Customer{id=8, name='陈道明', email='bdf@126.com', birth=null}

prepareStatement和statement

prepareStatement不需要拼接sql语句prepareStatement可以解决sql注入问题prepareStatement可以操作图片、音频等数据prepareStatement可以更高效的实现批量操作

https://www.bilibili.com/video/BV1eJ411c7rf?p=23

小结

几道练习题

1

public class AddItem { public static void main(String[] args) { System.out.println("***"); Scanner scanner = new Scanner(System.in); System.out.print("input user name: "); String name = scanner.next(); String email = scanner.next(); String birth = scanner.next(); String sql = "insert into customers(name, email, birth) values(?,?,?)"; int i = new AddItem().update(sql, name, email, birth); if (i > 0) { System.out.println("update success!!"); }else { System.out.println("update false!"); } } public int update(String sql, Object ... args) { Connection connection = null; PreparedStatement ps = null; try { connection = JDBCUtils.getConnection(); ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { System.out.println(args[i]); ps.setObject(i+1, args[i]); } // ps.execute(); return ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, ps); } return 0; } }

注意，之前都是用execute直接执行的，但他是有返回值的，如果是查询操作就返回true，如果是更新操作就返回false。而executeUpdate()方法是针对更新操作说的，返回0代表没有item受到影响，返回大于0的数代表受影响的行数

2

第一问和上一题很相似了，这里做一下第二问和第三问(只实现了按准考证查找，身份证类似)：

package practice; import bean.java.Student; import utils.JDBCUtils; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.Scanner; public class task2 { public static void main(String[] args) { System.out.println("请选择查询方式："); System.out.println("a 准考证号"); System.out.println("b 身份证号"); Scanner scanner = new Scanner(System.in); String s = scanner.next(); if ("a".equalsIgnoreCase(s)) { System.out.println("请输入准考证号"); String examCard = scanner.next(); String sql = "select FlowID flowID, Type type, IDCard idCard, ExamCard examCard, StudentName name, " + "Location location, Grade grade from examstudent where examCard=?"; Student student = new task2().queryMulti(Student.class, sql, examCard); System.out.println(student); }else if ("b".equalsIgnoreCase(s)) { } } public <T> T queryMulti(Class<T> clazz, String sql, Object ... args) { Connection connection = null; PreparedStatement ps = null; ResultSet rs = null; try { connection = JDBCUtils.getConnection(); ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i+1, args[i]); } rs = ps.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); if (rs.next()) { T t = clazz.newInstance(); for (int i = 0; i < columnCount; i++) { Object columnValue = rs.getObject(i + 1); String columnLable = metaData.getColumnLabel(i + 1); Field field = clazz.getDeclaredField(columnLable); field.setAccessible(true); field.set(t, columnValue); } return t; } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, ps, rs); } return null; } }

第三问：

public static void main(String[] args) { Scanner scanner = new Scanner(System.in); System.out.println("input the examId: "); String s = scanner.next(); String sql = "select FlowID flowID, Type type, IDCard idCard, ExamCard examCard, StudentName name, " + "Location location, Grade grade from examstudent where examCard=?"; Student student = new task2().queryMulti(Student.class, sql, s); if (student!=null) { String sql1 = "delete from examstudent where examCard = ?"; int i = new task2().update(sql1, s); if (i > 0) { System.out.println("success!"); } } }

操作Blob类型的数据

插入Blob数据

public class InsertBlob { @Test public void test() throws Exception { Connection connection = JDBCUtils.getConnection(); String sql = "insert into customers(name, email, birth, photo) values(?,?,?,?)"; PreparedStatement ps = connection.prepareStatement(sql); ps.setObject(1, "路飞"); ps.setObject(2, "lf@gmail.com"); ps.setObject(3, "1995-4-13"); ps.setBlob(4, new FileInputStream(new File("1.jpg"))); ps.execute(); } }

读取Blob数据

// 从数据库中读取Blob数据 @Test public void test2() throws Exception { InputStream is = null; FileOutputStream os = null; Connection connection = JDBCUtils.getConnection(); String sql = "select id, name, birth, email, photo from customers where id = ?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setObject(1, 22); ResultSet rs = ps.executeQuery(); if (rs.next()) { Blob photo = rs.getBlob("photo"); is = photo.getBinaryStream(); os = new FileOutputStream("2.jpg"); byte[] bytes = new byte[1024]; int len; while ((len = is.read(bytes)) != -1) { os.write(bytes, 0, len); } } JDBCUtils.closeResource(connection, ps, rs); is.close(); os.close(); }

prepareStatement 实现高效的批量操作

prepareStatement正常插入

这里没有用try，只是表达个意思：

@Test public void test() throws Exception { Connection connection = JDBCUtils.getConnection(); String sql = "insert into insert_test(name) values(?)"; PreparedStatement ps = connection.prepareStatement(sql); long l1 = System.currentTimeMillis(); for (int i = 0; i < 20000; i++) { ps.setObject(1, "name" + i); ps.execute(); } long l2 = System.currentTimeMillis(); System.out.println(l2-l1); JDBCUtils.closeResource(connection, ps); } 结果： 26479

这种方式每填充完一次占位符就执行一次，这样和磁盘交互次数较多导致效率不高，因此可以用如下方式：

使用Batch

// 使用batch插入 @Test public void test2() throws Exception { Connection connection = JDBCUtils.getConnection(); String sql = "insert into insert_test(name) values(?)"; PreparedStatement ps = connection.prepareStatement(sql); long l1 = System.currentTimeMillis(); for (int i = 0; i < 20000; i++) { ps.setObject(1, "name" + i); // 先存着，不执行 ps.addBatch(); if (i % 500 == 0) { // 存到500条后再执行 ps.executeBatch(); // 清空batch ps.clearBatch(); } } long l2 = System.currentTimeMillis(); System.out.println(l2-l1); JDBCUtils.closeResource(connection, ps); } 结果： 496

但使用Batch之前还要做几处配置：

设置自动提交

// 设置自动提交 @Test public void test3() throws Exception { Connection connection = JDBCUtils.getConnection(); String sql = "insert into insert_test(name) values(?)"; PreparedStatement ps = connection.prepareStatement(sql); long l1 = System.currentTimeMillis(); // 关闭自动提交 connection.setAutoCommit(false); for (int i = 0; i < 20000; i++) { ps.setObject(1, "name" + i); // 先存着，不执行 ps.addBatch(); if (i % 500 == 0) { // 存到500条后再执行 ps.executeBatch(); // 清空batch ps.clearBatch(); } } // 缓存结束再统一提交 connection.commit(); long l2 = System.currentTimeMillis(); System.out.println(l2-l1); JDBCUtils.closeResource(connection, ps); }

总结

PrepareStatement和Statement的对比 (面试)

https://www.bilibili.com/video/BV1eJ411c7rf?p=34

他们都是带着sql语句到数据库中执行操作的。

后者存在弊端：拼串和sql注入。

前者还能实现对Blob字段的操作。

前者批量插入数据时更高效：预编译

前者的诸多优点都是源于预编译：