Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package main.java.ie.revenue.bulknav.web.utils.dao;
- import org.springframework.jdbc.core.JdbcTemplate;
- import org.springframework.jdbc.datasource.DriverManagerDataSource;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.sql.SQLException;
- import java.sql.Timestamp;
- import java.util.ArrayList;
- import java.util.List;
- import java.util.Map;
- import java.util.Map.Entry;
- import java.util.Properties;
- import main.java.ie.revenue.bulknav.web.controllers.MainPageController;
- import main.java.ie.revenue.bulknav.web.models.Column;
- import main.java.ie.revenue.bulknav.web.models.Table;
- import org.springframework.ui.ModelMap;
- import org.apache.log4j.Logger;
- public class JDBC_SQL {
- /*
- * Create Tables = createNewTables(ArrayList<Column> columns); will create
- * new table tables or column tables depending whether or not the columnName
- * is set.
- *
- * Truncate Table = truncateTable(String dbName,String tableName) can only
- * truncate column tables because in table tables the tableName is a foreign
- * key of column tables
- *
- * Drop Table = dropTable(String tableName) drops a table from current
- * database
- *
- * Add KVP(column) to table = addKVP(String dbName,String tableName, String
- * newColumn) adds a new KVP to the table
- *
- * Drop KVP(column) from table = dropKVP(String dbName,String tableName,
- * String newColumn) drops a column from the table
- *
- * Checking for Table = isColumnInDb(Column column); boolean querying to see
- * if a column is in the database
- *
- * Checking for column = isTableInDb(Column column); boolean querying to see
- * if a table is in the database
- *
- * Pull Table Names = getTableNames(String dbName); currently pull table and
- * column tables
- *
- * Pull Specific Tables = getTablesFromDB(String dbName, String[]
- * tableNames); gets specific tables from the db
- *
- * Writes info to tables = writeToTables(ArrayList<Column> columns); write
- * info from columns object into the respective table
- *
- * Updates the columns = updateColumnMetaInDB(ArrayList<Column> columns);
- * updates the data of each of columns passed to it
- */
- public Logger log = Logger.getLogger(MainPageController.class.getName());
- private JdbcTemplate jdbcTemplateObject;
- public JDBC_SQL() throws SQLException, IOException {
- String configFile = "C:\\Workspace\\BulkNav\\WebContent\\resources\\jdbcProperties\\jdbc.properties";
- // read in properties file
- Properties connData = new Properties();
- FileInputStream fis = new FileInputStream(new File(configFile));
- connData.load(fis);
- fis.close();
- String url = connData.getProperty("mysql.url");
- String userName = connData.getProperty("mysql.username");
- String password = connData.getProperty("mysql.password");
- String driver = connData.getProperty("mysql.driver");
- // creates datasource object for JDBC
- DriverManagerDataSource dataSource = new DriverManagerDataSource(url,
- userName, password);
- dataSource.setDriverClassName(driver);
- // Initializes JDBC object to be used int class
- this.jdbcTemplateObject = new JdbcTemplate(dataSource);
- }
- /************************** CREATING TABLES **************************************************************************/
- @SuppressWarnings("unused")
- public String createNewTables(ArrayList<Table> createList) {
- String[] sql = new String[createList.size()];
- for (int i = 0; i < createList.size(); i++) {
- // implement create method
- String query = "CREATE TABLE IF NOT EXISTS "
- + createList.get(i).getName()
- + "(name VARCHAR(50) DEFAULT NULL , "
- + "columnCount INT(50) DEFAULT NULL, "
- + "dbName VARCHAR(50) DEFAULT NULL, "
- + "description VARCHAR(200) DEFAULT NULL, "
- + "tags VARCHAR(50) DEFAULT NULL, "
- + "dateModified TIMESTAMP (6) NOT NULL DEFAULT '0000-00-00 00:00:00', "
- + "uniqueIdentifier VARCHAR (100) DEFAULT NULL, "
- + "dynamic_cols TEXT);";
- // prints query to log
- log.info(query);
- jdbcTemplateObject.execute(query);
- // adds the query to the batch updates to be executed
- sql[i] += query;
- }
- // jdbcTemplateObject.batchUpdate(sql);
- if (sql != null) {
- return "sucess";
- }
- return "failure";
- }
- /************************** CREATING TABLES_COLUMN **************************************************************************/
- @SuppressWarnings("unused")
- public String createNewTables_Column(ArrayList<Column> createList) {
- String[] sql = new String[createList.size()];
- for (int i = 0; i < createList.size(); i++) {
- // implement create method
- String query = "CREATE TABLE IF NOT EXISTS "
- + createList.get(i).getTableName()+"_Column"
- + "(`name` VARCHAR(50) DEFAULT NULL, "
- + "`tableName` VARCHAR(50) DEFAULT NULL, "
- + "`dbName` VARCHAR(50) DEFAULT NULL, "
- + "`description` VARCHAR(200) DEFAULT NULL, "
- + "`tags` VARCHAR(50) DEFAULT NULL, "
- + "`dateModified` TIMESTAMP (6)NOT NULL DEFAULT '0000-00-00 00:00:00', "
- + "`uniqueIdentifier` VARCHAR (100) DEFAULT NULL, "
- + "dynamic_cols TEXT);";
- // prints query to log
- log.info(query);
- jdbcTemplateObject.execute(query);
- // adds the query to the batch updates to be executed
- sql[i] += query;
- }
- // jdbcTemplateObject.batchUpdate(sql);
- if (sql != null) {
- return "sucess";
- }
- return "failure";
- }
- /************************** TABLE BACK SLASH METHODS *****************************************************************************/
- public Table removeDBForwardSlashFromTable(Table entity) {
- String db = entity.getDbName();
- db = db.replaceAll("/", "");
- entity.setDbName(db);
- return entity;
- }
- public Table additionDBForwardSlashToTable(Table entity) {
- String dbName = "/" + entity.getDbName();
- entity.setDbName(dbName);
- return entity;
- }
- /************************** COLUMN BACK SLASH METHODS *****************************************************************************/
- public Column removeDBForwardSlashFromColumn(Column entity) {
- String db = entity.getDbName();
- db = db.replaceAll("/", "");
- entity.setDbName(db);
- return entity;
- }
- public Column additionDBForwardSlashToColumn(Column entity) {
- String dbName = "/" + entity.getDbName();
- entity.setDbName(dbName);
- return entity;
- }
- /************************** TRUNCATE TABLE *****************************************************************************/
- public String truncateTable(String dbName, String tableName) {
- log.info("Truncating the table.");
- String query = "TRUNCATE TABLE " + dbName + "." + tableName + " ;";
- // prints query to log
- log.info(query);
- jdbcTemplateObject.execute(query);
- return "sucess";
- }
- /************************** ALTER TO DROP TABLE *****************************************************************************/
- public String dropTable(String dbName) {
- List<Map<String, Object>> tableNames = new ArrayList<Map<String, Object>>();
- String query1 = "SELECT table_name FROM information_schema.tables WHERE table_schema='"
- + dbName + "';";
- tableNames = jdbcTemplateObject.queryForList(query1);
- for(int i =0; i <tableNames.size(); i ++)
- {
- for (Entry<String, Object> entry : tableNames.get(i).entrySet())
- {
- String query = "DROP TABLE " + dbName + "." + ((String)entry.getValue()) + " ;";
- log.info(query);
- jdbcTemplateObject.execute(query);
- }
- }
- return "sucess";
- }
- /************************** ALTER TO ADD KVP *****************************************************************************/
- public String addKVP(String dbName, String tableName, String newEntity) {
- String query = "ALTER TABLE " + dbName + "." + tableName + " ADD "
- + newEntity + " VARCHAR (60) DEFAULT NULL;";
- // prints query to log
- log.info(query);
- jdbcTemplateObject.execute(query);
- return "sucess";
- }
- /************************** ALTER TO DROP KVP *****************************************************************************/
- public String dropKVP(String dbName, String tableName, String newEntity) {
- String query = "ALTER TABLE " + dbName + "." + tableName + " DROP "
- + newEntity + " ;";
- // prints query to log
- log.info(query);
- jdbcTemplateObject.execute(query);
- return "sucess";
- }
- /************************** PULLING DATA **************************************************************************/
- public boolean isTableInDb(String dbName, String tableName) {
- List<Map<String, Object>> tableNames = new ArrayList<Map<String, Object>>();
- String query = "SELECT table_name FROM information_schema.tables WHERE table_schema='"
- + dbName + "' AND table_name= '"+ tableName + "';";
- tableNames = jdbcTemplateObject.queryForList(query);
- if (!tableNames.isEmpty()) {
- log.info("table was found");
- return true;
- }
- log.info("table wasn't found");
- return false;
- }
- // pulls columns from the database
- public List<Map<String, Object>> getTablesFromDB(String dbName,
- String[] tableNames) {
- // output list
- List<Map<String, Object>> database = new ArrayList<Map<String, Object>>();
- // checks tablenames against column tables
- for (int i = 0; i < tableNames.length; i++) {
- String query = "SELECT * FROM " + dbName + "." + tableNames[i] + " WHERE dateModified = (SELECT MAX(dateModified) FROM " + dbName + "." + tableNames[i]+");";
- database.addAll(jdbcTemplateObject.queryForList(query));
- }
- return database;
- }
- /************************** WRITING TO TABLES **************************************************************************/
- public void writeToTables(ArrayList<Table> createList , Timestamp now) {
- String[] sql = new String[createList.size()];
- for (int i = 0; i < createList.size(); i++) {
- //temporary local instance of kvp map
- ModelMap local = createList.get(i).getKvp();
- // start building sql statement
- String query = "INSERT INTO " + createList.get(i).getName()
- + "(`name`, `columnCount`, `dbName`, `description`, `tags`, "
- + "`dateModified`, `uniqueIdentifier`";
- // if local is not empty add in column for dynamic columns
- if ( local != null) {
- query += ", `dynamic_cols`";
- }
- // add in values for the columns
- query +=" ) VALUES ('" + createList.get(i).getName() + "', '"
- + createList.get(i).getTableColumnCounter() + "', '"
- + createList.get(i).getDbName() + "', '"
- + createList.get(i).getDescription() + "', '"
- + createList.get(i).getTags() + "', '"
- + now + "', '"
- + createList.get(i).getUniqueIdentity() + "' ";
- // build the text string that holds all the dynamic columns
- int k = 0;
- if ( local != null) {
- String dynam_cols = ",'";
- for (Entry<String, Object> entry : local.entrySet()) {
- dynam_cols += (String)entry.getKey() + ":" + (String)entry.getValue() ;
- k++;
- if (k < local.size()){
- dynam_cols += "|";
- }
- }
- dynam_cols += "'";
- query += dynam_cols;
- }
- // adds end marker to insert statement
- query += " ); ";
- // prints query to log
- log.info(query);
- jdbcTemplateObject.execute(query);
- // adds query to sql commands to be batch executed
- sql[i] = query;
- }
- }
- /************************** WRITING TO TABLES_COLUMNS **************************************************************************/
- public void writeToTables_Column(ArrayList<Column> createList, Timestamp now) {
- String[] sql = new String[createList.size()];
- for (int i = 0; i < createList.size(); i++) {
- //temporary local instance of kvp map
- ModelMap local = createList.get(i).getKvp();
- // start building sql statement
- String query = "INSERT INTO " + createList.get(i).getTableName()+"_Column"
- + " ( `name`, `tableName`, `dbName`, `description`,"
- + " `tags`, `dateModified`, `uniqueIdentifier`";
- // if local is not empty add in column for dynamic columns
- if ( local != null) {
- query += ", `dynamic_cols`";
- }
- // add in values for the columns
- query +=" ) VALUES ('" + createList.get(i).getName() + "', '"
- + createList.get(i).getTableName() + "', '"
- + createList.get(i).getDbName() + "', '"
- + createList.get(i).getDescription() + "', '"
- + createList.get(i).getTags() + "', '"
- + now + "', '"
- + createList.get(i).getUniqueIdentity() + "' ";
- // build the text string that holds all the dynamic columns
- int k = 0;
- if ( local != null) {
- String dynam_cols = ",'";
- for (Entry<String, Object> entry : local.entrySet()) {
- dynam_cols += (String)entry.getKey() + ":" + (String)entry.getValue() ;
- k++;
- if (k < local.size()){
- dynam_cols += "|";
- }
- }
- dynam_cols += "'";
- query += dynam_cols;
- }
- // adds end marker to insert statement
- query += ");";
- // prints query to log
- log.info(query);
- jdbcTemplateObject.execute(query);
- // adds query to sql commands to be batch executed
- sql[i] = query;
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement