1. 创建UserDao接口
public interface UserDao {
/**
* 查询总条数
* @return
*/
public int getCount();
/**
* 插入User
*/
public void addUser(User user);
/**
* 修改User
*/
public void updateUser(User user);
/**
* 删除User
*/
public void deleteUser(Integer id);
/**
*查询一个User
*/
public User getUserById(Integer id);
/**
* 查询所有User
*
*/
public List<User> getAllUsers();
/**
*查询所有User放入Map中
*
*/
public List<Map<String,Object>> getAllUserSMap();
}
2.创建UserDao的实现类UserDaoImpl.java
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int getCount() {
return this.jdbcTemplate.queryForObject("select count(1) from user", Integer.class);
}
@Override
public void addUser(User user) {
this.jdbcTemplate.update("insert into user value(0,?,?)", user.getName(),user.getTg_age());
}
@Override
public void updateUser(User user) {
this.jdbcTemplate.update("update user set name=?,tg_age=? where id=?", user.getName(),user.getTg_age(),user.getId());
}
@Override
public void deleteUser(Integer id) {
this.jdbcTemplate.update("delete from user where id=?", id);
}
@Override
public User getUserById(Integer id) {
String sql = "select * from user where id=?";
Object[] objs = {id};
return this.jdbcTemplate.queryForObject("select * from user where id = ?",objs,new RowMapper<User>() {
@Override
public User mapRow(ResultSet arg0, int arg1) throws SQLException {
String name = arg0.getString("name");
int tg_age = arg0.getInt("tg_age");
User user = new User(id, name, tg_age);
return user;
}
});
}
@Override
public List<User> getAllUsers() {
String sql = "select * from user";
// return this.jdbcTemplate.queryForList(<u>sql</u>, User.class); 只能多行一列的数据
return this.jdbcTemplate.query(sql, new RowMapper<User>() {
@Override
public User mapRow(ResultSet arg0, int arg1) throws SQLException {
Integer id = arg0.getInt("id");
String name = arg0.getString("name");
int tg_age = arg0.getInt("tg_age");
User user = new User(id,name,tg_age);
return user;
}
});
}
@Override
public List<Map<String, Object>> getAllUserSMap() {
String sql = "select * from user";
return this.jdbcTemplate.queryForList(sql);
}
}
3.创建UserService类,跟UserDao基本一样
public interface UserService {
/**
* 查询总条数
* @return
*/
public int getCount();
/**
* 插入User
*/
public void addUser(User user);
/**
* 修改User
*/
public void updateUser(User user);
/**
* 删除User
*/
public void deleteUser(Integer id);
/**
*查询一个User
*/
public User getUserById(Integer id);
/**
* 查询所有User
*
*/
public List<User> getAllUsers();
/**
*查询所有User放入Map中
*
*/
public List<Map<String,Object>> getAllUserSMap();
}
4.创建UserService的实现类UserServiceImpl
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
@Override
public int getCount() {
return userDao.getCount();
}
@Override
public void addUser(User user) {
userDao.addUser(user);
}
@Override
public void updateUser(User user) {
userDao.updateUser(user);
}
@Override
public void deleteUser(Integer id) {
userDao.deleteUser(id);
}
@Override
public User getUserById(Integer id) {
return userDao.getUserById(id);
}
@Override
public List<User> getAllUsers() {
return userDao.getAllUsers();
}
@Override
public List<Map<String, Object>> getAllUserSMap() {
return userDao.getAllUserSMap();
}
}
5.创建UserAction类
@Controller
public class UserAction {
@Autowired
private UserService userService;
public int getCount() {
return userService.getCount();
}
public void addUser(User user) {
userService.addUser(user);
}
public void updateUser(User user) {
userService.updateUser(user);
}
public void deleteUser(Integer id) {
userService.deleteUser(id);
}
public User getUserById(Integer id) {
return userService.getUserById(id);
}
public List<User> getAllUsers() {
return userService.getAllUsers();
}
public List<Map<String, Object>> getAllUserSMap() {
return userService.getAllUserSMap();
}
}
6.测试
此处只举例最后一个方法
public class TestAxiba {
public static void main(String[] args) {
ApplicationContext app = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
UserAction bean = app.getBean(UserAction.class);
List<Map<String, Object>> allUserSMap = bean.getAllUserSMap();
for (Map<String, Object> map : allUserSMap) {
System.out.println(map);
}
}
}