dao
接口
package com.haikang.spring5.jdbc.dao;
import com.haikang.spring5.jdbc.pojo.User;
import java.util.List;
public interface UserDao {
// 添加员工方法
int addUser(User user);
// 修改员工信息方法
int updateUser(User user);
// 删除员工信息方法
int deleteUser(Integer id);
// 查询返回某个值,返回表中总共记录数
int countRow();
// 查询Id为1,的邮箱
String findEmailById(Integer id);
// 根据Id查询员工信息
User findUserById(Integer id);
// 查询所有员工信息
List<User> fineAllUser();
// 批量添加操作
int[] batchAdd(List<Object[]> batchArgs);
// 批量修改操作
int[] batchUpdateUser(List<Object[]> batchArgs);
// 批量删除操作
int[] batchDelete(List<Object[]> batchArgs);
}
dao
接口实现类
package com.haikang.spring5.jdbc.dao;
import com.haikang.spring5.jdbc.pojo.User;
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;
/**
* @Author 海康
* @Version 1.0
*/
@Repository
public class UserDaoImpl implements UserDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int addUser(User user) {
// 定义Sql语句
String sql = "insert into t_user(username,password,age,sex,email) VALUES(?,?,?,?,?)";
// 调用JdbcTemplate中update方法
int result = jdbcTemplate.update(sql, user.getUserName(), user.getPassword(),
user.getAge(), user.getSex(), user.getEmail());
return result;
}
@Override
public int updateUser(User user) {
// 定义Sql语句
String sql = "update t_user set username=?,password=?,age=?,sex=?,email=? where id=?";
// 调用JdbcTemplate中的update方法
int result = jdbcTemplate.update(sql, user.getUserName(), user.getPassword(), user.getAge(),
user.getSex(), user.getEmail(), user.getId());
return result;
}
@Override
public int deleteUser(Integer id) {
// 定义Sql语句
String sql = "delete from t_user where id=?";
// 调用JdbcTemplate中的delete方法
int result = jdbcTemplate.update(sql, id);
return result;
}
// 查询返回表中记录数
@Override
public int countRow() {
// 定义Sql语句
String sql = "select count(*) from t_user";
// 调用JdbcTemplate中的方法
Integer query = jdbcTemplate.queryForObject(sql, Integer.class);
return query;
}
// 根据Id查询邮箱
@Override
public String findEmailById(Integer id) {
// 定义Sql
String sql = "select email from t_user where id=?";
// 调用JdbcTemplate中的方法
String email = jdbcTemplate.queryForObject(sql, String.class, id);
return email;
}
// 根据Id查询员工信息
@Override
public User findUserById(Integer id) {
// 定义Sql语句
String sql = "select * from t_user where id = ?";
User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
return user;
}
// 查询所有员工信息
@Override
public List<User> fineAllUser() {
// 定义Sql
String sql = "select * from t_user";
List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
return userList;
}
@Override
public int[] batchAdd(List<Object[]> batchArgs) {
// 定义Sql语句
String sql = "insert into t_user(username,password,age,sex,email) VALUES(?,?,?,?,?)";
// 调用JdbcTemplate中方法
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
return ints;
}
// 批量修改
@Override
public int[] batchUpdateUser(List<Object[]> batchArgs) {
// 定义Sql语句
String sql = "update t_user set username=?,password=?,age=?,sex=?,email=? where id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
return ints;
}
@Override
public int[] batchDelete(List<Object[]> batchArgs) {
// 定义Sql语句
String sql = "delete from t_user where id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
return ints;
}
}
Service
层
package com.haikang.spring5.jdbc.service;
import com.haikang.spring5.jdbc.dao.UserDao;
import com.haikang.spring5.jdbc.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @Author 海康
* @Version 1.0
*/
@Service
public class UserService {
@Autowired
private UserDao userDao;
// 调用Dao层,添加一个员工
public void addUser(User user){
int result = userDao.addUser(user);
System.out.println(result);
}
// 调用Dao层,修改员工方法
public void updateUser(User user){
int result = userDao.updateUser(user);
System.out.println(result);
}
// 调用Dao层,删除员工方法
public void deleteUser(Integer id){
int result = userDao.deleteUser(id);
System.out.println(result);
}
// 查询表中有多少条记录
public void count(){
int countRow = userDao.countRow();
System.out.println(countRow);
}
// 根据Id查询email记录
public String findEmailById(Integer id){
String emailById = userDao.findEmailById(id);
return emailById;
}
// 根据Id查询员工信息
public User findUserById(Integer id){
User userById = userDao.findUserById(id);
return userById;
}
// 查询所有员工信息
public List<User> findAllUser(){
List<User> users = userDao.fineAllUser();
return users;
}
// 批量添加多条记录
public int[] batchAddUser(List<Object[]> batchArgs){
int[] ints = userDao.batchAdd(batchArgs);
return ints;
}
// 批量修改多条记录
public int[] batchUpdate(List<Object[]> batchArgs){
int[] ints = userDao.batchUpdateUser(batchArgs);
return ints;
}
// 批量删除
public int[] batchDelete(List<Object[]> batchArgs){
int[] ints = userDao.batchDelete(batchArgs);
return ints;
}
}
test
类
package com.haikang.spring5.jdbc.test;
import com.haikang.spring5.jdbc.pojo.User;
import com.haikang.spring5.jdbc.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* @Author 海康
* @Version 1.0
*/
public class JdbcTemplate {
@Test
public void test(){
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
UserService userService = context.getBean("userService", UserService.class);
userService.addUser(new User(null,"艾明","123",21,"女","123@qq.com"));
}
@Test
public void testUpdate(){
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
UserService userService = context.getBean("userService", UserService.class);
userService.updateUser(new User(19,"艾明","1314",21,"女","123@qq.com"));
}
@Test
public void testDelete(){
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
UserService userService = context.getBean("userService", UserService.class);
userService.deleteUser(19);
}
// 查询表中记录数
@Test
public void testCount(){
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
UserService userService = context.getBean("userService", UserService.class);
userService.count();
}
// 根据Id查询email记录
@Test
public void testEmail(){
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
UserService userService = context.getBean("userService", UserService.class);
String emailById = userService.findEmailById(1);
System.out.println(emailById);
}
// 根据Id查询员工信息
@Test
public void testFindUserById(){
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
UserService userService = context.getBean("userService", UserService.class);
User userById = userService.findUserById(1);
System.out.println(userById);
}
// 查询所有员工信息
@Test
public void testFindAllUser(){
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
UserService userService = context.getBean("userService", UserService.class);
List<User> allUser = userService.findAllUser();
for (int i = 0; i < allUser.size(); i++) {
System.out.println(allUser.get(i));
}
}
// 批量添加多条记录
@Test
public void testBatch(){
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
UserService userService = context.getBean("userService", UserService.class);
Object[] obj1 = {"大理","123",21,"男","123@qq.com"};
Object[] obj2 = {"大理","123",21,"男","123@qq.com"};
Object[] obj3 = {"大理","123",21,"男","123@qq.com"};
List<Object[]> batchArgs = new ArrayList<>();
batchArgs.add(obj1);
batchArgs.add(obj2);
batchArgs.add(obj3);
int[] ints = userService.batchAddUser(batchArgs);
System.out.println(Arrays.toString(ints));
}
// 批量修改多条记录
@Test
public void testBatchUpdate(){
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
UserService userService = context.getBean("userService", UserService.class);
Object[] obj1 = {"大理","12dsfs3",21,"男","123@qq.com",20};
Object[] obj2 = {"大理","12fds3",21,"男","123@qq.com",21};
Object[] obj3 = {"大理","12sdf3",21,"男","123@qq.com",22};
List<Object[]> batchArgs = new ArrayList<>();
batchArgs.add(obj1);
batchArgs.add(obj2);
batchArgs.add(obj3);
int[] ints = userService.batchUpdate(batchArgs);
System.out.println(Arrays.toString(ints));
}
// 批量修改多条记录
@Test
public void testBatchDelete(){
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
UserService userService = context.getBean("userService", UserService.class);
Object[] obj1 = {20};
Object[] obj2 = {21};
Object[] obj3 = {22};
List<Object[]> batchArgs = new ArrayList<>();
batchArgs.add(obj1);
batchArgs.add(obj2);
batchArgs.add(obj3);
int[] ints = userService.batchDelete(batchArgs);
System.out.println(Arrays.toString(ints));
}
}
·