《转》jsp+mysql实例简单的购物车_mysql+iquery购物车-CSDN博客

jsp+mysql实例简单的购物车

该项目数据库用MySQL对数据的管理，有两个表，一个表是cart另一个是shop。小练习拿出来分享希望对大家有用

1 数据库表

cart表

shop表

2 页面源码展示

index.jsp

1. <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>

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

4. <html>

5. <head>

6. <title>在线购物</title>

7. </head>

8. <%

9. //装载MySQL5.5的JDBC驱动

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

11. //建立数据库连接

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

13. Connection conn= DriverManager.getConnection(url);

14. //建立一个Statement对象，用于执行SQL语句

15. Statement stat = conn.createStatement();

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

17. String sql = "select * from shop";

18. ResultSet rs = stat.executeQuery(sql);

19. %>

20. <body>

21. <h2>在线购物</h2>

22. <hr>

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

24. <tr bgcolor="#dddddd">

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

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

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

28. </tr>

29. <%

30. String bm,name,price,info,img;

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

32. while (rs.next()){

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

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

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

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

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

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

39.

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

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

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

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

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

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

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

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

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

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

50. }

51. %>

52. </table>

53. <br>

54. <a href="cart.jsp">查看购物车</a>

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

56. </body>

57. </html>

buy.jsp

1. <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>

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

3. <%

4. //装载MySQL5.5的JDBC驱动

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

6. //建立数据库连接

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

8. Connection conn= DriverManager.getConnection(url);

9. PreparedStatement pstat = null;

10. ResultSet rs = null;

11.

12. String sql = null;

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

14.

15. //将商品放入购物车

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

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

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

19. String name=null,price=null;

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

21. pstat = conn.prepareStatement(sql);

22. pstat.setString(1,bm);

23. rs = pstat.executeQuery();

24. if (rs.next()){

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

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

27. }

28. rs.close();

29. pstat.close();

30.

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

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

33. pstat = conn.prepareStatement(sql);

34. pstat.setString(1,name);

35. pstat.setString(2,price);

36. pstat.setInt(3,1);

37. pstat.setString(4,price);

38. pstat.executeUpdate();

39. pstat.close();

40.

41. //关闭数据库连接

42. conn.close();

43.

44. //重定向到购物车页面

45. response.sendRedirect("cart.jsp");

46. }

47.

48. //更改商品的数量

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

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

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

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

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

54. pstat = conn.prepareStatement(sql);

55. pstat.setInt(1,num);

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

57. pstat.setInt(3,id);

58. pstat.executeUpdate();

59. pstat.close();

60.

61. //关闭数据库连接

62. conn.close();

63.

64. //重定向到购物车页面

65. response.sendRedirect("cart.jsp");

66. }

67.

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

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

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

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

72. pstat = conn.prepareStatement(sql);

73. pstat.setInt(1,id);

74. pstat.executeUpdate();

75. pstat.close();

76.

77. //关闭数据库连接

78. conn.close();

79.

80. //重定向到购物车页面

81. response.sendRedirect("cart.jsp");

82. }

83.

84. //清空购物车

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

86. sql = "delete from cart";

87. pstat = conn.prepareStatement(sql);

88. pstat.executeUpdate();

89. pstat.close();

90.

91. //关闭数据库连接

92. conn.close();

93.

94. //重定向到购物车页面

95. response.sendRedirect("cart.jsp");

96. }

97. %>

cart.jsp

1. <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>

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

4. <html>

5. <head>

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

7. </head>

8. <%

9. //装载MySQL5.5的JDBC驱动

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

11. //建立数据库连接

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

13. Connection conn= DriverManager.getConnection(url);

14. //建立一个Statement对象，用于执行SQL语句

15. Statement stat = conn.createStatement();

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

17. String sql = "select * from cart";

18. ResultSet rs = stat.executeQuery(sql);

19. %>

20. <body>

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

22. <hr>

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

24. <tr bgcolor="#dddddd">

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

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

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

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

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

30. </tr>

31. <%

32. String id,name,price,num,count;

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

34. while (rs.next()){

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

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

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

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

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

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

41.

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

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

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

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

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

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

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

49. }

50. %>

51. </table>

52. <br>

53. <a href="index.jsp">继续购物</a>

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

55. </body>

56. <script language="javascript">

57. function updateNum(id,num,price){

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

59. window.location = url;

60. }

61. </script>

62. </html>

3 结果:

数据库中内容随之更新：

遇到问题：

问题1：

Stacktrace:] with root cause

java.sql.SQLException:Access denied for user 'root'@'localhost' (using password: YES)

原因：

代码中输入密码与MySQL中的真正密码不一致，导致登陆失败

解决：

把代码中“password=root”的root改为实际数据库的密码。

问题2：

Stacktrace:] with root cause

com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '1-MP3-450-450-1' for key 'PRIMARY'

解决方法：

把主键设为自动增长

参考程序原作《http://blog.csdn.net/myserverthepeople/article/details/7981300》