public static List < ColumnMetaData > buildMDFromSQLTable ( final Connection con , final String dataDefinition , final Parser parser ) throws SQLException { final List < ColumnMetaData > cmds = new ArrayList <> ( ) ; final String dfTbl = parser != null ? parser . getDataFileTable ( ) : "DATAFILE" ; final String dsTbl = parser != null ? parser . getDataStructureTable ( ) : "DATASTRUCTURE" ; final StringBuilder sqlSb = new StringBuilder ( ) ; sqlSb . append ( "SELECT * FROM " ) . append ( dfTbl ) . append ( " INNER JOIN " ) . append ( dsTbl ) . append ( " ON " ) . append ( dfTbl ) . append ( ".DATAFILE_NO = " ) . append ( dsTbl ) . append ( ".DATAFILE_NO " + "WHERE DATAFILE_DESC = ? ORDER BY DATASTRUCTURE_COL_ORDER" ) ; try ( PreparedStatement stmt = con . prepareStatement ( sqlSb . toString ( ) ) ) { // always use PreparedStatement // as the DB can do clever things. stmt . setString ( 1 , dataDefinition ) ; try ( ResultSet rs = stmt . executeQuery ( ) ) { int recPosition = 1 ; // put array of columns together. These will be used to put together // the dataset when reading in the file while ( rs . next ( ) ) { final ColumnMetaData column = new ColumnMetaData ( ) ; column . setColName ( rs . getString ( "DATASTRUCTURE_COLUMN" ) ) ; column . setColLength ( rs . getInt ( DATASTRUCTURE_LENGTH ) ) ; column . setStartPosition ( recPosition ) ; column . setEndPosition ( recPosition + rs . getInt ( DATASTRUCTURE_LENGTH ) - 1 ) ; recPosition += rs . getInt ( DATASTRUCTURE_LENGTH ) ; cmds . add ( column ) ; } } if ( cmds . isEmpty ( ) ) { throw new FPException ( "Data File Key [" + dataDefinition + "] Is Not In The database OR No Columns Specified In Table" ) ; } } return cmds ; }
Returns a definition of pz column metadata from a given pz datastructure held in an SQL database