数据库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>

效果如下：

下载源码：https://pan.baidu.com/s/1NJpbStu36KkT0Urj_LFLzQ

巴特西

数据库web项目对数据库的操作

1.0、JSTL与jsp实现对数据库的操作

最新文章

热门文章