package com.thomasjwilde.WildeDB.Database;
import com.sun.istack.internal.NotNull;
import com.sun.istack.internal.Nullable;
import com.thomasjwilde.WildeDB.WildeBeans.WildeApplication;
import com.thomasjwilde.WildeDB.WildeBeans.WildeBean;
import com.thomasjwilde.WildeDB.WildeBeans.WildeBeanProperty;
import com.thomasjwilde.WildeDB.WildeBeans.WildeBeanUtils;
import org.apache.commons.lang3.StringUtils;
import java.sql.*;
import java.util.*;
public class Database {
private static Database database;
private Connection connection;
public enum DatabaseType{
SQLITE, MSSQL, OSQL, MYSQL
}
public static void main(String[] args) {
Database.init(DatabaseType.SQLITE, "jdbc:sqlite:Test.db");
Database database = Database.getInstance();
//Retrieving the meta data object
// database.initApplicationBeans();
WildeApplication.getInstance().printAllBeanProperties();
}
/**
* Method is called prior to any database methods called
* @param databaseType Enum for database type for the connection (SQLITE, MSSQL, OSQL, MYSQL)
* @param connectionString Connection string currently is only appropriate for SQLITE
* @see DatabaseType
*/
public static void init(DatabaseType databaseType, String connectionString){
switch (databaseType) {
case SQLITE:
try {
// Init Connection
getInstance().connection = DriverManager.getConnection(connectionString);
// Get database table meta data
getInstance().initApplicationBeans();
// Close database connection on app close
Runtime current = Runtime.getRuntime();
current.addShutdownHook(new Thread(){
@Override
public void run() {
super.run();
getInstance().onApplicationClose();
}
});
} catch (SQLException throwables) {
throwables.printStackTrace();
}
break;
}
}
private Database(){
// if(connection == null){
// throw new NullPointerException("init must be called prior to getInstance()");
// }
}
public static Database getInstance(){
if(database == null){
database = new Database();
}
return database;
}
/**
* This method creates wildebeans for all of the tables in the database, wildeproperties for each of those
* wildebeans, and all subbeans for foreign key references, and properties for those subbeans. All of these
* beans are passed to the WildeApplication singleton and can then be used as templates when actual objects are
* created from those beans. These templates show the been relationship and allow for automatic join statements in
* quereies.
*/
private void initApplicationBeans(){
ResultSet databaseMetaRs = null;
ResultSet databaseHelperRs = null;
ResultSet rsPrimaryKey = null;
ResultSet rsForeignKey = null;
ResultSet rsTableColumns = null;
try {
DatabaseMetaData metaData = database.connection.getMetaData();
String[] types = {"TABLE"};
//Retrieving the columns in the database
databaseMetaRs = metaData.getTables(null, null, "t_%", types);
int iter = 1;
while (databaseMetaRs.next()) {
String tableName = databaseMetaRs.getString("TABLE_NAME");
iter++;
// Create a unique wildebean
WildeBean wildeBean = new WildeBean();
wildeBean.setSqlTableName(tableName);
System.out.println("New Table: " + tableName);
// Set the primary key
rsPrimaryKey = metaData.getPrimaryKeys(null, null, tableName);
// PrimaryKey resultSet will be closed if there is no primary key
// TODO Will need to be able to have multiple primary keys
if(!rsPrimaryKey.isClosed())
wildeBean.setPrimaryKeyColumn(rsPrimaryKey.getString("COLUMN_NAME"));
// Set the foreign keys
// Need to look into what would happen with composite foreign keys
// This is, a foreign key that has multiple columns which refer to compisite primary key of another table
// Reference https://sqlite.org/foreignkeys.html#fk_composite
rsForeignKey = metaData.getImportedKeys(null, null, tableName);
ResultSetMetaData rsmd = rsForeignKey.getMetaData();
while (rsForeignKey.next()) {
ForeignKey foreignKey = new ForeignKey();
foreignKey.setColumnName(rsForeignKey.getString("FKCOLUMN_NAME"));
foreignKey.setReferencedTable(rsForeignKey.getString("PKTABLE_NAME"));
foreignKey.setReferencedTablePK(rsForeignKey.getString("PKCOLUMN_NAME"));
if (!wildeBean.getForeignKeys().contains(foreignKey)) {
wildeBean.getForeignKeys().add(foreignKey);
}
// Following commented code prints all the column names and values from the
/*for (int i = 1; i <= rsmd.getColumnCount() ; i++) {
System.out.println("ImportKey column: " + rsmd.getColumnName(i) + "; value: " + rsForeignKey.getObject(rsmd.getColumnName(i)));
}
System.out.println();*/
}
// Add the rest of the columns
// Get the result set for the columns
rsTableColumns = metaData.getColumns(null, null, tableName, null);
// ResultSetMetaData can be used to print all columns and values
ResultSetMetaData columnMd = rsTableColumns.getMetaData();
// Create the WildeBeanProperties for the WildeBean
createWildeBeanPropertiesForTableColumnRs(rsTableColumns, wildeBean);
// Add the bean to the application beans
WildeApplication.getInstance().getUniqueDatabaseBeans().add(wildeBean);
}
// get the helper table info to populate into bean
databaseHelperRs = metaData.getTables(null, null, "h_%", types);
while (databaseHelperRs.next()) {
String tableName = databaseHelperRs.getString("TABLE_NAME");
switch (tableName) {
case WildeApplication.TABLE_SUPPORT:
// Get the table support columns and add them to the Application Wilde Beans
// Create a unique wildebean
WildeBean wildeBean = new WildeBean();
wildeBean.setSqlTableName(tableName);
System.out.println("New Table: " + tableName);
// Set the primary key
rsPrimaryKey = metaData.getPrimaryKeys(null, null, tableName);
// PrimaryKey resultSet will be closed if there is no primary key
if(!rsPrimaryKey.isClosed())
wildeBean.setPrimaryKeyColumn(rsPrimaryKey.getString("COLUMN_NAME"));
// There are no foreign key columns for the table support
// Create the WildeBeanProperties for the WildeBean
rsTableColumns = metaData.getColumns(null, null, tableName, null);
ResultSetMetaData columnMd = rsTableColumns.getMetaData();
// Create the WildeBeanProperties for the WildeBean
createWildeBeanPropertiesForTableColumnRs(rsTableColumns, wildeBean);
// Add the bean to the application beans
WildeApplication.getInstance().getUniqueDatabaseBeans().add(wildeBean);
break;
}
}
// Create sub beans for foreign key values
for (WildeBean wildeBean : WildeApplication.getInstance().getUniqueDatabaseBeans()) {
setSubBeans(wildeBean);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(databaseMetaRs);
close(rsPrimaryKey);
close(rsForeignKey);
close(rsTableColumns);
}
}
/**
* This is a recursive method called within initApplicationBeans() to set all sub-beans. The sub beans are set
* to the property of the parent bean that owns them, and given a reference to the parent bean. Parent beans have
* a method to retrieve their children beans.
* @param wildeBean WildeBean to check for subbeans.
*/
private void setSubBeans(WildeBean wildeBean) {
foreignkeyloop:
for (ForeignKey foreignKey : wildeBean.getForeignKeys()) {
WildeBeanProperty wildeBeanProperty = wildeBean.getWildeBeanProperty(foreignKey.getColumnName());
WildeBean subBean = WildeApplication.getInstance().getWildeBean(foreignKey.getReferencedTable()).newInstance();
// Go ahead and check the bean name to make sure there is not a circular reference which would cause a stackoverflow
String subBeanName = WildeBeanUtils.createBeanName(wildeBeanProperty);
if (subBeanName.contains("$")) {
// check to make sure there's not a circular reference to the latest bnea
String[] individualBeanNames = subBeanName.split("\\$");
for (int i = 0; i < individualBeanNames.length-1; i++) {
/*System.out.println("Checking bean name " + individualBeanNames[individualBeanNames.length-1] + " for to ensure it's not equal to the latest bean name " + individualBeanNames[i]);*/
if(Objects.equals(individualBeanNames[individualBeanNames.length-1], individualBeanNames[i])){
System.out.println("ERROR -- LOOP DETECTED, NOT CREATING SUB BEAN");
continue foreignkeyloop;
}
}
}
System.out.println("creating sub bean name: " + subBeanName);
wildeBeanProperty.setSubWildeBean(subBean);
setSubBeans(subBean);
}
}
/**
* Method used to print column names of a table
* @param rsmd ResultSetMetaData collected from rs.getMetaData()
* @throws SQLException
*/
private void printAllColumnNames(ResultSetMetaData rsmd) throws SQLException{
List<String> columns = new ArrayList<>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
columns.add(rsmd.getColumnName(i));
}
System.out.println(StringUtils.join(columns, ", "));
}
/**
* This method takes the resultSet from metaData.getColumns for a particular table
* and populates a wildeBean with WildeBeanProperties for each of the columns
* @param rsTableColumns ResultSet taken from metaData.getColumns(null, null, tableName, null);
* @param wildeBean WildeBean whose WildeBeanProperties are going to be populated
* @throws SQLException
*/
private void createWildeBeanPropertiesForTableColumnRs(ResultSet rsTableColumns, WildeBean wildeBean) throws SQLException{
while (rsTableColumns.next()) {
/*printAllColumnNamesAndValues(rsTableColumns, columnMd);*/
WildeBeanProperty wildeBeanProperty = new WildeBeanProperty(wildeBean);
String tableName = wildeBean.getSqlTableName();
wildeBeanProperty.setSqlTableName(tableName);
wildeBeanProperty.setSqlColumnName(rsTableColumns.getString("COLUMN_NAME"));
wildeBeanProperty.setSqlDataType(rsTableColumns.getString("TYPE_NAME"));
wildeBeanProperty.setColumnLimit(rsTableColumns.getInt("COLUMN_SIZE"));
wildeBeanProperty.setNullable(Objects.equals(rsTableColumns.getString("IS_NULLABLE"), "YES"));
wildeBeanProperty.setAutoIncrement(Objects.equals(rsTableColumns.getString("IS_AUTOINCREMENT"), "YES"));
wildeBeanProperty.setGeneratedColumn(Objects.equals(rsTableColumns.getString("IS_GENERATEDCOLUMN"), "YES"));
WildeBeanUtils.createDisplayNameFromSqlColumn(wildeBeanProperty);
wildeBeanProperty.setPrimaryKey(Objects.equals(wildeBean.getPrimaryKeyColumn(), wildeBeanProperty.getSqlColumnName()));
if (wildeBean.getForeignKeys().isEmpty()) {
wildeBeanProperty.setForeignKey(false);
} else {
// See if there is any match to the column name
wildeBeanProperty.setForeignKey(
wildeBean.getForeignKeys().stream()
.anyMatch(foreignKey -> Objects.equals(foreignKey.getColumnName(), wildeBeanProperty.getSqlColumnName())));
}
wildeBean.getBeanProperties().add(wildeBeanProperty);
}
}
/**
* Method used for printing all database table column and values for a particular ResultSet row
* Should be called within a while(rs.next()) loop
* @param rs The ResultSet or row whose column and values are being printed
* @param resultSetMetaData The ResultSetMetaData which should be collected prior to the while loop by rs.getMetaData()
*/
private void printAllColumnNamesAndValues(ResultSet rs, ResultSetMetaData resultSetMetaData) {
try {
for (int i = 1; i <= resultSetMetaData.getColumnCount() ; i++) {
System.out.println("Table column: " + resultSetMetaData.getColumnName(i) + "; value: " + rs.getObject(resultSetMetaData.getColumnName(i)));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
/**
* Standard query call for a table and an
* @param tableName The name of the table to prefer the query
* @param where A where clause similar to Sql Developer. Should not contain the word "Where".
* @return
*/
public ArrayList<WildeBean> query(@NotNull String tableName, @Nullable String where){
return DBUtil.query(connection, tableName, where);
}
public void close(PreparedStatement preparedStatement) {
if(preparedStatement != null){
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public void close(ResultSet rs) {
if(rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public void close(ResultSet rs, PreparedStatement preparedStatement) {
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(preparedStatement != null){
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public void onApplicationClose(){
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}