Native jdbc integration with spring
Introduction to JDBC
The JDBC API allows users to access any form of tabular data, especially data stored in relational databases.
Implementation process:
- Connect to a data source, such as a database.
- Pass query and update instructions for the database.
- Process the database response and return the result.
First, you need to import the jar package, mysql-connector-java-6.0.6.jar, if you use maven to add some dependencies into pom.xml
<!--mysql connnector-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
<!--end-->
JDBC programming steps
- Load the driver:
- get database connection
- Create Statement\PreparedStatement objects:
loading classcom.mysql.jdbc.Driver". This has been deprecated. The new driver class is com.mysql.cj.jdbc.Driver'. The driver is automatically registered via SPI and there is usually no need to load the driver class manually.
Prepare the data sheet
CREATE DATABASE suveng DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
use suveng;
create table user(
id int NOT NULL PRIMARY KEY auto_increment,
name VARCHAR(50) ,
age int
) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Native JDBC example:
/**
* @author Veng Su [email protected]
* @date 2018/8/14 21:03
*/
public class DBUtils {
private String url;
private String username;
private String password;
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public static void main(String[] args) throws ClassNotFoundException, SQLException {
DBUtils dbUtils=new DBUtils();
dbUtils.setUrl("jdbc:mysql://localhost:3306/suveng?serverTimezone=Asia/Shanghai&characterEncoding=utf8");
dbUtils.setUsername("root");
dbUtils.setPassword("root");
// 1. Load Driver
Class.forName("com.mysql.cj.jdbc.Driver");
//2. Obtaining a database connection
Connection conn = DriverManager.getConnection(dbUtils.getUrl(), dbUtils.getUsername(), dbUtils.getPassword());
//3.Manipulate database to add, delete, and check
//Pre-compiled SQL to reduce sql execution
String sql="INSERT INTO user (name, age) VALUES ("suveng", 22)";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.executeUpdate();
ResultSet rs2 = stmt.executeQuery("SELECT * FROM user");
//If data is available, rs.next() returns true
while(rs2.next()){
System.out.println(rs2.getString("name")+" 年龄："+rs2.getInt("age"));
}
}
}
This is to use the native jdbc to link the database and increase the query operation, but our spring has encapsulated jdbc, which makes our operation easier.
Spring JDBC example
Programming steps:
- Configure datasource datasource
- Initialize jdbcTemplate
- Use jdbcTemplate to make additions, deletions and changes to the database
public class SpringJDBCDemo {
@Test
public void springJDBCTest() {
//Initialize the DataSource
DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
driverManagerDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
driverManagerDataSource.setUrl("jdbc:mysql://localhost:3306/suveng?serverTimezone=Asia/Shanghai&characterEncoding=utf8");
driverManagerDataSource.setUsername("root");
driverManagerDataSource.setPassword("root");
//Initialize jdbcTemplate
JdbcTemplate jdbcTemplate = new JdbcTemplate(driverManagerDataSource);
// Query a piece of data
User user1 = jdbcTemplate.queryForObject("select * from user where id = ?", new BeanMapper(), 1);
System.out.println(user1);
jdbcTemplate.update("delete from user where id = ?", 1);
jdbcTemplate.update("update user set name = ? where id = ?", "peter", 1);
jdbcTemplate.update("insert into user(name,age) values( "john", 10)");
// Query all data
List<User> list2 = jdbcTemplate.query("select * from user", new BeanMapper());
for (User user : list2) {
System.out.println(user);
}
// Query all data
List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from user");
for (Map<String, Object> map : list) {
for (String key : map.keySet()) {
System.out.print(map.get(key));
}
System.out.println();
}
}
private class BeanMapper implements org.springframework.jdbc.core.RowMapper<User> {
// When querying, it is possible to return multiple data, all of which will be placed in the rs result set
public User mapRow(ResultSet resultSet, int i) throws SQLException {
User user=new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setAge(resultSet.getInt("age"));
return user;
}
}
}
test:
operation result;
question
Why do you need to pass datasource when using jdbcTemplate? This can be explained by the class diagram, the initialization of jdbcTemplate requires a datasource interface, and the parent class of DriverManagerDateSource implements the dataSource interface, so it can be passed in to construct. As shown below.
DriverManagerDateSource
JdbcTemplate
Since the instance can be new, we should hand it over to spring to manage.
Configure DataSource and jdbcTemplate in beans.xml
<!--Configuring the DataSource-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/suveng?serverTimezone=Asia/Shanghai &characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</bean>
<!--Configuring jdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
Now that the configuration is complete, we can start injecting.
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring/config/beans.xml")
public class SpringJDBCDemo {
@Resource
JdbcTemplate jdbcTemplate;
@Test
public void springJDBCXMLTest(){
// Query a piece of data
User user1 = jdbcTemplate.queryForObject("select * from user where id = ?", new BeanMapper(), 1);
System.out.println(user1);
jdbcTemplate.update("delete from user where id = ?", 1);
jdbcTemplate.update("update user set name = ? where id = ?", "peter", 1);
jdbcTemplate.update("insert into user(name,age) values( ?, ?)", "john", 1000);
List<User> list2 = jdbcTemplate.query("select * from user", new BeanMapper());
for (User user : list2) {
System.out.println(user);
}
List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from user");
for (Map<String, Object> map : list) {
for (String key : map.keySet()) {
System.out.print(map.get(key));
}
System.out.println();
}
}
@Test
public void springJDBCTest() {
//Initialize the DataSource
DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
driverManagerDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
driverManagerDataSource.setUrl("jdbc:mysql://localhost:3306/suveng?serverTimezone=Asia/Shanghai&characterEncoding=utf8");
driverManagerDataSource.setUsername("root");
driverManagerDataSource.setPassword("root");
//Initialize jdbcTemplate
JdbcTemplate jdbcTemplate = new JdbcTemplate(driverManagerDataSource);
// Query a piece of data
User user1 = jdbcTemplate.queryForObject("select * from user where id = ?", new BeanMapper(), 1);
System.out.println(user1);
jdbcTemplate.update("delete from user where id = ?", 1);
jdbcTemplate.update("update user set name = ? where id = ?", "peter", 1);
jdbcTemplate.update("insert into user(name,age) values( "john", 10)");
List<User> list2 = jdbcTemplate.query("select * from user", new BeanMapper());
for (User user : list2) {
System.out.println(user);
}
List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from user");
for (Map<String, Object> map : list) {
for (String key : map.keySet()) {
System.out.print(map.get(key));
}
System.out.println();
}
}
private class BeanMapper implements org.springframework.jdbc.core.RowMapper<User> {
// When querying, it is possible to return multiple data, all of which will be placed in the rs result set
// rounum represents the following table value of the record
public User mapRow(ResultSet resultSet, int i) throws SQLException {
User user=new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setAge(resultSet.getInt("age"));
return user;
}
}
}
operation result:
Code cloud code address:
https://gitee.com/suwenguang/SpringFrameworkDemo
After donwnload comes down, src/main/java/spring/my/suveng/jdbc is the code source of this article. Others are citations from other articles.
in conclusion
the quick and easy way to use spring jdbc. More exploration of jdbc still takes time to study. come on