鸿 网 互 联 www.68idc.cn

当前位置 : 服务器租用 > 网站制作教程 > JSP > >

JSP实例一、购物车

来源:互联网 作者:佚名 时间:2015-08-19 07:35
知识准备: JDBC 访问数据库的基本步骤 ( 1 )、将数据库的 JDBC 驱动加载到工程类库中。 ( 2 )、加载 JDBC 驱动,并将其注册到 DriverManager 中。下面是一些主流数据库的 JDBC 驱动加载注册码: ①、 oracle 数据库 Class.forName(oracle.jdbc.driver.O

知识准备:

JDBC访问数据库的基本步骤

1)、将数据库的JDBC驱动加载到工程类库中。

2)、加载JDBC驱动,并将其注册到DriverManager中。下面是一些主流数据库的JDBC驱动加载注册码:

①、oracle数据库

Class.forName(“oracle.jdbc.driver.OracleDriver”).newInstance();

②、Sql Server 7.0/2000数据库

Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver”).newInstance();

③、MySQL数据库

Class.forName(“com.mysql.jdbc.Driver”).newInstance();

(3)、建立数据库连接,取得Connection对象。

①、oracle数据库

String url=”jdbc:oracle:thin:@localhost:1521:orcl”;

String user=”scott”:

String password=”tiger”;

Connetion conn=DriverManager.getConnetion(url,user,password);

②、Sql Server 7.0/2000数据库

String url=”jdbc:microsoft:sqlserver://localhost:1443;DatabaseName=pubs”;

String user=”sa”;

String password=””;

Connection conn=DriverManager.getConnection(url,user,password);

③、MySQL数据库

String url=”jdbc:mysql://localhost:3306/testDB?user=root&password=root&

useUnicode=true&charaterEncoding=gb2312”;

Connection conn=DriverManager.getConnection(url);

(4)、建立Statement对象或PreparedStatement对象。

//建立Statement对象

Statement stmt=conn.createStatement();

//建立PreparedStatement对象

String sql=”select *from users where userName=? And password=?”;

PreparedStatement pstmt=conn.prepareStatement(sql);

pstmt.setString(1,”admin”);

pstmt.setString(2,”liubin”);

(5)、执行SQL语句。

//执行静态SQL查询

String sql=”select * from users”;

ResultSet rs = stmt.executeQuery(sql);

//执行insertupdatedelete等语句,先定义sql

Stmt.executeUpdate(sql);

(6)、访问结果记录集ResultSet对象。

While(rs.next())

{

    out.println(“你的第一个字段内容为:“+rs.getString(1));

    out.println(“你的第二个字段内容为:“+rs.getString(2));

}

(7)、依次将ResultSetStatementPrepareStatementConnection对象关闭,释放所有占用的资源:

rs.close();

stmt.close();

Pstmt.close();

con.close();

 

index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>

<%@ page import="java.sql.*"%>

 

<html>

  <head>

    <title>在线购物</title>

  </head>

  <%

    //装载MySQL5.0的JDBC驱动

    Class.forName("com.mysql.jdbc.Driver").newInstance();

    //建立数据库连接

    String url ="jdbc:mysql://localhost:3306/eshop?user=root&password=zhz&useUnicode=true&characterEncoding=gb2312";

    Connection conn= DriverManager.getConnection(url);

    //建立一个Statement对象,用于执行SQL语句

    Statement stat = conn.createStatement();

    //执行查询并得到查询结果

    String sql = "select * from shop";

    ResultSet rs = stat.executeQuery(sql); 

  %>  

  <body>

    <h2>在线购物</h2>

    <hr>

    <table border="1" width="600">

        <tr bgcolor="#dddddd">

            <td align="center" width="80">商品缩图</td>

            <td align="center">商品摘要</td>

            <td align="center" width="100">在线购买</td>

        </tr>

        <%

            String bm,name,price,info,img;

            //将查询结果集中的记录输出到页面上

            while (rs.next()){

                //从当前记录中读取各字段的值

                img = rs.getString("SP_PIC").trim();

                bm = rs.getString("SP_NO").trim();

                name = rs.getString("SP_NAME").trim();

                price = rs.getString("SP_PRICE").trim();

                info = rs.getString("SP_INFO").trim();

               

                out.println("<tr>");

                out.println("<td><img src='"+ img +"' border=0 height=60 width=60></td>");

                out.println("<td>");

                out.println("商品编号:"+ bm +"<br>");

                out.println("商品名称:"+ name +"<br>");

                out.println("商品价格:"+ price +"元<br>");

                out.println("商品简介:"+ info +"<br>");

                out.println("</td>");

                out.println("<td><a href='buy.jsp?op=add&bm="+bm+"'>放入购物车</a></td>");

                out.println("</tr>");

            }

        %>     

    </table>

    <br>

    <a href="cart.jsp">查看购物车</a>&nbsp;&nbsp;

    <a href="buy.jsp?op=clear">清空购物车</a>          

  </body>

