JDBC

When it comes to java operating database, JDBC must be inexcusable. Mybatis actually encapsulates JDBC

// Register JDBC Driver 
Class.forName("com.mysql.cj.jdbc.Driver"); 

// open a connection 
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/spring-shiwu? characterEncoding=utf-8&serverTimezone=UTC", "root", "123456"); 

// Execute query 
stmt = conn.createStatement(); 
String sql = "SELECT id,user_name,real_name,password,age,d_id from t_user where id = 1"; ResultSet rs = stmt.executeQuery(sql); 

// Get result set 
while (rs.next()) { 
Integer id = rs.getInt("id"); 
String userName = rs.getString("user_name"); 
String realName = rs.getString("real_name"); 
String password = rs.getString("password"); 
Integer did = rs.getInt("d_id"); 
user.setId(id); 
user.setUserName(userName); 
user.setRealName(realName);
user.setPassword(password); 
user.setDId(did); 
System.out.println(user); 
}

• jdbc operation steps
  • Class.forName register driver
  • Get a Connection object
  • Create a Statement object
  • The execute() method executes the SQL statement to obtain the ResultSet result set
  • Assign a value to the property of POJO through the ResultSet result set
  • Finally, close the related resources

We need to maintain the connection of management resources by ourselves. If we forget it, it is likely to cause the connection of database service to be exhausted. The specific business SQL statements are written directly in the code, and the coupling is enhanced. In addition, each connection goes through these steps and repeats a lot of code.

• Summarize the characteristics of the above operation
  • Duplicate code
  • resource management
  • Result set processing
  • SQL coupling (that is, it is not flexible. It rewrites SQL statements every time, rather than a relatively omnipotent method. It can dynamically pass in parameters and perform different functions dynamically)

JDBC optimization 1

First, optimize from the aspects of code duplication and resource management. We can create a tool class to deal with this problem

public class DBUtils {

    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/mybatisdb?characterEncoding=utf-8&serverTimezone=UTC";
    private static final String JDBC_NAME = "root";
    private static final String JDBC_PASSWORD = "123456";

    private static  Connection conn;

    /**
     * Provide external methods for obtaining database connection
     */
    public static Connection getConnection() throws Exception {
        if(conn == null){
            try{
                conn = DriverManager.getConnection(JDBC_URL,JDBC_NAME,JDBC_PASSWORD);
            }catch (Exception e){
                e.printStackTrace();
                throw new Exception();
            }
        }
        return conn;
    }

    /**
     * close resource
     * @param conn
     */
    public static void close(Connection conn ){
        close(conn,null);
    }

    public static void close(Connection conn, Statement sts ){
        close(conn,sts,null);
    }

    public static void close(Connection conn, Statement sts , ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }

        if(sts != null){
            try {
                sts.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}

In this way, JDBC code can call the corresponding code of this tool class. It looks better, but it's not enough

JDBC optimization 2

We can optimize the method of DML (i.e. add, delete and modify) operation. First solve the problem of SQL coupling and encapsulate the method of DML operation in DBUtils

/**
     * Execute DML operation of database
     * @return
     */
    public static Integer update(String sql,Object ... paramter) throws Exception{
        conn = getConnection();

         //Dynamic splicing parameters
        PreparedStatement ps = conn.prepareStatement(sql);
        if(paramter != null && paramter.length > 0){
            for (int i = 0; i < paramter.length; i++) {
                //The setObject method is a built-in api that encapsulates parameters
                ps.setObject(i+1,paramter[i]);
            }
        }
        int i = ps.executeUpdate();
        //Called tool class method
        close(conn,ps);
        return i;
    }

This is the JDBC code, which is simplified as follows

public void addUser(){
        String sql = "INSERT INTO T_USER(user_name,real_name,password,age,d_id)values(?,?,?,?,?)";
        try {
            DBUtils.update(sql,"bobo","Wang Wu","111",22,1001);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

The problem of sql coupling has been solved, but the processing problem of ResultSet result set has not been solved, so we need to continue to optimize

JDBC optimization 3

To optimize the result set, that is, dynamically assign objects, it is not difficult to think of using reflection

public static <T> List<T> query(String sql, Class clazz, Object ... parameter) throws  Exception{
        //It's as like as two peas just update.
        conn = getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        if(parameter != null && parameter.length > 0){
            for (int i = 0; i < parameter.length; i++) {
                ps.setObject(i+1,parameter[i]);
            }
        }
        ResultSet rs = ps.executeQuery();

        // Get the metadata of the corresponding table structure
        ResultSetMetaData metaData = ps.getMetaData();
        List<T> list = new ArrayList<>();
        while(rs.next()){
            // Get the corresponding value according to the field name, and then encapsulate the data into the corresponding object
            int columnCount = metaData.getColumnCount();

            Object o = clazz.newInstance();
            //The column displacement in the database starts from 1
            for (int i = 1; i < columnCount+1; i++) {
                // Get the corresponding value according to the name of each column
                String columnName = metaData.getColumnName(i);

                //Get the value of the corresponding field of the result set through the data column
                Object columnValue = rs.getObject(columnName);

                //Assignment of objects using reflection
                setFieldValueForColumn(o,columnName,columnValue);
            }
            list.add((T) o);
        }
        return list;
    }

private static void setFieldValueForColumn(Object o, String columnName,Object columnValue) {
        Class<?> clazz = o.getClass();
        try {
            // Get properties by field
            Field field = clazz.getDeclaredField(columnName);
            // Private attribute release permission
            field.setAccessible(true);
            field.set(o,columnValue);
            field.setAccessible(false);
        }catch (Exception e){
            // If it doesn't exist, it will_ Convert to hump nomenclature_ name  --> userName
            if(columnName.contains("_")){
                Pattern linePattern = Pattern.compile("_(\\w)");
                columnName = columnName.toLowerCase();
                Matcher matcher = linePattern.matcher(columnName);
                StringBuffer sb = new StringBuffer();
                while (matcher.find()) {
                    matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
                }
                matcher.appendTail(sb);
                // Call the copy operation again
                setFieldValueForColumn(o,sb.toString(),columnValue);
            }
        }
    }

Apache DBUtils (very efficient)

A QueryRunner class is provided in DButils, which encapsulates the methods of adding, deleting, modifying and querying the database. The way to obtain QueryRunner is as follows

druid.properties configuration file

druid.username=root
druid.password=admin
druid.url=jdbc:mysql://localhost:3306/spring-shiwu?characterEncoding=utf-8&serverTimezone=UTC
druid.minIdle=10
druid.maxActive=30

private static final String PROPERTY_PATH = "druid.properties";

    private static DruidDataSource dataSource;
    private static QueryRunner queryRunner;

    public static void init() {
        //This api has been used by spring before
        Properties properties = new Properties();
        //Load profile
        InputStream in = DruidUtils.class.getClassLoader().getResourceAsStream(PROPERTY_PATH);
        try {
            properties.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //Establish data source
        dataSource = new DruidDataSource();
        dataSource.configFromPropety(properties);
        // Initialize QueryRunner with data source
        queryRunner = new QueryRunner(dataSource);
    }

The method provided in QueryRunner solves the problem of duplicate code, and the incoming data source solves the problem of resource management. The processing of ResultSet result set is handled through ResultSetHandler (it is an interface and provides some implementation classes). We can also implement the interface ourselves, or use the default related implementation provided in DBUtils. The default implementation is ArrayHandler, and the handle method converts it into an object array

Self realization

public void queryUser() throws Exception{
        DruidUtils.init();
        QueryRunner queryRunner = DruidUtils.getQueryRunner();
        String sql = "select * from t_user";
        //The ResultSet result set is processed through the ResultSetHandler. We can implement the interface ourselves. Here is the lambda expression writing method
        //Or use the default implementation provided in DBUtils. The default implementation is array handle r, which converts it into an object array
        List<User> list = queryRunner.query(sql, new ResultSetHandler<List<User>>() {
            @Override
            public List<User> handle(ResultSet rs) throws SQLException {
                List<User> list = new ArrayList<>();
                while(rs.next()){
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUserName(rs.getString("user_name"));
                    user.setRealName(rs.getString("real_name"));
                    user.setPassword(rs.getString("password"));
                    list.add(user);
                }
                return list;
            }
        });
        for (User user : list) {
            System.out.println(user);
        }
    }

Handle queries through the implementation class of ResultHandle

public void queryUserUseBeanListHandle() throws Exception{
        DruidUtils.init();
        QueryRunner queryRunner = DruidUtils.getQueryRunner();
        String sql = "select * from t_user";
        // It will not automatically help us realize the conversion of hump naming
        List<User> list = queryRunner.query(sql, new BeanListHandler<User>(User.class));
        for (User user : list) {
            System.out.println(user);
        }
    }

Spring JDBC (very efficient)

A template method JdbcTemplate is provided in Spring, which encapsulates various execute,query and update methods. JdbcTemplate is the central class of the core package of JDBC, which simplifies the operation of JDBC and avoids common exceptions. It encapsulates
As the core process of JDBC, the application only needs to provide SQL statements and extract the result set. It is thread safe.

Initial configuration class

@Configuration
@ComponentScan
public class SpringConfig {

    @Bean
    public DataSource dataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUsername("root");
        dataSource.setPassword("admin");
        dataSource.setUrl("jdbc:mysql://localhost:3306/spring-shiwu?characterEncoding=utf-8&serverTimezone=UTC");
        return  dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource){
        JdbcTemplate template = new JdbcTemplate();
        template.setDataSource(dataSource);
        return template;
    }
}

CRUD operation

@Repository
public class UserDao {

    @Autowired
    private JdbcTemplate template;

    public void addUser(){
        int count = template.update("insert into t_user(user_name,real_name)values(?,?)","bobo","Miss Bobo");
        System.out.println("count = " + count);
    }



    public void query1(){
        String sql = "select * from t_user";
        List<User> list = template.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUserName(rs.getString("user_name"));
                user.setRealName(rs.getString("real_name"));
                return user;
            }
        });
        for (User user : list) {
            System.out.println(user);
        }
    }


    public void query2(){
        String sql = "select * from t_user";
        List<User> list = template.query(sql, new BeanPropertyRowMapper<>(User.class));
        for (User user : list) {
            System.out.println(user);
        }
    }

}

The query result set mapping of spring JDBC is the same as that of Apache DBUtils, and the code is basically the same

Hibernate

Although Apache DBUtils and SpringJdbcTemplate simplify the operation of the database, they provide relatively simple functions (lack of cache, transaction management, etc.), so we often do not directly use the above technologies in actual development, but use Hibernate (rarely used now) and MyBatis and other professional ORM persistence layer frameworks.

What is ORM

ORM (object relational mapping), that is, the mapping between objects and relationships. Objects are objects in programs and relationships are them
The relationship with the data in the database, that is, the problem that ORM framework helps us solve is the connection between program objects and relational database
Mutual mapping problem

Use of Hibernate

Introducing maven dependency

