2020-10-29

1.登录界面

<title>Title</title>

  <link rel="stylesheet" href="css/bootstrap.css">

 

  <script src="js/jquery.min.js"></script>

  <script src="js/bootstrap.bundle.js"></script>

  <script src="js/bootstrap.js"></script>

 

  <style>

    .container{

      width:20%;

      height: 20%;

      margin-top: 10%;

    }

  </style>

index.jsp

<div class="container">

  <div class="row clearfix">

    <div class="col-md-12 column">

      <form role="form" method="post" action="login.jsp">

        <div class="form-group">

          <label for="exampleInputEmail1">账户名</label><input class="form-control" name="username" id="exampleInputEmail1" type="text" />

        </div>

        <div class="form-group">

          <label for="exampleInputPassword1">密码</label><input class="form-control" name="password" id="exampleInputPassword1" type="password" />

        </div>

        <div class="checkbox">

          <label><input type="checkbox" />Check me out</label>

        </div> <button class="btn btn-default" type="submit">提交</button>

      </form>

    </div>

  </div>

</div>

2.登陆跳转

<%

    request.setCharacterEncoding("utf-8");

    String username = request.getParameter("username");

    String password = request.getParameter("password");

 

    //加载驱动

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

 

    //建立连接

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

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

 

    //sql语句模板

    String sql = "select * from user where username = ? and password = ?";

 

    //创建preparedStatement对象

    PreparedStatement ps =connection.prepareStatement(sql);

 

    //参数赋值

    ps.setString(1,username);

    ps.setString(2,password);

 

    //执行查询

    ResultSet rs = ps.executeQuery();

 

    if (rs.next()){

        //如果查询结果存在相应的数据 表示登陆成功

        session.setAttribute("username",username);//把用户名写入session对象中

        out.print("登陆成功");

        response.sendRedirect("home.jsp");

    }else{

        out.print("登陆失败");//三秒后跳转页面

        response.setHeader("refresh","3;url='home.jsp");

    }

 

%>

3.引用数据库页面

home.jsp

<%

    //加载数据库驱动

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

    //建立数据库连接

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

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

 

    String sql ="select * from user";

 

    PreparedStatement pstm =connection.prepareStatement(sql);

    ResultSet rs=pstm.executeQuery();

 

 

%>

<div class="container">

    <div class="row clearfix">

        <div class="col-md-12 column">

            <h3 style="text-align: center">欢迎来到个人主页</h3>

            <a class="btn" id="modal-198502" role="button" href="#modal-container-198502" data-toggle="modal">添加用户</a>

            <table class="table">

                <thead>

                <th>ID</th>

                <th>用户名</th>

                <th>性别</th>

                <th>年龄</th>

                </thead>

                <tbody>

                <%

                    while(rs.next()){

                        out.print("<tr>");

                        out.print(

                                "<td>"+rs.getString("id")

                                        +"</td><td>"+rs.getString("username")

                                        +"</td><td>"+rs.getString("gender")

                                        +"</td><td>"+rs.getString("age")

                                        +"</td>"

                                        +"<td><a href='delete.jsp?id="+rs.getString("id")+"'>删除</a>"

                                        +"&nbsp;<a href='edit.jsp?id="+rs.getString("id")+"'>修改</a>"+

                                        "</td>"

 

                        );

                        out.print("</tr>");

                    }

                %>

                </tbody>

            </table>

        </div>

    </div>

 

</div>

 

 

 

 

<div class="modal fade" id="modal-container-198502" role="dialog" aria-hidden="true" aria-labelledby="myModalLabel">

    <div class="modal-dialog">

        <div class="modal-content">

            <div class="modal-header">

                <button class="close" aria-hidden="true" type="button" data-dismiss="modal">×</button>

                <h4 class="modal-title" id="myModalLabel">

                    标题

                </h4>

                <button type="button" class="close" data-dismiss="modal" aria-hidden="true"></button>

            </div>

            <form method="post" action="add.jsp">

                <div class="modal-body">

                    <div class="form-group">

                        <label>用户名：</label><input type="text" name="username" class="form-control" />

                    </div>

                    <div class="form-group">

                        <label>密码：</label><input type="text" name="password" class="form-control" />

                    </div>

                    <div class="form-group">

                        <label>性别：</label><input type="text" name="gender" class="form-control" />

                    </div>

                    <div class="form-group">

                        <label>年龄：</label><input type="text" name="age" class="form-control" />

                    </div>

 

                </div>

                <div class="modal-footer">

                    <button class="btn btn-default" type="button" data-dismiss="modal">关闭</button>

                    <button class="btn btn-primary" type="submit">提交</button>

                </div>

            </form>

        </div>

    </div>

