JDBC

前言

​ Java中万物皆对象，那Java与数据库的关系呢?

​ 数据库中所有的数据都在表中：列名、类型、多行数据

​ Java中所有的数据都使用对象：属性、类型、多个对象

​ 通过上面的观察，我们发现一张表正好对应一一个Java类，一 行数据对应一个对象,表中列对应于Java类的属性, 那么这种跟数据库对应的类我们统称为model(pojo, bean等…)
类。

概述

​ JDBC（Java Data Base Connectivity）：Java数据连接规范，是Java访问数据库的基石，其他所有访问数据库的技术（mybatis，hibernate，Springjdbc等）都是基于JDBC来实现的

JDBC到底是什么

​ JDBC是一组接口，为了访问不同数据库提供统一的操作，对程序员隐瞒了具体的实现，只是提供了一种执行sql的API，哪一个数据库想让Java使用它，就需要去实现JDBC这一组接口，由于虽然数据库的每个厂商实现方式不同，但是因为他们实现了一组相同的接口，所以对我们Java程序员来说只要学会使用一种，其他的也就都会使用了

​ JDBC3.0对应Java版本1.5，JDBC4.0对应Java版本1.6

Statement的问题

​ 1：参数只能使用字符串拼接的方式传递，字符串拼接太过复杂，比较麻烦

​ 2：Statement执行行效率略低

​ 3：会造成sql注入

​ sql注入：用户输入内容的时候，输入了数据库的关键字，改变了原本sql的行为

PreparedStatement解决了Statement引出的三个问题

​ 1：通过调用方法，根据索引向sql中传递参数，避免字符串拼接太过复杂

​ 2：预编译对象，执行效率较高

​ 3：可以预防sql注入

DAO

​ DAO设计模式

package com.ishangu.dao;import java.util.List;import com.ishangu.model.Emp;/*** 主要存放针对emp表的数据库操作抽象方法*/public interface EmpDAO {//增加的方法void save(Emp emp);//删除方法void delete(int empno);//修改的方法void update(Emp emp);//根据ID查询单个用户Emp selectByEmpno(int empno);//无条件查所有数据List<Emp> selectAll();//根据工资范围查询List<Emp> selectBetweenSal(int i,int j);
}

DAO组成

​ DatabaseConnection：专门负责数据库打开或关闭操作的类

​ VO：主要由属性，setter，getter方法组成，VO类中的属性与表中的字段相对应，每一个VO类的对象都表示表中的每一条记录

​ DAO：主要定义操作的接口，定义一系列数据库的原子性操作，例如增删改查等

​ Impl：DAO接口的真实实现类，主要完成具体数据库操作，但不负责数据库的打开和关闭

​ PROxy：代理实现类，主要完成具体数据库操作，但不负责数据库的打开和关闭

​ Factory：工厂类，通过工厂类取得一个DAO的实例化对象

​ DAO的主要目的实际上可以简单的理解成将数据库的各种操作封装到一起，与其他业务相关代码隔开

​ 使用DAO设计模式已经成为了一个规范

​ com.ishangu.model：对应数据库表的类

​ com.ishangu.dao：针对数据库操作的接口，一般每一个model类都有一个接口，里面放所有的CRUD操作

​ com.ishangu.dao.impl：放接口的实现类，一个接口可能会有多个实现类

​ com.ishangu.utils：存放一些工具类，例如DatabaseConnection或者加密工具类等

数据库操作工具类DBUtil

将基本数据库信息封装成常量

private static final String URL = "jdbc:mysql:///";
private static final String DATA_BASE_NAME="bbb";
private static final String USERNAME = "root";
private static final String PASSWORD = "123456";

JDBC常用对象

private static Connection con;
private static PreparedStatement ps;
private static ResultSet rs;

私有化工具类的构造方法

​ 不让别人new对象

private DBUtil() {}

获取数据库连接对象

public static Connection getConnection() {try {con = DriverManager.getConnection(URL+DATA_BASE_NAME,USERNAME, PASSWORD);} catch (Exception e) {e.printStackTrace();}return con;
}

获取预编译对象

public static PreparedStatement getPS(String sql) {//获取连接对象getConnection();try {ps = con.prepareStatement(sql);} catch (Exception e) {e.printStackTrace();}return ps;
}

关闭连接对象的方法

public static void close(Connection con) {if(con!=null){try {con.close();} catch (Exception e) {e.printStackTrace();}}
}

关闭预编译对象的方法

public static void close(Connection con, PreparedStatement ps) {if (ps != null) {try {ps.close();close(con);} catch (Exception e) {e.printStackTrace();}}
}

关闭结果集对象的方法

public static void close(Connection con, PreparedStatement ps, ResultSet rs) {if (rs != null) {try {rs.close();close(con, ps);} catch (Exception e) {e.printStackTrace();}}
}

通用的DML

​ 参数1：需要执行的SQL语句

​ 参数2：是SQL中占位符需要的值，由于不知道SQL中有多少个占位符，所以采用Object…

