I believe everyone is familiar with Oracle. It is a large database. As for the database, I believe you are all familiar with it. As a software system, whether it is an App, a small program, or a traditional web site that we often make, we all have users. Information and related business data are usually stored in related databases, such as MySQL, Oracle, SQL server, etc.
By the time you read this article, I believe you already have enough understanding of Spring Boot. In this document, I will use Oracle database for data storage. PS: There are syntax differences between Oracle database and MySQL. , although the overall structure is the same, there are still different syntax.
Next we officially start:
JdbcTemplate
JdbcTemplate is a core class in the Spring framework, used to simplify the interaction between Java applications and relational databases. It provides a simple and flexible way to perform database operations such as SQL queries, updates, and stored procedure calls.
JdbcTemplate encapsulates some common database operations, such as querying single or multiple rows of data, inserting, updating and deleting data, etc. It communicates with the database using JDBC (Java Database Connectivity) and provides some convenient methods to process result sets, handle exceptions, and perform transaction operations.
Using JdbcTemplate can reduce the workload of writing repeated JDBC code and improve development efficiency. It also provides some advanced features, such as named parameters, batch operations, and mapping of query results, making database operations more convenient and easier to maintain.
Data source configuration
Here, let me explain that when I wrote this article this year, I found that Oracle11 had stopped updating, and Oracle 21 is relatively new on the official website, so I used Oracle 19c. If you use Oracle yourself, it is still relatively old. Version, please change it to the version you correspond to. Different versions will have corresponding differences:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!--Import jdbc dependencies--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>com.oracle.ojdbc</groupId> <artifactId>ojdbc8</artifactId> <version>19.3.0.0</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency>
Next, add data source information in src/main/resources/application.properties created by our Spring Boot.
# Mysql configuration #spring.datasource.url=jdbc:mysql://localhost:3306/test #spring.datasource.username=root #spring.datasource.password=123456 #spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver #Oracle configuration spring.datasource.url=jdbc:oracle:thin:@localhost:1521/orcl spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
PS: MySQL 8 driver is used by default in Spring Boot 2.x, so cj is added above
Use JdbcTemplate to operate the database
Spring’s JdbcTemplate is automatically configured. We can use @Autowried or the constructor or the set method to inject it into the bean we want.
The database we created in Oracle is actually the relevant user we created. We use the account and password of the user we created to log in, and then create tables under the user.
The following is the SQL statement of the table created after I created the root user and then logged in using the root user’s account and password.
CREATE TABLE useradd ( name varchar2 (100) NOT NULL, age integer NOT NULL )
Next, we create entity objects based on the tables created in the database:
public class User {<!-- -->
private String name;
private Integer age;
public String getName() {<!-- -->
return name;
}
public void setName(String name) {<!-- -->
this.name = name;
}
public Integer getAge() {<!-- -->
return age;
}
public void setAge(Integer age) {<!-- -->
this.age = age;
}
public User(String name, Integer age) {<!-- -->
this.name = name;
this.age = age;
}
public User() {<!-- -->
}
@Override
public boolean equals(Object o) {<!-- -->
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
User user = (User) o;
return Objects.equals(name, user.name) & & Objects.equals(age, user.age);
}
@Override
public int hashCode() {<!-- -->
return Objects.hash(name, age);
}
@Override
public String toString() {<!-- -->
return "User{" +
"name='" + name + ''' +
", age=" + age +
'}';
}
}
Create related interfaces
public interface UserService {<!-- -->
/**
* Add a new user
*
* @param name
* @param age
*/
int create(String name, Integer age);
/**
* Query users based on name
*
* @param name
* @return
*/
List<User> getByName(String name);
/**
* Delete users based on name
*
* @param name
*/
int deleteByName(String name);
/**
* Get the total number of users
*/
int getAllUsers();
/**
* Delete all users
*/
int deleteAllUsers();
}
After that, we implement the data access operations in the interface through jdbcTemplate:
@Service
public class UserServiceImpl implements UserService {<!-- -->
private JdbcTemplate jdbcTemplate;
UserServiceImpl(JdbcTemplate jdbcTemplate) {<!-- -->
this.jdbcTemplate = jdbcTemplate;
}
@Override
public int create(String name, Integer age) {<!-- -->
return jdbcTemplate.update("insert into USERADD(NAME, AGE) values(?, ?)", name, age);
}
@Override
public List<User> getByName(String name) {<!-- -->
List<User> users = jdbcTemplate.query("select NAME, AGE from USERADD where NAME = ?", (resultSet, i) -> {<!-- -->
User user = new User();
user.setName(resultSet.getString("NAME"));
user.setAge(resultSet.getInt("AGE"));
return user;
}, name);
return users;
}
@Override
public int deleteByName(String name) {<!-- -->
return jdbcTemplate.update("delete from USERADD where NAME = ?", name);
}
@Override
public int getAllUsers() {<!-- -->
return jdbcTemplate.queryForObject("select count(1) from USERADD", Integer.class);
}
@Override
public int deleteAllUsers() {<!-- -->
return jdbcTemplate.update("delete from USERADD");
}
}
Then we use Spring Boot’s unit test to create a test case to see whether we are operating the database correctly through creation, deletion, and query.
@RunWith(SpringRunner.class)
@SpringBootTest
public class Chapter31ApplicationTests {<!-- -->
@Autowired
private UserService userSerivce;
@Before
public void setUp() {<!-- -->
// Prepare and clear the user table
userSerivce.deleteAllUsers();
}
@Test
public void test() throws Exception {<!-- -->
//Insert 5 users
userSerivce.create("miaow", 10);
userSerivce.create("jjkeo", 11);
userSerivce.create("cfase", 30);
userSerivce.create("okeda", 21);
userSerivce.create("joke", 17);
// Query the user named Oscar and determine whether the age matches
List<User> userList = userSerivce.getByName("joke");
Assert.assertEquals(17, userList.get(0).getAge().intValue());
// Check the database, there should be 5 users
Assert.assertEquals(5, userSerivce.getAllUsers());
//Delete two users
userSerivce.deleteByName("jjkeo");
userSerivce.deleteByName("cfase");
// Check the database, there should be 5 users
Assert.assertEquals(3, userSerivce.getAllUsers());
}
}
We found that it was successful. Through the simple example above, we can see that the configuration of accessing the database under Spring Boot still adheres to the original intention of the framework: simplicity.
We only need to add database dependencies to pom.xml, and then configure the connection information in application.properties. We do not need to create JdbcTemplate beans like in Spring applications, and can directly inject them into our own objects. .