JDBC存在的问题分析以及解决方案——自定义持久层框架(Mybatis)

第一部分：JDBC操作存在的问题

1.1问题分析

public static void main(String[] args) { 
    Connection connection = null; 
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        // 加载数据库驱动 
        Class.forName("com.mysql.jdbc.Driver"); 
        // 通过驱动管理类获取数据库链接 
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis? characterEncoding=utf-8", "root", "root");
        // 定义sql语句？表示占位符 
        String sql = "select * from user where username = ?";
        // 获取预处理
        statement preparedStatement = connection.prepareStatement(sql); 
        // 设置参数，第一个参数为sql语句中参数的序号(从1开始)，第二个参数为设置的参数值 
        preparedStatement.setString(1, "tom"); 
        // 向数据库发出sql执行查询，查询出结果集 
        resultSet = preparedStatement.executeQuery(); 
        // 遍历查询结果集 
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username"); 
            // 封装User
            user.setId(id);
            user.setUsername(username); 
        } 
        System.out.println(user); 
    }catch (Exception e) {
        e.printStackTrace(); 
    } finally {
    // 释放资源 
    if (resultSet != null) {
        try {
            resultSet.close(); 
        } catch (SQLException e) {
            e.printStackTrace(); 
        } 
    } if (preparedStatement != null) {
        try {preparedStatement.close();
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
    }if (connection != null) { 
        try {
            connection.close(); 
    } catch (SQLException e) { 
            e.printStackTrace(); 
    }
    }
}
问题点总结：

原始jdbc操作存在如下问题：

1.数据库连接创建、释放频繁，造成系统资源浪费，从而影响系统性能；
2.Sql语句在代码中硬编码，代码不易维护，sql一边动就需要改代码；
3.使用preparedStatement向占位符传参，存在硬编码，因为sql本身的where条件不一定存在；
4.对结果集解析存在硬编码(查询列名)，sql变动解析代码也要变，理想结果是可以将结果封装为pojo对象直接使用；

1.2解决思路

  • 使用数据库连接池代替初始化连接资源；
  • 将sql语句抽取到xml配置文件中；
  • 使用反射、内省等技术，将实体对象和表字段做自动映射

1.3自定义框架技术思路

1.3.1使用端：
提供两部分配置信息：数据库配置信息、sql配置信息(sql语句、参数类型、返回值类型)
使用配置文件存储配置信息：
    (1) sqlMapConfig.xml：存放数据库配置信息，存放mapper.xml的全路径；
    (2) mapper.xml存放sql配置信息；
1.3.2自定义框架端(本质是对jdbc的封装)：
  1. 加载配置文件
根据配置文件的路径，加载配置文件成字节输入流，存储在内存中，
创建Resources类，方法InputSteam getResourceAsSteam(String path)
  1. 创建两个javaBean
用来存放配置文件解析出来的内容
Configuration：核心配置类，存放sqlMapConfig.xml解析出来的内容
MapperStatement：映射配置类，存放mapper.xml解析出来的内容
  1. 解析配置文件：dom4j
创建类：SqlSessionFactoryBuilder 方法：build(InputStrem in)
第一：使用dom4j解析配置文件，将解析出来的内容封装到容器对象中
第二：创建SqlSessionFactory对象，生产sqlSession会话对象(工厂模式)
  1. 创建SqlSessionFactory
方法：openSession() : 获取sqlSession接口的实现类实例对象
  1. 创建sqlSession接口及实现类：主要封装crud方法

1.4 ⾃定义框架实现

在使⽤端项⽬中创建配置配置⽂件
创建 sqlMapConfig.xml

〈configuration〉
 <!--数据库连接信息-->
 <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
 <property name="jdbcUrl" value="jdbc:mysql:///zdy_mybatis"></property>
 <property name="user" value="root"></property>
 <property name="password" value="root"></property>
 <! --引⼊sql配置信息-->
 <mapper resource="mapper.xml"></mapper>
</configuration>

mapper.xml

<mapper namespace="User">
 <select id="selectOne" paramterType="com.lagou.pojo.User"
resultType="com.lagou.pojo.User">
 select * from user where id = #{id} and username =#{username}
 </select>
 
 <select id="selectList" resultType="com.lagou.pojo.User">
 select * from user
 </select>
</mapper>

User实体

public class User {
 //主键标识
 private Integer id;
 //⽤户名
 private String username;
 //get  set
 }

再创建⼀个Maven⼦⼯程并且导⼊需要⽤到的依赖坐标

<properties>
 <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
 <maven.compiler.encoding>UTF-8</maven.compiler.encoding>
 <java.version>1.8</java.version>
 <maven.compiler.source>1.8</maven.compiler.source>
 <maven.compiler.target>1.8</maven.compiler.target>
</properties> <dependencies>
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.17</version>
 </dependency>
 
 <dependency>
 <groupId>c3p0</groupId>
 <artifactId>c3p0</artifactId>
 <version>0.9.1.2</version>
 </dependency>
 
 <dependency>
 <groupId>log4j</groupId>
 <artifactId>log4j</artifactId>
 <version>1.2.12</version>
 </dependency>
 
 <dependency>
 <groupId>junit</groupId>
 <artifactId>junit</artifactId>
 <version>4.10</version>
 </dependency>
 
 <dependency>
 <groupId>dom4j</groupId>
 <artifactId>dom4j</artifactId>
 <version>1.6.1</version>
 </dependency>
 
 <dependency>
 <groupId>jaxen</groupId>
 <artifactId>jaxen</artifactId>
 <version>1.1.6</version>
 </dependency>
</dependencies>

Configuration

public class Configuration {
 //数据源
 private DataSource dataSource;
 //map集合： key:statementId value:MappedStatement
 private Map<String,MappedStatement> mappedStatementMap = new HashMap<String,
MappedStatement>();
 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; }}

MappedStatement

public class MappedStatement {
 //id
 private Integer id;
 //sql语句
 private String sql;
 //输⼊参数
 private Class<?> paramterType;
 //输出参数
 private Class<?> resultType;
 //get  set
 }

Resources

public class Resources {
     public static InputStream getResourceAsSteam(String path){ InputStream
        resourceAsStream = Resources.class.getClassLoader.getResourceAsStream(path);
         return resourceAsStream;
     } 
 }

SqlSessionFactoryBuilder

public class SqlSessionFactoryBuilder {
    private Configuration configuration;
     public SqlSessionFactoryBuilder() {
        this.configuration = new Configuration();
     }
    public SqlSessionFactory build(InputStream inputStream) throws  DocumentException, PropertyVetoException, ClassNotFoundException {
         //1.解析配置⽂件，封装Configuration XMLConfigerBuilder
        xmlConfigerBuilder = new
         XMLConfigerBuilder(configuration);
         Configuration configuration =
         xmlConfigerBuilder.parseConfiguration(inputStream);
         //2.创建 sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new
        DefaultSqlSessionFactory(configuration);
         return sqlSessionFactory;
 }

XMLConfigerBuilder

public class XMLConfigerBuilder {
private Configuration configuration;
 public XMLConfigerBuilder(Configuration configuration) {
 this.configuration = new Configuration();
 }
 public Configuration parseConfiguration(InputStream inputStream) throws
DocumentException, PropertyVetoException, ClassNotFoundException {
 Document document = new SAXReader().read(inputStream);
//<configuation>
 Element rootElement = document.getRootElement();
 List<Element> propertyElements =
rootElement.selectNodes("//property");
 Properties properties = new Properties();
 for (Element propertyElement : propertyElements) {
 String name = propertyElement.attributeValue("name");
 String value = propertyElement.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
 configuration.setDataSource(comboPooledDataSource);
 //mapper 部分
 List<Element> mapperElements = rootElement.selectNodes("//mapper");
 XMLMapperBuilder xmlMapperBuilder = new
XMLMapperBuilder(configuration);
 for (Element mapperElement : mapperElements) {
 String mapperPath = mapperElement.attributeValue("resource");
 InputStream resourceAsSteam =
 Resources.getResourceAsSteam(mapperPath);
 xmlMapperBuilder.parse(resourceAsSteam);
 }
 return configuration; }

XMLMapperBuilder

public class XMLMapperBuilder {
private Configuration configuration;
 public XMLMapperBuilder(Configuration configuration) {
 this.configuration = configuration;
 }
 public void parse(InputStream inputStream) throws DocumentException,
ClassNotFoundException {
 Document document = new SAXReader().read(inputStream);
 Element rootElement = document.getRootElement();
 String namespace = rootElement.attributeValue("namespace");
 List<Element> select = rootElement.selectNodes("select");
 for (Element element : select) { //id的值
 String id = element.attributeValue("id");
 String paramterType = element.attributeValue("paramterType");
 String resultType = element.attributeValue("resultType"); //输⼊参
数class
 Class<?> paramterTypeClass = getClassType(paramterType);
 //返回结果class
 Class<?> resultTypeClass = getClassType(resultType);
 //statementId
 String key = namespace + "." + id;
 //sql语句
 String textTrim = element.getTextTrim();
 //封装 mappedStatement
 MappedStatement mappedStatement = new MappedStatement();
 mappedStatement.setId(id);
 mappedStatement.setParamterType(paramterTypeClass);
 mappedStatement.setResultType(resultTypeClass);
 mappedStatement.setSql(textTrim);
 //填充 configuration
 configuration.getMappedStatementMap().put(key, mappedStatement);
 private Class<?> getClassType (String paramterType) throws
ClassNotFoundException {
 Class<?> aClass = Class.forName(paramterType);
 return aClass;
 }
}

sqlSessionFactory 接⼝及D efaultSqlSessionFactory 实现类

public interface SqlSessionFactory {
 public SqlSession openSession();
}
public class DefaultSqlSessionFactory implements SqlSessionFactory {
 private Configuration configuration;
 public DefaultSqlSessionFactory(Configuration configuration) {
this.configuration = configuration; }
 public SqlSession openSession(){
 return new DefaultSqlSession(configuration);
 } }

sqlSession 接⼝及 DefaultSqlSession 实现类

public interface SqlSession {
 public <E> List<E> selectList(String statementId, Object... param)
Exception;
 public <T> T selectOne(String statementId,Object... params) throws
Exception;
 public void close() throws SQLException; }


public class DefaultSqlSession implements SqlSession {
 private Configuration configuration;
 public DefaultSqlSession(Configuration configuration) {
 this.configuration = configuration;
 //处理器对象
 private Executor simpleExcutor = new SimpleExecutor();
 public <E > List < E > selectList(String statementId, Object...param)
throws Exception {
 MappedStatement mappedStatement =
configuration.getMappedStatementMap().get(statementId);
 List<E> query = simpleExcutor.query(configuration,
mappedStatement, param);
 return query;
 }
 //selectOne 中调⽤ selectList
 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("返回结果过多");
 }
}
 public void close () throws SQLException {
 simpleExcutor.close();
 }
}

Executor

public interface Executor {
 <E> List<E> query(Configuration configuration, MappedStatement
mappedStatement,Object[] param) throws Exception;
 void close() throws SQLException; }

SimpleExecutor

public class SimpleExecutor implements Executor {
 private Connection connection = null;
 public <E> List<E> query(Configuration configuration, MappedStatement
mappedStatement, Object[] param) throws SQLException, NoSuchFieldException,
IllegalAccessException, InstantiationException, IntrospectionException,
InvocationTargetException {
 //获取连接
 connection = configuration.getDataSource().getConnection();
 // select * from user where id = #{id} and username = #{username}
String sql = mappedStatement.getSql();
 //对sql进⾏处理
 BoundSql boundsql = getBoundSql(sql);
 // select * from where id = ? and username = ?
 String finalSql = boundsql.getSqlText();
 //获取传⼊参数类型
 Class<?> paramterType = mappedStatement.getParamterType();
 //获取预编译preparedStatement对象
 PreparedStatement preparedStatement =
connection.prepareStatement(finalSql);
 List<ParameterMapping> parameterMappingList =
boundsql.getParameterMappingList();
 for (int i = 0; i < parameterMappingList.size(); i++) {
 ParameterMapping parameterMapping = parameterMappingList.get(i);
 String name = parameterMapping.getName();
 //反射
 Field declaredField = paramterType.getDeclaredField(name);
 declaredField.setAccessible(true);
 //参数的值
 Object o = declaredField.get(param[0]);
 //给占位符赋值
 preparedStatement.setObject(i + 1, o);
 }
 ResultSet resultSet = preparedStatement.executeQuery();
 Class<?> resultType = mappedStatement.getResultType();
 ArrayList<E> results = new ArrayList<E>();
 while (resultSet.next()) {
 ResultSetMetaData metaData = resultSet.getMetaData();
 (E) resultType.newInstance();
 int columnCount = metaData.getColumnCount();
 for (int i = 1; i <= columnCount; i++) {
 //属性名
 String columnName = metaData.getColumnName(i);
 //属性值
 Object value = resultSet.getObject(columnName);
 //创建属性描述器，为属性⽣成读写⽅法
 PropertyDescriptor propertyDescriptor = new
PropertyDescriptor(columnName, resultType);
 //获取写⽅法
 Method writeMethod = propertyDescriptor.getWriteMethod();
 //向类中写⼊值
 writeMethod.invoke(o, value);
 }
 results.add(o);
 }
 return results;
 }
 @Override
 public void close() throws SQLException {
 connection.close();
 }
 private BoundSql getBoundSql(String sql) {
 //标记处理类：主要是配合通⽤标记解析器GenericTokenParser类完成对配置⽂件等的解
析⼯作，其中TokenHandler主要完成处理
 ParameterMappingTokenHandler parameterMappingTokenHandler = new
ParameterMappingTokenHandler();
 //GenericTokenParser :通⽤的标记解析器，完成了代码⽚段中的占位符的解析，然后再根
据给定的标记处理器(TokenHandler)来进⾏表达式的处理
 //三个参数：分别为openToken (开始标记)、closeToken (结束标记)、handler (标记
处 理器)
 GenericTokenParser genericTokenParser = new GenericTokenParser("# {",
"}", parameterMappingTokenHandler);
 String parse = genericTokenParser.parse(sql);
 List<ParameterMapping> parameterMappings =
parameterMappingTokenHandler.getParameterMappings();
 BoundSql boundSql = new BoundSql(parse, parameterMappings);
 return boundSql;
 }
}

BoundSql

public class BoundSql {
 //解析过后的sql语句
 private String sqlText;
 //解析出来的参数
 private List<ParameterMapping> parameterMappingList = new
ArrayList<ParameterMapping>();
 public BoundSql(String sqlText, List<ParameterMapping>
 parameterMappingList) {
 this.sqlText = sqlText;
 this.parameterMappingList = parameterMappingList;
 }
 public String getSqlText() {
 return sqlText;
 }
 public void setSqlText(String sqlText) {
 this.sqlText = sqlText;
 }
 public List<ParameterMapping> getParameterMappingList() {
 return parameterMappingList;
 }
 public void setParameterMappingList(List<ParameterMapping>
parameterMappingList) {
 this.parameterMappingList = parameterMappingList;
 }
}

1.5 ⾃定义框架优化

通过上述我们的⾃定义框架，我们解决了JDBC操作数据库带来的⼀些问题：例如频繁创建释放数据库连
接，硬编码，⼿动封装返回结果集等问题，但是现在我们继续来分析刚刚完成的⾃定义框架代码，会有问题如下：
dao的实现类中存在重复的代码，整个操作的过程模板重复(创建sqlsession,调⽤sqlsession⽅
法，关闭 sqlsession)
dao的实现类中存在硬编码，调⽤sqlsession的⽅法时，参数statement的id硬编码

解决：使⽤代理模式来创建接⼝的代理对象

@Test
 public void test2() throws Exception {
 InputStream resourceAsSteam = Resources.getResourceAsSteam(path：
"sqlMapConfig.xml")
 SqlSessionFactory build = new
SqlSessionFactoryBuilder().build(resourceAsSteam);
 SqlSession sqlSession = build.openSession();
 User user = new User();
 user.setld(l);
 user.setUsername("tom");
 //代理对象
 UserMapper userMapper = sqlSession.getMappper(UserMapper.class);
 User userl = userMapper.selectOne(user);
 System・out.println(userl);
 }

在sqlSession中添加⽅法

public interface SqlSession {
 public <T> T getMappper(Class<?> mapperClass);

实现类

@Override
 public <T> T getMappper(Class<?> mapperClass) {
 T o = (T) Proxy.newProxyInstance(mapperClass.getClassLoader(), new
Class[] {mapperClass}, new InvocationHandler() {
 @Override
 public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
 // selectOne
 String methodName = method.getName();
 // className:namespace
 String className = method.getDeclaringClass().getName();
 //statementid
 String key = className+"."+methodName;
 MappedStatement mappedStatement =
configuration.getMappedStatementMap().get(key);
 Type genericReturnType = method.getGenericReturnType();
 ArrayList arrayList = new ArrayList<> ();
 //判断是否实现泛型类型参数化
 if(genericReturnType instanceof ParameterizedType){
 return selectList(key,args);
 return selectOne(key,args);
 }
 });
 return o;
 }

日拱一卒，功不唐捐；小伙伴们一起学习起来

源码&资料
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容