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 }