第四次作业

user Bean.java

package com.media.bean;

public class UserBean {
    private String username;

    private String password;

    private int gender;

    private int age;

    private int status;

    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 int getGender() {
        return gender;
    }

    public void setGender(int gender) {
        this.gender = gender;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }
}

注册页面

<form method="post" action="register.jsp">
  用户名：<input type="text" name="username"><br>
  密码： <input type="text" name="password"><br>
  性别： <input type="text" name="gender"><br>
  年龄： <input type="text" name="age"><br>
  <button type="submit">注册</button>

</form>

使用Statement在数据库增加用户

<%

    UserBean user = new UserBean();
    user.setUsername(request.getParameter("username"));
    user.setPassword(request.getParameter("password"));
    Class.forName("com.mysql.jdbc.Driver");
    String url = "jdbc:mysql://127.0.0.1:3306/book?user=root&password=root";
    Connection connection = DriverManager.getConnection(url);
    Statement stmt = connection.createStatement();
    String sql = "insert into user (username,password,gender,age) values('"+user.getUsername()+"','"+user.getPassword()+"',1,23)";
    int count = stmt.executeUpdate(sql);

    if(count == 0 ){
        out.print("注册失败");
    }else{
        out.print("注册成功");
    }

%>

使用PreparedStatement语句增加用户

使用Statement删除用户

<%
    Class.forName("com.mysql.jdbc.Driver");   
    
    String url ="jdbc:mysql://127.0.0.1:3306/book?user=root&password=root";

    Connection connection = DriverManager.getConnection(url);

    Statement stmt = connection.createStatement();

    String sql="delete from user where id = 4 ";

    int count = stmt.executeUpdate(sql);

    if(count == 0){
        out.print("删除失败");
    }else{
        out.print("删除成功");
    }
%>

使用PreparedStatement删除用户

<%

    Class.forName("com.mysql.jdbc.Driver");
 
    String url = "jdbc:mysql://localhost:3306/book";

    Connection connection = DriverManager.getConnection(url,"root","root");

    String sql = "delete  from user where id = ? ";
    PreparedStatement pstm = connection.prepareStatement(sql);
    pstm.setInt(5);
%>

使用Statement语句修改用户 现在要修改id为1的用户stadius的值为2

    Class.forName("com.mysql.jdbc.Driver");

    String url ="jdbc:mysql://127.0.0.1:3306/book?user=root&password=root";

    Connection connection = DriverManager.getConnection(url);
  
    Statement stmt = connection.createStatement();
  
    String sql = "update user set stadius = 3 where id= 3";

    int count = stmt.executeUpdate(sql);

    if(count == 0){
        out.print("修改失败");
    }else{
        out.print("修改成功");
    }

%>

使用PreparedStatement语句修改用户 现在要修改id为2的用户stadius的值为3

    Class.forName("com.mysql.jdbc.Driver");   
    
    String url ="jdbc:mysql://127.0.0.1:3306/book";
    
    Connection connection = DriverManager.getConnection(url,"book","book");
    
    String sql="update user set statius=? where statius=?";
    
    PreparedStatement pstm=connection.prepareStatement(sql);
    
    pstm.setString(2,3);

使用Statement查询用户

<%

    Class.forName("com.mysql.jdbc.Driver");

//建立数据库连接
    String url = "jdbc:mysql://127.0.0.1:3306/book?user=root&password=root";

    Connection connection = DriverManager.getConnection(url);//获得链接对象

    Statement stmt = connection.createStatement();

    //用户名username为123
    String sql = "select * from user ";

    ResultSet rs = stmt.executeQuery(sql);

    while (rs.next()){

        out.print(rs.getString("username")+"-"+rs.getString(2)+"<br>");

    }
%>

使用PreparedStatement查询用户

<%
   
    Class.forName("com.mysql.jdbc.Driver");

    String url = "jdbc:mysql://localhost:3306/book";

    Connection connection = DriverManager.getConnection(url,"root","root");

    String sql = "select * from user where username = ? and password = ? ";
    PreparedStatement temt = connection.prepareStatement(sql);

    temt.setString(1,request.getParameter("username"));

    temt.setString(2,request.getParameter("password"));

    ResultSet rs = temt.executeQuery();

    while (rs.next())
    {
        out.print(rs.getString("username")+"-"+rs.getString(2)+"<br>");
    }
%>

所有用户

查询的用户