首先要完成一个java web连接数据库的增删改查,要有前台的.jsp文件,还要有后台的文件,前台的文件负责产生网页,来直接使进行增删改查,而后台的分为util层,这一层主要负责完成与数据库的连接,代码如下。
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//连接数据库
public class DButil {
public static String db_url = "jdbc:mysql://localhost:3306/haohaoxuexi";
public static String db_user = "root";
public static String db_pass = "zxh521+.";
public static Connection getConn () {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");//加载驱动
conn = DriverManager.getConnection(db_url, db_user, db_pass);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void main(String[] args) {
try
{
//第三步:获取连接类实例con,用con创建Statement对象类实例 sql_statement
Connection con = getConn();
Statement sql_statement = con.createStatement();
String query = "select * from tiantianxiangshang";
ResultSet result = sql_statement.executeQuery(query);
//显示数据中network表中的内容:
System.out.println("数据库haohaoxuexi表tiantianxiangshang中的数据如下:");
System.out.println("------------------------");
System.out.println("name" + " " + "teacher" + " " + "classroom");
System.out.println("------------------------");
//对获得的查询结果进行处理,对Result类的对象进行操作
while (result.next())
{
String name = result.getString("name");
String teacher = result.getString("teacher");
String classroom = result.getString("classroom");
//取得数据库中的数据
System.out.println(" " +name + " " +teacher+ " " + classroom);
}
} catch (SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
/**
* 关闭连接
* @param state
* @param conn
*/
public static void close (Statement state, Connection conn) {
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close (ResultSet rs, Statement state, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
这个类主要完成数据库的连接,并且把数据库里的内容显示出来。要实现数据库的连接首先要完成驱动,然后完成与conn的连接。如果把数据库比喻成一个小岛,那么数据库的连接就相当于在连接小岛时搭建一座桥,而Connection con = getConn();就是实现搭建桥的过程。
在后台的层中有一层为entity层,这一层建立实体类,里面建立的是数据库中的变量,然后建立set、get方法,并且有无参和有参的构造函数。代码如下。
package entity;
public class course {
private int id;
private String name;
private String teacher;
private String classroom;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTeacher() {
return teacher;
}
public void setTeacher(String teacher) {
this.teacher = teacher;
}
public String getClassroom() {
return classroom;
}
public void setClassroom(String classroom) {
this.classroom = classroom;
}
public course() {}
public course(int id, String name, String teacher, String classroom) {
this.id = id;
this.name = name;
this.teacher = teacher;
this.classroom = classroom;
}
public course(String name, String teacher, String classroom) {
this.name = name;
this.teacher = teacher;
this.classroom = classroom;
}
}
建立dao层,dao层是工具层,这一层主要是进行与数据库之间的操作,搭建后台与数据库之间的桥梁。代码如下。
package dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import entity.course;
import util.DButil;
//与数据库建立联系
public class coursedao {
public boolean add(course course) {//建立course类的对象
String sql="insert into tiantianxiangshang(name,teacher,classroom)values('"+course.getName()+"','"+course.getTeacher()+"','"+course.getClassroom()+"')";
//添加语句,调用course类里面的方法,完成添加
Connection conn=DButil.getConn();//建立桥
Statement state=null;//建立车
Boolean f=false;
int a=0;
try {
state = conn.createStatement();//桥给车一个可以运输货物的功能
a=state.executeUpdate(sql);//把运输货物的多少赋值给a
} catch (Exception e) {
e.printStackTrace();
} finally {
DButil.close(state, conn);//在运输结束后,毁掉桥和车
}
if (a > 0) {
f = true;
}
return f;
}
public boolean delete (int id) {
boolean f = false;
String sql = "delete from course where id='" + id + "'";
Connection conn = DButil.getConn();
Statement state = null;
int a = 0;
try {
state = conn.createStatement();
a = state.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DButil.close(state, conn);
}
if (a > 0) {
f = true;
}
return f;
}
public boolean update(course course) {
String sql = "update course set name='" + course.getName() + "', teacher='" + course.getTeacher() + "', classroom='" + course.getClassroom()
+ "' where id='" + course.getId() + "'";
Connection conn = DButil.getConn();
Statement state = null;
boolean f = false;
int a = 0;
try {
state = conn.createStatement();
a = state.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DButil.close(state, conn);
}
if (a > 0) {
f = true;
}
return f;
}
public boolean name(String name) {
Boolean flag=false;
String sql="select from tiantianxiangshang where name='"+name+"'";
Connection conn=DButil.getConn();//建立桥
Statement state=null;//建立车
ResultSet rs = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DButil.close(rs, state, conn);
}
return flag;
}
public course getCourseById(int id) {
String sql = "select * from course where id ='" + id + "'";
Connection conn = DButil.getConn();
Statement state = null;
ResultSet rs = null;
course course = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
String name = rs.getString("name");
String teacher = rs.getString("teacher");
String classroom = rs.getString("classroom");
course = new course(id, name, teacher, classroom);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DButil.close(rs, state, conn);
}
return course;
}
public course getCourseByName(String name) {
String sql = "select * from course where name ='" + name + "'";
Connection conn = DButil.getConn();
Statement state = null;
ResultSet rs = null;
course course = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
int id = rs.getInt("id");
String teacher = rs.getString("teacher");
String classroom = rs.getString("classroom");
course = new course(id, name, teacher, classroom);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DButil.close(rs, state, conn);
}
return course;
}
public List<course> search(String name, String teacher, String classroom) {
String sql = "select * from course where ";
if (name != "") {
sql += "name like '%" + name + "%'";
}
if (teacher != "") {
sql += "teacher like '%" + teacher + "%'";
}
if (classroom != "") {
sql += "classroom like '%" + classroom + "%'";
}
List<course> list = new ArrayList<>();
Connection conn = DButil.getConn();
Statement state = null;
ResultSet rs = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
Course bean = null;
while (rs.next()) {
int id = rs.getInt("id");
String name2 = rs.getString("name");
String teacher2 = rs.getString("teacher");
String classroom2 = rs.getString("classroom");
bean = new course(id, name2, teacher2, classroom2);
list.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DButil.close(rs, state, conn);
}
return list;
}
public List<course> list() {
String sql = "select * from course";
List<course> list = new ArrayList<>();
Connection conn = DButil.getConn();
Statement state = null;
ResultSet rs = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
course bean = null;
while (rs.next()) {
int id = rs.getInt("id");
String name2 = rs.getString("name");
String teacher2 = rs.getString("teacher");
String classroom2 = rs.getString("classroom");
bean = new course(id, name2, teacher2, classroom2);
list.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DButil.close(rs, state, conn);
}
return list;
}
}
这一层主要是完成与数据库之间的操作,比如在写添加这个方法时,建立实体类的对象,然后写添加语句,之后是搭建与小岛之间的桥梁,然后建立一个可也在桥梁上运行货物的车,但是先给它赋一个空指针,使用try catch语句,在try语句中给车一个可以拉货物的功能,然后把车拉了的货物赋给一个常量。最后判断如果这个常量的值大于0,那么就返回true,否则就返回false。里面还有可以查找课程名称的方法name,这个方法在service类中可以用来查找输入的名字是否已经存在。还有其他的几个方法。
另一个层是service层,这个层是服务层,用来沟通dao层和sevlet层之间的桥梁。代码如下。
package service;
import java.awt.List;
import dao.coursedao;
import entity.course;
/*服务层
* 实现sevelet与dao层之间的联系
*/
public class courseservice {
coursedao cdao=new coursedao();
public boolean add(course course) {
Boolean f=false;
if(!cdao.name(course.getName())) {
cdao.add(course);
f=true;
}
return f;
}
public void del(int id) {
cdao.delete(id);
}
public void update(course course) {
cdao.update(course);
}
public course getCourseById(int id) {
return cdao.getCourseById(id);
}
public course getCourseByName(String name) {
return cdao.getCourseByName(name);
}
public List<course> search(String name, String teacher, String classroom) {
return cdao.search(name, teacher, classroom);
}
public List<course> list() {
return cdao.list();
}
}
package service;
import java.awt.List;
import dao.coursedao;
import entity.course;
/*服务层
* 实现sevelet与dao层之间的联系
*/
public class courseservice {
coursedao cdao=new coursedao();
public boolean add(course course) {
Boolean f=false;
if(!cdao.name(course.getName())) {
cdao.add(course);
f=true;
}
return f;
}
public void del(int id) {
cdao.delete(id);
}
public void update(course course) {
cdao.update(course);
}
public course getCourseById(int id) {
return cdao.getCourseById(id);
}
public course getCourseByName(String name) {
return cdao.getCourseByName(name);
}
public List<course> search(String name, String teacher, String classroom) {
return cdao.search(name, teacher, classroom);
}
public List<course> list() {
return cdao.list();
}
}
首先建立dao层类的对象,以添加为例,如果输入的名字不存在,则成功输入,否则返回false。
另外一层是servlet层,这一层是完成与后台之间的操作。代码如下。
package sevelet;
import java.io.IOException;
import java.util.List;
import javax.sevelet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class courseservlet extends HttpServlet {
private static final long serialVersionUID = 1L;
courseservice service = new courseservice();
/**
* 方法选择
*/
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
String method = req.getParameter("method");
if ("add".equals(method)) {
add(req, resp);
} else if ("del".equals(method)) {
del(req, resp);
} else if ("update".equals(method)) {
update(req, resp);
} else if ("search".equals(method)) {
search(req, resp);
} else if ("getcoursebyid".equals(method)) {
getCourseById(req, resp);
} else if ("getcoursebyname".equals(method)) {
getCourseByName(req, resp);
} else if ("list".equals(method)) {
list(req, resp);
}
}
/**
* 添加
* @param req
* @param resp
* @throws IOException
* @throws ServletException
*/
private void add(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
req.setCharacterEncoding("utf-8");
String name = req.getParameter("name");
String teacher = req.getParameter("teacher");
String classroom = req.getParameter("classroom");
Course course = new Course(name, teacher, classroom);
//添加后消息显示
if(service.add(course)) {
req.setAttribute("message", "添加成功");//传递参数给jsp
req.getRequestDispatcher("add.jsp").forward(req,resp);//与前台add.jsp文件取得联系
} else {
req.setAttribute("message", "课程名称重复,请重新录入");
req.getRequestDispatcher("add.jsp").forward(req,resp);
}
}
}
这一层里面我只写了添加的方法。getParameter方法用来接受在文本框中输入的内容。将.jsp中的name、teacher、classroom重新赋值给字符串类型的name,teacher,classroom。然后建立course类的对象,然后把之间字符串类型的name,teacher,classroom封装在一个包里,方便直接使用。使用try catch语句,在try语句中判断,如果在service类中的add方法可以正确运行,那么就使用setAttribute方法,传递参数到.jsp文件,然后用getRequestDispatcher方法,实现servlet与.jsp文件的连接。
后台的文件就是这些。下面是前台,需要运行然后出现网页,直接进行增改查的.jsp文件。
显示主页的为index.jsp文件。代码如下。
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>首页</title>
<style>
.a{
font-size: 26px;
margin-top: 20px;
}
</style>
</head>
<body>
<div align="center">//给文本框设置格式
<h1 style="color: red;">课程信息管理系统</h1>
<div class="a">
<a href="add.jsp">课程信息录入</a>
</div>
<div class="a">
<a href="courseservlet?method=list">课程信息修改</a>
</div>
<div class="a">
<a href="del.jsp">课程信息删除</a>
</div>
<div class="a">
<a href="search.jsp">课程信息查询</a>
</div>
</div>
</body>
</html>
在这个里面主要完成给文本框设置格式,然后可以进行增删改查的选择,在选择之后直接进去相关的网页来进行操作。
添加的.jsp代码如下。
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>//设置字体的格式
.a{
margin-top: 20px;
}
.b{
font-size: 20px;
width: 160px;
color: white;
background-color: greenyellow;
}
</style>
</head>
<body>
<%
Object message = request.getAttribute("message");//接收从sevelet里面传来的参数
if(message!=null && !"".equals(message)){
%>
<script type="text/javascript">
alert("<%=request.getAttribute("message")%>");
</script>
<%} %>
<div align="center">//给文本框设置格式
<h1 style="color: red;">课程信息录入</h1>
<a href="index.jsp">返回主页</a>
<form action="courseservlet?method=add" method="post" onsubmit="return check()">
<div class="a">
课程名称<input type="text" id="name" name="name"/>
</div>
<div class="a">
任课教师<input type="text" id="teacher" name="teacher" />
</div>
<div class="a">
上课地点<input type="text" id="classroom" name="classroom" />
</div>
<div class="a">
<button type="submit" class="b">保 存</button>
</div>
</form>
</div>
<script type="text/javascript">
function check() {
var name = document.getElementById("name");;
var teacher = document.getElementById("teacher");
var classroom = document.getElementById("classroom");
//非空
if(name.value == '') {
alert('课程名称为空');
name.focus();
return false;
}
if(teacher.value == '') {
alert('教师为空');
teacher.focus();
return false;
}
if(classroom.value == '') {
alert('上课地点为空');
classroom.focus();
return false;
}
//教师
if(teacher.value != 'wangjianmin' && teacher.value != 'wanghui' && teacher.value != 'liudan' && teacher.value != 'liulijia' && teacher.value != 'yangziguang'){
alert('教师名称错误');
return false;
}
//教室
if(!/^jijiao/.test(classroom.value) && !/^yijiao/.test(classroom.value) && !/^erjiao/.test(classroom.value) && !/^sanjiao/.test(classroom.value)) {
alert('上课地点错误');
return false;
}
}
</script>
</body>
</html>
在servlet类中层使用setAttribute方法来向.jsp文件中传递参数,那么在.jsp文件中就要使用getAttribute方法来接收从servlet文件中传递来的参数。同样需要对文本框设置格式,然后在添加时判断输入的课程名称、任课老师、上课地点是否为空,如果为空就使用alert来弹出一个框提示错误。然后判断输入的老师是否为这五个老师中的一个,否则提示错误。然后运用四则公式判断输入的上课地点是否为以基教、一教、二教、三教开头,否则提示错误。
然后是删,删分为两步,第一步是输入课程名称,然后点击删除,出现这一科课程的全部信息,然后提示是否真的删除。代码如下。
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
.a{
margin-top: 20px;
}
.b{
font-size: 20px;
width: 160px;
color: white;
background-color: greenyellow;
}
</style>
</head>
<body>
<%
Object message = request.getAttribute("message");
if(message!=null && !"".equals(message)){
%>
<script type="text/javascript">
alert("<%=request.getAttribute("message")%>");
</script>
<%} %>
<div align="center">
<h1 style="color: red;">课程信息删除</h1>
<a href="index.jsp">返回主页</a>
<form action="CourseServlet?method=getcoursebyname" method="post" onsubmit="return check()">
<div class="a">
课程名称<input type="text" id="name" name="name"/>
</div>
<div class="a">
<button type="submit" class="b">查 找</button>
</div>
</form>
</div>
<script type="text/javascript">
function check() {
var name = document.getElementById("name");;
//非空
if(name.value == '') {
alert('课程名称为空');
name.focus();
return false;
}
}
</script>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
.a{
margin-top: 20px;
}
.b{
font-size: 20px;
width: 160px;
color: white;
background-color: greenyellow;
}
.tb, td {
border: 1px solid black;
font-size: 22px;
}
</style>
</head>
<body>
<div align="center">
<h1 style="color: red;">课程信息删除</h1>
<a href="index.jsp">返回主页</a>
<table class="tb">
<tr>
<td>课程名称</td>
<td>${course.name}</td>
</tr>
<tr>
<td>任课教师</td>
<td>${course.teacher}</td>
</tr>
<tr>
<td>上课地点</td>
<td>${course.classroom}</td>
</tr>
</table>
<div class="a">
<a onclick="return check()" href="CourseServlet?method=del&id=${course.id}">删 除</a>
</div>
</div>
<script type="text/javascript">
function check() {
if (confirm("真的要删除吗?")){
return true;
}else{
return false;
}
}
</script>
</body>
</html>
然后是修改的.jsp文件。代码如下。
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
.a{
margin-top: 20px;
}
.b{
font-size: 20px;
width: 160px;
color: white;
background-color: greenyellow;
}
</style>
</head>
<body>
<%
Object message = request.getAttribute("message");
if(message!=null && !"".equals(message)){
%>
<script type="text/javascript">
alert("<%=request.getAttribute("message")%>");
</script>
<%} %>
<div align="center">
<h1 style="color: red;">课程信息修改</h1>
<a href="index.jsp">返回主页</a>
<form action="CourseServlet?method=update" method="post" onsubmit="return check()">
<div class="a">
课程名称<input type="text" id="name" name="name" value="${course.name}"/>
</div>
<div class="a">
任课教师<input type="text" id="teacher" name="teacher" value="${course.teacher}"/>
</div>
<div class="a">
上课地点<input type="text" id="classroom" name="classroom" value="${course.classroom}"/>
</div>
<input type="hidden" id="id" name="id" value="${course.id}"/>
<div class="a">
<button type="submit" class="b">修 改</button>
</div>
</form>
</div>
<script type="text/javascript">
function check() {
var name = document.getElementById("name");;
var teacher = document.getElementById("teacher");
var classroom = document.getElementById("classroom");
//非空
if(name.value == '') {
alert('课程名称为空');
name.focus();
return false;
}
if(teacher.value == '') {
alert('教师为空');
teacher.focus();
return false;
}
if(classroom.value == '') {
alert('上课地点为空');
classroom.focus();
return false;
}
//教师
if(teacher.value != '王建民' && teacher.value != '王辉' && teacher.value != '刘丹' && teacher.value != '刘立嘉' && teacher.value != '杨子光'){
alert('教师名称错误');
return false;
}
//教室
if(!/^基教/.test(classroom.value) && !/^一教/.test(classroom.value) && !/^二教/.test(classroom.value) && !/^三教/.test(classroom.value)) {
alert('上课地点错误');
return false;
}
}
</script>
</body>
</html>
在进行修改的时候也会进行判断,是否是这五个老师中的一个,并且判断是否是以基教、一教、二教、三教开头的。
然后是查询,查询要求实现模糊查询。代码如下。
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
.a{
margin-top: 20px;
}
.b{
font-size: 20px;
width: 160px;
color: white;
background-color: greenyellow;
}
</style>
</head>
<body>
<div align="center">
<h1 style="color: red;">课程信息查询</h1>
<a href="index.jsp">返回主页</a>
<form action="CourseServlet?method=search" method="post" onsubmit="return check()">
<div class="a">
课程名称<input type="text" id="name" name="name"/>
</div>
<div class="a">
任课教师<input type="text" id="teacher" name="teacher" />
</div>
<div class="a">
上课地点<input type="text" id="classroom" name="classroom" />
</div>
<div class="a">
<button type="submit" class="b">查 询</button>
</div>
</form>
</div>
<script type="text/javascript">
function check() {
var name = document.getElementById("name");;
var teacher = document.getElementById("teacher");
var classroom = document.getElementById("classroom");
//非空
if(name.value == '' && teacher.value == '' && classroom.value == '') {
alert('请填写一个条件');
return false;
}
}
</script>
</body>
</html>
要填写一个条件,否则提示错误。
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
.a{
margin-top: 20px;
}
.b{
font-size: 20px;
width: 160px;
color: white;
background-color: greenyellow;
}
.tb, td {
border: 1px solid black;
font-size: 22px;
}
</style>
</head>
<body>
<div align="center">
<h1 style="color: red;">课程信息列表</h1>
<a href="index.jsp">返回主页</a>
<table class="tb">
<tr>
<td>id</td>
<td>课程名称</td>
<td>任课教师</td>
<td>上课地点</td>
</tr>
<!-- forEach遍历出adminBeans -->
<c:forEach items="${courses}" var="item" varStatus="status">
<tr>
<td>${item.id}</td>
<td><a>${item.name}</a></td>
<td>${item.teacher}</td>
<td>${item.classroom}</td>
</tr>
</c:forEach>
</table>
</div>
</body>
</html>
然后输入一个条件之后就会遍历出相关的所有信息。