1.分析JDBC操作问题
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/gmall?characterEncoding=utf-8", "root", "root"); //定义sql语句,?表示占位符 String sql = "select * from user where id = ? and username = ?"; //获取预处理statement preparedStatement = connection.prepareStatement(sql); //设置参数 preparedStatement.setInt(1,1); preparedStatement.setString(2,"张三"); //执行sql resultSet = preparedStatement.executeQuery(); //遍历结果集 while (resultSet.next()) { int id = resultSet.getInt("id"); String username = resultSet.getString("username"); User user = new User(); user.setId(id); user.setUsername(username); System.out.println(user); } } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } finally { // 释放资源 if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } try { preparedStatement.close(); } catch (SQLException e) { e.printStackTrace(); } try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
原始JDBC问题总结:
- 数据库连接创建、释放频繁造成系统资源浪费,从而影响系统性能;
- sql语句在代码中硬编码,不宜维护;
- preparedStatement向占位符传参数存在硬编码;
- 对结果集解析存在硬编码(查询列名),sql变化导致解析代码变化;
2、问题解决思路
- 使用数据库连接池初始化连接资源;
- 将sql语句抽取到xml配置文件中;
- 使用反射、内省等底层技术,自动将实体与表进行属性与字段的自动映射;
3、自定义框架设计
设计思路:
使用端(项目):引用自定义持久层框架的jar包
提供两部分配置信息:数据库配置信息、sql配置信息(sql语句、参数类型、返回值类型)
使用配置文件来提供这两部分配置信息:
(1)sqlMapConfig.xml:存放数据库配置信息,存放mapper.xml的全路径;
(2)mapper.xml:存放sql配置信息
自定义持久层框架本身(工程):本职就是对JDBC代码进行封装
(1)加载配置文件:根据配置文件的路径,加载配置文件成字节输入流,存储在内存中
创建Resources类 方法:InputStream getResourceAsStream(String path)
(2)创建两个JavaBean(容器对象):存放就是对配置文件解析出来的内容
Configuration:核心配置类,存放sqlMapConfig.xml解析出来的内容
MappedStatement:映射配置类,存放mapper.xml解析出来的内容
(3)解析配置文件:dom4j
创建sqlSessionFactoryBuilder类:方法:sqlSessionFactory build():
第⼀:使⽤dom4j解析配置⽂件,将解析出来的内容封装到Configuration和MappedStatement中
第⼆:创建SqlSessionFactory对象;生产sqlSession:会话对象(工厂模式)
(4)创建SqlSessionFactory接口及实现类DefaultSqlSessionFacotry
方法:openSession() : 获取sqlSession接口的实现类实例对象
(5)创建sqlSession接口及实现类DefaultSqlSession
主要封装crud方法:selectList()、selectOne()、update()、delete()
(6)创建Executor接口及实现类SimpleExecutor实现类
query(Configuration,MappedStatement,Object... params);执行JDBC代码
涉及到的设计模式:
Builder构建者模式、工厂模式、代理模式
4、自定义框架实现
pom.xml
<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>
sqlMapConfig.xml
<configuration> <!--数据库连接信息--> <dataSource> <property name="driverClass" value="com.mysql.jdbc.Driver"></property> <property name="jdbcUrl" value="jdbc:mysql://xxxxxx:3306/gmall?characterEncoding=utf-8"></property> <property name="username" value="root"></property> <property name="password" value="root"></property> </dataSource> <!--引⼊sql配置信息--> <mapper resource="UserMapper.xml"></mapper> </configuration>
UserMapper.xml
<mapper namespace="com.lagou.dao.UserDao"> <select id="queryByCondition" paramterType="com.lagou.pojo.User" resultType="com.lagou.pojo.User"> select * from user where id = #{id} </select> <select id="findAll" resultType="com.lagou.pojo.User"> select * from user </select> </mapper>
User.javapublic class User { private Integer id; private String username; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + '}'; } }
Configuration.javapublic class Configuration { //数据源 private DataSource dataSource; //map集合 <statementId,MappedStatement> private 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; } }
MappedStatement.javapublic class MappedStatement { //id private String id; //sql private String sql; //输入参数 private String paramterType; //输出参数 private String resultType; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getSql() { return sql; } public void setSql(String sql) { this.sql = sql; } public String getParamterType() { return paramterType; } public void setParamterType(String paramterType) { this.paramterType = paramterType; } public String getResultType() { return resultType; } public void setResultType(String resultType) { this.resultType = resultType; } }
Resources.javapublic class Resources { public static InputStream getResourceAsStream(String path){ InputStream resourceAsStream = Resources.class.getClassLoader().getResourceAsStream(path); return resourceAsStream; } }
SqlSessionFactoryBuilder.javapublic class SqlSessionFactoryBuilder { private Configuration configuration; public SqlSessionFactoryBuilder(){ this.configuration = new Configuration(); } public SqlSessionFactory build(InputStream inputStream) throws PropertyVetoException, DocumentException { //1.解析配置文件,封装Configuration XMLConfigerBuilder xmlConfigerBuilder = new XMLConfigerBuilder(configuration); Configuration configuration = xmlConfigerBuilder.parseConfiguration(inputStream); //2.创建sqlSessionFactory SqlSessionFactory sqlSessionFactory = new DefaultSqlSessionFactory(configuration); return sqlSessionFactory; } }
XMLConfigerBuilder.javapublic class XMLConfigerBuilder { private Configuration configuration; public XMLConfigerBuilder(Configuration configuration) { this.configuration = configuration; } public Configuration parseConfiguration(InputStream inputStream) throws DocumentException, PropertyVetoException { Document document = new SAXReader().read(inputStream); //Configuration //获取根节点 Element rootElement = document.getRootElement(); //property节点 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); XMLMapperBuilder xmlMapperBuilder = new XMLMapperBuilder(configuration); //解析mapper部分 List<Element> mapperElements = rootElement.selectNodes("//mapper"); for (Element mapperElement : mapperElements) { String mapperPath = mapperElement.attributeValue("resource"); InputStream resourceAsStream = Resources.getResourceAsStream(mapperPath); xmlMapperBuilder.parse(resourceAsStream); } return configuration; } }
XMLMapperBuilder.javapublic class XMLMapperBuilder { private Configuration configuration; public XMLMapperBuilder(Configuration configuration) { this.configuration = configuration; } public void parse(InputStream resourceAsStream) throws DocumentException { Document document = new SAXReader().read(resourceAsStream); //Configuration //获取根节点 Element rootElement = document.getRootElement(); //获取namespace String namespace = rootElement.attributeValue("namespace"); //property节点 List<Element> selectNodes = rootElement.selectNodes("//select"); for (Element element : selectNodes) { String id = element.attributeValue("id"); String paramterType = element.attributeValue("paramterType"); String resultType = element.attributeValue("resultType"); //输⼊参数 String sqlText = element.getTextTrim(); MappedStatement mappedStatement = new MappedStatement(); //statementId String statementId = namespace+"."+id; mappedStatement.setId(id); mappedStatement.setSql(sqlText); mappedStatement.setParamterType(paramterType); mappedStatement.setResultType(resultType); configuration.getMappedStatementMap().put(statementId,mappedStatement); } } }
SqlSessionFactory.java和实现类DefaultSqlSessionFactory.javapublic interface SqlSessionFactory { public SqlSession openSession(); } public class DefaultSqlSessionFactory implements SqlSessionFactory { private Configuration configuration; public DefaultSqlSessionFactory(Configuration configuration) { this.configuration = configuration; } @Override public SqlSession openSession() { return new DefaultSqlSession(configuration); } }
SqlSession.java和实现类DefaultSqlSession.javapublic interface SqlSession { public <E> List<E> selectList(String statementId, Object... param) throws Exception; public <T> T selectOne(String statementId,Object... param) throws Exception; public void close() throws SQLException; } public class DefaultSqlSession implements SqlSession { private Configuration configuration; //处理器对象 private SimpleExecutor simpleExecutor = new SimpleExecutor(); public DefaultSqlSession(Configuration configuration) { this.configuration = configuration; } @Override public <E> List<E> selectList(String statementId, Object... params) throws Exception { MappedStatement mappedStatement = configuration.getMappedStatementMap().get(statementId); List<Object> query = simpleExecutor.query(configuration, mappedStatement, params); return (List<E>) query; } @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 void close() throws SQLException { simpleExecutor.close(); } }
Executor.java和实现类SimpleExecutor.javapublic interface Executor { <E> List<E> query(Configuration configuration, MappedStatement mappedStatement, Object[] param) throws Exception; void close() throws SQLException; } public class SimpleExecutor implements Executor { private Connection connection = null; @Override public <E> List<E> query(Configuration configuration, MappedStatement mappedStatement, Object[] params) throws Exception { //获取连接 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 sqlText = boundSql.getSqlText(); PreparedStatement preparedStatement = connection.prepareStatement(sqlText); //获取传入参数类型 String paramterType = mappedStatement.getParamterType(); Class<?> paramtertypeClass = getClassType(paramterType); List<ParameterMapping> parameterMappingList = boundSql.getParameterMappings(); for (int i = 0; i < parameterMappingList.size(); i++) { ParameterMapping parameterMapping = parameterMappingList.get(i); String content = parameterMapping.getContent(); //反射 Field declaredField = paramtertypeClass.getDeclaredField(content); //暴力访问 declaredField.setAccessible(true); Object o = declaredField.get(params[0]); preparedStatement.setObject(i+1,o); } //执行sql ResultSet resultSet = preparedStatement.executeQuery(); String resultType = mappedStatement.getResultType(); Class<?> resultTypeClass = getClassType(resultType); ArrayList<Object> objects = new ArrayList<>(); while (resultSet.next()){ Object o =resultTypeClass.newInstance(); //元数据 ResultSetMetaData metaData = resultSet.getMetaData(); 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, resultTypeClass); Method writeMethod = propertyDescriptor.getWriteMethod(); writeMethod.invoke(o,value); } objects.add(o); } return (List<E>) objects; } /** * 获取类类型 * @param paramterType * @return * @throws ClassNotFoundException */ private Class<?> getClassType(String paramterType) throws ClassNotFoundException { if(paramterType !=null){ Class<?> aClass = Class.forName(paramterType); return aClass; } return null; } private BoundSql getBoundSql(String sql) { //标记处理类:主要是配合通用标记解析器完成对配置文件的解析 ParameterMappingTokenHandler parameterMappingTokenHandler = new ParameterMappingTokenHandler(); //通用标记解析器:通⽤的标记解析器,完成了代码⽚段中的占位符的解析,然后再根给定的标记处理器(TokenHandler)来进⾏表达式的处理 GenericTokenParser genericTokenParser = new GenericTokenParser("#{", "}", parameterMappingTokenHandler); String parse = genericTokenParser.parse(sql); List<ParameterMapping> parameterMappings = parameterMappingTokenHandler.getParameterMappings(); BoundSql boundSql = new BoundSql(parse, parameterMappings); return boundSql; } public void close() throws SQLException { connection.close(); } }
BoundSql.javapublic class BoundSql { //解析过后的sql语句 private String sqlText; //解析出来的参数 List<ParameterMapping> parameterMappings; public BoundSql(String sqlText, List<ParameterMapping> parameterMappings) { this.sqlText = sqlText; this.parameterMappings = parameterMappings; } public String getSqlText() { return sqlText; } public void setSqlText(String sqlText) { this.sqlText = sqlText; } public List<ParameterMapping> getParameterMappings() { return parameterMappings; } public void setParameterMappings(List<ParameterMapping> parameterMappings) { this.parameterMappings = parameterMappings; } }
相关解析工具类,复制直接使用
public class GenericTokenParser { private final String openToken; //开始标记 private final String closeToken; //结束标记 private final TokenHandler handler; //标记处理器 public GenericTokenParser(String openToken, String closeToken, TokenHandler handler) { this.openToken = openToken; this.closeToken = closeToken; this.handler = handler; } /** * 解析${}和#{} * @param text * @return * 该方法主要实现了配置文件、脚本等片段中占位符的解析、处理工作,并返回最终需要的数据。 * 其中,解析工作由该方法完成,处理工作是由处理器handler的handleToken()方法来实现 */ public String parse(String text) { // 验证参数问题,如果是null,就返回空字符串。 if (text == null || text.isEmpty()) { return ""; } // 下面继续验证是否包含开始标签,如果不包含,默认不是占位符,直接原样返回即可,否则继续执行。 int start = text.indexOf(openToken, 0); if (start == -1) { return text; } // 把text转成字符数组src,并且定义默认偏移量offset=0、存储最终需要返回字符串的变量builder, // text变量中占位符对应的变量名expression。判断start是否大于-1(即text中是否存在openToken),如果存在就执行下面代码 char[] src = text.toCharArray(); int offset = 0; final StringBuilder builder = new StringBuilder(); StringBuilder expression = null; while (start > -1) { // 判断如果开始标记前如果有转义字符,就不作为openToken进行处理,否则继续处理 if (start > 0 && src[start - 1] == '\\') { builder.append(src, offset, start - offset - 1).append(openToken); offset = start + openToken.length(); } else { //重置expression变量,避免空指针或者老数据干扰。 if (expression == null) { expression = new StringBuilder(); } else { expression.setLength(0); } builder.append(src, offset, start - offset); offset = start + openToken.length(); int end = text.indexOf(closeToken, offset); while (end > -1) {存在结束标记时 if (end > offset && src[end - 1] == '\\') {//如果结束标记前面有转义字符时 // this close token is escaped. remove the backslash and continue. expression.append(src, offset, end - offset - 1).append(closeToken); offset = end + closeToken.length(); end = text.indexOf(closeToken, offset); } else {//不存在转义字符,即需要作为参数进行处理 expression.append(src, offset, end - offset); offset = end + closeToken.length(); break; } } if (end == -1) { // close token was not found. builder.append(src, start, src.length - start); offset = src.length; } else { //首先根据参数的key(即expression)进行参数处理,返回?作为占位符 builder.append(handler.handleToken(expression.toString())); offset = end + closeToken.length(); } } start = text.indexOf(openToken, offset); } if (offset < src.length) { builder.append(src, offset, src.length - offset); } return builder.toString(); } } public class ParameterMapping { private String content; public ParameterMapping(String content) { this.content = content; } public String getContent() { return content; } public void setContent(String content) { this.content = content; } } public class ParameterMappingTokenHandler implements TokenHandler { private List<ParameterMapping> parameterMappings = new ArrayList<ParameterMapping>(); // context是参数名称 #{id} #{username} public String handleToken(String content) { parameterMappings.add(buildParameterMapping(content)); return "?"; } private ParameterMapping buildParameterMapping(String content) { ParameterMapping parameterMapping = new ParameterMapping(content); return parameterMapping; } public List<ParameterMapping> getParameterMappings() { return parameterMappings; } public void setParameterMappings(List<ParameterMapping> parameterMappings) { this.parameterMappings = parameterMappings; } } public interface TokenHandler { String handleToken(String content); }
Test.java
@Test public void test() throws Exception { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = build.openSession(); User user = new User(); user.setId(1); user.setUsername("lisi"); User o = sqlSession.selectOne("user.selectOne",user); List<User> objects = sqlSession.selectList("user.selectList"); for (User object : objects) { System.out.println(object); } }
5、自定义框架优化
通过上述我们的自定义框架,我们解决了JDBC操作数据库带来的⼀些问题:例如频繁创建释放数据库连接,硬编码,手动封装返回结果集等问题,但是现在我们继续来分析刚刚完成的自定义框架代码,有没有什么问题?
问题如下:
- dao的实现类中存在重复的代码,整个操作的过程模板重复(创建sqlsession,调⽤sqlsession法,关闭 sqlsession)
- dao的实现类中存在硬编码,调用sqlsession的⽅法时,参数statement的id硬编码
解决:实用代理模式来创建接口的代理对象
Test.java
@Test public void test2() throws PropertyVetoException, DocumentException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSession sqlSession = new SqlSessionFactoryBuilder().build(resourceAsStream).openSession(); UserDao mapper = sqlSession.getMapper(UserDao.class); List<User> all = mapper.findAll(); User user = new User(); user.setId(1); User user1 = mapper.queryByCondition(user); System.out.println(user1); for (User object : all) { System.out.println(object); } }
在SqlSession.java中新增方法
public interface SqlSession { public <T> T getMappper(Class<?> mapperClass); }
实现类DefaultSqlSession.java中新增实现
@Override public <T> T getMapper(Class<?> mapperClass) { Object o = Proxy.newProxyInstance(mapperClass.getClassLoader(), new Class[]{mapperClass}, new InvocationHandler() { @Override public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { //方法名 String methodName = method.getName(); String className = method.getDeclaringClass().getName(); //statementid String key = className+"."+methodName; //获取被调用对象返回值类型 Type genericReturnType = method.getGenericReturnType(); if(genericReturnType instanceof ParameterizedType){ List<Object> objects = selectList(key, args); return objects; } return selectOne(key,args); } }); return (T) o; }