In part 2, we learned how to write Restful interfaces and generate interface documentation. We need to store interface data persistently. In this section, we will mainly study several persistence frameworks to store data. In this section, we will use mysql as a demonstration database.

At the beginning of the contact with the database operation, we will use JDBC for database operation, but each time to create a connection, closing the connection is very troublesome, Spring JDBC for simple encapsulation into a new framework — JdbcTemplate.

Project structures,

1 Add mysql dependencies

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    The < version > 8.0.11 < / version >
</dependency>
Copy the code

2. Add the jdbcTemplate dependency

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
Copy the code

3. Configure the mysql

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql: / /localhost: 3306 /simple_fast?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&useAffectedRows=true&allowPublicKeyRetrieval=true
    username: root
    password: root
Copy the code

Simple operation database

Create a new table named Student to test the common functionality of the JdbcTemplate. The construction sentences are as follows:

CREATE TABLE `student` (
   `student_id` int(30) NOT NULL,
   `age` int(1) DEFAULT NULL COMMENT 'age',
   `name` varchar(45) DEFAULT NULL COMMENT 'name',
   `sex` int(1) DEFAULT NULL COMMENT 'Gender: 1: male, 2: female, 0: unknown',
   `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
   `status` int(1) DEFAULT NULL COMMENT 'Status: 1: Normal, -1: Deleted'.PRIMARY KEY (`student_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='Student List'
Copy the code

The corresponding entity class is as follows:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class StudentBean implements Serializable {
    
    private static final long serialVersionUID = 4618004018169112427L;

    private Integer studentId;
    private Integer age;
    private String name;
    private Integer sex;
    private Date createTime;
    private Integer status;
}
Copy the code

JdbcTemplate provides a relatively complete database operation API. There are two kinds of APIS commonly used in daily development, respectively

  • The query and queryXXX
  • Update and batchUpdate

JdbcTemplate is also very simple to use. Where do YOU need injection

@Autowired
private JdbcTemplate jdbcTemplate;
Copy the code

1 Query a single entity

public StudentBean getStudent(int status){
    String sql = "select * from student where status = ? limit 1";
    return jdbcTemplate.queryForObject(sql,new Object[]{status},new BeanPropertyRowMapper<>(StudentBean.class));
}
Copy the code

2 Query the List

public List<StudentBean> studentBeanList(int status){
    String sql = "select * from student where status = ?";
    return  jdbcTemplate.query(sql,new Object[]{status},new BeanPropertyRowMapper<>(StudentBean.class));
}
Copy the code

3. Delete the vm

public int deleteStudent(int status){
    String sql = "delete from student where status = 0";
    return jdbcTemplate.update(sql);
}
Copy the code

4. Update operations

public int updateStudent(int studentId,String name){
    String sql = "update student set name = ? where student_id = ?";
    return jdbcTemplate.update(sql,new Object[]{name,studentId});
}
Copy the code

5. Insert operations

public int addStudent(a){
    String sql = "insert into student(student_id,age,name,status) values(? ,? ,? ,?) ";
    return jdbcTemplate.update(sql,new Object[]{30.18."Java journey".0});
}
Copy the code

6. Batch insert

JdbcTemplate provides a batch insert API. To reduce operations with the database and improve the insert efficiency, the JdbcTemplate stores the data to be inserted in the buffer in batches and inserts multiple data at a time.

public int batchAddStudent(a){

    // Construct the list collection
    List<StudentBean> studentBeanList = new ArrayList<>();
    StudentBean studentBean = new StudentBean(31.31."Java journey".1.new Date(), 1);
    StudentBean studentBean1 = new StudentBean(32.32."javatrip".1.new Date(), 1);
    studentBeanList.add(studentBean);
    studentBeanList.add(studentBean1);
    String sql = "insert into student values(? ,? ,? ,? ,? ,?) ";

    int[] ints = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            StudentBean student = studentBeanList.get(i);
            ps.setInt(1, student.getStudentId());
            ps.setInt(2, student.getAge());
            ps.setString(3, student.getName());
            ps.setInt(4, student.getSex());
            ps.setDate(5.new java.sql.Date(System.currentTimeMillis()));
            ps.setInt(6, student.getStatus());
        }

        @Override
        public int getBatchSize(a) {
            returnstudentBeanList.size(); }});return ints.length;
}
Copy the code

These are just a few simple examples, but you can refer to the official JdbcTemplate API for more detailed usage. JdbcTemplate is an easy framework for beginners to use, and it is also very convenient to use. However, it also has some disadvantages. It hardcodes SQL into Java code. If the SQL needs to be modified, the Java code needs to be recompiled, which is not good for maintenance.

The sample code for this article has been uploaded togithub, point astarSupport!

Spring Boot series tutorial directory

Spring-boot-route (I) Several ways for Controller to receive parameters

Spring-boot-route (2) Several methods of reading configuration files

Spring-boot-route (3) Upload multiple files

Spring-boot-route (4) Global exception processing

Spring-boot-route (5) Integrate Swagger to generate interface documents

Spring-boot-route (6) Integrate JApiDocs to generate interface documents

Spring-boot-route (7) Integrate jdbcTemplate operation database

Spring-boot-route (8) Integrating mybatis operation database

Spring-boot-route (9) Integrate JPA operation database

Spring-boot-route (10) Switching between multiple data sources

Spring-boot-route (11) Encrypting database configuration information

Spring-boot-route (12) Integrate REDis as cache

Spring-boot-route RabbitMQ

Spring-boot-route Kafka

Spring-boot-route (15) Integrate RocketMQ

Spring-boot-route (16) Use logback to produce log files

Spring-boot-route (17) Use AOP to log operations

Spring-boot-route (18) Spring-boot-adtuator monitoring applications

Spring-boot-route (19) Spring-boot-admin Monitoring service

Spring-boot-route (20) Spring Task Implements simple scheduled tasks

Spring-boot-route (21) Quartz Implements dynamic scheduled tasks

Spring-boot-route (22) Enables email sending

Spring-boot-route (23) Developed wechat official accounts

Spring-boot-route (24) Distributed session consistency processing

Spring-boot-route (25) two lines of code to achieve internationalization

Spring-boot-route (26) Integrate webSocket

This series of articles are frequently used in the work of knowledge, after learning this series, to cope with daily development more than enough. If you want to know more, just scan the qr code below and let me know. I will further improve this series of articles!