ZUCC BK阿码农 2021年1月5日
PS.
(1)为应试准备的博客，不重要仅代表不太考的内容，不是JDBC里不重要。
(2)下面有一些骗分小技巧
1. JDBC代码应试
(0)加载驱动程序、获取数据库连接（不重要）
(1)初始化
- 创建conn连接，一般写在try前，初始定义空。
java.sql.Connection conn =null;- 设计JDBC肯定有OOP（面向对象）的内容，比如：
- 我们的函数可以使用implement（接口）
- 也肯定会涉及@Override重写
- 但不重要
- 短学期会用
(2)进入try
- 获取conn实体的连接
conn=DBUtil.getConnection();(3)方法一：Statement（个人常用：查询语句、遍历输出）
//定义sql语句String sql="select 列名 from 表名 where 列A="+列A的内容;//创建Statementjava.sql.Statement st=conn.createStatement();//定义rs数据队列，execute执行Statement，结果存在rs里java.sql.ResultSet rs=st.executeQuery(sql);//最后务必关闭Statement以及rs数据队列st.close();rs.close();- rs一般会执行rs.next()判断是否查询到有用信息，再进行删、改
if(rs.next()){ //查询有结果，先关闭此次查询 rs.close(); st.close(); //进行其他的sql操作，比如对找到的行进行UPDATE、DELETE //新建Statement或Preparedstatement，进行下一次的sql操作}else{ //查询结果为空 rs.close(); st.close();}- rs也能通过rs.next()进行遍历，将查询出来的值一一打印或写进某处
while(rs.next()){//查询有结果，进行操作，直到最后结果操作完毕后退出 //对rs所指的内容进行操作}(4)方法二：PreparedStatement（个人常用：增加语句、删除语句、更新语句，查询语句也可以）
//定义sql语句，问号"?"是PreparedStatement的优势，下文会提及//列1是更新列，列2是判断列String sql="update 表名 set 列名1=? where 列名2= ?";//创建PreparedStatementjava.sql.PreparedStatement pst=conn.prepareStatement(sql);//pst.setObject(第n个问号，数据值);//setObect：根据问号处数据的类型，可以是setInt、setDouble、setString等pst.setString(1, 第一个问号的值);pst.setInt(2, 第一个问号的值);//执行操作pst.execute();//最后务必关闭PreparedStatementpst.close();(5)try内容结束，紧跟catch与final
//catch抓的SQLException是SQL错误，有错误会报错并打印路径（监督try的内容）catch (SQLException e) { e.printStackTrace(); try { //提交事务（不重要） conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e);} //最后的操作，关闭数据库finally { if (conn!=null) { try { //关闭数据库 conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } }}2. Statement和Preparedstatement区别
(1)区别：
- Statement用于执行静态SQL语句，在执行时，必须指定一个事先准备好的SQL语句
- PreparedStatement继承自Statement，都为接口
- PreparedStatement可以使用占位符（即问号？），是预编译的，批处理比Statement效率高
- PreparedStatement可以减少编译次数，来提高数据库性能
- 三种方法 execute、 executeQuery 和 executeUpdate 已被更改以使之不再需要参数（这些方法的 Statement 形式：'接受 SQL 语句参数的形式'，不应该用于 PreparedStatement 对象）
(2)百度百科有一句话（
不相信可以点传送门，老翁也说过好像）
- 有人主张，在JDBC应用中，如果你已经是稍有水平开发者，你就应该始终以PreparedStatement代替Statement.也就是说，在任何时候都不要使用Statement
(3)个人解读：PreparedStatement比Statement：高效（预编译、重复多次执行）、方便、代码易读（SetObject方法比写大长段的Sql字符串好）
(4)他人见解：
JDBC：深入理解PreparedStatement和Statement
Statement和PrepareStatement的区别详解
3. 关于骗分
(1)程序填空题如果有的话，在所难免会有初始化，都是亘古不变的，比如：
//创建connjava.sql.Connection conn =null;//conn获取连接conn=DBUtil.getConnection();//创建Statement或Preparedstatementjava.sql.Statement st=conn.createStatement();java.sql.PreparedStatement pst=conn.prepareStatement(sql);//Preparedstatement操作执行pst.execute();//关闭各种rs.close();//结果集关闭st.close();//Statement关闭pst.close();//Preparedstatement关闭conn.close();//连接关闭(2)个人觉得Preparedstatement方便，sql直接写问号
- Statement涉及ResultSet结果集
- Preparedstatement在后面setObject（setInt、setString、setDouble等）,简单易懂
(3)开头初始化创建和结尾关闭操作不拿分那可真是浪费了
4. 随便拉了一些源码，可以参考整体的代码环境
（1）实体类方法重写（最下面是ICMD接口代码+方法注释）
package src.cn.edu.zucc.waimai.comtrol.example;import java.sql.SQLException;import java.sql.Timestamp;import java.util.ArrayList;import java.util.List;import src.cn.edu.zucc.waimai.itf.ICMD;import src.cn.edu.zucc.waimai.model.BeanCMD;import src.cn.edu.zucc.waimai.model.BeanOrder;import src.cn.edu.zucc.waimai.model.BeanQs;import src.cn.edu.zucc.waimai.model.BeanQsbill;import src.cn.edu.zucc.waimai.model.BeanSj;import src.cn.edu.zucc.waimai.model.BeanSjFL;import src.cn.edu.zucc.waimai.model.BeanSjMJ;import src.cn.edu.zucc.waimai.model.BeanSjYHQ;import src.cn.edu.zucc.waimai.model.BeanSp;import src.cn.edu.zucc.waimai.model.BeanUser;import src.cn.edu.zucc.waimai.model.BeanUserAdd;import src.cn.edu.zucc.waimai.util.BaseException;import src.cn.edu.zucc.waimai.util.BusinessException;import src.cn.edu.zucc.waimai.util.DBUtil;import src.cn.edu.zucc.waimai.util.DbException;public class CMDManager implements ICMD { @Override public void QSJD(BeanQs qs,BeanOrder order) throws BaseException{ java.sql.Connection conn =null; try { conn=DBUtil.getConnection(); String sql="select * from order_data where order_id="+order.getOrder_id();//查找订单 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); sql="update order_data set qs_id=?,order_state=? where order_id=?"; java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setInt(1,qs.getQs_id()); pst.setString(2, "派送中"); pst.setInt(3, order.getOrder_id()); pst.execute(); pst.close(); } else { rs.close(); st.close(); throw new BusinessException("该订单不存在"); } } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public void modifyOrder(BeanOrder order,String state) throws BaseException{ if(state.equals("")) { throw new BusinessException("修改状态不能为空！"); } java.sql.Connection conn =null; try { conn=DBUtil.getConnection(); String sql="select * from order_data where order_id="+order.getOrder_id();//查找订单 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); sql="update order_data set order_state=? where order_id= ? ";//更新操作 java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setString(1, state); pst.setInt(2, order.getOrder_id()); pst.execute(); pst.close(); }else { rs.close(); st.close(); throw new BusinessException("该订单不存在"); } } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public List<BeanOrder> loadAllOrder()throws BaseException{ List<BeanOrder> result=new ArrayList<BeanOrder>(); java.sql.Connection conn =null; try { conn=DBUtil.getConnection(); String sql="select *" + " from order_data order by order_id"; java.sql.PreparedStatement pst= conn.prepareStatement(sql); java.sql.ResultSet rs=pst.executeQuery(); while(rs.next()) { BeanOrder p=new BeanOrder(); p.setOrder_id(rs.getInt(1)); p.setSj_id(rs.getInt(2)); p.setUser_id(rs.getInt(3)); p.setQs_id(rs.getInt(4)); p.setOrder_origin_money(rs.getFloat(5)); p.setOrder_final_money(rs.getFloat(6)); p.setMj_id(rs.getInt(7)); p.setYouhuiquan_id(rs.getInt(8)); p.setOrder_set_time(rs.getTimestamp(9)); p.setOrder_set_arrive_time(rs.getTimestamp(10)); p.setUser_address_id(rs.getInt(11)); p.setOrder_state(rs.getString(12)); result.add(p); } rs.close(); pst.close(); return result; } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } @Override public void modifyQS(BeanQs qs,String name,String grade) throws BaseException{ if(name.equals("")) { throw new BusinessException("骑手姓名不能为空！"); } if(grade.equals("")) { throw new BusinessException("骑手等级不能为空！"); } java.sql.Connection conn =null; try { conn=DBUtil.getConnection(); String sql="select * from qs_data where qs_id="+qs.getQs_id();//查找骑手 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); sql="update qs_data set qs_name=?,qs_grade=? where qs_id= ? ";//更新操作 java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setString(1, name); pst.setString(2, grade); pst.setInt(3, qs.getQs_id()); pst.execute(); pst.close(); }else { rs.close(); st.close(); throw new BusinessException("该骑手已不存在"); } } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public void deleteQS(BeanQs qs) throws BaseException{ java.sql.Connection conn =null; int a=1; try { conn=DBUtil.getConnection(); String sql="select * from qs_data where qs_id="+qs.getQs_id()+"qs_name="+qs.getQs_name();//检查是否存在商品 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); sql="delete from qs_data where qs_id=?";//删除操作 java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setInt(1, qs.getQs_id()); pst.execute(); pst.close(); }else { rs.close(); st.close(); throw new BusinessException("该骑手已经不存在"); } } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public void regQS(String name,String grade)throws BaseException{ if(name.equals("")) { throw new BusinessException("骑手姓名不能为空！"); } if(grade.equals("")) { throw new BusinessException("骑手等级不能为空！"); } java.sql.Connection conn=null; try { conn=DBUtil.getConnection(); String sql="insert into qs_data(qs_name,qs_join_date,qs_grade) values(?,now(),?)"; java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst=conn.prepareStatement(sql); pst.setString(1, name); pst.setString(2, grade); pst.execute(); pst.close(); } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if(conn!=null) try { conn.close(); }catch (SQLException e) { e.printStackTrace(); } } } @Override public void modifyMJ(BeanSjMJ sjmj,String top,String count,String ifmj) throws BaseException{ if(top.equals("")) { throw new BusinessException("满减金额不能为空！"); } if(count.equals("")) { throw new BusinessException("优惠金额不能为空！"); } if(ifmj.equals("")) { throw new BusinessException("是否与优惠券叠加不能为空！"); } java.sql.Connection conn =null; try { conn=DBUtil.getConnection(); String sql="select * from sj_manjian where mj_id="+sjmj.getMj_id();//查找满减 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); sql="update sj_manjian set mj_top_money=?,mj_discount_money=?,if_add_youhuiquan=? where mj_id= ? ";//更新操作 java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setFloat(1, Float.parseFloat(top)); pst.setFloat(2, Float.parseFloat(count)); pst.setInt(3,Integer.parseInt(ifmj)); pst.setInt(4,sjmj.getMj_id()); pst.execute(); pst.close(); }else { rs.close(); st.close(); throw new BusinessException("该商家分栏已经不存在该商品了"); } } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public void deleteMJ(BeanSjMJ sjmj) throws BaseException{ java.sql.Connection conn =null; try { conn=DBUtil.getConnection(); String sql="select * from sj_manjian where mj_id="+sjmj.getMj_id();//检查是否存在商品 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); sql="delete from sj_manjian where mj_id=?";//删除操作 java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setInt(1, sjmj.getMj_id()); pst.execute(); pst.close(); }else { rs.close(); st.close(); throw new BusinessException("已经不存在该满减政策"); } } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public void regMJ(BeanSj sj,String top,String count,String ifmj)throws BaseException{ if(top.equals("")) { throw new BusinessException("满减金额不能为空！"); } if(count.equals("")) { throw new BusinessException("优惠金额不能为空！"); } if(ifmj.equals("")) { throw new BusinessException("是否与优惠券叠加不能为空！"); } java.sql.Connection conn=null; try { conn=DBUtil.getConnection(); String sql="insert into sj_manjian(sj_id,mj_top_money," + "mj_discount_money,if_add_youhuiquan) values(?,?,?,?)"; java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst=conn.prepareStatement(sql); pst.setInt(1, sj.getSj_id()); pst.setFloat(2, Float.parseFloat(top)); pst.setFloat(3, Float.parseFloat(count)); pst.setInt(4, Integer.parseInt(ifmj)); pst.execute(); pst.close(); } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if(conn!=null) try { conn.close(); }catch (SQLException e) { e.printStackTrace(); } } } @Override public void modifyYHQ(BeanSjYHQ sjyhq,String youhui_money,String jidan,String days) throws BaseException{ if(youhui_money.equals("")) { throw new BusinessException("优惠金额不能为空！"); } if(jidan.equals("")) { throw new BusinessException("集单要求不能为空！"); } if(days.equals("")) { throw new BusinessException("增加天数不能为空！"); } java.sql.Connection conn =null; try { conn=DBUtil.getConnection(); String sql="select * from sj_youhuiquan where youhuiquan_id="+sjyhq.getYouhuiquan_id();//查找优惠券 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); Timestamp longend =new Timestamp(0); sql="select ADDDATE(?,interval ? day)"; java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setTimestamp(1, sjyhq.getYouhuiquan_end_time()); pst.setInt(2,Integer.parseInt(days)); rs=pst.executeQuery(); while(rs.next()) longend=rs.getTimestamp(1); pst.close(); rs.close(); sql="update sj_youhuiquan set youhui_money=?,jidan_least_count=?,youhuiquan_end_time=? where youhuiquan_id= ? ";//更新操作 pst=conn.prepareStatement(sql); pst.setFloat(1, Float.parseFloat(youhui_money)); pst.setInt(2, Integer.parseInt(jidan)); pst.setTimestamp(3, longend); pst.setInt(4, sjyhq.getYouhuiquan_id()); pst.execute(); pst.close(); }else { rs.close(); st.close(); throw new BusinessException("该商家分栏已经不存在该商品了"); } } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public void deleteYHQ(BeanSjYHQ sjyhq) throws BaseException{ java.sql.Connection conn =null; try { conn=DBUtil.getConnection(); String sql="select * from sj_youhuiquan where youhuiquan_id="+sjyhq.getYouhuiquan_id();//检查是否存在商品 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); sql="delete from sj_youhuiquan where youhuiquan_id=?";//删除操作 java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setInt(1, sjyhq.getYouhuiquan_id()); pst.execute(); pst.close(); }else { rs.close(); st.close(); throw new BusinessException("已经不存在该优惠券"); } } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public void regYHQ(BeanSj sj,String youhui_money,String jidan,String days)throws BaseException{ if(youhui_money.equals("")) { throw new BusinessException("优惠金额不能为空！"); } if(jidan.equals("")) { throw new BusinessException("集单要求不能为空！"); } if(days.equals("")) { throw new BusinessException("活动天数不能为空！"); } java.sql.Connection conn=null; try { conn=DBUtil.getConnection(); Timestamp begin=new Timestamp(0); Timestamp end=new Timestamp(0); String sql="select ADDDATE(now(),interval 0 day)"; java.sql.PreparedStatement pst=conn.prepareStatement(sql); java.sql.ResultSet rs=pst.executeQuery(); while(rs.next()) begin=rs.getTimestamp(1); pst.close(); rs.close(); sql="select ADDDATE(now(),interval ? day)"; pst=conn.prepareStatement(sql); pst.setInt(1,Integer.parseInt(days)); rs=pst.executeQuery(); while(rs.next()) end=rs.getTimestamp(1); pst.close(); rs.close(); sql="insert into sj_youhuiquan(sj_id,youhui_money," + "jidan_least_count,youhuiquan_begin_time,youhuiquan_end_time) values(?,?,?,?,?)"; pst=conn.prepareStatement(sql); pst.setInt(1, sj.getSj_id()); pst.setFloat(2, Float.parseFloat(youhui_money)); pst.setInt(3, Integer.parseInt(jidan)); pst.setTimestamp(4, begin); pst.setTimestamp(5, end); pst.execute(); pst.close(); } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if(conn!=null) try { conn.close(); }catch (SQLException e) { e.printStackTrace(); } } } @Override public void modifySP(BeanSp sp,String name,String price,String left) throws BaseException{ java.sql.Connection conn =null; try { int sp_id=sp.getSp_id(); conn=DBUtil.getConnection(); String sql="select * from sp_data where sp_id="+sp_id;//查找商品 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); sql="update sp_data set sp_name=?,sp_price=?,sp_left_count=? where sp_id= ? ";//更新操作 java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setString(1, name); pst.setFloat(2, Float.parseFloat(price)); pst.setInt(3, Integer.parseInt(left)); pst.setInt(4, sp_id); pst.execute(); pst.close(); }else { rs.close(); st.close(); throw new BusinessException("该商家分栏已经不存在该商品了"); } } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public void deleteSP(BeanSp sp) throws BaseException{ java.sql.Connection conn =null; try { int sp_id=sp.getSp_id(); int fl_id=sp.getFl_id(); conn=DBUtil.getConnection(); String sql="select * from sp_data where sp_id="+sp_id;//检查是否存在商品 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); sql="delete from sp_data where sp_id=?";//删除操作 java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setInt(1, sp_id); pst.execute(); pst.close(); sql="update sp_leibie set sp_count=sp_count-1 where leibie_id=?";// pst=conn.prepareStatement(sql); pst.setInt(1, fl_id); pst.execute(); pst.close(); }else { rs.close(); st.close(); throw new BusinessException("已经不存在该商品"); } } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public void modifySjFL(BeanSjFL sjfl,String name) throws BaseException{ java.sql.Connection conn =null; try { int sjfl_id=sjfl.getLeibie_id(); conn=DBUtil.getConnection(); String sql="select * from sp_leibie where leibie_id="+sjfl_id;//查找商家类别 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); sql="update sp_leibie set leibie_name=? where leibie_id= ? ";//更新操作 java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setString(1, name); pst.setInt(2, sjfl_id); pst.execute(); pst.close(); }else { rs.close(); st.close(); throw new BusinessException("该商家已经不存在该分栏"); } } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public void deleteSjFL(BeanSjFL sjfl) throws BaseException{ java.sql.Connection conn =null; try { int sjfl_id=sjfl.getLeibie_id(); conn=DBUtil.getConnection(); String sql="select * from sp_data where sp_belong_leibie_id="+sjfl_id;//检查是否存在商品 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); while(rs.next()) { rs.close(); st.close(); throw new BusinessException("该商家分栏中仍存在商品，不能直接删除"); } rs.close(); sql="select * from sp_leibie where leibie_id="+sjfl_id;//查找商家分栏 rs=st.executeQuery(sql); if(rs.next()&&sjfl.getLeibie_sp_count()==0) { rs.close(); st.close(); sql="delete from sp_leibie where leibie_id=?";//删除操作 java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setInt(1, sjfl_id); pst.execute(); pst.close(); }else { if(sjfl.getLeibie_sp_count()!=0) { rs.close(); st.close(); throw new BusinessException("该商家分栏中商品数不为0，请检查"); }else { rs.close(); st.close(); throw new BusinessException("已经不存在该商家"); } } // conn.commit();//结束事务 } // catch (BaseException e) {// try {// conn.rollback();// } catch (SQLException e1) {// // TODO Auto-generated catch block// e1.printStackTrace();// }// } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public void modifySj(BeanSj sj,String name,String xinji) throws BaseException{ java.sql.Connection conn =null; try { int sj_id=sj.getSj_id(); conn=DBUtil.getConnection(); String sql="select * from sj_data where sj_id="+sj_id;//查找商家 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); sql="update sj_data set sj_name=?,sj_xinji=? where sj_id= ? ";//更新操作 java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setString(1, name); pst.setInt(2, Integer.parseInt(xinji)); pst.setInt(3, sj_id); pst.execute(); pst.close(); }else { rs.close(); st.close(); throw new BusinessException("已经不存在该商家"); } } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public void deleteSj(BeanSj sj) throws BaseException { java.sql.Connection conn =null; try { int sj_id=sj.getSj_id(); conn=DBUtil.getConnection();// conn.setAutoCommit(false);//开启事务 String sql="select * from sp_leibie where sj_id="+sj_id;//检查是否存在分栏 java.sql.Statement st=conn.createStatement(); java.sql.ResultSet rs=st.executeQuery(sql); while(rs.next()) { rs.close(); st.close(); throw new BusinessException("该商家仍存在分栏，不能直接删除"); } rs.close(); sql="select * from sj_data where sj_id="+sj_id;//查找商家 rs=st.executeQuery(sql); if(rs.next()) { rs.close(); st.close(); sql="delete from sj_data where sj_id=?";//删除操作 java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setInt(1, sj_id); pst.execute(); pst.close(); }else { rs.close(); st.close(); throw new BusinessException("已经不存在该商家"); } // conn.commit();//结束事务 } // catch (BaseException e) {// try {// conn.rollback();// } catch (SQLException e1) {// // TODO Auto-generated catch block// e1.printStackTrace();// }// } catch (SQLException e) { e.printStackTrace(); try { conn.commit(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } } } } @Override public List<BeanUserAdd> loadAllYHadd(BeanUser user)throws BaseException{ List<BeanUserAdd> result=new ArrayList<BeanUserAdd>(); java.sql.Connection conn =null; try { conn=DBUtil.getConnection(); String sql="select user_id,user_province,user_city,user_area,user_address_detail,user_ad_name,user_ad_phonenum" + " from user_address where user_id=? order by user_address_id"; java.sql.PreparedStatement pst= conn.prepareStatement(sql); pst.setInt(1, user.getUser_id()); java.sql.ResultSet rs=pst.executeQuery(); while(rs.next()) { BeanUserAdd p=new BeanUserAdd(); p.setUser_id(rs.getInt(1)); p.setUser_province(rs.getString(2)); p.setUser_city(rs.getString(3)); p.setUser_area(rs.getString(4)); p.setUser_add_detail(rs.getString(5)); p.setUser_add_name(rs.getString(6)); p.setUser_add_phonenum(rs.getString(7)); result.add(p); } rs.close(); pst.close(); return result; } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } @Override public BeanUser reg(String username,int usersex, String pwd,String pwd2,String userphonenum, String usere_mail,String usercity) throws BaseException{ if(username.equals("")) { throw new BusinessException("用户名不能为空！"); } if(usersex!=0 && usersex!=1) { throw new BusinessException("性别请输入0（女性）或1（男性）！"); } if(pwd.equals("")) { throw new BusinessException("密码不能为空！"); } if(pwd2.equals("")) { throw new BusinessException("请第二次输入密码！"); } if(!(pwd.equals(pwd2))) { throw new BusinessException("两次密码不一致！"); } if(userphonenum.equals("")) { throw new BusinessException("手机号码不能为空！"); } if(usere_mail.equals("")) { throw new BusinessException("邮箱不能为空！"); } if(usercity.equals("")) { throw new BusinessException("所在城市不能为空！"); } java.sql.Connection conn=null; try { conn=DBUtil.getConnection(); String sql="select * from user_data where user_name=?"; java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setString(1,username); java.sql.ResultSet rs=pst.executeQuery(); if(rs.next())throw new BusinessException("姓名已存在！"); rs.close(); pst.close(); sql="insert into user_data(user_name,user_sex,user_pwd,user_phonenum," + "user_email,user_city,user_register_time) values(?,?,?,?,?,?,?)"; pst=conn.prepareStatement(sql); pst.setString(1, username); pst.setInt(2, usersex); pst.setString(3, pwd); pst.setString(4, userphonenum); pst.setString(5, usere_mail); pst.setString(6, usercity); pst.setTimestamp(7, new java.sql.Timestamp(System.currentTimeMillis())); pst.execute(); pst.close(); BeanUser bu=new BeanUser(); bu.setUser_name(username); bu.setUser_sex(usersex); bu.setUser_pwd(pwd); bu.setUser_phonenum(userphonenum); bu.setUser_email(usere_mail); bu.setUser_city(usercity); bu.setUser_register_time(new java.sql.Timestamp(System.currentTimeMillis())); bu.setUser_vip_end_time(null); return bu; } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if(conn!=null) try { conn.close(); }catch (SQLException e) { e.printStackTrace(); } } } @Override public List<BeanUser> loadAllYH()throws BaseException{ List<BeanUser> result=new ArrayList<BeanUser>(); java.sql.Connection conn =null; try { conn=DBUtil.getConnection(); String sql="select user_id,user_name,user_sex,user_pwd,user_phonenum,user_email,user_city,user_register_time,user_vip_end_time" + " from user_data"; java.sql.PreparedStatement pst= conn.prepareStatement(sql); java.sql.ResultSet rs=pst.executeQuery(); while(rs.next()) { BeanUser p=new BeanUser(); p.setUser_id(rs.getInt(1)); p.setUser_name(rs.getString(2)); p.setUser_sex(rs.getInt(3)); p.setUser_pwd(rs.getString(4)); p.setUser_phonenum(rs.getString(5)); p.setUser_email(rs.getString(6)); p.setUser_city(rs.getString(7)); p.setUser_register_time(rs.getTimestamp(8)); p.setUser_vip_end_time(rs.getTimestamp(9)); result.add(p); } rs.close(); pst.close(); return result; } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } @Override public List<BeanQs> loadAllQS()throws BaseException{ List<BeanQs> result=new ArrayList<BeanQs>(); java.sql.Connection conn =null; try { conn=DBUtil.getConnection(); String sql="select * from qs_data"; java.sql.PreparedStatement pst= conn.prepareStatement(sql); java.sql.ResultSet rs=pst.executeQuery(); while(rs.next()) { BeanQs p=new BeanQs(); p.setQs_id(rs.getInt(1)); p.setQs_name(rs.getString(2)); p.setQs_join_date(rs.getTimestamp(3)); p.setQs_grade(rs.getString(4)); result.add(p); } rs.close(); pst.close(); return result; } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } @Override public List<BeanQsbill> loadAllQSbill(BeanQs qs)throws BaseException{ List<BeanQsbill> result=new ArrayList<BeanQsbill>(); java.sql.Connection conn=null; try { conn=DBUtil.getConnection(); String sql="select * from qs_bill" + " where qs_id = ?"; java.sql.PreparedStatement pst= conn.prepareStatement(sql); pst.setInt(1,qs.getQs_id()); java.sql.ResultSet rs=pst.executeQuery(); while(rs.next()) { BeanQsbill p=new BeanQsbill(); p.setQs_id(rs.getInt(1)); p.setOrder_id(rs.getInt(2)); p.setQs_getmoney_time(rs.getTimestamp(3)); p.setQs_getmoney(rs.getFloat(4)); p.setSp_evaluate_qsxinji(rs.getInt(5)); result.add(p); } rs.close(); pst.close(); return result; } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } @Override public BeanCMD login(String username,String pwd)throws BaseException{ if(username.equals("")) { throw new BusinessException("用户名为空！"); } if(pwd.equals("")) { throw new BusinessException("密码不能为空！"); } java.sql.Connection conn=null; try { conn=DBUtil.getConnection(); String sql="select cmd_id,cmd_name,cmd_pwd from cmd_data where cmd_name=? and cmd_pwd=?"; java.sql.PreparedStatement pst= conn.prepareStatement(sql); pst.setString(1, username); pst.setString(2, pwd); java.sql.ResultSet rs= pst.executeQuery(); if(rs.next()) { BeanCMD bu=new BeanCMD(); bu.setCMD_id(rs.getInt(1));//获取用户编号 bu.setCMD_name(username); bu.setCMD_pwd(pwd); pst.close(); rs.close(); return bu; }else { throw new BusinessException("账号或密码错误"); } } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if(conn!=null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } @Override public List<BeanCMD> loadAll()throws BaseException{ List<BeanCMD> result=new ArrayList<BeanCMD>(); java.sql.Connection conn =null; try { conn=DBUtil.getConnection(); String sql="select * from cmd_data"; java.sql.PreparedStatement pst= conn.prepareStatement(sql); java.sql.ResultSet rs=pst.executeQuery(); while(rs.next()) { BeanCMD p=new BeanCMD(); p.setCMD_id(rs.getInt(1)); p.setCMD_name(rs.getString(2)); p.setCMD_pwd(rs.getString(3)); result.add(p); } rs.close(); pst.close(); return result; } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn!=null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } @Override public BeanCMD reg(String cmdusername,String cmdpwd,String username,String pwd)throws BaseException{ if(cmdusername.equals("")) { throw new BusinessException("管理员名为空！"); } if(cmdpwd.equals("")) { throw new BusinessException("管理员密码不能为空！"); } if(username.equals("")) { throw new BusinessException("新管理员名为空！"); } if(pwd.equals("")) { throw new BusinessException("新管理员密码不能为空！"); } java.sql.Connection conn=null; try { conn=DBUtil.getConnection(); String sql="select * from cmd_data where cmd_name=?"; java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setString(1,username); java.sql.ResultSet rs=pst.executeQuery(); if(rs.next()) { rs.close(); pst.close(); throw new BusinessException("管理员姓名已存在！"); } rs.close(); pst.close(); sql="select cmd_pwd from cmd_data where cmd_name=?"; pst=conn.prepareStatement(sql); pst.setString(1, cmdusername); rs=pst.executeQuery(); if(rs.next()) { if(!cmdpwd.equals(rs.getString(1))) { rs.close(); pst.close(); throw new BusinessException("管理员密码认证失败！"); } }else { rs.close(); pst.close(); throw new BusinessException("管理员账号不存在"); } rs.close(); pst.close(); sql="insert into cmd_data(cmd_name,cmd_pwd) values(?,?)"; pst=conn.prepareStatement(sql); pst.setString(1, username); pst.setString(2, pwd); pst.execute(); pst.close(); BeanCMD bu=new BeanCMD(); bu.setCMD_name(username); bu.setCMD_pwd(pwd); return bu; } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if(conn!=null) try { conn.close(); }catch (SQLException e) { e.printStackTrace(); } } } @Override public BeanCMD modify(String cmdusername,String cmdpwd,String username,String pwd)throws BaseException{ if(cmdusername.equals("")) { throw new BusinessException("管理员名为空！"); } if(cmdpwd.equals("")) { throw new BusinessException("管理员密码不能为空！"); } if(username.equals("")) { throw new BusinessException("新管理员名为空！"); } if(pwd.equals("")) { throw new BusinessException("新管理员密码不能为空！"); } java.sql.Connection conn=null; try { conn=DBUtil.getConnection(); String sql="select * from cmd_data where cmd_name=?"; java.sql.PreparedStatement pst=conn.prepareStatement(sql); pst.setString(1,username); java.sql.ResultSet rs=pst.executeQuery(); if(rs.next()&&(!rs.getString(2).equals(cmdusername))) { rs.close(); pst.close(); throw new BusinessException("管理员姓名已存在！"); } rs.close(); pst.close(); sql="select cmd_pwd from cmd_data where cmd_name=?"; pst=conn.prepareStatement(sql); pst.setString(1, cmdusername); rs=pst.executeQuery(); if(rs.next()) { if(!cmdpwd.equals(rs.getString(1))) { rs.close(); pst.close(); throw new BusinessException("管理员密码认证失败！"); } }else { rs.close(); pst.close(); throw new BusinessException("管理员账号不存在"); } rs.close(); pst.close(); sql="update cmd_data set cmd_name=?,cmd_pwd=? where cmd_name=?"; pst=conn.prepareStatement(sql); pst.setString(1, username); pst.setString(2, pwd); pst.setString(3, cmdusername); pst.execute(); pst.close(); BeanCMD bu=new BeanCMD(); bu.setCMD_name(username); bu.setCMD_pwd(pwd); return bu; } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if(conn!=null) try { conn.close(); }catch (SQLException e) { e.printStackTrace(); } } }}（2）接口
package src.cn.edu.zucc.waimai.itf;import java.util.List;import src.cn.edu.zucc.waimai.model.BeanCMD;import src.cn.edu.zucc.waimai.model.BeanOrder;import src.cn.edu.zucc.waimai.model.BeanQs;import src.cn.edu.zucc.waimai.model.BeanQsbill;import src.cn.edu.zucc.waimai.model.BeanSj;import src.cn.edu.zucc.waimai.model.BeanSjFL;import src.cn.edu.zucc.waimai.model.BeanSjMJ;import src.cn.edu.zucc.waimai.model.BeanSjYHQ;import src.cn.edu.zucc.waimai.model.BeanSp;import src.cn.edu.zucc.waimai.model.BeanUser;import src.cn.edu.zucc.waimai.model.BeanUserAdd;import src.cn.edu.zucc.waimai.util.BaseException;public interface ICMD { /** * 骑手接单 * 能修改姓名、等级 */ public void QSJD(BeanQs qs,BeanOrder order) throws BaseException; /** * 修改订单状态 * 能修改姓名、等级 */ public void modifyOrder(BeanOrder order,String state) throws BaseException; /** * 提取当前所有订单信息 * @return * @throws BaseException */ public List<BeanOrder> loadAllOrder()throws BaseException; /** * 修改骑手 * 能修改姓名、等级 */ public void modifyQS(BeanQs qs,String name,String grade) throws BaseException; /** * 删除骑手 * 需要骑手没有接过单 */ public void deleteQS(BeanQs qs) throws BaseException; /** * 新增骑手 * 包含姓名，日期，等级 */ public void regQS(String name,String grade)throws BaseException; /** * 修改商家优惠券 * 能修改优惠金额、集单要求、增加天数 */ public void modifyMJ(BeanSjMJ sjmj,String top,String count,String ifmj) throws BaseException; /** * 删除商家优惠券 * */ public void deleteMJ(BeanSjMJ sjmj) throws BaseException; /** * 新增商家优惠券 * 包含优惠金额，集单要求，活动天数 */ public void regMJ(BeanSj sj,String top,String count,String ifmj)throws BaseException; /** * 修改商家优惠券 * 能修改优惠金额、集单要求、增加天数 */ public void modifyYHQ(BeanSjYHQ sjyhq,String youhui_money,String jidan,String days) throws BaseException; /** * 删除商家优惠券 * */ public void deleteYHQ(BeanSjYHQ sjyhq) throws BaseException; /** * 新增商家优惠券 * 包含优惠金额，集单要求，活动天数 */ public void regYHQ(BeanSj sj,String youhui_money,String jidan,String days)throws BaseException; /** * 修改商品 * 能修改商品名、商品价格、商品余量 */ public void modifySP(BeanSp sp,String name,String price,String left) throws BaseException; /** * 删除商品 * */ public void deleteSP(BeanSp sp) throws BaseException; /** * 修改商家 * 仅能修改分栏名 */ public void modifySjFL(BeanSjFL sjfl,String name) throws BaseException; /** * 删除商家分栏 * 需要分栏内无商品 */ public void deleteSjFL(BeanSjFL sjfl) throws BaseException; /** * 修改商家 * 仅能修改商家名和星级 */ public void modifySj(BeanSj sj,String name,String xinji) throws BaseException; /** * 删除商家 * 需要无分栏 */ public void deleteSj(BeanSj sj) throws BaseException; /** * 提取当前所有用户地址信息 * @return * @throws BaseException */ public List<BeanUserAdd> loadAllYHadd(BeanUser user)throws BaseException; /** * 注册： * 要求用户名不能重复，不能为空 * 两次输入的密码必须一致，密码不能为空 * 如果注册失败，则抛出异常 * @param userid * @param pwd 密码 * @param pwd2 重复输入的密码 * @return * @throws BaseException */ public BeanUser reg(String username,int usersex, String pwd,String pwd2,String userphonenum, String usere_mail,String usercity) throws BaseException; /** * 提取当前所有用户信息 * @return * @throws BaseException */ public List<BeanUser> loadAllYH()throws BaseException; /** * 提取当前所有骑手信息 * @return * @throws BaseException */ public List<BeanQs> loadAllQS()throws BaseException; /** * 提取当前所有骑手账单信息 * @return * @throws BaseException */ public List<BeanQsbill> loadAllQSbill(BeanQs qs)throws BaseException; /** * 登陆 * 1、如果用户不存在或者密码错误，抛出一个异常 * 2、如果认证成功，则返回当前用户信息 * @param userid * @param pwd * @return * @throws BaseException */ public BeanCMD login(String username,String pwd)throws BaseException; /** * 提取当前所有管理员信息 * @return * @throws BaseException */ public List<BeanCMD> loadAll()throws BaseException; /** * 新增 * 1、如果用户不存在或者密码错误，抛出一个异常 * 2、如果认证成功，则返回当前用户信息 * @param userid * @param pwd * @return * @throws BaseException */ public BeanCMD reg(String cmdusername,String cmdpwd,String username,String pwd)throws BaseException; /** * 修改 * 1、如果用户不存在或者密码错误，抛出一个异常 * 2、如果认证成功，则返回当前用户信息 * @param userid * @param pwd * @return * @throws BaseException */ public BeanCMD modify(String cmdusername,String cmdpwd,String username,String pwd)throws BaseException;}原文转载：http://www.shaoqun.com/a/505944.html
wangwei：https://www.ikjzd.com/w/1744
海淘贝：https://www.ikjzd.com/w/1726
ZUCC BK阿码农 2021年1月5日PS.(1)为应试准备的博客，不重要仅代表不太考的内容，不是JDBC里不重要。(2)下面有一些骗分小技巧1.JDBC代码应试(0)加载驱动程序、获取数据库连接（不重要）(1)初始化创建conn连接，一般写在try前，初始定义空。java.sql.Connectionconn=null;设计JDBC肯定有OOP（面向对象）的内容，比如：我们的函数可以使用imp
五洲会：五洲会
ad公司：ad公司
深圳最有趣的节日是？：深圳最有趣的节日是？
暴击！元旦过后邮政国际小包资费要全面上调？：暴击！元旦过后邮政国际小包资费要全面上调？
疫情之下如何逆风翻盘销量翻倍_制定广告策略必备的三大妙招_快速筛选高绩效关键词：疫情之下如何逆风翻盘销量翻倍_制定广告策略必备的三大妙招_快速筛选高绩效关键词
没有评论:
发表评论