Analyze JDBC operation problems
The problem summary
- The frequent creation and release of database connections waste system resources, which affects system performance.
- Sql statements are hardcoded in the code, which makes the code difficult to maintain. Therefore, the Sql may change greatly in actual applications, and the Java code needs to be changed.
- Hard coding exists in transferring parameters to possession bit symbols in preparedStatement, because the WHERE conditions of SQL statements are not necessarily, which may be more or less. Modifying SQL requires modifying codes, making the system difficult to maintain.
- There is hard coding (query column name) for result set parsing, SQL changes lead to parsing code changes, the system is not easy to maintain, if the database records can be encapsulated into POJO object parsing is more convenient
The solution
- Initialize connection resources using a database connection pool
- The database connection configuration and SQL statements are extracted into XML configuration files to meet the open close principle
- Use low-level techniques such as reflection and introspection to automatically map entities to tables for attributes and fields
Custom framework design ideas
Architectural drawing
Design ideas
Usage side (provides configuration)
- Use SQLMapperconfig.xml to provide global configuration information (data source, etc.)
- Use mapper.xml to provide SQL statement file information
- The SqlSession interface class implements select, INSERT, UPDATE, and DELETE operations
Frame-side (JDBC encapsulation)
- Reading configuration Files
- Read sqlMapperconfig. XML
- Read the Mapper XML
- Parsing configuration files
- The file is parsed to generate Configuration and MapperStatement objects
- Executing the JDBC process
Custom framework implementation
Using the
Creating a Configuration File
Sqlmapperconfig. XML (global configuration file, including data source configuration, mapper path, etc.)
<configuration>
<! -- Data source 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 file path -->
<mapper resource="mapper/userMapper.xml"/>
</configuration>
Copy the codeMapper.xml (namespace, SQL statement configuration, etc.)
<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>
Copy the codeUse sqlSession operation interface (read configuration file to build sqlSession database operation)
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
sqlSession = sqlSessionFactory.openSession();
List<User> list = sqlSession.selectList("user.findAll");
Copy the codeFrame side
Parsing configuration files
Configuration (Global Configuration)
@Data
public class Configuration {
/** - Data source */
private DataSource dataSource;
/** - SQL statement */
private Map<String, MapperStatement> mapperStatementMap = new HashMap<>();
}
Copy the codeMapperStatement (SQL Statement configuration)
@Data
public class MapperStatement {
/** statement-id Identifier (namespace+ ID) */
private String id;
/** - Parameter type */
private String parameterType;
/** - Return type */
private String resultType;
/** - SQL statement */
private String sql;
/** - SQL type */
private SqlType sqlType;
}
Copy the codeXmlConfigBuilder (Global configuration parsing)
public class XMLConfigBuilder {
public Configuration parseConfiguration(InputStream inputStream) throws DocumentException, PropertyVetoException {
Configuration configuration = new Configuration();
// Parse data source 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);
}
// Build a connection pool
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);
// Read the mapper. XML file path and parse 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));
}
returnconfiguration; }}Copy the codeXmlMapperStatementBuilder (SQL statement parsing)
public class XMLMapperBuilder {
private Configuration configuration;
public XMLMapperBuilder(Configuration configuration) {
this.configuration = configuration;
}
public void parseMapper(InputStream inputStream) throws DocumentException {
// Parse the SQL Statement and encapsulate it into the collection
Document document = new SAXReader().read(inputStream);
Element rootElement = document.getRootElement();
// Parse SQL statements to encapsulate 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); }}}}Copy the codeSqlSession interface implementation
SqlSessionFactoryBuilder
public class SqlSessionFactoryBuilder {
public SqlSessionFactory build(InputStream inputStream) throws PropertyVetoException, DocumentException {
XMLConfigBuilder xmlConfigBuilder = new XMLConfigBuilder();
Configuration configuration = xmlConfigBuilder.parseConfiguration(inputStream);
return newDefaultSqlSessionFactory(configuration); }}Copy the codeDefaultSqlSessionFactory
public class DefaultSqlSessionFactory implements SqlSessionFactory{
private final Configuration configuration;
public DefaultSqlSessionFactory(Configuration configuration) {
this.configuration = configuration;
}
@Override
public SqlSession openSession(a) {
return newDefaultSqlSession(configuration); }}Copy the codeDefaultSqlSession
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("Too many query results or not present"); }}@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();
// Determine whether generic type parameterization is performed
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; }}Copy the codeReference code
lov3r/lagou_mybatis