dicoperate.java

包括从业人员信息录入等功能

package DIC;
import java.sql.*;
import java.util.*;
import DB.*;//连接数据库包

public class DicOperate {
    private String TableName="";//参数传进来的字典表表名
    private String SqlStr="";
    private Connection conn=null;  //连接
    private Statement stmt = null;
    private PreparedStatement prep = null;
    private DICBean dbn=null;
    /*构造函数*/
    public DicOperate() {
        dbn=new DICBean();
        OpenDB op= new OpenDB();
        if(op.openConnect()){
            conn = op.getConnection();
            try{
                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            }
            catch(Exception e){};
        }
    }
    /*设置、获取表名称*/
    public void setTableName(String TableName){
       this.TableName=TableName;
    }
    public String getTableName(String TableName){
       return TableName;
   }
/*---------------------------------查询方法------------------------------------ */
    public DICBean QryRcdByPK(String PKValue) {    //按主键查询一条记录
       SqlStr="select * from "+TableName+" where dic_code='"+PKValue+"'";
       try{
            ResultSet rs=stmt.executeQuery(SqlStr);
            if (rs.next()) {
                String DIC_CODE=rs.getString("DIC_CODE");
                if((DIC_CODE!=null)&&(!DIC_CODE.equals("")))  dbn.setDIC_CODE(DIC_CODE);

                String DIC_TEXT=rs.getString("DIC_TEXT");
                if((DIC_TEXT!=null)&&(!DIC_TEXT.equals("")))  dbn.setDIC_TEXT(DIC_TEXT);

                String DIC_SPELL=rs.getString("DIC_SPELL");
                if((DIC_SPELL!=null)&&(!DIC_SPELL.equals("")))dbn.setDIC_SPELL(DIC_SPELL);

                String DIC_ASPELL=rs.getString("DIC_ASPELL");
                if((DIC_ASPELL!=null)&&(!DIC_ASPELL.equals(""))) dbn.setDIC_ASPELL(DIC_ASPELL);
            }
            rs.close();
        }catch(Exception e){System.out.print(e);}
       return dbn;
    }
    public List QryRcdByValue(String Value) {    //全字段模糊查询
       SqlStr="select * from "+TableName+" where DIC_CODE like '%"+Value+"%' "+" or DIC_TEXT like '%"+Value+"%' "+" or DIC_SPELL like '%"+Value+"%' "+" or DIC_ASPELL like '%"+Value+"%' ";
       List records=new ArrayList();
       try{
            ResultSet rs=stmt.executeQuery(SqlStr);
            if (rs.next()) {
                rs.previous() ;
                while (rs.next()) {
                    DICBean tempbean = new DICBean();
                    String DIC_CODE=rs.getString("DIC_CODE");
                    if((DIC_CODE!=null)&&(!DIC_CODE.equals("")))  tempbean.setDIC_CODE(DIC_CODE);

                   String DIC_TEXT=rs.getString("DIC_TEXT");
                   if((DIC_TEXT!=null)&&(!DIC_TEXT.equals("")))  tempbean.setDIC_TEXT(DIC_TEXT);

                   String DIC_SPELL=rs.getString("DIC_SPELL");
                   if((DIC_SPELL!=null)&&(!DIC_SPELL.equals("")))tempbean.setDIC_SPELL(DIC_SPELL);

                   String DIC_ASPELL=rs.getString("DIC_ASPELL");
                   if((DIC_ASPELL!=null)&&(!DIC_ASPELL.equals(""))) tempbean.setDIC_ASPELL(DIC_ASPELL);

                 records.add(tempbean);
                }
            }
            rs.close();
        }catch(Exception e){}
       return records;
    }
    public int QryRcdCountsByValue(String Value){
        int counts=0;
        SqlStr="select count(DIC_CODE) counts from "+TableName+" where DIC_CODE like '%"+Value+"%' "+" or DIC_TEXT like '%"+Value+"%' "+" or DIC_SPELL like '%"+Value+"%' "+" or DIC_ASPELL like '%"+Value+"%' ";
        try{
             ResultSet rs=stmt.executeQuery(SqlStr);
             if (rs.next()) {
               rs.previous() ;
                while (rs.next()) {
                    counts = rs.getInt("counts");
                }
             }
             rs.close();
         }catch(Exception e){}
        return counts;
    }
    public List QryRcdByValue(String Value, int CurrentPage,int PageSize) {    //全字段模糊查询,分页显示
       //Value:作为查询条件的字段值，CurrentPage:当前的页码，PageSize:每页显示的记录数
       String SqlStr2="select * from "+TableName+" where DIC_CODE like '%"+Value+"%' "+" or DIC_TEXT like '%"+Value+"%' "+" or DIC_SPELL like '%"+Value+"%' "+" or DIC_ASPELL like '%"+Value+"%' ";
       SqlStr="select * from ( select rownum as my_rownum,table_a.* from (" + SqlStr2 +") table_a ";
       SqlStr=SqlStr+       " where rownum    <="+ String.valueOf(CurrentPage*PageSize)+") ";
       SqlStr=SqlStr+ "where my_rownum > "+String.valueOf((CurrentPage-1)*PageSize);
       List records=new ArrayList();
       try{
            ResultSet rs=stmt.executeQuery(SqlStr);
            if (rs.next()) {
                rs.previous() ;
                while (rs.next()) {
                    DICBean tempbean = new DICBean();
                    String DIC_CODE=rs.getString("DIC_CODE");
                    if((DIC_CODE!=null)&&(!DIC_CODE.equals("")))  tempbean.setDIC_CODE(DIC_CODE);

                   String DIC_TEXT=rs.getString("DIC_TEXT");
                   if((DIC_TEXT!=null)&&(!DIC_TEXT.equals("")))  tempbean.setDIC_TEXT(DIC_TEXT);

                   String DIC_SPELL=rs.getString("DIC_SPELL");
                   if((DIC_SPELL!=null)&&(!DIC_SPELL.equals("")))tempbean.setDIC_SPELL(DIC_SPELL);

                   String DIC_ASPELL=rs.getString("DIC_ASPELL");
                   if((DIC_ASPELL!=null)&&(!DIC_ASPELL.equals(""))) tempbean.setDIC_ASPELL(DIC_ASPELL);

                   records.add(tempbean);
                }
            }
            rs.close();
        }catch(Exception e){}
       return records;
    }
    //判断代码是否存在
    public boolean IsExistsCode(String DIC_Code){
        boolean bol=false;
        SqlStr="select * from "+TableName;
        SqlStr=SqlStr+" where DIC_CODE= '"+DIC_Code+"'";
        try{
             ResultSet rs=stmt.executeQuery(SqlStr);
             if (rs.next()) {
               bol=true;
             }
             rs.close();
         }catch(Exception e){}
        return bol;
    }
    //判断名称是否存在
    public boolean IsExistsText(String DIC_Text){
        boolean bol=false;
        SqlStr="select * from "+TableName;
        SqlStr=SqlStr+" where DIC_TEXT= '"+DIC_Text+"'";
        try{
             ResultSet rs=stmt.executeQuery(SqlStr);
             if (rs.next()) {
               bol=true;
             }
             rs.close();
         }catch(Exception e){}
        return bol;
    }

