PostgreSQL claims to be the most advanced open source database in the world, as shown below.

This article belongs to the introduction, initializing a spring boot project from zero, integrating PostgreSQL and using JPA and MyBatis to operate it. Only PostgreSQL related operations are recorded here. The project creation process is not described in detail. For similar processes, please refer to < spring boot: Idea initializes background projects from scratch > > . The Docker container built in the morning is used for PostgreSQL database. For the construction process, please refer to < docker case: building PostgreSQL container in Mac system and using > > , the following case also expands the user table created in this article.

1 project dependency

The current project uses the following dependency packages:

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.3'
    implementation 'org.apache.commons:commons-lang3:3.10'
    implementation 'com.alibaba:fastjson:1.2.71'
    implementation 'com.github.javafaker:javafaker:1.0.2'
    implementation 'com.google.guava:guava:29.0-jre'
    implementation 'com.github.pagehelper:pagehelper-spring-boot-starter:1.2.13'


    runtimeOnly 'org.postgresql:postgresql'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}

2 project configuration

There are two profiles application.properties And application-dev.properties . The former mainly configures the general items and specifies the environment configuration file, and the latter is the configuration file of the current environment (the value of the database configuration item needs to refer to < docker case: building PostgreSQL container in Mac system and using > > Settings in). The contents of the configuration file are as follows:
application.properties

spring.profiles.active=dev

mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

application-dev.properties

server.port=8280
#Basic database information configuration
spring.datasource.url=jdbc:postgresql://localhost:5433/pg-db-crane
spring.datasource.username=pg-user-crane
spring.datasource.password=crane
spring.datasource.driverClassName=org.postgresql.Driver
spring.jpa.show-sql=true
#JPA related configuration
spring.jpa.database=postgresql
#spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL9Dialect
spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false

logging.config=classpath:logback-dev.xml

3 JPA operation

For simple simulation operation, create entity class, database persistence operation class and a controller class.

3.1 construction entity

The user entity class is constructed according to the user table. The complete code is as follows:

@Entity
@Table(name = "user", schema = "public")
@Where(clause = "id > 0")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "name", nullable = false)
    private String name;
    private String phone;
    private String email;
    private String position;
    private String address;
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }


    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }


    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }


    public String getPosition() {
        return position;
    }

    public void setPosition(String position) {
        this.position = position;
    }


    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }


    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

}

3.2 database persistence class

According to the User entity, build the UserRepository interface and implement several operation methods:

• getTop10UserList: get the top 10 user information in the default order
• getUserByName: query user information by name
• countAllByName: count the number of users by name

The complete code is as follows:

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {

    //If the table name is a reserved word in db, it needs to be escaped. If it is not a reserved word, you can use the table name directly
    @Query(value = "select * from \"user\"", nativeQuery = true)
    List<User> getTop10UserList();

    @Query(value = "select * from \"user\" where name = :name", nativeQuery = true)
    List<User> getUserByName(@Param("name") String userName);

    Long countAllByName(String name);
}

3.3 controller class

Four basic operations are implemented in the controller class UserController:

• Add: add a user data
• Update: update a user data
• Get: get several types of data, including total data, total statistics by name, query all data by JPA, get the top 10 data, query data by name and simulate different types of operations
• page: realize paging query through MyBatis, with user name as optional parameter

The complete code is as follows:

@RestController
@RequestMapping("/user")
public class UserController {
    private final UserRepository userRepository;
    private final SqlSession sqlSession;

    public UserController(UserRepository userRepository, SqlSession sqlSession) {
        this.userRepository = userRepository;
        this.sqlSession = sqlSession;
    }

    /**
     * Add user, only provide user name, other information will be generated automatically
     *
     * @param userName User name
     * @return
     */
    @PostMapping("/add")
    public Object add(String userName) {
        Faker faker = new Faker(new Locale("zh", "CN"));
        User user = new User();
        user.setName(userName);
        user.setAddress(faker.address().fullAddress());
        user.setCreateTime(faker.date().birthday());
        user.setEmail("crane@qq.com");
        user.setPhone(faker.phoneNumber().phoneNumber());
        user.setPosition(faker.company().profession());
        this.userRepository.save(user);
        return user;
    }

    /**
     * Modify user name by id
     *
     * @param id       id
     * @param userName New user name
     * @return
     */
    @PostMapping("/update")
    public Object update(Integer id, String userName) {
        User user = this.userRepository.findById(id).orElseThrow(() -> new RuntimeException("user does not exist"));
        user.setName(userName);
        return this.userRepository.save(user);
    }

    /**
     * Get user information
     *
     * @param userName user name
     * @return
     */
    @PostMapping("/get")
    public Object get(String userName) {
        long countAll = this.userRepository.count();
        long countName = this.userRepository.countAllByName(userName);
        List<User> allByJpa = this.userRepository.findAll();
        List<User> top10ByQuery = this.userRepository.getTop10UserList();
        List<User> userByName = this.userRepository.getUserByName(userName);
        return new JSONObject().fluentPut("count", new JSONObject().fluentPut("all", countAll).fluentPut(userName, countName)).fluentPut("userByName", userByName).fluentPut("allByJpa", allByJpa).fluentPut("top10ByQuery", top10ByQuery);
    }

    /**
     * Paging query
     *
     * @param userName full name
     * @return
     */
    @PostMapping("/page")
    public Object page(String userName) {
        int page = 1;
        int pageSize = 10;
        PageRowBounds rowBounds = new PageRowBounds((page - 1) * pageSize, pageSize);
        List<Object> rows = this.sqlSession.selectList("user.get", new JSONObject().fluentPut("name", userName), rowBounds);
        return new JSONObject().fluentPut("curPage", page).fluentPut("totalCount", rowBounds.getTotal()).fluentPut("rows", rows);
    }

}

4 MyBatis operation

The location of the mapper file is specified in the configuration file and created in the specified location user.xml To write a statement, we implement a function simulation of querying users by name. The complete contents of the file are as follows. See page method in the controller class UserController for the calling method.

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

<mapper namespace="user">
    <select id="get" resultType="java.util.Map">
        SELECT * FROM "user" where id > 0
        <if test="name != null and name !=''">
            and name=#{name}
        </if>
        order by id
    </select>
</mapper>

5 request simulation

Use Postman to simulate the above request. The results are as follows:

5.1 add

5.2 update

5.3 get

5.4 page

6 attach complete project code

It's not easy to create, get 1 point. Portal