Announcement Announcement Module
Collapse
No announcement yet.
getSimpleJdbcTemplate().query problem Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • getSimpleJdbcTemplate().query problem

    Hi,

    We got an issue with using getSimpleJdbcTemplate().query. It will only return 4 row in result even when the table has 10 rows and I use select (*)... Also If I just query for matching id number (with a where stmt), the result is only match when id is with in the 4 row. It feel just like the entire table is only mapped for the first four rows.
    I am using eclipse, and postgres. When I debug, I can see it only map four records in mapRow.

    Any help?

    Thanks

    John

  • #2
    Could you post your DAO method? We need to see the query

    Use code tags

    Comment


    • #3
      Here is the implementation class:
      @Repository ("versionDao")
      public class SpringJDBCTemplateVersionDao extends SimpleJdbcDaoSupport implements VersionDao{

      private static final String SQL_LIST_AVAILABLE_TABLE_VERSIONS = "select versionnumber as VersionNumber, implementationdate as ImplementationDate, originatingcenter as OriginatingCenter, mastertable as MasterTable from versions order by versionnumber";
      private static final String SQL_LIST_AVAILABLE_TABLE_VERSIONS_NoOrder = "select versionnumber as VersionNumber, implementationdate as ImplementationDate, originatingcenter as OriginatingCenter, mastertable as MasterTable from versions";
      //private static final String SQL_LIST_AVAILABLE_TABLE_VERSIONS_NoOrder = "select * from versions";
      private JdbcTemplate jdbcTemplate;
      private DataSource dataSource;
      // Interface info:
      // public List<Versions> getAvailableVersions() throws DAOException;
      // public List<Versions> getAvailableVersions(String theVersionNumber, int sortBy) throws DAOException ;
      public List<String> getAvailableVersions() throws DAOException {
      System.out.println("SpringJDBCTemplateVersionDao line 24 Timestamp: " + new java.util.Date());
      return this.getSimpleJdbcTemplate().query(SQL_LIST_AVAILA BLE_TABLE_VERSIONS, new TableVersionMapper());
      }

      class TableVersionMapper implements RowMapper<String> {
      public String mapRow(ResultSet rs, int rowNum)throws SQLException{
      System.out.println("SpringJDBCTemplateVersionDao line 30 Timestamp: " + new java.util.Date());
      System.out.println("SpringJDBCTemplateVersionDao line 31 rowNum: " + rowNum);
      return rs.getString("versionnumber");
      }
      }

      private static final RowMapper<Versions> tableVersionRowMapper = new RowMapper<Versions>() {

      public Versions mapRow(ResultSet rs, int rowNum) throws SQLException{
      Versions versioning = new Versions();
      versioning.setVersionnumber(rs.getInt("versionnumb er"));
      versioning.setImplementationdate(rs.getDate("imple mentationdate"));
      versioning.setMastertable(rs.getInt("mastertable") );
      versioning.setOriginatingcenter(rs.getInt("origina tingcenter"));
      System.out.println("SpringJDBCTemplateVersionDao line 44 Timestamp: " + new java.util.Date());
      System.out.println("SpringJDBCTemplateVersionDao line 45 rowNum: " + rowNum);
      System.out.println("SpringJDBCTemplateVersionDao line 46 record count: ");

      return versioning;
      }
      };

      public List<Versions> getAvailableVersions(int sortBy) throws DAOException {
      String orderString = null;
      switch(sortBy){
      /*
      * public static final int SORT_BY_VersionNumber = 0;
      public static final int SORT_BY_ImplementationDate = 1;
      public static final int SORT_BY_OriginatingCenter = 2;
      public static final int SORT_BY_MasterTable = 3;
      */
      case VersionDao.SORT_BY_OriginatingCenter:
      orderString = " order by originatingcenter";
      break;
      case VersionDao.SORT_BY_ImplementationDate:
      orderString = " order by implementationdate";
      break;
      case VersionDao.SORT_BY_MasterTable:
      orderString = " order by mastertable";
      break;
      default:
      orderString = " order by versionnumber";
      }
      System.out.println("SpringJDBCTemplateVersionDao line 68 orderString: " + new java.util.Date());
      System.out.println("SpringJDBCTemplateVersionDao line 69 orderString: " + orderString);
      System.out.println("SpringJDBCTemplateVersionDao line 76 tableVersionRowMapper: " + tableVersionRowMapper.toString());

      //////List<Versions> result1=null;
      List<Versions> result1 = this.getSimpleJdbcTemplate().query(SQL_LIST_AVAILA BLE_TABLE_VERSIONS_NoOrder+ orderString, tableVersionRowMapper);
      System.out.println("SpringJDBCTemplateVersionDao line 75 result1 size: " + result1.size());
      //List<String> result2 = this.getSimpleJdbcTemplate().query(SQL_LIST_AVAILA BLE_TABLE_VERSIONS, new TableVersionMapper());

      //test
      dataSource = this.getDataSource();
      JdbcTemplate template5 = new JdbcTemplate(dataSource);
      JdbcTemplate template = new JdbcTemplate(this.getDataSource());
      int rowCountTest = template5.queryForInt("select count(*) from versions");
      //int rowCountTest = template5.queryForInt("select count(*) from versions where versionnumber = '16' or versionnumber = '17' or versionnumber = '13'");
      System.out.println("SpringJDBCTemplateVersionDao line 91 result1 size: " + rowCountTest);

      int rowCountTest2 = template.queryForInt("select count(*) from versions where versionnumber = '16' or versionnumber = '17' or versionnumber = '13'");
      System.out.println("SpringJDBCTemplateVersionDao line 94 result1 size: " + rowCountTest2);

      template.setFetchSize(9000);
      template.setMaxRows(9000);
      System.out.println("SpringJDBCTemplateVersionDao line 98 result1 size: " + template.queryForInt("select count(*) from versions where versionnumber = '16' or versionnumber = '17' or versionnumber = '13'"));

      SqlRowSet srs=null;
      int rowCount=1;
      srs = template.queryForRowSet("select * from versions where entrynumber = 1");
      while (srs.next()) {
      System.out.println("Current JdbcTemplate getMaxRows: "+ template.getMaxRows());
      System.out.println("Current JdbcTemplate getFetchSize: "+ template.getFetchSize());
      System.out.println("Entry number: "+ srs.getString("entrynumber"));
      System.out.println("Version number: "+ srs.getString("versionnumber"));
      System.out.println("This is record number: "+rowCount);
      rowCount++;
      }
      System.out.println("SpringJDBCTemplateVersionDao line 84 result1 size: " + --rowCount);

      //Connection conn = DataSourceUtils.getConnection(dataSource);

      return result1;
      }



      Here is the interface DAO:
      public interface VersionDao {



      /**
      * Set sorting default
      */
      public static final int SORT_BY_VersionNumber = 0;
      public static final int SORT_BY_ImplementationDate = 1;
      public static final int SORT_BY_OriginatingCenter = 2;
      public static final int SORT_BY_MasterTable = 3;


      /**
      * Get a list of available version from the database
      *
      * @return a list of Versions
      * @throws DAOException error accessing the data source
      */
      // public List<Versions> getAvailableVersions() throws DAOException;
      public List<String> getAvailableVersions() throws DAOException;
      // public List<String> getVideoCategories() throws DAOException;
      /**
      * Get a sorted list of version from the database for the given category. <br>
      * The list is sorted by title.
      *
      * @param theVersionNumber is the VersionNumber
      * @return a list of Versions objects
      * @throws DAOException error accessing the data source
      * @see #getVideoRecordings(String theCategory, int sortBy)
      */
      public List<Versions> getAvailableVersions(int sortBy) throws DAOException ;

      /**
      * The following can be add on
      public List<Versions> getVideoRecordings(String theCategory, int sortBy)throws DAOException;
      public VideoRecording getVideoRecording(String recordingId)throws DAOException;
      public VideoRecording getVideoRecording(int recordingId) throws DAOException;
      */
      }




      Thanks a lot

      Comment


      • #4
        It is resolved:
        The database is only four records. But somehow the pgadmin show 6 records. I restart the database and it show 4 records.
        However, the database should be 6 records. Why it lost two record and why it show in Spring DAO and not updated in PGADMIN3 is another topic....

        Comment

        Working...
        X