JSP第六次作业

public class JDBCUtils {
    // 获取对象方法
    public static Connection getcon() throws Exception {
        // 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 获取连接
        Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306bc", "root", "root");
        return con;
    }

    public static void close(Statement st, Connection con, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (con != null) {
                        con.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {

                    try {
                        if (st != null) {
                            st.close();
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }

            }
        }

    }

}

public class User {
    private int id;
    private String name;
    private int money;
    private String email;
    private Date birthday;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getMoney() {
        return money;
    }
    public void setMoney(int money) {
        this.money = money;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    
}

public class UserDao {

    // 提供添加方法
    public boolean insert(User user) {
        Connection con = null;
        Statement st = null;
        try {
            // 连接对象
            con = JDBCUtils.getcon();
            // 获取执行sql语句
            st = con.createStatement();
            // 执行sql语句
            // java.util.Date birthday=user.getBirthday();
            java.sql.Date sqlbirthday = new java.sql.Date(
                    new java.util.Date().getTime());
            int row = st
                    .executeUpdate("insert into user(id,name,money,email,birthday)"
                            + "values("
                            + user.getId()
                            + ",'"
                            + user.getName()
                            + "','"
                            + user.getMoney()
                            + "','"
                            + user.getEmail()
                            + "','" + sqlbirthday + "'" + ")");
            if (row > 0) {
                return true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(st, con, null);
        }
        return false;

    }
    public void findall(){
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        
        try {
            // 连接对象
            con = JDBCUtils.getcon();
            // 获取执行sql语句
            st = con.createStatement();
            //执行sql语句
            rs=st.executeQuery("select * from user");
            //遍历rs
            while(rs.next()){
                System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "
                        +rs.getInt("money")+" "+rs.getString("email")+" "+rs.getDate("birthday")+"");
            }
        
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.close(st, con, rs);
        }
        
        
    }
//提供查询所有语句的方法
        public List<User> findAllUser() {
            Connection con = null;
            Statement st = null;
            ResultSet rs = null;
            
            try {
                // 连接对象
                con = JDBCUtils.getcon();
                // 获取执行sql语句
                st = con.createStatement();
                //执行sql语句
                rs=st.executeQuery("select * from user");
                //遍历rs
                List<User> list=new ArrayList<User>();
                while(rs.next()){
                    
                    User user=new User();
                    /*System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "
                            +rs.getInt("money")+" "+rs.getString("email")+" "+rs.getDate("birthday")+"");
                            */
                    
                    user.setId(rs.getInt("id"));
                    user.setName(rs.getString("name"));
                    user.setMoney(rs.getInt("money"));
                    user.setEmail(rs.getString("email"));
                    user.setBirthday(rs.getDate("birthday"));
                    list.add(user);
                
                            
                }
            return list;
            } catch (Exception e) {
                e.printStackTrace();
            }finally{
                JDBCUtils.close(st, con, rs);
            }
            return null;
                
        }
        //根据id查询对象
        public User findbyid(int id){
            Connection con = null;
            PreparedStatement st = null;
            ResultSet rs = null;
            
            try {
                // 连接对象
                con = JDBCUtils.getcon();
                // 获取执行sql语句
                st=con.prepareStatement("select * from user where id=?");
                
                //执行sql语句
                st.setInt(1, id);
                rs=st.executeQuery();
                //遍历rs
                if(rs.next()){
                    User user=new User();
                    user.setId(rs.getInt("id"));
                    user.setName(rs.getString("name"));
                    user.setMoney(rs.getInt("money"));
                    user.setEmail(rs.getString("email"));
                    user.setBirthday(rs.getDate("birthday"));
                    return user;
                }
            
            } catch (Exception e) {
                e.printStackTrace();
            }finally{
                JDBCUtils.close(st, con, rs);
            }
            return null;
        }
        

//提供修改数据方法
public boolean update(User user){
    Connection con = null;
    PreparedStatement st = null;
    try {
        // 连接对象
        con = JDBCUtils.getcon();
        // 获取执行sql语句
        
        st = con.prepareStatement("update user set money=?,name=? where id=?");
        // 执行sql语句
        st.setInt(3, user.getId());
        st.setInt(1, user.getMoney());
        st.setString(2, user.getName());
        int row = st.executeUpdate();
        if (row > 0) {
            return true;
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.close(st, con, null);
    }
    return false;
    
}

//提供删除数据方法
public boolean delete(int id){
    Connection con = null;
    PreparedStatement st = null;
    try {
        // 连接对象
        con = JDBCUtils.getcon();
        // 获取执行sql语句
        
        st = con.prepareStatement("delete from user where id=?");
        // 执行sql语句
        st.setInt(1, id);
        int row = st.executeUpdate();
        if (row > 0) {
            return true;
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.close(st, con, null);
    }
    return false;
    
    }
}

public class Jdbcinsettest {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        UserDao dao=new UserDao();
        User user=new User();
        user.setId(5);
        user.setName("wcp");
        user.setMoney(1000);
        user.setEmail("2316726@qq.com");
        user.setBirthday(new Date(1992-02-02));
        boolean flag=dao.insert(user);
        System.out.print(flag);
    }

}

public class Jdbccinserttest {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        UserDao dao=new UserDao();
        dao.findall();
        List <User> list=dao.findAllUser();
        System.out.print(list.size());
        
    }

}

public class Jdbccid {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        UserDao dao=new UserDao();
        User u=dao.findbyid(2);
        System.out.println(u.getName());
    }

}

public class Jdbcupdate {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        UserDao dao=new UserDao();
        User user=new User();
        user.setId(2);
        user.setMoney(20);
        user.setName("llii");
        boolean flag=dao.update(user);
        System.out.print(flag);
    }

}

public class Jdbcdele {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        UserDao dao=new UserDao();
        User u=new User();
        boolean flag=dao.delete(3);
        System.out.print(flag);

    }

}