registrationdao.java

java struts source code

package  emptyprj;


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.util.HashMap;

import javawebstudio.struts_db.ConnectionPool;
import javawebstudio.struts_db.NetForm;

public class registrationDAO {

    private Connection con;
    private ConnectionPool pool;
    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='toregistrationAction.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 registrationDAO() 
  {
       pool = ConnectionPool.getInstance();
       try 
       {
           con = pool.getConnection();
        } 
	    catch (SQLException e) 
        {
      		e.printStackTrace();
      		throw new RuntimeException("Unable to get connection.");
       } 
   }
 
     public void Close() 
    {
           try 
            {
                  if (con != null) con.close();
            } 
            catch (SQLException e) 
            {
            	    throw new RuntimeException(e.getMessage());
            }
     }


  public void create(NetForm m_registration) throws SQLException {
    //需要特别注意，如果表中包含有“自动生成序号”的字段，下面“？”变量中需要手工去掉“自动生成序号”字段对应的“？”和变量，并重新排列ps插入变量的序号！！
    PreparedStatement ps = null;
    //<createSQL>
    String sql = "INSERT INTO users 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.setInt(1,m_registration.getInt("ID"));
      ps.setString(2,m_registration.getString("username"));
      ps.setString(3,m_registration.getString("password"));
      ps.setString(4,m_registration.getString("email"));
      ps.setString(5,m_registration.getString("role"));
      ps.setInt(6,m_registration.getInt("topics"));
      ps.setInt(7,m_registration.getInt("reverts"));
      ps.setInt(8,m_registration.getInt("grades"));
      //</create>

      if (ps.executeUpdate() != 1) {
        throw new SQLException ("error.create.registration");
      }
    } 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(NetForm m_registration,String keyID) {
    //需要特别注意，如果表中包含有“自动生成序号”的字段，下面“？”变量中需要手工去掉“自动生成序号”字段对应的“？”和变量，并重新排列ps插入变量的序号！！
    PreparedStatement ps = null;
    //<updateSQL>
   String sql = "UPDATE users SET ID = ?,username = ?,password = ?,email = ?,role = ?,topics = ?,reverts = ?,grades = ? WHERE ID = ?";
      //</updateSQL>
    try {
      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }
       //用SQL Server请加入下行: 
      con.setAutoCommit(true);   //如果是MySQL，请注解掉这一句
      //SQL Server
      ps = con.prepareStatement(sql);
      //<update>
      ps.setInt(1,m_registration.getInt("ID"));
      ps.setString(2,m_registration.getString("username"));
      ps.setString(3,m_registration.getString("password"));
      ps.setString(4,m_registration.getString("email"));
      ps.setString(5,m_registration.getString("role"));
      ps.setInt(6,m_registration.getInt("topics"));
      ps.setInt(7,m_registration.getInt("reverts"));
      ps.setInt(8,m_registration.getInt("grades"));
      //</update>

      ///////////////
      //<updatekeyID>
      ps.setInt(9,java.lang.Integer.parseInt(keyID,10));
          //</updatekeyID>


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

    } 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.registration");
      }

    } 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 users WHERE ";
    sql+="ID";
    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.setInt(1,java.lang.Integer.parseInt(keyID,10));
          //</setremovekeyIDdata>


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

    } 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 NetForm findByPrimaryKey(String keyID)
    throws SQLException {

    
    PreparedStatement ps = null;
    ResultSet rs = null;
    NetForm m_registration= new NetForm();

    //<findByPrimaryKeySQL>
   String sql = "SELECT * from users  WHERE ID = ?";
      //</findByPrimaryKeySQL>
    try {

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

      ps = con.prepareStatement(sql);
      //<setfindkeyIDdata>
      ps.setInt(1,java.lang.Integer.parseInt(keyID,10));
          //</setfindkeyIDdata>
      rs = ps.executeQuery();

      if (rs.next()) {
            
        //<findByPrimaryKey>
      m_registration.setInt("ID",rs.getInt(1));
      m_registration.setString("username",rs.getString(2));
      m_registration.setString("password",rs.getString(3));
      m_registration.setString("email",rs.getString(4));
      m_registration.setString("role",rs.getString(5));
      m_registration.setInt("topics",rs.getInt(6));
      m_registration.setInt("reverts",rs.getInt(7));
      m_registration.setInt("grades",rs.getInt(8));
      //</findByPrimaryKey>

        return m_registration;
      } else {
        throw new SQLException (
          "error.removed.registration");
      }

    } 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++) {           
        NetForm m_registration= new NetForm(); 
        //<find>
      m_registration.setInt("ID",rs.getInt(1));
      m_registration.setString("username",rs.getString(2));
      m_registration.setString("password",rs.getString(3));
      m_registration.setString("email",rs.getString(4));
      m_registration.setString("role",rs.getString(5));
      m_registration.setInt("topics",rs.getInt(6));
      m_registration.setInt("reverts",rs.getInt(7));
      m_registration.setInt("grades",rs.getInt(8));
      //</find>

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

      }

      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");
      }
    }
  }

}