sql for paging notice board in jsp

by g.dev   Last Updated November 09, 2018 01:26 AM

I'm trying to make a notice board and before apply paging function it showed posts well.

but after i applied paging fuction it shows only one post and paging function shows pages well.

so i think this is a sql problem. when i try to show count it shows 1. even i tried with this shows 1.

sql = "select * from mvc_board ";

this is my sql

 String sql = "SELECT ROWNUM, bId, bName, bTitle, bContent," +
               "bDate, bHit, bGroup, bStep, bIndent " +
               "from(select * from mvc_board order by bGroup DESC , bStep asc)";

i tried to make mine from this sql. but couldn't understand.

sql = "select *, (select u_name from user where idx = writer_fk) writer, (select idx from answer where idx = answer_fk) answer from board order by idx desc limit "+startRow+", "+endRow;

ref. http://queserasera.tistory.com/14

this is DAO

  public ArrayList<BDto> getBoardList(int startRow, int endRow, String keyField, String keyWord) {
        ArrayList<BDto> dtos = new ArrayList<BDto>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "SELECT ROWNUM, bId, bName, bTitle, bContent," +
               "bDate, bHit, bGroup, bStep, bIndent " +
               "from(select * from mvc_board order by bGroup DESC , bStep asc)";
       System.out.println(sql);
         try{
            if(keyWord != null && !keyWord.equals("") && !keyWord.equals("null")) {
                sql += " WHERE " + keyField.trim() +" LIKE '%"+keyWord.trim()+"%'";
            }


           connection =  dataSource.getConnection();

           //특정행부터  레코드를 가져오기 위해서 옵션 설정
           preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

           while(resultSet.next()){

             int count=0;

                int ROWNUM = resultSet.getInt("ROWNUM");
                int bId = resultSet.getInt("bId");
                String bName = resultSet.getString("bName");
                String bTitle = resultSet.getString("bTitle");
                String bContent = resultSet.getString("bContent");
                Timestamp bDate = resultSet.getTimestamp("bDate");
                int bHit = resultSet.getInt("bHit");
                int bGroup = resultSet.getInt("bGroup");
                int bStep = resultSet.getInt("bStep");
                int bIndent = resultSet.getInt("bIndent");

                BDto dto = new BDto(ROWNUM, bId, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent);
                dtos.add(dto);

                  count++;
                  System.out.println(count);
             //while문끝

           }//if문끝


        }catch (Exception e) {
           System.out.println(e+"=> getBoardList fail");
       }finally {
            try {
                if(resultSet != null) resultSet.close();
                if(preparedStatement != null) preparedStatement.close();
                if(connection != null) connection.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }

       return dtos;
   }

=============added sql on sql developer==================================

select rn, bId, bName, bTitle, bContent, bDate, bHit, bGroup, bStep, bIndent 
from ( select b.*, row_number() over (order by bGroup DESC, bStep asc) rn
from mvc_board b %s ) 
where rn between 1 and 10
order by rn;


Answers 2


In the example you gave us the paging is added by the limit clause, but ORACLEhas it's own syntax for these. Try:

 String sql = "SELECT rown, bId, bName, bTitle, bContent," +
              "bDate, bHit, bGroup, bStep, bIndent " +
              "from(select rownum rown, mvc_board.* from mvc_board order by bGroup DESC , bStep asc) " +
              "WHERE rown between ? and ? ";

Bind startRow and endRow before executing the query:

preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,startRow);
preparedStatement.setInt(2,endRow);
resultSet = preparedStatement.executeQuery();

Change the WHERE to AND:

sql +=" and " + keyField.trim()+" LIKE '%"+keyWord.trim()+"%'" ;

And finally change the if(resultSet.next()){ back to a while loop to get more then one row as result.

Radagast81
Radagast81
October 16, 2018 08:22 AM

The main problem in your getBoardList() method was the usage of if (resultSet.next()) instead of while (resultSet.next()) when iterating through the result set; that caused the code to only ever return a single DTO.

There are other things you should also consider. For example, starting from Java 7, it's preferrable to use the try-with resources statement instead of traditional try-finally for JDBC resource handling.

An important thing to note is that injecting field names like keyField from the UI is a really bad practice because it makes the query vulnerable to SQL injection. You should at least create a static whitelist of allowed field names to sanitize the input, if you cannot use static WHERE conditions.

Finally, paging queries in Oracle 11g are traditionally done with an inline view that uses the row_number analytic function. Note that row-numbering starts from 1. Starting from Oracle 12c, the row-limiting clause can be used and the inline view is no longer needed.

An example implementation that considers the above points could look like the following:

 public List<BDto> getBoardList(int startRow, int endRow, String keyField, String keyWord) {

    List<BDto> dtos = new ArrayList<BDto>();

    // the %s in the template will be replaced with a 
    // WHERE condition when a keyword is present
    final String sqlTemplate = "select rn, bId, bName, bTitle, "
            + "bContent, bDate, bHit, bGroup, bStep, bIndent "
            + "from ( "
            + "     select b.*, row_number() over (order by bGroup DESC, bStep asc) rn" 
            + "    from mvc_board b %s "
            + "    ) "
            + "    where rn between ? and ? "
            + "order by rn";

    boolean whereCondition = false;

    String sql = null;

    if (keyWord != null && !keyWord.equals("") && !keyWord.equals("null")) {
        sql = String.format(sqlTemplate, 
                   " WHERE " + keyField.trim() + " LIKE '%' || ? || '%'");
        whereCondition = true;
    } else {
        sql = String.format(sqlTemplate, "");
    }

    System.out.println(sql);

    try (Connection connection = dataSource.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

        int parameterIndex = 1;

        if (whereCondition) {
            preparedStatement.setString(parameterIndex++, keyWord);
        }
        preparedStatement.setInt(parameterIndex++, startRow);
        preparedStatement.setInt(parameterIndex, endRow);

        try (ResultSet resultSet = preparedStatement.executeQuery()) {

            int count = 0;

            while (resultSet.next()) {

                int ROWNUM = resultSet.getInt("rn");
                int bId = resultSet.getInt("bId");
                String bName = resultSet.getString("bName");
                String bTitle = resultSet.getString("bTitle");
                String bContent = resultSet.getString("bContent");
                Timestamp bDate = resultSet.getTimestamp("bDate");
                int bHit = resultSet.getInt("bHit");
                int bGroup = resultSet.getInt("bGroup");
                int bStep = resultSet.getInt("bStep");
                int bIndent = resultSet.getInt("bIndent");

                dtos.add(new BDto(ROWNUM, bId, bName, bTitle, 
                        bContent, bDate, bHit, bGroup, bStep, bIndent));

                count++;
            }
            System.out.println(count);
        }

    } catch (SQLException e) {
        System.out.println(e + "=> getBoardList fail");
    }

    return dtos;
}
Mick Mnemonic
Mick Mnemonic
October 16, 2018 10:41 AM

Related Questions


Oracle error messages

Updated April 24, 2018 00:26 AM

Getting int as decimal(22,0) using ojdbc6 jar

Updated May 24, 2016 09:11 AM