springmvc实现的简单用户管理系统

package com.qunar.user.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.List; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; import com.qunar.user.dao.UserDao; import com.qunar.user.model.Limiter; import com.qunar.user.model.User; /** * 操作q_user数据表的接口实现 * * @author jasmine.wei * */ public class UserDaoImpl extends JdbcDaoSupport implements UserDao { /** * @param user 要新增的user实体 * @return 新增实体插入数据库生成的自增主键 * <ul> * 另一种实现方式 Object[] args = {user.getUserId(),user.getNickname(),user.getRealname(), * user.getPassword(),user.getEmail(),new Integer(user.getStatus())}; String sql = * "insert into q_user(user_id,nickname,realname,password,email," + "status) values(?,?,?,?,?,?)"; * //获得JdbcTemplate对象，并调用update方法对数据库进行操作 this.getJdbcTemplate().update(sql,args); * <ul> */ @Override public int addUser(final User user) { final String sql = "insert into q_user(user_id,nickname,realname,password,email," + "status,last_login_time) values(?,?,?,?,?,?,?)"; KeyHolder key = new GeneratedKeyHolder(); try { this.getJdbcTemplate().update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection con) throws SQLException { PreparedStatement preState = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); preState.setString(1, user.getUserId()); preState.setString(2, user.getNickname()); preState.setString(3, user.getRealname()); preState.setString(4, user.getPassword()); preState.setString(5, user.getEmail()); preState.setInt(6, user.getStatus()); preState.setTimestamp(7, new Timestamp(user.getLastLoginTime().getTime())); return preState; } }, key); } catch (DataAccessException e) { return 0; } // 从主键持有者中获得主键值 user.setId(key.getKey().intValue()); return user.getId(); } /** * 不真正从数据库删除，而是更新status字段标记为删除状态 * * @param id 用户id */ @Override public boolean deleteUser(int id) { Object[] args = { 2, new Integer(id) }; String sql = "update q_user set status =? where id = ?"; int status = this.getJdbcTemplate().update(sql, args); if (status == -1) { return false; } return true; } /** * @param user 要更新的user实体 * @return 是否更新成功 */ @Override public boolean updateUser(User user) { Object[] args = { user.getUserId(), user.getNickname(), user.getRealname(), user.getPassword(), user.getEmail(), new Integer(user.getStatus()), new Integer(user.getId()) }; String sql = "update q_user set user_id=?,nickname=?,realname=?,password=?,email=?," + "status=? where id=?"; int status = this.getJdbcTemplate().update(sql, args); if (status == -1) { return false; } return true; } /** * 根据id查询用户详情 * * @param id 用户id * @return 实体所对应的User对象，查询不到的情况返回null */ @Override public User queryUser(int id) { String sql = "select * from q_user where id=?"; User user = null; try { user = getJdbcTemplate().queryForObject(sql, new UserRowMapper(), id); } catch (DataAccessException e) { return null; } return user; } /** * 根据userId查询用户详情 * * @param userId 用户userId * @return 实体所对应的User对象，查询不到的情况返回null */ @Override public User queryUser(String userId) { String sql = "select * from q_user where user_id=?"; User user = null; try { user = getJdbcTemplate().queryForObject(sql, new UserRowMapper(), userId); } catch (DataAccessException e) { return null; } return user; } /** * 根据id查询用户详情 * * @param id 用户id * @return 实体所对应的User对象，查询不到的情况返回null */ @Override public List<User> queryUserList(Limiter limiter) { List<User> list = null; try { list = (List<User>) getJdbcTemplate().query("select * from q_user limit ?, ?", new UserRowMapper(), limiter.getStart(), limiter.getSize()); } catch (DataAccessException e) { return null; } return list; } /** * @author jasmine.wei 私有内部类 继承了RowMapper接口 用于将ResultSet包装成相应的值对象后返回 */ private class UserRowMapper implements RowMapper<User> { public User mapRow(ResultSet rs, int rowNumber) throws SQLException { User user = new User(rs.getString("user_id"), rs.getString("nickname"), rs.getString("realname"), rs.getString("password"), rs.getString("email"), rs.getInt("status")); user.setLastLoginTime(rs.getTimestamp("last_login_time")); user.setId(rs.getInt("id")); return user; } } }