时间：2021-07-01 10:21:17 帮助过：9人阅读
订单表建表语句：
1 create table TEST.SHOPORDER 2 ( 3 scid VARCHAR2(10), 4 scuser VARCHAR2(20), 5 scgid VARCHAR2(15), 6 scnum NUMBER, 7 scmoney NUMBER, 8 sctime VARCHAR2(20), 9 sczt VARCHAR2(10) 10 ) 11 tablespace SYSTEM 12 pctfree 10 13 pctused 40 14 initrans 1 15 maxtrans 255 16 storage 17 ( 18 initial 64K 19 next 1M 20 minextents 1 21 maxextents unlimited 22 ); 23 comment on column TEST.SHOPORDER.scid 24 is ‘订单号‘; 25 comment on column TEST.SHOPORDER.scuser 26 is ‘用户名‘; 27 comment on column TEST.SHOPORDER.scgid 28 is ‘商品ID‘; 29 comment on column TEST.SHOPORDER.scnum 30 is ‘商品数量‘; 31 comment on column TEST.SHOPORDER.scmoney 32 is ‘商品总价‘; 33 comment on column TEST.SHOPORDER.sctime 34 is ‘交易时间‘; 35 comment on column TEST.SHOPORDER.sczt 36 is ‘订单状态‘;
用户表建表语句：
1 create table TEST.USER_TABLE 2 ( 3 userid NUMBER not null, 4 uname VARCHAR2(20), 5 upassword VARCHAR2(20), 6 unickname VARCHAR2(20), 7 uemail VARCHAR2(50), 8 utime VARCHAR2(30) 9 ) 10 tablespace USERS 11 pctfree 10 12 initrans 1 13 maxtrans 255 14 storage 15 ( 16 initial 64K 17 next 1M 18 minextents 1 19 maxextents unlimited 20 ); 21 comment on column TEST.USER_TABLE.userid 22 is ‘用户id‘; 23 comment on column TEST.USER_TABLE.uname 24 is ‘用户名‘; 25 comment on column TEST.USER_TABLE.upassword 26 is ‘用户密码‘; 27 comment on column TEST.USER_TABLE.unickname 28 is ‘用户昵称‘; 29 comment on column TEST.USER_TABLE.uemail 30 is ‘用户邮箱‘; 31 comment on column TEST.USER_TABLE.utime 32 is ‘注册时间‘;
日志表建表语句：
1 create table TEST.SHOPLOG 2 ( 3 pname VARCHAR2(30), 4 pugroup VARCHAR2(30), 5 puptime VARCHAR2(30), 6 pip VARCHAR2(30), 7 plog VARCHAR2(100) 8 ) 9 tablespace SYSTEM 10 pctfree 10 11 pctused 40 12 initrans 1 13 maxtrans 255 14 storage 15 ( 16 initial 64K 17 next 1M 18 minextents 1 19 maxextents unlimited 20 ); 21 comment on column TEST.SHOPLOG.pname 22 is ‘用户名‘; 23 comment on column TEST.SHOPLOG.pugroup 24 is ‘用户组‘; 25 comment on column TEST.SHOPLOG.puptime 26 is ‘登陆时间‘; 27 comment on column TEST.SHOPLOG.pip 28 is ‘IP‘; 29 comment on column TEST.SHOPLOG.plog 30 is ‘操作‘;
数据库链接驱动类：
1 package com.hanqi.util; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.sql.Statement; 8 9 /** 10 * 数据库驱动连接类 11 * @author ZBK 12 */ 13 public class DBHelper { 14 /** 15 * 数据库用户名 16 */ 17 public static final String USERNAME = "test"; 18 /** 19 * 数据库密码 20 */ 21 public static final String PASSWORD = "test"; 22 /** 23 * 数据库驱动类 24 */ 25 public static final String DRIVER = "oracle.jdbc.OracleDriver"; 26 /** 27 * 数据库地址URL 28 */ 29 public static final String URL = "jdbc:oracle:thin:@localhost:1521:xe"; 30 31 /** 32 * 获取数据库连接 33 * @return 34 */ 35 public static Connection getConnection() { 36 Connection conn = null; 37 try { 38 Class.forName(DRIVER); 39 conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); 40 } catch (ClassNotFoundException e) { 41 e.printStackTrace(); 42 } catch (SQLException e) { 43 e.printStackTrace(); 44 } 45 return conn; 46 } 47 48 49 /** 50 * 释放资源 51 * @param conn 数据库连接对象 52 * @param sm Statement对象 53 * @param rs ResultSet结果集对象 54 */ 55 public static void destroy(Connection conn, Statement sm, ResultSet rs) { 56 if (conn != null) { 57 try { 58 conn.close(); 59 } catch (SQLException e) { 60 e.printStackTrace(); 61 } 62 conn = null; 63 } 64 if (sm != null) { 65 try { 66 sm.close(); 67 } catch (SQLException e) { 68 e.printStackTrace(); 69 } 70 sm = null; 71 } 72 if (rs != null) { 73 try { 74 rs.close(); 75 } catch (SQLException e) { 76 e.printStackTrace(); 77 } 78 rs = null; 79 } 80 } 81 82 /** 83 * 验证前台传入的参数是否为空 84 * @param args 85 * @return 86 */ 87 public static boolean checkParam(String... args) { 88 for (String s : args) { 89 if (s == null || s.trim().length() < 1) { 90 return false; 91 } 92 } 93 return true; 94 } 95 }
数据库操作方法类：
1 package com.hanqi.dal; 2 3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.util.ArrayList; 8 import java.util.Date; 9 import java.util.List; 10 11 import com.hanqi.model.Goods; 12 import com.hanqi.model.Log; 13 import com.hanqi.model.Order; 14 import com.hanqi.model.TradingStatusFile; 15 import com.hanqi.model.User; 16 import com.hanqi.util.DBHelper; 17 18 public class MethodDal { 19 private Connection conn; 20 private PreparedStatement ps; 21 private ResultSet rs; 22 23 private static int scnum=1000; 24 //初始化链接 25 public void init(String sql) { 26 conn = DBHelper.getConnection(); 27 try { 28 ps = conn.prepareStatement(sql); 29 } catch (SQLException e) { 30 e.printStackTrace(); 31 } 32 } 33 //释放资源 34 public void close(){ 35 DBHelper.destroy(conn, ps, rs); 36 } 37 //判断传入的参数有没有空的方法，只要有空的就返回false 38 public boolean checkParam(String... args){//这样传参数代表参数个数不确定，传几个都可以 39 for(String s : args){ 40 if("".equals(s)||s==null){ 41 return false; 42 } 43 } 44 return true; 45 } 46 /** 47 * 返回所有商品 48 * @return 49 */ 50 public List<Goods> getAllGoods(){ 51 String sql = "select * from GOODS_TABLE g"; 52 init(sql); 53 List<Goods> list = new ArrayList<Goods>(); 54 55 try { 56 rs = ps.executeQuery(); 57 while (rs.next()) { 58 Goods good=new Goods(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getInt(6),rs.getString(7)); 59 list.add(good); 60 } 61 } catch (SQLException e) { 62 e.printStackTrace(); 63 } 64 return list; 65 } 66 /** 67 * 增加一条商品信息 68 * @param g 69 * @return 70 */ 71 public int insertGoods(Goods g) { 72 String sql = "insert into GOODS_TABLE values(tablexulie.nextval,?,?,?,?,?,?)"; 73 74 init(sql); 75 int a = -1; 76 try { 77 ps.setString(1, g.getGname()); 78 ps.setString(2, g.getGdetails()); 79 ps.setString(3, g.getGpicture()); 80 ps.setInt(4, g.getGprice()); 81 ps.setInt(5, g.getGleixing()); 82 ps.setString(6, g.getGpinpai()); 83 a = ps.executeUpdate(); 84 } catch (SQLException e) { 85 e.printStackTrace(); 86 } 87 return a; 88 } 89 /** 90 * 删除商品信息 91 */ 92 public int deleteGoods(int gid) { 93 String sql = "delete from GOODS_TABLE g where g.gid=? "; 94 95 init(sql); 96 int a = -1; 97 try { 98 ps.setInt(1, gid); 99 a = ps.executeUpdate(); 100 } catch (SQLException e) { 101 e.printStackTrace(); 102 } 103 return a; 104 } 105 //修改商品信息 106 public int UpdateGood(Goods g) { 107 String sql = "update GOODS_TABLE g set g.gname=?,g.gdetails=?,g.gpicture=?,g.gprice=?,g.gleixing=?,g.gpinpai=? where g.gid=?"; 108 init(sql); 109 int a = -1; 110 try { 111 ps.setString(1, g.getGname()); 112 ps.setString(2, g.getGdetails()); 113 ps.setString(3, g.getGpicture()); 114 ps.setInt(4, g.getGprice()); 115 ps.setInt(5, g.getGleixing()); 116 ps.setString(6, g.getGpinpai()); 117 ps.setInt(7, g.getGid()); 118 a = ps.executeUpdate(); 119 } catch (SQLException e) { 120 e.printStackTrace(); 121 } 122 return a; 123 } 124 public int UpdateGoodNP(Goods g) { 125 String sql = "update GOODS_TABLE g set g.gname=?,g.gdetails=?,g.gprice=?,g.gleixing=?,g.gpinpai=? where g.gid=?"; 126 init(sql); 127 int a = -1; 128 try { 129 ps.setString(1, g.getGname()); 130 ps.setString(2, g.getGdetails()); 131 ps.setInt(3, g.getGprice()); 132 ps.setInt(4, g.getGleixing()); 133 ps.setString(5, g.getGpinpai()); 134 ps.setInt(6, g.getGid()); 135 136 a = ps.executeUpdate(); 137 } catch (SQLException e) { 138 e.printStackTrace(); 139 } 140 return a; 141 } 142 143 //获取数量 144 public int getGoodsSum(String scuser) { 145 String sql = "select s.scnum from SHOPPINGCAR s where s.scuser=?"; 146 int sum=0; 147 init(sql); 148 149 try { 150 ps.setString(1, scuser); 151 rs = ps.executeQuery(); 152 153 while (rs.next()) { 154 sum+=rs.getInt("scnum"); 155 } 156 } catch (SQLException e) { 157 e.printStackTrace(); 158 } 159 return sum; 160 } 161 162 public Goods getGoods(String gid) { 163 String sql = "select * from GOODS_TABLE g where g.gid="+gid; 164 init(sql); 165 Goods good=new Goods(); 166 try { 167 rs = ps.executeQuery(); 168 while (rs.next()) { 169 good=new Goods(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getInt(6),rs.getString(7)); 170 } 171 } catch (SQLException e) { 172 e.printStackTrace(); 173 } 174 return good; 175 } 176 /** 177 * 增加日志记录 178 * @param g 179 * @return 180 */ 181 public int insertLog(Log l) { 182 String sql = "insert into ShopLog values(?,?,?,?,?)"; 183 184 init(sql); 185 int a = -1; 186 try { 187 ps.setString(1, l.getPname()); 188 ps.setString(2, l.getPugroup()); 189 ps.setString(3, l.getPuptime()); 190 ps.setString(4, l.getPip()); 191 ps.setString(5, l.getPlog()); 192 a = ps.executeUpdate(); 193 } catch (SQLException e) { 194 e.printStackTrace(); 195 } 196 return a; 197 } 198 /** 199 * 返回所有日志 200 * @return 201 */ 202 public List<Log> getAllLogs(){ 203 String sql = "select * from ShopLog s order by s.puptime desc"; 204 init(sql); 205 List<Log> list = new ArrayList<Log>(); 206 207 try { 208 rs = ps.executeQuery(); 209 while (rs.next()) { 210 Log l=new Log(); 211 l.setPname(rs.getString(1)); 212 l.setPugroup(rs.getString(2)); 213 l.setPuptime(rs.getString(3)); 214 l.setPip(rs.getString(4)); 215 l.setPlog(rs.getString(5)); 216 217 list.add(l); 218 } 219 } catch (SQLException e) { 220 e.printStackTrace(); 221 } 222 return list; 223 } 224 /** 225 * 返回购物车数据 226 * @return 227 */ 228 public List<Order> getAllOrder(String username){ 229 String sql = "select t.* from SHOPPINGCAR t where t.scuser=?"; 230 init(sql); 231 List<Order> list = new ArrayList<Order>(); 232 233 try { 234 ps.setString(1, username); 235 rs = ps.executeQuery(); 236 while (rs.next()) { 237 Order l=new Order(); 238 239 l.setScid(rs.getString(1)); 240 l.setScuser(rs.getString(4)); 241 l.setScgid(rs.getString(5)); 242 l.setScnum(rs.getInt(3)); 243 l.setSctime((new Date()).toLocaleString()); 244 245 list.add(l); 246 } 247 } catch (SQLException e) { 248 e.printStackTrace(); 249 } 250 return list; 251 } 252 /** 253 * 删除购物车已经提交的信息 254 */ 255 public int deleteSCar(String username) { 256 String sql = "delete from SHOPPINGCAR g where g.scuser=? "; 257 258 init(sql); 259 int a = -1; 260 try { 261 ps.setString(1, username);