</html>

 

 

 

cart.jsp

<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>

<%@ page import="java.sql.*"%>

 

<html>

  <head>

    <title>我的购物车</title>

  </head>

  <%

    //装载MySQL5.0的JDBC驱动

    Class.forName("com.mysql.jdbc.Driver").newInstance();

    //建立数据库连接

    Stringurl="jdbc:mysql://localhost:3306/eshop?user=root&password=zhz&useUnicode=true&characterEncoding=gb2312";

    Connection conn= DriverManager.getConnection(url);

    //建立一个Statement对象,用于执行SQL语句

    Statement stat = conn.createStatement();

    //执行查询并得到查询结果

    String sql = "select * from cart";

    ResultSet rs = stat.executeQuery(sql); 

  %>  

  <body>

    <h2>我的购物车</h2>

    <hr>

    <table border="1" width="600">

        <tr bgcolor="#dddddd">

            <td align="center" width="80">商品名称</td>

            <td align="center">商品单价</td>

            <td align="center" width="100">购买数量</td>

            <td align="center" width="100">金额</td>

            <td align="center" width="100">编辑</td>

        </tr>

        <%

            String id,name,price,num,count;

            //将查询结果集中的记录输出到页面上

            while (rs.next()){

                //从当前记录中读取各字段的值

                id = rs.getString("ID").trim();

                name = rs.getString("SP_NAME").trim();

                price = rs.getString("SP_PRICE").trim();

                num = rs.getString("BUY_NUM").trim();

                count = rs.getString("COUNT").trim();

               

                out.println("<tr>");

                out.println("<td>"+ name +"</td>");

                out.println("<td>"+ price +"</td>");

                out.println("<td><input type=text value="+ num +" onChange=/"updateNum('"+id+"',this.value,'"+price+"')/"></td>");

                out.println("<td>"+ count +"</td>");

                out.println("<td><a href='buy.jsp?op=del&id="+id+"'>退回商品架</a></td>");

                out.println("</tr>");

            }

        %>     

    </table>

    <br>

    <a href="index.jsp">继续购物</a>&nbsp;&nbsp;

    <a href="buy.jsp?op=clear">清空购物车</a>           

  </body>

  <script language="javascript">

    function updateNum(id,num,price){

        var url = "buy.jsp?op=update&id="+id+"&num="+num+"&price="+price;

        window.location = url;

    }

  </script> 

</html>

 

 

buy.jsp

<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>

<%@ page import="java.sql.*"%>

