?? elecdevinfodaoimpl.java
字號(hào):
package imis_elec;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import database.DBPoolManager;
public class ElecDevInfoDAOImpl implements ElecDevInfoDAO {
private static final String GET_CUSTOMER_SQL =
"SELECT * FROM ElecDev_info WHERE ElecDevNo = ?";
private static final String CREATE_CUSTOMER_SQL =
"INSERT INTO ElecDev_info (ElecEquiNo,ElecDevNo,UserId,ElecDevTypeNo,ElecDevName,ElecDevType,ElecDevPower,ElecDevSpec,ElecDevStatus,ElecDevAddr,ReMark,Memo) VALUES (ElecEquiNo.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
private static final String DELETE_CUSTOMER_SQL =
"DELETE FROM ElecDev_info WHERE ElecDevNo = ?";
private static final String UPDATE_CUSTOMER_SQL =
"UPDATE ElecDev_info SET ElecDevNo=?, UserId=?, ElecDevTypeNo=?, ElecDevName=?, ElecDevType=?, ElecDevPower=?, ElecDevSpec=?, ElecDevStatus=?,ElecDevAddr=?, ReMark=?, Memo=? WHERE ElecDevNo = ?";
private static final String SEARCH_CUSTOMER_SQL =
"select e1.ElecEquiNo,e1.ElecDevNo,e1.userid,e1.elecdevtypeno,e1.elecdevname,e1.elecdevtype" +
",e1.elecdevpower,e1.elecdevspec,e1.elecdevstatus,e1.elecdevaddr,e1.remark,e1.memo,e2.elecdevtypename" +
" from elecdev_info e1,ElecDev_Type e2 where e1.elecdevtypeno=e2.elecdevtypeno AND ";
public boolean deleteElecDevInfo(String elecDevNo) throws Exception {
boolean flag = false;
Connection connection = null;
PreparedStatement pStatement = null;
DBPoolManager db = new DBPoolManager();
db.getConnection();
try {
connection = db.conn;
pStatement = connection.prepareStatement(DELETE_CUSTOMER_SQL);
pStatement.setString(1, elecDevNo);
int i = pStatement.executeUpdate();
flag = i > 0 ? true : false;
pStatement.close();
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
db.freeConnection();
}
return flag;
}
public ElecDevInfoTO getElecDevInfo(String elecDevNo) throws Exception {
Connection connection = null;
PreparedStatement pStatement = null;
ResultSet rs = null;
ElecDevInfoTO elecDevInfo = null;
DBPoolManager db = new DBPoolManager();
db.getConnection();
try {
connection = db.conn;
// Prepare a statement to insert a record
pStatement = connection.prepareStatement(GET_CUSTOMER_SQL);
pStatement.setString(1,elecDevNo);
rs = pStatement.executeQuery();
if (rs.next()) {
elecDevInfo = new ElecDevInfoTO();
elecDevInfo.setElecEquiNo(rs.getInt("ElecEquiNo"));
elecDevInfo.setElecDevNo(rs.getString("ElecDevNo"));
elecDevInfo.setUserId(rs.getString("UserId"));
elecDevInfo.setElecDevTypeNo(rs.getInt("ElecDevTypeNo"));
elecDevInfo.setElecDevName(rs.getString("ElecDevName"));
elecDevInfo.setElecDevType(rs.getString("ElecDevType"));
elecDevInfo.setElecDevPower(rs.getDouble("ElecDevPower"));
elecDevInfo.setElecDevSpec(rs.getString("ElecDevSpec"));
elecDevInfo.setElecDevStatus(rs.getInt("ElecDevStatus"));
elecDevInfo.setElecDevAddr(rs.getString("ElecDevAddr"));
elecDevInfo.setReMark(rs.getInt("ReMark"));
elecDevInfo.setMemo(rs.getString("Memo"));
}
rs.close();
pStatement.close();
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
db.freeConnection();
}
return elecDevInfo;
}
public boolean insertElecDevInfo(ElecDevInfoTO elecDevInfo) throws Exception {
boolean flag = false;
Connection connection = null;
PreparedStatement pStatement = null;
DBPoolManager db = new DBPoolManager();
db.getConnection();
try {
connection = db.conn;
//Prepare a statement to insert a record
pStatement = connection.prepareStatement(CREATE_CUSTOMER_SQL);
pStatement.setString(1, elecDevInfo.getElecDevNo());
pStatement.setString(2, elecDevInfo.getUserId());
pStatement.setInt(3, elecDevInfo.getElecDevTypeNo());
pStatement.setString(4, elecDevInfo.getElecDevName());
pStatement.setString(5, elecDevInfo.getElecDevType());
pStatement.setDouble(6, elecDevInfo.getElecDevPower());
pStatement.setString(7, elecDevInfo.getElecDevSpec());
pStatement.setInt(8, elecDevInfo.getElecDevStatus());
pStatement.setString(9, elecDevInfo.getElecDevAddr());
pStatement.setInt(10, elecDevInfo.getReMark());
pStatement.setString(11, elecDevInfo.getMemo());
int i = pStatement.executeUpdate();
flag = i > 0 ? true : false;
pStatement.close();
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
db.freeConnection();
}
return flag;
}
public ArrayList searchElecDevInfos(ElecDevInfoTO search) throws Exception {
ArrayList arrayList = new ArrayList();
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
DBPoolManager db = new DBPoolManager();
db.getConnection();
//Build the search criterias
StringBuffer criteriaSql = new StringBuffer(512);
criteriaSql.append(SEARCH_CUSTOMER_SQL);
if (search.getMemo() != null) {
criteriaSql.append(" UserId in ( select UserId from ElecInfo where OrganId in ( Select organid from organ Connect by prior organid=suporganid Start with organid = " +
DBUtil.fixSqlFieldValue(search.getMemo()) + " )) AND ");
}
if (search.getElecDevNo() != null) {
criteriaSql.append("ElecDevNo LIKE '%" +
DBUtil.fixSqlFieldValue(search.getElecDevNo()) + "%' AND ");
}
if (search.getUserId() != null) {
criteriaSql.append("UserId LIKE '%" +
DBUtil.fixSqlFieldValue(search.getUserId()) + "%' AND ");
}
//Remove unused 'And' & 'WHERE'
if (criteriaSql.substring(criteriaSql.length()-5).
equals(" AND "))
criteriaSql.delete(criteriaSql.length()-5,
criteriaSql.length()-1);
if (criteriaSql.substring(criteriaSql.length()-7).
equals(" WHERE "))
criteriaSql.delete(criteriaSql.length()-7,
criteriaSql.length()-1);
criteriaSql.append(" order by UserId,ElecDevNo");
try {
connection = db.conn;
statement = connection.createStatement();
rs = statement.executeQuery(criteriaSql.toString());
while (rs.next()) {
ElecDevInfoTO elecDevInfo = new ElecDevInfoTO();
elecDevInfo.setElecEquiNo(rs.getInt("ElecEquiNo"));
elecDevInfo.setElecDevNo(rs.getString("ElecDevNo"));
elecDevInfo.setElecDevTypeName(rs.getString("elecdevtypename"));
elecDevInfo.setUserId(rs.getString("UserId"));
elecDevInfo.setElecDevTypeNo(rs.getInt("ElecDevTypeNo"));
elecDevInfo.setElecDevName(rs.getString("ElecDevName"));
elecDevInfo.setElecDevType(rs.getString("ElecDevType"));
elecDevInfo.setElecDevPower(rs.getDouble("ElecDevPower"));
elecDevInfo.setElecDevSpec(rs.getString("ElecDevSpec"));
elecDevInfo.setElecDevStatus(rs.getInt("ElecDevStatus"));
if(elecDevInfo.getElecDevStatus() == 1) {
elecDevInfo.setElecDevStatusName("好");
} else if(elecDevInfo.getElecDevStatus() == 2) {
elecDevInfo.setElecDevStatusName("需要維修");
} else if(elecDevInfo.getElecDevStatus() == 3) {
elecDevInfo.setElecDevStatusName("停用");
} else if(elecDevInfo.getElecDevStatus() == 4) {
elecDevInfo.setElecDevStatusName("報(bào)廢");
} else if(elecDevInfo.getElecDevStatus() == 5) {
elecDevInfo.setElecDevStatusName("其他");
}
elecDevInfo.setElecDevAddr(rs.getString("ElecDevAddr"));
elecDevInfo.setReMark(rs.getInt("ReMark"));
elecDevInfo.setMemo(rs.getString("Memo"));
arrayList.add(elecDevInfo);
}
rs.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
db.freeConnection();
}
return arrayList;
}
public boolean updateElecDevInfo(ElecDevInfoTO elecDevInfo) throws Exception {
boolean flag = false;
Connection connection = null;
PreparedStatement pStatement = null;
DBPoolManager db = new DBPoolManager();
db.getConnection();
try {
connection = db.conn;
// Prepare a statement to insert a record
pStatement = connection.prepareStatement(UPDATE_CUSTOMER_SQL);
pStatement.setString(1, elecDevInfo.getElecDevNo());
pStatement.setString(2, elecDevInfo.getUserId());
pStatement.setInt(3, elecDevInfo.getElecDevTypeNo());
pStatement.setString(4, elecDevInfo.getElecDevName());
pStatement.setString(5, elecDevInfo.getElecDevType());
pStatement.setDouble(6, elecDevInfo.getElecDevPower());
pStatement.setString(7, elecDevInfo.getElecDevSpec());
pStatement.setInt(8, elecDevInfo.getElecDevStatus());
pStatement.setString(9, elecDevInfo.getElecDevAddr());
pStatement.setInt(10, elecDevInfo.getReMark());
pStatement.setString(11, elecDevInfo.getMemo());
pStatement.setString(12, elecDevInfo.getElecDevNo());
int i = pStatement.executeUpdate();
flag = i > 0 ? true : false;
pStatement.close();
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
db.freeConnection();
}
return flag;
}
}
?? 快捷鍵說(shuō)明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -