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的內容;
//創建Statement
java.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= ?";
//創建PreparedStatement
java.sql.PreparedStatement pst=conn.prepareStatement(sql);
//pst.setObject(第n個問號，資料值);
//setObect：根據問號處資料的型別，可以是setInt、setDouble、setString等
pst.setString(1, 第一個問號的值);
pst.setInt(2, 第一個問號的值);
//執行操作
pst.execute();
//最后務必關閉PreparedStatement
pst.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的區別詳解
- statement和preparestatement的區別
3. 關于騙分
(1)程式填空題如果有的話，在所難免會有初始化，都是亙古不變的，比如：
//創建conn
java.sql.Connection conn =null;
//conn獲取連接
conn=DBUtil.getConnection();
//創建Statement或Preparedstatement
java.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）ICMD介面原始碼
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;
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/245032.html
標籤：Java
上一篇：Java的多型機制原理