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

i trying dynamically convert rows columns in mysql , display results in java/swing.

the code use convert rows columns, following

set @cols = null; set @sql = null;  select   group_concat(distinct concat('max(if(name = ''',       name, ''', amount , null)) ', '''', name , '''')   ) @cols table1;   set @sql = concat('select    date, ', @cols , ', sum(amount) total  table1 group date;');  prepare stmt  @sql;  execute stmt; 

although got results in mysql, don't know how use code , display results in java swing.

creating stored procedure prepared statement

first, must create stored procedure using prepared statement. have not tested code, stored procedure can created using code following.

drop procedure if exists execsql; delimiter // create procedure execsql ()  begin    set @sql = concat( ...    prepare stmt @sql;   execute stmt;   deallocate prepare stmt; end // delimiter ; 

invoking mysql stored procedure using jdbc

jdbc offers callablestatement class can use invoke stored procedures. may check tutorial calling mysql stored procedures jdbc.

// use call name of stored procedure string query = "{call execsql()}";  // use callablestatement obtain resultset callablestatement stmt = conn.preparecall(query) resultset rs = stmt.executequery(); 

showing result in swing jtable

to display data, may create tablemodel resultset. there many classes/libraries can use fill tablemodel resultset (such this dbutils or this jdbctablemodel).

// execute query string query = "{call execsql()}"; callablestatement stmt = conn.preparecall(query) resultset rs = stmt.executequery();  // fill tablemodel results tablemodel normaltablemodel = dbutils.resultsettotablemodel(rs);  // create swing table jtable swingtable = new jtable (transposedtablemodel); 

transposing tablemodel

sometimes want transpose (convert rows columns) results ot query. although mysql does not support pivot/unpivot, can transpose swing's tablemodel. think transposing tablemodel better (simpler) alternative.

basically, can create tablemodel wrapper transpose results. may check spring tablemodel , jidesoft transposetablemodel. code transposedtablemodel may 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, can use "normal" sql, fill tablemodel result , use transposed tablemodel.

stmt = con.createstatement(); resultset rs = stmt.executequery("select * table1");  tablemodel normaltablemodel = dbutils.resultsettotablemodel(rs); tablemodel transposedtablemodel = new transposedtablemodel(normaltablemodel);  jtable swingtable = new jtable (transposedtablemodel);