/*
|
* Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license
|
* Click nbfs://nbhost/SystemFileSystem/Templates/Classes/Class.java to edit this template
|
*/
|
package com.megatim.fdxcommons.tools.database.queries.json;
|
|
import com.megatim.fdxcommons.model.enumeration.TypeDonnee;
|
import static com.megatim.fdxcommons.model.enumeration.TypeDonnee.ALPHANUMERIQUE;
|
import static com.megatim.fdxcommons.model.enumeration.TypeDonnee.DATE;
|
import static com.megatim.fdxcommons.model.enumeration.TypeDonnee.DECIMAL;
|
import static com.megatim.fdxcommons.model.enumeration.TypeDonnee.NUMERIQUE;
|
import com.megatim.fdxcommons.model.integration.ColumnDefinition;
|
import com.megatim.fdxcommons.tools.database.contrat.AppColumnDefinition;
|
import com.megatim.fdxcommons.tools.database.contrat.QueryMetaData;
|
import com.megatim.fdxcommons.tools.database.exceptions.BadColumnTypeException;
|
import com.megatim.fdxcommons.tools.database.tables.appcolumns.AppColumnDefinitions;
|
import com.megatim.fdxcommons.tools.database.tables.appcolumns.IndexColumnDefinition;
|
import com.megatim.fdxcommons.tools.database.tables.appcolumns.ParentIdColumnDefinition;
|
import com.megatim.fdxcommons.model.pojo.JsonTableDefinition;
|
import java.sql.Connection;
|
import java.sql.JDBCType;
|
import java.sql.Statement;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
/**
|
*
|
* @author ASUS
|
*/
|
public class CreateJsonTableIfNotExistQuery implements QueryMetaData<Void> {
|
|
private final Connection connection;
|
private final AppColumnDefinitions appColumnDefinitions;
|
private final JsonTableDefinition tableDefinition;
|
private final List<String> queries = new ArrayList<>();
|
|
public CreateJsonTableIfNotExistQuery(Connection connection, AppColumnDefinitions appColumnDefinitions, JsonTableDefinition tableDefinition) {
|
this.connection = connection;
|
this.appColumnDefinitions = appColumnDefinitions;
|
this.tableDefinition = tableDefinition;
|
}
|
|
@Override
|
public Void execute() throws Exception {
|
query(tableDefinition);
|
|
for (String query : queries) {
|
System.out.println(query);
|
}
|
|
try ( Statement stmt = connection.createStatement()) {
|
for (String query : queries) {
|
stmt.execute(query);
|
}
|
}
|
return null;
|
}
|
|
private void query(JsonTableDefinition jsonTableDefinition) throws BadColumnTypeException {
|
StringBuilder query = new StringBuilder("CREATE TABLE IF NOT EXISTS " + jsonTableDefinition.getTableName() + "(");
|
List<AppColumnDefinition> primaryKeys = new ArrayList<>();
|
|
for (ColumnDefinition colDef : jsonTableDefinition.getColumnDefs()) {
|
query.append(colDef.getName().toLowerCase()).append(" ").append(sqlColumnType(colDef));
|
if (colDef.isRequired()) {
|
query.append(" NOT NULL");
|
}
|
query.append(", ");
|
}
|
|
for (AppColumnDefinition appColumnDefinition : appColumnDefinitions.appColumns()) {
|
query.append(columnDefinitonString(appColumnDefinition)).append(", ");
|
|
if (appColumnDefinition.isPrimaryKey()) {
|
primaryKeys.add(appColumnDefinition);
|
}
|
}
|
//Defintion de la colonne fdx_parent_id
|
if (jsonTableDefinition.getParentTableName() != null) {
|
query.append(columnDefinitonString(new ParentIdColumnDefinition())).append(", ");
|
}
|
|
if (!primaryKeys.isEmpty()) {
|
query.append(" PRIMARY KEY(");
|
primaryKeys.stream().forEach(pk -> query.append(pk.name()).append(", "));
|
|
//Retirer la dernière virgule
|
query.replace(query.length() - 2, query.length(), ")");
|
} else {
|
//Retirer la dernière virgule
|
query.replace(query.length() - 2, query.length(), "");
|
}
|
|
//Contrainte de clé étrangère sur fdx_parent_id
|
if (jsonTableDefinition.getParentTableName() != null) {
|
query.append(parentIdDefintion(jsonTableDefinition.getParentTableName(), new ParentIdColumnDefinition().name(), new IndexColumnDefinition().name()));
|
}
|
query.append(");");
|
|
queries.add(query.toString());
|
|
if (!jsonTableDefinition.getTablesDefinition().isEmpty()) {
|
for (JsonTableDefinition tableDef : jsonTableDefinition.getTablesDefinition()) {
|
query(tableDef);
|
}
|
}
|
}
|
|
private String parentIdDefintion(String parentTableName, String parentIdColumnName, String indexColumnName) {
|
StringBuilder builder = new StringBuilder("");
|
builder.append(", FOREIGN KEY (").append(parentIdColumnName).append(") REFERENCES ").append(parentTableName).append("(").append(indexColumnName).append(")");
|
|
return builder.toString();
|
}
|
|
private String sqlColumnType(ColumnDefinition colDef) throws BadColumnTypeException {
|
switch (TypeDonnee.fromValeur(colDef.getTypeDonnee())) {
|
case ALPHANUMERIQUE:
|
return jdbcType(colDef) + "(" + colDef.getTaille() + ")";
|
case NUMERIQUE:
|
return jdbcType(colDef).getName();
|
case DATE:
|
String requiredDefinition = colDef.isRequired() ? " NOT NULL" : " NULL";
|
return jdbcType(colDef).getName() + requiredDefinition;
|
case DECIMAL:
|
return jdbcType(colDef) + "(" + colDef.getTaille() + "," + 4 + ")";
|
}
|
throw new BadColumnTypeException("Type de colonne incorrect " + colDef.getTypeDonnee());
|
}
|
|
private JDBCType jdbcType(ColumnDefinition colDef) throws BadColumnTypeException {
|
switch (TypeDonnee.fromValeur(colDef.getTypeDonnee())) {
|
case NUMERIQUE:
|
return colDef.getTaille() < 5 ? JDBCType.SMALLINT : JDBCType.BIGINT;
|
case DECIMAL:
|
return JDBCType.DECIMAL;
|
case DATE:
|
return JDBCType.TIMESTAMP;
|
case ALPHANUMERIQUE:
|
return JDBCType.VARCHAR;
|
}
|
throw new BadColumnTypeException("Type de colonne incorrect " + colDef.getName());
|
}
|
|
private String columnDefinitonString(AppColumnDefinition column) throws BadColumnTypeException {
|
StringBuilder definition = new StringBuilder();
|
definition
|
.append(column.name().toLowerCase())
|
.append(" ")
|
.append(column.type())
|
.append(" ")
|
.append(column.isNull() ? "NULL " : "NOT NULL ");
|
|
if (column.defaultValue() != null) {
|
definition
|
.append("DEFAULT ")
|
.append(column.defaultValue());
|
}
|
return definition.toString();
|
}
|
}
|
