regbeanoperator.java

基于strurs架构+servlet+jsp+javabean架构科研管理系统

/*
 * Created on 2008-6-8
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package cn.edu.zucc.research.ejb.bmp.reg;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import javax.ejb.EJBException;
import javax.ejb.NoSuchEntityException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * @author chenfang
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class RegBeanOperator {

    public Integer resId;

    public String resNum;

    public String resName;

    public String categoryId;

    public String uploadStaff;

    public Date checkDate;

    public String checkStaff;

    public String checkRemark;

    public Connection con;

    public String dbName = "java:ResearchDB";

    public void makeConnection() throws NamingException, SQLException {

        InitialContext ic = new InitialContext();
        DataSource ds = (DataSource) ic.lookup(dbName);
        con = ds.getConnection();
    }

    public void closeConnection() throws SQLException {
        this.con.close();
    }

    public Integer insertRow(String resNum, String resName, String categoryId,
            String uploadStaff, Date checkDate, String checkStaff,
            String checkRemark) throws SQLException {
       
        try {
            this.makeConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        String insertStatement = "insert into researchReg(resNum,resName,categoryId,uploadStaff" +
        		",checkDate,checkStaff,checkRemark) values ( ? , ? , ? , ? , ? , ? , ?)";
        PreparedStatement prepStmt = con.prepareStatement(insertStatement);
        
        prepStmt.setString(1, resNum);
        prepStmt.setString(2, resName);
        prepStmt.setString(3, categoryId);
        prepStmt.setString(4, uploadStaff);
        if(checkDate==null)
            prepStmt.setDate(5,null);
        else
            prepStmt.setDate(5, new java.sql.Date(checkDate.getTime()));
        prepStmt.setString(6, checkStaff);
        prepStmt.setString(7, checkRemark);

        prepStmt.executeUpdate();prepStmt.close();
        
        String selectStatement = "select resId "
                + "from researchReg where resNum = ? ";
        prepStmt = con.prepareStatement(selectStatement);
        prepStmt.setString(1, resNum);
        ResultSet rs = prepStmt.executeQuery();
        while(rs.next()){
        resId=(Integer)rs.getObject(1);
        System.out.println("id "+resId);}
        prepStmt.close();
        this.closeConnection();
        return resId;
    }

    public void deleteRow(Integer id) throws SQLException {

        try {
            this.makeConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        String deleteStatement = "delete from researchReg where resId = ? ";
        PreparedStatement prepStmt = con.prepareStatement(deleteStatement);

        prepStmt.setObject(1, id);
        prepStmt.executeUpdate();
        prepStmt.close();
        this.closeConnection();
    }
    public void loadRow() throws SQLException {

        try {
            this.makeConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        String selectStatement = "select resNum,resName,categoryId,uploadStaff,checkDate,checkStaff,checkRemark "
                + "from researchReg where resId = ? ";
        PreparedStatement prepStmt = con.prepareStatement(selectStatement);

        prepStmt.setObject(1, this.resId);

        ResultSet rs = prepStmt.executeQuery();

        if (rs.next()) {
            this.resNum = rs.getString(1);
            this.resName = rs.getString(2);
            this.categoryId = rs.getString(3);
            this.uploadStaff = rs.getString(4);
            this.checkDate =(java.util.Date) rs.getObject(5);
            this.checkStaff = rs.getString(6);
            this.checkRemark = rs.getString(7);
           
            prepStmt.close();
            this.closeConnection();
        } else {
            prepStmt.close();
            this.closeConnection();
            throw new NoSuchEntityException("Row for id " + resId
                    + " not found in database.");
        }
    }

    public void storeRow() throws SQLException {

        try {
            this.makeConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        String updateStatement = "update researchReg set resNum =  ? ,"
                + "resName = ? , categoryId = ? ,uploadStaff = ?," +
                		"checkDate = ?,checkStaff = ?,checkRemark = ? "
                + "where resId = ?";
        PreparedStatement prepStmt = con.prepareStatement(updateStatement);

        prepStmt.setString(1, resNum);
        prepStmt.setString(2, resName);
        prepStmt.setString(3, categoryId);
        prepStmt.setString(4, uploadStaff);
        //prepStmt.setObject(5, checkDate);
        if(checkDate==null)
            prepStmt.setDate(5,null);
        else
            prepStmt.setDate(5, new java.sql.Date(checkDate.getTime()));
        prepStmt.setString(6, checkStaff);
        prepStmt.setString(7, checkRemark);
        prepStmt.setObject(8, resId);
        int rowCount = prepStmt.executeUpdate();
        prepStmt.close();
        this.closeConnection();

        if (rowCount == 0) {
            throw new EJBException("Storing row for id " + resId + " failed.");
        }
    }

    public Collection selectAll() throws SQLException {
        try {
            this.makeConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        String selectStatement = "select resId " + "from researchReg";
        PreparedStatement prepStmt = con.prepareStatement(selectStatement);

        ResultSet rs = prepStmt.executeQuery();
        ArrayList a = new ArrayList();

        while (rs.next()) {
            Integer resId = (Integer)rs.getObject(1);
            a.add(resId);
        }

        prepStmt.close();
        this.closeConnection();
        return a;
    }

    public boolean selectByPrimaryKey(Integer primaryKey) throws SQLException {
        try {
            this.makeConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        String selectStatement = "select resId "
                + "from researchReg where resId = ? ";
        PreparedStatement prepStmt = con.prepareStatement(selectStatement);
        prepStmt.setObject(1, primaryKey);

        ResultSet rs = prepStmt.executeQuery();
        boolean result = rs.next();
        prepStmt.close();
        this.closeConnection();
        return result;
    }

    public Collection selectByResNum(String resNum) throws SQLException {
        try {
            this.makeConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        String selectStatement = "select resId "
                + "from researchReg where resNum = ? ";
        PreparedStatement prepStmt = con.prepareStatement(selectStatement);
        prepStmt.setString(1, resNum);
        ResultSet rs = prepStmt.executeQuery();
        ArrayList a = new ArrayList();

        while (rs.next()) {
            Integer resId = (Integer)rs.getObject(1);
            a.add(resId);
        }

        prepStmt.close();
        this.closeConnection();
        return a;
    }

    public Collection selectBySeaNum(String propertyName, Object value,
            String id) throws SQLException {
        try {
            this.makeConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        String selectStatement = "select reg.resId "
                + "from researchReg as reg,researchDetail as detail "
                + "where reg.resNum=detail.resNum and detail.responsible=? and reg.resNum like ?";
        PreparedStatement prepStmt = con.prepareStatement(selectStatement);
        prepStmt.setString(1, id);
        String num = (String) value;
        num = "%" + num + "%";
        prepStmt.setString(2, num);
        ResultSet rs = prepStmt.executeQuery();
        ArrayList a = new ArrayList();

        while (rs.next()) {
            Integer resId = (Integer)rs.getObject(1);
            a.add(resId);
        }

        prepStmt.close();
        this.closeConnection();
        return a;
    }

    public Collection selectBySeaName(String propertyName, Object value,
            String id) throws SQLException {
        try {
            this.makeConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        String selectStatement = "select reg.resId "
                + "from researchReg as reg,researchDetail as detail "
                + "where reg.resNum=detail.resNum and detail.responsible=? and reg.resName like ?";
        PreparedStatement prepStmt = con.prepareStatement(selectStatement);
        prepStmt.setString(1, id);
        String num = (String) value;
        num = "%" + num + "%";
        prepStmt.setString(2, num);
        ResultSet rs = prepStmt.executeQuery();
        ArrayList a = new ArrayList();

        while (rs.next()) {
            Integer resId = (Integer)rs.getObject(1);
            a.add(resId);
        }

        prepStmt.close();
        this.closeConnection();
        return a;
    }

    public Collection selectFeasibility(String timeone, String timetwo)
            throws SQLException {
        try {
            this.makeConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        String selectStatement = "select reg.resId "
                + "from researchReg as reg,researchDetail as rd where "
                + " reg.resNum=rd.resNum and reg.checkDate is not null and"
                + " (rd.isOver is null or (rd.isOver between ? and ? )) and reg.checkDate<?";
        PreparedStatement prepStmt = con.prepareStatement(selectStatement);

        prepStmt.setString(1, timeone);
        prepStmt.setString(2, timetwo);
        prepStmt.setString(3, timetwo);
        ResultSet rs = prepStmt.executeQuery();
        ArrayList a = new ArrayList();

        while (rs.next()) {
            Integer resId = (Integer)rs.getObject(1);
            a.add(resId);
        }

        prepStmt.close();
        this.closeConnection();
        return a;
    }

    public Collection selectNotChecked() throws SQLException {
        try {
            this.makeConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        String selectStatement = "select resId "
                + "from researchReg where checkDate is null";
        PreparedStatement prepStmt = con.prepareStatement(selectStatement);

        ResultSet rs = prepStmt.executeQuery();
        ArrayList a = new ArrayList();

        while (rs.next()) {
            Integer resId = (Integer)rs.getObject(1);
            a.add(resId);
        }

        prepStmt.close();
        this.closeConnection();
        return a;
    }

    public Collection selectChecked() throws SQLException {
        try {
            this.makeConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        String selectStatement = "select resId "
                + "from researchReg where checkDate is not null";
        PreparedStatement prepStmt = con.prepareStatement(selectStatement);

        ResultSet rs = prepStmt.executeQuery();
        ArrayList a = new ArrayList();

        while (rs.next()) {
            Integer resId = (Integer)rs.getObject(1);
            a.add(resId);
        }

        prepStmt.close();
        this.closeConnection();
        return a;
    }

}