public static void dml(String sql,Object...objects){getPS(sql);// 此时预编译对象已经有了try {for (int i = 0; i < objects.length; i++) {ps.setObject(i+1, objects[i]);}ps.execute();// 执行sql语句} catch (Exception e) {e.printStackTrace();}finally{close(con, ps);}
}

通用的DQL（有条件）

​ 最大的难题就是查询不同的表，需要返回不同的model类对象，此时我们可以在形参中传入查询的model类型，然后根据反射创建对象，完成结果集的封装

public static <E> E dqlOne(String sql,Class<E> c,Object...objects){getPS(sql);E e=null;// 声明返回的值try {for (int i = 0; i < objects.length; i++) {ps.setObject(i+1, objects[i]);}rs = ps.executeQuery();// 首先获取结果集中所有列的集合ResultSetMetaData data = rs.getMetaData();// 然后获得列的数量int count = data.getColumnCount();// 根据列的数量创建一个字符串数组String[] str = new String[count];// 将所有的列名称存入到字符串数组之内for (int i = 0; i < str.length; i++) {str[i] = data.getColumnLabel(i+1);}String[] fields=method(str);// 需要返回的是一个单纯的整数，也证明sql是求总数的sqlif(c.getName().equals("int")){rs.next();return (E) new Integer(rs.getInt(1));}// 开始遍历结果集while(rs.next()){if (rs.getRow() > 1) {throw new Exception("应该查出一条数据，但是查出了多条数据");}// 通过反射，将e实例化成对象e = c.newInstance();// 通过循环给e对象的属性赋值for (int i = 0; i < str.length; i++) {// 使用列名称通过反射对象，获取对象的属性Field field = c.getDeclaredField(fields[i]);// 公开访问权限field.setAccessible(true);// 给属性设置值field.set(e, rs.getObject(str[i]));}}} catch (Exception ex) {ex.printStackTrace();}finally{close(con,ps,rs);}return e;
}

通用的DQL（无条件）

​ 查询不同的表，需要返回不同的model类对象，此时我们可以在形参中传入查询的model类型，然后根据反射创建对象，完成结果集的封装。

public static <E> List<E> dqlList(String sql,Class<E> c,Object...objects){getPS(sql);List<E> list = new ArrayList<>();// 声明返回的值try {for (int i = 0; i < objects.length; i++) {ps.setObject(i+1, objects[i]);}rs = ps.executeQuery();// 首先获取结果集中所有列的集合ResultSetMetaData data = rs.getMetaData();// 然后获得列的数量int count = data.getColumnCount();// 根据列的数量创建一个字符串数组String[] str = new String[count];// 将所有的列名称存入到字符串数组之内for (int i = 0; i < str.length; i++) {str[i] = data.getColumnLabel(i+1);}String[] fields = method(str);// 开始遍历结果集while(rs.next()){// 通过反射，将e实例化成对象E e = c.newInstance();// 通过循环给e对象的属性赋值for (int i = 0; i < str.length; i++) {// 使用列名称通过反射对象，获取对象的属性Field field = c.getDeclaredField(fields[i]);// 公开访问权限field.setAccessible(true);// 给属性设置值field.set(e, rs.getObject(str[i]));}list.add(e);}} catch (Exception ex) {ex.printStackTrace();}finally{close(con,ps,rs);}return list;
}

数据库列名转化成Java属性名

public static String[] method(String[] str){//创建一个数组用来保存model中的属性名长度与数据库列的数组长度一致String[] fields= new String[str.length];for (int i = 0; i < fields.length; i++) {if(str[i].indexOf("_")!=-1){// 代表列名中包含下划线// 由于列名中可能不止一个下划线，所以做了如下处理String[] split = str[i].split("_");StringBuffer sb = new StringBuffer();// 假设列名user_a_b_c_d// 遍历根据下划线分割出来的数组for (int j = 0; j < split.length; j++) {if (j==0) {// 数组中的第一个元素不进行大写转换sb.append(split[j]);}else {// 将字符串转成char数组char[] charArray = split[j].toCharArray();charArray[0] -= 32;// A 65 a 97sb.append(charArray);}}//将StringBuffer转成字符串，然后存入到字符串数组中fields[i]=sb.toString();}else{fields[i]=str[i];}}return fields;
}

总结Util的问题

​ 1：封装结果集的时候，是根据数据库的列名，来获取Java类中的属性，然后对属性赋值，这就引出一个问题，如果数据库的列名和model类的属性名不一致就会造成封装结果集失败

	方案1：sql语句使用别名，让别名与model的属性名一致可以解决（会导致sql语句过长，不考虑）方案2：设计数据库的时候让数据库的列名称与model的属性名称一致（命名规则不符合要求，不考虑）方案3：在查询方法的参数中传入一个map，key和value分别对应分别对应数据库的列名与model的属性名（性能较高的解决方案，mybatis采用的方式，增加了大量的代码）方案4：在util中获取数据库列名称时，判断其是否包含_,如果包含的话就去掉_并将后面的字母转成大写（符合命名规则，并且代码简单简单易懂，对使用者没有影响）

