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

**ashario** · Sep 13th, 2005, 11:35 PM

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&#40;String sql&#41; &#123;
        GenericRowMapper mapper = new GenericRowMapper&#40;&#41;;
        SqlResult result = new SqlResult&#40;&#41;;

        // 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 &#40;sql.toLowerCase&#40;&#41;.startsWith&#40;"insert"&#41; ||
                sql.toLowerCase&#40;&#41;.startsWith&#40;"update"&#41; ||
                sql.toLowerCase&#40;&#41;.startsWith&#40;"delete"&#41;&#41; &#123;
          getJdbcTemplate&#40;&#41;.update&#40;sql&#41;;

        &#125; else &#123;
            List data = getJdbcTemplate&#40;&#41;.query&#40;sql, new RowMapperResultReader&#40;mapper&#41;&#41;;

            result.setData&#40;data&#41;;
            result.setColumnNames&#40;mapper.getColumnNames&#40;&#41;&#41;;
        &#125;

        return result;
    &#125;

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&#40;sql&#41;;
cs.execute&#40;&#41;;
result = cs.getResultSet&#40;&#41;;

if &#40;result != null&#41; &#123;
 // generate a html representation of the results
 html = getHtml&#40;&#41;;
&#125; else &#123;
 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?

**wpoitras** · Sep 21st, 2005, 08:30 AM

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;

**ashario** · Sep 21st, 2005, 04:52 PM

Nice one. Thanks.

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

Thread Tools

Display

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

Similar Threads

JdbcTemplate: logging sql statement just like hibernate?

usersByUsernameQuery SQL statement problem

could not satisfy dependencies

Exceptions while creating application and service beans

Transaction Management

Posting Permissions