Java Spring Boot postgresql with HikariCP - get raw string output from statement query

Question

I am trying to write a Spring Boot Controller that allows the user to make arbitrary SELECT queries to a Postgres database and see the result. I implemented this by using a form like the one in the link. The project is based on this starter app.

Code:

@Controller
@SpringBootApplication
public class Main {

  @Value("${spring.datasource.url}")
  private String dbUrl;

  @Autowired
  private DataSource dataSource;

  public static void main(String[] args) throws Exception {
    SpringApplication.run(Main.class, args);
  }

  @GetMapping("/query")
  public String queryForm(Model model) {
    model.addAttribute("query", new Query());
    return "query";
  }

  @PostMapping("/query")
  public String querySubmit(@ModelAttribute Query query) {
    try (final Connection connection = dataSource.getConnection()) {
      final Statement stmt = connection.createStatement();
      final String rawQueryContent = query.getContent().trim();
      final String queryContent;
      if(!rawQueryContent.toLowerCase().contains("limit")) {
        queryContent = rawQueryContent + " LIMIT 500";
      } else {
        queryContent = rawQueryContent;
      }
      final ResultSet rs = stmt.executeQuery(queryContent);
      final StringBuilder sb = new StringBuilder();
      while (rs.next()) {
        sb.append("Row #" + rs.getRow() + ": " + rs.toString() + "\n");
      }
      query.setContent(sb.toString());
      rs.close();
      stmt.closeOnCompletion();
    } catch (Exception e) {
      query.setContent(e.getMessage());
    }
    return "queryresult";
  }

  @Bean
  public DataSource dataSource() throws SQLException {
    if (dbUrl == null || dbUrl.isEmpty()) {
      return new HikariDataSource();
    } else {
      HikariConfig config = new HikariConfig();
      config.setJdbcUrl(dbUrl);
      return new HikariDataSource(config);
    }
  }
}

The form looks like this:

But the output I am getting looks like this:

Row 1: HikariProxyResultSet@188463256 wrapping org.postgresql.jdbc.PgResultSet@ff61f7d 
Row 2: HikariProxyResultSet@188463256 wrapping org.postgresql.jdbc.PgResultSet@ff61f7d 
Row 3: HikariProxyResultSet@188463256 wrapping org.postgresql.jdbc.PgResultSet@ff61f7d 
Row 4: HikariProxyResultSet@188463256 wrapping org.postgresql.jdbc.PgResultSet@ff61f7d

This is not what I want! I want to see the actual rows in the database, as in:

Row 1:  "Dave" | 23 | "Philadelphia"
Row 2:  "Anne" | 72 | "New York"
Row 3:  "Susie" | 44 | "San Francisco"
Row 4:  "Alex" | 22 | "Miami"

Heck, I would rather get the raw string output that I normally get when I hand-type SQL into the database than the address in memory of the ResultSet.

How do I get the actual database output without knowing in advance exactly how many columns there will be in the table or the types of the columns?

@prasad_ - rs.toString() is returning the address in memory of the ResultSet as a String. That is not what I want. I want the actual content of the database. — Michael Lafayette, Sep 17 '18 at 05:42
Take a close look at this document. It is the result set api. It has methods to retrieve information from a query output: [Result Set](https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html). — prasad_, Sep 17 '18 at 05:45
you need to concatenate each value by getting relevant columns — Ori Marko, Sep 17 '18 at 05:46
@user7294900 I don't know the number of columns in the table that I am querying. — Michael Lafayette, Sep 17 '18 at 05:48
@prasad_ "The ResultSet interface provides getter methods (getBoolean, getLong, and so on) for retrieving column values from the current row. Values can be retrieved using either the index number of the column or the name of the column." I do not know the types of the columns or the number of columns in advance. — Michael Lafayette, Sep 17 '18 at 05:49
Can you give an example of what an input query string looks like? — prasad_, Sep 17 '18 at 05:50
@prasad_ Sure. "SELECT * FROM ticks". I don't know all the columns in the ticks table, I just know that the ticks table exists. Or how about "SELECT * FROM pg_catalog.pg_tables" . This tells me the names of the tables so that I can query them. — Michael Lafayette, Sep 17 '18 at 05:52
In the `java.sql` package there are classes which help figure the meta data of queried tables. Something like this: [JDBC - retrieve column names](https://stackoverflow.com/questions/696782/retrieve-column-names-from-java-sql-resultset). Try searching on the net for specific question you are getting now. May be look for a JDBC tutorial? — prasad_, Sep 17 '18 at 05:55
@prasad_ You mentioned metadata. I found connection.getMetaData() . I don't know if this tells me the number of columns in the table that I am querying. If I knew the number of columns, I could just do `rs.getString(columnNumber)` for each column (I am not sure if rs.getString works on non-String columns e.g. integer, date, etc). — Michael Lafayette, Sep 17 '18 at 06:03
May be trying helps. May be try one column and see how it works. — prasad_, Sep 17 '18 at 06:04

M. Deinum · Answer 1 · 2018-09-17T06:09:54.627

I would suggest, for starters to simplify your code by using the JdbcTemplate combined with a ResultSetExtractor to simplify the code. You can use the ResultSet itself to get the number of columns for a result.

I'm also not sure why you are redefining the DataSource.

All in all something like the code below should do the trick (haven't tested it and typed it from the top of my head, so might need some polishing).

@Controller
@SpringBootApplication
public class Main {

  @Autowired
  private JdbcTemplate jdbc;

  public static void main(String[] args) throws Exception {
    SpringApplication.run(Main.class, args);
  }

  @GetMapping("/query")
  public String queryForm(Model model) {
    model.addAttribute("query", new Query());
    return "query";
  }

  @PostMapping("/query")
  public String querySubmit(@ModelAttribute Query query) {
    final String rawQueryContent = query.getContent().trim();
    final String queryContent;
    if(!rawQueryContent.toLowerCase().contains("limit")) {
        queryContent = rawQueryContent + " LIMIT 500";
    } else {
        queryContent = rawQueryContent;
    }
    String content = jdbc.query(queryContent, new ResultSetExtractor<StringBuilder>() {
        public StringBuilder extractData(ResultSet rs) {
          StringBuilder sb = new StringBuilder();
          int columns = rs.getMetaData().getColumnCount();
          while (rs.next()) {
            int row = rs.getRow();
            sb.append(rs.getRow()).append('|');
            for (int i = 1 ; i <= columns ; i++) {
              sb.append(rs.getObject(i)).append('|');
            }
          }
          return sb.toString();
        }      
    });
    query.setContent(content);
    return "queryresult";
  }
}

See also How to get the number of columns from a JDBC ResultSet? on how to get the number of columns.

I think you mean queryContent, not queryContext. – Michael Lafayette Sep 17 '18 at 06:08 — Michael Lafayette, Sep 17 '18 at 06:08
As stated typed it as an answer without testing it. – M. Deinum Sep 17 '18 at 06:09 — M. Deinum, Sep 17 '18 at 06:09

Java Spring Boot postgresql with HikariCP - get raw string output from statement query

1 Answers1