1、编写一个学生信息注册页面,并把学生信息写入数据库名称为stuinfo的数据库中
<html>
<head>
<title>201819101003</title>
<link rel="stylesheet" href="css/bootstrap.css">
<script src="js/jquery.min.js"></script>
<script src="js/bootstrap.js"></script>
<script src="js/bootstrap.bundle.js"></script>
<style>
.container{
width: 20%;
height: 20%;
margin-top: 10%;
}
</style>
</head>
<body>
<div class="container">
<div class="row clearfix">
<div class="col-md-12 column">
<form method="post" action="login.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>
</div>
</div>
</div>
</body>
</html>
2、编写一个学生登录页面(从stuinfo(books)数据库中读取学生信息)
<%
//修改request中的数据编码
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/books";
Connection connection = DriverManager.getConnection(url,"root","root");
//sql 模板
String sql = "select * from user where username = ? and password = ?";
//使用PreparedStatement对象执行sql语句
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1,username);
ps.setString(2,password);
ResultSet rs = ps.executeQuery();
if(rs.next()){
//表示登录成功
session.setAttribute("username",username);
response.sendRedirect("home.jsp");
}else{
//表示登录未成功
out.print("登录失败,3秒之后跳转登录页面...");
response.setHeader("refresh","3;url=login.jsp");
}
%>
3、编写学生管理页面,实现学生信息的增删改查等操作
增—zjy和zjy111
<%
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接
String url = "jdbc:mysql://localhost:3306/books"; //数据库连接地址
Connection connection = getConnection(url,"root","root"); //第一个root为数据库的用户名 第二个root为数据库的密码
connection.setAutoCommit(false); //关闭自动提交
String sql = "insert into user (username,password,gender,age,id) values (?,?,?,?,?)"; //在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,0);
ps.addBatch();//添加到批处理
ps.setString(1,user.getUsername()+"111");
ps.setString(2,user.getPassword()+"111");
ps.setInt(3,user.getGender());
ps.setInt(4,user.getAge());
ps.setInt(5,0);
ps.addBatch();//添加到批处理 insert update delete
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");
//事务
//mysql 数据库引擎 MyISAM 不支持 事务
//默认 Innodb 支持事务
%>
删–zjy111
<%
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接
String url = "jdbc:mysql://localhost:3306/books"; //数据库连接地址
Connection connection = DriverManager.getConnection(url,"root","root"); //第一个root为数据库的用户名 第二个root为数据库的密码
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("删除失败");
}
%>
改查—查找zjy,并将zjy密码改为123456,年龄改为21
<html>
<head>
<title>编辑页面</title>
<%--引入css文件和js文件--%>
<link rel="stylesheet" href="css/bootstrap.css">
<script src="js/jquery.min.js"></script>
<script src="js/bootstrap.js"></script>
<script src="js/bootstrap.bundle.js"></script>
<style>
.container{
width: 50%;
margin-top:20px;
}
</style>
</head>
<body>
<div class="container">
<div class="row clearfix">
<div class="col-md-12 column">
<h3 style="text-align: center">编辑页面</h3>
<form method="post" action="update.jsp">
<%
int id = Integer.parseInt(request.getParameter("id"));
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接
String url = "jdbc:mysql://localhost:3306/books";//数据库连接地址
Connection connection = DriverManager.getConnection(url, "root", "root");
//stmt.executeUpdate()执行增删改的驱动
String sql = "select * from user where id=?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1,id);
ResultSet rs = ps.executeQuery();
while (rs.next()){
out.print("<input type='hidden' name='id' value="+rs.getString("id")+">");
out.print("用户名:<input type='text' class='form-control' name='username' value="+rs.getString("username")+"><br>");
out.print("密码:<input type='text' class='form-control' name='password' value="+rs.getString("password")+"><br>");
out.print("性别:<input type='text' class='form-control' name='gender' value="+rs.getString("gender")+"><br>");
out.print("年龄:<input type='text' class='form-control' name='age' value="+rs.getString("age")+"><br>");
}
%>
<button type="submit" style="width: 100%" class="btn btn-info">提交</button>
</form>
</div>
</div>
</div>
</body>
</html>
<%
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接
String url = "jdbc:mysql://localhost:3306/books";//数据库连接地址
Connection connection = DriverManager.getConnection(url, "root", "root");
//stmt.executeUpdate()执行增删改的驱动
String sql = "update user set username = ?, password = ?,gender = ?,age = ? where id = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1,user.getUsername());
ps.setString(2,user.getUsername());
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("修改失败");
}
%>