Analyze JDBC operation problems

public static void main(String[] args) throws Exception {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        // Load the database driver
        Class.forName("com.mysql.jdbc.Driver");
        // Get the database link from the driver management class
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis? characterEncoding=utf-8"."root"."root");
        // Define SQL statements? Represents the placeholder S
        String sql = "select * from user where username = ?";
        // Get preprocessing
        preparedStatement = connection.prepareStatement(sql);
        The first parameter is the sequence number (starting from 1) of the parameter in the SQL statement, and the second parameter is the value of the parameter
        preparedStatement.setString(1."tom");
        // Issue SQL to the database to execute the query and query the result set
        resultSet = preparedStatement.executeQuery();
        User user = new User();
        // Iterate over the query result set
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            / / packaging
            user.setId(id);
            user.setUsername(username);
        }
        System.out.println(user);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // Release resources
        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(); }}}}Copy the code

Summary of JDBC issues:

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 as POJO object parsing is more convenient.

Problem Solution

① Initialize connection resources using the database connection pool
② Extract SQL statements into XML configuration files
③ Use reflection, introspection and other underlying technologies to automatically map attributes and fields between entities and tables

Custom framework design

Using the

Provide core configuration files:

Sqlmapconfig. XML: stores data source information and introduces mapper. XML
Mapper. XML: configuration file information of the SQL statement

Frame side

1. Read the configuration file

After reading, it exists in the form of a stream. We cannot store the configuration information read in the form of a stream in the memory. It is not easy to operate

Create javabeans for storage

(1) Configuration: Stores the basic database information and the unique identifier of Map and Mapper: namespace + “.” + ID
(2) MappedStatement: SQL statement, Statement type, input parameter Java type, output parameter Java type

2. Parse the configuration file

SqlSessionFactory Build () :

First: use DOM4J to parse the Configuration file and encapsulate the parsed content in Configuration and MappedStatement
Create an implementation class DefaultSqlSession for SqlSessionFactory

Create SqlSessionFactory

Method: openSession() : Obtain the implementation class instance object of the sqlSession interface

4. Create sqlSession interface and implementation class: encapsulates CRUD methods

SelectList (String statementId,Object Param) : Query all
SelectOne (String statementId,Object Param) : Queries a single Object

Specific implementation: encapsulation JDBC to complete the database table query operation.

Design patterns involved: Builder design pattern, factory pattern, agent pattern

Custom framework implementation

Using the

Create a configuration profile in the user side project

Create sqlmapconfig.xml as follows:

<configuration>

        <! -- Database configuration information -->
    <dataSource>
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql:///zdy_mybatis"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
    </dataSource>

    <! Where mapper.xml is stored -->
    <mapper resource="UserMapper.xml"></mapper>

</configuration>
Copy the code

Mapper. XML is as follows:

<mapper namespace="com.lagou.dao.IUserDao">

    <! StatementId --> statementId--> statementId
    <select id="findAll" resultType="com.lagou.pojo.User" >
        select * from user
    </select>

    <! -- User user = new User() user.setId(1); user.setUsername("zhangsan") -->
    <select id="findByCondition" resultType="com.lagou.pojo.User" paramterType="com.lagou.pojo.User">
        select * from user where id = #{id} and username = #{username}
    </select>
</mapper>
Copy the code

The User entity is as follows:

public class User {

    private Integer id;
    private String username;


