bboss persistent 实现数据库查询操作

4.4 查询

4.4.1普通查询操作代码段

   

    DBUtil dbUtil = new DBUtil();

       String sqlstr = "SELECT RECORD, OPERTIME, ACTION_ID, " +

           "ACTDOCINFO_ID,activityID FROM OFFICE_DOCACTION WHERE ACTDOCINFO_ID = " + docInfoId;

       System.out.println("sqlstr ======" + sqlstr);

       try {

           dbUtil.executeSelect(sqlstr);

           if (dbUtil.size()>0){

              for(int i=0;i<dbUtil.size();i++){

                  Action action = new Action();

                  action.setRecord(dbUtil.getString(i,"RECORD"));

                  action.setOperTime((Timestamp)dbUtil.getDate(i,"OPERTIME"));

                  action.setActionId(dbUtil.getInt(i,"ACTION_ID"));

                  action.setDocInfoId(dbUtil.getInt(i,"ACTDOCINFO_ID"));

                  action.setActivityID(dbUtil.getString(i,"activityID"));              

                  actionList.add(i,action);

              }

           }

           else{

              System.out.println("在OFFICE_DOCACTION表中没有找到docInfoId=" + docInfoId + "的操作记录");

              return null;

           }

       } catch (SQLException e) {

           e.printStackTrace();

           throw new DataAccessException("",e);

       }

       return actionList;

4.4.2普通分页查询操作

      

           public ListInfo test(int offset, int pageItem)

    {

        DBUtil util = new DBUtil();

        String  select = "select * from a";

        try

        {

            util.executeSelect(select,offset,pageItem);

            Action action = null;

            List list = new ArrayList();

            for(int i = 0; i < util.size(); i ++)

            {

               action = new Action();

               action.setName(util.getString(i,"name"));

               .....

               list.add(action);

            }

            ListInfo listInfo = new ListInfo();

            listInfo.setDatas(list);

            listInfo.setItems(util.getTotalSize());

            return listInfo;

        }

        catch(SQLException e)

        {

            return null;

        }

       

    }

 

4.4.3分块查询操作

   

    //分块查询和一般的查询没什么区别，只是在查询数据库时安批来获取从数据库中数据每批记录数为fetchsize,

    //当前获取的一批数据使用完后，在继续获取下一批数据,这对查询海量数据表时特别有用

    DBUtil util = new DBUtil();

    String  select = "select * from a";

   

    int fetchsize = 100;

    try

    {     

       util.executeSelect(select,fetchsize);

       for(int i = 0; i < util.size(); i ++)

       {

           //遍历数据

          

           .....

       }

      

    }

    catch(SQLException e)

    {

       return null;

    }

   

}

4.4.4针对oracle高效分页查询操作

    在指定dbName的数据库中执行分页查询操作，本方法利用oracle自身提供的分页机制，提升oracle的高效分页查询

    获取高效的oracle分页语句，sql中已经写好ROW_NUMBER() OVER ( ORDER   BY   cjrq ) rownum

    否则不能调用本方法生成oralce的分页语句,其中rownum就对应了本方法的参数rownum

   String sql = "select name,row_number() over (order by id,name) rownum_ from test";

     DBUtil dbUtil = new DBUtil();

     dbUtil.executeSelectForOracle("bspf",sql,offset,maxsize,"rownum_");//在指定的数据库上执行

dbUtil.executeSelectForOracle(sql,offset,maxsize,"rownum_");//在缺省的数据库中执行

      .....

     dbUtil.size();

      dbUtil.getTotalSize();

 

 

4.4.4针对oracle高效预编译分页查询操作

    在指定dbName的数据库中执行分页查询操作，本方法利用oracle自身提供的分页机制，提升oracle的高效分页查询

    获取高效的oracle分页语句，sql中已经写好ROW_NUMBER() OVER ( ORDER   BY   cjrq ) rownum

    否则不能调用本方法生成oralce的分页语句,其中rownum就对应了本方法的参数rownum

   String sql = "select name,row_number() over (order by id,name) rownum_ from test where name=?";

     PreparedDBUtil dbUtil = new PreparedDBUtil();

     dbUtil.preparedSelect(“bspf”,sql ,offset,pagesize,”rownum_”) ; //在指定的数据库上执行

dbUtil.preparedSelect(sql ,offset,pagesize,”rownum_”) ; //在默认的数据库上执行

pdb.setString(1,"biaoping.yin1");

 

     dbUtil.executePrepared();

     .....

     dbUtil.size();

      dbUtil.getTotalSize();

 

