創(chuàng)建數(shù)據(jù)庫和表

sql語句:

DROP TABLE IF EXISTS PRoduct;CREATE TABLE product(       product_id varchar(20) NOT NULL,       product_name varchar(50) DEFAULT NULL,       price decimal(6,2) DEFAULT NULL,       info varchar(100) DEFAULT NULL,       PRIMARY KEY(product_id))ENGINE=InnoDB DEFAULT CHARSET=utf8;

文件目錄如下

Product.java

 1 package com.g.pojo; 2  3 public class Product { 4     private String product_id; 5     private String product_name; 6     private double price; 7     private String info; 8     public String getProduct_id() { 9         return product_id;10     }11     public void setProduct_id(String product_id) {12         this.product_id = product_id;13     }14     public String getProduct_name() {15         return product_name;16     }17     public void setProduct_name(String product_name) {18         this.product_name = product_name;19     }20     public double getPrice() {21         return price;22     }23     public void setPrice(double price) {24         this.price = price;25     }26     public String getInfo() {27         return info;28     }29     public void setInfo(String info) {30         this.info = info;31     }32     33     34     35 }

ProductDao.java代碼

 1 package com.g.dao; 2  3 import java.util.List; 4  5 import com.g.pojo.Product; 6  7 public interface ProductDao { 8     /** 9      * 數(shù)據(jù)庫 新增數(shù)據(jù)10      * @param product 要增加的數(shù)據(jù)對象11      * @return是否增加成功的標(biāo)志12      * @throws Exception 如果有異常,將直接拋出13      */14     public boolean addProduct(Product product) throws Exception;15     16     /**17      * 查詢?nèi)康腜roduct18      * @param product_name 產(chǎn)品名稱19      * @return返回全部的查詢結(jié)果,每一個product對象表示表的一行記錄20      * @throws Exception 如果有異常,將直接拋出21      */22     public List<Product> findAll(String product_name)throws Exception;23     24     /**25      * 根據(jù)產(chǎn)品編號查詢產(chǎn)品26      * @param product_id 產(chǎn)品編號27      * @return 產(chǎn)品對象28      * @throws Exception 如果有異常,將直接拋出29      */30     public Product findByProductId(String product_id)throws Exception;31     32 }

ProductService.java代碼

 1 package com.g.service; 2  3 import java.util.List; 4  5 import com.g.dao.ProductDao; 6 import com.g.dao.ProductDaoImpl; 7 import com.g.db.DBConnection; 8 import com.g.pojo.Product; 9 10 /**11  * 操作數(shù)據(jù)庫12  * @author 思思博士13  *14  */15 public class ProductService implements ProductDao{16     17     private DBConnection dbconn=null;18     private ProductDao dao=null;19     //在構(gòu)造方法中實例化數(shù)據(jù)庫連接,同時實例化dao對象20     public ProductService() throws Exception{21         this.dbconn=new DBConnection();22         //實例化ProductDao的實現(xiàn)類23         this.dao=new ProductDaoImpl(this.dbconn.getConnection());                24     }    25     public boolean addProduct(Product product) throws Exception {26         boolean flag=false;27         try{28             if(this.dao.findByProductId(product.getProduct_id())==null){29                 //如果要插入的產(chǎn)品編號不存在30                 flag=this.dao.addProduct(product);//新增一條產(chǎn)品信息31             }32         }33         catch (Exception e) {34             throw e;35         }finally{36             this.dbconn.close();37         }38         39         return flag;40     }41 42     public List<Product> findAll(String keyWord) throws Exception {43         List<Product> all=null; //定義產(chǎn)品返回的集合44         try {45             all=this.dao.findAll(keyWord);46         } catch (Exception e) {47             throw e;48         }finally{49             this.dbconn.close();50         }51         return all;52     }53 54     public Product findByProductId(String product_id) throws Exception {55         Product product=null;56         try {57             product=this.dao.findByProductId(product_id);58         } catch (Exception e) {59             throw e;60         }finally{61             this.dbconn.close();62         }63         return product;64     }65 }

DBConnection.java代碼

 1 package com.g.db; 2  3 import java.sql.Connection; 4 import java.sql.DriverManager; 5  6 public class DBConnection { 7     private static final String Driver="com.MySQL.jdbc.Driver"; 8     private static final String Url="jdbc:mysql://127.0.0.1:3306/testweb"; 9     private static final String User="root";10     private static final String Password="gys";11     private Connection conn=null;12     13     //進(jìn)行數(shù)據(jù)庫連接14     public DBConnection() throws Exception{15         try{16             //用反射加載數(shù)據(jù)庫驅(qū)動17             Class.forName(Driver);18             this.conn=DriverManager.getConnection(Url,User,Password);19         }20         catch (Exception e) {21             throw e;22         }23     }24     //取得數(shù)據(jù)庫的連接25     public Connection getConnection(){26         return this.conn;        27     }28     //關(guān)閉數(shù)據(jù)庫29     public void close() throws Exception{30         if(this.conn!=null){31             try {32                 this.conn.close();33                 34             } catch (Exception e) {35                 throw e;36             }37         }38     }39     40 }

ProductDaoImpI.java代碼

 1 package com.g.dao; 2  3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.util.ArrayList; 7 import java.util.List; 8  9 import com.g.pojo.Product;10 11 /**12  * 實現(xiàn)DAO接口的類.但是不負(fù)責(zé)數(shù)據(jù)庫的打開和關(guān)閉13  * @author 思思博士14  *15  */16 public class ProductDaoImpl implements ProductDao{17     private Connection conn=null;18     private PreparedStatement pstmt=null;19     //通過構(gòu)造方法取得數(shù)據(jù)庫連接20     public ProductDaoImpl(Connection conn){21         this.conn=conn;22     }23     public boolean addProduct(Product product) throws Exception {24         boolean flag=false;25         String sql="insert into product(product_id,product_name,price,info) values(?,?,?,?)";26         this.pstmt=this.conn.prepareStatement(sql);27         this.pstmt.setString(1,product.getProduct_id());28         this.pstmt.setString(2,product.getProduct_name());29         this.pstmt.setDouble(3,product.getPrice());30         this.pstmt.setString(4,product.getInfo());31         32         if(this.pstmt.executeUpdate()>0){33             flag=true;34         }35         this.pstmt.close();36         return flag;37     }38     39     public List<Product> findAll(String product_name) throws Exception {        40         List<Product> list=new ArrayList<Product>();41         String sql="select product_id,product_name,price,info from product";42         if(product_name!=null&&!"".equals(product_name)){43             sql="select product_id,product_name,price,info from product where product_name like ?";44             this.pstmt=this.conn.prepareStatement(sql);45             this.pstmt.setString(1,"%"+product_name+"%");46         }47         else {48             this.pstmt=this.conn.prepareStatement(sql);49         }50         ResultSet rs=this.pstmt.executeQuery();51         Product product=null;52         while(rs.next()){53             product=new Product();54             product.setProduct_id(rs.getString(1));55             product.setProduct_name(rs.getString(2));56             product.setPrice(rs.getDouble(3));57             product.setInfo(rs.getString(4));58             list.add(product);59         }60         this.pstmt.close();61         return list;62     }63     64     public Product findByProductId(String product_id) throws Exception {65         Product product=null;66         String sql="select product_id,product_name,price,info from product where product_id=?";67         this.pstmt=this.conn.prepareStatement(sql);68         this.pstmt.setString(1,product_id);69         ResultSet rs=this.pstmt.executeQuery();70         if(rs.next()){71             product=new Product();72             product.setProduct_id(rs.getString(1));73             product.setProduct_name(rs.getString(2));74             product.setPrice(rs.getDouble(3));75             product.setInfo(rs.getString(4));76         }77         this.pstmt.close();78         return product;79     }80     81     82 }

DAOFactory.java代碼

 1 package com.g.factory; 2  3 import com.g.dao.ProductDao; 4 import com.g.service.ProductService; 5  6 public class DAOFactory { 7     public static ProductDao getIEmpDAOInstance() throws Exception{ 8         //取得業(yè)務(wù)操作類 9         return new ProductService();10     }11 }

TestInsertProduct.java代碼

 1 package com.g.test; 2  3 import com.g.factory.DAOFactory; 4 import com.g.pojo.Product; 5  6 public class TestInsertProduct { 7     public static void main(String[] args) { 8         Product product=null; 9         try {10             for(int i=0;i<5;i++){11                 product=new Product();12                 product.setProduct_id("350115001010"+i);13                 product.setProduct_name("水杯"+i);14                 product.setPrice(100+i);15                 product.setInfo("這是一個精美的杯子"+i);16                 DAOFactory.getIEmpDAOInstance().addProduct(product);17             }18         } catch (Exception e) {19             e.printStackTrace();20         }21     }22 }

add.jsp代碼

 1 <%@ page language="java" import="java.util.*" contentType="text/html; charset=utf-8"%> 2 <% 3 String path = request.getContextPath(); 4 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; 5 %> 6  7 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 8 <html> 9   <head>10     <base href="<%=basePath%>">11     12     <title>添加產(chǎn)品</title>13   </head>14   15   <body>16     <form action="insert.jsp" method="post">17         產(chǎn)品編號:<input name="product_id" /><br/>18         產(chǎn)品名稱:<input name="product_name" /><br />19         產(chǎn)品價格:<input name="price" /><br/>20         產(chǎn)品信息:<textarea rows="5" cols="15" name="info"></textarea><br/>21         <input type="submit" value="添加" />&nbsp;&nbsp;22         <input type="reset" value="重置" />23     </form>24   </body>25 </html>

insert.jsp代碼

 1 <%@page import="com.g.factory.DAOFactory"%> 2 <%@page import="com.g.pojo.Product"%> 3 <%@ page language="java" import="java.util.*" contentType="text/html; charset=utf-8"%> 4 <% 5 String path = request.getContextPath(); 6 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; 7 %> 8  9 <%10  request.setCharacterEncoding("utf-8");11  %>12 13 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">14 <html>15   <head>16     <base href="<%=basePath%>">    17     <title>執(zhí)行添加產(chǎn)品</title>18   </head>19   20   <body>21     <%22         Product product=new Product();23         product.setProduct_id(request.getParameter("product_id"));24         product.setProduct_name(request.getParameter("product_name"));25         product.setPrice(Double.parseDouble(request.getParameter("price")));26         product.setInfo(request.getParameter("info"));27         boolean flag=DAOFactory.getIEmpDAOInstance().addProduct(product);//執(zhí)行添加操作28         if(flag){29         %>30         <h4>添加產(chǎn)品信息成功</h4>31         <%}else{%>32         <h4>添加產(chǎn)品信息失敗.</h4>33         <%} %>34   </body>35 </html>

list.jsp代碼

 1 <%@page import="com.g.factory.DAOFactory"%> 2 <%@page import="com.g.pojo.Product"%> 3 <%@ page language="java" import="java.util.*" contentType="text/html; charset=utf-8"%> 4 <% 5 String path = request.getContextPath(); 6 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; 7 %> 8 <%request.setCharacterEncoding("utf-8"); %> 9 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">10 <html>11   <head>12     <base href="<%=basePath%>">    13     <title>查詢產(chǎn)品列表</title>14   </head>15   16   <body>17    <%18        String product_name=request.getParameter("product_name");19        if(product_name==null)20            product_name="";21        List<Product> list=DAOFactory.getIEmpDAOInstance().findAll(product_name);22     %>23     <form action="list.jsp" method="post">24         請輸入產(chǎn)品名稱:<input name="product_name" value="<%=product_name %>"/>25         <input type="submit" value="提交" />26     </form>27     <table>28         <tr>29             <td>產(chǎn)品編號</td>30             <td>產(chǎn)品名稱</td>31             <td>產(chǎn)品價格</td>32             <td>產(chǎn)品信息</td>33         </tr>34         <%35             for(int i=0;i<list.size();i++){36                 Product p=list.get(i);//取出每一個產(chǎn)品37             %>38             <tr>39                  <td><%=p.getProduct_id() %></td>40                  <td><%=p.getProduct_name() %></td>41                  <td><%=p.getPrice() %></td>42                  <td><%=p.getInfo() %></td>43              </tr>44             <%}%>45          46          47     </table>48   </body>49 </html>