Spring JDBC 01 uses spring JDBC to operate database
1 What is spring JDBC
Spring simplifies the workload of JDBC programming by abstracting JDBC access and consistent APIs. We just need to declare the SQL, call the appropriate Spring JDBC framework API, and process the result set. Transactions are managed by Spring and JDBC checked exceptions are converted to Spring consistent unchecked exceptions, thus simplifying development.
2. Pre-preparation
2.1 jar package
Spring-jdbc: Spring JDBC package
MySQL: MySQL driver package
dbcp: database connection pool
Spring-webmvc: Spring MVC framework package
annotation:@resource needs to use the package, which is in Tomcat. If it's a web project and the running environment is Tomcat, you don't need to import the package.
junit: Unit Test Package
2.2 database (using mysql database 5.4)
1 # Create user tables
2 CREATE TABLE t_user (
3 user_id INT AUTO_INCREMENT PRIMARY KEY,
4 user_name VARCHAR (30),
5 credits INT,
6 password VARCHAR (32),
7 last_visit DATETIME,
8 last_ip VARCHAR(23)
9 ) ENGINE = InnoDB;
10
11 # Query the structure of t_user table
12 DESC t_user;
13
14 # Create user login log table
15 CREATE TABLE t_login_log (
16 login_log_id INT AUTO_INCREMENT PRIMARY KEY,
17 user_id INT,
18 ip VARCHAR (23),
19 login_datetime DATETIME
20 ) ENGINE = InnoDB;
21
22 #Query the structure of t_login_log table
23 DESC t_login_log;
24
25 INSERT INTO t_user
26 (user_name, password)
27 VALUES
28 ("wys", "182838" );
29
30 SELECT * FROM t_user;
Relevant tables
3 environment build (using eclipse)
3.1 Importing related jar packages using maven
1 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
2 <modelVersion>4.0.0</modelVersion>
3 <groupId>cn.xiangxu</groupId>
4 <artifactId>baobaotao</artifactId>
5 <version>0.0.1-SNAPSHOT</version>
6 <packaging>war</packaging>
7 <dependencies>
8 <dependency>
9 <groupId>org.springframework</groupId>
10 <artifactId>spring-webmvc</artifactId>
11 <version>3.2.8.RELEASE</version>
12 </dependency>
13 <dependency>
14 <groupId>mysql</groupId>
15 <artifactId>mysql-connector-java</artifactId>
16 <version>5.1.37</version>
17 </dependency>
18 <dependency>
19 <groupId>org.springframework</groupId>
20 <artifactId>spring-jdbc</artifactId>
21 <version>3.2.8.RELEASE</version>
22 </dependency>
23 <dependency>
24 <groupId>commons-dbcp</groupId>
25 <artifactId>commons-dbcp</artifactId>
26 <version>1.4</version>
27 </dependency>
28 <dependency>
29 <groupId>javax.annotation</groupId>
30 <artifactId>javax.annotation-api</artifactId>
31 <version>1.2</version>
32 </dependency>
33 <dependency>
34 <groupId>junit</groupId>
35 <artifactId>junit</artifactId>
36 <version>4.12</version>
37 </dependency>
38 </dependencies>
39 </project>
pom.xml
3.2 Create properties files for storing database-related information
1 driverClassName=com.mysql.jdbc.Driver
2 url=jdbc:mysql://127.0.0.1:3306/sampledb
3 username=root
4 password=182838
5 maxActive=10
6 maxWait=3000
mysql.properties
3.3 Create spring configuration file
3.3.1 Configure bean s for properties files
3.3.2 Configure database connection pool
3.3.3 Configuration of jdbcTemplate
3.3.4 Configuration Component Scan
1 <?xml version="1.0" encoding="UTF-8"?>
2 <beans xmlns="http://www.springframework.org/schema/beans"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
4 xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
5 xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"
6 xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:util="http://www.springframework.org/schema/util"
7 xmlns:jpa="http://www.springframework.org/schema/data/jpa"
8 xsi:schemaLocation="
9 http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
10 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
11 http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
12 http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd
13 http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
14 http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
15 http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
16 http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
17 http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd">
18
19 <!-- read mysql.properties file -->
20 <util:properties id="mysql" location="classpath:config/mysql.properties"/>
21
22 <!-- Configure connection pool -->
23 <bean id="ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
24 <property name="driverClassName" value="#{mysql.driverClassName}" />
25 <property name="url" value="#{mysql.url}" />
26 <property name="username" value="#{mysql.username}" />
27 <property name="password" value="#{mysql.password}" />
28 </bean>
29
30 <!-- To configure jdbcTemplate -->
31 <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
32 <property name="dataSource" ref="ds"></property>
33 </bean>
34
35 <!-- Component Scanning -->
36 <context:component-scan base-package="com.baobaotao"></context:component-scan>
37
38 </beans>
spring_mysql.xml
Note: We don't need to configure spirng's master controller because we only operate on the persistence layer; although we use spring containers, we can start containers by writing code
3.4 Project Structure Chart
4 Code to Implement Database Operation
4.1 Writing Entity Classes
1 package com.baobaotao.entity;
2
3 import java.io.Serializable;
4 import java.util.Date;
5
6 public class User implements Serializable {
7
8 private static final long serialVersionUID = -3573627859368072117L;
9
10 private Integer userId;
11 private String userName;
12 private Integer credits;
13 private String password;
14 private Date lastVisit;
15 private String lastIp;
16
17 public User() {
18 super();
19 // TODO Auto-generated constructor stub
20 }
21
22 public User(Integer userId, String userName, Integer credits, String password, Date lastVisit, String lastIp) {
23 super();
24 this.userId = userId;
25 this.userName = userName;
26 this.credits = credits;
27 this.password = password;
28 this.lastVisit = lastVisit;
29 this.lastIp = lastIp;
30 }
31
32 @Override
33 public int hashCode() {
34 final int prime = 31;
35 int result = 1;
36 result = prime * result + ((lastIp == null) ? 0 : lastIp.hashCode());
37 return result;
38 }
39 @Override
40 public boolean equals(Object obj) {
41 if (this == obj)
42 return true;
43 if (obj == null)
44 return false;
45 if (getClass() != obj.getClass())
46 return false;
47 User other = (User) obj;
48 if (lastIp == null) {
49 if (other.lastIp != null)
50 return false;
51 } else if (!lastIp.equals(other.lastIp))
52 return false;
53 return true;
54 }
55 public Integer getUserId() {
56 return userId;
57 }
58 public void setUserId(Integer userId) {
59 this.userId = userId;
60 }
61 public String getUserName() {
62 return userName;
63 }
64 public void setUserName(String userName) {
65 this.userName = userName;
66 }
67 public Integer getCredits() {
68 return credits;
69 }
70 public void setCredits(Integer credits) {
71 this.credits = credits;
72 }
73 public String getPassword() {
74 return password;
75 }
76 public void setPassword(String password) {
77 this.password = password;
78 }
79 public Date getLastVisit() {
80 return lastVisit;
81 }
82 public void setLastVisit(Date lastVisit) {
83 this.lastVisit = lastVisit;
84 }
85 public String getLastIp() {
86 return lastIp;
87 }
88 public void setLastIp(String lastIp) {
89 this.lastIp = lastIp;
90 }
91
92 @Override
93 public String toString() {
94 return "User [userId=" + userId + ", userName=" + userName + ", credits=" + credits + ", password=" + password
95 + ", lastVisit=" + lastVisit + ", lastIp=" + lastIp + "]";
96 }
97
98 }
User.java
1 package com.baobaotao.entity;
2
3 import java.io.Serializable;
4 import java.util.Date;
5
6 public class LoginLog implements Serializable {
7
8 private static final long serialVersionUID = 5176708814959439551L;
9
10 private Integer loginLogId;
11 private String userId;
12 private String ip;
13 private Date loginDatetime;
14 @Override
15 public int hashCode() {
16 final int prime = 31;
17 int result = 1;
18 result = prime * result + ((ip == null) ? 0 : ip.hashCode());
19 result = prime * result + ((loginDatetime == null) ? 0 : loginDatetime.hashCode());
20 result = prime * result + ((loginLogId == null) ? 0 : loginLogId.hashCode());
21 result = prime * result + ((userId == null) ? 0 : userId.hashCode());
22 return result;
23 }
24 @Override
25 public boolean equals(Object obj) {
26 if (this == obj)
27 return true;
28 if (obj == null)
29 return false;
30 if (getClass() != obj.getClass())
31 return false;
32 LoginLog other = (LoginLog) obj;
33 if (ip == null) {
34 if (other.ip != null)
35 return false;
36 } else if (!ip.equals(other.ip))
37 return false;
38 if (loginDatetime == null) {
39 if (other.loginDatetime != null)
40 return false;
41 } else if (!loginDatetime.equals(other.loginDatetime))
42 return false;
43 if (loginLogId == null) {
44 if (other.loginLogId != null)
45 return false;
46 } else if (!loginLogId.equals(other.loginLogId))
47 return false;
48 if (userId == null) {
49 if (other.userId != null)
50 return false;
51 } else if (!userId.equals(other.userId))
52 return false;
53 return true;
54 }
55 public Integer getLoginLogId() {
56 return loginLogId;
57 }
58 public void setLoginLogId(Integer loginLogId) {
59 this.loginLogId = loginLogId;
60 }
61 public String getUserId() {
62 return userId;
63 }
64 public void setUserId(String userId) {
65 this.userId = userId;
66 }
67 public String getIp() {
68 return ip;
69 }
70 public void setIp(String ip) {
71 this.ip = ip;
72 }
73 public Date getLoginDatetime() {
74 return loginDatetime;
75 }
76 public void setLoginDatetime(Date loginDatetime) {
77 this.loginDatetime = loginDatetime;
78 }
79 public LoginLog() {
80 super();
81 // TODO Auto-generated constructor stub
82 }
83 public LoginLog(Integer loginLogId, String userId, String ip, Date loginDatetime) {
84 super();
85 this.loginLogId = loginLogId;
86 this.userId = userId;
87 this.ip = ip;
88 this.loginDatetime = loginDatetime;
89 }
90 @Override
91 public String toString() {
92 return "LoginLog [loginLogId=" + loginLogId + ", userId=" + userId + ", ip=" + ip + ", loginDatetime="
93 + loginDatetime + "]";
94 }
95
96 }
LoginLog.java
4.2 Writing UserDao Interface
1 package com.baobaotao.dao;
2
3 import java.util.List;
4
5 import com.baobaotao.entity.User;
6
7 public interface UserDao {
8 /**
9 * Adding records to user tables
10 * @param user User Table Entity Objects
11 */
12 public void insert(User user);
13
14 /**
15 * Query all user data
16 * @return A collection of queries to records
17 */
18 public List<User> findAll();
19
20 }
UserDao.java
4.3 Write the implementation class UserDaoImpl of UserDao interface
1 package com.baobaotao.dao;
2
3 import java.sql.ResultSet;
4 import java.sql.SQLException;
5 import java.util.List;
6
7 import javax.annotation.Resource;
8
9 import org.springframework.jdbc.core.JdbcTemplate;
10 import org.springframework.jdbc.core.RowMapper;
11 import org.springframework.stereotype.Repository;
12
13 import com.baobaotao.entity.User;
14
15 @Repository("userDao")
16 public class UserDaoImpl implements UserDao {
17
18 @Resource(name="jt")
19 private JdbcTemplate jt;
20
21 public void insert(User user) {
22
23 String sql = "INSERT INTO t_user " +
24 "(user_name, password) " +
25 "VALUES " +
26 "(?, ?) ";
27 Object [] args = {user.getUserName(), user.getPassword()};
28 Integer num = jt.update(sql, args);
29 if(num > 0) {
30 System.out.println("Successful insertion of data");
31 } else {
32 System.out.println("Failure to insert data");
33 }
34
35 }
36
37 public List<User> findAll() {
38 String sql = "SELECT * FROM t_user ";
39 List<User> users = jt.query(sql, new UserRowMapper());
40 return users;
41 }
42
43 /**
44 * Result Set Processing: Each query record is transformed into an entity object
45 * @author Three less
46 *
47 */
48 class UserRowMapper implements RowMapper<User> {
49
50 public User mapRow(ResultSet rs, int rowNum) throws SQLException {
51 User user = new User();
52 user.setUserId(rs.getInt("user_id"));
53 user.setUserName(rs.getString("user_name"));
54 user.setCredits(rs.getInt("credits"));
55 user.setPassword(rs.getString("password"));
56 user.setLastVisit(rs.getDate("last_visit"));
57 user.setLastIp(rs.getString("last_ip"));
58 return user;
59 }
60 }
61
62 }
UserDaoImpl
When using spring JDBC to operate database, programmers only need to write relevant sql statements, and the parameters to be determined can be used? Instead, call the relevant methods of the JdbcTemplate class to execute the sql statement.
Main methods in the JdbcTemplate class
Excute method: can be used to execute any SQL statement, generally used to execute DDL statements;
update method and batchUpdate method: update method is used to execute new, modified, deleted statements, etc.
The batchUpdate method is used to execute batch-related statements;
Query method and queryForXXX method: used to execute query-related statements
call method: used to execute stored procedures, function-related statements.
Attention:
There are three parameters when using the query method
Parametric 1 sql statement string
Parametric 2. Unknown parameter groups
Parametric 3. Query result processing (that is to say, every query record becomes an entity object, and the general practice of less than three is to write an internal class that implements the RowMapper interface, and then create an internal class object as parameter 3)
4.4 Project Structure Chart
5 test classes
5.1 The spring container needs to be started before testing because the spring container function is used in our code
5.2 Writing test methods
1 package testDao;
2
3 import java.util.List;
4
5 import org.junit.Before;
6 import org.junit.Test;
7 import org.springframework.context.ApplicationContext;
8 import org.springframework.context.support.ClassPathXmlApplicationContext;
9
10 import com.baobaotao.dao.UserDao;
11 import com.baobaotao.entity.User;
12
13 public class TestDao {
14
15 private ApplicationContext ac;
16 private UserDao userDao;
17
18 @Before
19 public void init() {
20 ac = new ClassPathXmlApplicationContext("config/spring_mysql.xml"); // Start container
21 System.out.println(ac);
22
23 userDao = ac.getBean("userDao", UserDao.class); // Creating Objects with Containers
24 System.out.println(userDao);
25 }
26
27 /**
28 * Test insertion data
29 */
30 @Test
31 public void test01() {
32 User user = new User();
33 user.setUserName("wym");
34 user.setPassword("111");
35
36 userDao.insert(user);
37 }
38
39 /**
40 * Test and query all data
41 */
42 @Test
43 public void test02() {
44 List<User> users = userDao.findAll();
45 for(User user : users) {
46 System.out.println(user);
47 }
48 }
49
50
51 }
Test class
Keywords:
Java
Spring
JDBC
MySQL
Added by gigya on Thu, 13 Jun 2019 21:08:46 +0300