Spring-data-jdbcTemplate案例

本節主要學習Spring-data針對jdbc的封裝JdbcTemplate技術。

1.配置坐標

新建maven項目，pom.xml中配置坐標：

<dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>5.2.7.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>5.2.7.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.2.7.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>5.2.7.RELEASE</version> </dependency> <!-- hikaricp --> <dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> <version>3.4.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.48</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjweaver</artifactId> <version>1.9.5</version> </dependency> <dependency> <groupId>org.junit.jupiter</groupId> <artifactId>junit-jupiter-api</artifactId> <version>5.7.0-M1</version> <scope>test</scope> </dependency> <!--日誌框架--> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.7.25</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-simple</artifactId> <version>1.7.30</version> <scope>compile</scope> </dependency> </dependencies>

2.編寫配置文件

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driverjdbc.url=jdbc:mysql://127.0.0.1:3306/cms?useUnicode=true&characterEncoding=utf8&useSSL=truejdbc.username=rootjdbc.password=rootreadOnly=falseconnectionTimeout=30000idleTimeout=600000maxLifetime=1800000maximumPoolSize=15

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd"> <!-- 定義掃描註解所在的包 --> <context:component-scan base-package="raky.train" /> <!--1. 導入資源文件，屬性配置文件 --> <context:property-placeholder location="classpath:jdbc.properties" /> <!--2. 配置數據源,連接池：dbcp/dbcp2/c3p0/druid/HikariCP --> <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource"> <property name="driverClassName" value="${jdbc.driver}" /> <property name="jdbcUrl" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> <property name="readOnly" value="${readOnly}" /> <property name="connectionTimeout" value="${connectionTimeout}" /> <property name="idleTimeout" value="${idleTimeout}" /> <property name="maxLifetime" value="${maxLifetime}" /> <property name="maximumPoolSize" value="${maximumPoolSize}" /> </bean> <!-- 3.集成jdbcTemplate : spring對jdbc的輕度封裝，相當於apache dbutils --> <!-- jdbcTemplate 相當於Connection[jdbc]，QueryRunner[dbutils] --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource" /> </bean></beans>

3.編寫實體類

User.java

package raky.train.entity;import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;@Data@Builder@NoArgsConstructor@AllArgsConstructorpublic class User { private Integer id; private String name; private String pass; private Integer age;}

4.編寫持久化層代碼

UserDao.java

package raky.train.dao;import java.util.List;import raky.train.entity.User;public interface UserDao { int insert(User user); int update(User user); int delete(Integer id); User getOne(Integer id); List<User> getList(User user);}

UserDaoImpl.java

package raky.train.dao.impl;import org.springframework.context.annotation.Primary;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.support.JdbcDaoSupport;import org.springframework.stereotype.Repository;import raky.train.dao.UserDao;import raky.train.entity.User;import java.util.List;@Primary@Repositorypublic class UserDaoImpl extends JdbcDaoSupport implements UserDao{ public UserDaoImpl(JdbcTemplate jdbcTemplate){ super.setJdbcTemplate(jdbcTemplate); } @Override public int insert(User user) { final String sql = "insert into user(name, pass, age)values(?, ?, ?)"; return super.getJdbcTemplate() .update(sql,user.getName(), user.getPass(), user.getAge()); } @Override public int update(User user) { final String sql = "update user set name=?, pass=?, age=? where id= ?"; return super.getJdbcTemplate() .update(sql, user.getName(), user.getPass(), user.getAge(), user.getId()); } @Override public int delete(Integer id) { final String sql = "delete from user where id=?"; return super.getJdbcTemplate().update(sql,id); } @Override public User getOne(Integer id) { final String sql = "select * from user where id = ?"; return super.getJdbcTemplate() .queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id); } @Override public List<User> getList(User user) { final String sql = "select * from user"; return super.getJdbcTemplate() .query(sql, new BeanPropertyRowMapper<User>(User.class)); }}

3.5編寫服務層代碼

UserService.java

package raky.train.service;import java.util.List;import raky.train.entity.User;public interface UserService { int insert(User user); int update(User user); int delete(Integer id); User getOne(Integer id); List<User> getList(User user);}

UserServiceImpl.java

package raky.train.service.impl;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import raky.train.dao.UserDao;import raky.train.entity.User;import raky.train.service.UserService;@Servicepublic class UserServiceImpl implements UserService { @Autowired private UserDao userDao; @Override public int insert(User user) { return userDao.insert(user); } @Override public int update(User user) { return userDao.update(user); } @Override public int delete(Integer id) { return userDao.delete(id); } @Override public User getOne(Integer id) { return userDao.getOne(id); } @Override public List<User> getList(User user) { return userDao.getList(user); }}

3.6編寫控制層代碼

UserController.java

package raky.train.controller;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import raky.train.entity.User;import raky.train.service.UserService;@Controllerpublic class UserController { @Autowired private UserService userService; public int insert(User user) { return userService.insert(user); } public int update(User user) { return userService.update(user); } public int delete(Integer id) { return userService.delete(id); } public User getOne(Integer id) { return userService.getOne(id); } public List<User> getList(User user) { return userService.getList(user); }}

3.7編寫測試代碼

UserControllerTest.java

package raky.train.controller;import lombok.extern.slf4j.Slf4j;import org.junit.jupiter.api.Test;import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;import raky.train.entity.User;import javax.annotation.Resource;import java.util.List;import static org.junit.jupiter.api.Assertions.*;@Slf4j@SpringJUnitConfig(locations = {"classpath:applicationContext.xml"})public class UserControllerTest { @Resource private UserController userController; @Test void insert() { User user = User.builder().name("abc").pass("abc").build(); int result = userController.insert(user); log.info("===>user:{}",user); log.info("===>{}",result); assertEquals(result,1); } @Test void update() { User user = User.builder().id(22).name("cba").pass("cba").age(99).build(); int result = userController.update(user); log.info("===>{}",result); assertEquals(result,1); } @Test void delete() { int result = userController.delete(333); log.info("===>{}",result); assertEquals(result,0); } @Test void getOne() { User user = userController.getOne(1); log.info("===>{}",user); assertNotNull(user); } @Test void getList() { User user = User.builder().build(); List<User> userList = userController.getList(user); userList.forEach(x -> log.debug("===>{}",x)); }}

3.8調試運行

調試運行測試代碼，結果如下：

至此，Spring-data-jdbcTemplate案例學習筆記整理完畢。希望對大家有幫助。越分享越快樂。