Announcement Announcement Module
Collapse
No announcement yet.
generic sql statement - how to tell if there is a ResultSet Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • generic sql statement - how to tell if there is a ResultSet

    I have an admin page for one of my apps where i can type in a sql statement and have it executed

    this page supports select, update, delete, insert, store procs etc

    If the sql statement returns a ResultSet then I display the results in the browser to the user.

    Is there any way using Spring's jdbc classes to determine if a sql statement I have executed has any results?

    Right now I have a poor implementation as follows in my DAO impl class.

    Code:
        public SqlResult executeSql(String sql) {
            GenericRowMapper mapper = new GenericRowMapper();
            SqlResult result = new SqlResult();
    
            // i wish i knew a 'spring' way to work out whether a sql statement produced some results.
            // for now we just check for the insert, update and delete keywords
            if (sql.toLowerCase().startsWith("insert") ||
                    sql.toLowerCase().startsWith("update") ||
                    sql.toLowerCase().startsWith("delete")) {
              getJdbcTemplate().update(sql);
    
            } else {
                List data = getJdbcTemplate().query(sql, new RowMapperResultReader(mapper));
    
                result.setData(data);
                result.setColumnNames(mapper.getColumnNames());
            }
    
            return result;
        }
    It would be nice if there was a way for me to execute a statement and somehow interrogate the result to see if the statement returned a resultset or how many updated/deleted/inserted rows and process accordingly.

    In plain jdbc code I would do something like
    Code:
    String sql = "a sql statement";
    CallableStatement clstmt = null;
    ResultSet rset = null;
    CallableStatement cs = con.prepareCall(sql);
    cs.execute();
    result = cs.getResultSet();
    
    if (result != null) {
     // generate a html representation of the results
     html = getHtml();
    } else {
     html = "<html>SQL executed successfully</html>";
    &#125;
    But as I have found out - directly calling jdbc is evil
    Is there a Spring way to do this?

  • #2
    Try something like this:
    Code:
    public String executeSql&#40;final String sql&#41; &#123;
        return getJdbcTemplate.execute&#40;sql, new PreparedStatementCallback&#40;&#41; &#123;
          public Object doInPreparedStatement&#40;PreparedStatement ps&#41; throws SQLException &#123;
            ps.execute&#40;&#41;;
            try &#123;
              ResultSet result = ps.getResultSet&#40;&#41;;
      
              if &#40;result != null&#41; &#123;
                // generate a html representation of the results
                html = getHtml&#40;result&#41;;
              &#125;
              else &#123;
                html = "<html>SQL executed successfully</html>";
              &#125;
            &#125;
            finally &#123;
              JdbcUtils.closeResultSet&#40;result&#41;;
            &#125;
          &#125;
        &#125;&#41;;
    &#125;

    Comment


    • #3
      Nice one. Thanks.

      Comment

      Working...
      X