大数据第26天-JDBC-杨大伟
1 package com.atguigu.jdbc.util; 2 3 import com.alibaba.druid.pool.DruidDataSourceFactory; 4 5 import javax.sql.DataSource; 6 import java.io.IOException; 7 import java.io.InputStream; 8 import java.sql.*; 9 import java.util.Properties; 10 11 public class JdbcUtil { 12 13 private static DataSource dataSource; // 用静态属性保存唯一的一个连接池对象. 14 15 public static Connection getConnection() throws SQLException { 16 if (dataSource == null) { // 只有第一次获取连接时才创建连接池对象 17 try { 18 Properties properties = new Properties(); 19 InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("druid.properties"); 20 properties.load(in); 21 in.close(); 22 // 直接通过工厂来自动读取配置文件中的配置信息, 并创建连接池对象. 23 dataSource = DruidDataSourceFactory.createDataSource(properties); 24 } catch (Exception e) { 25 throw new SQLException(e); // 异常的转型, 使得之前的代码不需要再修改即可用这个新方法. 26 } 27 } 28 Connection connection = dataSource.getConnection(); 29 return connection; 30 } 31 32 public static Connection getConnectionOld() throws IOException, ClassNotFoundException, SQLException { 33 // 读取配置文件 34 Properties properties = new Properties(); 35 InputStream input = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"); 36 properties.load(input); 37 input.close(); 38 // 加载驱动类 39 Class.forName(properties.getProperty("driverClassName")); 40 // 通过驱动管理器获取连接 41 return DriverManager.getConnection(properties.getProperty("url"), properties); 42 } 43 44 public static void close(Connection connection) { 45 close(connection, null); 46 } 47 48 public static void close(Connection connection, Statement statement) { 49 close(connection, statement, null); 50 } 51 52 public static void close(Connection connection, Statement statement, ResultSet resultSet) { 53 if (resultSet != null) { 54 try { 55 resultSet.close(); 56 } catch (SQLException throwables) { 57 throwables.printStackTrace(); 58 } 59 } 60 61 if (statement != null) { 62 try { 63 statement.close(); 64 } catch (SQLException throwables) { 65 throwables.printStackTrace(); 66 } 67 } 68 69 if (connection != null) { 70 try { 71 connection.close(); 72 } catch (SQLException throwables) { 73 throwables.printStackTrace(); 74 } 75 } 76 77 } 78 }
1 package com.atguigu.jdbc.util; 2 3 import java.io.IOException; 4 import java.lang.reflect.Field; 5 import java.sql.*; 6 import java.util.ArrayList; 7 import java.util.List; 8 9 public class CommonUtil { 10 11 /** 12 * 通用查询 13 * @param 14 * @param clazz 告诉此查询将来要返回的对象的类型 15 * @param sql 查询SQL 16 * @param args 替换SQL中?的实参列表 17 * @param <T> 类型的泛型参数 18 * @return 一个保存了批量的T类型的对象的集合 19 * @throws Exception 20 */ 21 public static <T> List<T> query(Connection connection, Class<T> clazz, String sql, Object... args) throws Exception { 22 List<T> list = new ArrayList<>(); 23 PreparedStatement preparedStatement = null; 24 ResultSet resultSet = null; 25 try { 26 preparedStatement = connection.prepareStatement(sql); // 预编译 27 for (int i = 0; i < args.length; i++) { // 替换? 28 preparedStatement.setObject(i + 1, args[i]); 29 } 30 resultSet = preparedStatement.executeQuery(); // 执行查询 31 ResultSetMetaData metaData = resultSet.getMetaData(); // 获取元数据, 主要是为了拿到表结构 32 int columnCount = metaData.getColumnCount(); // 获取列数 33 while (resultSet.next()) { 34 T object = clazz.newInstance(); // 直接创建一个javabean对象 35 for (int i = 0; i < columnCount; i++) { // 依次处理虚表中的所有列 36 String columnLabel = metaData.getColumnLabel(i + 1); // 标签就是虚表的列名, 同时也JavaBean类的属性名; 37 Object value = resultSet.getObject(columnLabel); // value将来可用于对象的属性值. 38 Field field = clazz.getDeclaredField(columnLabel); // 根据标签也是属性名获取属性定义对象, 用于反射 39 field.setAccessible(true); // 暴力反射 40 field.set(object, value); // 通过反射的方式为目标javabean对象的属性赋值. 41 } 42 list.add(object); // 把一个完整的javabean对象添加到集合中. 43 } 44 return list; 45 } finally { 46 JdbcUtil.close(null, preparedStatement, resultSet); 47 } 48 49 } 50 51 /** 52 * 通用查询 53 * @param clazz 告诉此查询将来要返回的对象的类型 54 * @param sql 查询SQL 55 * @param args 替换SQL中?的实参列表 56 * @param <T> 类型的泛型参数 57 * @return 一个保存了批量的T类型的对象的集合 58 * @throws Exception 59 */ 60 public static <T> List<T> query(Class<T> clazz, String sql, Object... args) throws Exception { 61 List<T> list = new ArrayList<>(); 62 Connection connection = null; 63 PreparedStatement preparedStatement = null; 64 ResultSet resultSet = null; 65 try { 66 connection = JdbcUtil.getConnection(); // 获取连接 67 preparedStatement = connection.prepareStatement(sql); // 预编译 68 for (int i = 0; i < args.length; i++) { // 替换? 69 preparedStatement.setObject(i + 1, args[i]); 70 } 71 resultSet = preparedStatement.executeQuery(); // 执行查询 72 ResultSetMetaData metaData = resultSet.getMetaData(); // 获取元数据, 主要是为了拿到表结构 73 int columnCount = metaData.getColumnCount(); // 获取列数 74 while (resultSet.next()) { 75 T object = clazz.newInstance(); // 直接创建一个javabean对象 76 for (int i = 0; i < columnCount; i++) { // 依次处理虚表中的所有列 77 String columnLabel = metaData.getColumnLabel(i + 1); // 标签就是虚表的列名, 同时也JavaBean类的属性名; 78 Object value = resultSet.getObject(columnLabel); // value将来可用于对象的属性值. 79 Field field = clazz.getDeclaredField(columnLabel); // 根据标签也是属性名获取属性定义对象, 用于反射 80 field.setAccessible(true); // 暴力反射 81 field.set(object, value); // 通过反射的方式为目标javabean对象的属性赋值. 82 } 83 list.add(object); // 把一个完整的javabean对象添加到集合中. 84 } 85 return list; 86 } finally { 87 JdbcUtil.close(connection, preparedStatement, resultSet); 88 } 89 90 } 91 92 /** 93 * 通用的查询 94 * @param connection 调用者传入的连接对象 95 * @param sql 要执行的sql 96 * @param args 用于替换sql中?的实参列表 97 */ 98 public static void view(Connection connection, String sql, Object... args) throws SQLException, IOException, ClassNotFoundException { 99 PreparedStatement preparedStatement = null; 100 ResultSet resultSet = null; 101 try { 102 preparedStatement = connection.prepareStatement(sql); // 预编译sql 103 for (int i = 0; i < args.length; i++) { // 批量替换sql中的所有?为实参. 104 preparedStatement.setObject(i + 1, args[i]); 105 } 106 resultSet = preparedStatement.executeQuery(); // 执行查询 107 ResultSetMetaData metaData = resultSet.getMetaData(); // metaData对象中包含的是虚表的表结构等原始数据 108 int columnCount = metaData.getColumnCount(); // 获取虚表的列数 109 System.out.println("----------------------------------------------------------------------------"); 110 for (int i = 0; i < columnCount; i++) { // 遍历所有列 111 String columnLabel = metaData.getColumnLabel(i + 1); // 根据列索引依次获取列标签 112 System.out.print(columnLabel + "\t"); // 打印表头 113 } 114 System.out.println(); 115 System.out.println("----------------------------------------------------------------------------"); 116 while (resultSet.next()) { // 遍历数据 117 for (int i = 0; i < columnCount; i++) { // 再一次遍历所有列 118 String columnLabel = metaData.getColumnLabel(i + 1); // 动态取出各个列标签 119 Object value = resultSet.getObject(columnLabel); // 根据列标签再取实际值, 更灵活 120 System.out.print(value + "\t"); 121 } 122 System.out.println(); 123 } 124 System.out.println("----------------------------------------------------------------------------"); 125 } finally { 126 JdbcUtil.close(null, preparedStatement, resultSet); 127 } 128 } 129 130 /** 131 * 通用的查询 132 * @param sql 要执行的sql 133 * @param args 用于替换sql中?的实参列表 134 */ 135 public static void view(String sql, Object... args) throws SQLException, IOException, ClassNotFoundException { 136 Connection connection = null; 137 PreparedStatement preparedStatement = null; 138 ResultSet resultSet = null; 139 try { 140 connection = JdbcUtil.getConnection(); 141 preparedStatement = connection.prepareStatement(sql); // 预编译sql 142 for (int i = 0; i < args.length; i++) { // 批量替换sql中的所有?为实参. 143 preparedStatement.setObject(i + 1, args[i]); 144 } 145 resultSet = preparedStatement.executeQuery(); // 执行查询 146 ResultSetMetaData metaData = resultSet.getMetaData(); // metaData对象中包含的是虚表的表结构等原始数据 147 int columnCount = metaData.getColumnCount(); // 获取虚表的列数 148 System.out.println("----------------------------------------------------------------------------"); 149 for (int i = 0; i < columnCount; i++) { // 遍历所有列 150 String columnLabel = metaData.getColumnLabel(i + 1); // 根据列索引依次获取列标签 151 System.out.print(columnLabel + "\t"); // 打印表头 152 } 153 System.out.println(); 154 System.out.println("----------------------------------------------------------------------------"); 155 while (resultSet.next()) { // 遍历数据 156 for (int i = 0; i < columnCount; i++) { // 再一次遍历所有列 157 String columnLabel = metaData.getColumnLabel(i + 1); // 动态取出各个列标签 158 Object value = resultSet.getObject(columnLabel); // 根据列标签再取实际值, 更灵活 159 System.out.print(value + "\t"); 160 } 161 System.out.println(); 162 } 163 System.out.println("----------------------------------------------------------------------------"); 164 } finally { 165 JdbcUtil.close(connection, preparedStatement, resultSet); 166 } 167 } 168 169 /** 170 * 通用更新操作, 可以执行DDL, 除了select外的DML 171 * @param connection 连接对象 172 * @param sql 要执行的SQL 173 * @param args 用于替换sql中的?的实参列表 174 * @return 影响的行数 175 */ 176 public static int update(Connection connection, String sql, Object... args) throws SQLException { 177 PreparedStatement preparedStatement = null; 178 try { 179 preparedStatement = connection.prepareStatement(sql); // 预编译SQL 180 for (int i = 0; i < args.length; i++) { // 替换sql中的? 181 preparedStatement.setObject(i + 1, args[i]); 182 } 183 int n = preparedStatement.executeUpdate(); // 执行更新操作, 并接收它影响的行数 184 return n; 185 } finally { // 释放资源, 不要关闭连接对象, 因为这个连接是别的程序传过来的. 186 JdbcUtil.close(null, preparedStatement); 187 } 188 } 189 190 /** 191 * 通用更新操作, 可以执行DDL, 除了select外的DML 192 * @param sql 要执行的SQL 193 * @param args 用于替换sql中的?的实参列表 194 * @return 影响的行数 195 */ 196 public static int update(String sql, Object... args) throws SQLException, IOException, ClassNotFoundException { 197 Connection connection = null; 198 PreparedStatement preparedStatement = null; 199 try { 200 connection = JdbcUtil.getConnection(); // 获取连接 201 preparedStatement = connection.prepareStatement(sql); // 预编译SQL 202 for (int i = 0; i < args.length; i++) { // 替换sql中的? 203 preparedStatement.setObject(i + 1, args[i]); 204 } 205 int n = preparedStatement.executeUpdate(); // 执行更新操作, 并接收它影响的行数 206 return n; 207 } finally { // 释放资源 208 JdbcUtil.close(connection, preparedStatement); 209 } 210 } 211 }
1 package com.atguigu.jdbc.test; 2 3 import com.atguigu.jdbc.util.CommonUtil; 4 import com.atguigu.jdbc.util.JdbcUtil; 5 import org.junit.Test; 6 7 import java.io.IOException; 8 import java.sql.Connection; 9 import java.sql.PreparedStatement; 10 import java.sql.SQLException; 11 12 public class PreparedStatementTest { 13 14 @Test 15 public void test6() { 16 Connection connection = null; 17 try { 18 connection = JdbcUtil.getConnection(); 19 String sql = "insert into student(name, age, mobile) values(?, ?, ?)"; 20 CommonUtil.update(connection, sql, "小黑", 18, "32842934"); 21 CommonUtil.update(connection, sql, "小红", 12, "234842934"); 22 } catch (Exception e) { 23 e.printStackTrace(); 24 } finally { 25 JdbcUtil.close(connection); 26 } 27 } 28 29 @Test 30 public void test5() throws SQLException, IOException, ClassNotFoundException { 31 /*CommonUtil.update("create table student(" + 32 "id int," + 33 "name varchar2(20)," + 34 "age int," + 35 "mobile varchar2(20)," + 36 "gender varchar2(3) default '男'," + 37 "primary key(id))");*/ 38 39 CommonUtil.update("insert into student(id, name, age, mobile) values(?, ?, ?, ?)", 1, "小丽", 30, "136234234234"); 40 CommonUtil.update("insert into student(id, name, age, mobile) values(?, ?, ?, ?)", 2, "小花", 10, "135234234234"); 41 CommonUtil.update("insert into student(id, name, age, mobile) values(?, ?, ?, ?)", 3, "小刚", 25, "137234234234"); 42 } 43 44 @Test 45 public void test4() throws SQLException, IOException, ClassNotFoundException { 46 String sql = "insert into teacher(name,age,salary,birthday) values (?, ?, ?, ?)"; 47 int rows = CommonUtil.update(sql, "贺飞", 20, 500, "1992-5-3"); 48 System.out.println(rows + " rows"); 49 } 50 51 @Test 52 public void test3() { 53 String sql = "insert into teacher(name,age,salary,birthday) values (?, ?, ?, ?)"; 54 Object[] args = {"小柴", 30, 6000, "1982-5-2"}; 55 56 Connection connection = null; 57 PreparedStatement preparedStatement = null; 58 try { 59 connection = JdbcUtil.getConnection(); 60 preparedStatement = connection.prepareStatement(sql); 61 for (int i = 0; i < args.length; i++) { 62 preparedStatement.setObject(i + 1, args[i]); 63 } 64 int n = preparedStatement.executeUpdate(); 65 System.out.println(n + " rows affected"); 66 } catch (Exception e) { 67 e.printStackTrace(); 68 } finally { 69 JdbcUtil.close(connection, preparedStatement); 70 } 71 } 72 73 @Test 74 public void test2() { 75 Connection connection = null; 76 PreparedStatement preparedStatement = null; 77 try { 78 connection = JdbcUtil.getConnection(); 79 String sql = "insert into teacher(name,age,salary,birthday) values (?, ?, ?, ?)"; 80 preparedStatement = connection.prepareStatement(sql); 81 82 preparedStatement.setObject(1, "芳芳"); 83 preparedStatement.setObject(2, 20); 84 preparedStatement.setObject(3, 5000); 85 preparedStatement.setObject(4, "1999-2-1"); 86 87 int n = preparedStatement.executeUpdate(); 88 System.out.println(n + " rows affected"); 89 } catch (Exception e) { 90 e.printStackTrace(); 91 } finally { 92 JdbcUtil.close(connection, preparedStatement); 93 } 94 } 95 96 @Test 97 public void test1() { 98 Connection connection = null; 99 PreparedStatement preparedStatement = null; 100 try { 101 connection = JdbcUtil.getConnection(); 102 String sql = "create table if not exists teacher(" + 103 "id int auto_increment, " + 104 "name varchar(20), " + 105 "age int, " + 106 "salary double, " + 107 "birthday date," + 108 "primary key(id)" + 109 ")"; 110 preparedStatement = connection.prepareStatement(sql); 111 int n = preparedStatement.executeUpdate(); 112 System.out.println(n + " rows affected"); 113 } catch (Exception e) { 114 e.printStackTrace(); 115 } finally { 116 JdbcUtil.close(connection, preparedStatement); 117 } 118 } 119 }
1 package com.atguigu.jdbc.test; 2 3 import com.atguigu.jdbc.javabean.Customer; 4 import com.atguigu.jdbc.javabean.Student; 5 import com.atguigu.jdbc.util.CommonUtil; 6 import com.atguigu.jdbc.util.JdbcUtil; 7 import org.junit.Test; 8 9 import java.io.IOException; 10 import java.lang.reflect.Field; 11 import java.sql.*; 12 import java.util.ArrayList; 13 import java.util.List; 14 15 public class ResultSetTest { 16 17 @Test 18 public void test8() throws Exception { 19 List<Student> query = CommonUtil.query(Student.class, "select * from student where id > ?", 1); 20 for (int i = 0; i < query.size(); i++) { 21 System.out.println(query.get(i)); 22 } 23 } 24 25 @Test 26 public <T> void test7() { 27 List<T> list = new ArrayList<T>(); // 保存多个对象, 有多少条记录就有多少个对象. 28 String sql = "select id,name,age, gender,email from customer where id > ?"; 29 Object[] args = {0}; 30 Class<T> clazz = (Class<T>)Customer.class; 31 32 Connection connection = null; 33 PreparedStatement preparedStatement = null; 34 ResultSet resultSet = null; 35 try { 36 connection = JdbcUtil.getConnection(); 37 preparedStatement = connection.prepareStatement(sql); 38 for (int i = 0; i < args.length; i++) { 39 preparedStatement.setObject(i + 1, args[i]); 40 } 41 resultSet = preparedStatement.executeQuery(); 42 ResultSetMetaData metaData = resultSet.getMetaData(); 43 int columnCount = metaData.getColumnCount(); 44 while (resultSet.next()) { 45 T object = clazz.newInstance(); 46 for (int i = 0; i < columnCount; i++) { 47 String columnLabel = metaData.getColumnLabel(i + 1); // 标签就是列名, 同时也Student类的属性名; 48 Object value = resultSet.getObject(columnLabel); // value将来可用于对象的属性值. 49 Field field = clazz.getDeclaredField(columnLabel); 50 field.setAccessible(true); 51 field.set(object, value); 52 } 53 list.add(object); 54 } 55 } catch (Exception e) { 56 e.printStackTrace(); 57 } finally { 58 JdbcUtil.close(connection, preparedStatement, resultSet); 59 } 60 61 for (int i = 0; i < list.size(); i++) { 62 System.out.println(list.get(i)); 63 } 64 } 65 66 @Test 67 public void test6() { 68 List<Student> list = new ArrayList<>(); // 保存多个对象, 有多少条记录就有多少个对象. 69 Connection connection = null; 70 PreparedStatement preparedStatement = null; 71 ResultSet resultSet = null; 72 try { 73 connection = JdbcUtil.getConnection(); 74 String sql = "select name, age, id, mobile, gender from student where id > ?"; 75 preparedStatement = connection.prepareStatement(sql); 76 preparedStatement.setObject(1, 2); 77 resultSet = preparedStatement.executeQuery(); 78 ResultSetMetaData metaData = resultSet.getMetaData(); 79 int columnCount = metaData.getColumnCount(); 80 while (resultSet.next()) { 81 Student student = new Student(); 82 for (int i = 0; i < columnCount; i++) { 83 String columnLabel = metaData.getColumnLabel(i + 1); // 标签就是列名, 同时也Student类的属性名; 84 Object value = resultSet.getObject(columnLabel); // value将来可用于对象的属性值. 85 Field field = Student.class.getDeclaredField(columnLabel); 86 field.setAccessible(true); 87 field.set(student, value); 88 } 89 list.add(student); 90 } 91 } catch (Exception e) { 92 e.printStackTrace(); 93 } finally { 94 JdbcUtil.close(connection, preparedStatement, resultSet); 95 } 96 97 for (int i = 0; i < list.size(); i++) { 98 System.out.println(list.get(i)); 99 } 100 } 101 102 103 @Test 104 public void test5() throws SQLException, IOException, ClassNotFoundException { 105 CommonUtil.view("select * from user"); 106 } 107 108 @Test 109 public void test4() { 110 String sql = "select * from world.city where id > ?"; 111 Object[] args = {0}; 112 113 Connection connection = null; 114 PreparedStatement preparedStatement = null; 115 ResultSet resultSet = null; 116 try { 117 connection = JdbcUtil.getConnection(); 118 preparedStatement = connection.prepareStatement(sql); // 预编译sql 119 for (int i = 0; i < args.length; i++) { // 批量替换sql中的所有?为实参. 120 preparedStatement.setObject(i + 1, args[i]); 121 } 122 resultSet = preparedStatement.executeQuery(); // 执行查询 123 ResultSetMetaData metaData = resultSet.getMetaData(); // metaData对象中包含的是虚表的表结构等原始数据 124 int columnCount = metaData.getColumnCount(); // 获取虚表的列数 125 System.out.println("----------------------------------------------------------------------------"); 126 for (int i = 0; i < columnCount; i++) { // 遍历所有列 127 String columnLabel = metaData.getColumnLabel(i + 1); // 根据列索引依次获取列标签 128 System.out.print(columnLabel + "\t"); // 打印表头 129 } 130 System.out.println(); 131 System.out.println("----------------------------------------------------------------------------"); 132 while (resultSet.next()) { // 遍历数据 133 for (int i = 0; i < columnCount; i++) { // 再一次遍历所有列 134 String columnLabel = metaData.getColumnLabel(i + 1); // 动态取出各个列标签 135 Object value = resultSet.getObject(columnLabel); // 根据列标签再取实际值, 更灵活 136 System.out.print(value + "\t"); 137 } 138 System.out.println(); 139 } 140 System.out.println("----------------------------------------------------------------------------"); 141 } catch (Exception e) { 142 e.printStackTrace(); 143 } finally { 144 JdbcUtil.close(connection, preparedStatement, resultSet); 145 } 146 147 } 148 149 @Test 150 public void test3() { 151 List<Student> list = new ArrayList<>(); // 保存多个对象, 有多少条记录就有多少个对象. 152 Connection connection = null; 153 PreparedStatement preparedStatement = null; 154 ResultSet resultSet = null; 155 try { 156 connection = JdbcUtil.getConnection(); 157 String sql = "select name, age, id, mobile, gender from student where id > ?"; 158 preparedStatement = connection.prepareStatement(sql); 159 preparedStatement.setObject(1, 2); 160 resultSet = preparedStatement.executeQuery(); 161 while (resultSet.next()) { 162 int id = resultSet.getInt("id"); // 使用列标签比使用列索引更好. 列标签就是虚表的列名 163 String name = resultSet.getString("name"); 164 int age = resultSet.getInt("age"); 165 String mobile = resultSet.getString("mobile"); 166 String gender = resultSet.getString("gender"); 167 Student student = new Student(id, name, age, mobile, gender); // ORM Object Relation Mapping 168 list.add(student); 169 } 170 } catch (Exception e) { 171 e.printStackTrace(); 172 } finally { 173 JdbcUtil.close(connection, preparedStatement, resultSet); 174 } 175 176 for (int i = 0; i < list.size(); i++) { 177 System.out.println(list.get(i)); 178 } 179 } 180 181 @Test 182 public void test2() { 183 Connection connection = null; 184 PreparedStatement preparedStatement = null; 185 ResultSet resultSet = null; 186 try { 187 connection = JdbcUtil.getConnection(); 188 String sql = "select name, age, id, mobile mob, gender stuGender from student where id > ?"; 189 preparedStatement = connection.prepareStatement(sql); 190 preparedStatement.setObject(1, 2); 191 resultSet = preparedStatement.executeQuery(); 192 while (resultSet.next()) { 193 int id = resultSet.getInt("id"); // 使用列标签比使用列索引更好. 列标签就是虚表的列名 194 String name = resultSet.getString("name"); 195 int age = resultSet.getInt("age"); 196 String mobile = resultSet.getString("mob"); 197 String gender = resultSet.getString("stuGender"); 198 System.out.println(id + "\t" + name + "\t" + age + "\t" + mobile + "\t" + gender); 199 } 200 } catch (Exception e) { 201 e.printStackTrace(); 202 } finally { 203 JdbcUtil.close(connection, preparedStatement, resultSet); 204 } 205 } 206 207 @Test 208 public void test1() { 209 /*mysql> select id, name, age, mobile, gender from student where id > 2; 210 +----+------+------+--------------+--------+ 211 | id | name | age | mobile | gender | <--- 初始的时候, 内部游标指向第一条记录前 212 +----+------+------+--------------+--------+ 213 | 3 | 小花 | 10 | 135234234234 | 男 | <---- next() 返回boolean并真的下移游标 214 | 4 | 小刚 | 25 | 137234234234 | 男 | <---- next() 返回boolean并真的下移游标 215 | 5 | 小黑 | 18 | 32842934 | 男 | 216 | 6 | 小红 | 12 | 234842934 | 男 | <---- next() 返回boolean并真的下移游标 217 +----+------+------+--------------+--------+<---- next() 返回boolean并真的下移游标*/ 218 Connection connection = null; 219 PreparedStatement preparedStatement = null; 220 ResultSet resultSet = null; // 结果集对象也是资源, 必须要释放 221 try { 222 connection = JdbcUtil.getConnection(); 223 String sql = "select id, name, age, mobile, gender from student where id > ?"; 224 preparedStatement = connection.prepareStatement(sql); 225 preparedStatement.setObject(1, 2); 226 resultSet = preparedStatement.executeQuery(); // 此时的游标在第一条记录之前 227 while (resultSet.next()) {// next()方法有2个作用, 一个是返回当前游标指向的行后面是否有新行, 另一个作用是真的下移游标 228 int id = resultSet.getInt(1); // 从当前游标指向的行取第1列 229 String name = resultSet.getString(2); // 从当前游标指向的行取第2列 230 int age = resultSet.getInt(3); // 从当前游标指向的行取第3列 231 String mobile = resultSet.getString(4); // 从当前游标指向的行取第4列 232 String gender = resultSet.getString(5); // 从当前游标指向的行取第5列 233 System.out.println(id + "\t" + name + "\t" + age + "\t" + mobile + "\t" + gender); 234 } 235 } catch (Exception e) { 236 e.printStackTrace(); 237 } finally { 238 JdbcUtil.close(connection, preparedStatement, resultSet); 239 } 240 } 241 }
1 package com.atguigu.jdbc.util; 2 3 import java.io.IOException; 4 import java.lang.reflect.Field; 5 import java.sql.*; 6 import java.util.ArrayList; 7 import java.util.List; 8 9 public class CommonUtil { 10 11 /** 12 * 通用查询 13 * @param 14 * @param clazz 告诉此查询将来要返回的对象的类型 15 * @param sql 查询SQL 16 * @param args 替换SQL中?的实参列表 17 * @param <T> 类型的泛型参数 18 * @return 一个保存了批量的T类型的对象的集合 19 * @throws Exception 20 */ 21 public static <T> List<T> query(Connection connection, Class<T> clazz, String sql, Object... args) throws Exception { 22 List<T> list = new ArrayList<>(); 23 PreparedStatement preparedStatement = null; 24 ResultSet resultSet = null; 25 try { 26 preparedStatement = connection.prepareStatement(sql); // 预编译 27 for (int i = 0; i < args.length; i++) { // 替换? 28 preparedStatement.setObject(i + 1, args[i]); 29 } 30 resultSet = preparedStatement.executeQuery(); // 执行查询 31 ResultSetMetaData metaData = resultSet.getMetaData(); // 获取元数据, 主要是为了拿到表结构 32 int columnCount = metaData.getColumnCount(); // 获取列数 33 while (resultSet.next()) { 34 T object = clazz.newInstance(); // 直接创建一个javabean对象 35 for (int i = 0; i < columnCount; i++) { // 依次处理虚表中的所有列 36 String columnLabel = metaData.getColumnLabel(i + 1); // 标签就是虚表的列名, 同时也JavaBean类的属性名; 37 Object value = resultSet.getObject(columnLabel); // value将来可用于对象的属性值. 38 Field field = clazz.getDeclaredField(columnLabel); // 根据标签也是属性名获取属性定义对象, 用于反射 39 field.setAccessible(true); // 暴力反射 40 field.set(object, value); // 通过反射的方式为目标javabean对象的属性赋值. 41 } 42 list.add(object); // 把一个完整的javabean对象添加到集合中. 43 } 44 return list; 45 } finally { 46 JdbcUtil.close(null, preparedStatement, resultSet); 47 } 48 49 } 50 51 /** 52 * 通用查询 53 * @param clazz 告诉此查询将来要返回的对象的类型 54 * @param sql 查询SQL 55 * @param args 替换SQL中?的实参列表 56 * @param <T> 类型的泛型参数 57 * @return 一个保存了批量的T类型的对象的集合 58 * @throws Exception 59 */ 60 public static <T> List<T> query(Class<T> clazz, String sql, Object... args) throws Exception { 61 List<T> list = new ArrayList<>(); 62 Connection connection = null; 63 PreparedStatement preparedStatement = null; 64 ResultSet resultSet = null; 65 try { 66 connection = JdbcUtil.getConnection(); // 获取连接 67 preparedStatement = connection.prepareStatement(sql); // 预编译 68 for (int i = 0; i < args.length; i++) { // 替换? 69 preparedStatement.setObject(i + 1, args[i]); 70 } 71 resultSet = preparedStatement.executeQuery(); // 执行查询 72 ResultSetMetaData metaData = resultSet.getMetaData(); // 获取元数据, 主要是为了拿到表结构 73 int columnCount = metaData.getColumnCount(); // 获取列数 74 while (resultSet.next()) { 75 T object = clazz.newInstance(); // 直接创建一个javabean对象 76 for (int i = 0; i < columnCount; i++) { // 依次处理虚表中的所有列 77 String columnLabel = metaData.getColumnLabel(i + 1); // 标签就是虚表的列名, 同时也JavaBean类的属性名; 78 Object value = resultSet.getObject(columnLabel); // value将来可用于对象的属性值. 79 Field field = clazz.getDeclaredField(columnLabel); // 根据标签也是属性名获取属性定义对象, 用于反射 80 field.setAccessible(true); // 暴力反射 81 field.set(object, value); // 通过反射的方式为目标javabean对象的属性赋值. 82 } 83 list.add(object); // 把一个完整的javabean对象添加到集合中. 84 } 85 return list; 86 } finally { 87 JdbcUtil.close(connection, preparedStatement, resultSet); 88 } 89 90 } 91 92 /** 93 * 通用的查询 94 * @param connection 调用者传入的连接对象 95 * @param sql 要执行的sql 96 * @param args 用于替换sql中?的实参列表 97 */ 98 public static void view(Connection connection, String sql, Object... args) throws SQLException, IOException, ClassNotFoundException { 99 PreparedStatement preparedStatement = null; 100 ResultSet resultSet = null; 101 try { 102 preparedStatement = connection.prepareStatement(sql); // 预编译sql 103 for (int i = 0; i < args.length; i++) { // 批量替换sql中的所有?为实参. 104 preparedStatement.setObject(i + 1, args[i]); 105 } 106 resultSet = preparedStatement.executeQuery(); // 执行查询 107 ResultSetMetaData metaData = resultSet.getMetaData(); // metaData对象中包含的是虚表的表结构等原始数据 108 int columnCount = metaData.getColumnCount(); // 获取虚表的列数 109 System.out.println("----------------------------------------------------------------------------"); 110 for (int i = 0; i < columnCount; i++) { // 遍历所有列 111 String columnLabel = metaData.getColumnLabel(i + 1); // 根据列索引依次获取列标签 112 System.out.print(columnLabel + "\t"); // 打印表头 113 } 114 System.out.println(); 115 System.out.println("----------------------------------------------------------------------------"); 116 while (resultSet.next()) { // 遍历数据 117 for (int i = 0; i < columnCount; i++) { // 再一次遍历所有列 118 String columnLabel = metaData.getColumnLabel(i + 1); // 动态取出各个列标签 119 Object value = resultSet.getObject(columnLabel); // 根据列标签再取实际值, 更灵活 120 System.out.print(value + "\t"); 121 } 122 System.out.println(); 123 } 124 System.out.println("----------------------------------------------------------------------------"); 125 } finally { 126 JdbcUtil.close(null, preparedStatement, resultSet); 127 } 128 } 129 130 /** 131 * 通用的查询 132 * @param sql 要执行的sql 133 * @param args 用于替换sql中?的实参列表 134 */ 135 public static void view(String sql, Object... args) throws SQLException, IOException, ClassNotFoundException { 136 Connection connection = null; 137 PreparedStatement preparedStatement = null; 138 ResultSet resultSet = null; 139 try { 140 connection = JdbcUtil.getConnection(); 141 preparedStatement = connection.prepareStatement(sql); // 预编译sql 142 for (int i = 0; i < args.length; i++) { // 批量替换sql中的所有?为实参. 143 preparedStatement.setObject(i + 1, args[i]); 144 } 145 resultSet = preparedStatement.executeQuery(); // 执行查询 146 ResultSetMetaData metaData = resultSet.getMetaData(); // metaData对象中包含的是虚表的表结构等原始数据 147 int columnCount = metaData.getColumnCount(); // 获取虚表的列数 148 System.out.println("----------------------------------------------------------------------------"); 149 for (int i = 0; i < columnCount; i++) { // 遍历所有列 150 String columnLabel = metaData.getColumnLabel(i + 1); // 根据列索引依次获取列标签 151 System.out.print(columnLabel + "\t"); // 打印表头 152 } 153 System.out.println(); 154 System.out.println("----------------------------------------------------------------------------"); 155 while (resultSet.next()) { // 遍历数据 156 for (int i = 0; i < columnCount; i++) { // 再一次遍历所有列 157 String columnLabel = metaData.getColumnLabel(i + 1); // 动态取出各个列标签 158 Object value = resultSet.getObject(columnLabel); // 根据列标签再取实际值, 更灵活 159 System.out.print(value + "\t"); 160 } 161 System.out.println(); 162 } 163 System.out.println("----------------------------------------------------------------------------"); 164 } finally { 165 JdbcUtil.close(connection, preparedStatement, resultSet); 166 } 167 } 168 169 /** 170 * 通用更新操作, 可以执行DDL, 除了select外的DML 171 * @param connection 连接对象 172 * @param sql 要执行的SQL 173 * @param args 用于替换sql中的?的实参列表 174 * @return 影响的行数 175 */ 176 public static int update(Connection connection, String sql, Object... args) throws SQLException { 177 PreparedStatement preparedStatement = null; 178 try { 179 preparedStatement = connection.prepareStatement(sql); // 预编译SQL 180 for (int i = 0; i < args.length; i++) { // 替换sql中的? 181 preparedStatement.setObject(i + 1, args[i]); 182 } 183 int n = preparedStatement.executeUpdate(); // 执行更新操作, 并接收它影响的行数 184 return n; 185 } finally { // 释放资源, 不要关闭连接对象, 因为这个连接是别的程序传过来的. 186 JdbcUtil.close(null, preparedStatement); 187 } 188 } 189 190 /** 191 * 通用更新操作, 可以执行DDL, 除了select外的DML 192 * @param sql 要执行的SQL 193 * @param args 用于替换sql中的?的实参列表 194 * @return 影响的行数 195 */ 196 public static int update(String sql, Object... args) throws SQLException, IOException, ClassNotFoundException { 197 Connection connection = null; 198 PreparedStatement preparedStatement = null; 199 try { 200 connection = JdbcUtil.getConnection(); // 获取连接 201 preparedStatement = connection.prepareStatement(sql); // 预编译SQL 202 for (int i = 0; i < args.length; i++) { // 替换sql中的? 203 preparedStatement.setObject(i + 1, args[i]); 204 } 205 int n = preparedStatement.executeUpdate(); // 执行更新操作, 并接收它影响的行数 206 return n; 207 } finally { // 释放资源 208 JdbcUtil.close(connection, preparedStatement); 209 } 210 } 211 }
1 package com.atguigu.jdbc.util; 2 3 import com.alibaba.druid.pool.DruidDataSourceFactory; 4 5 import javax.sql.DataSource; 6 import java.io.IOException; 7 import java.io.InputStream; 8 import java.sql.*; 9 import java.util.Properties; 10 11 public class JdbcUtil { 12 13 private static DataSource dataSource; // 用静态属性保存唯一的一个连接池对象. 14 15 public static Connection getConnection() throws SQLException { 16 if (dataSource == null) { // 只有第一次获取连接时才创建连接池对象 17 try { 18 Properties properties = new Properties(); 19 InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("druid.properties"); 20 properties.load(in); 21 in.close(); 22 // 直接通过工厂来自动读取配置文件中的配置信息, 并创建连接池对象. 23 dataSource = DruidDataSourceFactory.createDataSource(properties); 24 } catch (Exception e) { 25 throw new SQLException(e); // 异常的转型, 使得之前的代码不需要再修改即可用这个新方法. 26 } 27 } 28 Connection connection = dataSource.getConnection(); 29 return connection; 30 } 31 32 public static Connection getConnectionOld() throws IOException, ClassNotFoundException, SQLException { 33 // 读取配置文件 34 Properties properties = new Properties(); 35 InputStream input = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"); 36 properties.load(input); 37 input.close(); 38 // 加载驱动类 39 Class.forName(properties.getProperty("driverClassName")); 40 // 通过驱动管理器获取连接 41 return DriverManager.getConnection(properties.getProperty("url"), properties); 42 } 43 44 public static void close(Connection connection) { 45 close(connection, null); 46 } 47 48 public static void close(Connection connection, Statement statement) { 49 close(connection, statement, null); 50 } 51 52 public static void close(Connection connection, Statement statement, ResultSet resultSet) { 53 if (resultSet != null) { 54 try { 55 resultSet.close(); 56 } catch (SQLException throwables) { 57 throwables.printStackTrace(); 58 } 59 } 60 61 if (statement != null) { 62 try { 63 statement.close(); 64 } catch (SQLException throwables) { 65 throwables.printStackTrace(); 66 } 67 } 68 69 if (connection != null) { 70 try { 71 connection.close(); 72 } catch (SQLException throwables) { 73 throwables.printStackTrace(); 74 } 75 } 76 77 } 78 }
1 package com.atguigu.jdbc.test; 2 3 import com.alibaba.druid.pool.DruidDataSource; 4 import com.alibaba.druid.pool.DruidDataSourceFactory; 5 import com.atguigu.jdbc.util.JdbcUtil; 6 import org.junit.Test; 7 8 import javax.sql.DataSource; 9 import java.io.InputStream; 10 import java.sql.Connection; 11 import java.sql.SQLException; 12 import java.util.Properties; 13 14 public class DruidTest { 15 16 @Test 17 public void test3() throws SQLException { 18 Connection connection = JdbcUtil.getConnection(); 19 System.out.println(connection); 20 } 21 22 @Test 23 public void test2() throws Exception { 24 Properties properties = new Properties(); 25 InputStream in = getClass().getClassLoader().getResourceAsStream("druid.properties"); 26 properties.load(in); 27 in.close(); 28 // 直接通过工厂来自动读取配置文件中的配置信息, 并创建连接池对象. 29 DataSource dataSource = DruidDataSourceFactory.createDataSource(properties); 30 Connection connection = dataSource.getConnection(); 31 System.out.println(connection); 32 } 33 34 @Test 35 public void test1() throws SQLException { 36 DruidDataSource druidDataSource = new DruidDataSource(); 37 // 传递必要的参数 38 druidDataSource.setDriverClassName("com.mysql.jdbc.Driver"); 39 druidDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/jdbc"); 40 druidDataSource.setUsername("root"); 41 druidDataSource.setPassword("123456"); 42 43 Connection connection = druidDataSource.getConnection(); 44 System.out.println(connection.getClass()); 45 46 connection.close(); 47 } 48 }
1 package com.atguigu.jdbc.test; 2 3 import com.atguigu.jdbc.javabean.Customer; 4 import com.atguigu.jdbc.util.JdbcUtil; 5 import org.apache.commons.dbutils.QueryRunner; 6 import org.apache.commons.dbutils.handlers.BeanHandler; 7 import org.apache.commons.dbutils.handlers.BeanListHandler; 8 import org.apache.commons.dbutils.handlers.ScalarHandler; 9 import org.junit.Test; 10 11 import java.sql.Connection; 12 import java.sql.SQLException; 13 import java.util.List; 14 15 public class DBUtilsTest { 16 17 @Test 18 public void test4() throws SQLException { 19 // <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) 20 QueryRunner queryRunner = new QueryRunner(); 21 Connection connection = JdbcUtil.getConnection(); 22 String sql = "select count(*) from customer"; 23 // 第3个参数是 结果集处理器对象, 是一个接口, 但是有很多子类. 24 // 把结果集中的第一行第一列直接取出来. 适合于取最简单的值. 25 ScalarHandler scalarHandler = new ScalarHandler(); 26 Object value = queryRunner.query(connection, sql, scalarHandler); 27 System.out.println(value); 28 } 29 30 @Test 31 public void test3() throws SQLException { 32 // <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) 33 QueryRunner queryRunner = new QueryRunner(); 34 Connection connection = JdbcUtil.getConnection(); 35 String sql = "select * from customer where id > ?"; 36 // 第3个参数是 结果集处理器对象, 是一个接口, 但是有很多子类. 37 // 把结果集中的所有记录全部转换为 指定的类型的 javabean对象, 并把所有对象保存一个List集合中. 38 BeanListHandler<Customer> customerBeanListHandler = new BeanListHandler<>(Customer.class); 39 List<Customer> list = queryRunner.query(connection, sql, customerBeanListHandler, 1); 40 for (int i = 0; i < list.size(); i++) { 41 System.out.println(list.get(i)); 42 } 43 } 44 45 @Test 46 public void test2() throws SQLException { 47 // <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) 48 QueryRunner queryRunner = new QueryRunner(); 49 Connection connection = JdbcUtil.getConnection(); 50 String sql = "select * from customer where id > ?"; 51 // 第3个参数是 结果集处理器对象, 是一个接口, 但是有很多子类. 52 // 结果集中的第一行数据 转换 成指定的类型 JavaBean对象 53 BeanHandler<Customer> beanHandler = new BeanHandler(Customer.class); 54 Customer customer = queryRunner.query(connection, sql, beanHandler, 1); 55 System.out.println(customer); 56 } 57 58 @Test 59 public void test1() throws SQLException { 60 QueryRunner queryRunner = new QueryRunner(); 61 Connection connection = JdbcUtil.getConnection(); 62 String sql = "insert into customer(name,age,gender,email) values(?,?,?,?)"; 63 int rows = queryRunner.update(connection, sql, "赵六", 60, "男", "zhao6@qq.com");// 第1个参数是连接, 第2个参数是sql, 后面是可变参数 64 System.out.println(rows + " rows affected"); 65 } 66 }
1 package com.atguigu.jdbc.dao; 2 3 import com.atguigu.jdbc.javabean.Customer; 4 import com.atguigu.jdbc.util.JdbcUtil; 5 import org.apache.commons.dbutils.QueryRunner; 6 import org.apache.commons.dbutils.handlers.BeanHandler; 7 import org.apache.commons.dbutils.handlers.BeanListHandler; 8 import org.apache.commons.dbutils.handlers.ScalarHandler; 9 10 import java.sql.Connection; 11 import java.sql.SQLException; 12 import java.util.List; 13 14 /** 15 * 专门处理customer表和Customer对象的映射 16 */ 17 public class CustomerDAO extends JdbcDAO<Customer> { 18 19 public CustomerDAO() { 20 super(Customer.class); 21 } 22 }
1 package com.atguigu.jdbc.dao; 2 3 import com.atguigu.jdbc.javabean.Customer; 4 import com.atguigu.jdbc.javabean.Student; 5 import com.atguigu.jdbc.javabean.User; 6 import org.junit.Test; 7 8 import java.sql.SQLException; 9 import java.util.Iterator; 10 import java.util.List; 11 12 /** 13 * DAO Data Access Object 14 */ 15 public class DAOTest { 16 17 @Test 18 public void test3() throws SQLException { 19 Iterator<User> iterator = new UserDAO().getList("select * from user").iterator(); 20 while (iterator.hasNext()) { 21 System.out.println(iterator.next()); 22 } 23 } 24 25 @Test 26 public void test2() throws SQLException { 27 StudentDAO studentDAO = new StudentDAO(); 28 List<Student> list = studentDAO.getList("select * from student where id > ?", 1); 29 for (Student student : list) { 30 System.out.println(student); 31 } 32 } 33 34 @Test 35 public void test1() throws SQLException { 36 CustomerDAO customerDAO = new CustomerDAO(); 37 List<Customer> list = customerDAO.getList("select * from customer where id > ?", 0); 38 for (Customer customer : list) { 39 System.out.println(customer); 40 } 41 System.out.println("*******************************"); 42 43 Customer customer = customerDAO.getBean("select * from customer where id = ?", 2); 44 System.out.println(customer); 45 46 Object value = customerDAO.getValue("select count(*) from customer"); 47 System.out.println("value = " + value); 48 49 customerDAO.update("delete from customer where id = ?", 1); 50 } 51 }
1 package com.atguigu.jdbc.dao; 2 3 import com.atguigu.jdbc.javabean.Student; 4 import com.atguigu.jdbc.util.JdbcUtil; 5 import org.apache.commons.dbutils.QueryRunner; 6 import org.apache.commons.dbutils.handlers.BeanHandler; 7 import org.apache.commons.dbutils.handlers.BeanListHandler; 8 import org.apache.commons.dbutils.handlers.ScalarHandler; 9 10 import java.sql.Connection; 11 import java.sql.SQLException; 12 import java.util.List; 13 14 public class JdbcDAO<T> { 15 16 private QueryRunner queryRunner = new QueryRunner(); // 执行器 17 private Class<T> clazz; 18 19 public JdbcDAO(Class<T> clazz) { 20 this.clazz = clazz; 21 } 22 23 public List<T> getList(String sql, Object... args) throws SQLException { 24 Connection connection = null; 25 try { 26 connection = JdbcUtil.getConnection(); 27 // 把结果集处理成一个List集合 28 BeanListHandler<T> handler = new BeanListHandler<T>(clazz); 29 return queryRunner.query(connection, sql, handler, args); 30 } finally { 31 JdbcUtil.close(connection); 32 } 33 } 34 35 public T getBean(String sql, Object... args) throws SQLException { 36 Connection connection = null; 37 try { 38 connection = JdbcUtil.getConnection(); 39 // 把结果集处理成一个对象 40 BeanHandler<T> handler = new BeanHandler<>(clazz); 41 return queryRunner.query(connection, sql, handler, args); 42 } finally { 43 JdbcUtil.close(connection); 44 } 45 } 46 47 public Object getValue(String sql, Object... args) throws SQLException { 48 Connection connection = null; 49 try { 50 connection = JdbcUtil.getConnection(); 51 ScalarHandler scalarHandler = new ScalarHandler(); 52 return queryRunner.query(connection, sql, scalarHandler, args); 53 } finally { 54 JdbcUtil.close(connection); 55 } 56 } 57 58 public int update(String sql, Object... args) throws SQLException { 59 Connection connection = null; 60 try { 61 connection = JdbcUtil.getConnection(); 62 return queryRunner.update(connection, sql, args); 63 } finally { 64 JdbcUtil.close(connection); 65 } 66 } 67 68 }
1 package com.atguigu.jdbc.dao; 2 3 4 import com.atguigu.jdbc.javabean.Student; 5 6 public class StudentDAO extends JdbcDAO<Student> { 7 8 public StudentDAO() { 9 super(Student.class); 10 } 11 }
1 package com.atguigu.jdbc.dao; 2 3 import com.atguigu.jdbc.javabean.User; 4 5 public class UserDAO extends JdbcDAO<User> { 6 7 public UserDAO() { 8 super(User.class); 9 } 10 }