<%

    //装载MySQL5.0的JDBC驱动

    Class.forName("com.mysql.jdbc.Driver").newInstance();

    //建立数据库连接

    String url ="jdbc:mysql://localhost:3306/eshop?user=root&password=zhz&useUnicode=true&characterEncoding=gb2312";

    Connection conn= DriverManager.getConnection(url);

    PreparedStatement pstat = null;

    ResultSet rs = null;

   

    String sql = null;

    String op = request.getParameter("op");

   

    //将商品放入购物

    if (op.equals("add")){

        //从商品库中取出所选购商品的数据

        String bm = request.getParameter("bm");

        String name=null,price=null;

        sql = "select * from shop where SP_NO=?";

        pstat = conn.prepareStatement(sql);

        pstat.setString(1,bm);

        rs = pstat.executeQuery();

        if (rs.next()){

            name = rs.getString("SP_NAME").trim();

            price = rs.getString("SP_PRICE").trim();

        }

        rs.close();

        pstat.close();

       

        //将所选购商品加入到购物车中

        sql = "insert into cart(SP_NAME,SP_PRICE,BUY_NUM,COUNT) values(?,?,?,?)";

        pstat = conn.prepareStatement(sql);

        pstat.setString(1,name);

        pstat.setString(2,price);

        pstat.setInt(3,1);

        pstat.setString(4,price);

        pstat.executeUpdate();

        pstat.close();

       

        //关闭数据库连接

        conn.close();

       

        //重定向到购物车页面

        response.sendRedirect("cart.jsp");

    }

   

    //更改商品的数量

    if (op.equals("update")){

        int id = Integer.parseInt(request.getParameter("id"));

        int num = Integer.parseInt(request.getParameter("num"));

        double price=Double.parseDouble(request.getParameter("price"));

        sql = "update cart set BUY_NUM = ?, COUNT = ? where ID=?";

        pstat = conn.prepareStatement(sql);

        pstat.setInt(1,num);

        pstat.setString(2,new Double(price*num).toString());

        pstat.setInt(3,id);

        pstat.executeUpdate();

        pstat.close();

       

        //关闭数据库连接

        conn.close();

       

        //重定向到购物车页面

        response.sendRedirect("cart.jsp");

    }  

   

    //将商品退回到商品架(将商品从购物车中删除)

    if (op.equals("del")){

        int id = Integer.parseInt(request.getParameter("id"));

        sql = "delete from cart where ID=?";

        pstat = conn.prepareStatement(sql);

        pstat.setInt(1,id);

        pstat.executeUpdate();

        pstat.close();

       

        //关闭数据库连接

        conn.close();

       

        //重定向到购物车页面

        response.sendRedirect("cart.jsp");

    }

   

    //清空购物

    if (op.equals("clear")){

        sql = "delete from cart";

        pstat = conn.prepareStatement(sql);

        pstat.executeUpdate();

        pstat.close();

       

        //关闭数据库连接

        conn.close();

       

        //重定向到购物车页面

        response.sendRedirect("cart.jsp");

    }

%>

 

 

 

 

数据库eshop

/*!40101 SET CHARACTER SET 'gb2312' */;

 

DROP DATABASE IF EXISTS `eshop`;

CREATE DATABASE `eshop` /*!40100 DEFAULT CHARACTER SET gb2312 */;

USE `eshop`;

 

/*!40101 SET CHARACTER SET 'gb2312' */;

 

CREATE TABLE `cart` (

  `ID` int(11) NOT NULL auto_increment,

  `SP_NAME` varchar(40) default NULL,

  `SP_PRICE` decimal(8,2) default NULL,

  `BUY_NUM` int(11) default NULL,

  `COUNT` decimal(9,2) default NULL,

  PRIMARY KEY  (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

 

CREATE TABLE `shop` (

  `SP_NO` char(10) NOT NULL,

  `SP_NAME` varchar(40) default NULL,

  `SP_PRICE` decimal(8,2) default NULL,

  `SP_INFO` varchar(200) default NULL,

  `SP_PIC` varchar(50) default NULL,

  PRIMARY KEY  (`SP_NO`)

) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

 

INSERT INTO `shop` (`SP_NO`,`SP_NAME`,`SP_PRICE`,`SP_INFO`,`SP_PIC`) VALUES ('SP0001','音频卡',950,'MAYA44 V3是具有24bit/96KHz专业指标的44出专业数字音频卡','pic/pic01.jpg');

INSERT INTO `shop` (`SP_NO`,`SP_NAME`,`SP_PRICE`,`SP_INFO`,`SP_PIC`) VALUES ('SP0002','合成器',4980,'PK-6以较低的价格为你提供E-MU最新的Proteus声音和高质量的键盘','pic/pic02.jpg');

INSERT INTO `shop` (`SP_NO`,`SP_NAME`,`SP_PRICE`,`SP_INFO`,`SP_PIC`) VALUES ('SP0003','MP3',450,'功能提示支持USB2.0高速传输','pic/pic03.jpg');

INSERT INTO `shop` (`SP_NO`,`SP_NAME`,`SP_PRICE`,`SP_INFO`,`SP_PIC`) VALUES ('SP0004','监听音箱',3800,'PX-T6A是强大的独立双功放、近场监听系统的理想选择','pic/pic04.jpg');

INSERT INTO `shop` (`SP_NO`,`SP_NAME`,`SP_PRICE`,`SP_INFO`,`SP_PIC`) VALUES ('SP0005','摄像机',29800,'3CCD/传感器总象素:112万像素','pic/pic05.jpg');

 

网友评论
<