使用JdbcTemplate完成CURD

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);
          }
     }
}