jsf实例

package com.softeem.jsf.model; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class UserDAO { private Connection con; private static final String FIND_ALL = "select * from login"; private static final String INSERT = "insert into login(username,password) values(?,?)"; private static final String DELETE = "delete from login where id=?"; private static final String UPDATE = "update login set username=?,password=? where id=?"; public UserDAO(Connection con) { this.con = con; } public List<UserDTO> findAll(String sql) { /* * 1.加载驱动 2.创建连接 3.创建一个Statement对象 4.执行sql语句，获得结果集 5.处理结果集 6.关闭连接 */ List<UserDTO> list = new ArrayList<UserDTO>(); try { PreparedStatement ps = con.prepareStatement(sql); ResultSet rs = ps.executeQuery(); while (rs.next()) { UserDTO dto = new UserDTO(); dto.setId(rs.getInt("id")); dto.setUsername(rs.getString("username")); dto.setPassword(rs.getString("password")); list.add(dto); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } public List<UserDTO> findAll(String sql,int page, int pageSize) { /* * 1.加载驱动 2.创建连接 3.创建一个Statement对象 4.执行sql语句，获得结果集 5.处理结果集 6.关闭连接 */ List<UserDTO> list = new ArrayList<UserDTO>(); try { PreparedStatement ps = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = ps.executeQuery(); int index = (page - 1) * pageSize; if (index != 0) { rs.absolute(index); } int count = 0; while (rs.next() && count < pageSize) { count++; UserDTO dto = new UserDTO(); dto.setId(rs.getInt("id")); dto.setUsername(rs.getString("username")); dto.setPassword(rs.getString("password")); list.add(dto); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } public UserDTO getById(int id) { UserDTO dto = new UserDTO(); try { PreparedStatement ps = con .prepareStatement("select * from login where id=?"); ps.setInt(1, id);// 预查询，“？”动态赋参 ResultSet rs = ps.executeQuery(); if (rs.next()) { dto.setId(rs.getInt(1)); dto.setUsername(rs.getString(2)); dto.setPassword(rs.getString(3)); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return dto; } public List<UserDTO> findAll() { List<UserDTO> list = new ArrayList<UserDTO>(); try { PreparedStatement ps = con.prepareStatement(FIND_ALL); ResultSet rs = ps.executeQuery(); while (rs.next()) { UserDTO dto = new UserDTO(); dto.setId(rs.getInt("id")); dto.setUsername(rs.getString("username")); dto.setPassword(rs.getString("password")); list.add(dto); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; } public void insert(UserDTO dto) { try { PreparedStatement ps = con.prepareStatement(INSERT); ps.setString(1, dto.getUsername()); ps.setString(2, dto.getPassword()); ps.execute();// } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void delete(int id) { try { PreparedStatement ps = con.prepareStatement(DELETE); ps.setInt(1, id); ps.execute(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void update(UserDTO dto) { try { PreparedStatement ps = con.prepareStatement(UPDATE); ps.setString(1, dto.getUsername()); ps.setString(2, dto.getPassword()); ps.setInt(3, dto.getId()); ps.execute(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public int getPageSize(String sqlCount, int pageSize) { int count = 0; try { PreparedStatement ps = con.prepareStatement(sqlCount); ResultSet rs = ps.executeQuery(); if (rs.next()) { count = rs.getInt(1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if (count % pageSize == 0) { return count / pageSize; } return count / pageSize + 1; } }