编写web页面,完成对实验一数据库的操作
操作界面: Op.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html >
<head >
<meta http-equiv ="Content-Type" content ="text/html; charset=UTF-8" >
<title > 唐源棕的数据库</title >
<link href ="Style.css" type ="text/css" rel ="stylesheet" />
</head >
<body >
<form action ="Insert.jsp" >
<table >
<tr > <th colspan ="8" > 数据表person插入信息</th > </tr >
<tr >
<td > username</td >
<td width ="500px" > <input type ="text" name ="username" /> </td >
</tr >
<tr >
<td > name</td >
<td > <input type ="text" name ="name" /> </td >
</tr >
<tr >
<td > age</td >
<td > <input type ="text" name ="age" /> </td >
</tr >
<tr >
<td > telenum</td >
<td > <input type ="text" name ="telenum" /> </td >
</tr >
<tr > <td colspan ="8" > <input type ="submit" value ="插入" > </td > </tr >
</table >
<br >
</form >
<form action ="Drop.jsp" >
<table >
<tr > <th colspan ="8" > 数据表user删除信息</th > </tr >
<tr >
<td > username</td >
<td width ="500px" > <input type ="text" name ="username" /> </td >
</tr >
<tr >
<td colspan ="8" > <input type ="submit" value ="删除" /> </td >
</tr >
</table >
</form >
</body >
</html >
插入数据 Insert .jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html >
<head >
<meta http-equiv ="Content-Type" content ="text/html; charset=UTF-8" >
<title > Insert title here</title >
<link href ="Style.css" type ="text/css" rel ="stylesheet" />
</head >
<body >
<%
Class .forName("com.mysql.jdbc.Driver" ); //加载jdbc驱动
String url = "jdbc:mysql://localhost:3306/mysql?useUnicode" //地址
+ "=true&characterEncoding=utf-8&useSSL=false" ;
Connection connection = (Connection) DriverManager.getConnection(url,"root" ,"tyz19960912" );
String username = request .getParameter("username" );
String name = request .getParameter("name" );
String age = request .getParameter("age" );
String telenum = request .getParameter("telenum" );
Statement statement = (Statement) connection.createStatement(); //连接实例
String query = "SELECT * FROM person where username='" +username+"'" ;
ResultSet result = statement.executeQuery(query);
%>
<h1 > 数据库操作结果</h1 >
<p >
<%
if (result.next ()){
String sql="update person set age='" +age+"',telenum = '" +telenum+"' where username= '" +username+"'" ;
statement.executeUpdate(sql);
out.println("成功更新:" +username);
}
else {
String sql="insert into person(username,name,age,telenum) values('" +username+"','" +name+"',+'" +age+"','" +telenum+"')" ;
statement.executeUpdate(sql);
out.println("成功插入:" +username);
}
%>
<%
query = "select *from users where username= '" +username+"'" ;
ResultSet result1 = statement.executeQuery(query);
if (!result1.next ()){
String sql = "insert into users(username,pass) values('" +username+"','12345')" ;
statement.executeUpdate(sql);
}
%>
<br >
<a href ="Select.jsp" > 查看数据库数据</a >
</p >
</body >
</html >
查看数据 Select .jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html >
<head >
<meta http-equiv ="Content-Type" content ="text/html; charset=UTF-8" >
<title > 数据库person中的数据</title >
<link href ="Style.css" type ="text/css" rel ="stylesheet" />
</head >
<body >
<%
Class .forName("com.mysql.jdbc.Driver" );
String url = "jdbc:mysql://localhost:3306/mysql?useUnicode" //地址
+ "=true&characterEncoding=utf-8&useSSL=false" ;
Connection connection = (Connection) DriverManager.getConnection(url,"root" ,"tyz19960912" );
Statement statement = (Statement) connection.createStatement(); //连接实例
%>
<h1 > 数据表user信息</h1 >
<table >
<tr >
<th > username</th >
<th > password</th >
</tr >
<%
String sql1 = "SELECT * FROM users" ;
ResultSet rs1 = statement.executeQuery(sql1);
while (rs1.next ()) {
String username = rs1.getString("username" );
String password = rs1.getString("pass" );
%>
<tr >
<td > <%= username%> </td >
<td > <%= password%> </td >
</tr >
<%
}
rs1.close();
%>
</table >
<h1 > 数据表person信息</h1 >
<table >
<tr >
<th > username</th >
<th > name</th >
<th > age</th >
<th > telenum</th >
</tr >
<%
String sql2 = "SELECT * FROM person" ;
ResultSet rs2 = statement.executeQuery(sql2);
while (rs2.next ()) {
String username = rs2.getString("username" );
String name = rs2.getString("name" );
int age = rs2.getInt("age" );
String telenum = rs2.getString("telenum" );
%>
<tr >
<td > <%= username%> </td >
<td > <%= name%> </td >
<td > <%= age%> </td >
<td > <%= telenum%> </td >
</tr >
<%
}
rs2.close();
%>
</table >
<p > <a href ="Op.jsp" > 返回数据库操作</a > </p >
</body >
</html >
删除数据库 Drop .jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html >
<head >
<meta http-equiv ="Content-Type" content ="text/html; charset=UTF-8" >
<title > Insert title here</title >
<link href ="Style.css" type ="text/css" rel ="stylesheet" />
</head >
<body >
<%
Class .forName("com.mysql.jdbc.Driver" ); //加载jdbc驱动
String url = "jdbc:mysql://localhost:3306/mysql?useUnicode" //地址
+ "=true&characterEncoding=utf-8&useSSL=false" ;
Connection connection = (Connection) DriverManager.getConnection(url,"root" ,"tyz19960912" );
String username = request .getParameter("username" );
Statement statement = (Statement) connection.createStatement(); //连接实例
String query = "SELECT * FROM person where username='" +username+"'" ;
ResultSet result = statement.executeQuery(query);
%>
<h1 > 数据库操作结果</h1 >
<p >
<%
if (result.next ()){
String sql="delete from users where username= '" +username+"'" ;
statement.executeUpdate(sql);
sql="delete from person where username= '" +username+"'" ;
statement.executeUpdate(sql);
out.println("删除:" +username);
}
else {
out.println("不存在" +username+",删除失败!!!" );
}
%>
<br >
<a href ="Select.jsp" > 查看数据库数据</a >
</p >
</body >
</html >
操作结果
Css表
@charset "UTF-8" ;
body
{
width : 50 % ;
margin : 0 auto ;
text-align : center ;
}
table {
margin : 0 auto ;
border-collapse :collapse ;
}
table , th , td {
border : 1 px solid black ;
}
th , td {
padding : 5 px 10 px ;
}