JdbcTemplate概念
Spring框架对JDBC进行封装,使用JdbcTemplate方便实现对数据库的操作
引入依赖
配置信息
<?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">
<!-- 注入组件扫描类 -->
<context:component-scan base-package="com.company.base.spring8"/>
<!-- 引入外部配置文件 -->
<context:property-placeholder location="classpath:orm.properties"/>
<!-- 注入数据源信息 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!-- 注入jdbcTemplate的操作bean -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=http://localhost:3306/test
jdbc.username=root
jdbc.password=123456
创建service与dao操作类,并注入JdbcTemplate
@Service
public class UserService {
@Autowired
private UserDao userDao;
}
public interface UserDao {
}
@Repository
public class UserDaoImpl implements UserDao{
@Autowired
private JdbcTemplate jdbcTemplate;
}
创建一个测试表
直接利用库里现有的表
利用JdbcTemplate进行添加操作
为了方便,创建一个与user字段进行对应的userPo
public class UserPo {
private String id;
private String email;
private String nickName;
private String passWord;
private String regTime;
private String userName;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getNickName() {
return nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName;
}
public String getPassWord() {
return passWord;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
public String getRegTime() {
return regTime;
}
public void setRegTime(String regTime) {
this.regTime = regTime;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
@Override
public String toString() {
return "UserPo{" +
"id='" + id + '\'' +
", email='" + email + '\'' +
", nickName='" + nickName + '\'' +
", passWord='" + passWord + '\'' +
", regTime='" + regTime + '\'' +
", userName='" + userName + '\'' +
'}';
}
}
@Service
public class UserService {
@Autowired
private UserDao userDao;
public int add(UserPo userPo) {
return userDao.add(userPo);
}
}
public interface UserDao {
int add(UserPo userPo);
}
@Repository
public class UserDaoImpl implements UserDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int add(UserPo userPo) {
String sql = "insert into user(id,email,nick_name,pass_word,reg_time,user_name) values(?,?,?,?,?,?)";
int update = jdbcTemplate.update(sql, new Object[]{userPo.getId(), userPo.getEmail(), userPo.getNickName(), userPo.getPassWord(), userPo.getRegTime(), userPo.getUserName()});
return update;
}
}
public class TestJdbcTemplate {
@Test
public void test() {
ApplicationContext context = new ClassPathXmlApplicationContext("orm.xml");
UserService userService = context.getBean("userService", UserService.class);
UserPo userPo = new UserPo();
userPo.setId("1");
userPo.setEmail("zhangsan@qq.com");
userPo.setNickName("zhangsan");
userPo.setPassWord("zhangsan");
userPo.setRegTime("2022-10-01");
userPo.setUserName("张三");
int add = userService.add(userPo);
System.out.println(add);
}
}
测试结果
利用JdbcTemplate进行修改操作
@Override
public int update(UserPo userPo) {
String sql = "update user set email = ?,nick_name=?,pass_word=?,reg_time=?,user_name = ? where id=?";
int update = jdbcTemplate.update(sql, new Object[]{userPo.getEmail(), userPo.getNickName(), userPo.getPassWord(), userPo.getRegTime(), userPo.getUserName(),userPo.getId()});
return update;
}
@Test
public void test2() {
ApplicationContext context = new ClassPathXmlApplicationContext("orm.xml");
UserService userService = context.getBean("userService", UserService.class);
UserPo userPo = new UserPo();
userPo.setId("1");
userPo.setEmail("zhangsan1@qq.com");
userPo.setNickName("zhangsan1");
userPo.setPassWord("zhangsan1");
userPo.setRegTime("2022-10-01");
userPo.setUserName("张三1");
int update = userService.update(userPo);
System.out.println(update);
}
测试结果
利用JdbcTemplate进行删除操作
@Override
public int delete(String userId) {
String sql = "delete from user where id=?";
int update = jdbcTemplate.update(sql, new Object[]{userId});
return update;
}
@Test
public void test3() {
ApplicationContext context = new ClassPathXmlApplicationContext("orm.xml");
UserService userService = context.getBean("userService", UserService.class);
int update = userService.delete("1");
System.out.println(update);
}
测试结果
利用JdbcTemplate进行查询操作
现有数据
1、用jdbc查询单个值
queryForObject(String sql, Class requiredType)
第一个参数:执行的sql语句
第二个参数:返回值类型的class
@Override
public int findCount() {
String sql = "select count(id) from user";
int count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
@Test
public void test4() {
ApplicationContext context = new ClassPathXmlApplicationContext("orm.xml");
UserService userService = context.getBean("userService", UserService.class);
int count = userService.findCount();
System.out.println("count=======" + count);
}
测试结果
2、用jdbc查询某个对象
queryForObject(String sql, RowMapper rowMapper, @Nullable Object… args)
第一个参数:执行的sql语句
第二个参数:RowMapper是接口,针对返回不同类型数据,使用这个接口里面实现类完成数据封装
第三个参数:sql的使用参数
@Override
public UserPo findOne(String userId) {
String sql = "select * from user where id = ?";
UserPo userPo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<UserPo>(UserPo.class), new Object[]{userId});
return userPo;
}
@Test
public void test5() {
ApplicationContext context = new ClassPathXmlApplicationContext("orm.xml");
UserService userService = context.getBean("userService", UserService.class);
UserPo one = userService.findOne("1");
System.out.println("po=======" + one.toString());
}
3、用jdbc查询对象集合
query(String sql, RowMapper rowMapper)
第一个参数:执行的sql语句
第二个参数:RowMapper是接口,针对返回不同类型数据,使用这个接口里面实现类完成数据封装
@Override
public List<UserPo> findAll() {
String sql = "select * from user";
List<UserPo> userPoList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<UserPo>(UserPo.class));
return userPoList;
}
@Test
public void test6() {
ApplicationContext context = new ClassPathXmlApplicationContext("orm.xml");
UserService userService = context.getBean("userService", UserService.class);
List<UserPo> userPoList = userService.findAll();
System.out.println("list=======" + userPoList);
}
测试结果
批量操作
batchUpdate(String sql, List<Object[]> batchArgs)
第一个参数:执行的sql语句
第二个参数:对象数组集合,底层会将集合中的对象数组循环取出并执行
1、批量新增
// 批量新增
@Override
public int[] batchAdd(List<Object[]> list) {
String sql = "insert into user(id,email,nick_name) value(?,?,?)";
return jdbcTemplate.batchUpdate(sql, list);
}
@Test
public void test7() {
ApplicationContext context = new ClassPathXmlApplicationContext("orm.xml");
UserService userService = context.getBean("userService", UserService.class);
List<Object[]> list = new ArrayList<>();
Object[] o1 = new Object[] {"4", "liubei@qq.com", "liubei"};
Object[] o2 = new Object[] {"5", "guanyu@qq.com", "guanyu"};
Object[] o3 = new Object[] {"6", "zhangfei@qq.com", "zhangfei"};
list.add(o1);
list.add(o2);
list.add(o3);
System.out.println(userService.batchAdd(list));
}
测试结果
2、批量修改
// 批量修改
@Override
public int[] batchUpdate(List<Object[]> list) {
String sql = "update user set email=?,nick_name=? where id=?";
return jdbcTemplate.batchUpdate(sql, list);
}
@Test
public void test8() {
ApplicationContext context = new ClassPathXmlApplicationContext("orm.xml");
UserService userService = context.getBean("userService", UserService.class);
List<Object[]> list = new ArrayList<>();
Object[] o1 = new Object[] {"liubei@qq.com", "liubei23", "4"};
Object[] o2 = new Object[] {"guanyu@qq.com", "guanyu23", "5"};
Object[] o3 = new Object[] {"zhangfei@qq.com", "zhangfei12", "6"};
list.add(o1);
list.add(o2);
list.add(o3);
System.out.println(userService.batchUpdate(list));
}
测试结果
3、批量删除
// 批量删除
@Override
public int[] batchDelete(List<Object[]> list) {
String sql = "delete from user where id=?";
return jdbcTemplate.batchUpdate(sql, list);
}
@Test
public void test9() {
ApplicationContext context = new ClassPathXmlApplicationContext("orm.xml");
UserService userService = context.getBean("userService", UserService.class);
List<Object[]> list = new ArrayList<>();
Object[] o1 = new Object[] {"4"};
Object[] o3 = new Object[] {"6"};
list.add(o1);
list.add(o3);
System.out.println(userService.batchDelete(list));
}
测试结果