数据库web项目对数据库的操作
1.0、JSTL与jsp实现对数据库的操作
MySql 数据库：
create database if not exists CommodityDB; use CommodityDB; drop table if exists Commod; create table Commod( cid int primary key auto_increment comment '编号', cname varchar(64) not null comment '商品名称', cprice datetime not null comment '商品价格', cdetail varchar(225) comment '商品描述' ); insert into Commod(cname,cprice,cdetail) values ('小米6',2499,'很不错的手机'), ('iPhoneX',5800,'不错，很贵'), ('锤子T3',2499,'不是很有名'), ('魅族手机',3699,'手机很好看'), ('OPPO R9',2599,'可以当炸弹用');
util工具类：
package com.hexianwei.util; import java.sql.*; import java.util.ArrayList; import java.util.List; /** * Sql的相关操作，增、删、改、查！ * @author 何仙伟 * */ public class DBUtil2 { //连接对象 //Statement 命令对象 //打开连接 //关闭连接 //得到一个连接对象 //查询（有参，无参） //修改（有参，无参） static Connection conn = null; static Statement stmt = null; //驱动，服务器地址，登录用户名，密码 static String DBDRIVER="com.mysql.jdbc.Driver"; static String DBURL="jdbc:mysql://localhost:3306/CommodityDB?serverTimezone=GMT%2B8"; static String DBUSER="root"; static String DBPWD="10086"; /** * 打开连接 */ public static void open() { //加载驱动 try { Class.forName(DBDRIVER); conn=DriverManager.getConnection(DBURL,DBUSER,DBPWD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } /** * 关闭连接 */ public static void close() { try { if(stmt!=null) stmt.close(); if(conn!=null && !conn.isClosed()) conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * 得到一个连接对象，当用户使用DBUtil无法解决个性问题时 * 可以通过本方法获得连接对象 * @return */ public static Connection getConnection() { try { if(conn==null ||conn.isClosed()) open(); } catch (SQLException e) { e.printStackTrace(); } return conn; } /** * executeQuery * executeUpdate * 获得查询的数据集 * select * from student where name='' and sex='' * @param sql * @return */ public static ResultSet executeQuery(String sql) { try { open();//保证连接是成功的 stmt = conn.createStatement(); return stmt.executeQuery(sql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } //修改表格内容 public static int executeUpdate(String sql) { int result = 0; try { open();//保证连接是成功的 stmt = conn.createStatement(); result = stmt.executeUpdate(sql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { close(); } return result; } /** * 如果执行的查询或存储过程，会返回多个数据集，或多个执行成功记录数 * 可以调用本方法，返回的结果， * 是一个List<ResultSet>或List<Integer>集合 * @param sql * @return */ public static Object execute(String sql) { boolean b=false; try { open();//保证连接是成功的 stmt = conn.createStatement(); b = stmt.execute(sql); //true,执行的是一个查询语句，我们可以得到一个数据集 //false,执行的是一个修改语句，我们可以得到一个执行成功的记录数 if(b){ return stmt.getResultSet(); } else { return stmt.getUpdateCount(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if(!b) { close(); } } return null; } public static Connection getConn() { try { Class.forName(DBDRIVER).newInstance(); conn=DriverManager.getConnection(DBURL, DBUSER, DBPWD); } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; } /** * 查询数据，参数形式 * select * from student where name=? and sex=? */ public static ResultSet executeQuery(String sql,Object[] in) { try { open();//保证连接是成功的 PreparedStatement pst = conn.prepareStatement(sql); for(int i=0;i<in.length;i++) pst.setObject(i+1, in[i]); stmt = pst;//只是为了关闭命令对象pst return pst.executeQuery(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } /** * 修改 */ public static int executeUpdate(String sql,Object[] in) { try { open();//保证连接是成功的 PreparedStatement pst = conn.prepareStatement(sql); for(int i=0;i<in.length;i++) pst.setObject(i+1, in[i]); stmt = pst;//只是为了关闭命令对象pst return pst.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { close(); } return 0; } public static Object execute(String sql,Object[] in) { boolean b=false; try { open();//保证连接是成功的 PreparedStatement pst = conn.prepareStatement(sql); for(int i=0;i<in.length;i++) pst.setObject(i+1, in[i]); b = pst.execute(); //true,执行的是一个查询语句，我们可以得到一个数据集 //false,执行的是一个修改语句，我们可以得到一个执行成功的记录数 if(b){ System.out.println("----"); /*List<ResultSet> list = new ArrayList<ResultSet>(); list.add(pst.getResultSet()); while(pst.getMoreResults()) { list.add(pst.getResultSet()); }*/ return pst.getResultSet(); } else { System.out.println("****"); List<Integer> list = new ArrayList<Integer>(); list.add(pst.getUpdateCount()); while(pst.getMoreResults()) { list.add(pst.getUpdateCount()); } return list; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if(!b) { System.out.println("===="); close(); } } return null; } /** * 调用存储过程 proc_Insert(?,?,?) * @param procName * @param in * @return */ public static Object executeProcedure(String procName,Object[] in) { open(); try { procName = "{call "+procName+"("; String link=""; for(int i=0;i<in.length;i++) { procName+=link+"?"; link=","; } procName+=")}"; CallableStatement cstmt = conn.prepareCall(procName); for(int i=0;i<in.length;i++) { cstmt.setObject(i+1, in[i]); } if(cstmt.execute()) { return cstmt.getResultSet(); } else { return cstmt.getUpdateCount(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } /** * 调用存储过程，并有输出参数 * @procName ，存储过程名称：proc_Insert(?,?) * @in ,输入参数集合 * @output,输出参数集合 * @type,输出参数类型集合 */ public static Object executeOutputProcedure(String procName, Object[] in,Object[] output,int[] type){ Object result = null; try { CallableStatement cstmt = conn.prepareCall("{call "+procName+"}"); //设置存储过程的参数值 int i=0; for(;i<in.length;i++){//设置输入参数 cstmt.setObject(i+1, in[i]); //print(i+1); } int len = output.length+i; for(;i<len;i++){//设置输出参数 cstmt.registerOutParameter(i+1,type[i-in.length]); //print(i+1); } boolean b = cstmt.execute(); //获取输出参数的值 for(i=in.length;i<output.length+in.length;i++) output[i-in.length] = cstmt.getObject(i+1); if(b) { result = cstmt.getResultSet(); } else { result = cstmt.getUpdateCount(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return result; } }
Javabean：
package com.hexianwei.vo; import java.util.*; public class Commod{ private int cid; private String cname; private double cprice; private String cdetail; public int getcid(){ return cid; } public void setcid(int cid){ this.cid = cid; } public String getcname(){ return cname; } public void setcname(String cname){ this.cname = cname; } public double getcprice(){ return cprice; } public void setcprice(double cprice){ this.cprice = cprice; } public String getcdetail(){ return cdetail; } public void setcdetail(String cdetail){ this.cdetail = cdetail; } public Commod () {} public Commod (int cid,String cname,double cprice,String cdetail) { this.cid = cid; this.cname = cname; this.cprice = cprice; this.cdetail = cdetail; } }
dao层：
package com.hexianwei.dao; import com.hexianwei.util.DBUtil2; import com.hexianwei.vo.Commod; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; public class CommodDao { static List<Commod> list = new ArrayList<>(); /** * 查询所有数据 * * @return */ public List<Commod> getAll() { list.clear();//调用方法之前把集合里的数据清空 Commod commod = null; String sql = "select * from Commod"; ResultSet rs = DBUtil2.executeQuery(sql); if (rs != null) { try { while (rs.next()) { commod = new Commod( rs.getInt(1), rs.getString(2), rs.getDouble(3), rs.getString(4) ); list.add(commod); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil2.close(); } } return list; } /** * 添加数据 * * @param cname * @param cprice * @param cdetail * @return */ public int insert(String cname, double cprice, String cdetail) { String sql = "insert into Commod(cname,cprice,cdetail) values(?,?,?)"; Object[] in = {cname, cprice, cdetail}; return DBUtil2.executeUpdate(sql, in); } /** * 根据id删除数据 * * @param cid * @return */ public int delete(int cid) { String sql = "delete from Commod where cid=?"; Object[] in = {cid}; return DBUtil2.executeUpdate(sql, in); } /** * 根据cid修改数据 * @param cname * @param cprice * @param cdetail * @param cid * @return */ public int update(String cname, double cprice, String cdetail,int cid){ String sql = "update Commod set cname=?, cprice=?,cdetail=? where cid=?"; Object[] in={cname,cprice,cdetail,cid}; return DBUtil2.executeUpdate(sql,in); } }
bo层：
package com.hexianwei.bo; import com.hexianwei.dao.CommodDao; import com.hexianwei.vo.Commod; import java.util.List; public class CommodBo { CommodDao cdao = new CommodDao(); /** * 查询 * @return */ public List<Commod> getAll(){ return cdao.getAll(); } /** * 添加 * @param cname * @param cprice * @param cdetail * @return */ public int insert(String cname, double cprice, String cdetail){ return cdao.insert(cname,cprice,cdetail); } /** * 删除 * @param cid * @return */ public int delete(int cid){ return cdao.delete(cid); } /** * 修改 * @param cname * @param cprice * @param cdetail * @param cid * @return */ public int update(String cname, double cprice, String cdetail,int cid){ return cdao.update(cname,cprice,cdetail,cid); } }
控制层：
package com.hexianwei.control; import com.hexianwei.bo.CommodBo; 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 java.io.IOException; @WebServlet(name = "Commod") public class Commod extends HttpServlet { CommodBo cbo = new CommodBo(); protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); String action = request.getParameter("action"); if (action.equals("getAll")) { getAll(request, response); }else if(action.equals("insert")){ insert(request,response); }else if(action.equals("delete")){ delete(request,response); }else if(action.equals("update")){ update(request,response); } } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } /** * 查询 * * @param response */ public void getAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setAttribute("list",cbo.getAll()); request.getRequestDispatcher("index.jsp").forward(request,response); } /** * 添加 * @param request * @param response */ public void insert(HttpServletRequest request, HttpServletResponse response) throws IOException { String cname = request.getParameter("cname"); double cprice = Double.parseDouble(request.getParameter("cprice")); String cdetail = request.getParameter("cdetail"); int i = cbo.insert(cname,cprice,cdetail); if (i>0){ response.sendRedirect("Commod?action=getAll"); } } /** * 删除 * @param request * @param response */ public void delete(HttpServletRequest request, HttpServletResponse response) throws IOException { int cid = Integer.parseInt(request.getParameter("cid")); int i = cbo.delete(cid); if (i>0){ response.sendRedirect("Commod?action=getAll"); } } /** * 修改 * @param request * @param response */ public void update(HttpServletRequest request, HttpServletResponse response) throws IOException { String cname = request.getParameter("cname"); double cprice = Double.parseDouble(request.getParameter("cprice")); String cdetail = request.getParameter("cdetail"); int cid = Integer.parseInt(request.getParameter("cid")); int i = cbo.update(cname,cprice,cdetail,cid); if (i>0){ response.sendRedirect("Commod?action=getAll"); } } }
jsp界面：
<%-- Created by IntelliJ IDEA. User: 猴赛雷 Date: 2018/9/15 Time: 7:22 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <html> <head> <title>$Title$</title> <style> a{ text-decoration: none; } #div1{ width: 80%; margin: 0 auto; } #table1{ width: 100%; border-collapse: collapse; text-align: center; } </style> </head> <body> <div id="div1"> <table id="table1" border="1"> <tr> <th>CID</th> <th>商品名称</th> <th>商品价格</th> <th>商品详情</th> <th>操作</th> </tr> <c:forEach var="getAll" items="${list}"> <tr> <td>${getAll.cid}</td> <td>${getAll.cname}</td> <td>${getAll.cprice}</td> <td>${getAll.cdetail}</td> <td> <a href="/Commod?action=delete&cid=${getAll.cid}"> <button class="del">删除</button> </a> <button class="etid">编辑</button> </td> </tr> </c:forEach> </table> <fieldset id="f"> <legend>添加/修改</legend> <form id="form1" action="" method="post"> <input id="cid" type="hidden" name="cid"> <label for="cname">商品名称</label> <input id="cname" type="text" name="cname" required><br/> <label for="cprice">商品价格</label> <input id="cprice" type="text" name="cprice" required><br/> <label for="cdetail">商品详情</label> <input id="cdetail" type="text" name="cdetail" required><br/> <button id="butAdd">添加</button> <button id="butUp">修改</button> </form> </fieldset> </div> <script src="js/jquery-1.11.3.min.js"></script> <script> $("#table1").on("click",".del",function () { if (confirm("您确定要删除吗？")) { return true; }else { return false; } }); //添加按钮 $("#butAdd").click(function () { document.getElementById("form1").action="Commod?action=insert"; $(this).submit(); }); //修改按钮 $("#butUp").click(function () { if ($("#cid")!="") { document.getElementById("form1").action="Commod?action=update"; $(this).submit(); }else{ alert("请选择您要修改的商品"); } }); $("#table1").on("click",".etid",function () { var td = this.parentNode.parentNode.childNodes; $("#cid").val(td[1].innerText); $("#cname").val(td[3].innerText); $("#cprice").val(td[5].innerText); $("#cdetail").val(td[7].innerText); }); </script> </body> </html>
效果如下：
