SpringJdbcTemplate

配置文件

1、依赖坐标



javax.servlet
javax.servlet-api
4.0.1
provided


org.junit.jupiter
junit-jupiter-api
${junit.version}
test


org.junit.jupiter
junit-jupiter-engine
${junit.version}
test




org.springframework
spring-context
5.2.4.RELEASE



org.springframework
spring-test
5.2.4.RELEASE
test



org.aspectj
aspectjweaver
1.9.5






org.springframework
spring-jdbc
5.2.4.RELEASE



org.springframework
spring-tx
5.2.4.RELEASE



mysql
mysql-connector-java
8.0.19



com.mchange
c3p0
0.9.5.5


junit
junit
4.12
compile


org.springframework
spring-test
5.2.4.RELEASE
compile



org.projectlombok
lombok
1.18.12
provided




2、配置环境

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
https://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">









3、提取properties

# 驱动名
jdbc.driver=com.mysql.cj.jdbc.Driver
# 数据库连接
jdbc.url=jdbc:mysql://localhost:3306/jdbcprac?useUnicode=true&characterEncoding=utf8&serverTimezOne=GMT%2B8&useSSL=false
# 数据库用户名称
jdbc.user= root
# 数据库用户密码
jdbc.password=0610


实体类

package com.geo.bean;
import lombok.*;
import org.springframework.stereotype.Component;
/**
* Created with IntelliJ IDEA.
*
* @Author: Suhai
* @Date: 2022/04/06/16:13
* @Description:
*/
@Data
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Component("user")
public class User {
private String id;
private String name;
private String sex;
private String classno;
private int age;
@Override
public String toString() {
return "[" +
"学号=" + id +
", 姓名='" + name + '\'' +
", 性别='" + sex + '\'' +
", 班号='" + classno + '\'' +
", 年龄=" + age +
']';
}
}


1、接口类

package com.geo.Dao;
import com.geo.bean.User;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
/**
* Created with IntelliJ IDEA.
*
* @Author: Suhai
* @Date: 2022/04/11/18:42
* @Description:
*/
@Repository("userDao")
public class UserDao {
@Resource(name = "jdbcTemplate")
JdbcTemplate jdbcTemplate;
/**
* 批量插入6条记录
* @param userList
*/
public void addUserBatch(List userList) {
String sql = "insert into user(id,name,sex,classno,age) values(?,?,?,?,?)";
int rows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter()
{
@Override
public void setValues(PreparedStatement preparedStatement, int i)
throws SQLException {
// 设置参数
preparedStatement.setString(1,userList.get(i).getId());
preparedStatement.setString(2,userList.get(i).getName());
preparedStatement.setString(3,userList.get(i).getSex());
preparedStatement.setString(4,userList.get(i).getClassno());
preparedStatement.setInt(5,userList.get(i).getAge());
} @Override
public int getBatchSize() {
return userList.size();
}
}).length;
// 调用对象的添加方法，返回主键
System.out.println("受影响的行数：" + rows);
System.out.println("执行成功输出：UserDao成功执行插入操作！");
}
/**
* 修改第一条记录为你的学号，姓名，性别，班号，年龄，执行成功输出：xxx成功执行修改操作
*
* @param id
*/
public void updateUserById(String id) {
int rows = jdbcTemplate.update("update user set id = ?, name = ?,sex = ?,classno = ?, age = ? where id = ?",
"419109070419","xxx","m","4191090502",21, id);
System.out.println("受影响的行数：" + rows);
System.out.println("执行成功输出：UserDao成功执行更新操作！");
}
/**
* 删除第5条记录，执行成功输出：xxx成功执行删除操作
* @param id
*/
public void deleteUserById(String id) {
int rows = jdbcTemplate.update("delete from user where id = ?",
id);
System.out.println("受影响的行数：" + rows);
System.out.println("执行成功输出：UserDao成功执行删除操作！");
}
/**
* 查找你的记录并输出，格式为：[学号:xxxx 姓名：xxx 性别：xxx 班号：xxx 年龄：xxx]
* @param id
*/
public void queryUserById(String id) {
String sql = "select * from user where id = ?";
User user = jdbcTemplate.queryForObject(sql, new Object[]
{id}, (resultSet, i) -> {
User user1 = new User();
user1.setId(resultSet.getString("id"));
user1.setName(resultSet.getString("name"));
user1.setSex(resultSet.getString("sex"));
user1.setClassno(resultSet.getString("classno"));
user1.setAge(resultSet.getInt("age"));
return user1;
});
System.out.println("执行成功输出：UserDao成功执行查询封装操作！");
System.out.println(user);
}
}


2、Service类

package com.geo.Dao;
import com.geo.bean.User;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
/**
* Created with IntelliJ IDEA.
*
* @Author: Suhai
* @Date: 2022/04/11/18:43
* @Description:
*/
@Service("userService")
public class UserService {
@Resource(name = "userDao")
UserDao userDao;
public void addUserBatch(List userList) {
userDao.addUserBatch(userList);
}
public void updateAUserById(String id) {
userDao.updateUserById(id);
}
public void deleteUserById(String id) {
userDao.deleteUserById(id);
}
public void queryUserById(String id) {
userDao.queryUserById(id);
}
}


测试类

package com.geo.Test;
import com.geo.Dao.UserDao;
import com.geo.Dao.UserService;
import com.geo.bean.User;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
/**
* Created with IntelliJ IDEA.
*
* @Author: Suhai
* @Date: 2022/04/11/19:12
* @Description:
*/
public class Test01 {
ApplicationContext ac = new ClassPathXmlApplicationContext("springjdbc.xml");//读取配置文件
UserService userService = (UserService) ac.getBean("userService");
@Test
public void addUserBatch(){
User user1 = new User("1","aaa","f","3",22);
User user2 = new User("2","bbb","f","3",22);
User user3 = new User("3","ccc","f","3",22);
User user4 = new User("4","ddd","f","3",22);
User user5 = new User("5","eee","f","3",22);
User user6 = new User("6","fff","f","3",22);
List userList = new ArrayList<>();
userList.add(user1);
userList.add(user2);
userList.add(user3);
userList.add(user4);
userList.add(user5);
userList.add(user6);
userService.addUserBatch(userList);
}
// 修改第一条记录为你的学号，姓名，性别，班号，年龄，执行成功输出：xxx成功执行修改操作
@Test
public void updateUserById(){
userService.updateAUserById("1");
}
// 删除第5条记录，执行成功输出：xxx成功执行删除操作
@Test
public void deleteUserById( ){
userService.deleteUserById("5");
}
// 查找你的记录并输出，格式为：[学号:xxxx 姓名：xxx 性别：xxx 班号：xxx 年龄：xxx]
@Test
public void queryUserById(){
userService.queryUserById("419109070419");
}
}