elecdev_repdaoimpl.java

JSP移动商品管理平台源代码.........

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 ElecDev_RepDAOImpl implements ElecDev_RepDAO {

	private static final String GET_CUSTOMER_SQL = "SELECT * FROM ElecDev_Rep WHERE ElecDevRepId = ?";

	private static final String CREATE_CUSTOMER_SQL = "INSERT INTO ElecDev_Rep (ElecDevRepId,DevType,UserId,AppBy,AppDate,ElecDevRepStaff,ElecDevRepContent,ElecDevRepResult,ElecDevRepDate,MainToDate,ReMark,Memo) VALUES (ElecDevRepId.Nextval, ?, ?, ?, to_date(?,'yy-mm-dd'), ?, ?, ?, to_date(?,'yy-mm-dd'),to_date(?,'yy-mm-dd'),?,?)";

	private static final String DELETE_CUSTOMER_SQL = "DELETE FROM ElecDev_Rep WHERE ElecDevRepId = ?";

	private static final String UPDATE_CUSTOMER_SQL = "UPDATE ElecDev_Rep SET DevType=?,UserId=?,AppBy=?,AppDate=to_date(?,'yy-mm-dd'),ElecDevRepStaff=?,ElecDevRepContent=?,ElecDevRepResult=?,ElecDevRepDate=to_date(?,'yy-mm-dd'),MainToDate=to_date(?,'yy-mm-dd'),ReMark=?,Memo=? where ElecDevRepId=?";

	private static final String SEARCH_CUSTOMER_SQL = 
		"select e1.elecdevrepid,e1.devtype,e2.elecdevtypename,e1.userid,e1.appby,e1.appdate,"+
		"e1.elecdevrepstaff,e1.elecdevrepcontent,e1.elecdevrepresult,e1.elecdevrepdate,e1.maintodate,e1.remark,e1.memo "+
		"from ElecDev_Rep e1, ElecDev_Type e2 where e1.devtype=e2.elecdevtypeno AND ";

	public boolean deleteElecDev_Rep(int elecDevRepId) throws Exception {

		boolean flag = false;
		// Connection connection = null;
		DBPoolManager db = new DBPoolManager();
		db.getConnection();
		PreparedStatement pStatement = null;
		try {
			// connection = DBConnection.getConnection();
			pStatement = db.conn.prepareStatement(DELETE_CUSTOMER_SQL);
			pStatement.setInt(1, elecDevRepId);
			int i = pStatement.executeUpdate();
			flag = i > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			// DBConnection.close(pStatement);
			// DBConnection.close(connection);
			db.freeConnection();
		}

		return flag;
	}

	public ElecDev_RepTO getElecDev_Rep(int elecDevRepId) throws Exception {
		// Connection connection = null;
		DBPoolManager db = new DBPoolManager();
		db.getConnection();
		PreparedStatement pStatement = null;
		ResultSet rs = null;
		ElecDev_RepTO elecDev_Rep = null;
		try {
			// connection = DBConnection.getConnection();
			// Prepare a statement to insert a record
			pStatement = db.conn.prepareStatement(GET_CUSTOMER_SQL);
			pStatement.setInt(1, elecDevRepId);
			rs = pStatement.executeQuery();
			if (rs.next()) {
				elecDev_Rep = new ElecDev_RepTO();
				elecDev_Rep.setElecDevRepId(rs.getInt("ElecDevRepId"));
				elecDev_Rep.setDevType(rs.getInt("DevType"));
				elecDev_Rep.setUserId(rs.getString("UserId"));
				elecDev_Rep.setAppBy(rs.getString("AppBy"));
				if (rs.getDate("AppDate") != null) {
					elecDev_Rep.setAppDate(rs.getDate("AppDate").toString());
				} else {
					elecDev_Rep.setAppDate("");
				}
				elecDev_Rep.setElecDevRepStaff(rs.getString("ElecDevRepStaff"));
				elecDev_Rep.setElecDevRepContent(rs
						.getString("ElecDevRepContent"));
				elecDev_Rep.setElecDevRepResult(rs
						.getString("ElecDevRepResult"));
				elecDev_Rep.setElecDevRepDate(rs.getDate("ElecDevRepDate")
						.toString());

				if (rs.getDate("MainToDate") != null) {
					elecDev_Rep.setMainToDate(rs.getDate("MainToDate")
							.toString());
				} else {
					elecDev_Rep.setMainToDate("");
				}

				elecDev_Rep.setReMark(rs.getInt("ReMark"));
				elecDev_Rep.setMemo(rs.getString("Memo"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			// DBConnection.close(rs);
			// DBConnection.close(pStatement);
			// DBConnection.close(connection);
			db.freeConnection();
		}

		return elecDev_Rep;

	}

	public boolean insertElecDev_Rep(ElecDev_RepTO elecDev_Rep)
			throws Exception {
		boolean flag = false;
		// Connection connection = null;
		DBPoolManager db = new DBPoolManager();
		db.getConnection();
		PreparedStatement pStatement = null;
		try {
			// connection = DBConnection.getConnection();
			// Prepare a statement to insert a record
			pStatement = db.conn.prepareStatement(CREATE_CUSTOMER_SQL);

			pStatement.setInt(1, elecDev_Rep.getDevType());
			pStatement.setString(2, elecDev_Rep.getUserId());
			pStatement.setString(3, elecDev_Rep.getAppBy());
			pStatement.setString(4, elecDev_Rep.getAppDate());
			pStatement.setString(5, elecDev_Rep.getElecDevRepStaff());
			pStatement.setString(6, elecDev_Rep.getElecDevRepContent());
			pStatement.setString(7, elecDev_Rep.getElecDevRepResult());
			pStatement.setString(8, elecDev_Rep.getElecDevRepDate());
			pStatement.setString(9, elecDev_Rep.getMainToDate());
			pStatement.setInt(10, elecDev_Rep.getReMark());
			pStatement.setString(11, elecDev_Rep.getMemo());

			int i = pStatement.executeUpdate();

			flag = i > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			// DBConnection.close(pStatement);
			// DBConnection.close(connection);
			db.freeConnection();
		}

		return flag;
	}

	public ArrayList searchElecDev_Rep(ElecDev_RepTO elecDev_Rep, String endDate)
			throws Exception {
		ArrayList arrayList = new ArrayList();
		// Connection connection = null;
		DBPoolManager db = new DBPoolManager();
		db.getConnection();
		Statement statement = null;
		ResultSet rs = null;
		StringBuffer criteriaSql = new StringBuffer(512);
		criteriaSql.append(SEARCH_CUSTOMER_SQL);

		if (elecDev_Rep.getElecDevRepId() != -1) {
			criteriaSql.append("ElecDevRepId=" + elecDev_Rep.getElecDevRepId()
					+ " AND ");
		}

		if (elecDev_Rep.getDevType() != -1) {
			criteriaSql.append("DevType=" + elecDev_Rep.getDevType() + " AND ");
		}

		if (!elecDev_Rep.getUserId().equals("")) {
			criteriaSql.append("UserId='" + elecDev_Rep.getUserId() + "' AND ");
		}

		if (elecDev_Rep.getElecDevRepDate() != null
				&& !elecDev_Rep.getElecDevRepDate().equals("")
				&& endDate != null && !endDate.equals("")) {
			criteriaSql.append("ElecDevRepDate between (to_date('"
					+ DBUtil.fixSqlFieldValue(elecDev_Rep.getElecDevRepDate())
					+ "','yyyy-mm-dd')) AND (to_date('"
					+ DBUtil.fixSqlFieldValue(endDate) + "','yyyy-mm-dd'))"
					+ " AND ");
		}

		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 e1.userid,elecdevrepdate");
		try {
			// connection = DBConnection.getConnection();
			statement = db.conn.createStatement();
			rs = statement.executeQuery(criteriaSql.toString());

			while (rs.next()) {
				ElecDev_RepTO elecDev_RepTO = new ElecDev_RepTO();
				elecDev_RepTO.setElecDevRepId(rs.getInt("ElecDevRepId"));
				elecDev_RepTO.setDevType(rs.getInt("DevType"));
				elecDev_RepTO.setUserId(rs.getString("UserId"));
				elecDev_RepTO.setAppBy(rs.getString("AppBy"));
				if(rs.getDate("AppDate") !=null){
					elecDev_RepTO.setAppDate(rs.getDate("AppDate").toString());
				}
				else
				{
					elecDev_RepTO.setAppDate("");
				}
			
				elecDev_RepTO.setElecDevRepStaff(rs
						.getString("ElecDevRepStaff"));
				elecDev_RepTO.setElecDevRepContent(rs
						.getString("ElecDevRepContent"));
				elecDev_RepTO.setElecDevRepResult(rs
						.getString("ElecDevRepResult"));
				elecDev_RepTO.setElecDevRepDate(rs.getDate("ElecDevRepDate")
						.toString());
				if (rs.getDate("MainToDate") != null) {
					elecDev_RepTO.setMainToDate(rs.getDate("MainToDate")
							.toString());
				} else {
					elecDev_RepTO.setMainToDate("");
				}
				elecDev_RepTO.setReMark(rs.getInt("ReMark"));
				elecDev_RepTO.setMemo(rs.getString("Memo"));
				elecDev_RepTO.setDevTypeName(rs.getString("Elecdevtypename"));

				arrayList.add(elecDev_RepTO);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			// DBConnection.close(rs);
			// DBConnection.close(statement);
			db.freeConnection();
		}

		return arrayList;
	}

	public boolean updateElecDev_Rep(ElecDev_RepTO elecDev_Rep)
			throws Exception {

		boolean flag = false;
		// Connection connection = null;
		DBPoolManager db = new DBPoolManager();
		db.getConnection();
		PreparedStatement pStatement = null;
		try {
			// connection = DBConnection.getConnection();
			// Prepare a statement to insert a record
			pStatement = db.conn.prepareStatement(UPDATE_CUSTOMER_SQL);
			pStatement.setInt(1, elecDev_Rep.getDevType());
			pStatement.setString(2, elecDev_Rep.getUserId());
			pStatement.setString(3, elecDev_Rep.getAppBy());
			pStatement.setString(4, elecDev_Rep.getAppDate());
			pStatement.setString(5, elecDev_Rep.getElecDevRepStaff());
			pStatement.setString(6, elecDev_Rep.getElecDevRepContent());
			pStatement.setString(7, elecDev_Rep.getElecDevRepResult());
			pStatement.setString(8, elecDev_Rep.getElecDevRepDate());
			pStatement.setString(9, elecDev_Rep.getMainToDate());
			pStatement.setInt(10, elecDev_Rep.getReMark());
			pStatement.setString(11, elecDev_Rep.getMemo());
			pStatement.setInt(12, elecDev_Rep.getElecDevRepId());
			
			int i = pStatement.executeUpdate();
			flag = i > 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			// DBConnection.close(pStatement);
			// DBConnection.close(connection);
			db.freeConnection();

		}

		return flag;

	}

}