userdao.java

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

}