JDBC操作问题
JDBC代码
import java.sql.*;
import java.util.ArrayList;
import java.util.List;public class 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/mybatis?characterEncoding=utf-8", " root", " root");//写sql语句String sql = "select * from user where username = ?";//获取预处理对象preparedStatement = connection.prepareStatement(sql);//设置参数preparedStatement.setString(1, "tom");//执行sql，获取结果集resultSet = preparedStatement.executeQuery();//封装结果集List<User> userList = new ArrayList<>();while (resultSet.next()) {User user = new User();int id = resultSet.getInt("id");String username = resultSet.getString("username");user.setId(id);user.setUsername(username);userList.add(user);}System.out.println(userList);} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException 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问题总结
- 频繁创建、释放数据库连接，造成系统资源浪费
- sql语句在代码中硬编码，不易维护
- 传参存在硬编码，修改sql还要修改代码
- 手动解析结果集
JDBC问题解决思路
- 使用数据库连接池初始化连接资源
- 将sql语句抽取到xml配置文件中
- 使用反射技术，将实体属性与表字段自动映射
自定义框架设计
使用端
提供核心配置文件：
sqlMapConfig.xml:存放数据源信息，引入Mapper.xml的全路径
Mapper.xml:sql语句的配置文件信息
框架端
- 读取配置文件
读取后以流的形式存在于内存中，后创建JavaBean存储
Configuration:存放数据库的基本信息、Map<唯一标识，Mapper>
唯一标识：namespace+“.”+id
MappedStatement:sql语句、statement类型、输入参数类型、输出参数类型
- 解析配置文件
创建SqlSessionFactoryBuilder类
方法：SqlSessionFactory build()
使用dom4j解析配置文件，将解析出来的内容封装到Configuration和MappedStatement
创建SqlSessionFactory的实现类DefaultSqlSessionFactory。
- 创建SqlSessionFactory
方法：openSession():获取sqlSession接口的实现类的实例对象
- 创建sqlSession接口及其实现类
方法：selectOne(String statementId,Object… params)
selectList(String statementId,Object… params)
- 创建Executor接口及其实现类
封装JDBC代码实现CRUD
** 设计模式：建造者模式、工厂模式、代理模式**
自定义框架实现
在使用端创建配置文件
<configuration><dataSource><property name="driverClass" value="com.mysql.jdbc.Driver"></property><property name="jdbcUrl" value="jdbc:mysql:///test"></property><property name="username" value="root"></property><property name="password" value="@wdlm212027"></property></dataSource><mapper resource="UserMapper.xml"></mapper>
</configuration>
<mapper namespace="user"><select id="selectList" resultType="com.wdlm.pojo.User">select * from user</select><select id="selectOne" resultType="com.wdlm.pojo.User" parameterType="com.wdlm.pojo.User">select * from user where id=#{id} and username=#{username}</select>
</mapper>
在使用端创建实体类
public class User {private Integer id;private String username;public User() {}public User(Integer id, String username) {this.id = id;this.username = 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;}
}在框架端导入Maven依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.wdlm.imybatis</groupId><artifactId>Imybatis</artifactId><version>1.0-SNAPSHOT</version><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></project>
在框架端创建配置类
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;public class Configuration {private DataSource dataSource;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;}
}
public class MappedStatement {private String id;private String resultType;private String parameterType;private String sql;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 paramterType) {this.parameterType = paramterType;}public String getSql() {return sql;}public void setSql(String sql) {this.sql = sql;}
}
在框架端创建配置文件读取类
public class Resources {public static InputStream getResourceAsStream(String path) {InputStream resource = Resources.class.getClassLoader().getResourceAsStream(path);return resource;}
}
在框架端创建SqlSessionFactoryBuilder
public class SqlSessionFactoryBuilder {public SqlSessionFactory build(InputStream resource) throws PropertyVetoException, DocumentException {XMLConfigBuilder xmlConfigBuilder = new XMLConfigBuilder();Configuration configuration = xmlConfigBuilder.parseConfig(resource);DefaultSqlSessionFactory defaultSqlSessionFactory = new DefaultSqlSessionFactory(configuration);return defaultSqlSessionFactory;}
}
在框架端创建解析类
public class XMLConfigBuilder {private Configuration configuration;public XMLConfigBuilder() {configuration = new Configuration();}public Configuration parseConfig(InputStream resource) throws DocumentException, PropertyVetoException {Document document = new SAXReader().read(resource);Element rootElement = document.getRootElement();List<Element> list = rootElement.selectNodes("//property");Properties properties = new Properties();for (Element element : list) {String name = element.attributeValue("name");String value = element.attributeValue("value");properties.setProperty(name, value);}ComboPooledDataSource dataSource = new ComboPooledDataSource();dataSource.setDriverClass(properties.getProperty("driverClass"));dataSource.setJdbcUrl(properties.getProperty("jdbcUrl"));dataSource.setUser(properties.getProperty("username"));dataSource.setPassword(properties.getProperty("password"));configuration.setDataSource(dataSource);List<Element> mapperList = rootElement.selectNodes("//mapper");for (Element element : mapperList) {String mapperPath = element.attributeValue("resource");InputStream mapperResource = Resources.getResourceAsStream(mapperPath);XMLMapperBuilder xmlMapperBuilder = new XMLMapperBuilder(configuration);xmlMapperBuilder.parse(mapperResource);}return configuration;}
}public class XMLMapperBuilder {private Configuration configuration;public XMLMapperBuilder(Configuration configuration) {this.configuration = configuration;}public void parse(InputStream resource) throws DocumentException {Document document = new SAXReader().read(resource);Element rootElement = document.getRootElement();List<Element> list = rootElement.selectNodes("//select");String namespace = rootElement.attributeValue("namespace");for (Element element : list) {String id = element.attributeValue("id");String resultType = element.attributeValue("resultType");String parameterType = element.attributeValue("parameterType");String sql = element.getTextTrim();MappedStatement mappedStatement = new MappedStatement();mappedStatement.setId(id);mappedStatement.setParameterType(parameterType);mappedStatement.setResultType(resultType);mappedStatement.setSql(sql);String key = namespace + "." + id;configuration.getMappedStatementMap().put(key, mappedStatement);}}
}
在框架端创建SqlSessionFactory接口及其实现类
public interface SqlSessionFactory {public SqlSession openSession();
}
public class DefaultSqlSessionFactory implements SqlSessionFactory {private Configuration configuration;public DefaultSqlSessionFactory(Configuration configuration) {this.configuration = configuration;}@Overridepublic SqlSession openSession() {return new DefaultSqlSession(configuration);}
}
在框架端创建SqlSession接口及其实现类
public interface SqlSession {public <E> List<E> selectList(String statementId, Object... params);public <T> T selectOne(String statementId, Object... params) throws Exception;}
public class DefaultSqlSession implements SqlSession {private Configuration configuration;public DefaultSqlSession(Configuration configuration) {this.configuration = configuration;}@Overridepublic <E> List<E> selectList(String statementId, Object... params) throws Exception {SimpleExecutor simpleExecutor = new SimpleExecutor();MappedStatement mappedStatement = configuration.getMappedStatementMap().get(statementId);List<Object> list = simpleExecutor.query(configuration, mappedStatement, params);return (List<E>) list;}@Overridepublic <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("查询结果为空或者过多");}}
}在框架端创建Executor接口及其实现类
public interface Executor {public <E>List<E> query(Configuration configuration, MappedStatement mappedStatement,Object... params);
}
public class SimpleExecutor implements Executor {@Overridepublic <E> List<E> query(Configuration configuration, MappedStatement mappedStatement, Object... params) throws Exception {DataSource dataSource = configuration.getDataSource();Connection connection = dataSource.getConnection();String sql = mappedStatement.getSql();BoundSql boundSql = getBoundSql(sql);PreparedStatement preparedStatement = connection.prepareStatement(boundSql.getSql());List<ParameterMapping> parameterMappingList = boundSql.getParameterMappingList();String parameterType = mappedStatement.getParameterType();Class<?> parameterClass = getClassType(parameterType);for (int i = 0; i < parameterMappingList.size(); i++) {ParameterMapping parameterMapping = parameterMappingList.get(i);String content = parameterMapping.getContent();Field declaredField = parameterClass.getDeclaredField(content);declaredField.setAccessible(true);Object o = declaredField.get(params[0]);preparedStatement.setObject(i + 1, o);}ResultSet resultSet = preparedStatement.executeQuery();String resultType = mappedStatement.getResultType();Class<?> resultTypeClass = getClassType(resultType);Object o = resultTypeClass.newInstance();ArrayList<Object> objects = new ArrayList<>();while (resultSet.next()) {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;}private BoundSql getBoundSql(String sql) {ParameterMappingTokenHandler parameterMappingTokenHandler = new ParameterMappingTokenHandler();GenericTokenParser genericTokenParser = new GenericTokenParser("#{", "}", parameterMappingTokenHandler);String parseSql = genericTokenParser.parse(sql);List<ParameterMapping> parameterMappings = parameterMappingTokenHandler.getParameterMappings();BoundSql boundSql = new BoundSql(parseSql, parameterMappings);return boundSql;}private Class<?> getClassType(String parameterType) throws ClassNotFoundException {if (parameterType != null) {return Class.forName(parameterType);}return null;}
}
在框架端创建Sql解析类
public class BoundSql {private String sql;private List<ParameterMapping> parameterMappingList = new ArrayList<>();public BoundSql() {}public BoundSql(String sql, List<ParameterMapping> parameterMappingList) {this.sql = sql;this.parameterMappingList = parameterMappingList;}public String getSql() {return sql;}public void setSql(String sql) {this.sql = sql;}public List<ParameterMapping> getParameterMappingList() {return parameterMappingList;}public void setParameterMappingList(List<ParameterMapping> parameterMappingList) {this.parameterMappingList = parameterMappingList;}
}在框架端创建Sql解析工具类
public interface TokenHandler {String handleToken(String content);
}
public class GenericTokenParser {private final String openToken; //开始标记private final String closeToken; //结束标记private final com.wdlm.utils.TokenHandler handler; //标记处理器public GenericTokenParser(String openToken, String closeToken, com.wdlm.utils.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 com.wdlm.utils.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;}}