0107 three shelves of spring operation database


Database development is one of the core contents of java, and the foundation is jdbc; However, using jdbc directly requires a lot of try catch finally template code; Using hibernate as orm framework is more convenient for management system, which follows jpa standard; In the Internet era, Mybatis is used because of its flexibility and convenience for sql optimization; In addition, spring also provides a database access mode of JDBC template, which is not used by a large number of enterprises; Before using these ORM frameworks, you must first configure the data source;

data source

Data connection pool, which can reuse connections

Common databases |Database description| |-|-| |h2| memory database| |derby| memory database| |hqldb| memory database| |mysql| commercial database, open source and free| |oracle| commercial database, oracle| |mssql is provided by sql server|

Introduce dependency: (spring JDBC, MySQL driven)


To configure jdbc data source for springboot:

spring.datasource.url = jdbc:mysql:localhost:3306/xxx
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.type = Configure data connection pool( org.apache.commons.dbp2.BasicDataSource)

hikaricp is used by default;

The code is as follows:

public class DemoDatasourceTomcatApplication {
	public static void main(String[] args) {
		final ConfigurableApplicationContext applicationContext = SpringApplication.run(DemoDatasourceTomcatApplication.class, args);
		final DataSource dataSource = applicationContext.getBean(DataSource.class);

Common data connection pools

data source Explain
tomcat tomcat is built-in, and springboot comes with it
dbcp2 External classic data source
druid Alibaba's open source, easy to monitor and expand data sources
hikricp A super fast data source open source in Japan

Verify the configured data source:


JDBC template provides a standard interface for database operation; Add, delete, change and check; JDBC template provides two ways to execute multiple sql statements in a connection: StatementCallback or ConnectionCallback;

The code example is as follows:

package com.springbootpractice.demo.demo_datasource_tomcat.dao.jdbc.impl;

import com.springbootpractice.demo.demo_datasource_tomcat.dao.entity.UserLoginEntity;
import com.springbootpractice.demo.demo_datasource_tomcat.dao.entity.enums.SexEnum;
import com.springbootpractice.demo.demo_datasource_tomcat.dao.jdbc.IUserJdbcBiz;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

 * Description: jdbc template code
 * @author carter
 * Creation time: 2:36 PM, January 7, 2020
public class UserJdbcBiz implements IUserJdbcBiz {

    private final JdbcTemplate jdbcTemplate;

    public UserJdbcBiz(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;

    public UserLoginEntity getUserLogin(Long id) {

        String sql = "SELECT id,user_name,password,sex,note FROM user_login WHERE id=? ";
        Object[] params = {id};
        return jdbcTemplate.queryForObject(sql, params, getUserLoginMapper());

    public List<UserLoginEntity> findUserLogin(String userName, String note) {

        String sql = "SELECT id,user_name,password,sex,note FROM user_login WHERE user_name=? and note=?";
        Object[] params = {userName, note};
        return Optional.ofNullable(jdbcTemplate.query(sql, params, getUserLoginMapper()))

    public long createUserLogin(UserLoginEntity entity) {
        String sql = "INSERT INTO user_login(user_name, password, sex, note) VALUES (?,?,?,?)";
        Object[] params = {entity.getUserName(), entity.getPassword(), entity.getSex().getCode(), entity.getNote()};
        return jdbcTemplate.update(sql, params);

    public long updateUserLogin(UserLoginEntity entity) {
        String sql = "UPDATE user_login SET user_name=? , password=? , sex=? , note=? WHERE id=? ";
        Object[] params = {entity.getUserName(), entity.getPassword(), entity.getSex().getCode(), entity.getNote(), entity.getId()};
        return jdbcTemplate.update(sql, params);

    public long deleteUserLogin(Long id) {
        String sql = "DELETE FROM user_login WHERE id=? ";
        Object[] params = {id};
        return jdbcTemplate.update(sql, params);

    public static RowMapper<UserLoginEntity> getUserLoginMapper() {
        return (ResultSet rs, int rowNum) -> UserLoginEntity.builder()



package com.springbootpractice.demo.demo_datasource_tomcat;

import com.springbootpractice.demo.demo_datasource_tomcat.dao.entity.UserLoginEntity;
import com.springbootpractice.demo.demo_datasource_tomcat.dao.entity.enums.SexEnum;
import com.springbootpractice.demo.demo_datasource_tomcat.dao.jdbc.IUserJdbcBiz;
import com.springbootpractice.demo.demo_datasource_tomcat.dao.jdbc.impl.UserJdbcBiz;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.util.Assert;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Objects;

class DemoDatasourceTomcatApplicationTests {

	private IUserJdbcBiz userJdbcBiz;

	void crateUserLoginTest() {

		final long id = userJdbcBiz.createUserLogin(UserLoginEntity.builder()
				.note("First account")

		final UserLoginEntity userLogin = userJdbcBiz.getUserLogin(id);

		Assert.isTrue(Objects.equals(id,userLogin.getId()),"Insert failure");


	private JdbcTemplate jdbcTemplate;

	void twoOperationOneConnectionTest() {

		final UserLoginEntity result = jdbcTemplate.execute((StatementCallback<UserLoginEntity>) statement -> {

			//First insert

			final int i = statement.executeUpdate("INSERT INTO user_login(user_name, password, sex, note) VALUES ('gemini.he','abc123456',2,'I am pretty!')");

			//Then query
			final ResultSet resultSet = statement.executeQuery("SELECT id,user_name,password,sex,note FROM user_login WHERE user_name='gemini.he'");

			 UserLoginEntity userLoginEntity =null;
			while (resultSet.next()){
			userLoginEntity = UserJdbcBiz.getUserLoginMapper().mapRow(resultSet, resultSet.getRow());

			return userLoginEntity;

		Assert.isTrue(Objects.equals("gemini.he",result.getUserName()),"Insert before query failed");


dbcp2 and spring JDB code path point me!


JPA:java persistence API, which defines object relationship mapping and entity object persistence interface, not limited to EJB, can run independently, develop, test and rely on Hibernate support; The core of JPA is entity bean, which is used by a persistent context, which includes three parts;

assembly Explain
ORM relationship description It supports annotation or xml description, which is used in springboot
Entity operation API CRUD operations on entities can be implemented through specifications
JPQL query language The object-oriented query language is agreed, which can realize flexible query

The functional system of JPA repository is as follows:

JpaReporsitory->>QueryByExampleExecutor: Query function?
JpaReporsitory-->>PageingAndSortingRepository: Paging and sorting functions
JpaReporsitory-->>CrudRepository: Add, delete, modify and check functions

You can define new query methods through annotations flexibly, or write an empty method and signature directly according to the rules

rely on


JPA run code point me!


It does not shield sql and provides a semi-automatic framework of dynamic sql, interface programming and simple sql binding pojo; At present, the most popular java persistence layer technology is mybatis, which is easier to use and more flexible than jpa; It can be applied to the current Internet environment, but it has a large amount of data, high concurrency and sensitive performance problems; Official definition: excellent persistence framework supporting customized sql, stored procedures and advanced mapping. Almost all jdbc code and manual setting of parameters and obtaining result sets are avoided. mybatis maps the interface and POJO to records in the database through xml or annotations.

High performance, flexible and convenient

  1. Support hump mapping, between sql and pojo, reduce the workload of developers;
  2. Without shielding sql, it provides flexibility and optimizes sql to the maximum extent;
  3. Support dynamic sql to adapt to changes in requirements

configuration file

Because of the limitation of annotation's function and readability, xml is mostly used to configure;

  1. The basic configuration file; specified by mybatis.config-location, where the underlying mybatis is configured
  2. Mapping configuration file; mybatis. Mapper locations = classpath: mapper / *. XML

rely on

Community latest version


Configuration of mybatis

Core categories: SqlSessionFactory application is unique internally The core class of SqlSession operation is generally erased in the application, i.e. senseless, instead of using various XMapper interfaces; Configuration: the core configuration of mybatis

Configuration project Explain
settings Set the lower level behavior of mybatis, such as hump mapping, actuator type, cache, etc
typeAliases Type alias, @ Alias("aa")
typeHandlers Type processor to handle supported types, such as enumeration, LocalDate, etc
plugins Plugins, interceptors, and mybatis are the most powerful and dangerous components. Through the agent and responsibility chain mode, you can modify the underlying implementation functions, as shown in the code
mappers Mapper, core component, defines the mapping relationship between sql and pojo, which can be generated by generator

Integrating springboot

Adopt @ MapperScan annotation

@MapperScan(basePackages = "com.springbootpractice.demo.mybatis.dao",
        annotationClass = Repository.class

Property configuration:

mybatis.mapper-locations= classpath:mapper/*.xml
mybatis.type-aliases-package= com.springbootpractice.demo.mybatis.dao.entity
mybatis.type-handlers-package= com.springbootpractice.demo.mybatis.dao.entity.handler
mybatis.config-location= classpath:mybatis.xml
mybatis.executor-type= reuse

Entity code, mapper interface and xml configuration are common. Here is an example:

domain entity

package com.springbootpractice.demo.mybatis.dao.entity;

import com.springbootpractice.demo.mybatis.dao.entity.enums.SexEnum;
import lombok.Data;
import org.apache.ibatis.type.Alias;

import java.io.Serializable;

 * Description: TODO
 * @author carter
 * Creation time: 5:06 PM, January 7, 2020
public class UserLoginEntity implements Serializable {

    private Long id;

    private String userName;

    private String password;

    private SexEnum sex;

    private String  note;


mapper interface

package com.springbootpractice.demo.mybatis.dao.mapper;

import com.springbootpractice.demo.mybatis.dao.entity.UserLoginEntity;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

 * Description: TODO
 * @author carter
 * Creation time: 5:17 PM, January 7, 2020
public interface UserLoginMapper {

     * Query user information by id
     * @param id id
     * @return User information
    UserLoginEntity getById(@Param("id") Long id);


Mapping xml of sql and domain

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

<mapper namespace="com.springbootpractice.demo.mybatis.dao.mapper.UserLoginMapper">

    <select id="getById" parameterType="java.lang.Long" resultType="userLogin">
        SELECT * FROM user_login where id=#{id}


The underlying mybatis configuration file:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//MYBATIS.ORG//DTD Config 3.0//EN"
        <plugin interceptor="com.springbootpractice.demo.mybatis.dao.plugin.MyPlugin">
            <property name="a" value="a"/>

There are many code generation tools. You can use the next idea plug-in or the maven plug-in to generate domain, mapper and XML code;

Mybatis? Demo run code point me!


  1. What are the common data connection pools? DBCP2 is applied simply;
  2. The whole process of spring JDBC operating database;
  3. The whole process of spring JPA operating database;
  4. The whole process of spring mybatis operating database; In the actual work, you can choose flexibly, but generally only one shelf is selected in a team, most of which is mybatis; Original is not easy, reprint please indicate the source.

Tags: Programming Mybatis SQL JDBC Spring

Posted on Tue, 07 Jan 2020 04:20:44 -0800 by Copernicus