1.传统JDBC代码及存在的问题
public class MyJDBC {
private String driver = "com.mysql.cj.jdbc.Driver";
private String url = "jdbc:mysql://localhost:3306/my_database?characterEncoding=utf-8&serverTimezone=UTC";
private String username = "root";
private String password = "root";
public List<User> queryUserList() throws Exception {
List<User> userList = new ArrayList<User>();
Class.forName(driver);
/**
* 问题一：频繁获取/释放数据库连接，影响数据库和应用性能
* 解决：数据库连接池技术，C3P0,DRUID（阿里巴巴荣誉出品，号称前无古人后无来者世界最强没有之一）
*/
Connection connection = DriverManager.getConnection(url, username, password);
/**
* 问题二：sql语句硬编码，后期难以维护
* 解决：若sql语句和java代码分离，比如sql写在配置文件中。Mybatis就是这么干的
*/
String sql = "select * from user";
/**
* 问题三：sql语句where条件和占位符一一对应，后期难以维护
*/
// String sql1 = "select * from user where username=? and id=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// preparedStatement.setInt(1,2);
ResultSet resultSet = preparedStatement.executeQuery();
User user = null;
/**
* 问题四：结果集解析麻烦，查询列硬编码
* 期望：如果单条记录直接返回实体对象，如果多条记录返回实体的集合
*/
while(resultSet.next()) {
user = new User();
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
userList.add(user);
}
resultSet.close();
preparedStatement.close();
connection.close();
return userList;
}
}
分析存在的问题及解决思路：
1.频繁获取/释放数据库连接，影响数据库应用性能---使用数据库连接池技术
2.数据库参数硬编码---使用配置文件的方式
3.SQL语句，设置参数，获取结果集参数均存在硬编码问题 -- 解决：使用配置文件
4..需要手动封装查询结果 -- 解决：使用反射和内省技术，将实体与表属性字段一一对应
2.自定义持久层框架实现思路
1.使用端（项目），引入自定义持久层框架jar包
提供两部分配置信息 1.数据库的配置信息 2.SQL的信息
使用配置文件来提供以上两部分配置文件：
1.sqlMapConfig.xml---存放数据库配置信息
2.mapper.xml---存放SQL信息
2.自定义持久层框架本身
1）加载配置文件
根据配置文件的路径，加载配置文件成字节输入流，存储到内存中
2）创建两个JavaBean，存放配置文件内容
1.Configuration.java --- 核心配置类，存放sqlMapConfig.xml解析出来的内容
2.MappedStatement --- 映射配置类，存放mapper.xml解析出来的内容
3）解析配置文件
利用dom4j技术解析配置文件
4）创建SqlSessionFactory接口及实现类，用于生产SqlSession
5）创建SqlSession接口及实现类，用于对数据库的CRUD操作
定义对数据库的操作：select(),selectOne(),insert(),delete(),update()等
6）创建Executor接口及实现类，对SqlSssion中对JDBC的操作进行再封装
query(Configuration,MappedStatement,Object ...params)
<meta name="source" content="lake">
3.代码：
Resources类：
package com.wy.io;
import java.io.InputStream;
public class Resources {
//根据配置文件的路径，将配置文件加载成字节输入流，存储在内存中
public static InputStream getResourceAsStream(String path){
InputStream resourceAsStream = Resources.class.getResourceAsStream(path);
return resourceAsStream;
}
}
JavaBean类
package com.wy.pojo;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
public class Configuration {
private DataSource dataSource;
Map<String,MappedStatement> mappedStatementMap = new HashMap<>();
public DataSource getDataSource() {
return dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
public Map<String, MappedStatement> getMappedStatementMap() {
return mappedStatementMap;
}
public void setMappedStatementMap(Map<String, MappedStatement> mappedStatementMap) {
this.mappedStatementMap = mappedStatementMap;
}
}
package com.wy.pojo;
import com.wy.config.SqlCommandType;
public class MappedStatement {
//id标识
private String id;
//返回值类型
private String resultType;
//参数值类型
private String parameterType;
//sql语句
private String sql;
//sql类型
private SqlCommandType sqlCommandType;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getResultType() {
return resultType;
}
public void setResultType(String resultType) {
this.resultType = resultType;
}
public String getParameterType() {
return parameterType;
}
public void setParameterType(String parameterType) {
this.parameterType = parameterType;
}
public String getSql() {
return sql;
}
public void setSql(String sql) {
this.sql = sql;
}
public SqlCommandType getSqlCommandType() {
return sqlCommandType;
}
public void setSqlCommandType(SqlCommandType sqlCommandType) {
this.sqlCommandType = sqlCommandType;
}
}
SqlCommandType枚举类
package com.wy.config;
public enum SqlCommandType {
UNKNOWN,
INSERT,
UPDATE,
DELETE,
SELECT,
FLUSH;
private SqlCommandType() {
}
}
SqlSessionFactoryBuilder类
package com.wy.sqlSession;
import com.wy.config.XMLconfigBuilder;
import com.wy.pojo.Configuration;
import org.dom4j.DocumentException;
import java.beans.PropertyVetoException;
import java.io.InputStream;
public class SqlSessionFactoryBuilder {
public SqlSessionFactory build(InputStream in) throws PropertyVetoException, DocumentException {
//第一，使用dom4j解析配置文件，将解析出来的内容封装到Configuration中
XMLconfigBuilder xmLconfigBuilder = new XMLconfigBuilder();
Configuration configuration = xmLconfigBuilder.parseConfig(in);
//第二，创建SqlSessionFactory对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryImpl(configuration);
return sqlSessionFactory;
}
}
解析Bean类
package com.wy.config;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.wy.io.Resources;
import com.wy.pojo.Configuration;
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import java.beans.PropertyVetoException;
import java.io.InputStream;
import java.util.List;
import java.util.Properties;
public class XMLconfigBuilder {
private Configuration configuration;
public XMLconfigBuilder() {
this.configuration = new Configuration();
}
/**
* 使用dom4j配置文件进行解析，封装Configuration
* @param inputStream
* @return
*/
public Configuration parseConfig(InputStream inputStream) throws DocumentException, PropertyVetoException {
//解析SqlMapConfig.xml
Document document = new SAXReader().read(inputStream);
//rootElement即为<configuration>
Element rootElement = document.getRootElement();
List<Element> list = rootElement.selectNodes("//property");
//利用properties存储element内容，即<property>标签下内容
Properties properties = new Properties();
for(Element element : list){
String name = element.attributeValue("name");
String value = element.attributeValue("value");
properties.setProperty(name,value);
}
//创建ComboPooledDataSource连接池
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
comboPooledDataSource.setDriverClass(properties.getProperty("driverClass"));
comboPooledDataSource.setJdbcUrl(properties.getProperty("jdbcUrl"));
comboPooledDataSource.setUser(properties.getProperty("username"));
comboPooledDataSource.setPassword(properties.getProperty("password"));
configuration.setDataSource(comboPooledDataSource);
//解析mapper.xml
List<Element> mapperList = rootElement.selectNodes("//mapper");
for(Element element : mapperList){
String mapperPath = element.attributeValue("resource");
InputStream resourceAsStream = Resources.getResourceAsStream("/"+mapperPath);
XMLMapperBuilder xmlMapperBuilder = new XMLMapperBuilder(configuration);
xmlMapperBuilder.parse(resourceAsStream);
}
return configuration;
}
}
package com.wy.config;
import com.wy.pojo.Configuration;
import com.wy.pojo.MappedStatement;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
public class XMLMapperBuilder {
private Configuration configuration;
public XMLMapperBuilder(Configuration configuration) {
this.configuration = configuration;
}
public void parse(InputStream inputStream) throws DocumentException {
Document document = new SAXReader().read(inputStream);
Element rootElement = document.getRootElement();
String namespace = rootElement.attributeValue("namespace");
List<Element> selects = rootElement.selectNodes("//select");
List<Element> inserts = rootElement.selectNodes("//insert");
List<Element> updates = rootElement.selectNodes("//update");
List<Element> deletes = rootElement.selectNodes("//delete");
generateMappedStatement(namespace,selects,SqlCommandType.SELECT);
generateMappedStatement(namespace,inserts,SqlCommandType.INSERT);
generateMappedStatement(namespace,updates,SqlCommandType.UPDATE);
generateMappedStatement(namespace,deletes,SqlCommandType.DELETE);
}
private void generateMappedStatement(String namespace,List<Element> list,SqlCommandType sqlCommandType){
for(Element element:list){
String id = element.attributeValue("id");
String resultType = element.attributeValue("resultType");
String parameterType = element.attributeValue("parameterType");
String sqlText = element.getTextTrim();
MappedStatement mappedStatement = new MappedStatement();
mappedStatement.setId(id);
mappedStatement.setResultType(resultType);
mappedStatement.setParameterType(parameterType);
mappedStatement.setSql(sqlText);
mappedStatement.setSqlCommandType(sqlCommandType);
String key = namespace+"."+id;
configuration.getMappedStatementMap().put(key,mappedStatement);
}
}
}
SqlSessionFactory接口及实现类
package com.wy.sqlSession;
public interface SqlSessionFactory {
Sqlsession openSession();
}
package com.wy.sqlSession;
import com.wy.pojo.Configuration;
public class SqlSessionFactoryImpl implements SqlSessionFactory {
private Configuration configuration;
public SqlSessionFactoryImpl(Configuration configuration) {
this.configuration = configuration;
}
@Override
public Sqlsession openSession() {
return new SqlSessionImpl(configuration);
}
}
SqlSession接口及实现类
package com.wy.sqlSession;
import java.util.List;
public interface Sqlsession {
//查询所有
<E> List<E> selectList(String statementId,Object... params) throws Exception;
//根据条件查询单个
<T> T selectOne(String statementId,Object... params) throws Exception;
//新增
boolean insert(String statementId,Object... params) throws Exception;
//修改
boolean update(String statementId,Object... params) throws Exception;
//删除
boolean delete(String statementId,Object... params) throws Exception;
//为DAO接口生成代理实现类
<T> T getMapper(Class<?> mapperClass);
}
package com.wy.sqlSession;
import com.wy.pojo.Configuration;
import com.wy.pojo.MappedStatement;
import java.lang.reflect.*;
import java.util.List;
import java.util.Objects;
public class SqlSessionImpl implements Sqlsession {
private Configuration configuration;
public SqlSessionImpl(Configuration configuration) {
this.configuration = configuration;
}
@Override
public <E> List<E> selectList(String statementId, Object... params) throws Exception {
Executor excutor = new ExecutorImpl();
List<Object> list = excutor.query(configuration, configuration.getMappedStatementMap().get(statementId), params);
return (List<E>) list;
}
@Override
public <T> T selectOne(String statementId, Object... params) throws Exception {
List<Object> objects = selectList(statementId,params);
if(objects.size() == 1){
return (T) objects.get(0);
}else{
throw new RuntimeException("查询结果为空或者返回结果过多");
}
}
@Override
public boolean insert(String statementId, Object... params) throws Exception {
if(Objects.isNull(params)){
return false;
}
Executor executor = new ExecutorImpl();
boolean insert = executor.insert(configuration,configuration.getMappedStatementMap().get(statementId),params);
return insert;
}
@Override
public boolean update(String statementId, Object... params) throws Exception {
if(Objects.isNull(params)){
return false;
}
Executor executor = new ExecutorImpl();
boolean update = executor.update(configuration, configuration.getMappedStatementMap().get(statementId), params);
return update;
}
@Override
public boolean delete(String statementId, Object... params) throws Exception {
Executor executor = new ExecutorImpl();
boolean delete = executor.delete(configuration, configuration.getMappedStatementMap().get(statementId), params);
return delete;
}
@Override
public <T> T getMapper(Class<?> mapperClass) {
//使用JDK动态代理来为DAO接口生成代理对象并返回
Object proxyInstance = Proxy.newProxyInstance(SqlSessionImpl.class.getClassLoader(), new Class[]{mapperClass}, new InvocationHandler() {
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
//底层去执行JDBC代码，根据不同情况来调用selectList或者selectOne或insert或update或delete
//准备参数1，statementId = namespace+id,所以这里mapper.xml的namespace要改成接口的全路径，id要改成和dao接口方法名字一样
String className = method.getDeclaringClass().getName();
String methodName = method.getName();
String statementId = className+"."+methodName;
MappedStatement mappedStatement = configuration.getMappedStatementMap().get(statementId);
switch (mappedStatement.getSqlCommandType()){
case SELECT:
//获取被调用方法的返回值类型
Type genericReturnType = method.getGenericReturnType();
//判断是否进行了泛型类型参数化
if(genericReturnType instanceof ParameterizedType){
List<Object> objects = selectList(statementId, args);
return objects;
}
return selectOne(statementId,args);
case INSERT:
return insert(statementId,args);
case UPDATE:
return update(statementId,args);
case DELETE:
return delete(statementId,args);
}
return null;
}
});
return (T) proxyInstance;
}
}
Executor接口及实现类
package com.wy.sqlSession;
import com.wy.pojo.Configuration;
import com.wy.pojo.MappedStatement;
import java.util.List;
public interface Executor {
<E> List<E> query(Configuration configuration, MappedStatement mappedStatement,Object... params) throws Exception;
boolean insert(Configuration configuration, MappedStatement mappedStatement,Object... params) throws Exception;
boolean update(Configuration configuration, MappedStatement mappedStatement,Object... params) throws Exception;
boolean delete(Configuration configuration, MappedStatement mappedStatement,Object... params) throws Exception;
}
package com.wy.sqlSession;
import com.wy.config.BoundSql;
import com.wy.pojo.Configuration;
import com.wy.pojo.MappedStatement;
import com.wy.utils.GenericTokenParser;
import com.wy.utils.ParameterMapping;
import com.wy.utils.ParameterMappingTokenHandler;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
public class ExecutorImpl implements Executor {
@Override
public <E> List<E> query(Configuration configuration, MappedStatement mappedStatement, Object... params) throws Exception {
PreparedStatement preparedStatement = getPreparedStatement(configuration, mappedStatement, params);
//5.执行SQL
ResultSet resultSet = preparedStatement.executeQuery();
//6.封装返回结果集
String resultType = mappedStatement.getResultType();
Class<?> resultTypeClass = getClassType(resultType);
ArrayList<Object> objects = new ArrayList<>();
while(resultSet.next()){
Object o = resultTypeClass.newInstance();
//取出resultSet的元数据
ResultSetMetaData metaData = resultSet.getMetaData();
for(int i=1;i<=metaData.getColumnCount();i++){
//字段名
String columnName = metaData.getColumnName(i);
//字段的值
Object value = resultSet.getObject(columnName);
//反射或者内省，根据数据库表和实体的对应关系，完成封装，这里使用内省
PropertyDescriptor propertyDescriptor = new PropertyDescriptor(columnName, resultTypeClass);
Method writeMethod = propertyDescriptor.getWriteMethod();
writeMethod.invoke(o,value);
}
objects.add(o);
}
return (List<E>) objects;
}
@Override
public boolean insert(Configuration configuration, MappedStatement mappedStatement, Object... params) throws Exception {
PreparedStatement preparedStatement = getPreparedStatement(configuration, mappedStatement, params);
boolean execute = preparedStatement.execute();
return execute;
}
@Override
public boolean update(Configuration configuration, MappedStatement mappedStatement, Object... params) throws Exception {
PreparedStatement preparedStatement = getPreparedStatement(configuration, mappedStatement, params);
int i = preparedStatement.executeUpdate();
return i>0;
}
@Override
public boolean delete(Configuration configuration, MappedStatement mappedStatement, Object... params) throws Exception {
PreparedStatement preparedStatement = getPreparedStatement(configuration, mappedStatement, params);
boolean execute = preparedStatement.execute();
return execute;
}
private Class<?> getClassType(String parameterType) throws ClassNotFoundException {
if(parameterType == null){
return null;
}
Class<?> aClass = Class.forName(parameterType);
return aClass;
}
//对#{}解析，1.将#{}使用?进行代替 2.解析出#{}里面的值进行存储
private BoundSql getBoundSql(String sql){
//标记处理类，配置标记解析器完成对占位符的解析处理工作
ParameterMappingTokenHandler parameterMappingTokenHandler = new ParameterMappingTokenHandler();
GenericTokenParser genericTokenParser = new GenericTokenParser("#{","}",parameterMappingTokenHandler);
//解析出来的Sql
String parseSql = genericTokenParser.parse(sql);
//#{}里面解析出来的参数名称
List<ParameterMapping> parameterMappings = parameterMappingTokenHandler.getParameterMappings();
BoundSql boundSql = new BoundSql(parseSql, parameterMappings);
return boundSql;
}
private PreparedStatement getPreparedStatement(Configuration configuration, MappedStatement mappedStatement, Object... params) throws Exception {
//1.注册驱动获取数据库连接
Connection connection = configuration.getDataSource().getConnection();
//2.1获取sql语句， select * from user where id = #{id} and username = #{username}
String sql = mappedStatement.getSql();
//2.2转换sql语句， select * from user where id = ? and username = ?,转换过程中还需要对#{}中的值解析并存储
BoundSql boundSql = getBoundSql(sql);
//3获取预处理对象prepareStatement
PreparedStatement preparedStatement = connection.prepareStatement(boundSql.getSqlText());
//4.设置参数
//获取参数全路径
String parameterType = mappedStatement.getParameterType();
Class<?> parameterTypeClass = getClassType(parameterType);
List<ParameterMapping> parameterMappingList = boundSql.getParameterMappingList();
setObject(preparedStatement,parameterMappingList,parameterTypeClass,params);
return preparedStatement;
}
private void setObject(PreparedStatement preparedStatement, List<ParameterMapping> parameterMappingList,
Class<?> parameterTypeClass, Object... params) throws Exception {
for (int i = 0; i < parameterMappingList.size(); i++) {
if (params.length > 1) {
// 不用对象设置入参
preparedStatement.setObject(i + 1, params[i]);
} else if (params.length == 1 && params[0].getClass().getClassLoader() == null) {
// 用对象入参，且对象不是实体类
preparedStatement.setObject(i + 1, params[0]);
} else {
// 入参是实体类
ParameterMapping parameterMapping = parameterMappingList.get(i);
String content = parameterMapping.getContent();
//反射
Field declaredField = parameterTypeClass.getDeclaredField(content);
//设置暴力访问
declaredField.setAccessible(true);
Object o = declaredField.get(params[0]);
preparedStatement.setObject(i+1,o);
}
}
}
}
存储转义后SQL类
四个工具类
- GenericTokenParser：定义一个解析器，解析#{}标签
- TokenHandler：标记处理器接口
- ParameterMappingTokenHandler：标记处理器实现，处理#{}标签，获取标签体里面的内容
- ParameterMapping：解析器解析#{}标签内容的存放结果
项目地址：
