📄 elecmeterinfodaoimpl.java
字号:
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 java.util.Calendar;
import database.DBPoolManager;
public class ElecMeterInfoDAOImpl implements ElecMeterInfoDAO{
private static final String GET_CUSTOMER_SQL =
"SELECT * FROM ElecMeter_Info WHERE ElecMeterRegNo = ?";
private static final String CREATE_CUSTOMER_SQL =
"INSERT INTO ElecMeter_Info (ElecMeterRegNo,UserId,ElecAmmeterType,ElecMulties,ElecMeterName,ElecMeterType,ElecMeterScope,ElecMeterCfg,ElecMeterAddr,ElecMeterStatus,UseElecType,UseDate,UseYearSum,ReMark,Memo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, to_date(?, 'yy-mm-dd'), ?, ?, ?)";
private static final String DELETE_CUSTOMER_SQL =
"DELETE FROM ElecMeter_Info WHERE ElecMeterRegNo = ?";
private static final String UPDATE_CUSTOMER_SQL =
"UPDATE ElecMeter_Info SET UserId=?, ElecAmmeterType=?, ElecMulties=?, ElecMeterName=?, ElecMeterType=?, ElecMeterScope=?, ElecMeterCfg=?,ElecMeterAddr=?, ElecMeterStatus=?, UseElecType=?, UseDate=to_date(?, 'yy-mm-dd'), UseYearSum=?, ReMark=?, Memo=? WHERE ElecMeterRegNo = ?";
private static final String SEARCH_CUSTOMER_SQL =
"SELECT * FROM ElecMeter_Info WHERE ";
public boolean insertElecMeterInfo(ElecMeterInfoTO elecMeterInfo) 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);
// 把字符串转换成 Date 对象
/*String strDate = elecMeterInfo.getUseDate();
String[] arrDate = strDate.split("[-]");
Calendar calender = Calendar.getInstance();
int year = Integer.parseInt(arrDate[0]);
int month = Integer.parseInt(arrDate[1]) - 1;
int date = Integer.parseInt(arrDate[2]);
calender.set(year, month, date);*/
pStatement.setString(1, elecMeterInfo.getElecMeterRegNo());
pStatement.setString(2, elecMeterInfo.getUserId());
pStatement.setInt(3, elecMeterInfo.getElecAmmeterType());
pStatement.setInt(4, elecMeterInfo.getElecMulties());
pStatement.setString(5, elecMeterInfo.getElecMeterName());
pStatement.setString(6, elecMeterInfo.getElecMeterType());
pStatement.setDouble(7, elecMeterInfo.getElecMeterScope());
pStatement.setString(8, elecMeterInfo.getElecMeterCfg());
pStatement.setString(9, elecMeterInfo.getElecMeterAddr());
pStatement.setInt(10, elecMeterInfo.getElecMeterStatus());
pStatement.setInt(11, elecMeterInfo.getUseElecType());
pStatement.setString(12, elecMeterInfo.getUseDate());
pStatement.setDouble(13, elecMeterInfo.getUseYearSum());
pStatement.setInt(14, elecMeterInfo.getReMark());
pStatement.setString(15, elecMeterInfo.getMemo());
int i = pStatement.executeUpdate();
flag = i > 0 ? true : false;
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
db.freeConnection();
}
return flag;
}
public boolean updateElecMeterInfo(ElecMeterInfoTO elecMeterInfo) 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);
// 把字符串转换成 Date 对象
/*String strDate = elecMeterInfo.getUseDate();
String[] arrDate = strDate.split("-");
Calendar calender = Calendar.getInstance();
int year = Integer.parseInt(arrDate[0]);
int month = Integer.parseInt(arrDate[1]) - 1;
int date = Integer.parseInt(arrDate[2]);
calender.set(year, month, date);*/
pStatement.setString(1, elecMeterInfo.getUserId());
pStatement.setInt(2, elecMeterInfo.getElecAmmeterType());
pStatement.setInt(3, elecMeterInfo.getElecMulties());
pStatement.setString(4, elecMeterInfo.getElecMeterName());
pStatement.setString(5, elecMeterInfo.getElecMeterType());
pStatement.setDouble(6, elecMeterInfo.getElecMeterScope());
pStatement.setString(7, elecMeterInfo.getElecMeterCfg());
pStatement.setString(8, elecMeterInfo.getElecMeterAddr());
pStatement.setInt(9, elecMeterInfo.getElecMeterStatus());
pStatement.setInt(10, elecMeterInfo.getUseElecType());
pStatement.setString(11, elecMeterInfo.getUseDate());
pStatement.setDouble(12, elecMeterInfo.getUseYearSum());
pStatement.setInt(13, elecMeterInfo.getReMark());
pStatement.setString(14, elecMeterInfo.getMemo());
pStatement.setString(15, elecMeterInfo.getElecMeterRegNo());
int i = pStatement.executeUpdate();
flag = i > 0 ? true : false;
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
db.freeConnection();
}
return flag;
}
public ElecMeterInfoTO getElecMeterInfo(String ElecMeterRegNo) throws Exception {
Connection connection = null;
PreparedStatement pStatement = null;
ResultSet rs = null;
ElecMeterInfoTO elecMeterInfo = 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,ElecMeterRegNo);
rs = pStatement.executeQuery();
if (rs.next()) {
elecMeterInfo = new ElecMeterInfoTO();
elecMeterInfo.setElecMeterRegNo(rs.getString("ElecMeterRegNo"));
elecMeterInfo.setUserId(rs.getString("UserId"));
elecMeterInfo.setElecAmmeterType(rs.getInt("ElecAmmeterType"));
elecMeterInfo.setElecMulties(rs.getInt("ElecMulties"));
elecMeterInfo.setElecMeterName(rs.getString("ElecMeterName"));
elecMeterInfo.setElecMeterType(rs.getString("ElecMeterType"));
elecMeterInfo.setElecMeterScope(rs.getDouble("ElecMeterScope"));
elecMeterInfo.setElecMeterCfg(rs.getString("ElecMeterCfg"));
elecMeterInfo.setElecMeterAddr(rs.getString("ElecMeterAddr"));
elecMeterInfo.setElecMeterStatus(rs.getInt("ElecMeterStatus"));
elecMeterInfo.setUseElecType(rs.getInt("UseElecType"));
elecMeterInfo.setUseDate(rs.getDate("UseDate").toString());
elecMeterInfo.setUseYearSum(rs.getDouble("UseYearSum"));
elecMeterInfo.setReMark(rs.getInt("ReMark"));
elecMeterInfo.setMemo(rs.getString("Memo"));
}
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
db.freeConnection();
}
return elecMeterInfo;
}
public boolean deleteElecMeterInfo(String ElecMeterRegNo) 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, ElecMeterRegNo);
int i = pStatement.executeUpdate();
flag = i > 0 ? true : false;
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
db.freeConnection();
}
return flag;
}
public ArrayList searchElecMeterInfos(ElecMeterInfoTO 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.getElecMeterRegNo() != null) {
criteriaSql.append("ElecMeterRegNo LIKE '%" +
DBUtil.fixSqlFieldValue(search.getElecMeterRegNo()) + "%' 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,ElecMeterRegNo,UseDate");
try {
connection = db.conn;
statement = connection.createStatement();
rs = statement.executeQuery(criteriaSql.toString());
while (rs.next()) {
ElecMeterInfoTO elecMeterInfo = new ElecMeterInfoTO();
elecMeterInfo.setElecMeterRegNo(rs.getString("ElecMeterRegNo"));
elecMeterInfo.setUserId(rs.getString("UserId"));
elecMeterInfo.setElecAmmeterType(rs.getInt("ElecAmmeterType"));
if(elecMeterInfo.getElecAmmeterType() == 1) {
elecMeterInfo.setAmmeterType("工业用电表");
} else if(elecMeterInfo.getElecAmmeterType() == 2) {
elecMeterInfo.setAmmeterType("居民用电表");
}
elecMeterInfo.setElecMulties(rs.getInt("ElecMulties"));
elecMeterInfo.setElecMeterName(rs.getString("ElecMeterName"));
elecMeterInfo.setElecMeterType(rs.getString("ElecMeterType"));
elecMeterInfo.setElecMeterScope(rs.getDouble("ElecMeterScope"));
elecMeterInfo.setElecMeterCfg(rs.getString("ElecMeterCfg"));
elecMeterInfo.setElecMeterAddr(rs.getString("ElecMeterAddr"));
elecMeterInfo.setElecMeterStatus(rs.getInt("ElecMeterStatus"));
if(elecMeterInfo.getElecMeterStatus() == 1) {
elecMeterInfo.setMeterStatus("好");
} else if(elecMeterInfo.getElecMeterStatus() == 2) {
elecMeterInfo.setMeterStatus("需要维修");
} else if(elecMeterInfo.getElecMeterStatus() == 3) {
elecMeterInfo.setMeterStatus("停用");
} else if(elecMeterInfo.getElecMeterStatus() == 4) {
elecMeterInfo.setMeterStatus("报废");
} else if(elecMeterInfo.getElecMeterStatus() == 5) {
elecMeterInfo.setMeterStatus("其他");
}
elecMeterInfo.setUseElecType(rs.getInt("UseElecType"));
if(elecMeterInfo.getUseElecType() == 1) {
elecMeterInfo.setElecType("非居民照明");
} else if(elecMeterInfo.getUseElecType() == 2) {
elecMeterInfo.setElecType("居民照明");
} else if(elecMeterInfo.getUseElecType() == 3) {
elecMeterInfo.setElecType("商业用电");
} else if(elecMeterInfo.getUseElecType() == 4) {
elecMeterInfo.setElecType("非商业用电");
} else if(elecMeterInfo.getUseElecType() == 5) {
elecMeterInfo.setElecType("工业用电");
} else if(elecMeterInfo.getUseElecType() == 6) {
elecMeterInfo.setElecType("非工业用电");
}
elecMeterInfo.setUseDate(rs.getDate("UseDate").toString());
elecMeterInfo.setUseYearSum(rs.getDouble("UseYearSum"));
elecMeterInfo.setReMark(rs.getInt("ReMark"));
elecMeterInfo.setMemo(rs.getString("Memo"));
arrayList.add(elecMeterInfo);
}
} catch (SQLException e) {
e.printStackTrace();
throw new Exception();
} finally {
db.freeConnection();
}
return arrayList;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -