?? oracleorderdao.java
字號:
package com.lmh.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.lmh.dao.db.DBTool;
import com.lmh.dao.inf.OrderDAO;
import com.lmh.dao.vo.OrderBean;
public class OracleOrderDAO implements OrderDAO {
private Connection conn = null;
/**
* 獲取訂單編號.
*
* @return 訂單編號.
*/
public long getOrderId() {
conn = DBTool.getConn();
long num = 0;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "select SEQ_T_ORDER.NEXTVAL as orderId from dual";
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
num = rs.getLong("orderId");
}
return num;
} catch (SQLException e) {
e.printStackTrace();
return num;
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
if (ps != null)
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 獲取訂單總數.
*
* @param nid
* 訂單編號 (-1:表無此條件).
* @param scuser
* 下單人(精確查) (null:表無此條件).
* @param sstatus
* 審核狀態 (null:表無此條件).
* @param sauser
* 審核人(精確查) (null:表無此條件).
* @param smcname
* 商品名(模糊查) (null:表無此條件).
* @return 訂單總數.
*/
public int getCount(long nid, String scuser, String sstatus, String sauser,
String smcname) {
conn = DBTool.getConn();
PreparedStatement ps = null;
ResultSet rs = null;
int num = 0;
StringBuffer sql = new StringBuffer();
sql.append("select count(*)as countAll from t_order where 1=1");
if (nid != -1) {
sql.append(" and nid = " + nid);
}
if (scuser != null && !scuser.equals("")) {
sql.append(" and scuser like '%" + scuser + "%'");
}
if (smcname != null && !smcname.equals("")) {
sql
.append(" and nid in (select norderid from t_order_item where smcname like '%"
+ smcname + "%')");
}
if (sstatus != null && !sstatus.equals("")) {
sql.append(" and sstatus like '%" + sstatus + "%'");
}
if (sauser != null && !sauser.equals("")) {
sql.append(" and sauser like '%" + sauser + "%'");
}
try {
ps = conn.prepareStatement(sql.toString());
rs = ps.executeQuery();
while (rs.next()) {
num = rs.getInt("countAll");
}
return num;
} catch (SQLException e) {
e.printStackTrace();
return num;
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
if (ps != null)
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 獲取訂單總數.
*
* @return 訂單總數.
*/
public int getCount() {
return getCount(-1, null, null, null, null);
}
/**
* 添加訂單.
*
* @param orderBean
* 訂單對象.
* @return 添加是否成功.
*/
public boolean insertOrder(OrderBean orderBean) {
conn = DBTool.getConn();
PreparedStatement ps = null;
StringBuffer sql = new StringBuffer("insert into t_order ");
sql = sql.append("( nid,scuser, dgdate,spaytype,ssendtype,");
sql = sql.append("nmctypesize,nmcsize,ntotalprice,sstatus,");
sql = sql.append("smsg,sauser, ssname, ssaddress,");
sql = sql.append("sscode, ssphone,ssemail)");
sql = sql.append("values");
sql = sql.append("( SEQ_T_ORDER.NEXTVAL,'");
sql = sql.append(orderBean.getScuser() + "',");
if (orderBean.getDgdate() != null
&& orderBean.getDgdate().length() != 0) {
sql = sql.append("to_date('" + orderBean.getDgdate()
+ "','yyyy-mm-dd'),'");
} else {
sql = sql.append("sysdate,'");
}
sql = sql.append(orderBean.getSpaytype() + "','"
+ orderBean.getSsendtype() + "',");
sql = sql.append(orderBean.getNmctypesize() + ","
+ orderBean.getNmcsize() + ",");
sql = sql.append(orderBean.getNtotalprice() + ",'"
+ orderBean.getSstatus() + "','");
sql = sql.append(orderBean.getSmsg() + "','" + orderBean.getSauser()
+ "','");
sql = sql.append(orderBean.getSsname() + "','");
sql = sql.append(orderBean.getSsaddress() + "','"
+ orderBean.getSscode() + "','");
sql = sql.append(orderBean.getSsphone() + "','"
+ orderBean.getSsemail() + "')");
try {
ps = conn.prepareStatement(sql.toString());
ps.executeUpdate();
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
if (ps != null)
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 修改訂單.
*
* @param orderBean
* 訂單對象.
* @return 添加是否成功.
*/
public boolean updateOrder(OrderBean orderBean) {
conn = DBTool.getConn();
PreparedStatement ps = null;
StringBuffer updateStr = new StringBuffer(" Update t_order set ");
updateStr.append(" nid = " + orderBean.getNid());
if (orderBean.getDgdate() != null) {
updateStr.append(",dgdate = sysdate ");
}
if (orderBean.getSsname() != null) {
updateStr.append(",ssname = '" + orderBean.getSsname() + "'");
}
if (orderBean.getSsaddress() != null) {
updateStr.append(",ssaddress = '" + orderBean.getSsaddress() + "'");
}
if (orderBean.getSscode() != null) {
updateStr.append(",sscode = '" + orderBean.getSscode() + "'");
}
if (orderBean.getSsphone() != null) {
updateStr.append(",ssphone = '" + orderBean.getSsphone() + "'");
}
if (orderBean.getSsemail() != null) {
updateStr.append(",ssemail = '" + orderBean.getSsemail() + "'");
}
if (orderBean.getSsendtype() != null) {
updateStr.append(",ssendtype = '" + orderBean.getSsendtype() + "'");
}
if (orderBean.getSpaytype() != null) {
updateStr.append(",spaytype = '" + orderBean.getSpaytype() + "'");
}
updateStr.append(" where nid= " + orderBean.getNid());
try {
ps = conn.prepareStatement(updateStr.toString());
ps.executeUpdate();
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
if (ps != null)
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 訂單審核.
*
* @param orderBean
* 訂單對象.
*
* @return 操作是否成功.
*/
public boolean auditingOrder(OrderBean orderBean) {
conn = DBTool.getConn();
PreparedStatement ps = null;
StringBuffer updateStr = new StringBuffer(" Update t_order set ");
updateStr.append(" nid = " + orderBean.getNid());
if (orderBean.getSstatus() != null) {
updateStr.append(",sstatus = '" + orderBean.getSstatus() + "'");
}
if (orderBean.getSmsg() != null) {
updateStr.append(" ,smsg = '" + orderBean.getSmsg() + "'");
}
if (orderBean.getSauser() != null) {
updateStr.append(" ,sauser = '" + orderBean.getSauser() + "'");
}
if (orderBean.getSstatus() != null
&& !orderBean.getSstatus().equals("1")) {
updateStr.append(" ,dadate = sysdate ");
}
updateStr.append(" where nid= " + orderBean.getNid());
try {
ps = conn.prepareStatement(updateStr.toString());
ps.executeUpdate();
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
if (ps != null)
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 獲取所有訂單.
*
* @param nid
* 訂單編號 (-1:表無此條件).
* @param scuser
* 下單人(精確查) (null:表無此條件).
* @param sstatus
* 審核狀態 (null:表無此條件).
* @param sauser
* 審核人(精確查) (null:表無此條件).
* @param smcname
* 商品名(模糊查) (null:表無此條件).
* @param pastart
* 記錄 開始行 (-1:表無此條件).
* @param paend
* 記錄 結束行 (-1:表無此條件).
* @param startDate
* (小值)下訂日期 (null:表無此條件).
* @param endDate
* (大值)下訂日期 (null:表無此條件).
*
* @return 訂單集合.
*/
public List searchOrder(long nid, String scuser, String sstatus,
String sauser, String smcname, String startDate, String endDate,
int pastart, int paend) {
conn = DBTool.getConn();
List<OrderBean> obList = new ArrayList<OrderBean>();
PreparedStatement ps = null;
ResultSet rs = null;
StringBuffer allStr = new StringBuffer();
allStr.append("select * from ");
allStr.append("(");
allStr.append("select rownum as myRow,nid,scuser,");
allStr.append("to_char(dgdate,'yyyy-mm-dd') as dgdate,");
allStr.append("spaytype,ssendtype,nmctypesize,");
allStr.append("nmcsize,ntotalprice,sstatus,smsg,sauser,");
allStr.append("to_char(dadate,'yyyy-mm-dd') as dadate,");
allStr.append(" ssname, ssaddress, sscode, ssphone,ssemail ");
allStr.append(" from t_order ");
allStr.append(" where 1=1 ");
if (nid != -1) {
allStr.append(" and nid = " + nid);
}
if (scuser != null && !scuser.equals("")) {
allStr.append(" and scuser ='" + scuser+"'");
}
if (smcname != null && !smcname.equals("")) {
allStr
.append(" and nid in (select norderid from t_order_item where smcname like '%"
+ smcname + "%')");
}
if (sstatus != null && !sstatus.equals("")) {
allStr.append(" and sstatus =" + sstatus);
}
if (sauser != null && !sauser.equals("")) {
allStr.append(" and sauser ='" + sauser+"'");
}
if (startDate != null && !startDate.equals("")) {
allStr.append(" and dgdate >=to_date('" + startDate
+ "','yyyy-mm-dd')");
}
if (endDate != null && !endDate.equals("")) {
allStr.append(" and dgdate <=to_date('" + endDate
+ "','yyyy-mm-dd')");
}
if (paend != -1) {
allStr.append(" and rownum <=" + paend);
}
allStr.append(" )");
if (pastart != -1) {
allStr.append(" where myRow >= " + pastart);
}
try {
ps = conn.prepareStatement(allStr.toString());
rs = ps.executeQuery();
while (rs.next()) {
OrderBean ob = new OrderBean();
ob.setNid(rs.getInt("nid"));
ob.setScuser(rs.getString("scuser"));
ob.setDgdate(rs.getString("dgdate"));
ob.setSpaytype(rs.getString("spaytype"));
ob.setSsendtype(rs.getString("ssendtype"));
ob.setNmcsize(rs.getInt("nmcsize"));
ob.setNmctypesize(rs.getInt("nmctypesize"));
ob.setNtotalprice(rs.getDouble("ntotalprice"));
ob.setSstatus(rs.getString("sstatus"));
ob.setSmsg(rs.getString("smsg"));
ob.setSauser(rs.getString("sauser"));
ob.setDadate(rs.getString("dadate"));
ob.setSsname(rs.getString("ssname"));
ob.setSsaddress(rs.getString("ssaddress"));
ob.setSscode(rs.getString("sscode"));
ob.setSsphone(rs.getString("ssphone"));
ob.setSsemail(rs.getString("ssemail"));
obList.add(ob);
}
return obList;
} catch (SQLException e) {
e.printStackTrace();
return obList;
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
if (ps != null)
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 獲取所有訂單.
*
* @param nid
* 訂單編號 (-1:表無此條件).
* @param scuser
* 下單人(精確查) (null:表無此條件).
* @param sstatus
* 審核狀態 (null:表無此條件).
* @param sauser
* 審核人(精確查) (null:表無此條件).
* @param smcname
* 商品名(模糊查) (null:表無此條件).
* @return 訂單集合.
*/
public List searchOrder(long nid, String scuser, String sstatus,
String sauser, String smcname) {
return searchOrder(nid, scuser, sstatus, sauser, smcname, null, null,
-1, -1);
}
/**
* 獲取所有訂單.
*
* @param scuser
* 下單人(精確查) (null:表無此條件).
* @param sstatus
* 審核狀態 (null:表無此條件).
* @param pastart
* 記錄 開始行 (-1:表無此條件).
* @param paend
* 記錄 結束行 (-1:表無此條件).
*
* @return 訂單集合.
*/
public List searchOrder(String scuser, String sstatus, int pastart,
int paend) {
return searchOrder(-1, scuser, sstatus, null, null, null, null,
pastart, paend);
}
/**
* 獲取所有訂單.
*
* @param scuser
* 下單人(精確查) (null:表無此條件).
* @param sstatus
* 審核狀態 (null:表無此條件).
* @return 訂單集合.
*/
public List searchOrder(String scuser, String sstatus) {
return searchOrder(-1, scuser, sstatus, null, null, null, null, -1, -1);
}
/**
* 獲取訂單對象.
*
* @param nid
* 訂單編號.
* @return 訂單對象.
*/
public OrderBean searchOrder(long nid) {
List obList = searchOrder(nid, null, null, null, null, null, null, -1,
-1);
if (obList.size() != 0) {
return (OrderBean) obList.get(0);
} else {
return null;
}
}
/**
* 獲取所有訂單.
*
* @return 訂單集合.
*/
public List searchOrder() {
return searchOrder(-1, null, null, null, null, null, null, -1, -1);
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -