jsp-第六周作业

 

package sxz11;

public class deleteUser {
    public static void main(String[] args) {
        userText userText = new userText();
        userText.delete(2);
    }
}

 

package sxz11;

import java.util.List;

public class findAllUser {
    public static void main(String[] args) {
        userText userText = new userText();
        List<user> list = userText.findAllUser();
        System.out.println(list.size());
    }
}

 

package sxz11;

public class findUserById {
      public static void main(String[] args) {
            userText userText = new userText();
           user u= userText.findUserById(1);
            System.out.println(u.getUsername());
        }


}

 

package sxz11;

public class updateUser {
     public static void main(String[] args) {
            userText userText = new userText();
            user user = new user();
            user.setId(1);
            user.setPassword("222222");
            user.setUsername("sxz");
            userText.update(user);
        }

}

 

package sxz11;
import java.util.Date;

public class userInsertText {
    public static void main(String[] args) {
        //实例化对象
        userText userText = new userText();
        user user = new user();
        user.setId(2);
        user.setUsername("sx");
        user.setPassword("32131");
        user.setEmail("3123058032@qq.com");
        user.setBirthDay(new Date());
        userText.insert(user);
    }
}

 

package sxz11;


    import java.util.Date;
public class user {

        private int id;
        private String username;
        private String password;
        private String email;
        private Date birthDay;
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        public String getUsername() {
            return username;
        }
        public void setUsername(String username) {
            this.username = username;
        }
        public String getPassword() {
            return password;
        }
        public void setPassword(String password) {
            this.password = password;
        }
        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;
        }
        
    }

 

package sxz11;

import java.sql.*;

public class JDBCUtils {
    //封装成工具类
    //获取连接对象的方法
    public static Connection getcon() throws Exception {
        //注册并加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接对象
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "sxz990715");
        return con;
    }

    public static void realse(ResultSet rs, Statement st, Connection con) {
        {
            //6.关闭资源,释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) { // ignore }
                    rs = null;
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) { // ignore }
                    st = null;
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) { // ignore }
                    rs = null;
                }
            }
        }
    }
}

 

package sxz11;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class userText {
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    //实现添加方法
    public boolean insert(user user) {

        try {
            //获取加载驱动并且连接对象
            con = JDBCUtils.getcon();
            st = con.createStatement();
            Date birthday = user.getBirthDay();
            String sqlbirthday = String.format("%tF", birthday);
            String sql = "insert into message(id,user,password,email,birthday)" + "values ('"
                    + user.getId() + "','"
                    + user.getUsername() + "','"
                    + user.getPassword() + "','"
                    + user.getEmail() + "','"
                    + sqlbirthday + "'"
                    + ")";
            int row = st.executeUpdate(sql);
            if (row > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.realse(null, st, con);
        }
        return false;
    }

    //查找所有用户
    public List<user> findAllUser() {
        try {
            con = JDBCUtils.getcon();
            st = con.createStatement();
            //执行sql语句
            rs = st.executeQuery("select * from message");
            //遍历结果集
            List<user> list = new ArrayList<user>();
            user user = new user();
            while (rs.next()) {
                java.sql.Date birthday = rs.getDate("birthday");
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setEmail(rs.getString(4));
                user.setBirthDay(birthday);
                list.add(user);

            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.realse(rs, st, con);
        }


        return null;
    }

    //根据Id查找用户
    public user findUserById(int id) {
        {
            try {
                PreparedStatement st = null;
                con = JDBCUtils.getcon();
                String sql = "select * from message where id=?";
                st = con.prepareStatement(sql);
                //执行sql语句
                st.setInt(1, id);
                rs = st.executeQuery();
                //遍历结果集
                user user = new user();
                if (rs.next()) {
                    java.sql.Date birthday = rs.getDate("birthday");
                    user.setId(rs.getInt(1));
                    user.setUsername(rs.getString(2));
                    user.setPassword(rs.getString(3));
                    user.setEmail(rs.getString(4));
                    user.setBirthDay(birthday);
                    return user;
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.realse(rs, st, con);
            }


            return null;
        }
    }

    //根据id值修改记录
    public boolean update(user user) {
        {
            try {
                PreparedStatement st = null;
                con = JDBCUtils.getcon();
                String sql = "update message set user =?,password=? where  id=?";
                st = con.prepareStatement(sql);
                //执行sql语句 占位符赋值
                st.setString(1, user.getUsername());
                st.setString(2, user.getPassword());
                st.setInt(3, user.getId());
                int row = st.executeUpdate();
                if (row > 0) {
                    return true;
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.realse(null, st, con);
            }
            return false;
        }
    }

    //根据ID删除用户
    public boolean delete(int id) {
        {
            try {
                PreparedStatement st = null;
                con = JDBCUtils.getcon();
                String sql = "delete from message where id=?";
                st = con.prepareStatement(sql);
                //执行sql语句
                st.setInt(1, id);
                int row = st.executeUpdate();
                if (row > 0) {
                    return true;
                }

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.realse(rs, st, con);
            }


            return false;
        }
    }
}