</div>

 

 

</body>

</html>

输入数据

若符合数据库中数据

若不符合

3.添加数据
add.jsp

<%

    //加载驱动

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

 

    //建立连接

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

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

 

    //关闭自动提交

    connection.setAutoCommit(false);

 

    //sql语句模板

    String sql = "insert into user (username,password,gender,age,id) value (?,?,?,?,?)";

    //创建preparedStatement对象

    PreparedStatement ps =connection.prepareStatement(sql);

 

    ps.setString(1,user.getUsername());

    ps.setString(2,user.getPassword());

    ps.setInt(3,user.getGender());

    ps.setInt(4,user.getAge());

    ps.setInt(5,7);

    ps.addBatch();

 

    ps.setString(1,user.getUsername()+"123");

    ps.setString(2,user.getPassword()+"123");

    ps.setInt(3,user.getGender());

    ps.setInt(4,user.getAge());

    ps.setInt(5,13);

    ps.addBatch();

 

 

    //事务

    int[] count={0};

    try {

        count = ps.executeBatch();

        connection.commit();

    }catch (SQLException e){

        connection.rollback();

        e.printStackTrace();

    }

    if(count[0] > 0){

        out.print("添加成功");

    }else{

        out.print("添加失败");

    }

    response.setHeader("refresh","3;url=home.jsp");

%>

输入

添加成功

4.删除数据
delect.jsp

<%

    //加载驱动

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

 

    //建立连接

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

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

 

    //sql语句模板

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

    //创建preparedStatement对象

    PreparedStatement ps =connection.prepareStatement(sql);

 

    int id = Integer.parseInt(request.getParameter("id"));

    ps.setInt(1,id);

    int count =ps.executeUpdate();

 

    if(count > 0){

        out.print("删除成功");

    }else{

        out.print("删除失败");

    }

%>

5.修改数据
update.jsp

<%

    //加载驱动

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

 

    //建立连接

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

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

 

    //sql语句模板

    String sql = "update user set username = ? ,password = ? ,gender = ?,age = ? where id = ?";

    //创建preparedStatement对象

    PreparedStatement ps =connection.prepareStatement(sql);

 

    ps.setString(1,user.getUsername());

    ps.setString(2,user.getPassword());

    ps.setInt(3,user.getGender());

    ps.setInt(4,user.getAge());

    ps.setInt(5,user.getId());

 

    int count = ps.executeUpdate();

    if(count > 0){

        out.print("修改成功");

    }else{

        out.print("修改失败");

    }

    response.setHeader("refresh","3;url=home.jsp");

%>

用户编辑页面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<div class="container">

    <div class="row clearfix">

        <div class="col-md-12 column">

            <form role="form" method="post" action="update.jsp">

                <h3 style="margin-top: 20px;">用户编辑</h3>

                <%

                    //加载驱动

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



                    //建立连接

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

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



                    //sql语句模板

                    String sql = "select * from user where id = ?";

                    //创建preparedStatement对象

                    PreparedStatement ps =connection.prepareStatement(sql);



                    int id = Integer.parseInt(request.getParameter("id"));

                    ps.setInt(1,id);



                    ResultSet rs =ps.executeQuery();





                    while (rs.next()){

                        out.print("<input type=\"hidden\" name='id' value="+id+">");

                        out.print("用户名：<input type='text' name='username' class=\"form-control\" value="+rs.getString("username")+"><br>");

                        out.print("密码：<input type='text' name='password' class=\"form-control\" value="+rs.getString("password")+"><br>");

                        out.print("性别：<input type='text' name='gender' class=\"form-control\" value="+rs.getString("gender")+"><br>");

                        out.print("年龄：<input type='text' name='age' class=\"form-control\" value="+rs.getString("age")+"><br>");

                    }

                %>

                <button type="submit" style="width: 100%" class="btn btn-info">提交</button>

            </form>

        </div>

    </div>

</div>

 

</body>

</html>