testdao.java

使用数据库存储过程的STRUTS应用程序.本程序在SQLSERVER数据库中建立两个数据库关联查询的存储过程,并建立了一个可以调用该过程的数据库应用程序,通过DAO进行存储,结果返回到Action中,

package  emptyprj;
import emptyprj.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.Collection;
import java.util.ArrayList;

////
import java.sql.CallableStatement;

public class TestDAO {

    private Connection con;
    private int rowCount;
    private int pageCount;
    private int length;      
    private String pagestr;
    public int getLength()  {  return (this.length);   }
    public void setLength(int length)  { this.length=length; }  
    public String getPagestr(int ipage)
    {
           String strPage="";
     	    if(getLength()>0)
     	    {
     	 	    strPage+="共";
     	 	    strPage+=String.valueOf(rowCount);
     	 	    strPage+="条记录，共";
     	 	    strPage+=String.valueOf(pageCount);
     	 	    strPage+="页，当前是第";
     	 	    strPage+=String.valueOf(ipage);
     	 	    strPage+="页，      ";
     	 	
     	 	    int istart,iend;
     	 	    istart=ipage-5;
     	 	    if(istart<0) {istart=0;}
     	 	    iend=istart+10;
     	 	    if(iend>pageCount) {iend=pageCount;}
     	 	    istart=iend-10;
     	 	    if(istart<0) {istart=0;}
     	 	    for(int i=istart;i<iend;i++)
     	 	    {
     	 	        strPage+="<a href='TestAction.do?action=find&search=search&page=";
     	 	        strPage+=String.valueOf(i+1);
     	 	        strPage+="'>";
     	 	        strPage+=String.valueOf(i+1);
     	 	        strPage+="</a>"; 
     	 	        strPage+="  ";
     	 	    }
     	 }
     	 this.pagestr=strPage;
     	 return strPage;
  }

  public TestDAO(Connection con) {
    this.con = con;
  }

  public void create(Test m_Test) throws SQLException {
    //需要特别注意，如果表中包含有“自动生成序号”的字段，下面“？”变量中需要手工去掉“自动生成序号”字段对应的“？”和变量，并重新排列ps插入变量的序号！！
    PreparedStatement ps = null;
    //<createSQL>
    String sql = "INSERT INTO dbo.员工奖金 VALUES (?,?,?,?,?)";
    //</createSQL>
    try {
      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }
       //用SQL Server请加入下行: 
      con.setAutoCommit(true);  //如果是MySQL，请注解掉这一句
      //SQL Server
      ps = con.prepareStatement(sql);
      //<create>
      ps.setString(1,m_Test.get姓名());
      ps.setString(2,m_Test.get性别());
      ps.setInt(3,m_Test.get年龄());
      ps.setString(4,m_Test.get时间());
      ps.setFloat(5,m_Test.get奖金());
      //</create>

      if (ps.executeUpdate() != 1) {
        throw new SQLException ("error.create.Test");
      }
    } catch (SQLException e) {
     
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      
    } finally {
      try {
        if (ps != null)
          ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }
  }

  public void update(Test m_Test,String keyID) {
    //需要特别注意，如果表中包含有“自动生成序号”的字段，下面“？”变量中需要手工去掉“自动生成序号”字段对应的“？”和变量，并重新排列ps插入变量的序号！！
    PreparedStatement ps = null;
    //<updateSQL>
   String sql = "UPDATE dbo.员工奖金 SET 姓名 = ?,性别 = ?,年龄 = ?,时间 = ?,奖金 = ? WHERE 姓名 = ?";
      //</updateSQL>
    try {
      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }
       //用SQL Server请加入下行: 
      con.setAutoCommit(true);   //如果是MySQL，请注解掉这一句
      //SQL Server
      ps = con.prepareStatement(sql);
      //<update>
      ps.setString(1,m_Test.get姓名());
      ps.setString(2,m_Test.get性别());
      ps.setInt(3,m_Test.get年龄());
      ps.setString(4,m_Test.get时间());
      ps.setFloat(5,m_Test.get奖金());
      //</update>

      ///////////////
      //<updatekeyID>
      ps.setString(6,keyID);
          //</updatekeyID>


      if (ps.executeUpdate() != 1) {
        throw new SQLException (
          "error.removed.Test");
      }

    } catch (SQLException e) {
      e.printStackTrace();
      throw new RuntimeException("error.unexpected");
    } finally {
      try {
        if (ps != null)
          ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }
  }
  public void remove(String sql) {
    PreparedStatement ps = null;
    try {

      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }
       //用SQL Server请加入下行: 
      con.setAutoCommit(true);  //如果是MySQL，请注解掉这一句
      //SQL Server
      ps = con.prepareStatement(sql);
   
      if (ps.executeUpdate() != 1) {
        throw new SQLException (
          "error.removed.Test");
      }

    } catch (SQLException e) {
      e.printStackTrace();
      throw new RuntimeException("error.unexpected");
    } finally {
      try {
        if (ps != null)
          ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }
  }