    public Integer getId(a) {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername(a) {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    @Override
    public String toString(a) {
        return "User{" +
                "id=" + id +
                ", username='" + username + ' ''+'}'; }}Copy the code

Frame side

Create a Maven subproject and import the required dependency coordinates.


      
<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.lagou</groupId>
    <artifactId>IPersistence</artifactId>
    <version>1.0 the 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>
Copy the code

The Configuration class encapsulates the information of the sqlmapconfig. XML and mapper. XML Configuration files

/** * Configuration: stores the contents parsed from the sqlmapconfig. XML Configuration file * MappedStatement: stores the contents parsed from the mapper. XML Configuration file */
public class Configuration {

    private DataSource dataSource;

    /** * key: Statementid value: encapsulates the mappedStatement object * the unique identifier of the SQL: namesapec. id: Statementid * namesapce is used to distinguish usermapper. XML from productmapper. XML * ID is the id of each mapper. XML  */
    Map<String, MappedStatement> mappedStatementMap = new HashMap<>();

    public DataSource getDataSource(a) {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Map<String, MappedStatement> getMappedStatementMap(a) {
        return mappedStatementMap;
    }

    public void setMappedStatementMap(Map<String, MappedStatement> mappedStatementMap) {
        this.mappedStatementMap = mappedStatementMap; }}Copy the code

MappedStatement class: stores the contents parsed from the mapper. XML configuration file

/** * The mapper. XML configuration file parses the contents * equivalent to a  tag */
public class MappedStatement {

    
    private String id;
    // Return value type
    private String resultType;
    // Parameter value type
    private String paramterType;
    / / SQL statements
    private String sql;

    public String getId(a) {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getResultType(a) {
        return resultType;
    }

    public void setResultType(String resultType) {
        this.resultType = resultType;
    }

    public String getParamterType(a) {
        return paramterType;
    }

    public void setParamterType(String paramterType) {
        this.paramterType = paramterType;
    }

    public String getSql(a) {
        return sql;
    }

    public void setSql(String sql) {
        this.sql = sql; }}Copy the code

Resources class: Loads the configuration file as a byte input stream based on its path and stores it in memory

public class Resources {

    // Based on the path of the configuration file, load the configuration file into a byte input stream and store it in memory
    public static InputStream getResourceAsSteam(String path){
        InputStream resourceAsStream = Resources.class.getClassLoader().getResourceAsStream(path);
        returnresourceAsStream; }}Copy the code

The SqlSessionFactoryBuilder is class:

Use DOM4J to parse the Configuration file and encapsulate the parsed content in Configuration
Create sqlSessionFactory object: Factory class: produce sqlSession: session object

public class SqlSessionFactoryBuilder {

    public SqlSessionFactory build(InputStream in) throws DocumentException, PropertyVetoException {
        // First: use dom4j to parse the Configuration file and encapsulate the parsed content in Configuration
        XMLConfigBuilder xmlConfigBuilder = new XMLConfigBuilder();
        Configuration configuration = xmlConfigBuilder.parseConfig(in);
        // Create sqlSessionFactory: factory class: produce sqlSession: session object
        DefaultSqlSessionFactory defaultSqlSessionFactory = new DefaultSqlSessionFactory(configuration);
        returndefaultSqlSessionFactory; }}Copy the code

XMLConfigBuilder class: Uses dom4J to parse the core Configuration file SQLmapconfig. XML and encapsulate the Configuration

public class XMLConfigBuilder {

    private Configuration configuration;

    public XMLConfigBuilder(a) {
        this.configuration = new Configuration();
    }

    /** * This method uses dom4j to parse the Configuration file and encapsulate the Configuration */
    public Configuration parseConfig(InputStream inputStream) throws DocumentException, PropertyVetoException {

        Document document = new SAXReader().read(inputStream);
        // Get the object, which is the 
      
        tag
      
        Element rootElement = document.getRootElement();
        // //property is an xpath, indicating that 3 searches for all 
      
        tag elements in the corresponding root node
      
        List<Element> list = rootElement.selectNodes("//property");
        Properties properties = new Properties();
        for (Element element : list) {
            // Fetch the value of the name attribute
            String name = element.attributeValue("name");
            // Fetch the value of the value attribute
            String value = element.attributeValue("value");
            properties.setProperty(name,value);
        }

        // assemble DataSource DataSource information
        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 parsing: take the path - byte input stream -dom4j parsing
        List<Element> mapperList = rootElement.selectNodes("//mapper");

        for (Element element : mapperList) {
            String mapperPath = element.attributeValue("resource");
            InputStream resourceAsSteam = Resources.getResourceAsSteam(mapperPath);
            XMLMapperBuilder xmlMapperBuilder = new XMLMapperBuilder(configuration);
            xmlMapperBuilder.parse(resourceAsSteam);
        }
        returnconfiguration; }}Copy the code

XMLMapperBuilder class: Use dom4J to parse and encapsulate the contents of the mapper.xml configuration file

public class XMLMapperBuilder {

    private Configuration configuration;

    public XMLMapperBuilder(Configuration configuration) {
        this.configuration =configuration;
    }

    public void parse(InputStream inputStream) throws DocumentException {
        Document document = new SAXReader().read(inputStream);
        Element rootElement = document.getRootElement();
        String namespace = rootElement.attributeValue("namespace");
        // Corresponds to the 
        List<Element> list = rootElement.selectNodes("//select");
        for (Element element : list) {
            String id = element.attributeValue("id");
            String resultType = element.attributeValue("resultType");
            String paramterType = element.attributeValue("paramterType");
            String sqlText = element.getTextTrim();
            MappedStatement mappedStatement = new MappedStatement();
            mappedStatement.setId(id);
            mappedStatement.setResultType(resultType);
            mappedStatement.setParamterType(paramterType);
            mappedStatement.setSql(sqlText);
            / / is statementid
            String key = namespace+"."+id; configuration.getMappedStatementMap().put(key,mappedStatement); }}}Copy the code

SqlSessionFactory interface and DefaultSqlSessionFactory implementation classes: used to create SqlSession sessions

public interface SqlSessionFactory {

    public SqlSession openSession(a);
}
Copy the code

public class DefaultSqlSessionFactory implements SqlSessionFactory {

    private Configuration configuration;

    public DefaultSqlSessionFactory(Configuration configuration) {
        this.configuration = configuration;
    }
    
    @Override
    public SqlSession openSession(a) {
        return newDefaultSqlSession(configuration); }}Copy the code

SqlSession interface and DefaultSqlSession implementation class: session, used to operate the database

public interface SqlSession {

    // Query all
    public <E> List<E> selectList(String statementid,Object... params) throws Exception;

    // Query a single item based on the condition
    public <T> T selectOne(String statementid,Object... params) throws Exception;
    
    // Generate the proxy implementation class for the Dao interface
    public <T> T getMapper(Class
        mapperClass);
    
}
Copy the code

public class DefaultSqlSession implements SqlSession {

    private Configuration configuration;

    public DefaultSqlSession(Configuration configuration) {
        this.configuration = configuration;
    }

    @Override
    public <E> List<E> selectList(String statementid, Object... params) throws Exception {
        // I'm going to call the Query method in simpleExecutor
        simpleExecutor simpleExecutor = new simpleExecutor();
        MappedStatement mappedStatement = configuration.getMappedStatementMap().get(statementid);
        List<Object> list = simpleExecutor.query(configuration, mappedStatement, params);

        return (List<E>) list;
    }

    @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("Query result is empty or too many results are returned"); }}}Copy the code

Executor Executor: Used to actually execute SQL statements.

public interface Executor {

    public <E> List<E> query(Configuration configuration,MappedStatement mappedStatement,Object... params) throws Exception;

}
Copy the code

public class simpleExecutor implements Executor {
    
    @Override                                                                                //user
    public <E> List<E> query(Configuration configuration, MappedStatement mappedStatement, Object... params) throws Exception {
        // 1. Register the driver and obtain the connection
        Connection connection = configuration.getDataSource().getConnection();

        Select * from user where id = #{id} and username = #{username}
        Select * from user where id =? and username = ? The value in #{} needs to be parsed as well
        String sql = mappedStatement.getSql();
        BoundSql boundSql = getBoundSql(sql);

        // 3. Get the prepared object: preparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement(boundSql.getSqlText());

        // 4. Set parameters
        // Get the full path of the parameterString paramterType = mappedStatement.getParamterType(); Class<? > paramtertypeClass = getClassType(paramterType);// Get the set of attribute names in #{}
        List<ParameterMapping> parameterMappingList = boundSql.getParameterMappingList();
        for (int i = 0; i < parameterMappingList.size(); i++) {
            ParameterMapping parameterMapping = parameterMappingList.get(i);
            // the id in #{id}
            String content = parameterMapping.getContent();
            // reflection gets the property object
            Field declaredField = paramtertypeClass.getDeclaredField(content);
            // Violent access prevention property is private, so access cannot be accessed
            declaredField.setAccessible(true);
            //params[0] is the user parameter object, and the resulting o is the value corresponding to the placeholder in #{}
            Object o = declaredField.get(params[0]);
            // Note that the subscript of the Settings parameter starts at 1
            preparedStatement.setObject(i + 1, o);

        }

        // 5. Execute SQL
        ResultSet resultSet = preparedStatement.executeQuery();
        // Get the full path of the entity in resultTypeString resultType = mappedStatement.getResultType(); Class<? > resultTypeClass = getClassType(resultType); ArrayList<Object> objects =new ArrayList<>();
        // 6. Encapsulate the return result set
        while (resultSet.next()) {
            // Use reflection to create entity-class objects
            Object o = resultTypeClass.newInstance();
            / / metadata
            ResultSetMetaData metaData = resultSet.getMetaData();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                / / the field name
                String columnName = metaData.getColumnName(i);
                // The value of the field
                Object value = resultSet.getObject(columnName);

                // Use reflection or introspection to complete the encapsulation according to the corresponding relationship between database tables and entities
                The /** * PropertyDescriptor class is a class in the introspection library * that generates accessor methods */ based on the columnName property in the resultTypeClass class
                PropertyDescriptor propertyDescriptor = new PropertyDescriptor(columnName, resultTypeClass);
                // Get the generated write method, which is equivalent to the setId or setUsername methods of the User class
                Method writeMethod = propertyDescriptor.getWriteMethod();
                // Execute the write method
                writeMethod.invoke(o, value);
            }
            objects.add(o);
        }
        return (List<E>) objects;
    }

    /** * Create an entity Class object * based on the entity Class path using reflection@paramParamterType encapsulates the full path of the entity class */
    privateClass<? > getClassType(String paramterType)throws ClassNotFoundException {
        if(paramterType ! =null) { Class<? > aClass = Class.forName(paramterType);return aClass;
        }
        return null;
    }


    #{/** *} #{/** *} 2. Parse the value inside #{} to store */
    private BoundSql getBoundSql(String sql) {
        // Tag handler class: configure tag parsers to handle placeholders (#{})
        ParameterMappingTokenHandler parameterMappingTokenHandler = new ParameterMappingTokenHandler();
        GenericTokenParser genericTokenParser = new GenericTokenParser("# {"."}", parameterMappingTokenHandler);
        #{id} becomes?
        String parseSql = genericTokenParser.parse(sql);
        //#{} parses the parameter name
        List<ParameterMapping> parameterMappings = parameterMappingTokenHandler.getParameterMappings();
        BoundSql boundSql = new BoundSql(parseSql, parameterMappings);
        returnboundSql; }}Copy the code

BoundSql class: used to encapsulate SQL and placeholder property names in #{} in the mapper.xml configuration file.

public class BoundSql {

    private String sqlText; // Parsed SQL

    // Store the object attribute names in #{}
    private List<ParameterMapping> parameterMappingList = new ArrayList<>();

    public BoundSql(String sqlText, List<ParameterMapping> parameterMappingList) {
        this.sqlText = sqlText;
        this.parameterMappingList = parameterMappingList;
    }

    public String getSqlText(a) {
        return sqlText;
    }

    public void setSqlText(String sqlText) {
        this.sqlText = sqlText;
    }

    public List<ParameterMapping> getParameterMappingList(a) {
        return parameterMappingList;
    }

    public void setParameterMappingList(List<ParameterMapping> parameterMappingList) {
        this.parameterMappingList = parameterMappingList; }}Copy the code

Use side test classes

public class IPersistenceTest {

    @Test
    public void test(a) throws Exception {
        InputStream resourceAsSteam = Resources.getResourceAsSteam("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsSteam);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        / / call
        User user = new User();
        user.setId(1);
        user.setUsername("Zhang");
        User user2 = sqlSession.selectOne("user.selectOne", user);
        System.out.println(user2);
        List<User> users = sqlSession.selectList("user.selectList"); }}Copy the code

Custom framework optimization

With our custom framework above, we have solved some of the problems associated with JDBC operating databases, such as frequently creating and releasing database connections, hard coding, and manually encapsulating return result sets, but now we move on to the custom framework code we just completed. Are there any problems?

The questions are as follows:

Dao implementation class has duplicate code, the entire operation process template is repeated (create SQLSession, call SQLSession method, close SQLSession)
The DAO implementation class is hard-coded. When the SQLSession method is called, the ID of statement is hard-coded

Solution: Use proxy mode to create proxy objects for the interface.

The test class code is modified as follows:

public class IPersistenceTest {

    @Test
    public void test(a) throws Exception {
        InputStream resourceAsSteam = Resources.getResourceAsSteam("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsSteam);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        / / call
        User user = new User();
        user.setId(1);
        user.setUsername("Zhang");
     
        IUserDao userDao = sqlSession.getMapper(IUserDao.class);
        List<User> all = userDao.findAll();
        for(User user1 : all) { System.out.println(user1); }}}Copy the code

Add methods to sqlSession

public interface SqlSession {
    // Generate the proxy implementation class for the Dao interface
    public <T> T getMapper(Class
        mapperClass);
}
Copy the code

Add the getMapper method in DefaultSqlSession to create proxy objects for the DAO layer

public class DefaultSqlSession implements SqlSession {

    private Configuration configuration;

    public DefaultSqlSession(Configuration configuration) {
        this.configuration = configuration;
    }

    @Override
    public <E> List<E> selectList(String statementid, Object... params) throws Exception {
        // I'm going to call the Query method in simpleExecutor
        simpleExecutor simpleExecutor = new simpleExecutor();
        MappedStatement mappedStatement = configuration.getMappedStatementMap().get(statementid);
        List<Object> list = simpleExecutor.query(configuration, mappedStatement, params);

        return (List<E>) list;
    }

    @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("Query result is empty or too many results are returned"); }}@Override
    public <T> T getMapper(Class
        mapperClass) {
        // Use the JDK dynamic proxy to generate proxy objects for the Dao interface and return

        Object proxyInstance = Proxy.newProxyInstance(DefaultSqlSession.class.getClassLoader(), new Class[]{mapperClass}, new InvocationHandler() {
            / * * * *@paramProxy Reference of the proxy object *@paramMethod A reference to the currently invoked method@paramArgs The argument * passed by the current calling method@return
             * @throws Throwable
             */
            @Override
            public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
                Call selctList or selectOne, depending on the case
                Parameter 1: Statmentid: the unique identifier of the SQL statement: namespace.id= The fully qualified name of the interface. The method name
                // The method name is findAll
                String methodName = method.getName();
                // Get com.lagou.dao.iuserDAO
                String className = method.getDeclaringClass().getName();

                / / com. Namely lagou. Dao. IUserDao. findAll
                String statementId = className+"."+methodName;

                // Prepare parameter 2: params:args
                // Gets the return value type of the called method
                Type genericReturnType = method.getGenericReturnType();
                List
      
        = List
       
         = List
        
       
      
                if(genericReturnType instanceof ParameterizedType){
                    List<Object> objects = selectList(statementId, args);
                    return objects;
                }
                returnselectOne(statementId,args); }});return(T) proxyInstance; }}Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Part ONE: Custom persistence layer framework

Analyze JDBC operation problems

Problem Solution

Custom framework design

Using the

Frame side

Custom framework implementation

Using the

Frame side

Custom framework optimization

Part ONE: Custom persistence layer framework

Analyze JDBC operation problems

Problem Solution

Custom framework design

Using the

Frame side

Custom framework implementation

Using the

Frame side

Custom framework optimization

Related Posts

Use the Redis feature to create a daily serial number

Install multiple JDK versions on your Mac and switch between them

Spring Transaction Management