Java EE practice project -- express e stack (day 3)

Java EE practice project - Express e stack (day 3)

Our project uses native JDBC+Servlet for development. For our servlet, we have followed the practice of spring MVC for optimization, so we can focus on business development!

Connection pool

DruidUtil

Simply call the Druid connection pool and encapsulate the methods of obtaining connection and closing.

public class DruidUtil {

    /**
     * data source
     */
    private static DataSource dataSource;
    static{
        try {
            Properties ppt = new Properties();
            InputStream stream = DruidUtil.class.getClassLoader().getResourceAsStream("druid.properties");
            ppt.load(stream);
            dataSource = DruidDataSourceFactory.createDataSource(ppt);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Get data source
     *
     * @return {@link DataSource}
     */
    public static DataSource getDataSource() {
        return dataSource;
    }

    /**
     * Get connection
     * Take a connection from the connection pool to the user
     *
     * @return {@link Connection}
     */
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }


    /**
     * close
     *
     * @param conn  Connecticut
     * @param state state
     * @param rs    rs
     */
    public static void close(Connection conn, Statement state, ResultSet rs){
        try {
            if(rs!=null) {
                rs.close();
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        try {
            if(state!=null) {
                state.close();
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        try {
            if(conn!=null) {
                conn.close();
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
    }
}

configuration file

url=jdbc:mysql:///express?useUnicode=true&characterEncoding=utf-8
username=root
password=
driverClassName=com.mysql.jdbc.Driver
initialSize=5
maxActive=10
minIdle=5
maxWait=3000

Jdbc tools

We also need to optimize our native JDBC

Original code:

We have encapsulated the connection acquisition and closing methods to the tool class DruiUtil, but we still have to repeat the code:

  • Each method has methods to get the connection and close the connection
  • Each method needs to precompile SQL, execute the query method, and then encapsulate the result set as an object
  • Each method also needs exception handling
 @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        //1. Get the connection to the database
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2. Precompiled SQL statements
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            //3. Filling parameters (optional)
            //4. Execute SQL statement
            result = state.executeQuery();
            //5. Obtain the results of implementation
            if(result.next()){
                int data1_size = result.getInt("data1_size");
                int data1_day = result.getInt("data1_day");
                int data2_size = result.getInt("data2_size");
                int data2_day = result.getInt("data2_day");
                Map data1 = new HashMap();
                data1.put("data1_size",data1_size);
                data1.put("data1_day",data1_day);
                Map data2 = new HashMap();
                data2.put("data2_size",data2_size);
                data2.put("data2_day",data2_day);
                data.add(data1);
                data.add(data2);
            }
            //6. Release of resources
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }

        return data;
    }

    /**
     * Used to query all express
     *
     * @param limit      Whether to paginate. true indicates pagination. false means to query all express
     * @param offset     SQL Start index of statement
     * @param pageNumber Number of page queries
     * @return Express collection
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Express> data = new ArrayList<>();
        //1. Get the connection to the database
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2. Precompiled SQL statements
        try {
            if(limit) {
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                //3. Filling parameters (optional)
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }

            //4. Execute SQL statement
            result = state.executeQuery();
            //5. Obtain the results of implementation
            while(result.next()){
                int id = result.getInt("id");
                String number = result.getString("number");
                String username = result.getString("username");
                String userPhone = result.getString("userPhone");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                data.add(e);
            }
            //6. Release of resources
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

Solution

  • First of all, our JDBC can be divided into two methods: addition, deletion and modification method and query method. Among them, the return value of the addition, deletion and modification methods is int, that is, the number of rows affected, and there are many results of the query method: return an int, return an object, return an object List, and return a Map. So this is the difficulty of optimization.

  • The core part of our JDBC is SQL, return value types and parameters. With a given SQL and parameters, we can get the results directly in the database. Then, if we give the return value type, for example, specify that the return value type is a Student object, we can encapsulate the result set into this object.

  • Our database types and Java types do not match, such as varchar and char. Generally, we think they correspond to String, so we also need to configure them here.

Method design

  1. The method of adding, deleting and modifying query is to pass in SQL and parameters. Since the number of parameters is uncertain, you can use non quantitative parameters to solve it.

    Example:

    • int update(String sql,Object ... objects);
  2. Because there are many kinds of query methods, it is difficult to use one method to cover thousands. We divide it according to the result of the returned value:

    • Map<String,T> queryForMap(String sql,Class<T> className,Object ... objects)
      • Encapsulate the result into a Map. The key of our Map is the parameter name, and the parameter value can be of any type, including Object. Therefore, we need to pass in a Class Object, and the user can limit the return value type. If our query statement has multiple return results, we use Object
    • List queryForList(String sql,Class<T> className,Object ... objects)
      • Encapsulate the query result as a List, and pass in a Class object like the previous map method
    • Object queryForObject(String sql,Class<T> className,Object ... objects)
      • Encapsulate the query result as an Object. Like the previous map method, it also needs to pass in a Class Object
  3. We pass in a Class to create objects and call methods through the reflection mechanism. The objects here are simply divided into two categories: POJO and basic data type packaging. We distinguish them through some condition judgment, and then encapsulate the query results of the database into these objects in a different way.

    • POJO object

      Each property has corresponding get and set methods

    • java.lang package object

      That is, the wrapper class of basic data type. These objects do not have get and set methods, but we can directly construct methods and pass in objects to implement them.

  4. For the result set resultset queried from our database, JDBC provides rich APIs, including the number of fields of the query result, the name and type of each field and other results. We can use these methods to correspond the database query result to the attribute of the object we want to encapsulate one by one:

    • ResultSetMetaData metaData = resultSet. The GetMetadata () method can get the detailed data of the result.

      We first get the number of fields, then traverse each field, match the type of each field with the data type of Java one by one through the violent method of switch(), then use the get method to obtain the result, and then encapsulate it into the object attribute:

      Example:

      • For example, we want to encapsulate it as a String object. Our database query result is that the field name is username and the value is "dulao". We first get the value of the field, such as varchar, and then use switch to make one-to-one correspondence. The result is String class. Then, according to the result of switch, we call the "get" + "S" + "tring" splicing method of resultSet to find the corresponding method to obtain the String type, and then use the newinstance of String object (resultSet. GetString (1)) to fill our Stirng object
      • For the POJO object (Student), after the switch, we need to obtain the field name username, then find the setUsername method by splicing the string "set" + "U" + "username", then encapsulate the query result into the Student's username attribute, then cycle through the next attribute, and finally complete the filling of each attribute of the Student object.
      • We can see that for the basic wrapped data type, except that there is a certain difference in the processing of Integer, the other methods are get plus the first letter of the data type in uppercase and the other letters in lowercase, and then the parameters are int (segment number), which can be easily filled in with the for loop.
      • This is the switch I wrote. For current projects, only these types are involved. In fact, this matching table can extract a configuration file separately and configure it according to different needs.
      • In addition, when we fill POJO objects, we use set plus the first letter of the query result field name in uppercase and other lowercase letters, although it is very effective. However, the expansibility is poor. This part can also be extracted. When passing parameters to our queryForObject method, only Class objects are passed in. Here we can optimize it. For example, we define an interface, and then we customize the implementation Class to make one-to-one correspondence.

Reference code

The Jdbc code is optimized to one line! In this way, as long as we give SQL, parameters and encapsulated object types, we can return the results we want.

Reference code:

Just have a look. You can also use it directly.

package com.kaikeba.util;

import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 *
 * @Description :
 * @author: Faker
 * @date : 2020-09-28
 */
public class JdbcUtil {

    /**
     * Auto submit
     */
    private static boolean commit = true;

    /**
     * Automatic hump naming
     */
    private static boolean camelCase = true;

    /**
     * Set commit
     *
     * @param commit Submit
     */
    public static void setCommit(boolean commit) {
        JdbcUtil.commit = commit;
    }


    /**
     * Set automatic hump naming
     *
     * @param camelCase Automatic hump naming
     */
    public static void setCamelCase(boolean camelCase) {
        JdbcUtil.camelCase = camelCase;
    }

    /**
     * Get connection
     *
     * @return {@link Connection}
     */
    private static Connection getConnection() {
        try {
            Connection connection = DruidUtil.getDataSource().getConnection();
            connection.setAutoCommit(commit);
            return connection;
        } catch (SQLException e) {
            throw new RuntimeException("Failed to get connection");
        }
    }

    /**
     * to update
     * As long as the update method returns the number of affected rows, the return value is int
     *
     * @param sql     sql
     * @param objects object
     * @return int
     */
    public static int update(String sql, Object... objects) {
        Connection connection = null;
        PreparedStatement statement = null;
        int i = -1;
        try {
            //Populate SQL parameters
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            int index = 1;
            for (Object object : objects) {
                statement.setObject(index++, object);
            }
            i = statement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return i;
        } finally {
            close(connection, statement);
        }
    }

    public static <T> List<Map<String, Object>> queryForMap(String sql, Object... objects) {
        return queryForMap(sql, Object.class, objects);
    }

    /**
     * Encapsulate the query result as a Map. If there are multiple pieces of data, return a List collection whose element is Map
     *
     * @param sql       sql
     * @param className Class name
     * @param objects   object
     * @return {@link T}
     */
    public static <T> List<Map<String, T>> queryForMap(String sql, Class<T> className, Object... objects) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String methodName = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            int index = 1;
            //Populate SQL parameters
            for (Object object : objects) {
                statement.setObject(index++, object);
            }
            resultSet = statement.executeQuery();
            List<Map<String, T>> list = new ArrayList<>();
            //Fill query results as Java objects
            while (resultSet.next()) {
                Map<String, T> map = handlerMapData(resultSet, className);
                list.add(map);
            }
            return list.isEmpty()?null:list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            close(connection, statement, resultSet);
        }
    }

    /**
     * Query as object
     * Pass in a className and return an object of the same type as the object of the given class
     * At present, only packaging data types and POJO classes can be processed
     *
     * @param sql     sql
     * @param objects object
     * @return {@link T}
     */
    public static <T> T queryForObject(String sql, Class<T> className, Object... objects) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            int index = 1;
            //Populate SQL parameters
            for (Object object : objects) {
                statement.setObject(index++, object);
            }
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                return null;
            }
            return handlerData(resultSet, className);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            close(connection, statement, resultSet);
        }
        //Fill query results as Java objects

    }

    /**
     * Query as list
     * Pass in a className and return a list of objects with the same object type as the given class
     *
     * @param sql     sql
     * @param objects object
     * @return {@link List<T>}
     */
    public static <T> List<T> queryForList(String sql, Class<T> className, Object... objects) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String methodName = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            int index = 1;
            //Populate SQL parameters
            for (Object object : objects) {
                statement.setObject(index++, object);
            }
        }catch (SQLException e) {
            System.out.println("SQL Parameter exception!");
            close(connection, statement);
            e.printStackTrace();
            return null;
        }
        try {
            resultSet = statement.executeQuery();
            List<T> list = new ArrayList<>();
            //Fill query results as Java objects
            while (resultSet.next()) {
                list.add(handlerData(resultSet, className));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, statement, resultSet);
        }
        return null;
    }


    /**
     * Handler data
     * This method encapsulates the result set as an object, either a pojo object or an Integer ordinary object
     * For example, select count(*) from table is also a query statement, and the ResultSet method is also used.
     *
     * @param resultSet Result set
     * @return {@link T}
     */
    private static <T> T handlerData(ResultSet resultSet, Class<T> className) throws SQLException{
        T object = null;
        try {
            //If it is a wrapper data type, Java Under Lang
            if (className.getName().startsWith("java.lang")) {
                return defaultHandlerData(resultSet, className);
            }
            object = className.newInstance();
            fillInData(object, className, resultSet, false);
        } catch (IllegalAccessException | InstantiationException | NoSuchMethodException | InvocationTargetException e) {
            e.printStackTrace();
        }
        return object;
    }

    private static <T> Map<String, T> handlerMapData(ResultSet resultSet, Class<T> className)throws SQLException {
        Map<String, T> object = null;
        try {
            object = new HashMap<>(16);
            fillInData(object, className, resultSet, true);
        } catch (IllegalAccessException | NoSuchMethodException | InvocationTargetException e) {
            e.printStackTrace();
        }
        return object;
    }

    /**
     * Fill data
     *
     * @param object    object
     * @param className Class name
     * @param resultSet Result set
     * @param toMap     mapping
     * @throws SQLException              sqlexception abnormal
     * @throws NoSuchMethodException     There is no such method exception
     * @throws InvocationTargetException Call target exception
     * @throws IllegalAccessException    Illegal access exception
     */
    private static <T> void fillInData(Object object, Class<T> className, ResultSet resultSet, boolean toMap) throws SQLException, NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        //Get the number of fields of a query record
        for (int i = 1; i < metaData.getColumnCount() + 1; i++) {
            //Take the name of each field
            String columnName = metaData.getColumnName(i);
            StringBuffer sb = new StringBuffer();
            Class type = matchObject(metaData.getColumnTypeName(i));
            //format
            String baseMethodName=columnName;
            if (camelCase){
                for (int j = 0; j < columnName.length(); j++) {
                    if (columnName.charAt(j) == '_') {
                        j++;
                        sb.append(columnName.substring(j, j + 1).toUpperCase());
                    } else {
                        sb.append(columnName.substring(j, j + 1));
                    }
                }
                baseMethodName=sb.toString();
            }
            //Call the set method of the object to fill in the object properties
            String[] strings = type.getTypeName().split("\\.");
            String typeName = strings[strings.length - 1];
            //Determine which get method to use to query data by field name
            String handlerMethodName = "get" + ("Integer".equals(typeName) ? "Int" : typeName.substring(0, 1).toUpperCase() + typeName.substring(1, typeName.length()));
            Method handlerMethod = resultSet.getClass().getDeclaredMethod(handlerMethodName, int.class);
            Object arg = handlerMethod.invoke(resultSet, i);
            //Call get method
            if (toMap) {
                Method method = object.getClass().getDeclaredMethod("put", Object.class, Object.class);
                method.invoke(object, baseMethodName, arg);
            } else {
                String methodName = "set" + baseMethodName.substring(0, 1).toUpperCase() + baseMethodName.substring(1, baseMethodName.length());
                Method method = className.getDeclaredMethod(methodName, type);
                method.invoke(object, arg);
            }
        }
    }

    /**
     * Default data handler
     *
     * @param resultSet Result set
     * @param className Class name
     * @return {@link T}* @throws NoSuchMethodException There is no such method exception
     * @throws SQLException              sqlexception abnormal
     * @throws InvocationTargetException Call target exception
     * @throws IllegalAccessException    Illegal access exception
     * @throws InstantiationException    Instantiation exception
     */
    private static <T> T defaultHandlerData(ResultSet resultSet, Class<T> className) throws NoSuchMethodException, SQLException, InvocationTargetException, IllegalAccessException, InstantiationException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        Constructor<T> constructor = null;
        Method handlerMethod = null;
        String handlerMethodName = null;
        //Match the type of this field, and set Java Lang is split, leaving the last type
        Class type = matchObject(metaData.getColumnTypeName(1));
        String[] strings = type.getTypeName().split("\\.");
        String typeName = strings[strings.length - 1];
        //If it is Integer, the get method of resultSet is getInt
        //Other methods add get before the type to correspond one by one
        if ("Integer".equals(typeName)) {
            constructor = className.getConstructor(int.class);
            handlerMethodName = "getInt";
        } else {
            constructor = className.getConstructor(type);
            handlerMethodName = "get" + typeName.substring(0, 1).toUpperCase() + typeName.substring(1, typeName.length());
        }
        //Call the get method of resultSet to get the data
        handlerMethod = resultSet.getClass().getDeclaredMethod(handlerMethodName, int.class);
        Object arg = handlerMethod.invoke(resultSet, 1);
        //Call a parameter construction method
        return constructor.newInstance(arg);
    }


    /**
     * Match the data types of the fields in the database to our java data types
     *
     * @param columnTypeName The name of the column type
     * @return {@link Class<?>}
     */
    private static Class<?> matchObject(String columnTypeName) {
        switch (columnTypeName) {
            case "INT":
            case "BIGINT":
                return Integer.class;
            case "TIMESTAMP":
                return Timestamp.class;
            case "VARCHAR":
            case "CHAR":
                return String.class;
            case "FLOAT":
                return Float.class;
            case "DOUBLE":
            case "DECIMAL":
                return Double.class;
            case "DATE":
            case "DATETIME":
                return Date.class;
            default:
                System.out.println(columnTypeName + "No matching type found for type");
                return Object.class;
        }
    }


    /**
     * close
     *
     * @param connection connect
     * @param statement  statement
     */
    private static void close(Connection connection, Statement statement) {
        close(connection, statement, null);
    }

    /**
     * close
     *
     * @param connection connect
     */
    private static void close(Connection connection) {
        close(connection, null, null);
    }

    /**
     * close
     *
     * @param connection connect
     * @param statement  statement
     * @param resultSet  Result set
     */
    private static void close(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (!commit) {
                connection.rollback();
            }
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}

Posted by franknu on Fri, 13 May 2022 09:19:04 +0300