 public void removeID(String keyID) {
   
    //<removeIDSQL>
    String sql="DELETE FROM dbo.员工奖金 WHERE ";
    sql+="姓名";
    sql+=" = ?";
    //</removeIDSQL>

    PreparedStatement ps = null;
    try {

      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }
       //用SQL Server请加入下行: 
      con.setAutoCommit(true);  //如果是MySQL，请注解掉这一句
      //SQL Server
      ps = con.prepareStatement(sql);
      
      //<setremovekeyIDdata>
      ps.setString(1,keyID);
          //</setremovekeyIDdata>


   
      if (ps.executeUpdate() != 1) {
        throw new SQLException (
          "error.removed.Test");
      }

    } catch (SQLException e) {
      e.printStackTrace();
      throw new RuntimeException("error.unexpected");
    } finally {
      try {
        if (ps != null)
          ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }
  }


  public Test findByPrimaryKey(String keyID)
    throws SQLException {

    
    PreparedStatement ps = null;
    ResultSet rs = null;
    Test m_Test= null;

    //<findByPrimaryKeySQL>
   String sql = "SELECT * from dbo.员工奖金  WHERE 姓名 = ?";
      //</findByPrimaryKeySQL>
    try {

      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }

      ps = con.prepareStatement(sql);
      //<setfindkeyIDdata>
      ps.setString(1,keyID);
          //</setfindkeyIDdata>
      rs = ps.executeQuery();

      if (rs.next()) {
        m_Test= new Test();
      
        //<findByPrimaryKey>
      m_Test.set姓名(rs.getString(1));
      m_Test.set性别(rs.getString(2));
      m_Test.set年龄(rs.getInt(3));
      m_Test.set时间(rs.getString(4));
      m_Test.set奖金(rs.getFloat(5));
      //</findByPrimaryKey>

        return m_Test;
      } else {
        throw new SQLException (
          "error.removed.Test");
      }

    } catch (SQLException e) {
      e.printStackTrace();
      throw new RuntimeException("error.unexpected");
    } finally {
      try {
              if (rs != null)
                 rs.close();
              if (ps != null)
                 ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }
  }

  public Collection findSQL(String sql,int ipage) {
    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList list = new ArrayList();

    try {
      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }
      ps = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
      rs = ps.executeQuery();
          rs.absolute(-1);
      rowCount=rs.getRow();
     
      int offset=1;
      int pagesize=getLength();
      if(getLength()<1)
      {
          pagesize=rowCount;
          pageCount=1;
      }
      else
      {
          pageCount=rowCount/getLength()+((rowCount%getLength())>0?1:0);
          offset=(ipage-1)*getLength()+1;
          if(offset<1)offset=1;
          if(offset>rowCount)offset=rowCount;        
         
      } 
      rs.absolute(offset);
      for(int i=0;i<pagesize&&offset<rowCount+1;i++,offset++) {           
        Test m_Test= new Test(); 
        //<find>
      m_Test.set姓名(rs.getString(1));
      m_Test.set性别(rs.getString(2));
      m_Test.set年龄(rs.getInt(3));
      m_Test.set时间(rs.getString(4));
      m_Test.set奖金(rs.getFloat(5));
      //</find>

        rs.next();
        list.add(m_Test);

      }

      return list;

    } catch (SQLException e) {
      //e.printStackTrace();
      //throw new RuntimeException("error.unexpected");
       return list;
    } finally {
      try {
              if (rs != null)
                 rs.close();
              if (ps != null)
                 ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }
  }
  
  
  ////////////存储过程调用测试
   public Collection proc(String sql,int ipage) {
    CallableStatement ps = null;
    ResultSet rs = null;
    ArrayList list = new ArrayList();

    try {
      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }
      ps = con.prepareCall(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
      rs = ps.executeQuery();
      rs.absolute(-1);
      rowCount=rs.getRow();
     
      int offset=1;
      int pagesize=getLength();
      if(getLength()<1)
      {
          pagesize=rowCount;
          pageCount=1;
      }
      else
      {
          pageCount=rowCount/getLength()+((rowCount%getLength())>0?1:0);
          offset=(ipage-1)*getLength()+1;
          if(offset<1)offset=1;
          if(offset>rowCount)offset=rowCount;        
         
      } 
      rs.absolute(offset);
      for(int i=0;i<pagesize&&offset<rowCount+1;i++,offset++) {           
        Test m_Test= new Test(); 
        //<find>
      m_Test.set姓名(rs.getString(1));
      m_Test.set性别(rs.getString(2));
      m_Test.set年龄(rs.getInt(3));
      m_Test.set时间(rs.getString(4));
      m_Test.set奖金(rs.getFloat(5));
      //</find>

        rs.next();
        list.add(m_Test);

      }

      return list;

    } catch (SQLException e) {
      //e.printStackTrace();
      //throw new RuntimeException("error.unexpected");
       return list;
    } finally {
      try {
              if (rs != null)
                 rs.close();
              if (ps != null)
                 ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }
  }

}