JDBC操作数据库

使用preparestatement执行查找语句
UserDao的实现类UserDaoImpl中，使用preparestatement执行查询语句的操作：
1.findById方法：

public User finById(int id) {
		User user=null;
		try {
			conn=this.getConnection();
			String sql="select * from users where id="+id;
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				user=new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return user;
		
	}
	

2.findAll方法：

	public List findAll() {
		List list=new ArrayList();
		try {
			conn=this.getConnection();
			String sql="select * from users ";
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				User user=new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;

		
	}

3.登陆login方法：ligin方法就是通过上面所说的fnidById的方法实现的。

public User login(String name, String password) {
		User user=null;
		try {
			conn=this.getConnection();
			String sql="select * from users where name=? and password=?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, password);
			rs=pstmt.executeQuery();
			while(rs.next()){
				user=new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
	}

4.分页：关键代码是sql语句：

public List findAll(int page) {
        List list=new ArrayList();
        int begin=(page-1)*5;
        try {
            conn=this.getConnection();
            String sql="SELECT TOP 5 *  FROM users WHERE " +
                    "(ID NOT IN  (SELECT TOP "+begin+" id   FROM users  ORDER BY id desc))" +
                    "ORDER BY ID desc ";
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while(rs.next()){
                User    user=new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                list.add(user);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return list;
    }