package com.softeem.jps;

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

public class LoginuserDAO {
public static void main(String[] args) {
    
    LoginuserDAO s = new LoginuserDAO();
    // 查询
    List<Loginuser> h = s.selectAll();
    int i = 0;
    Iterator it = h.iterator();
    while (it.hasNext()) {
        System.out.println(h.get(i).getUserId());
        System.out.println("\t" + h.get(i).getUserName());
        System.out.println("\t" + h.get(i).getUserPass());
        i++;
        it.next();
    }
    
    
    
    
}
    public void insert(Loginuser user) {
        Connection conn = null;
        try {
            conn = DBConnection.getConnection();

            String sql = "insert into loginuser(username,userpass) values(?,?)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, user.getUserName());
            pst.setString(2, user.getUserPass());
            pst.executeUpdate();
            pst.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.closeConnection(conn);
        }
    }

    /*
     * (non-Javadoc)
     *
     * @see com.edu.jdbc.ILoginuserDAO#update(com.edu.jdbc.Loginuser)
     */
    public void update(Loginuser user) {
        Connection conn = null;
        try {
            conn = DBConnection.getConnection();

            String sql = "update loginuser set username=?,userpass=? where userid=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, user.getUserName());
            pst.setString(2, user.getUserPass());
            pst.setInt(3, user.getUserId());
            pst.executeUpdate();
            pst.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.closeConnection(conn);
        }
    }

    /*
     * (non-Javadoc)
     *
     * @see com.edu.jdbc.ILoginuserDAO#delete(com.edu.jdbc.Loginuser)
     */
    public void delete(Loginuser user) {
        Connection conn = null;
        try {
            conn = DBConnection.getConnection();

            String sql = "delete from loginuser where userid=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, user.getUserId());
            pst.executeUpdate();
            pst.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.closeConnection(conn);
        }
    }

    /*
     * (non-Javadoc)
     *
     * @see com.edu.jdbc.ILoginuserDAO#selectAll()
     */
    public ArrayList<Loginuser> selectAll() {
        ArrayList<Loginuser> users = new ArrayList<Loginuser>();
        Connection conn = null;
        try {
            conn = DBConnection.getConnection();

            String sql = "select * from loginuser";
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                Loginuser user = new Loginuser();
                user.setUserId(rs.getInt("userid"));
                user.setUserName(rs.getString("username"));
                user.setUserPass(rs.getString("userpass"));
                users.add(user);
            }

            rs.close();
            pst.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.closeConnection(conn);
        }

        return users;
    }

    /*
     * (non-Javadoc)
     *
     * @see com.edu.jdbc.ILoginuserDAO#selectByID(int)
     */
    public Loginuser selectByID(int userId) {
        Connection conn = null;
        Loginuser user = new Loginuser();
        try {
            conn = DBConnection.getConnection();

            String sql = "select * from loginuser where userid=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, userId);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                user.setUserId(rs.getInt("userid"));
                user.setUserName(rs.getString("username"));
                user.setUserPass(rs.getString("userpass"));
            }

            rs.close();
            pst.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.closeConnection(conn);
        }

        return user;
    }

    public boolean checkLogin(Loginuser user) {
        boolean flag = false;
        Connection conn = null;
        try {// 1.2
            conn = DBConnection.getConnection();
            // 3
            String sql = "select count(*) from loginuser where username=? and userpass=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getUserPass());
            // 4
            ResultSet rs = ps.executeQuery();
            // 5
            while (rs.next()) {
                System.out.println(rs.getInt(1));
                if (rs.getInt(1) > 0) {
                    flag = true;
                }
            }// 6
            rs.close();
            ps.close();

        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {// 6
            DBConnection.closeConnection(conn);
        }

        return flag;
    }

}