一、什么是JDBCTemplate
- Spring 框架对JDBC进行了封装
- 使用
JdbcTemplate
方便实现对数据库操作
二、JDBCTemplate操作数据库——添加功能
对应数据库创建实体类
public class User {
private String userId;
private String userName;
private String uStatus;
public User() {
}
public User(String userId, String userName, String uStatus) {
this.userId = userId;
this.userName = userName;
this.uStatus = uStatus;
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getuStatus() {
return uStatus;
}
public void setuStatus(String uStatus) {
this.uStatus = uStatus;
}
@Override
public String toString() {
return "User{" +
"userId='" + userId + '\'' +
", userName='" + userName + '\'' +
", uStatus='" + uStatus + '\'' +
'}';
}
}
编写Service
和Dao
,在Dao
层进行数据库操作
@Service
public class UserService {
//注入dao
@Autowired
private UserDao userDao;
//添加User对象
public void add(User user){
userDao.add(user);
}
}
@Repository
public class UserDaoImpl implements UserDao {
//注入jdbcTemplate对象
@Autowired
private JdbcTemplate jdbcTemplate;
//接口实现类使用jdbcTemplate实现添加方法
@Override
public void add(User user) {
//1.创建sql语句
String sql = "insert into t_user values(?,?,?)";
//2.调用方法执行sql
int update = jdbcTemplate.update(sql, user.getUserId(), user.getUserName(), user.getuStatus());
System.out.println(update);
}
}
测试
@Test
public void testInsertUser(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
UserService userService = context.getBean("userService", UserService.class);
userService.add(new User(1,"张三","学习中..."));
}
执行测试之后,数据库就有了一条记录
三、JDBCTemplate操作数据库——修改和删除
Service层
@Service
public class UserService {
//注入dao
@Autowired
private UserDao userDao;
//添加User对象
public void add(User user){
userDao.add(user);
}
//修改User对象
public void updateUser(User user){
userDao.updateUser(user);
}
//删除User对象
public void deleteUser(User user){
userDao.deleteUser(user);
}
}
Dao层
@Repository
public class UserDaoImpl implements UserDao {
//注入jdbcTemplate对象
@Autowired
private JdbcTemplate jdbcTemplate;
//接口实现类使用jdbcTemplate实现添加方法
@Override
public void add(User user) {
//1.创建sql语句
String sql = "insert into t_user values(?,?,?)";
//2.调用方法执行sql
int update = jdbcTemplate.update(sql, user.getUserId(), user.getUserName(), user.getuStatus());
System.out.println(update);
}
@Override
public void updateUser(User user) {
String sql = "update t_user set username=?, ustatus=? where user_id=?";
int update = jdbcTemplate.update(sql, user.getUserName(), user.getuStatus(), user.getUserId());
System.out.println(update);
}
@Override
public void deleteUser(User user) {
String sql = "delete from t_user where user_id = ?";
int update = jdbcTemplate.update(sql, user.getUserId());
System.out.println(update);
}
}
四、JDBCTemplate操作数据库——查询
查询返回某个值:queryForObject(String sql, Class<T> requiredType)
@Override
public int selectCount() {
String sql = "select count(*) from t_user";
//第二个参数是返回类型的Class
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(count);
return count;
}
查询返回一个对象:queryForObject(String sql, RowMapper<T> rowMapper, @Nullable Object... args)
@Override
public User queryUser(int id) {
String sql = "select * from t_user where id = ?";
//第二个参数RowMapper是一个接口,针对返回不同类型的数据,使用这个接口里面实现类完成数据的封装
User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
System.out.println(user);
return user;
}
查询返回一个集合:query(String sql, RowMapper<T> rowMapper, @Nullable Object... args)
@Override
public List<User> queryList() {
String sql = "select * from t_user";
List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
System.out.println(userList);
return userList;
}
五、JDBCTemplate操作数据库——批量添加
批量操作:batchUpdate(String sql, List<Object[]> batchArgs)
通过传入类型为Object[]
的List
集合,对集合中的每一个元素执行添加操作的sql
语句
@Override
public void batchAddUser(List<Object[]> batchArgs) {
String sql = "insert into t_user values(?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
测试批量添加
@Test
public void tesBatchAdd(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
UserService userService = context.getBean("userService", UserService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"3","张三","学习中。。。"};
Object[] o2 = {"4","李四","运动中。。。"};
Object[] o3 = {"5","王五","游戏中。。。"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
userService.batchAdd(batchArgs);
}
六、JDBCTemplate操作数据库——批量修改和删除
批量操作:batchUpdate(String sql, List<Object[]> batchArgs)
通过传入类型为Object[]
的List
集合,对集合中的每一个元素执行修改操作的sql
语句
@Override
public void batchUpdateUser(List<Object[]> batchArgs) {
String sql = "update t_user set username=?, ustatus=? where user_id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
批量操作:batchUpdate(String sql, List<Object[]> batchArgs)
通过传入类型为Object[]
的List
集合,对集合中的每一个元素执行删除操作的sql
语句
@Override
public void batchDeleteUser(List<Object[]> batchArgs) {
String sql = "delete from t_user where user_id = ?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(ints);
}