4.DAO实现类

package com.wangguan;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 实现了DAO接口，并实现了所有方法，在dao视线中通过连接数据库进行数据操作
 * @author Administrator
 * 第四部分  DAO实现类
 */
public class UserInfoDAOImpl implements UserInfoDAO{

    //删除操作
    public void delete(int userid) {
        DataBaseConnection dbc = null;
        PreparedStatement pstmt = null;
        String sql = "delete from userInfo " +
                     "    where userId = ?; ";
        
        try {
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setInt(1, userid);
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbc.close();
        }
    }
    
    //添加操作
    public void insert(UserInfo userInfo) {
        DataBaseConnection dbc = null;
        PreparedStatement pstmt = null;
        String sql = "insert into userInfo(userName, passWord) " +
         "    values(?,?) ";
        try {
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setString(1, userInfo.getUsername());
            pstmt.setString(2, userInfo.getPassword());
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbc.close();
        }
    }

    public List<UserInfo> queryAll() {
        List<UserInfo> arr = new ArrayList<UserInfo>();
        
        String sql = "select userId, userName, passWord " +
                     "    from userInfo ";
        DataBaseConnection dbc = null;
        PreparedStatement pstmt = null;
        try {
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setUserid(rs.getInt(1));
                userInfo.setUsername(rs.getString(2));
                userInfo.setPassword(rs.getString(3));
                arr.add(userInfo);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbc.close();
        }
        
        return arr;
    }
    
    //按ID查询
    public UserInfo queryById(int userid) {
        UserInfo userInfo = null;
        String sql = "select userId, userName, passWord " +
                     "    from userInfo " +
                     "where userId = ? ";
        DataBaseConnection dbc = null;
        PreparedStatement pstmt = null;
        try {
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setInt(1, userid);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()) {
                userInfo = new UserInfo();
                userInfo.setUserid(rs.getInt(1));
                userInfo.setUsername(rs.getString(2));
                userInfo.setPassword(rs.getString(3));
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbc.close();
        }
        return userInfo;
    }

    //修改操作
    public void update(UserInfo userInfo) {
        PreparedStatement pstmt = null;
        DataBaseConnection dbc = null;
        String sql = "UPDATE userInfo " +
                     "    SET userName = ?, passWord = ? " +
                     "WHERE userId = ?;";
        try {
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setString(1, userInfo.getUsername());
            pstmt.setString(2, userInfo.getPassword());
            pstmt.setInt(3, userInfo.getUserid());
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbc.close();
        }
    }

}