java学习笔记之分层增删改查

增删改查

一.准备jar包

　　数据库驱动包

　　DBUtils包

　　C3P0连接池的jar包

二.配置好C3P0的配置文件

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <c3p0-config>
 3     <!-- 默认配置 -->
 4     <default-config>
 5         <property name="driverClass">com.mysql.jdbc.Driver</property>
 6         <property name="jdbcUrl">jdbc:mysql:///day26</property>
 7         <property name="user">root</property>
 8         <property name="password">root</property>
 9         
10         <property name="automaticTestTable">con_test</property>
11         <property name="checkoutTimeout">30000</property>
12         <property name="idleConnectionTestPeriod">30</property>
13         <property name="initialPoolSize">10</property>
14         <property name="maxIdleTime">30</property>
15         <property name="maxPoolSize">100</property>
16         <property name="minPoolSize">10</property>
17         <property name="maxStatements">200</property>
18         <user-overrides user="test-user">
19             <property name="maxPoolSize">10</property>
20             <property name="minPoolSize">1</property>
21             <property name="maxStatements">0</property>
22         </user-overrides>
23     </default-config> <!-- This app is massive! -->
24     <!-- 命名的配置 -->
25     <named-config name="my">
26         <property name="acquireIncrement">50</property>
27         <property name="initialPoolSize">100</property>
28         <property name="minPoolSize">50</property>
29         <property name="maxPoolSize">1000</property> <!-- intergalactoApp adopts a different approach to configuring statement 
30             caching -->
31         <property name="maxStatements">0</property>
32         <property name="maxStatementsPerConnection">5</property> <!-- he's important, but there's only one of him -->
33         <user-overrides user="master-of-the-universe">
34             <property name="acquireIncrement">1</property>
35             <property name="initialPoolSize">1</property>
36             <property name="minPoolSize">1</property>
37             <property name="maxPoolSize">5</property>
38             <property name="maxStatementsPerConnection">50</property>
39         </user-overrides>
40     </named-config>
41 </c3p0-config>

三.准备好工具类

　　ThreadLocal工具类：

 1 import java.sql.Connection;
 2 import java.sql.SQLException;
 3 
 4 public class ConnectionManager {
 5     private  static ThreadLocal<Connection> tl=new ThreadLocal<>();
 6     public static Connection getConnection() throws SQLException{
 7         Connection  con=tl.get();
 8         if(con==null){
 9             con=MyC3P0Utils.getConnection();
10             tl.set(con);
11         }
12         return con;
13     }
14     //开启事务
15     public static void begin() throws SQLException{
16         getConnection().setAutoCommit(false);
17     }
18     //提交事务
19     public static void commit() throws SQLException{
20         getConnection().commit();
21     }
22     //回滚事务
23     
24     public static void rollback() throws SQLException{
25         getConnection().rollback();
26     }
27     //关闭资源
28     public static void close() throws SQLException{
29         getConnection().close();
30     }
31     
32 }

C3P0:

 1 import java.sql.Connection;
 2 import java.sql.SQLException;
 3 import javax.sql.DataSource;
 4 import com.mchange.v2.c3p0.ComboPooledDataSource;
 5 public class MyC3P0Utils {
 6     private static DataSource dataSource=new ComboPooledDataSource();
 7     public static DataSource getDataSource(){
 8         return dataSource;
 9     }
10     public static Connection getConnection() throws SQLException{
11         return dataSource.getConnection();
12     }
13 
14 }

四.Dao层代码：

 1 public class ProductDao {
 2     /**
 3      * 查询所有数据
 4      * @return  Product泛型的List集合
 5      * @throws SQLException
 6      */
 7     public List<Products> findAll() throws SQLException{
 8         QueryRunner qr = new QueryRunner(MyC3P0Utils.getDataSource());
 9         List<Products> list = qr.query("select * from products",new BeanListHandler<>(Products.class));
10         return list;
11     }
12     /**
13      * 通过id查找数据
14      * @param id
15      * @return
16      * @throws SQLException
17      */
18     public Products findById(int id) throws SQLException{
19         QueryRunner qr = new QueryRunner(MyC3P0Utils.getDataSource());
20         Products product = qr.query("select * from products where pid=?",new BeanHandler<>(Products.class),id);
21         return product;
22     }
23     /**
24      * 插入数据
25      * @param products  products类型的对象
26      * @return  影响行数
27      * @throws SQLException
28      */
29     public int  insertData(Products products) throws SQLException{
30         QueryRunner qr = new QueryRunner(MyC3P0Utils.getDataSource());
31         Object[] objects={products.getPname(),products.getPrice(),products.getCategory_id()};
32         int re = qr.update("insert into products values(null,?,?,?)",objects);
33         return re;
34     }
35     /**
36      * 修改数据
37      * @param products
38      * @return 影响的行数
39      * @throws SQLException
40      */
41     public int updateData(Products products) throws SQLException{
42         QueryRunner qr = new QueryRunner(MyC3P0Utils.getDataSource());
43         Object[] objects={products.getPname(),products.getPrice(),products.getCategory_id(),products.getPid()};
44         int re = qr.update("update products set pname= ? ,price=? ,category_id=? where pid=?",objects);
45         return re;
46     }
47     /**
48      * 通过i删除产品
49      * @param pid 产品id
50      * @return  受影响的行数
51      * @throws SQLException
52      */
53     public int deleteData(int pid) throws SQLException{
54         QueryRunner qr = new QueryRunner();
55         int re = qr.update(ConnectionManager.getConnection(),"delete from products where pid =?",pid);
56         return re;
57     }
58 
59 }

五.Service层代码：

  1 public class ProductService {
  2     /**
  3      *查询所有
  4      * @return
  5      */
  6     public List<Products> findAll(){
  7         ProductDao productDao=new ProductDao();
  8         List<Products> list =null;
  9         try {
 10             list = productDao.findAll();
 11         } catch (SQLException e) {
 12             // TODO Auto-generated catch block
 13             e.printStackTrace();
 14         }
 15         return list;
 16     }
 17     /**
 18      * 通过id 查询数据
 19      * @param id
 20      * @return
 21      */
 22     public Products findById(int id){
 23         ProductDao productDao=new ProductDao();
 24         Products product=null;
 25         try {
 26              product = productDao.findById(id);
 27         } catch (SQLException e) {
 28             // TODO Auto-generated catch block
 29             e.printStackTrace();
 30         }
 31         return product;
 32         
 33     }
 34     /**
 35      * 插入数据
 36      * @param products
 37      * @return
 38      */
 39     public int insertData(Products products){
 40         ProductDao productDao=new ProductDao();
 41         int re=-1;
 42         try {
 43             re = productDao.insertData(products);
 44         } catch (SQLException e) {
 45             // TODO Auto-generated catch block
 46             e.printStackTrace();
 47         }
 48         return re;
 49     }
 50     /**
 51      * 修改数据
 52      * @param products
 53      * @return
 54      */
 55     public int updateData(Products products){
 56         ProductDao productDao=new ProductDao();
 57         int re=-1;
 58         try {
 59             re = productDao.updateData(products);
 60         } catch (SQLException e) {
 61             // TODO Auto-generated catch block
 62             e.printStackTrace();
 63         }
 64         return re;
 65         
 66     }
 67     /**
 68      * 通过pid删除数据
 69      * @param pid
 70      * @return
 71      */
 72     public int deleteData(int pid){
 73         ProductDao productDao=new ProductDao();
 74         int re= -1;
 75         try {
 76             re = productDao.deleteData(pid);
 77         } catch (SQLException e) {
 78             // TODO Auto-generated catch block
 79             e.printStackTrace();
 80         }
 81         return re;
 82     }
 83     
 84     public boolean deleteAll(HashSet<Integer> set){
 85         ProductDao productDao=new ProductDao();
 86         boolean flag=true;
 87         try {
 88             ConnectionManager.begin();
 89             for (Integer pid : set) {
 90         
 91                 int re = productDao.deleteData(pid);
 92                 
 93                 if(!(re>0)){
 94                     flag=false;
 95                     break;
 96                 }
 97             }
 98             if(flag){
 99                 ConnectionManager.commit();
100                 return true;
101             }else{
102                 ConnectionManager.rollback();
103                 return false;
104             }
105         } catch (SQLException e) {
106                 // TODO Auto-generated catch block
107             
108             try {
109                 ConnectionManager.rollback();
110             } catch (SQLException e1) {
111                 // TODO Auto-generated catch block
112                 e1.printStackTrace();
113             }
114         }finally{
115             try {
116                 ConnectionManager.close();
117             } catch (SQLException e) {
118                 // TODO Auto-generated catch block
119                 e.printStackTrace();
120             }
121         }
122         return false;
123     }
124     
125 
126 }

六 web层代码：

  1 public class ProductWeb {
  2     public static void main(String[] args) {
  3         Scanner scanner=new Scanner(System.in);
  4         while(true){
  5             System.out.println("请输入你的选择:");
  6             System.out.println("I:插入、U:修改、D:删除、DA:批量删除、QI：根据id查询、FA：查询所有、Q:退出");
  7             String string=scanner.nextLine().toUpperCase();
  8             
  9             switch (string) {
 10                 case "I":
 11                     insertData(scanner);
 12                     break;
 13                 case "U":
 14                     updateDate(scanner);
 15                     break;
 16                 case "D":
 17                     deleteData(scanner);
 18                     break;
 19                 case "DA":
 20                     deleteAll(scanner);
 21                     break;
 22                 case "QI":
 23                     findById(scanner);
 24                     break;
 25                 case "FA":
 26                     //查询所有
 27                     findAll();
 28                     break;
 29                 case "Q":
 30                     System.out.println("是否要退出？（y/n）");
 31                     String s1 = scanner.nextLine();
 32                     if("y".equalsIgnoreCase(s1)){
 33                         System.exit(0);
 34                     }
 35                     break;
 36                 default:
 37                     break;
 38             }
 39             
 40         
 41         }
 42         
 43     }
 44     /**
 45      * 批量删除
 46      * @param scanner
 47      */
 48     private static void deleteAll(Scanner scanner) {
 49         // TODO Auto-generated method stub
 50         HashSet<Integer> set =new HashSet<>();
 51         ProductService productService=new ProductService();
 52         String regex="[1-9]([0-9]*)";
 53         while(true){
 54             System.out.println("请输入删除产品pid(输入-1停止)");
 55         /*    String str=scanner.nextLine();
 56             if(!str.matches(regex)){
 57                 System.out.println("不符合规则");
 58                 continue;
 59             }
 60             int pid = Integer.parseInt(str);*/
 61             int pid =Integer.parseInt(scanner.nextLine());
 62             if(pid==-1){
 63                 break;
 64             }
 65             Products product = productService.findById(pid);
 66             if(product==null){
 67                 System.out.println("没有该条数据");
 68                 continue;
 69             }
 70             System.out.println(product);
 71             set.add(pid);
 72         }
 73         System.out.println("你要删除的数据条数是"+set.size());
 74         System.out.println("请确认是否要删除？（y/n）");
 75         String choice = scanner.nextLine();
 76         if("y".equalsIgnoreCase(choice)){
 77             boolean flag = productService.deleteAll(set);
 78             if(flag){
 79                 System.out.println("删除成功");
 80             }else {
 81                 System.out.println("删除失败");
 82             }
 83         }
 84         
 85     }
 86     /**
 87      * 通过id删除
 88      * @param scanner
 89      */
 90     private static void deleteData(Scanner scanner) {
 91         // TODO Auto-generated method stub
 92         System.out.println("请输入要删除产品的id");
 93         String regex="[1-9]([0-9]*)";
 94         String str1 = scanner.nextLine();
 95         if(!str1.matches(regex)){
 96             System.out.println("输入格式不符合");
 97             return;
 98         }
 99         int pid = Integer.parseInt(str1);
100         ProductService productService=new ProductService();
101         Products product = productService.findById(pid);
102         if(product==null){
103             System.out.println("没有数据");
104             return;
105         }
106         System.out.println("你要删除的产品数据是，确定要删除吗？（y/n）");
107         System.out.println(product);
108         String choice = scanner.nextLine();
109         if(!"y".equalsIgnoreCase(choice)){
110             System.out.println("输入有误，返回菜单");
111             return;
112         }
113         int re = productService.deleteData(pid);
114         if(re>0){
115             System.out.println("删除成功");
116         }else {
117             System.out.println("删除失败");
118         }
119     }
120     /**
121      * 修改产品
122      * @param scanner
123      */
124     private static void updateDate(Scanner scanner) {
125         // TODO Auto-generated method stub
126         System.out.println("请输入要修改产品的pid");
127         String regex="[1-9]([0-9]*)";
128         String str = scanner.nextLine();
129         if(!str.matches(regex)){
130             System.out.println("格式不正确");
131             return ;
132         }
133         int pid = Integer.parseInt(str);
134         ProductService productService=new ProductService();
135         Products product = productService.findById(pid);
136         if(product==null){
137             System.out.println("没有数据");
138             return;
139         }
140         System.out.println("请输入新的名字");
141         String pname=scanner.nextLine();
142         System.out.println("请输入新的价格");
143         double price = Double.parseDouble(scanner.nextLine());
144         System.out.println("请输入类别，（1是电子，2是服装，3是化妆品）");
145         int category_id = Integer.parseInt(scanner.next());
146         product.setCategory_id(category_id);
147         product.setPname(pname);
148         product.setPrice(price);
149         int re = productService.updateData(product);
150         if(re>0){
151             System.out.println("修改成功");
152         }else{
153             System.out.println("修改失败");
154         }
155     }
156     /**
157      * 插入数据
158      * @param scanner
159      */
160     private static void insertData(Scanner scanner) {
161         System.out.println("请输入产品名称");
162         String pname = scanner.nextLine();
163         System.out.println("请输入价格");
164         double price = Double.parseDouble(scanner.nextLine());
165         System.out.println("请输入类别，（1是电子，2是服装，3是化妆品）");
166         int cate = Integer.parseInt(scanner.next());
167         ProductService productService=new ProductService();
168         Products products=new Products();
169         products.setPname(pname);
170         products.setPrice(price);
171         products.setCategory_id(cate);
172         int re = productService.insertData(products);
173         if(re>0){
174             System.out.println("插入成功");
175         }
176     }
177     /**
178      * 通过id 查询数据
179      * @param scanner
180      */
181     private static void findById(Scanner scanner) {
182         // TODO Auto-generated method stub
183         System.out.println("请输入要查询的pid");
184         
185         String regex="[1-9]([0-9]*)";
186         String str = scanner.nextLine();
187         if(!str.matches(regex)){
188             System.out.println("输入格式不符合");
189             return;
190         }
191         int pid = Integer.parseInt(str);
192         ProductService productService=new ProductService();
193         Products product = productService.findById(pid);
194         if(product==null){
195             System.out.println("没有数据");
196             return;
197         }
198         System.out.println(product);
199     }
200     /**
201      * 查询所有
202      */
203     private static void findAll() {
204         ProductService productService=new ProductService();
205         List<Products> list = productService.findAll();
206         if(list==null){
207             System.out.println("查询出错");
208             return; 
209         }
210         if(list.size()==0){
211             System.out.println("没有数据");
212             return;
213         }
214         for (Products products : list) {
215             System.out.println(products.getPid()+"	"+products.getPname()+"	"+products.getPrice()+"	"+products.getCategory_id());
216         }
217     }
218 }

七.javaBean类

 1 public class Products {
 2     private Integer pid;
 3     private String pname;
 4     private double price ;
 5     private int category_id;
 6     public Integer getPid() {
 7         return pid;
 8     }
 9     public void setPid(Integer pid) {
10         this.pid = pid;
11     }
12     public String getPname() {
13         return pname;
14     }
15     public void setPname(String pname) {
16         this.pname = pname;
17     }
18     public double getPrice() {
19         return price;
20     }
21     public void setPrice(double price) {
22         this.price = price;
23     }
24     public int getCategory_id() {
25         return category_id;
26     }
27     public void setCategory_id(Integer category_id) {
28         this.category_id = category_id;
29     }
30     public Products(Integer pid, String pname, double price, int category_id) {
31         super();
32         this.pid = pid;
33         this.pname = pname;
34         this.price = price;
35         this.category_id = category_id;
36     }
37     @Override
38     public String toString() {
39         return "Products [pid=" + pid + ", pname=" + pname + ", price=" + price + ", category_id=" + category_id + "]";
40     }
41     public Products() {
42         super();
43         // TODO Auto-generated constructor stub
44     }
45 
46 }