- 用 类的封装 JDBC JSP MYSQL实现
2.方法:① 创建工具类DBUtil.java,内含实现四个操作的方法和数据库初始化等等
② 创建Student.java用来实现类的封装,创建StudentService.java向工具类传入sql 语句等
③创建home.jsp为主页,进入四个基本操作
④创建add.jsp输入添加的学生信息,创建doAdd.jsp接受传入的学生信息,并在内为 Student创建对象,把添加的信息传入对象内,为StudentService创建对象,把 studen对象放入进去,实现增添用户信息的操作
⑤创建Select .jsp,进行查看用户信息
⑥创建update.jsp,doUpdate.jsp,进行用户信息的修改
⑦创建delete.jsp,doDelete.jsp,进行用户信息的删除(原理和增添用户信息一样)
先放效果图
A.首页
B.点击添加新用户
C.点击添加后出现下面页面
D.点击返回主页面后,选择查看学生信息(用户添加成功)
E.也可以查看单个用户信息
E.在表格中就可点击删除和修改,和在主页时点击的操作一样,点击删除
F.点击删除后,出现提示,并返回主页面
E.再次点击查看用户信息(此时数据库信息已经更新)
F.点击修改用户信息
G.点击提交,出现提示
F.点击查看用户信息(数据已经更新)
下面放代码(不完整版)
1.Home.jsp页面代码
<body>
<div class="box">
<h1>实现学生信息表的增删改查操作</h1>
<a href="add.jsp" class="a1">添加</a><br>
<a href="Select.jsp" class="a2">查看</a><br>
<a href="Delete.jsp" class="a3">删除</a><br>
<a href="update.jsp" class="a4">修改</a><br>
</div>
</body>
2.Student.java页面(建一个类,用于对象的封装)
public class Student {
private String sno;
private String sname;
private String sex;
private String classes;
private String QQ;
private String pwd;
private String address;
public Student()
{}
public Student(String sno, String sname, String sex, String classes, String QQ, String pwd, String address) {
this.sno = sno;
this.sname = sname;
this.sex = sex;
this.classes = classes;
this.QQ = QQ;
this.pwd = pwd;
this.address = address;
}
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 String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getClasses() {
return classes;
}
public void setClasses(String classes) {
this.classes = classes;
}
public String getQQ() {
return QQ;
}
public void setQQ(String QQ) {
this.QQ = QQ;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "Student{" +
"sno='" + sno + '\'' +
", sname='" + sname + '\'' +
", sex='" + sex + '\'' +
", classes='" + classes + '\'' +
", QQ='" + QQ + '\'' +
", pwd='" + pwd + '\'' +
", address='" + address + '\'' +
'}';
}
}
3.StudentService.java页面
public class StudentService {
private DBUtil db;
public StudentService() {
db = new DBUtil();
}
//查看用户信息
public List<Map<String, String>> getUsers(String username) {
String sql = "select * from student";
String[] params = null;
if (username != null) {
sql = sql + " where sname like ?";
params = new String[] { "%" + username + "%" };
}
return db.getList(sql, params);
}
//删除用户
public int delUser(String id) {
String[] params = { id };
String sql = "delete from student where sno=?";
return db.update(sql, params);
}
//增加用户
public int addUser(Student user) {
String[] params = {user.getSno(),user.getSname(),user.getSex(),user.getClasses(), user.getQQ(),user.getPwd(), user.getAddress() };
String sql = "insert into student(sno,sname,sex,classes,QQ,pwd,address) values(?,?,?,?,?,?,?)";
return db.update(sql, params);
}
//修改用户
public int updateUser(Student user) {
String[] params = {user.getSname(),user.getSex(),user.getClasses(),user.getQQ(),user.getPwd(), user.getAddress(),user.getSno()};
String sql = "update student set sname=?,sex=?,classes=?,QQ=?,pwd=?,address=? where sno=?";
return db.update(sql, params);
}
}
4.DBUtil.java代码
public class DBUtil {
private String driver;
private String url;
private String username;
private String password;
private Connection con;
private PreparedStatement pstmt;
//public static final long PAGE_REC_NUM = 8;
public void setDriver(String driver) {
this.driver = driver;
}
public void setUrl(String url) {
this.url = url;
}
public void setUsername(String username) {
this.username = username;
}
public void setPassword(String password) {
this.password = password;
}
//创建一个构造方法
public DBUtil() {
driver = "com.mysql.cj.jdbc.Driver";
url = "jdbc:mysql://localhost:3306/mealnew?useSSL=false";
username = "root";
password = "root";
}
//init初始化(注册驱动和获取连接)
private void init() {
try {
Class.forName(driver);
con = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭
private void close() {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private void setParams(String[] params) {
if (params != null) {
for (int i = 0; i < params.length; i++) {
try {
pstmt.setString(i + 1, params[i]);//
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//修改,传入sql语句和修改的变量
public int update(String sql, String[] params) {
int result = 0;
init();//引用方法,初始化
try {
pstmt = con.prepareStatement(sql);//传入sql语句
setParams(params);//调用方法
result = pstmt.executeUpdate();//如果result>0则修改成功
} catch (SQLException e) {
e.printStackTrace();
} finally {
close();
}
return result;
}
public int update(String sql) {
return update(sql, null);
}
public List<Map<String, String>> getList(String sql, String[] params) {
List<Map<String, String>> list = null;
init();
try {
pstmt = con.prepareStatement(sql);
setParams(params);
ResultSet rs = pstmt.executeQuery();
list = getListFromRS(rs);
rs.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
close();
}
return list;
}
public List<Map<String, String>> getList(String sql) {
return getList(sql, null);
}
public Map<String, String> getMap(String sql, String[] params) {
Map<String, String> m = null;
List<Map<String, String>> l = getList(sql, params);
if (l != null && l.size() != 0) {
m = (Map<String, String>) (l.get(0));
}
return m;
}
public Map<String, String> getMap(String sql) {
return getMap(sql, null);
}
private List<Map<String, String>> getListFromRS(ResultSet rs) throws SQLException {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
Map<String, String> m = new HashMap<String, String>();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
String colName = rsmd.getColumnLabel(i);
String s = rs.getString(colName);
if (s != null) {
m.put(colName, s);
}
}
list.add(m);
}
return
5.add.jsp(输入增加的学生信息)
<body>
<div class="box">
<form action="http://localhost:8080/work/student/doAdd.jsp" method="post">
<h1>添加学生信息</h1>
<span>学号:<input type="text" name="sno" /><br /></span>
<span>姓名:<input type="text" name="sname" /><br /></span>
<span>性别:<input type="text" name="sex" /><br /></span>
<span>班级:<input type="text" name="classes" /><br /></span>
<span>QQ: <input type="text" name="QQ" /><br /></span>
<span>密码:<input type="password" name="pwd" /><br /></span>
<span>地址:<input type="text" name="address" /><br /></span>
<span><input type="submit" value="添加" /></span>
<span><input type="button" value="返回主页面" onclick='location.href=("home.jsp")'></span>
</form>
</div>
</body>
6.doAdd.jsp
<body>
<div class="box">
<h1>添加学生信息</h1>
<%
request.setCharacterEncoding("UTF-8");
//获取值
String sno=request.getParameter("sno");
String sname=request.getParameter("sname");
String sex=request.getParameter("sex");
String classes=request.getParameter("classes");
String QQ=request.getParameter("QQ");
String pwd=request.getParameter("pwd");
String address=request.getParameter("address");
//创建类的对象,放入参值
Student student=new Student(sno,sname,sex,classes,QQ,pwd,address);
student.setSname(sname);
StudentService studentService=new StudentService();
int i=studentService.addUser(student);
if(i>0)
{%>
<h3>添加成功</h3>
<%}else{%>
<h3>添加失败</h3>
<% }%>
<span ><input type="button" value="返回主页面" onclick='location.href=("home.jsp")' class="fh"></span>
</div>
</body>
7.Select.jsp页面(查看)
<body>
<div class="box">
<h1>查看学生信息</h1>
<form action="http://localhost:8080/work/student/Select.jsp" method="post">
请输入要查看的姓名<input type="text" name="sname">
<input type="submit" value="查看" />
</form>
<%
request.setCharacterEncoding("UTF-8");
//获取值
String sname=request.getParameter("sname");
StudentService studentService=new StudentService();
studentService.getUsers(sname);
Map<String,String> map = new TreeMap<>();
List<Map<String, String>> list=new ArrayList<>();
list=studentService.getUsers(sname);
int i=0;
for(Map<String, String> m : list)
{%>
<table border="1">
<%if(i==0)
{%>
<tr>
<td>sno</td>
<td>sname</td>
<td>sex</td>
<td>classes</td>
<td>QQ</td>
<td>pwd</td>
<td>address</td>
<td>操作</td>
<td>操作</td>
</tr>
<%}
i++;
%>
<td><%=m.get("sno")%></td>
<td><%=m.get("sname")%></td>
<td><%=m.get("sex")%></td>
<td><%=m.get("classes")%></td>
<td><%=m.get("QQ")%></td>
<td><%=m.get("pwd")%></td>
<td><%=m.get("address")%></td>
<td><a href="Delete.jsp">删除</a> </td>
<td><a href="update.jsp">修改</a> </td>
</table>
<%}%>
<span ><input type="button" value="返回主页面" onclick='location.href=("home.jsp")' class="fh"></span>
</div>
</body>
8.update.jsp(修改学生用户信息)
<body>
<div class="box">
<h1>修改学生信息</h1>
<form action="http://localhost:8080/work/student/doUpdate.jsp" method="post">
<span>学号:<input type="text" name="sno" /><br /></span>
<span>姓名:<input type="text" name="sname" /><br /></span>
<span>性别:<input type="text" name="sex" /><br /></span>
<span>班级:<input type="text" name="classes" /><br /></span>
<span>QQ: <input type="text" name="QQ" /><br /></span>
<span>密码:<input type="password" name="pwd" /><br /></span>
<span>地址:<input type="text" name="address" /><br /></span>
<span><input type="submit" value="修改" /></span>
<span><input type="button" value="返回主页面" onclick='location.href=("home.jsp")'></span>
</form>
</div>
</body>
9.doUpdate.jsp
<body>
<div class="box">
<%
request.setCharacterEncoding("UTF-8");
//获取值
String sno=request.getParameter("sno");
String sname=request.getParameter("sname");
String sex=request.getParameter("sex");
String classes=request.getParameter("classes");
String QQ=request.getParameter("QQ");
String pwd=request.getParameter("pwd");
String address=request.getParameter("address");
Student student=new Student(sno,sname,sex,classes,QQ,pwd,address);
StudentService studentService=new StudentService();
int W=studentService.updateUser(student);
if(W>0) {
out.println("<h1>修改成功</h1>");
}
else{
out.println("<h1>修改失败</h1>");
}
%>
<%
Map<String,String> map = new TreeMap<>();
List<Map<String, String>> list=new ArrayList<>();
list=studentService.getUsers(sname);
int i=0;
for(Map<String, String> m : list)
{%>
<table border="1">
<%if(i==0)
{%>
<tr>
<td>sno</td>
<td>sname</td>
<td>sex</td>
<td>classes</td>
<td>QQ</td>
<td>pwd</td>
<td>address</td>
<td>操作</td>
<td>操作</td>
</tr>
<%}
i++;
%>
<td><%=m.get("sno")%></td>
<td><%=m.get("sname")%></td>
<td><%=m.get("sex")%></td>
<td><%=m.get("classes")%></td>
<td><%=m.get("QQ")%></td>
<td><%=m.get("pwd")%></td>
<td><%=m.get("address")%></td>
<td><a href="doDelete.jsp">删除</a> </td>
<td><a href="update.jsp">修改</a> </td>
</table>
<%}
%>
<span ><input type="button" value="返回主页面" onclick='location.href=("home.jsp")' class="fh"></span>
</div>
</body>
10.delete.jsp(删除)
<body>
<div class="box">
<h1>删除学生信息</h1>
<form action="http://localhost:8080/work/student/doDelete.jsp" method="post">
请输入删除对象的学号<input type="text" name="sno">
<input type="submit" value="删除" /><br>
<span><input type="button" value="返回主页面" onclick='location.href=("home.jsp")' class="fh"></span>
</form>
</div>
</body>
11.doDelete.jsp
<body><div class="box">
<h1>删除学生信息</h1>
<%
request.setCharacterEncoding("UTF-8");
//获取值
String sno=request.getParameter("sno");
String sname=null;
StudentService studentService=new StudentService();
Map<String,String> map = new TreeMap<>();
List<Map<String, String>> list=new ArrayList<>();
list=studentService.getUsers(sname);
int i=studentService.delUser(sno);
if(i>0)
{
out.println("<h2>删除成功</h2>");
}
else
{
out.println("<h2>删除失败</h2>");
}
%>
<br><span><input type="button" value="查看学生信息" onclick='location.href=("Select.jsp")' class="fh"></span><br>
<span><input type="button" value="返回主页面" onclick='location.href=("home.jsp")' class="fh"></span>
</div>
</body>
这仅是基础,结束!