一、JdbcTemplate的概述
二、JdbcTemplate的CRUD
1、创建maven项目并导入jar包的坐标
2、在domain包下创建UserInfo实体类
package com.wedu.spring09.domain;
import java.io.Serializable;
import java.util.Date;
/**
* 用户实体
*/
public class UserInfo implements Serializable {
private Integer id;
private String username;
private String password;
private Integer age;
private Date regtime;
private String siteaddress;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
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 Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getRegtime() {
return regtime;
}
public void setRegtime(Date regtime) {
this.regtime = regtime;
}
public String getSiteaddress() {
return siteaddress;
}
public void setSiteaddress(String siteaddress) {
this.siteaddress = siteaddress;
}
@Override
public String toString() {
return "UserInfo{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", age=" + age +
", regtime=" + regtime +
", siteaddress='" + siteaddress + '\'' +
'}';
}
}
3、在dao包下创建持久层接口IUserInfoDao并添加CRUD方法
package com.wedu.spring09.dao;
import com.wedu.spring09.domain.UserInfo;
import java.util.List;
/**
* 用户持久层接口
*/
public interface IUserInfoDao {
/**
* 查询所有用户
* @return
*/
List<UserInfo> findAllUserInfo();
/**
* 根据id查询用户
* @param id
* @return
*/
UserInfo findUserInfoById(Integer id);
/**
* 增加用户
* @param userInfo
*/
void saveUserInfo(UserInfo userInfo);
/**
* 修改用户
* @param userInfo
*/
void updateUserInfo(UserInfo userInfo);
/**
* 根据id删除用户
* @param id
*/
void deleteUserInfo(Integer id);
}
4、在dao包下创建持久层接口的实现类UserInfoDaoImpl并实现CRUD方法
package com.wedu.spring09.dao.impl;
import com.wedu.spring09.dao.IUserInfoDao;
import com.wedu.spring09.domain.UserInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
* 用户持久层实现
*/
@Repository("userInfoDao")
public class UserInfoDaoImpl implements IUserInfoDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public List<UserInfo> findAllUserInfo() {
return jdbcTemplate.query("select * from userinfo", new BeanPropertyRowMapper<UserInfo>(UserInfo.class));
}
@Override
public UserInfo findUserInfoById(Integer id) {
List<UserInfo> list = jdbcTemplate.query("select * from userinfo where id=?", new BeanPropertyRowMapper<UserInfo>(UserInfo.class), id);
if (list.isEmpty()) {
return null;
}
if (list.size() > 1) {
throw new RuntimeException("结果集不唯一");
}
return list.get(0);
}
@Override
public void saveUserInfo(UserInfo userInfo) {
jdbcTemplate.update("insert into userInfo(username,password,age,regtime,siteaddress)value(?,?,?,?,?)",
userInfo.getUsername(), userInfo.getPassword(), userInfo.getAge(), userInfo.getRegtime(), userInfo.getSiteaddress());
}
@Override
public void updateUserInfo(UserInfo userInfo) {
jdbcTemplate.update("update userInfo set username=?,password=?,age=?,regtime=?,siteaddress=? where id=?", userInfo.getUsername(),
userInfo.getPassword(), userInfo.getAge(), userInfo.getRegtime(), userInfo.getSiteaddress(), userInfo.getId());
}
@Override
public void deleteUserInfo(Integer id) {
jdbcTemplate.update("delete from userInfo where id=?", id);
}
}
5、在service包下创建业务层接口IUserInfoService并添加CRUD方法
package com.wedu.spring09.service;
import com.wedu.spring09.domain.UserInfo;
import java.util.List;
/**
* 用户的业务层接口
*/
public interface IUserInfoService {
/**
* 查询所有用户
* @return
*/
List<UserInfo> findAllUserInfo();
/**
* 根据id查询用户
* @param id
* @return
*/
UserInfo findUserInfoById(Integer id);
/**
* 增加用户
* @param userInfo
*/
void saveUserInfo(UserInfo userInfo);
/**
* 修改用户
* @param userInfo
*/
void updateUserInfo(UserInfo userInfo);
/**
* 根据id删除用户
* @param id
*/
void deleteUserInfo(Integer id);
}
6、在service包下创建业务层接口的实现类UserInfoServiceImpl并实现CRUD方法
package com.wedu.spring09.service.impl;
import com.wedu.spring09.dao.IUserInfoDao;
import com.wedu.spring09.domain.UserInfo;
import com.wedu.spring09.service.IUserInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* 用户的业务层实现
*/
@Service("userInfoService")
public class UserInfoServiceImpl implements IUserInfoService {
@Autowired
private IUserInfoDao userInfoDao;
@Override
public List<UserInfo> findAllUserInfo() {
return userInfoDao.findAllUserInfo();
}
@Override
public UserInfo findUserInfoById(Integer id) {
return userInfoDao.findUserInfoById(id);
}
@Override
public void saveUserInfo(UserInfo userInfo) {
userInfoDao.saveUserInfo(userInfo);
}
@Override
public void updateUserInfo(UserInfo userInfo) {
userInfoDao.updateUserInfo(userInfo);
}
@Override
public void deleteUserInfo(Integer id) {
userInfoDao.deleteUserInfo(id);
}
}
7、在resources下创建配置文件bean.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<!--告知spring在创建容器时需要扫描的包-->
<context:component-scan base-package="com.wedu.spring09"/>
<!--配置JdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg name="dataSource" ref="dataSource"/>
</bean>
<!--配置数据源-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test?useSSL=true&useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</bean>
</beans>
8、在测试模块中创建测试类UserInfoServiceTest类并编写测试方法测试
package com.wedu.spring09.service;
import com.wedu.spring09.domain.UserInfo;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.Date;
import java.util.List;
/**
* spring jdbcTemplate使用:基于注解的用户业务层CRUD测试
*/
public class UserInfoServiceTest {
private IUserInfoService userInfoService;
@Before
public void init() {
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
userInfoService = ac.getBean("userInfoService", IUserInfoService.class);
}
/**
* 查询所有账户
*/
@Test
public void findAllUserInfoTest() {
List<UserInfo> userInfoList = userInfoService.findAllUserInfo();
for (UserInfo userInfo : userInfoList) {
System.out.println(userInfo);
}
}
/**
* 添加账户
*/
@Test
public void saveUserInfoTest() {
UserInfo userInfo = new UserInfo();
userInfo.setUsername("test");
userInfo.setPassword("123456");
userInfo.setAge(6);
userInfo.setRegtime(new Date());
userInfo.setSiteaddress("https://blog.csdn.net/yu1755128147");
userInfoService.saveUserInfo(userInfo);
}
/**
* 根据id查询账户
*/
@Test
public void findUserInfoByIdTest() {
UserInfo userInfo = userInfoService.findUserInfoById(6);
System.out.println(userInfo);
}
/**
* 更新账户
*/
@Test
public void updateUserInfoTest() {
UserInfo userInfo = userInfoService.findUserInfoById(6);
userInfo.setUsername("测试");
userInfoService.updateUserInfo(userInfo);
}
/**
* 删除账户
*/
@Test
public void deleteUserInfoTest() {
userInfoService.deleteUserInfo(6);
}
}