SetupQueryEditor.java

package binky.reportrunner.ui.actions.general;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

import javax.sql.DataSource;

import binky.reportrunner.data.RunnerDataSource;
import binky.reportrunner.service.DatasourceService;
import binky.reportrunner.ui.actions.base.StandardRunnerAction;
import binky.reportrunner.ui.actions.general.metadata.Column;
import binky.reportrunner.ui.actions.general.metadata.FKey;
import binky.reportrunner.ui.actions.general.metadata.Meta;
import binky.reportrunner.ui.actions.general.metadata.Table;

import com.google.gson.Gson;

public class SetupQueryEditor extends StandardRunnerAction {

	private static final long serialVersionUID = -5994330814661409736L;
	private String dataSourceName;
	private DatasourceService datasourceService;

	private String json;
	
	@Override
	public String execute() throws Exception {
		
		RunnerDataSource rds=datasourceService.getDataSource(dataSourceName);
		DataSource ds = datasourceService.getJDBCDataSource(rds);
		Connection conn = ds.getConnection();
		
		DatabaseMetaData dbm= conn.getMetaData();
		
		ResultSet rs = dbm.getTables(null, null, "%", new String[]{"TABLE"});
		List<Table> tables = new LinkedList<Table>();
		while(rs.next()) {
			
			String tabCat=rs.getString("TABLE_CAT");
			String tabSchema=rs.getString("TABLE_SCHEM");
			String tabName=rs.getString("TABLE_NAME");
			
			ResultSet rsCols =dbm.getColumns(tabCat, tabSchema, tabName, "%");
			List<Column> cols = new LinkedList<Column>();
			
			while (rsCols.next())  cols.add(new Column(rsCols.getString("COLUMN_NAME"), rsCols.getString("COLUMN_NAME"), rsCols.getString("TYPE_NAME"), rsCols.getInt("COLUMN_SIZE")));
			ResultSet rsFK = dbm.getExportedKeys(tabCat, tabSchema, tabName);
			List<FKey> fks = new LinkedList<FKey>();
			rsCols.close();
			while (rsFK.next()) {
				String referencedTableName=rsFK.getString("FKTABLE_NAME");
				String name=rsFK.getString("FKCOLUMN_NAME");
				String label=rsFK.getString("FKCOLUMN_NAME");
				String reverseLabel=rsFK.getString("PKTABLE_NAME");
				String[] foreignKeyNames = new String[]{rsFK.getString("FKCOLUMN_NAME")};
				String[] referencedKeyNames = new String[]{rsFK.getString("PKCOLUMN_NAME")};				
				fks.add(new FKey(referencedTableName, name, label, foreignKeyNames, referencedKeyNames, reverseLabel));
			}
			rsFK.close();
			tables.add(new Table(cols.toArray(new Column[0]), fks.toArray(new FKey[0])));
		}
		
		Meta meta = new Meta();
		meta.setTables(tables.toArray(new Table[0]));
		
		Gson gson = new Gson();
		this.json=gson.toJson(meta);
		rs.close();
		conn.close();
		return SUCCESS;
	}

	public String getJson() {
		return json;
	}

	public void setDataSourceName(String dataSourceName) {
		this.dataSourceName = dataSourceName;
	}

	public void setDatasourceService(DatasourceService datasourceService) {
		this.datasourceService = datasourceService;
	}

}