    //获取总记录条数
  public int getAllRcdCounts(){
     int Counts=0;
     try{
       ResultSet rs=stmt.executeQuery("select * from "+TableName);
       if (rs.next()){
        rs.last();
        Counts=rs.getRow();
       }
       rs.close();
     }catch(Exception e){}
     return Counts;
   }


    //用于测试时返回Sql语句
    public String getSqlStr(){
        return SqlStr;
    }
/*---------------------------------插入方法------------------------------------*/
    public boolean InsertDIC(DICBean dbn){
        boolean bol=false;
        SqlStr="insert into "+TableName+"(DIC_CODE,DIC_TEXT,DIC_SPELL,DIC_ASPELL)";
        SqlStr=SqlStr+" values(?,?,?,?)";
        try{
            prep = conn.prepareStatement(SqlStr);

            String DIC_CODE=dbn.getDIC_CODE();
            if(DIC_CODE==null) prep.setString(1, null);
            else prep.setString(1, dbn.getDIC_CODE());

            String DIC_TEXT=dbn.getDIC_TEXT();
            if(DIC_TEXT==null) prep.setString(2, null);
            else prep.setString(2, DIC_TEXT);

            String DIC_SPELL=dbn.getDIC_SPELL();
            if(DIC_SPELL==null) prep.setString(3, null);
            else prep.setString(3, DIC_SPELL);

            String DIC_ASPELL=dbn.getDIC_ASPELL();
            if(DIC_ASPELL==null) prep.setString(4, null);
            else prep.setString(4, DIC_ASPELL);
            prep.execute();
            prep.close();
            bol=true;
        }catch(Exception e){}
        return bol;
    }
/*---------------------------------修改方法------------------------------------*/
    public boolean UpdateDIC(DICBean dbn){
        boolean bol=false;
        String DIC_TEXT=dbn.getDIC_TEXT();
        if(DIC_TEXT==null)DIC_TEXT="";

        String DIC_SPELL=dbn.getDIC_SPELL();
        if(DIC_SPELL==null)DIC_SPELL="";

        String DIC_ASPELL=dbn.getDIC_ASPELL();
        if(DIC_ASPELL==null)DIC_ASPELL="";

        SqlStr="update "+TableName+" set ";
        SqlStr=SqlStr+"DIC_TEXT='"+DIC_TEXT+"',";
        SqlStr=SqlStr+"DIC_SPELL='"+DIC_SPELL+"',";
        SqlStr=SqlStr+"DIC_ASPELL='"+DIC_ASPELL+"'";
        SqlStr=SqlStr+" where DIC_CODE= '"+dbn.getDIC_CODE()+"'";
        try{
            stmt.execute(SqlStr);
            bol=true;
        }catch(Exception e){}
        return bol;
    }
/*---------------------------------删除方法------------------------------------*/
    public boolean DeleteDIC(String PKValue){
        boolean bol=false;
        SqlStr="delete "+TableName;
        SqlStr=SqlStr+" where DIC_CODE= '"+PKValue+"'";
        try{
            stmt.execute(SqlStr);
            bol=true;
        }catch(Exception e){}
        return bol;
    }
    //关闭数据库连接
    public void close()
      {
          try{
              this.conn.close();
          }catch(Exception e){}
  }
/*
    public static void main(String args[]){
        //测试用
    }
*/
}