 <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

Entity class configuration file

<hibernate-mapping>
    <class name="com.gupaoedu.vip.model.User" table="t_user">
        <id name="id" />
        <property name="userName" column="user_name"></property>
        <property name="realName" column="real_name"></property>
    </class>
</hibernate-mapping>

Hibernate configuration file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">
            com.mysql.cj.jdbc.Driver
        </property>
        <property name="hibernate.connection.url">
            jdbc:mysql://localhost:3306/mybatisdb?characterEncoding=utf8&amp;serverTimezone=UTC
        </property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">123456</property>
        <property name="hibernate.dialect">
            org.hibernate.dialect.MySQLDialect
        </property>

        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.format_sql">true</property>
        <property name="hibernate.hbm2ddl.auto">update</property>

        <mapping resource="User.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

CRUD operation

public class HibernateTest {

    /**
     * Hibernate Demonstration operation case
     * @param args
     */
    public static void main(String[] args) {
        Configuration configuration = new Configuration();
        // Hibernate is used by default cfg. xml
        configuration.configure();
        // Create Session factory
        SessionFactory factory = configuration.buildSessionFactory();
        // Create Session
        Session session = factory.openSession();
        // Get transaction object
        Transaction transaction = session.getTransaction();
        // Open transaction
        transaction.begin();
        // Add objects to the database
        User user = new User();
        user.setId(668);
        user.setUserName("hibernate-1");
        user.setRealName("Persistence layer framework");
        session.save(user);
        transaction.commit();
        session.close();
    }
}

The mapping file can also be annotated

Data
@Entity
@Table(name = "t_user")
public class User {

    @Id
    @Column(name = "id")
    private Integer id;

    @Column(name = "user_name")
    private String userName;

    @Column(name = "real_name")
    private String realName;

    @Column(name = "password")
    private String password;

    @Column(name = "age")
    private Integer age;

    @Column(name = "i_id")
    private Integer dId;
}

The JPA provided to us in Spring encapsulates the persistence layer framework uniformly, and is essentially implemented based on Hibernate JPA
Therefore, we can also operate through the API of spring data JPA
dao's interface only needs to inherit the JpaRepository interface (similar to the inheritance of mybatis plus)

public interface IUserDao extends JpaRepository<User,Integer> {
}

@Service
public class UserServiceImpl implements IUserService {

    @Autowired
    private IUserDao dao;

    @Override
    public List<User> query() {
        return dao.findAll();
    }

    @Override
    public User save(User user) {
        return dao.save(user);
    }
}

Advantages and disadvantages of Hibernate

• advantage
  • Generate SQL according to the dialect of the database, with good portability
  • Automatically manage connection resources
  • The complete mapping between objects and relational data is realized, and the operation of objects is the same as that of database records
  • Provides caching mechanism
• shortcoming
  • For example, the get(),update() and save() methods in the API actually operate on all fields, and there is no way to specify some fields,
    In other words, it is not flexible enough (because it is fully automatic)
  • It is also very difficult to customize the way of generating SQL. If you want to do some optimization based on SQL.
  • Dynamic SQL is not supported, such as table name, condition and parameter change in sub table. SQL cannot be generated automatically according to conditions
    Therefore, we need a more flexible framework