【编程新实务】Lab2 HTML以及J2EE简单编程

项目地址：

github项目地址

实验目的

安装配置好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框架弄明白再来填坑吧~！