// 根据id进行delete
public static int delete(int id) {
Connection dbConn = DataBaseTool.getConnection();
String sql = "delete from orders where id=" + id + "";
int flag = DataBaseTool.executeSQL(dbConn, sql);
DataBaseTool.close(null, null, dbConn);
return flag;
}
/*
* 根据两个hashMap中输入，进行部分更新
* hashMap:要修改的数据项及修改值
* hashMap2：查询待修改项的范围
*/
public static int update(HashMap<String, String> hashMap, HashMap<String, String> hashMap2) {
Connection dbConn = DataBaseTool.getConnection();
StringBuffer sql = new StringBuffer("update orders set ");
for (Entry<String, String> entry : hashMap.entrySet()) {
sql.append(entry.getKey() + "='" + entry.getValue() + "',");
}
sql.delete(sql.length() - 1, sql.length());
sql.append(" where ");
for (Entry<String, String> entry : hashMap2.entrySet()) {
sql.append(entry.getKey() + "='" + entry.getValue() + "' and ");
}
sql.delete(sql.length() - 4, sql.length());
int result = DataBaseTool.executeSQL(dbConn, sql.toString());
return result;
}
// 查询订单全部数据-返回链表
public static ArrayList<Order> search(String sql) throws SQLException {
// 建立连接
Connection dbConn = DataBaseTool.getConnection();
// 执行sql语句
ResultSet rs = DataBaseTool.executeQuery(dbConn, sql);
ArrayList<Order> list = new ArrayList<Order>();
// 结果存储
while (rs.next()) {
Order order = new Order();
// int数据
order.setId(rs.getInt("id"));
order.setUserId(rs.getInt("userId"));
order.setsDistribution(rs.getInt("sDistribution"));
order.settDistribution(rs.getInt("tDistribution"));
order.setCarId(rs.getInt("carId"));
order.setCurrentDistribution(rs.getInt("currentDistribution"));
// 发件人
order.setSender(rs.getString("sender"));
order.setsCode(rs.getString("sCode"));
order.setsAddress(rs.getString("sAddress"));
order.setsPhone(rs.getString("sPhone"));
// 收件人
order.setReceiver(rs.getString("receiver"));
order.settCode(rs.getString("tCode"));
order.settAddress(rs.getString("tAddress"));
order.settPhone(rs.getString("tPhone"));
// 订单信息
order.setVolumn(rs.getFloat("volumn"));
order.setWeight(rs.getFloat("weight"));
order.setPrice(rs.getFloat("price"));
order.setStatus(rs.getInt("status"));
order.setGoodsType(rs.getInt("goodsType"));
order.setRemark(rs.getString("remark"));
order.setWorker(rs.getString("worker"));
order.setPath(rs.getString("path"));
order.setWorkDate(rs.getString("workDate"));
list.add(order);
}
// 关闭连接
DataBaseTool.close(rs, rs.getStatement(), dbConn);
// 返回数据
return list;
}
// 根据map进行search -- 组合查询
public static ArrayList<Order> searchByMap(HashMap<String, String> hashMap) throws SQLException {
Connection dbConn = DataBaseTool.getConnection();
StringBuffer sql = new StringBuffer("select * from orders where 1=1 ");
for (Entry<String, String> entry : hashMap.entrySet()) {
sql.append("and " + entry.getKey() + "=" + entry.getValue() + " ");
}
ResultSet rs = DataBaseTool.executeQuery(dbConn, sql.toString());
ArrayList<Order> list = new ArrayList<Order>();
while (rs.next()) {
Order od = new Order();
od.setId(rs.getInt("id"));
od.setUserId(rs.getInt("userId"));
od.setSender(rs.getString("sender"));
od.setsAddress(rs.getString("sAddress"));
od.setsCode(rs.getString("sCode"));
od.setsPhone(rs.getString("sPhone"));
od.setsDistribution(rs.getInt("sDistribution"));
od.setReceiver(rs.getString("receiver"));
od.settAddress(rs.getString("tAddress"));
od.settCode(rs.getString("tCode"));
od.settPhone(rs.getString("tPhone"));
od.settDistribution(rs.getInt("tDistribution"));
od.setWorker(rs.getString("worker"));
od.setWorkDate(rs.getString("workDate"));
od.setVolumn(rs.getFloat("volumn"));
od.setWeight(rs.getFloat("weight"));
od.setPath(rs.getString("path"));
od.setPrice(rs.getFloat("price"));
od.setStatus(rs.getInt("status"));
od.setCarId(rs.getInt("carId"));
od.setCurrentDistribution(rs.getInt("currentDistribution"));
od.setGoodsType(rs.getInt("goodsType"));
od.setRemark(rs.getString("remark"));
list.add(od);
}
DataBaseTool.close(rs, rs.getStatement(), dbConn);
return list;
}
// 根据id更新订单信息
public static int update(Order order) {
// 建立数据库连接
String sql = "";
int flag = 0;
Connection dbConn;
if (order.getUserId() == 0) {
sql = "update orders set userId=" + null + " where id=" + order.getId() + "";
} else {
sql = "update orders set userId=" + order.getUserId() + " where id=" + order.getId() + "";
}
dbConn = DataBaseTool.getConnection();
flag = DataBaseTool.executeSQL(dbConn, sql);
DataBaseTool.close(null, null, dbConn);
if (order.getCarId() == 0) {
sql = "update orders set carId=" + null + " where id=" + order.getId() + "";
} else {
sql = "update orders set carId=" + order.getCarId() + " where id=" + order.getId() + "";
}
dbConn = DataBaseTool.getConnection();
flag = DataBaseTool.executeSQL(dbConn, sql);
DataBaseTool.close(null, null, dbConn);
if (order.getCurrentDistribution() == 0) {
sql = "update orders set currentDistribution=" + null + " where id=" + order.getId() + "";
} else {
sql =
"update orders set currentDistribution="
+ order.getCurrentDistribution()
+ " where id="
+ order.getId()
+ "";
}
dbConn = DataBaseTool.getConnection();
flag = DataBaseTool.executeSQL(dbConn, sql);
DataBaseTool.close(null, null, dbConn);
// sql语句
sql =
"update orders set "
+ "sender='"
+ order.getSender()
+ "',"
+ "sAddress='"
+ order.getsAddress()
+ "',"
+ "sCode='"
+ order.getsCode()
+ "', "
+ "sPhone='"
+ order.getsPhone()
+ "',"
+ "sDistribution="
+ order.getsDistribution()
+ ", "
+ "receiver='"
+ order.getReceiver()
+ "',"
+ "tAddress='"
+ order.gettAddress()
+ "',"
+ "tCode='"
+ order.gettCode()
+ "',"
+ "tPhone='"
+ order.gettPhone()
+ "', "
+ "tDistribution="
+ order.gettDistribution()
+ ","
+ "worker='"
+ order.getWorker()
+ "',"
+ "workDate='"
+ order.getWorkDate()
+ "',"
+ "volumn="
+ order.getVolumn()
+ ","
+ "weight="
+ order.getWeight()
+ ","
+ "path='"
+ order.getPath()
+ "',"
+ "price="
+ order.getPrice()
+ ","
+ "status="
+ order.getStatus()
+ ","
+ "goodsType="
+ order.getGoodsType()
+ " ,"
+ "remark='"
+ order.getRemark()
+ "' where id="
+ order.getId()
+ "";
dbConn = DataBaseTool.getConnection();
flag = DataBaseTool.executeSQL(dbConn, sql);
DataBaseTool.close(null, null, dbConn);
return flag;
}
// 增加订单信息
public static int add(Order order) {
// 建立数据库连接
Connection dbConn = DataBaseTool.getConnection();
// sql语句
String sql = "";
if (order.getUserId() == 0) {
sql = "insert into orders values(" + null + ",";
} else {
sql = "insert into orders values(" + order.getUserId() + ",";
}
sql =
sql
+ "'"
+ order.getSender()
+ "',"
+ "'"
+ order.getsAddress()
+ "',"
+ "'"
+ order.getsCode()
+ "',"
+ "'"
+ order.getsPhone()
+ "',"
+ ""
+ order.getsDistribution()
+ ","
+ "'"
+ order.getReceiver()
+ "',"
+ "'"
+ order.gettAddress()
+ "',"
+ "'"
+ order.gettCode()
+ "',"
+ "'"
+ order.gettPhone()
+ "',"
+ ""
+ order.gettDistribution()
+ ","
+ "'"
+ order.getWorker()
+ "',"
+ "'"
+ order.getWorkDate()
+ "',"
+ ""
+ order.getVolumn()
+ ","
+ ""
+ order.getWeight()
+ ","
+ "'"
+ order.getPath()
+ "',"
+ ""
+ order.getPrice()
+ ","
+ ""
+ order.getStatus()
+ ",";
if (order.getCarId() == 0) {
sql = sql + "" + null + ",";
} else {
sql = sql + "" + order.getCarId() + ",";
}
if (order.getCurrentDistribution() == 0) {
sql = sql + "" + null + ",";
} else {
sql = sql + "" + order.getCurrentDistribution() + ",";
}
sql = sql + "" + order.getGoodsType() + ",'" + order.getRemark() + "')";
int flag = DataBaseTool.executeSQL(dbConn, sql);
DataBaseTool.close(null, null, dbConn);
return flag;
}