分析JDBC操作问题
https://upload-images.jianshu.io/upload_images/19453215-b93db688d71a8474.png
问题总结
- 数据库连接创建、释放频繁造成系统资源浪费，从而影响系统性能。
- Sql语句在代码中硬编码，造成代码不易维护，实际应用中sql变化的可能较大，sql变动需要改变 java代码。
- 使用preparedStatement向占有位符号传参数存在硬编码，因为sql语句的where条件不一定，可能 多也可能少，修改sql还要修改代码，系统不易维护。
- 对结果集解析存在硬编码(查询列名)，sql变化导致解析代码变化，系统不易维护，如果能将数据 库 记录封装成pojo对象解析比较方便
解决方案
- 使用数据库连接池初始化连接资源
- 将数据库连接配置、sql语句抽取到xml配置文件中， 使其满足开闭原则
- 使用反射、内省等底层技术，自动将实体与表进行属性与字段的自动映射
自定义框架设计思路
架构设计图
https://upload-images.jianshu.io/upload_images/19453215-35a00b64f4f80aea.png
设计思路
使用端（提供配置）
- 使用sqlMapperConfig.xml提供全局配置信息（数据源等）
- 使用Mapper.xml提供sql语句文件信息
- 提供SqlSession接口类实现select、insert、update、delete操作
框架端（JDBC封装）
- 读取配置文件
- 读取sqlMapperconfig.xml
- 读取Mapper.xml
- 解析配置文件
- 解析文件生成Configuration、MapperStatement对象
- 执行JDBC流程
自定义框架实现
使用端
创建配置文件
sqlMapperConfig.xml(全局配置文件，包括数据源配置，mapper路径等)
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<!--数据源配置-->
<datasource>
<property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test"/>
<property name="username" value="root"/>
<property name="password" value=""/>
</datasource>
<!--引入mapper.xml文件路径-->
<mapper resource="mapper/userMapper.xml"/>
</configuration>
mapper.xml（namespace命名空间、SQL语句配置等）
<?xml version="1.0" encoding="utf-8" ?>
<mapper namespace="com.gigabytc.dao.UserDao">
<select id="findAll" resultType="com.gigabytc.entity.User" >
select * from user
</select>
<select id="findByCondition" resultType="com.gigabytc.entity.User" parameterType="com.gigabytc.entity.User">
select * from user where id = #{id} and username = #{username}
</select>
<insert id="addUser" parameterType="com.gigabytc.entity.User" resultType="int">
insert into user values(#{id}, #{username}, #{password}, #{birthday})
</insert>
<update id="updateUser" parameterType="com.gigabytc.entity.User" resultType="int" >
update user SET username = #{username}, password=#{password}, birthday=#{birthday} WHERE id = #{id}
</update>
<delete id="deleteUser" parameterType="com.gigabytc.entity.User" resultType="int" >
delete from user where id=#{id}
</delete>
</mapper>
使用sqlSession操作接口（读取配置文件构建SqlSession数据库操作）
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
sqlSession = sqlSessionFactory.openSession();
List<User> list = sqlSession.selectList("user.findAll");
框架端
解析配置文件
Configuration（全局配置）
@Data
public class Configuration {
/**
- 数据源
*/
private DataSource dataSource;
/**
- SQL statement集合
*/
private Map<String, MapperStatement> mapperStatementMap = new HashMap<>();
}
MapperStatement（SQL语句配置）
@Data
public class MapperStatement {
/**
statement-id标识（namespace+id）
*/
private String id;
/**
- 参数类型
*/
private String parameterType;
/**
- 返回类型
*/
private String resultType;
/**
- sql语句
*/
private String sql;
/**
- sql类型
*/
private SqlType sqlType;
}
XmlConfigBuilder（全局配置解析）
public class XMLConfigBuilder {
public Configuration parseConfiguration(InputStream inputStream) throws DocumentException, PropertyVetoException {
Configuration configuration = new Configuration();
//解析数据源配置
Document document = new SAXReader().read(inputStream);
Element rootElement = document.getRootElement();
List<Element> list = rootElement.selectNodes("//datasource/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 = 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文件路径，解析mapper.xml
List<Element> mapperList = rootElement.selectNodes("//mapper");
for (Element element : mapperList) {
String resource = element.attributeValue("resource");
XMLMapperBuilder xmlMapperBuilder = new XMLMapperBuilder(configuration);
xmlMapperBuilder.parseMapper(Resources.getResourceAsStream(resource));
}
return configuration;
}
}
XmlMapperStatementBuilder(SQL语句解析)
public class XMLMapperBuilder {
private Configuration configuration;
public XMLMapperBuilder(Configuration configuration) {
this.configuration = configuration;
}
public void parseMapper(InputStream inputStream) throws DocumentException {
//解析SQL statement并封装到集合
Document document = new SAXReader().read(inputStream);
Element rootElement = document.getRootElement();
//解析sql语句封装MapperStatement
buildStatement(rootElement, new String[]{"select", "update", "insert", "delete"});
}
private void parseStatement(String namespace, Element element) {
String id = element.attributeValue("id");
String parameterType = element.attributeValue("parameterType");
String resultType = element.attributeValue("resultType");
String sql = element.getTextTrim();
String elementName = element.getName();
SqlType sqlType = SqlType.valueOf(elementName.toUpperCase());
MapperStatement mapperStatement = new MapperStatement();
mapperStatement.setId(id);
mapperStatement.setParameterType(parameterType);
mapperStatement.setResultType(resultType);
mapperStatement.setSql(sql);
mapperStatement.setSqlType(sqlType);
String statementId = namespace + "." + id;
configuration.getMapperStatementMap().put(statementId, mapperStatement);
}
private void buildStatement(Element rootElement, String[] nodes) {
String namespace = rootElement.attributeValue("namespace");
for (String node : nodes) {
List<Element> list = rootElement.selectNodes("//" + node);
for (Element element : list) {
parseStatement(namespace, element);
}
}
}
}
SqlSession相关接口实现
SqlSessionFactoryBuilder
public class SqlSessionFactoryBuilder {
public SqlSessionFactory build(InputStream inputStream) throws PropertyVetoException, DocumentException {
XMLConfigBuilder xmlConfigBuilder = new XMLConfigBuilder();
Configuration configuration = xmlConfigBuilder.parseConfiguration(inputStream);
return new DefaultSqlSessionFactory(configuration);
}
}
DefaultSqlSessionFactory
public class DefaultSqlSessionFactory implements SqlSessionFactory{
private final Configuration configuration;
public DefaultSqlSessionFactory(Configuration configuration) {
this.configuration = configuration;
}
@Override
public SqlSession openSession() {
return new DefaultSqlSession(configuration);
}
}
DefaultSqlSession
public class DefaultSqlSession implements SqlSession{
public DefaultSqlSession(Configuration configuration) {
this.configuration = configuration;
}
private Configuration configuration;
@Override
public <E> List<E> selectList(String statementId, Object... params) throws Exception {
Executor selectExecutor = new SelectExecutor();
MapperStatement mapperStatement = configuration.getMapperStatementMap().get(statementId);
return selectExecutor.query(configuration, mapperStatement, params);
}
@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 int insert(String statementId, Object... params) throws Exception {
return this.update(statementId,params);
}
@Override
public int update(String statementId, Object... params) throws Exception {
Executor updateExecutor = new UpdateExecutor();
MapperStatement mapperStatement = configuration.getMapperStatementMap().get(statementId);
return updateExecutor.update(configuration,mapperStatement,params);
}
@Override
public int delete(String statementId, Object... params) throws Exception {
return this.update(statementId,params);
}
@Override
public <T> T getMapper(Class<?> c) {
Object o = Proxy
.newProxyInstance(DefaultSqlSession.class.getClassLoader(), new Class[]{c}, new InvocationHandler() {
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
Class<?> declaringClass = method.getDeclaringClass();
String statementId = declaringClass.getName() + "." + method.getName();
MapperStatement mapperStatement = configuration.getMapperStatementMap().get(statementId);
SqlType sqlType = mapperStatement.getSqlType();
switch (sqlType) {
case SELECT:
Type genericReturnType = method.getGenericReturnType();
// 判断是否进行了 泛型类型参数化
if(genericReturnType instanceof ParameterizedType){
return selectList(statementId, args);
}
return selectOne(statementId,args);
case INSERT:
return insert(statementId,args);
case UPDATE:
return update(statementId,args);
case DELETE:
return delete(statementId,args);
default :
break;
}
return null;
}
});
return (T) o;
}
}
参考代码
本文由博客一文多发平台 OpenWrite 发布！