​ 2：由于Integer与int无法通过反射直接获取对象，所有如果是求总数的sql会无法封装结果集

	方案1：判断如果传入的参数类型是int，表示这个sql肯定返回的是一个单行单列的int值，直接取出int值return方案2：单独写一个用于求总数的方法，例如：public static int count(String sql,Object...objects)

​ 3：增加的时候需要从那个对象中一个一个取值，如果对象的属性过多，会非常麻烦，所以尽量改成调用的时候传入一个对象即可，在util中自动从对象里面取值放入到占位符

/**
* 通用的增加，要求如下： 1：有多少个占位符，传入的model中需要有多少个属性有值
* 2：model属性的书写顺序要与数据库列名的顺序一致，书写sql要规范 由于传入的model有可能是任何类型，所以在这里需要使用泛型指定形参
*/
public static <E> void saveModel(String sql, E e) {getPS(sql);try {Field[] fields = e.getClass().getDeclaredFields();int j = 1;for (int i = 0; i < fields.length; i++) {fields[i].setAccessible(true);if (fields[i].get(e) != null) {ps.setObject(j++, fields[i].get(e));}}ps.execute();} catch (Exception e2) {e2.printStackTrace();} finally {close(con, ps);}
}

​ 4：修改的时候需要从那个对象中一个一个取值，如果对象的属性过多，会非常麻烦，所以尽量改成调用的时候传入一个对象即可，在util中自动从对象里面取值放入到占位符

/**
* 根据id通用的修改，要求如下： 1：有多少个占位符，传入的model中需要有多少个属性有值
* 2：model属性的书写顺序要与数据库列名的顺序一致，书写sql要规范 由于传入的model有可能是任何类型，所以在这里需要使用泛型指定形参
*/
public static <E> void updateModel(String sql, E e) {getPS(sql);try {Field[] fields = e.getClass().getDeclaredFields();int j = 1;for (int i = 1; i < fields.length; i++) {fields[i].setAccessible(true);if (fields[i].get(e) != null) {ps.setObject(j++, fields[i].get(e));}}fields[0].setAccessible(true);ps.setObject(j, fields[0].get(e));ps.execute();} catch (Exception e2) {e2.printStackTrace();} finally {close(con, ps);}
}

分页查询

/*** 传入的sql：select*from emp where deptno=? limit ?,?* 转化的sql* 通用分页查询，使用这个方法会返回分页的各种数据*/
public static <E> PageInfo<E> dqlListLimit(String sql,Class<E> c,Object...objects){// 1：将传入的sql语句转化成求总数的sqlsql = sql.toLowerCase();StringBuffer sb = new StringBuffer("select count(*)");sb.append(sql.substring(sql.indexOf("from")));// 2：去掉limit之后的sqlsb.replace(sb.indexOf("limit"), sb.length(), "");//sb.append(sql.substring(sql.indexOf("from"),sql.indexOf("limit")))// 3：去掉参数中的分页参数Object[] of = Arrays.copyOf(objects, objects.length-2);Integer count = dqlOne(sb.toString(), int.class, of);// 4：修改参数执行原本的sqlint pageSize=(int) objects[objects.length-1];int pageNumber=(int)objects[objects.length-2];objects[objects.length-2]=(pageNumber-1)*pageSize;List<E> data = dqlList(sql, c, objects);PageInfo<E>  pageInfo= new PageInfo<>(data, count, ageNumber,pageSize);return pageInfo;
}

/*** 主要记录与分页有关的所有信息即可 1：数据 2：记录总条数 3：每页显示多少条数据 4：当前页码 5：首页 6：尾页 7：上一页 8：下一页*/
// 自动获取setter、getter、ToString、equals、hashCode
@Data
/** @NoArgsConstructor* * @AllArgsConstructor*/
// 给所有不为null的属性生成一个构造方法
// @RequiredArgsConstructor
// @Value
public class PageInfo<E> {/*** 存放数据*//** // 用来指定这个属性不准为NULL，如果是空会抛空指针异常* * @NonNull*/private List<E> data;/*** 记录总数据*//* @NonNull */private int count;/*** 每页显示多少条数据*/private int pageSize;/*** 当前页码*/private int pageNumber;/*** 总页数*/private int countPage;/*** 首页*/private int startPage;/*** 尾页*/private int endPage;/*** 上一页*/private boolean upPage;/*** 下一页*/private boolean nextPage;/*** @param data* @param count* @param pageSize* @param pageNumber*/public PageInfo(List<E> data, int count, int pageNumber, int pageSize) {super();this.data = data;// 数据this.count = count;// 总数this.pageSize = pageSize;// 每页显示多少数据this.pageNumber = pageNumber;// 当前页码this.countPage = count % pageSize == 0 ? count / pageSize : (count / pageSize) + 1;// 求总共有多少页this.startPage = 1;this.endPage = countPage;this.upPage = pageNumber == 1 ? false : true;// 第一页是没有上一页// pageNumber==countPage表示已经是最后一页了，所以没有下一页this.nextPage = pageNumber == countPage ? false : true;}
}