实验九 JDBC数据库访问二
一、实验目标:
掌握并熟练使用JDBC查询数据库
区分精确查询与模糊查询,掌握预处理语句。
二、实验内容:
在实验八的基础上完成
(必做)完成全部查询和条件查询
用MVC模式完成查询全部记录及按关键字商品号查询(精确查询)。
(选作):按商品名模糊查询,按某个排序查询或者输入行号,定位某条记录等。。。
JavaBean的设计要求:
Message.java:数据类,与表相对应的数据模型
MessageDAO.java:业务类,所有和表相关的操作如查询、添加、删除、修改等操作都封装在此类中。
三、注意事项
⒈认真填写实验报告
⒉遵守实验室各项制度,服从实验指导教师的安排
⒊按规定的时间完成实验
实验十 JDBC数据库访问三
一、实验目标:
掌握并熟练使用JDBC操作数据库:添加,删除及修改。
练习使用数据库连接池及其他相关技术。
二、实验内容:
- 在添加记录的基础上,实现对表记录的更新、删除;
a) 用MVC模式来完成
b) 要考虑操作过程中的数据安全性问题,比如添加、更新记录要检查用户名,删除如果是基于编号的删除要考虑编号是否存在 - (选做)尝试用数据连接池来完成数据库的连接。
- 用DAO设计模式对程序进行重构。
三、注意事项
⒈认真填写实验报告
⒉遵守实验室各项制度,服从实验指导教师的安排
⒊按规定的时间完成实验
项目代码:
package cn.jdbc.domain;
public class Student
{
private String sno; // 学号
private String sname; // 姓名
private int sage; // 年龄
public String getSno()
{
return sno;
}
public void setSno(String sno)
{
this.sno = sno;
}
public String getSname()
{
return sname;
}
public void setSname(String sname)
{
this.sname = sname;
}
public int getSage()
{
return sage;
}
public void setSage(int sage)
{
this.sage = sage;
}
}
package cn.jdbc.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import cn.jdbc.domain.Student;
import cn.jdbc.utils.Utils;
/*
* 完成对数据库的增删改查操作
*/
public class StudentDao {
//为学生表添加数据
public boolean insert(Student student) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = Utils.getConnection();
// SQL语句
String sql="insert into student values(?,?,?)";
//得到预编译对象
stmt=conn.prepareStatement(sql);
stmt.setString(1, student.getSno());
stmt.setString(2, student.getSname());
stmt.setInt(3, student.getSage());
int num = stmt.executeUpdate();
if (num > 0) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
Utils.release(rs, stmt, conn);
}
return false;
}
//查询所有数据
public List < Student > findAll() {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
List < Student > list = new ArrayList < Student > ();
try {
// 获得数据的连接
conn = Utils.getConnection();
// SQL语句
String sql="select * from student";
//得到预编译对象
stmt=conn.prepareStatement(sql);
rs = stmt.executeQuery();
// 处理结果集,遍历rs结果集
while (rs.next()) {
Student student = new Student();
student.setSno(rs.getString("sno"));
student.setSname(rs.getString("sname"));
student.setSage(rs.getInt("sage"));
list.add(student);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
Utils.release(rs, stmt, conn);
}
return null;
}
// 根据id查找指定的student
public Student find(String id) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
Student student = new Student();
try {
// 获得数据的连接
conn = Utils.getConnection();
//SQL语句
String sql = "select * from student where sno=?";
//得到预编译对象
stmt=conn.prepareStatement(sql);
stmt.setString(1, id);
rs = stmt.executeQuery();
// 处理结果集
while (rs.next()) {
student.setSno(rs.getString("sno"));
student.setSname(rs.getString("sname"));
student.setSage(rs.getInt("sage"));
return student;
}
return null;
} catch (Exception e) {
e.printStackTrace();
} finally {
Utils.release(rs, stmt, conn);
}
return null;
}
//删除学生数据
public boolean delete(String id){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = Utils.getConnection();
//sql语句
String sql = "delete from student where sno=?";
//获取预处理对象
stmt= conn.prepareStatement(sql);
stmt.setString(1, id);
int num = stmt.executeUpdate();
if (num > 0) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
Utils.release(rs, stmt, conn);
}
return false;
}
// 修改用户
public boolean update(Student student) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
//获得数据的连接
con = Utils.getConnection();
//sql语句
String sql="update student set sname=?,sage=? where sno =?";
//得到预编译对象
stmt=con.prepareStatement(sql);
stmt.setString(1, student.getSname());
stmt.setInt(2, student.getSage());
stmt.setString(3, student.getSno());
int num = stmt.executeUpdate();
if (num > 0) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
Utils.release(rs, stmt, con);
}
return false;
}
}
package cn.jdbc.servlet;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.jdbc.dao.StudentDao;
import cn.jdbc.domain.Student;
public class Display extends HttpServlet {
/**
* Constructor of the object.
*/
public Display() {
super();
}
/**
* Destruction of the servlet. <br>
*/
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
request.setCharacterEncoding("UTF-8");
//获取数据库操作的对象
StudentDao dao = new StudentDao();
//获取数组对象
List<Student> list = new ArrayList<Student>();
//获取查询的学号
String search_sno = request.getParameter("search_no");
//获取添加的数据
String add_sno = request.getParameter("add_sno");
String add_sname = request.getParameter("add_sname");
String add_sage = request.getParameter("add_sage");
//获取修改的数据
String cg_sno = request.getParameter("cg_sno");
String cg_sname = request.getParameter("cg_sname");
String cg_sage = request.getParameter("cg_sage");
//获取删除的学号
String del_sno = request.getParameter("del_sno");
//查询数据
if(search_sno!=null){
Student student= dao.find(search_sno);
list.add(student);
search_sno="";
}else{//显示所有的数据
list=dao.findAll();
}
//添加数据
if(add_sage!=null){
Student student = new Student();
student.setSno(add_sno);
student.setSname(add_sname);
student.setSage(Integer.parseInt(add_sage));
dao.insert(student);
list=dao.findAll();
add_sno="";
add_sname="";
add_sage="";
}
//修改数据
if(cg_sage!=null){
Student student = new Student();
student.setSno(cg_sno);
student.setSname(cg_sname);
student.setSage(Integer.parseInt(cg_sage));
dao.update(student);
list=dao.findAll();
cg_sno="";
cg_sname="";
cg_sage="";
}
//删除数据
if(del_sno!=null){
dao.delete(del_sno);
list=dao.findAll();
del_sno="";
}
//传递数组到jsp页面
request.setAttribute("list", list);
request.getRequestDispatcher("index.jsp").forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
// TODO Auto-generated method stub
doGet(request, response);
}
/**
* Initialization of the servlet. <br>
*
* @throws ServletException if an error occurs
*/
public void init() throws ServletException {
// Put your code here
}
}
package cn.jdbc.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Utils {
// 加载驱动,并建立数据库连接
public static Connection getConnection() throws SQLException,
ClassNotFoundException {
// 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 获取数据库连接
String url = "jdbc:mysql://localhost:3306/javaweb?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf-8";
// 用户名
String username = "root";
// 密码
String password = "1234";
Connection conn = DriverManager.getConnection(url, username,
password);
return conn;
}
// 关闭数据库连接,释放资源
public static void release(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" contentType="text/html; charset=UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML>
<html>
<head>
<base href="<%=basePath%>">
<style type="text/css">
body
{
text-align: center;
}
</style>
<title>学生数据</title>
<meta charset="UTF-8">
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<h2>学生信息查询系统</h2>
<table align="center" border="1" cellpadding="0" cellspacing="0" width="60%">
<thead>
<tr>
<th>学号</th>
<th>姓名</th>
<th>年龄</th>
</tr>
</thead>
<tbody>
<c:forEach items="${list}" var="i">
<tr >
<td>${i.sno}</td>
<td>${i.sname}</td>
<td>${i.sage}</td>
</tr>
</c:forEach>
</tbody>
</table>
<br><br><br>
<table align="center" border="0" cellpadding="0" cellspacing="0" width="40%">
<tr>
<td><a href="display.do">首页</a></td>
<td><a href="inquire.jsp">查询</a></td>
<td><a href="add.jsp">添加</a></td>
<td><a href="amend.jsp">修改</a></td>
<td><a href="delete.jsp">删除</a></td>
</tr>
</table>
</body>
</html>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" contentType="text/html; charset=UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML>
<html>
<head>
<base href="<%=basePath%>">
<style type="text/css">
body
{
text-align: center;
}
</style>
<title>修改功能</title>
<meta charset="UTF-8">
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body align="center">
<h3>修改数据</h3>
<form action="display.do" method="post">
<label>学号:</label>
<input type="text" name="cg_sno"><br><br>
<label>姓名:</label>
<input type="text" name="cg_sname"><br><br>
<label>年龄:</label>
<input type="text" name="cg_sage">
<br><br>
<input type="submit" value="修改"><br><br>
<a href="display.do">返回首页</a><br>
</form>
</body>
</html>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" contentType="text/html; charset=UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML>
<html>
<head>
<base href="<%=basePath%>">
<style type="text/css">
body
{
text-align: center;
}
</style>
<title>查询功能</title>
<meta charset="UTF-8">
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body align="center">
<h3>删除数据</h3>
<form action="display.do" method="post">
<label>删除数据学生的学号:</label>
<input type="text" name="del_sno">
<br><br>
<input type="submit" value="删除"><br><br>
<a href="display.do">返回首页</a><br>
</form>
</body>
</html>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" contentType="text/html; charset=UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML>
<html>
<head>
<base href="<%=basePath%>">
<style type="text/css">
body
{
text-align: center;
}
</style>
<title>增加功能</title>
<meta charset="UTF-8">
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body align="center">
<h3>添加数据</h3>
<form action="display.do" method="post">
<label>学号:</label>
<input type="text" name="add_sno"> <br><br>
<label>姓名:</label>
<input type="text" name="add_sname"> <br><br>
<label>年龄:</label>
<input type="text" name="add_sage">
<br><br>
<input type="submit" value="添加"><br><br>
<a href="display.do">返回首页</a><br>
</form>
</body>
</html>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" contentType="text/html; charset=UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML>
<html>
<head>
<base href="<%=basePath%>">
<style type="text/css">
body
{
text-align: center;
}
</style>
<title>查询功能</title>
<meta charset="UTF-8">
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body align="center">
<h3>查询数据</h3>
<form action="display.do" method="post">
<label>查询的学号:</label>
<input type="text" name="search_no">
<br><br>
<input type="submit" value="查询">
<br><br>
<a href="display.do">返回首页</a><br>
</form>
</body>
</html>
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
<display-name></display-name>
<servlet>
<description>This is the description of my J2EE component</description>
<display-name>This is the display name of my J2EE component</display-name>
<servlet-name>Display</servlet-name>
<servlet-class>cn.jdbc.servlet.Display</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>Display</servlet-name>
<url-pattern>/display.do</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>
代码说明
由于该学习数据上传至CSDN时,数据库已经被我之前误删了,如果需要运行拷贝至具体项目,需自行组建数据库表,另外该项目依赖eclipse开发平台,需要读者自行添加驱动与各类标签库。
项目运行结果展示