/* The current table and his current column. Field used like a cursor moved forward during the filling operation */
String currentFieldName = null;
String currentTable = null;
try{
DatabaseTableUtils databaseTableUtils = new DatabaseTableUtils();
List<String> tableNames = databaseTableUtils.getTableNamesOrdered();
// 1) obtain a native jdbc connection to use for insert generated data
conn = DatabaseConnectionFactory.getDataSource().getConnection();
/*
* TIPS: set the mysql variable "wait_timeout" to an high value for long operation if the connection is closed automatically
* It is "The number of seconds the server waits for activity on a no interactive connection before closing it"
*
* SET GLOBAL wait_timeout = 28800; value 1 to 2147483
* show global variables like 'wait_timeout' ;
*/
Statement statement = conn.createStatement();
// 1b) If required, prepare the file where write the sql statement(s)
if(!this.outputSqlFile.equalsIgnoreCase("") && this.outputSqlFile !=null)
{
outputFile = new File(this.outputSqlFile);
if(outputFile.exists())
outputFile.delete();
outputFile.createNewFile(); //create a new file
fileWriter = new FileWriter(outputFile);
out = new BufferedWriter(fileWriter);
saveToFile = true;
// a fix header message
out.write("\n\n");
out.write("---- NOTE: the following query don't have the insert part for the auto-increment field(s) \n");
}
// 2 for each table get informations about the fields and the FK constraints (if any)
for(int i=0; i<tableNames.size(); i++)
{
currentTable = tableNames.get(i);
if(Log4jManager.IS_LOGGING_CONFIGURED)
logger.info("----- Starting filling of table: "+currentTable);
if(saveToFile) {
out.write("\n\n");
out.write("---- Table: "+currentTable+" ----"+"\n");
out.flush();
}
ArrayList<MetadataTableInfoBean> tableFieldsInfoList = databaseTableUtils.getFieldsInfo(currentTable);
// All the FK relations of the current table (ie which table references)
ArrayList<TableFKconstraintsBean> tableFKinfoList = databaseTableUtils.getFkInformation(currentTable);
/* Flag to indicates if the current field references another field in a child table */
boolean fieldHasChild = false;
/* Start the query Building */
for(int k=0;k<recordToInsert;k++)
{
/* The current table column names to use in the sql insert query */
String columnNameList = "";
/* The list of values place in the sql query that we are building */
String valuesList = "'";
// 3 for each field, depending on his data type choose the right generator type to use
for(int j=0;j<tableFieldsInfoList.size();j++)
{
MetadataTableInfoBean field = tableFieldsInfoList.get(j);
currentFieldName = field.getFieldName();
fieldHasChild = false;
// the type of the current field ( eg varchar(10) bigint(10) )
String fieldType = field.getFieldType();
String childColumn = null;
String childTable = null;
if(Log4jManager.IS_LOGGING_CONFIGURED)
logger.debug("Current field: "+currentFieldName +" of Type:"+fieldType);
// check if the current field has child
for(TableFKconstraintsBean fkInfoList:tableFKinfoList)
{
//true if the current field point another table
if(fkInfoList.getParentColumnName().equalsIgnoreCase(currentFieldName)) {
fieldHasChild = true;
//get who is the referenced column+table pair
childColumn = fkInfoList.getReferencedColumnName();
childTable = fkInfoList.getReferencedTableName();
}
}
/**
* Note the child table is already filled because our table list is in the filling order
*/
if(fieldHasChild && childTable !=null && childColumn !=null)
{
if(Log4jManager.IS_LOGGING_CONFIGURED)
logger.info("- Table "+currentTable+"("+currentFieldName+") references the Table:"+childTable+"("+childColumn+") ");
/* the fields with "auto_increment" option don't appear in the insert query: is mysql that generate his value */
if(!field.getExtraInfo().equalsIgnoreCase("auto_increment")) {
columnNameList += field.getFieldName()+",";
// Get the allowed values for the current field from the child column
ArrayList<String> allowedValues = databaseTableUtils.getColumnValue(childTable,childColumn);
String chosenValue = allowedValues.get(k); //use the 'k' index because all the table have the same total row
valuesList +=chosenValue+"','";
}