项目地址:
实验目的
安装配置好Tomcat应用服务器,使用Java进行JSP、Servlet的编写web应用并将其部署到Tomcat上。
实验对应知识点
结合HTML进行JavaEE框架中的JSP、Servlet编程。
实验前任务
学习Java的基本语法以及Java数据库编程。
代码框架
设计思路
本实验主要就是实现网页上的手动输入数据插入或者删除。
index.jsp为主要前端,负责读取用户输入的需要删除或者插入的信息,以表单形式发送到servlet层,然后servlet与数据库连接进行相关操作,并把操作的返回信息存到servletcontent(可以通俗理解为上下文)中。之后进行页面重定向跳转到drop.jsp或者insert.jsp,取出执行结果并输出反馈信息。
jsp和servlet的区别:
前者是html中的java,后者是java中的html
代码实现
src层
bean
Person数据表条目映射
package bean;
public class Person {
private String username;
private String name;//主键
private Integer age;
private String teleno;
public Person(String username,String name,Integer age,String teleno){
this.username = username;
this.name = name;
this.age = age;
this.teleno = teleno;
}
public Person(String username,String name){
this(username,name,null,"");
}
public Person(String username,String name,Integer age){
this(username,name,age,"");
}
public Person(String username,String name,String teleno) {
this(username,name,null,teleno);
}
public String getUsername(){
return username;
}
public String getName(){
return name;
}
public Integer getAge(){
return age;
}
public String getTeleno(){
return teleno;
}
public void setUsername(String username){
this.username = username;
}
public void setName(String name){
this.name = name;
}
public void setAge(Integer age){
this.age = age;
}
public void setTeleno(String teleno){
this.teleno = teleno;
}
}
User数据表条目映射
package bean;
public class User {
private String username;//主键
private String password;
public User(String username,String password){
this.username = username;
this.password = password;
}
public String getUsername() {
return username;
}
public String getPassword() {
return password;
}
public void setUsername(String username) {
this.username = username;
}
public void setPassword(String password) {
this.password = password;
}
}
lab2
数据库连接与操作封装
package lab2;
import java.sql.*;
import org.apache.log4j.*;
import org.apache.tomcat.dbcp.dbcp2.BasicDataSource;
public class DB_conn_op {
Logger logger = LogManager.getLogger(DB_conn_op.class);
private String JDBC_DRIVER = PropertiesUtil.getValue("JDBC_DRIVER");
private String DB_URL = PropertiesUtil.getValue("DB_URL");
private String USER = PropertiesUtil.getValue("USER");
private String PASS = PropertiesUtil.getValue("PASS");
private Connection con = null;
private PreparedStatement pstmt = null;
public DB_conn_op() throws Exception {
con = getConnection();
}
public Connection getConnection() throws Exception{
@SuppressWarnings("resource")
BasicDataSource ds = new BasicDataSource();
ds.setUsername(USER);
ds.setPassword(PASS);
ds.setUrl(DB_URL);
ds.setDriverClassName(JDBC_DRIVER);
//设置最大连接数
ds.setMaxIdle(20);
//设置最大空闲连接数
//设置初始化连接数
ds.setInitialSize(10);
//设置最小空闲连接数
ds.setMinIdle(2);
//设置最大等待时间,单位为毫秒
ds.setMaxWaitMillis(1000);
Connection con = ds.getConnection();
return con;
}
public int executeUpdate(String sql,Object... params) {//插入返回操作的记录数
int rlt = 0;
try{
pstmt = con.prepareStatement(sql);
set_Params(pstmt,params);
rlt = pstmt.executeUpdate();
pstmt.close();
}catch (Exception e){
e.printStackTrace();
}
return rlt;
}
//设置参数
private void set_Params(PreparedStatement pstmt, Object[] params) throws Exception{
if(params!=null){
for(int i=0;i<params.length;i++){
if(params[i] instanceof String) pstmt.setString(i+1,(String)params[i]);
else if(params[i] instanceof Integer) pstmt.setInt(i+1,(Integer)params[i]);
else if(params[i] == null) pstmt.setNull(i+1, Types.INTEGER);
}
}
}
public ResultSet query(String sql,Object... params) throws Exception{//返回是否找到
pstmt = con.prepareStatement(sql);
set_Params(pstmt,params);
ResultSet rs = pstmt.executeQuery();
return rs;
}
public void close() throws Exception{
pstmt.close();
if(this.con!=null) {
this.con.close();
System.out.println("数据库已断开链接!");
}
}
}
读取配置文件的类
package lab2;
import java.io.InputStream;
import java.util.Properties;
public class PropertiesUtil {
public static String getValue(String key){
String ret = null;
try{
InputStream in = PropertiesUtil.class.getResourceAsStream("config.properties");
Properties properties = new Properties();
properties.load(in);
ret = properties.getProperty(key);
in.close();
}catch (Exception e){
e.printStackTrace();
}
return ret;
}
}
配置文件
JDBC_DRIVER = net.sf.log4jdbc.DriverSpy
DB_URL = jdbc:log4jdbc:mysql://localhost:3306/lab1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
USER = root
PASS = 123456
Op
Person的增删改查
package Op;
import java.sql.*;
import lab2.*;
import bean.*;
public class PersonOp {
public void creatTable(DB_conn_op sjk) throws Exception{//创建表
String sql = "create table if not exists person"
+ "("
+ "username varchar(10) not null,"
+ "name varchar(20) not null,"
+ "age int default 18,"
+ "teleno char(11) default '18570253175',"
+ "primary key (name)"
+ ")";
sjk.executeUpdate(sql);
}
public int addPerson(Person p,DB_conn_op sjk) throws Exception{//增加对象
UserOp uo = new UserOp();
if(findPerson(p,sjk)==true) {//person表中存在
updatePerson(p,sjk);
return 1;
}
else {
//person表中不存在,先插入user表
if(!uo.findUser(p.getUsername(), sjk))
uo.addUser(new User(p.getUsername(), "888888"),sjk);
//插入person表中
String sql = "insert into person(username,name,age,teleno) values(?,?,?,?)";
Object[] obj = {p.getUsername(),p.getName(),p.getAge(),p.getTeleno()};
sjk.executeUpdate(sql,obj);
return 2;
}
}
public void deletePerson(String username,DB_conn_op sjk) throws Exception{//删除对象
String sql = "delete from person where username like ? ";
sjk.executeUpdate(sql,username);
}
public boolean findPerson(Person p,DB_conn_op sjk) throws Exception{//查询
String sql4 = "select * from person where name = ?";
ResultSet rs = sjk.query(sql4,p.getName());
return rs.next();
}
public void updatePerson(Person p,DB_conn_op sjk) throws Exception{//更新
String sql = "update person set name = ?,age = ?,teleno = ? where username = ?";
Object[] obj = {p.getName(),p.getAge(),p.getTeleno(),p.getUsername()};
sjk.executeUpdate(sql,obj);
}
public void Print_Table(DB_conn_op sjk) throws Exception{
System.out.println("表person");
System.out.println("字段名username\t字段名name\t字段名age\t\t字段名teleno");
System.out.println("-----------------------------------------------------------");
String sql = "select * from person";
ResultSet rs = sjk.query(sql);
while(rs.next()) {
String username = rs.getString("username");
String name = rs.getString("name");//主键
Integer age = rs.getInt("age");
if(age==0) age = null;
String teleno = rs.getString("teleno");
if(teleno.length()==0) teleno = null;
System.out.println(username + "\t\t" + name + "\t\t" + age +"\t\t"+teleno);
}
rs.close();
}
public void dropTable(DB_conn_op sjk) throws Exception{
String sql = "DROP TABLE person";
sjk.executeUpdate(sql);
}
}
User的增删改查
package Op;
import java.sql.ResultSet;
import lab2.*;
import bean.*;
public class UserOp {
public void creatTable(DB_conn_op sjk) {//创建表
String sql = "create table if not exists users"
+ "( "
+ "username varchar(10) not null,"
+ "pass varchar(8) not null,"
+ "primary key (username)"
+ ")";
sjk.executeUpdate(sql);
}
public void addUser(User u,DB_conn_op sjk) throws Exception{//增加数据
String sql = "insert into users(username,pass) values(?,?)";
Object[] obj = {u.getUsername(),u.getPassword()};
sjk.executeUpdate(sql,obj);
}
public int deleteUser_Username(String username,DB_conn_op sjk) throws Exception {//删除
String sql1 = "delete from person where username like ?";
sjk.executeUpdate(sql1, username);
String sql = "delete from users where username like ?";
return sjk.executeUpdate(sql,username);
}
public boolean findUser(String username,DB_conn_op sjk) throws Exception {//查找
String sql = "select * from users where username = ?";
return sjk.query(sql,username).next();
}
public void updateUser(User u,DB_conn_op sjk) throws Exception{
String sql = "update person set pass = ? where username = ?";
Object[] obj = {u.getPassword(),u.getUsername()};
sjk.executeUpdate(sql,obj);
}
public void Print_Table(DB_conn_op sjk) throws Exception{
System.out.println("表users");
System.out.println("字段名username\t字段名pass");
System.out.println("-------------------------");
String sql = "select * from users";
ResultSet rs = sjk.query(sql);
while(rs.next()) {
String username = rs.getString("username");
String password = rs.getString("pass");//主键
System.out.println(username + "\t\t" + password);
}
}
public void dropTable(DB_conn_op sjk) throws Exception{
String sql = "DROP TABLE users";
sjk.executeUpdate(sql);
}
}
Servlet
Addperson
package Servlet;
import java.io.IOException;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import lab2.DB_conn_op;
import bean.*;
import Op.*;
/**
* Servlet implementation class Addperson
*/
@WebServlet("/Addperson")
public class Addperson extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public Addperson() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setContentType("text/html;charset=UTF-8");
ServletContext context = this.getServletContext();
String username = new String(request.getParameter("username").getBytes("iso-8859-1"),"UTF-8");
String name = new String(request.getParameter("name").getBytes("iso-8859-1"),"UTF-8");
String age1 = request.getParameter("age");
Integer age;
if(age1.length()>0) age = Integer.valueOf(age1);
else age = null;
String teleno = request.getParameter("teleno");
PersonOp po = new PersonOp();
int flag = 0;
try {
flag = po.addPerson(new Person(username,name,age,teleno),(DB_conn_op)context.getAttribute("sjk"));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
context.setAttribute("username1",username);
context.setAttribute("flag",flag);
response.sendRedirect(request.getContextPath()+"/jsp/insert.jsp");
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
Deleteuser
package Servlet;
import java.io.IOException;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import Op.*;
import lab2.*;
/**
* Servlet implementation class Deleteuser
*/
@WebServlet("/Deleteuser")
public class Deleteuser extends HttpServlet {
private static final long serialVersionUID = 1L;
PersonOp po = new PersonOp();
/**
* @see HttpServlet#HttpServlet()
*/
public Deleteuser() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setContentType("text/html;charset=UTF-8");
ServletContext sc = getServletConfig().getServletContext();
UserOp uo = new UserOp();
String username =(String)request.getParameter("username");
int flag = 0;
try {
flag = uo.deleteUser_Username(username,(DB_conn_op)sc.getAttribute("sjk"));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
sc.setAttribute("username2", username);//保存上下文
sc.setAttribute("flag1", flag);//保存上下文
response.sendRedirect(request.getContextPath()+"/jsp/drop.jsp");//重定向
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
log4j日志框架配置文件,目前运行显示无法读取配置文件,待解决
log4j.logger.jdbc.sqlonly=DEBUG,console
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss.SSS} %m%n%n
log4j.logger.jdbc.sqltiming=INFO,console
log4j.logger.jdbc.connection=INFO,console
Webcontent
css
Style.css(主要就设置了居中属性和table线条一些格式),大多还是直接用了标签的属性
@charset "UTF-8";
body
{
width:50%;
margin:0 auto;
text-align:center;
}
table{
margin:0 auto;
border-collapse:collapse;
background-color:#f0f0f0;
}
table,th,td{
border:1px solid black;
}
th,td{
padding:5px 10px;
}
img
两张背景图
jsp
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "lab2.*" %>
<%@ page import = "bean.*" %>
<%@ page import = "Op.*" %>
<%@ page import = "java.sql.*" %>
<%@ page import = "Servlet.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>CodeFriday</title>
<link href = "../css/Style.css" type="text/css" rel="stylesheet"/>
</head>
<body background = "../img/1.jpg">
<%
DB_conn_op sjk = new DB_conn_op();//实例化数据库连接
application.setAttribute("sjk",sjk);//加入servletcontent实现共享
%>
<br><br><br>
<form action = "../Addperson" onsubmit="return check();" method="post">
<table>
<tr><th colspan="8" style="font-size:18pt">数据表Person插入信息</th></tr>
<tr>
<td ><b>username </b></td>
<td width="500px">
<input style="font-size:18pt;width:500px;"
required="required"
placeholder="请输入10个以内字符"
maxlength="10"
type="text"
name ="username"/>
</td>
</tr>
<tr>
<td><b>name</b></td>
<td width="500px">
<input style="font-size:18pt;width:500px;"
required="required"
placeholder="请输入20个以内字符"
maxlength="20"
type="text"
name ="name"/>
</td>
</tr>
<tr>
<td><b>age</b></td>
<td width="500px">
<input style="font-size:18pt;width:500px;"
placeholder="请输入一个整数"
type="text"
id = "age"
name ="age"
/>
</td>
</tr>
<tr>
<td><b>teleno</b></td>
<td width="500px">
<input style="font-size:18pt;width:500px;"
placeholder="请输入11个以内字符"
type="text"
maxlength="11"
name ="teleno"
id = "teleno"
/>
</td>
</tr>
<tr>
<td colspan="8">
<input style="font-size:18pt" type="reset" value = "重置">
<input style="font-size:18pt" type="submit" value = "插入">
</td>
</tr>
</table>
<br>
</form>
<script type="text/javascript" >
function check()
{
var n = document.getElementById("age");
var reg=/^(?:[1-9][0-9]?|1[01][0-9]|120)$/;
n = n.value;
if(n.length == 0) return true;
if(!reg.test(n)){
alert("age必须为1-120整数");
return false;
}
return true;
}
</script>
<form action = "../Deleteuser" onsubmit="return show_confirm();" method="GET">
<table>
<tr><th colspan="8" style="font-size:18pt">数据表user删除信息</th></tr>
<tr>
<td><b>username</b></td>
<td width="500px">
<input style="font-size:18pt;width:500px;"
required="required"
placeholder="请输入10个以内字符"
maxlength="10"
type="text"
name ="username"/></td>
</tr>
<tr>
<td colspan="8">
<input style="font-size:18pt" type="reset" value = "重置">
<input style="font-size:18pt" type="submit" value = "删除">
</td>
</tr>
</table>
</form>
<script type="text/javascript">
function show_confirm()
{
var r=confirm("确认删除?");
return r;
}
</script>
<br><br>
<a href = "show_db.jsp" style="font-size:18pt">查看数据库数据</a>
</body>
</html>
drop.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "lab2.*" %>
<%@ page import = "bean.*" %>
<%@ page import = "Op.*" %>
<%@ page import = "java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>删除</title>
<link href = "../css/Style.css" type="text/css" rel="stylesheet"/>
</head>
<body>
<%
String username = (String)application.getAttribute("username2");
int flag =(int)application.getAttribute("flag1");
%>
<h1>数据库操作结果</h1>
<h2>
<%
if(flag == 1) out.println("删除成功:"+username);
else out.println("删除失败,"+username+"不存在");
%>
</h2>
<a href = "show_db.jsp" style="font-size:18pt">查看数据库数据</a>
</body>
</html>
insert.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "lab2.*" %>
<%@ page import = "bean.*" %>
<%@ page import = "Op.*" %>
<%@ page import = "java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF_8">
<title>插入</title>
<link href = "../css/Style.css" type="text/css" rel="stylesheet"/>
</head>
<body>
<h1>数据库操作结果</h1>
<h2>
<%
String username = (String)application.getAttribute("username1");
int flag = (int)application.getAttribute("flag");
if(flag == 1) out.println("更新成功:"+username);
else out.println("插入成功:"+username);
%>
</h2>
<a href = "show_db.jsp" style="font-size:18pt">查看数据库数据</a>
<br>
<a href = "index.jsp" style="font-size:18pt">返回数据库操作</a>
</body>
</html>
show_db.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "lab2.*" %>
<%@ page import = "bean.*" %>
<%@ page import = "Op.*" %>
<%@ page import = "java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF_8">
<title>查看数据库</title>
<link href = "../css/Style.css" type="text/css" rel="stylesheet"/>
</head>
<body background = "../img/2.jpg">
<%
ServletContext context = this.getServletContext();
DB_conn_op sjk = (DB_conn_op)context.getAttribute("sjk");
%>
<h1>数据表user信息</h1>
<table>
<tr>
<th style="font-size:18pt">username</th>
<th style="font-size:18pt">password</th>
</tr>
<%
String sql = "select * from users";
ResultSet rs = sjk.query(sql);
while(rs.next()) {
String username = rs.getString("username");
String password = rs.getString("pass");
%>
<tr>
<td><%= username %></td>
<td><%= password %></td>
</tr>
<%
}
rs.close();
%>
</table>
<br>
<h1>数据表person信息</h1>
<table>
<tr>
<th style="font-size:18pt">username</th>
<th style="font-size:18pt">name</th>
<th style="font-size:18pt">age</th>
<th style="font-size:18pt">teleno</th>
</tr>
<%
String sql1 = "select * from person";
ResultSet rs1 = sjk.query(sql1);
while(rs1.next()) {
String username = rs1.getString("username");
String name = rs1.getString("name");//主键
Integer age = rs1.getInt("age");
if(age==0) age = null;
String teleno = rs1.getString("teleno");
if(teleno.length()==0) teleno = null;
%>
<tr>
<td><%= username %></td>
<td><%= name %></td>
<td><%= age %></td>
<td><%= teleno %></td>
</tr>
<%
}
rs1.close();
%>
</table>
<a href = "index.jsp" style="font-size:18pt">返回数据库操作</a>
</body>
</html>
运行截图
index.jsp
带正则判断age栏输入合法性,其实可以直接用input标签的属性控制实现
删除加了一个confirm属性避免误删
总结
HTML参考手册
Servlet基础
jsp基础
之前有过一些html基础 ,但是没系统学,拿现成的源码涂涂改改部署到虚拟机上过,html参考手册可以当作一本字典一样,读一篇文章时不认识的字就直接查。因此从0开始做这个实验前后花了大概两天时间。对一个网站(前端+后端)如何开发有了初步了解,不过实验一中就想用Log4j日志框架记录完整的sql语句以及执行历史,但是相关jar包导入了也不行,而在实验二中还是不行,吐了。等之后把log4j框架弄明白再来填坑吧~!