4.4.5预编译查询

String sql = "select name from test where name=?";

           PreparedDBUtil pdb = new PreparedDBUtil();

           try {

              pdb.preparedSelect(sql);

              pdb.setString(1,"biaoping.yin1");

              pdb.executePrepared();

             

              System.out.println(pdb.size());

              System.out.println(pdb.getString(0,"name"));

             

             

           } catch (SQLException e) {

          

              e.printStackTrace();

                     }

4.4.6通用预编译分页查询

String sql = "select name from test where name=?";

           PreparedDBUtil pdb = new PreparedDBUtil();

           try {

              int offset = 10; //分页的起始位置

                 int pagesize = 10;//每页的显示最多记录条数

              pdb.preparedSelect(sql, offset, pagesize);//缺省数据库中执行分页查询操作

//pdb.preparedSelect(‘query’,sql, offset, pagesize);//指定数据库中执行分页查询操作

              pdb.setString(1,"biaoping.yin1");//设置分页参数

              pdb.executePrepared();//执行

             

              System.out.println(pdb.size());//获取当页获取到的记录条数

              System.out.println(pdb.getTotalSize());//获取到的总记录条数

              System.out.println(pdb.getString(0,"name")); //获取记录中字段的值

             

             

           } catch (SQLException e) {

          

              e.printStackTrace();

           }

4.4.7查询top n条记录

String sql = "select name from test where name=?";

           DBUtil pdb = new DBUtil();

           try {

              pdb. executeSelectLimit(sql, 100);//在缺省的数据库上查询top 100条记录

              //pdb. executeSelectLimit(“bspf”,sql, 100);//在指定的数据库上查询top 100条记录

 

             

             

              System.out.println(pdb.size());//获取当页获取到的记录条数

              System.out.println(pdb.getString(0,"name")); //获取记录中字段的值

             

             

           } catch (SQLException e) {

          

              e.printStackTrace();

           }

4.4.8针对oracle高效查询top n条记录

String sql = " select name,row_number() over (order by id,name) rownum_ from test ";

           DBUtil pdb = new DBUtil();

           try {

              pdb. executeSelectLimitForOracle (sql, 100,”rownum_”);//在缺省的数据库上查询top 100条记录

              //pdb. executeSelectLimitForOracle (“bspf”,sql, 100, ”rownum_”);//在指定的数据库上查询top 100条记录

              System.out.println(pdb.size());//获取当页获取到的记录条数

              System.out.println(pdb.getString(0,"name")); //获取记录中字段的值

             

             

           } catch (SQLException e) {

          

              e.printStackTrace();

           }

4.4.7查询clob字段的值

void prepareDefaultSelectClob()

       {

           for(int i = 0; i < 1; i ++)

           {

              PreparedDBUtil p = new PreparedDBUtil();

             

             

              try {

                 

             

                  p.preparedSelect("select name,content from test where name=? ");

                  p.setString(1,"biaoping.yin1");

                 

                  p.executePrepared();

                  for(int I = 0; I < p.size(); I ++)

                  {

                     String content = p.getString(I,”content”);

                  }

              } catch (SQLException e) {

                  // TODO Auto-generated catch block

                  e.printStackTrace();

              } catch (Exception e) {

                  // TODO Auto-generated catch block

                  e.printStackTrace();

              }

           }

                    

                    

              }

4.4.8查询blob字段的值

void prepareDefaultSelectClob()

       {

           for(int i = 0; i < 1; i ++)

           {

              PreparedDBUtil p = new PreparedDBUtil();

             

             

              try {

                 

             

                  p.preparedSelect("select name,content from test where name=? ");

                  p.setString(1,"biaoping.yin1");

                 

                  p.executePrepared();

                  for(int I = 0; I < p.size(); I ++)

                  {

File shark = new File("d:/workspace/shark-222.zip");

                    

p.getFile(I,shark);

                  }

              } catch (SQLException e) {

                  // TODO Auto-generated catch block

                  e.printStackTrace();

              } catch (Exception e) {

                  // TODO Auto-generated catch block

                  e.printStackTrace();

              }

           }

                    

                    

              }

 

4.4.9预编译查询和普通查询的不同点

jdbc规范中提供了两种不同的查询操作：预编译和普通两种。

和插入操作一样，预编译查询的效率比普通查询的效率要高一些，预编译查询能够有效地防止sql注入问题，编码和调试没有普通查询那么方便，综合各种因素推荐使用预编译查询操作