class=”markdown_views prism-atom-one-dark”>

Know Java’s database connection template JDBCTemplate

Get to know JDBCTemplate

Learn about JDBC

JDBC (Java Database Connectivity), which is Java’s specification for connecting to databases, is the Java API for executing database SQL statements.
JDBC can connect to various databases because it provides a unified access interface, which is also in line with the pattern of Java program interface design.

JDBC needs to connect to the database every time, and then issue SQL statements, pass values, and close the database. Such a process operation, once a certain step is forgotten, many problems will arise, so JDBCTemplate was designed.

Learn about JDBCTemplate

JDBCTemplate = JDBC + Template is a combination of JDBC. It is more convenient for program implementation and completes all JDBC underlying operations for us. Therefore, for the operation of the database, there is no need to connect, open, and close each time.

JDBC and JDBCTemplate are like warehouse managers, responsible for accessing items from the warehouse (database). The latter uses “electric door automatic control”

JDBCTemplate is more cumbersome to implement than ORM, so most development uses ORM (JPA and MyBatis ). But JDBCTemplate still has a market because of the low learning cost.

Example: Use JDBCTemplate to realize data addition, deletion, modification and query

Configure basic dependencies

 
         dependency
             groupIdorg.springframework.boot</groupId
             artifactIdspring-boot-starter-jdbc</artifactId  
         </dependency

         
         dependency
             groupIdmysql</groupId>
             artifactIdmysql-connector-java</artifactId
             scoperuntime</scope>
         </dependency
 

After adding, you also need to configure the database connection information. In this way, JDBCTemplate can connect to the database normally.
Configure the database address and user information in the application.properties configuration file.

# MySql database information
     # Driver address
 spring.datasource.driver-class-name=com.mysql.cj.jdbc.  Driver
     # Configure IP address, encoding, time zone
 spring.datasource.url=jdbc:mysql://localhost:3306/book?useUnicode=true&  characterEncoding=utf-8&serverTimezone=UTC  &useSSL=true
     # username
 spring.datasource.username=root
     # Password There is no password here because the database itself does not have a password
 spring.datasource.password=
 

Create a new entity class

Create a new test entity class User, implement the RowMapper class, and re-mapRow method to map entity fields and data table fields. (There are SQL commands to create data tables below)

package com.example.model;

 import lombok.Data;
 import org.springframework.jdbc.core.RowMapper;

 import java.sql.ResultSet;
 import java.sql.SQLException;

 @Data
 public class User implements RowMapperUser> {
     
     private Integer id;
     private String username;
     private String password;

     @Override
     public User mapRow(ResultSet resultSet, int i) throws  span> SQLException {
     
         User user = new User();
         user.setId(resultSet.getInt("id"));
         user.setUsername(resultSet.getString("username"));
         user.setPassword(resultSet.getString("password"));
         return user;
     }
 }

 

Manipulating data

JDBCTemplate provides the following 3 methods for manipulating data.

• execute: means “execute”, which is used to directly execute SQL statements.
• updata: means “update”, including adding, modifying, and deleting operations.
• query: means “query”.

Create data table, add, query, delete

Before using JDBCTemplate, you need to inject JDBCTemplate into the controller, and then you can execute SQL operations through the execute method.

package com.example.test;

 import com.example.model.User;
 import org.junit.jupiter.api.Test;
 import org.springframework.beans.factory.annotation.Autowired;  
 import org.springframework.boot.test.context.SpringBootTest;  
 import org.springframework.jdbc.core.JdbcTemplate;

 import java.util.List;

 @SpringBootTest
 public class UserControllerTest {
     
     @Autowired
     private JdbcTemplate jdbcTemplate;

     // create table
     @Test
     public void createUserTable() throws Exception {
     
         String sql = "CREATE TABLE `user`(\n" +
                 "`id` int(10) NOT NULL AUTO_INCREMENT,\n" +
                 "`username` varchar(100) DEFAULT NULL,\n" +
                 "`password` varchar(100) DEFAULT NULL,\n" +
                 "PRIMARY KEY(`id`)\n" +
                 ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;\n" +
                 "\n";
         jdbcTemplate.execute(sql);
     }

     //Add data
     @Test
     public void saveUserTest() throws Exception {
     
         String sql = "INSERT INTO user (USERNAME, PASSWORD) VALUES ('Zhang San', '123456');";
         Integer rows = jdbcTemplate.update(sql);
         System.out.println(rows);
     }

     // query data
     @Test
     public void getUserByName() throws Exception {
     
         String name = "Zhang San";
         String sql = "SELECT * FROM user WHERE USERNAME = ?";
         ListUser list = jdbcTemplate.query(sql, new User(), name)  ;
         for (User user : list) {
     
             System.out.println(user);
         }
     }

     // query all data
     @Test
     public void list() throws Exception {
     
         String sql = "SELECT * FROM user LIMIT 0,1000";
         ListUser userList = jdbcTemplate.query(sql, new User());
         for (Object user : userList) {
     
             System.out.println(user);
         }
     }

     // data modification
     @Test
     public void updateUserPassword() throws Exception {
     
         Integer id = 1;
         String password = "99998888";
         String sql = "UPDATE user SET PASSWORD = ? WHERE ID = ?"  ;
         Integer rows = jdbcTemplate.update(sql, password, id)  ;
         System.out.println(rows);
     }

     // data deletion
     @Test
     public void deleteUserById() throws Exception {
     
         Integer id = 1;
         String sql = "DELETE FROM user WHERE ID = ?";
         Integer rows = jdbcTemplate.update(sql, id);
         System.out.println(rows);
     }
 }
 

Get to know ORM

ORM (Object Relational Mapping) is Object/Relational Mapping. It provides a conceptual and easy-to-understand data model, which establishes a mapping relationship between tables in the database and memory objects . It is produced with the development of object-oriented software development method, and object-oriented development method is still the current mainstream development method.

object and relational data are two representations of business entities. Business entities are represented as objects in memory, and as relational data in database. Objects in memory are not persisted, only objects in relational databases (or NoSQL databases, or files) are persisted.

Object/relational mapping (ORM) systems generally exist in the form of middleware, because there are associations and inheritance relationships between objects in memory, while in databases, relational data cannot directly express many-to-many associations and inheritance relation. A relational diagram of objects, databases mapped through ORM.

The more commonly used ORMs are JPA popular abroad and MyBatis popular in China.

The main source of this article: Title: Chapter 8 of “Spring Boot Practical School”; Author: Long Zhonghua; Publisher: China Industry and Information Technology Press.

n>(user);
}
}

// data modification
@Test
public void updateUserPassword() throws Exception {

Integer id = 1;
String password = “99998888”;
String sql = “UPDATE user SET PASSWORD = ? WHERE ID = ?” ;
Integer rows = jdbcTemplate.update(sql, password, id) ;
System.out.println(rows);
}

// data deletion
@Test
public void deleteUserById() throws Exception {

Integer id = 1;
String sql = “DELETE FROM user WHERE ID = ?”;
Integer rows = jdbcTemplate.update(sql, id);
System.out.println(rows);
}
}

Get to know ORM

ORM (Object Relational Mapping) is Object/Relational Mapping. It provides a conceptual and easy-to-understand data model, which establishes a mapping relationship between tables in the database and memory objects . It is produced with the development of object-oriented software development method, and object-oriented development method is still the current mainstream development method.

object and relational data are two representations of business entities. Business entities are represented as objects in memory, and as relational data in database. Objects in memory are not persisted, only objects in relational databases (or NoSQL databases, or files) are persisted.

Object/relational mapping (ORM) systems generally exist in the form of middleware, because there are associations and inheritance relationships between objects in memory, while in databases, relational data cannot directly express many-to-many associations and inheritance relation. A relational diagram of objects, databases mapped through ORM.

The more commonly used ORMs are JPA popular abroad and MyBatis popular in China.

The main source of this article: Title: Chapter 8 of “Spring Boot Practical School”; Author: Long Zhonghua; Publisher: China Industry and Information Technology Press.