How to dynamically convert row to column in MySQL and display the results in Swing

Question

I am trying to dynamically convert rows to columns in MySQL and display the results in Java/Swing.

The code I use to convert rows to columns, is the following

SET @cols = NULL;
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(Name = ''',
      Name, ''', Amount , NULL)) AS ', '''', Name , '''')
  ) INTO @cols
FROM table1;


SET @sql = CONCAT('SELECT 
  Date, ', @cols , ', SUM(Amount) AS Total 
FROM table1
GROUP BY Date;');

prepare stmt 
FROM @sql;

execute stmt;

Although I got the results in MySQL, I don't know how use that code and display the results in Java Swing.

Please clarify your question. -- Do you want to [invoke a mysql stored procedure](http://www.mysqltutorial.org/calling-mysql-stored-procedures-from-jdbc/) and [show the result in a Swing JTable](https://stackoverflow.com/questions/27815400/retrieving-data-from-jdbc-database-into-jtable) ? — Jaime, Sep 12 '17 at 11:12
yes i want to invoke this mysql procedure and show it in jtable, can you guide me with example? please — praveen kumar, Sep 12 '17 at 11:52
If you want to transpose the results, I think you must transpose the TableModel in Java instead of "Dynamically convert row to column" — Jaime, Sep 12 '17 at 13:07
`and show it in jtable,` - well there are plenty of examples in the forum and on the web that show how to do this. You can start by searching this forum using `JTable` and `ResultSet` to find examples showing how to create a `TableModel` with data found in a ResultSet. This is how you solve many problems. You just pick a couple of keywords and do a search. — camickr, Sep 12 '17 at 14:50

Jaime · Accepted Answer · 2017-09-12T13:06:17.473

Creating a Stored Procedure with a Prepared Statement

First, you must create a stored procedure using a prepared statement. I have not tested your code, but the stored procedure can be created using code like the following.

DROP PROCEDURE IF EXISTS execSql;
DELIMITER //
CREATE PROCEDURE execSql () 
BEGIN

  SET @sql = CONCAT( ...

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Invoking a MySQL Stored Procedure using JDBC

JDBC offers a CallableStatement class you can use to invoke stored procedures. You may check a tutorial for Calling MySQL Stored Procedures from JDBC.

// use CALL with the name of the stored procedure
String query = "{CALL execSql()}";

// use CallableStatement to obtain the ResultSet
CallableStatement stmt = conn.prepareCall(query)
ResultSet rs = stmt.executeQuery();

Showing the result in a Swing JTable

To display the data, you may create a TableModel with the ResultSet. There are many classes/libraries that you can use to fill a TableModel with the resultset (such as this DbUtils or this JDBCTableModel).

// execute the query
String query = "{CALL execSql()}";
CallableStatement stmt = conn.prepareCall(query)
ResultSet rs = stmt.executeQuery();

// fill the TableModel with the results
TableModel normalTableModel = DBUtils.resultSetToTableModel(rs);

// create the Swing Table
JTable swingTable = new JTable (transposedTableModel);

Transposing a TableModel

Sometimes you want to transpose (convert rows to columns) the results ot a Query. Although MySQL does not support PIVOT/UNPIVOT, you can transpose the Swing's TableModel. I think transposing the TableModel is the better (simpler) alternative.

Basically, you can create a TableModel wrapper that transpose the results. You may check the Spring TableModel and the JideSoft TransposeTableModel. The code for a TransposedTableModel may be something like the following...

public TransposedTableModel extends AbstractTableModel {

  private TableModel innerTableModel;

  public TransposedTableModel(TableModel innerTableModel) {
     this.innerTableModel = innerTableModel;
  }

  @Override
  public int getRowCount() {
     return innerTableModel.getColumnCount();
  }

  @Override
  public   int getColumnCount() {
     return innerTableModel.getRowCount();
  }

  Override
  public   Object getValue(int row, int column) {
     return innerTableModel.getValue(column, row);
  }
};

Then, you can use a "normal" SQL, fill a TableModel with the result and the use the transposed TableModel.

stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM table1");

TableModel normalTableModel = DBUtils.resultSetToTableModel(rs);
TableModel transposedTableModel = new TransposedTableModel(normalTableModel);

JTable swingTable = new JTable (transposedTableModel);

How to dynamically convert row to column in MySQL and display the results in Swing

1 Answers1