Java+MySQL笔记

  • 判断map中是否已存在key(啊,我太傻了,因为我做的是Java+MySQL,我直接SQL语句里加group by不就好了吗,这样就不会有重复的商品名称了,不用判断是否已存在,不过还是收获了一个用map的方法,后来我去问了我同学她怎么判断key是否已存在的,她是直接map.get(key)看是否有内容,有内容就存在了,也挺好的)
 1 public Map<String,Double> getProductTotalFee(String productName) throws BaseException{
 2         Map<String,Double> map=new HashMap<String,Double>();
 3         Connection conn=null;
 4         try {
 5             conn=DBUtil.getConnection();
 6             String sql="select ProductName,Quantity,UnitPrice from viewOrderDetail where productName like '%"+productName+"%'";
 7             java.sql.Statement st=conn.createStatement();
 8             java.sql.ResultSet rs=st.executeQuery(sql);
 9             int count=0;
10             while(rs.next()) {
11                 count++;
12                 boolean flag=map.containsKey(rs.getString(1));
13                 if(flag){
14                     map.put(rs.getString(1),map.get(rs.getString(1))+rs.getInt(2)*rs.getDouble(3));
15                 }else {
16                     map.put(rs.getString(1),rs.getInt(2)*rs.getDouble(3));
17                 }
18             }
19             if(count==0)
20                 throw new BaseException("商品不存在");
21         }catch(SQLException e) {
22             e.printStackTrace();
23             throw new DbException(e);
24         }finally {
25             if(conn!=null)
26                 try {
27                     conn.close();
28                 } catch (SQLException e) {
29                     // TODO Auto-generated catch block
30                     e.printStackTrace();
31                 }
32         }
33         return map;
34     } 
  •  在主码自增的数据库中插入一条数据后,返回该记录主码的值
 1 @Override
 2     public int settle(BeanShopkeeper shop,BeanCustomer cust,int coupid, int addressid,float originprice,float finalprice,Date requiretime) throws BaseException{
 3         Connection conn=null;
 4         int keyID=0;
 5         SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
 6         String time = df.format(System.currentTimeMillis());
 7         Timestamp createTime = Timestamp.valueOf(time);
 8         try {
 9             conn=DBUtil.getConnection();
10             String sql="insert into productorder(shop_id,cust_id,coup_id,add_id,originprice,finalprice,starttime,requiretime,orderstate) values(?,?,?,?,?,?,?,?,?)";
11             java.sql.PreparedStatement pst=conn.prepareStatement(sql,java.sql.PreparedStatement.RETURN_GENERATED_KEYS);
12             pst.setInt(1,shop.getShop_id());
13             pst.setInt(2,cust.getCust_id());
14             pst.setInt(3,coupid);
15             pst.setInt(4,addressid);
16             pst.setFloat(5,originprice);
17             pst.setFloat(6,finalprice);
18             pst.setTimestamp(7,createTime);
19             pst.setTimestamp(8,new java.sql.Timestamp(requiretime.getTime()));
20             pst.setString(9,"等待骑手接单");
21             pst.executeUpdate();
22             ResultSet rs=pst.getGeneratedKeys();
23             while(rs.next()) {
24                 keyID=rs.getInt(1);
25             }
26             pst.close();
27         }catch(SQLException e) {
28             e.printStackTrace();
29         }finally {
30             if(conn!=null) {
31                 try {
32                     conn.close();
33                 } catch (SQLException e) {
34                     // TODO Auto-generated catch block
35                     e.printStackTrace();
36                 }
37             }
38         }
39         return keyID;
40     }
  •  建表时,实现主码自增的SQL语句
 1 /*==============================================================*/
 2 /* Table: address                                               */
 3 /*==============================================================*/
 4 create table address
 5 (
 6    add_id               int not null AUTO_INCREMENT,
 7    cust_id              int,
 8    province             varchar(20) not null,
 9    city                 varchar(20) not null,
10    area                 varchar(20) not null,
11    location             varchar(20) not null,
12    contacts             varchar(20) not null,
13    phonenumber          varchar(20) not null,
14    primary key (add_id)
15 );
  •  MySQL中时间增加几个月,几年等。
 1 set date = now();
 2 select date_add(date, interval ? day);   
 3 select date_add(date, interval ? hour);  
 4 select date_add(date, interval ? minute);
 5 select date_add(date, interval ? second);
 6 select date_add(date, interval ? microsecond);
 7 select date_add(date, interval ? week); 
 8 select date_add(date, interval ? month);
 9 select date_add(date, interval ? quarter);
10 select date_add(date, interval ? year);
 1 public void VIPRegister(BeanCustomer customer,int length) throws BaseException{
 2         Connection conn=null;
 3         SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
 4         String time = df.format(System.currentTimeMillis());
 5         Timestamp date = Timestamp.valueOf(time);
 6         try {
 7             conn=DBUtil.getConnection();
 8             String sql="select ifVIP from customer where cust_id=?";
 9             java.sql.PreparedStatement pst=conn.prepareStatement(sql);
10             pst.setInt(1,customer.getCust_id());
11             pst.execute();
12             ResultSet rs=pst.getResultSet();
13             String origininfo=null;
14             while(rs.next()) {
15                 origininfo=rs.getString(1);
16             }
17             rs.close();
18             pst.close();
19             if(origininfo.equals("否")) {
20                 sql="update customer set ifVIP=? where cust_id=?";
21                 pst=conn.prepareStatement(sql);
22                 pst.setString(1,"是");
23                 pst.setInt(2,customer.getCust_id());
24                 pst.execute();
25                 pst.close();
26                 sql="update customer set VIPdeadline=date_add(?, interval ? month) where cust_id=?";
27                 pst=conn.prepareStatement(sql);
28                 pst.setTimestamp(1,date);
29                 pst.setInt(2,length);
30                 pst.setInt(3,customer.getCust_id());
31                 pst.execute();
32                 pst.close();
33             }else if (origininfo.equals("是")) {
34                 sql="select VIPdeadline from customer where cust_id=?";
35                 pst=conn.prepareStatement(sql);
36                 pst.setInt(1,customer.getCust_id());
37                 pst.execute();
38                 rs=pst.getResultSet();
39                 Date ddddd=null;
40                 while(rs.next()) {
41                     ddddd=rs.getDate(1);
42                 }
43                 rs.close();
44                 pst.close();
45                 sql="update customer set VIPdeadline=date_add(?, interval ? month) where cust_id=?";
46                 pst=conn.prepareStatement(sql);
47                 pst.setDate(1,new java.sql.Date(ddddd.getTime()));
48                 pst.setInt(2,length);
49                 pst.setInt(3,customer.getCust_id());
50                 pst.execute();
51                 pst.close();
52             }
53             
54         }catch(SQLException e) {
55             e.printStackTrace();
56         }finally {
57             if(conn!=null) {
58                 try {
59                     conn.close();
60                 } catch (SQLException e) {
61                     // TODO Auto-generated catch block
62                     e.printStackTrace();
63                 }
64             }
65         }
66 }

 

posted @ 2020-07-13 20:11  无機盐  阅读(136)  评论(0编辑  收藏  举报