题目:
1、有一个存放商品的仓库,每天都有商品出库和入库。
2、每种商品都有名称、生产厂家、型号、规格等。
3、出入库时必须填写出入库单据,单据包括商品名称、生产厂家、型号、 规格、数量、日期、时间、入库单位(或出库单位)名称、送货(或提货)人 姓名。
首先建立数据库goodsmanager
table:goods记录商品信息
table:list记录出入库信息
Java部分:
先把页面写好
首页面用<iframe>分为左侧导航栏,和右侧页面
点击左侧超链接即可实现右侧页面的跳转,在left.jsp中使用<a>的target属性来控制
1 <div>
2 <div><a style="color:blue" target="right" href="select.jsp">查询</a></div><br/>
3 <div><a style="color:blue" target="right" href="out.jsp">出库</a></div><br/>
4 <div><a style="color:blue" target="right" href="in.jsp">入库</a></div><br/>
5 <div><a style="color:blue" target="right" href="1.jsp">增加商品</a></div><br/>
6 <div><a style="color:blue" target="right" href="2.jsp">删除商品</a></div><br/>
7 <div><a style="color:blue" target="right" href="3.jsp">修改商品</a></div><br/>
8 <div><a style="color:blue" target="right" href="4.jsp">查询商品</a></div><br/>
9 </div>
left.jsp
验证跳转没有问题后,先写出库和入库。
新建数据库连接工具DB.java,放到com.ccr.jap.util包下
新建GoodsBean和ListBean,包括对应的私有变量和get、set函数
新建GoodsDao、ListDao进行数据库的增删改查操作
入库涉及到两种情况:
1、商品已存在
2、商品未存在
首先调用GoodsDao里的chick()函数,如果商品存在,返回id,否则返回-1;
1 public int chick(GoodsBean g)
2 {
3 int i=-1;//i>0查询有结果
4 DB db=new DB();
5 Connection con = db.getCon();
6 try
7 {
8 Statement stm = con.createStatement();
9 ResultSet rs =stm.executeQuery("select id from goods where name='"+g.getName()+"' and PM='"+g.getPM()+"' and type='"+g.getType()+"' and specification='"+g.getSpecification()+"'");
10 if(rs.next())
11 {
12 i=rs.getInt("id");
13 }
14 }
15 catch (Exception e)
16 {
17 e.printStackTrace();
18 }
19 return i;
20 }
GoodsDao.chick()
如果存在,直接修改商品数量
1 public boolean update(int number,int i)
2 {
3 int num=0;
4 DB db=new DB();
5 Connection con = db.getCon();
6 try
7 {
8 Statement stm = con.createStatement();
9 ResultSet rs =stm.executeQuery("select number from goods where id="+i);
10 System.out.println("update"+i+"num "+number);
11 rs.next();
12 num=rs.getInt("number");
13 System.out.print(num+"+"+number);
14 num=num+number;
15 System.out.println("="+num);
16 stm.execute("update goods set number="+num+" where id="+i);
17 }
18 catch (Exception e)
19 {
20 e.printStackTrace();
21 return false;
22 }
23 return true;
24 }
GoodsDao.update(int number,int i)
不存在,则新建商品信息
1 public boolean insert(GoodsBean g)
2 {
3 DB db=new DB();
4 Connection con = db.getCon();
5 try
6 {
7 Statement stm = con.createStatement();
8 stm.execute("insert into goods(name,PM,type,specification,number) values ('"+g.getName()+"','"+g.getPM()+"','"+g.getType()+"','"+g.getSpecification()+"',"+g.getNumber()+")");
9 }
10 catch (Exception e)
11 {
12 e.printStackTrace();
13 return false;
14 }
15 return true;
16 }
GoodsDao.insert(GoodsBean g)
然后调用ListDao里的insert()函数新建入库信息
1 public boolean insert(ListBean l)
2 {
3 DB db=new DB();
4 Connection con = db.getCon();
5 try
6 {
7 Statement stm = con.createStatement();
8 stm.execute("insert into list(name,PM,type,specification,number,units,PName,io,date) "
9 + "values ('"+l.getName()+"','"+l.getPM()+"','"+l.getType()+"','"+l.getSpecification()+"',"+l.getNumber()+",'"+l.getUnits()+"','"+l.getPName()+"','"+l.getIo()+"','"+l.getDate()+"')");
10 }
11 catch (Exception e)
12 {
13 e.printStackTrace();
14 return false;
15 }
16 return true;
17 }
ListDao.insert(ListBean l)
出库同理,但是在number赋值前先乘以-1变成负数
然后是servlet和jsp部分
入库:
1 <h1>入库</h1>
2 <form method="post" action="GoodsServlet?action=in">
3 <div>
4 <div>
5 <div style="display:inline"><label for="name"> 名 称 </label></div>
6 <div style="display:inline"><input type="text" name="name" id="name"></div>
7 </div><br/>
8 <div>
9 <div style="display:inline"><label for="name"> 型 号 </label></div>
10 <div style="display:inline"><input type="text" name="type" id="type"></div>
11 </div><br/>
12 <div>
13 <div style="display:inline"><label for="name">出产厂家</label></div>
14 <div style="display:inline"><input type="text" name="PM" id="PM"></div>
15 </div><br/>
16 <div>
17 <div style="display:inline"><label for="name"> 规 格 </label></div>
18 <div style="display:inline"><input type="text" name="specification" id="specification"></div>
19 </div><br/>
20 <div>
21 <div style="display:inline"><label for="name">入库单位</label></div>
22 <div style="display:inline"><input type="text" name="units" id="units"></div>
23 </div><br/>
24 <div>
25 <div style="display:inline"><label for="name">送货人名</label></div>
26 <div style="display:inline"><input type="text" name="PName" id="PName"></div>
27 </div><br/>
28 <div>
29 <div style="display:inline"><label for="name"> 数 量 </label></div>
30 <div style="display:inline"><input type="text" name="number" id="number"></div>
31 </div><br/>
32 <div>
33 <div><input type="submit" value="提交"></div>
34 </div>
35 </div>
36 </form>
in.jsp
提交form表单到GoodsServlet,并附加信息action=in,出库同理
1 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
2 {
3 request.setCharacterEncoding("UTF-8");
4 response.setCharacterEncoding("UTF-8");
5 response.setContentType("text/html;charset=UTF-8");
6 response.setHeader("content-type", "text/html;charset=UTF-8");
7 String action=request.getParameter("action");
8 if(action.equals("select"))
9 {
10 select(request,response);
11 }
12 if(action.equals("in"))
13 {
14 in(request,response);
15 }
16 if(action.equals("out"))
17 {
18 out(request,response);
19 }
20 }
21 protected void in(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
22 {
23 int id=-2;
24 ListBean b=new ListBean();
25 GoodsBean g=new GoodsBean();
26 GoodsDao gd=new GoodsDao();
27 ListDao ld=new ListDao();
28 b.setName(request.getParameter("name"));
29 b.setPM(request.getParameter("PM"));
30 b.setType(request.getParameter("type"));
31 b.setSpecification(request.getParameter("specification"));
32 b.setNumber(Integer.parseInt(request.getParameter("number")));
33 b.setUnits(request.getParameter("units"));
34 b.setPName(request.getParameter("PName"));
35 b.setIo("in");
36 b.setDate();
37 ld.insert(b);
38
39
40 g.setName(request.getParameter("name"));
41 g.setPM(request.getParameter("PM"));
42 g.setType(request.getParameter("type"));
43 g.setSpecification(request.getParameter("specification"));
44 g.setNumber(Integer.parseInt(request.getParameter("number")));
45 id=gd.chick(g);
46 System.out.println(id);
47 if(id>0)
48 {
49 gd.update(b.getNumber(), id);
50 }
51 else
52 {
53 gd.insert(g);
54 }
55 request.getRequestDispatcher("in.jsp").forward(request,response);
56 }
57 protected void out(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
58 {
59 int id=-2;
60 ListBean b=new ListBean();
61 GoodsBean g=new GoodsBean();
62 GoodsDao gd=new GoodsDao();
63 ListDao ld=new ListDao();
64 b.setName(request.getParameter("name"));
65 b.setPM(request.getParameter("PM"));
66 b.setType(request.getParameter("type"));
67 System.out.println(b.getType());
68 b.setSpecification(request.getParameter("specification"));
69 int number=Integer.parseInt(request.getParameter("number"));
70 number=-1*number;
71 System.out.println("SN"+number);
72 b.setNumber(number);
73 b.setUnits(request.getParameter("units"));
74 b.setPName(request.getParameter("PName"));
75 b.setIo("out");
76 b.setDate();
77 ld.insert(b);
78
79
80 g.setName(request.getParameter("name"));
81 g.setPM(request.getParameter("PM"));
82 g.setType(request.getParameter("type"));
83 g.setSpecification(request.getParameter("specification"));
84 g.setNumber(number);
85 id=gd.chick(g);
86 if(id>0)
87 {
88 gd.update(b.getNumber(), id);
89 }
90 else
91 {
92 System.out.println("错误");
93 }
94 request.getRequestDispatcher("out.jsp").forward(request,response);
95 }
View Code
查询:在页面中,输入日期和时间,点击查询按钮,出现查询结构
调用ListDao的Select,使用名称和日期进行查询,将结果存在List中
jsp代码
1 <h1>信息查询</h1>
2 <form method="post" action="GoodsServlet?action=select">
3 <div>
4 <div>
5 <div><label>产品名称</label></div>
6 <div><input type="text" name="name" id="name"></div>
7 </div>
8 <div>
9 <div><label>日期</label></div>
10 <div><input type="text" name="date" id="date"></div>
11 </div>
12 <div>
13 <input type="submit" value="查询">
14 </div>
15 </div>
16 </form>
17 <c:set var="list" value="${sessionScope.l}"></c:set>
18 <c:if test="${l!=null }">
19 <c:forEach var="i" begin="0" end="${l.size()-1}">
20 名称 ${l[i].getName()}
21 型号 ${l[i].getType()}
22 出产厂家 ${l[i].getPM()}
23 规格 ${l[i].getSpecification()}
24 数量 ${l[i].getNumber()}
25 单位 ${l[i].getUnits()}
26 姓名 ${l[i].getPName()}
27 操作 ${l[i].getIo()}
28 日期 ${l[i].getDate()}
29 <br/>
30 </c:forEach>
31 </c:if>
select.jsp
使用了EL语句和JSTL标签进行遍历
Java代码:
1 public List<ListBean> select(String name,String date)
2 {
3 List<ListBean> l=new ArrayList<ListBean>();
4 ListBean b=null;
5 DB db=new DB();
6 Connection con = db.getCon();
7 try
8 {
9 Statement stm = con.createStatement();
10 ResultSet rs = stm.executeQuery("select * from list where name like '%"+name+"%' and date ='"+date+"'");
11 while(rs.next())
12 {
13 b=new ListBean();
14 b.setName(rs.getString("name"));
15 b.setPM(rs.getString("PM"));
16 b.setType(rs.getString("type"));
17 b.setSpecification(rs.getString("specification"));
18 b.setNumber(rs.getInt("number"));
19 b.setUnits(rs.getString("units"));
20 b.setPName(rs.getString("PName"));
21 b.setIo(rs.getString("io"));
22 b.setDate(rs.getString("date"));
23 l.add(b);
24 }
25 } catch (Exception e)
26 {
27 e.printStackTrace();
28
29 }
30 return l;
31 }
ListDao.select(String name,String date)
GoodsServlet:
1 protected void select(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
2 {
3 String name=request.getParameter("name");
4 String date=request.getParameter("date");
5 ListDao d=new ListDao();
6 List<ListBean>l=d.select(name,date);
7 request.getSession().setAttribute("l", l);
8 request.getRequestDispatcher("select.jsp").forward(request,response);
9 }
View Code
第二部分:商品的增删改查
首先写好Dao相关的操作函数
1 public class GoodsDao
2 {
3 public boolean delete(String name)
4 {
5 DB db=new DB();
6 Connection con = db.getCon();
7 try
8 {
9 Statement stm = con.createStatement();
10 stm.execute("delete from goods where name='"+name+"'");
11 }
12 catch (Exception e)
13 {
14 e.printStackTrace();
15 return false;
16 }
17 return true;
18 }
19 public List<GoodsBean> select(String name)
20 {
21 List<GoodsBean> l=new ArrayList<GoodsBean>();
22 GoodsBean b=null;
23 DB db=new DB();
24 Connection con = db.getCon();
25 try
26 {
27 Statement stm = con.createStatement();
28 ResultSet rs = stm.executeQuery("select * from goods where name like '%"+name+"%'");
29 while(rs.next())
30 {
31 b=new GoodsBean();
32 b.setName(rs.getString("name"));
33 b.setPM(rs.getString("PM"));
34 b.setType(rs.getString("type"));
35 b.setSpecification(rs.getString("specification"));
36 b.setNumber(rs.getInt("number"));
37 l.add(b);
38 }
39 } catch (Exception e)
40 {
41 e.printStackTrace();
42
43 }
44 return l;
45 }
46 public boolean insert(GoodsBean g)
47 {
48 DB db=new DB();
49 Connection con = db.getCon();
50 try
51 {
52 Statement stm = con.createStatement();
53 stm.execute("insert into goods(name,PM,type,specification,number) values ('"+g.getName()+"','"+g.getPM()+"','"+g.getType()+"','"+g.getSpecification()+"',"+g.getNumber()+")");
54 }
55 catch (Exception e)
56 {
57 e.printStackTrace();
58 return false;
59 }
60 return true;
61 }
62 public boolean update(int number,int i)
63 {
64 int num=0;
65 DB db=new DB();
66 Connection con = db.getCon();
67 try
68 {
69 Statement stm = con.createStatement();
70 ResultSet rs =stm.executeQuery("select number from goods where id="+i);
71 System.out.println("update"+i+"num "+number);
72 rs.next();
73 num=rs.getInt("number");
74 System.out.print(num+"+"+number);
75 num=num+number;
76 System.out.println("="+num);
77 stm.execute("update goods set number="+num+" where id="+i);
78 }
79 catch (Exception e)
80 {
81 e.printStackTrace();
82 return false;
83 }
84 return true;
85 }
86 public boolean update(String name,GoodsBean b)
87 {
88 System.out.println("Dao"+name+b.getName()+b.getType());
89 DB db=new DB();
90 Connection con = db.getCon();
91 try
92 {
93 Statement stm = con.createStatement();
94 stm.execute("update goods set name='"+b.getName()+"',type='"+b.getType()+"',PM='"+b.getPM()+"',specification='"+b.getSpecification()+"' where name='"+name+"'");
95 }
96 catch (Exception e)
97 {
98 e.printStackTrace();
99 return false;
100 }
101 return true;
102 }
103 public int chick(GoodsBean g)
104 {
105 int i=-1;//i>0查询有结果
106 DB db=new DB();
107 Connection con = db.getCon();
108 try
109 {
110 Statement stm = con.createStatement();
111 ResultSet rs =stm.executeQuery("select id from goods where name='"+g.getName()+"' and PM='"+g.getPM()+"' and type='"+g.getType()+"' and specification='"+g.getSpecification()+"'");
112 if(rs.next())
113 {
114 i=rs.getInt("id");
115 }
116 }
117 catch (Exception e)
118 {
119 e.printStackTrace();
120 }
121 return i;
122 }
123 }
GoodsDao
在修改时,重构一个参数为GoodsBean和name的函数
然后写servlet(由于之前忘了写这一部分,所以直接起名为GoodsServlet2)
1 package com.ccr.jsp.servlet;
2
3 import java.io.IOException;
4 import java.util.List;
5
6 import javax.servlet.ServletException;
7 import javax.servlet.annotation.WebServlet;
8 import javax.servlet.http.HttpServlet;
9 import javax.servlet.http.HttpServletRequest;
10 import javax.servlet.http.HttpServletResponse;
11
12 import com.ccr.jsp.bean.GoodsBean;
13 import com.ccr.jsp.bean.ListBean;
14 import com.ccr.jsp.dao.GoodsDao;
15 import com.ccr.jsp.dao.ListDao;
16
17 /**
18 * Servlet implementation class GoodsServlet2
19 */
20 @WebServlet("/GoodsServlet2")
21 public class GoodsServlet2 extends HttpServlet {
22 private static final long serialVersionUID = 1L;
23
24 /**
25 * @see HttpServlet#HttpServlet()
26 */
27 public GoodsServlet2() {
28 super();
29 // TODO Auto-generated constructor stub
30 }
31
32 /**
33 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
34 */
35 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
36 request.setCharacterEncoding("UTF-8");
37 response.setCharacterEncoding("UTF-8");
38 response.setContentType("text/html;charset=UTF-8");
39 response.setHeader("content-type", "text/html;charset=UTF-8");
40 String action=request.getParameter("action");
41 if(action.equals("add"))
42 {
43 System.out.println("add");
44 add(request,response);
45 }
46 if(action.equals("delete"))
47 {
48 delete(request,response);
49 }
50 if(action.equals("update"))
51 {
52 update(request,response);
53 }
54 if(action.equals("select"))
55 {
56 select(request,response);
57 }
58 if(action.equals("select2"))
59 {
60 select2(request,response);
61 }
62 }
63 protected void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
64 {
65 int id=-2;
66 GoodsBean g=new GoodsBean();
67 GoodsDao gd=new GoodsDao();
68 g.setName(request.getParameter("name"));
69 g.setPM(request.getParameter("PM"));
70 g.setType(request.getParameter("type"));
71 g.setSpecification(request.getParameter("specification"));
72 g.setNumber(0);
73 System.out.println(id);
74 gd.insert(g);
75 request.getRequestDispatcher("1.jsp").forward(request,response);
76 }
77 protected void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
78 {
79 String name=request.getParameter("name");
80 GoodsDao gd=new GoodsDao();
81 gd.delete(name);
82 request.getRequestDispatcher("2.jsp").forward(request,response);
83 }
84 protected void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
85 {
86 String name=request.getParameter("name0");
87 System.out.println("SNAME"+name);
88 GoodsBean g=new GoodsBean();
89 GoodsDao gd=new GoodsDao();
90 g.setName(request.getParameter("name"));
91 g.setPM(request.getParameter("PM"));
92 g.setType(request.getParameter("type"));
93 g.setSpecification(request.getParameter("specification"));
94 System.out.println(g.getName()+g.getType());
95 gd.update(name,g);
96 request.getRequestDispatcher("3.jsp").forward(request,response);
97 }
98 protected void select(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
99 {
100 String name=request.getParameter("name0");
101 System.out.println("name="+name);
102 GoodsDao gd=new GoodsDao();
103 List<GoodsBean>gl=gd.select(name);
104 System.out.println(gl.size());
105 request.getSession().setAttribute("gl", gl);
106 request.getRequestDispatcher("4.jsp").forward(request,response);
107 }
108 protected void select2(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
109 {
110 String name=request.getParameter("name0");
111 System.out.println("name="+name);
112 GoodsDao gd=new GoodsDao();
113 List<GoodsBean>gl=gd.select(name);
114 System.out.println(gl.size());
115 request.getSession().setAttribute("gl", gl);
116 request.getRequestDispatcher("3.jsp").forward(request,response);
117 }
118 }
GoodsServlet2
在插入时,直接设置数量为0
最后设计一下界面。完工!
总结:
1、新学习了el语句和JSTL标签库的一些使用,很方便
2、复制代码很快,但是很容易出错