?? materialdao.java
字號:
package imis_mate.DAO;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import database.DBPoolManager;
import imis_mate.bean.MateOutDetailBean;
import imis_mate.bean.MaterialBean;
import imis_mate.bean.MaterialBuyDetailBean;
import imis_mate.bean.MaterialCheckBean;
import imis_mate.bean.MaterialTypeBean;
public class MaterialDAO {
// SQL 語句
private static final String INSERT_MATERIAL_SQL = "INSERT INTO "
+ "Material_Table(materialNo,materialTypeName,materialTypeNo,maModel,maBrand,maMadeIn,buyId,priceUnit,inCount,nowCount,inDate,userCode,inFrom,reMark)"
+ "VALUES (MaterialNo.Nextval,?,?,?,?,?,?,?,?,?,?,?,?,?)";
private static final String INSERT_MATERIAL_CHECK_SQL = "INSERT INTO "
+ "Material_Table(materialNo,materialTypeName,materialTypeNo,maModel,maBrand,maMadeIn,buyId,priceUnit,inCount,nowCount,inDate,userCode,inFrom,reMark)"
+ "VALUES (MaterialNo.Nextval,?,?,?,?,?,?,?,?,?,to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),?,?,?)";
private static final String UPDATE_MATERIAL_SQL = "" +
"UPDATE Material_Table "+
"SET materialTypeName = ?,materialTypeNo = ?,maModel = ?,maBrand = ?,maMadeIn = ?,buyId = ?,priceUnit = ?,inCount = ?,nowCount = ?,inDate = ?,userCode = ?,inFrom = ?,reMark = ? "+
"WHERE materialNo = ?";
private static final String UPDATE_MATERIAL_OUT_SQL = "" +
"UPDATE Material_Table "+
"SET nowCount = nowCount + ? "+
"WHERE materialNo = ?";
private static final String UPDATE_MATERIAL_OUT_SQL_FOR_CHECK = "" +
"UPDATE Material_Table "+
"SET nowCount = ? "+
"WHERE materialNo = ?";
private static final String DELETE_MATERIAL_SQL = "DELETE FROM Material_Table "
+ "WHERE MaterialNo = ?";
private static final String SELECT_MATERIAL_SQL = "SELECT materialNo,materialTypeName,materialTypeNo,maModel,maBrand,maMadeIn,buyId,priceUnit,inCount,nowCount,inDate,userCode,inFrom,reMark "
+ "FROM Material_Table " + "WHERE MaterialNo = ?";
private static final String SEARCH_MATERIAL_SQL = ""
+ "SELECT materialNo,materialTypeName,materialTypeNo,maModel,maBrand,maMadeIn,buyId,priceUnit,inCount,nowCount,inDate,userCode,inFrom,reMark "
+ "FROM Material_Table " + "WHERE ";
private static final String RESEVE_SEARCH_MATERIAL_SQL = ""
+ "SELECT materialNo,materialTypeName,materialTypeNo,maModel,maBrand,maMadeIn,priceUnit,nowCount,inFrom,inDate,buyId "
+ "FROM Material_Table " + "WHERE ";
private static final String BUY_SEARCH_MATERIAL_SQL = ""
+ "SELECT BuyId,BuyNo,MaterialTypeName,MaModel,MaBrand,MaMadeIn,MaterialTypeNo,PriceUnit,InCount,flag,reflag "
+ "FROM Material_Buy_Detail "
+ "WHERE Flag IS NULL AND ReFlag IS NULL ";
private static final String BUY_INSERT_MATERIAL_SQL = "INSERT INTO "
+ "Material_Table(materialNo,materialTypeName,materialTypeNo,maModel,maBrand,maMadeIn,buyId,priceUnit,inCount,nowCount,inDate,userCode,inFrom,reMark)"
+ "VALUES (MaterialNo.Nextval,?,?,?,?,?,?,?,?,?,to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),?,?,?)";
/*private static final String CHECK_SEARCH_MATERIAL_SQL = ""+
"SELECT Material_Table.materialNo,Material_Table.materialTypeName,Material_Table.materialTypeNo,Material_Table.maModel,Material_Table.maBrand,Material_Table.maMadeIn,Material_Table.priceUnit,Material_Table.nowCount,Material_Table.inFrom,Material_Table.inDate "+
"FROM Material_Table,Material_Check_Table " +
"WHERE Material_Table.MaterialNo = Material_Check_Table.MaterialNo " +
"AND Material_Table.nowCount > 0 " +
"AND ";*/
private static final String CHECK_SEARCH_MATERIAL_SQL = ""+
"SELECT distinct Material_Table.materialNo,Material_Table.materialTypeName,Material_Table.materialTypeNo,Material_Table.maModel,Material_Table.maBrand,Material_Table.maMadeIn,Material_Table.priceUnit,Material_Table.nowCount,Material_Table.inFrom,Material_Table.inDate "+
"FROM Material_Table " +
"WHERE Material_Table.nowCount > 0 " +
"AND ";
private static final String UPDATE_NOWCOUNT_SQL = ""
+ "UPDATE Material_Table " + "SET nowCount = nowCount - ? "
+ "WHERE materialNo = ?";
private static final String CHECK_DELECT_MATERIAL_SQL = "" +
"DELETE FROM Material_Table " +
"WHERE ROWNUM <= 1 " +
"AND ";
private static final String DELETE_OUT_UPDATE_NOWCOUNT_SQL = "" +
"UPDATE Material_Table " +
"SET nowCount = nowCount + ? " +
"WHERE materialNo = ?";
// 方法
public boolean insertMaterial(MaterialBean mate) throws Exception {
// 插入庫存信息
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBConnection.getConnection();
ps = conn.prepareStatement(INSERT_MATERIAL_SQL);
ps.setString(1, mate.getMaterialTypeName());
ps.setString(2, mate.getMaterialTypeNo());
ps.setString(3, mate.getMaModel());
ps.setString(4, mate.getMaBrand());
ps.setString(5, mate.getMaMadeIn());
ps.setInt(6, mate.getBuyId());
ps.setDouble(7, mate.getPriceUnit());
ps.setInt(8, mate.getInCount());
ps.setInt(9, mate.getNowCount());
ps.setDate(10, mate.getInDate());
ps.setString(11, mate.getUserCode());
ps.setInt(12, mate.getInFrom());
ps.setString(13, mate.getReMark());
int i = ps.executeUpdate();
flag = i > 0 ? true : false;
} catch (Exception e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(ps);
DBConnection.close(conn);
}
return flag;
}
public boolean insertMaterialNotDate(MaterialBean mate) throws Exception {
// 插入庫存信息 取系統日期
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBConnection.getConnection();
ps = conn.prepareStatement(INSERT_MATERIAL_CHECK_SQL);
ps.setString(1, mate.getMaterialTypeName());
ps.setString(2, mate.getMaterialTypeNo());
ps.setString(3, mate.getMaModel());
ps.setString(4, mate.getMaBrand());
ps.setString(5, mate.getMaMadeIn());
ps.setInt(6, mate.getBuyId());
ps.setDouble(7, mate.getPriceUnit());
ps.setInt(8, mate.getInCount());
ps.setInt(9, mate.getNowCount());
//ps.setDate(10, mate.getInDate());
ps.setString(10, mate.getUserCode());
ps.setInt(11, mate.getInFrom());
ps.setString(12, mate.getReMark());
int i = ps.executeUpdate();
flag = i > 0 ? true : false;
} catch (Exception e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(ps);
DBConnection.close(conn);
}
return flag;
}
//盤點信息插入
public boolean insertCheckMaterial(MaterialBean mate) throws Exception {
//
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBConnection.getConnection();
ps = conn.prepareStatement(INSERT_MATERIAL_SQL);
ps.setString(1, mate.getMaterialTypeName());
ps.setString(2, mate.getMaterialTypeNo());
ps.setString(3, mate.getMaModel());
ps.setString(4, mate.getMaBrand());
ps.setString(5, mate.getMaMadeIn());
ps.setInt(6, mate.getBuyId());
ps.setDouble(7, mate.getPriceUnit());
ps.setInt(8, mate.getInCount());
ps.setInt(9, mate.getNowCount());
//ps.setDate(10, mate.getInDate());
ps.setString(10, mate.getUserCode());
ps.setInt(11, mate.getInFrom());
ps.setString(12, mate.getReMark());
int i = ps.executeUpdate();
flag = i > 0 ? true : false;
} catch (Exception e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(ps);
DBConnection.close(conn);
}
return flag;
}
public boolean updateMaterial(MaterialBean mate) throws Exception {
// 更新庫存信息
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBConnection.getConnection();
ps = conn.prepareStatement(UPDATE_MATERIAL_SQL);
ps.setString(1, mate.getMaterialTypeName());
ps.setString(2, mate.getMaterialTypeNo());
ps.setString(3, mate.getMaModel());
ps.setString(4, mate.getMaBrand());
ps.setString(5, mate.getMaMadeIn());
ps.setInt(6, mate.getBuyId());
ps.setDouble(7, mate.getPriceUnit());
ps.setInt(8, mate.getInCount());
ps.setInt(9, mate.getNowCount());
ps.setDate(10, mate.getInDate());
ps.setString(11, mate.getUserCode());
ps.setInt(12, mate.getInFrom());
ps.setString(13, mate.getReMark());
ps.setInt(14, mate.getMaterialNo());
int i = ps.executeUpdate();
flag = i > 0 ? true : false;
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(ps);
DBConnection.close(conn);
}
return flag;
}
public boolean deleteMaterial(String materialNo) throws Exception {
// 刪除庫存信息
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBConnection.getConnection();
ps = conn.prepareStatement(DELETE_MATERIAL_SQL);
ps.setString(1, materialNo);
int i = ps.executeUpdate();
flag = i > 0 ? true : false;
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(ps);
DBConnection.close(conn);
}
return flag;
}
public MaterialBean selectMaterial(int materialNo) throws Exception {
// 查詢庫存信息
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
MaterialBean mate = null;
try {
conn = DBConnection.getConnection();
ps = conn.prepareStatement(SELECT_MATERIAL_SQL);
ps.setInt(1, materialNo);
rs = ps.executeQuery();
if (rs.next()) {
mate = new MaterialBean();
mate.setMaterialNo(rs.getString("materialNo"));
mate.setMaterialTypeName(rs.getString("materialTypeName"));
mate.setMaterialTypeNo(rs.getString("materialTypeNo"));
mate.setMaModel(rs.getString("maModel"));
mate.setMaBrand(rs.getString("maBrand"));
mate.setMaMadeIn(rs.getString("maMadeIn"));
mate.setBuyId(rs.getInt("buyId"));
mate.setPriceUnit(rs.getDouble("priceUnit"));
mate.setInCount(rs.getInt("inCount"));
mate.setNowCount(rs.getInt("nowCount"));
mate.setInDate(rs.getDate("inDate"));
mate.setUserCode(rs.getString("userCode"));
mate.setInFrom(rs.getInt("inFrom"));
mate.setReMark(rs.getString("reMark"));
}
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
DBConnection.close(rs);
DBConnection.close(ps);
DBConnection.close(conn);
}
return mate;
}
// public ArrayList searchMaterial(MaterialBean mate) throws Exception {
// //按條件查詢庫存信息
//
// ArrayList array = new ArrayList();
// Connection conn = null;
// Statement stmt = null;
// ResultSet rs = null;
//
// //Build the search criterias
// StringBuffer sql = new StringBuffer(512);
// sql.append(SEARCH_MATERIAL_SQL);
// if(mate.getMaterialTypeName() != null &&
// mate.getMaterialTypeName().equals("") != true) {
// sql.append("MaterialTypeName LIKE '%" +
// DBUtil.fixSqlFieldValue(mate.getMaterialTypeName()) + "%' AND ");
// }
// if(mate.getMaterialTypeNo() != null &&
// mate.getMaterialTypeNo().equals("") != true) {
// sql.append("MaterialTypeNo LIKE '%" +
// DBUtil.fixSqlFieldValue(mate.getMaterialTypeNo()) + "%' AND ");
// }
// if(mate.getMaModel() != null && mate.getMaModel().equals("") != true) {
// sql.append("MaModel LIKE '%" +
// DBUtil.fixSqlFieldValue(mate.getMaModel()) + "%' AND ");
// }
// if(mate.getMaBrand() != null && mate.getMaBrand().equals("") != true) {
// sql.append("MaBrand LIKE '%" +
// DBUtil.fixSqlFieldValue(mate.getMaBrand()) + "%' AND ");
// }
// if(mate.getMaMadeIn() != null && mate.getMaMadeIn().equals("") != true) {
// sql.append("MaMadeIn LIKE '%" +
// DBUtil.fixSqlFieldValue(mate.getMaMadeIn()) + "%'");
// }
//
// //Remove unused 'AND' & 'WHERE'
// if(sql.substring(sql.length()-5).equals(" AND ")) {
// sql.delete(sql.length()-5, sql.length()-1);
// }
// if(sql.substring(sql.length()-7).equals(" WHERE ")) {
// sql.delete(sql.length()-7, sql.length()-1);
// }
// try {
// conn = DBConnection.getConnection();
// stmt = conn.createStatement();
// rs = stmt.executeQuery(sql.toString());
//
// while(rs.next()) {
// MaterialBean mate2 = new MaterialBean();
// mate2.setMaterialNo(rs.getString("materialNo"));
// mate2.setMaterialTypeName(rs.getString("materialTypeName"));
// mate2.setMaterialTypeNo(rs.getString("materialTypeNo"));
// mate2.setMaModel(rs.getString("maModel"));
// mate2.setMaBrand(rs.getString("maBrand"));
// mate2.setMaMadeIn(rs.getString("maMadeIn"));
// mate2.setBuyId(rs.getInt("buyId"));
// mate2.setPriceUnit(rs.getDouble("priceUnit"));
// mate2.setInCount(rs.getInt("inCount"));
// mate2.setNowCount(rs.getInt("nowCount"));
// mate2.setInDate(rs.getDate("inDate"));
// mate2.setUserCode(rs.getString("userCode"));
// mate2.setInFrom(rs.getInt("inFrom"));
// mate2.setReMark(rs.getString("reMark"));
// array.add(mate2);
//
// }
//
// } catch (SQLException e) {
// e.printStackTrace();
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -