只是基于MVC模式通过图书管理系统实现对数据库的增删改差操作,以便之后的改进
前提回顾：
增删改查如何实现？
1、当点击提交按钮后，信息将通过表单的action操作提交给web.xml。
2、web.xml根据相应的找到对应的servlet进行操作。
3、执行servlet的时候也在对bean进行模型中值的改变
4、通过bean对象将模型值输出
操作实现：
1、首先通过jsp写出表单并添加“增”提交方式（部分代码，最后有完整代码）
<!--将表单信息发到ShowMenu3Servlet.do对应的servlet中进行处理-->
<form action="ShowMenu3Servlet.do?dataBase=library&tableName=re_book" method="post">
<span>借书人：</span><input type=text name="user_name" size=10 />
<span>所借图书：</span><input type=text name="book_name" size=10 />
<span>借书日期：</span><input type=text name="jieshu_date" size=10 />
<span>归还日期：</span><input type=text name="deadline" size=10 />
<input type=submit name="caozuo" value="tianjia" />
<!-- 增操作 -->
<input type=submit name="caozuo" value="xiugai" />
<!-- 改操作 -->
<input type=submit name="caozuo" value="shanchu" />
<!-- 删操作 -->
<input type=submit name="caozuo" value="chaxun" />
<!-- 查操作 -->
</form>
2、在servlet中对提交过来的数据进行处理
package com.demo.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import com.demo.javabean.Menu3Bean;
import com.demo.javabean.ShowMenu3Bean;
public class ShowMenu3Servlet extends HttpServlet{
private static final long serialVersionUID = 1L;
public void doPost(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
String caozuo=(String)request.getParameter("caozuo");
//读取表单中的名为caozuo属性的值，赋给字符串变量caozuo
request.setCharacterEncoding("utf-8");
//将获得的所有信息转换成utf-8格式
if(caozuo.equals("xiugai")) {//进行改操作
Xiugai(request,response);//执行修改方法
System.out.print("xiugai");
}
if(caozuo.equals("shanchu")) {//进行删操作
Shanchu(request,response);//执行删除方法
System.out.print("shanchu");
}
if(caozuo.equals("tianjia")){//进行增操作
Tianjia(request,response);//执行添加方法
System.out.print("tianjia");
}
if(caozuo.equals("chaxun")) {//进行查操作
Chaxun(request,response);//执行查询方法
System.out.print("chaxun");
}
Show(request,response);//显示数据库的内容
}
public void doGet(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
doPost(request,response);//若method方式为get，调用doPost方法（省去判断提交的是什么方法）
}
public void fail(HttpServletRequest request,HttpServletResponse response,
String backNews) {
response.setContentType("text/html;charset=GB2312");
try {
PrintWriter out=response.getWriter();
out.println("<html><body>");
out.println("<h2>"+backNews+"</h2>") ;
out.println("出错了");
out.println("<a href =welcome.jsp>返回</a>");
out.println("</body></html>");
}
catch(IOException exp){
}
}
protected void Xiugai(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{
String un = request.getParameter("user_name");
String bn = request.getParameter("book_name");
String jsdate = request.getParameter("jieshu_date");
String dd = request.getParameter("deadline");
if(un == null || bn ==null || jsdate==null || dd==null) {
System.out.print("请把借书信息填写完整");
return;
}
Menu3Bean menu3Bean = new Menu3Bean();
boolean isExist = menu3Bean.isExist(un);
if(!isExist) {
System.out.print("修改用户不存在");
return;
}else {
menu3Bean.modify(un,bn,jsdate,dd);
return;
}
}
protected void Tianjia(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
String un = request.getParameter("user_name");//获取menu3.jsp中名为user_name属性的值
String bn =request.getParameter("book_name");//获取menu3.jsp中名为book_name属性的值
String jsdate = request.getParameter("jieshu_date");//获取menu3.jsp中名为jieshu_date属性的值
String dd = request.getParameter("deadline");//获取menu3.jsp中名为deadline属性的值
if(un == null || bn == null || jsdate== null || dd==null) {
System.out.print("请把信息填写完整");
response.sendRedirect("menu3.jsp");//重定向回menu3.jsp（不共享request）
return;
}
Menu3Bean menu3Bean = new Menu3Bean();//创建一个bean对象，该对象中进行数据库具体操作
boolean isExist = menu3Bean.isExist(un);//判断是否存在用户
if(isExist) {
System.out.print("该用户已添加");
return;
}else {
menu3Bean.add(un,bn,jsdate,dd);//添加用户
return;
}
}
protected void Shanchu(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{
String user_name = request.getParameter("user_name");
Menu3Bean menu3Bean = new Menu3Bean();
boolean isExist = menu3Bean.isExist(user_name);
if(isExist) {
menu3Bean.del(user_name);
return;
}else {
System.out.print("无法删除，用户不存在");
return;
}
}
protected void Chaxun(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{
String un = request.getParameter("user_name");
String bn = request.getParameter("book_name");
String jsdate = request.getParameter("jieshu_date");
String dd = request.getParameter("deadline");
Menu3Bean menu3Bean = new Menu3Bean();
boolean isExist = menu3Bean.isExist(un);
if(isExist) {
menu3Bean.Search(un,bn,jsdate,dd);
return;
}else {
System.out.println("没有查询到该用户的借书信息");
return;
}
}
protected void Show(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{
Connection con=null;
String dataBase = request.getParameter("dataBase");
String tableName = request.getParameter("tableName");
HttpSession session=request.getSession(true);
ShowMenu3Bean pageBean=null;
try{
pageBean=(ShowMenu3Bean)session.getAttribute("pageBean");
if(pageBean==null){
pageBean=new ShowMenu3Bean();
session.setAttribute("pageBean",pageBean);
}
}
catch(Exception exp){
pageBean=new ShowMenu3Bean();
session.setAttribute("pageBean",pageBean);
}
String uri="jdbc:mysql://127.0.0.1/"+dataBase+"?useSSL=false";
try {
con=DriverManager.getConnection(uri,"root","123");//优化：使用一个数据库链接方法
Statement sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs=sql.executeQuery("SELECT * FROM "+tableName);
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
String []columnName = new String[columnCount];
for(int i=0;i<columnName.length;i++) {
columnName[i] = metaData.getColumnName(i+1);
}
pageBean.setColumnName(columnName);
rs.last();
int rowNumber=rs.getRow();
String [][] tableRecord=pageBean.getTableRecord();
tableRecord = new String[rowNumber][columnCount];
rs.beforeFirst();
int i=0;
while(rs.next()){
for(int k=0;k<columnCount;k++)
tableRecord[i][k] = rs.getString(k+1);
i++;
}
pageBean.setTableRecord(tableRecord);
con.close();
response.sendRedirect("menu3.jsp");
}
catch(SQLException e){
System.out.println(e);
}
return;
}
}
3、创建bean对象进行数据库的操作
Menu3Bean
package com.demo.javabean;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class Menu3Bean{
public boolean isExist(String username) {
boolean isExist = false;
DBAccess db = new DBAccess();//创建数据库的bean对象
if(db.createConn()) {//数据库是否链接成功
String sql = "select * from re_book where user_name='"+username+"';";
db.query(sql);//执行sql语句
if(db.next()) {
isExist = true;
}
db.closeRs();
db.closeStm();
db.closeConn();
}
return isExist;
}
//修改操作
public void modify(String username, String bookname, String jieshudate, String deadline) {
DBAccess db = new DBAccess();
if(db.createConn()) {
String sql = "update re_book set book_name='"+bookname+"',jieshu_date='"+jieshudate+"',deadline='"+deadline+"' where user_name='"+username+"';";
db.update(sql);
db.closeStm();
db.closeConn();
}
}
//查询操作
public void Search(String username, String bookname, String jieshudate, String deadline) {
DBAccess db = new DBAccess();
if(db.createConn()) {
String sql = "select * from re_book where user_name='"+username+"'";
db.query(sql);
ResultSet rs = db.getRs();
try {
while(db.next()) {
String un = rs.getString("user_name");
String bn = rs.getString("book_name");
String jsdate = rs.getString("jieshu_date");
String dd= rs.getString("deadline");
System.out.println("借书人："+un+" 书名："+bn+" 结束日期："+jsdate+" 归还日期："+dd);
}
db.closeStm();
db.closeConn();
}
catch(Exception e) {
e.printStackTrace();
}
}
}
//增操作
public void add(String username, String bookname, String jieshudate, String deadline) {
DBAccess db = new DBAccess();
if(db.createConn()) {
String sql ="INSERT INTO re_book VALUES"+"("+"'"+username+"','"+bookname+"','"+jieshudate+"','"+deadline+"')";
db.update(sql);
db.closeStm();
db.closeConn();
}
}
//删除操作
public void del(String username) {
DBAccess db = new DBAccess();
if(db.createConn()) {
String sql = "delete from re_book where user_name= '"+username+"'; ";
db.update(sql);
db.closeStm();
db.closeConn();
}
}
}
DBAccess
package com.demo.javabean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBAccess {
private String drv = "com.mysql.jdbc.Driver";
private String url = "jdbc:mysql://localhost:3306/library";
private String usr = "root";
private String pwd = "123";
private Connection conn = null;
private Statement stm = null;
private ResultSet rs = null;
private ResultSetMetaData metaData = null;
public boolean createConn() {
boolean b = false;
try {
Class.forName(drv).newInstance();
conn = DriverManager.getConnection(url, usr, pwd);
b = true;
} catch (SQLException e) {
} catch (ClassNotFoundException e) {
} catch (InstantiationException e) {
} catch (IllegalAccessException e) {
}
return b;
}
public boolean update(String sql) {
boolean b = false;
try {
stm = conn.createStatement();
stm.execute(sql);
b = true;
} catch (Exception e) {
System.out.println(e.toString());
}
return b;
}
public void query(String sql) {
try {
stm = conn.createStatement();
rs = stm.executeQuery(sql);
} catch (Exception e) {
}
}
public boolean next() {
boolean b = false;
try {
if(rs.next())b = true;
} catch (Exception e) {
}
return b;
}
public String getValue(String field) {
String value = null;
try {
if(rs!=null)value = rs.getString(field);
} catch (Exception e) {
}
return value;
}
public void closeConn() {
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
}
}
public void closeStm() {
try {
if (stm != null)
stm.close();
} catch (SQLException e) {
}
}
public void closeRs() {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
}
}
public Connection getConn() {
return conn;
}
public void setConn(Connection conn) {
this.conn = conn;
}
public String getDrv() {
return drv;
}
public void setDrv(String drv) {
this.drv = drv;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public ResultSet getRs() {
return rs;
}
public void setRs(ResultSet rs) {
this.rs = rs;
}
public Statement getStm() {
return stm;
}
public void setStm(Statement stm) {
this.stm = stm;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsr() {
return usr;
}
public void setUsr(String usr) {
this.usr = usr;
}
public void setMetaData(ResultSetMetaData metadata) {
this.metaData=metadata;
}
public ResultSetMetaData getMetaData() {
return metaData;
}
}
小结
1.sql语句增删改查：
增：String sql =“INSERT INTO re_book VALUES”+"("+"’"+username+"’,’"+bookname+"’,’"+jieshudate+"’,’"+deadline+"’)";
删：String sql = “delete from re_book where user_name= '”+username+"’; “;
改：String sql = “update re_book set book_name=’”+bookname+”’,jieshu_date=’"+jieshudate+"’,deadline=’"+deadline+"’ where user_name=’"+username+"’;";
查：String sql = “select * from re_book where user_name=’”+username+"’";
2.MVC的含义：
jsp是view，负责直接与用户进行交互。
servlet是control，负责对提交的内容进行流程的管理。
bean是model，负责对数据进行更新与储存。
946
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
