· 9 years ago · Jun 07, 2017, 02:28 PM
1package main.java.ie.revenue.bulknav.web.utils.dao;
2
3import org.springframework.jdbc.core.JdbcTemplate;
4import org.springframework.jdbc.datasource.DriverManagerDataSource;
5
6import java.io.File;
7import java.io.FileInputStream;
8import java.io.IOException;
9import java.sql.SQLException;
10import java.sql.Timestamp;
11import java.util.ArrayList;
12import java.util.List;
13import java.util.Map;
14import java.util.Map.Entry;
15import java.util.Properties;
16
17import main.java.ie.revenue.bulknav.web.controllers.MainPageController;
18import main.java.ie.revenue.bulknav.web.models.Column;
19import main.java.ie.revenue.bulknav.web.models.Table;
20
21import org.springframework.ui.ModelMap;
22import org.apache.log4j.Logger;
23
24public class JDBC_SQL {
25
26 /*
27 * Create Tables = createNewTables(ArrayList<Column> columns); will create
28 * new table tables or column tables depending whether or not the columnName
29 * is set.
30 *
31 * Truncate Table = truncateTable(String dbName,String tableName) can only
32 * truncate column tables because in table tables the tableName is a foreign
33 * key of column tables
34 *
35 * Drop Table = dropTable(String tableName) drops a table from current
36 * database
37 *
38 * Add KVP(column) to table = addKVP(String dbName,String tableName, String
39 * newColumn) adds a new KVP to the table
40 *
41 * Drop KVP(column) from table = dropKVP(String dbName,String tableName,
42 * String newColumn) drops a column from the table
43 *
44 * Checking for Table = isColumnInDb(Column column); boolean querying to see
45 * if a column is in the database
46 *
47 * Checking for column = isTableInDb(Column column); boolean querying to see
48 * if a table is in the database
49 *
50 * Pull Table Names = getTableNames(String dbName); currently pull table and
51 * column tables
52 *
53 * Pull Specific Tables = getTablesFromDB(String dbName, String[]
54 * tableNames); gets specific tables from the db
55 *
56 * Writes info to tables = writeToTables(ArrayList<Column> columns); write
57 * info from columns object into the respective table
58 *
59 * Updates the columns = updateColumnMetaInDB(ArrayList<Column> columns);
60 * updates the data of each of columns passed to it
61 */
62
63 public Logger log = Logger.getLogger(MainPageController.class.getName());
64
65 private JdbcTemplate jdbcTemplateObject;
66
67 public JDBC_SQL() throws SQLException, IOException {
68
69 String configFile = "C:\\Workspace\\BulkNav\\WebContent\\resources\\jdbcProperties\\jdbc.properties";
70
71 // read in properties file
72 Properties connData = new Properties();
73 FileInputStream fis = new FileInputStream(new File(configFile));
74 connData.load(fis);
75 fis.close();
76
77 String url = connData.getProperty("mysql.url");
78 String userName = connData.getProperty("mysql.username");
79 String password = connData.getProperty("mysql.password");
80 String driver = connData.getProperty("mysql.driver");
81
82 // creates datasource object for JDBC
83 DriverManagerDataSource dataSource = new DriverManagerDataSource(url,
84 userName, password);
85
86 dataSource.setDriverClassName(driver);
87
88 // Initializes JDBC object to be used int class
89 this.jdbcTemplateObject = new JdbcTemplate(dataSource);
90
91 }
92
93 /************************** CREATING TABLES **************************************************************************/
94
95 @SuppressWarnings("unused")
96 public String createNewTables(ArrayList<Table> createList) {
97 String[] sql = new String[createList.size()];
98
99 for (int i = 0; i < createList.size(); i++) {
100
101 // implement create method
102 String query = "CREATE TABLE IF NOT EXISTS "
103 + createList.get(i).getName()
104 + "(name VARCHAR(50) DEFAULT NULL , "
105 + "columnCount INT(50) DEFAULT NULL, "
106 + "dbName VARCHAR(50) DEFAULT NULL, "
107 + "description VARCHAR(200) DEFAULT NULL, "
108 + "tags VARCHAR(50) DEFAULT NULL, "
109 + "dateModified TIMESTAMP (6) NOT NULL DEFAULT '0000-00-00 00:00:00', "
110 + "uniqueIdentifier VARCHAR (100) DEFAULT NULL, "
111 + "dynamic_cols TEXT);";
112
113 // prints query to log
114 log.info(query);
115
116 jdbcTemplateObject.execute(query);
117
118 // adds the query to the batch updates to be executed
119 sql[i] += query;
120
121 }
122 // jdbcTemplateObject.batchUpdate(sql);
123 if (sql != null) {
124 return "sucess";
125 }
126 return "failure";
127 }
128
129 /************************** CREATING TABLES_COLUMN **************************************************************************/
130
131 @SuppressWarnings("unused")
132 public String createNewTables_Column(ArrayList<Column> createList) {
133 String[] sql = new String[createList.size()];
134 for (int i = 0; i < createList.size(); i++) {
135
136 // implement create method
137 String query = "CREATE TABLE IF NOT EXISTS "
138 + createList.get(i).getTableName()+"_Column"
139 + "(`name` VARCHAR(50) DEFAULT NULL, "
140 + "`tableName` VARCHAR(50) DEFAULT NULL, "
141 + "`dbName` VARCHAR(50) DEFAULT NULL, "
142 + "`description` VARCHAR(200) DEFAULT NULL, "
143 + "`tags` VARCHAR(50) DEFAULT NULL, "
144 + "`dateModified` TIMESTAMP (6)NOT NULL DEFAULT '0000-00-00 00:00:00', "
145 + "`uniqueIdentifier` VARCHAR (100) DEFAULT NULL, "
146 + "dynamic_cols TEXT);";
147
148 // prints query to log
149 log.info(query);
150
151 jdbcTemplateObject.execute(query);
152
153 // adds the query to the batch updates to be executed
154 sql[i] += query;
155
156 }
157 // jdbcTemplateObject.batchUpdate(sql);
158 if (sql != null) {
159 return "sucess";
160 }
161 return "failure";
162 }
163
164 /************************** TABLE BACK SLASH METHODS *****************************************************************************/
165
166 public Table removeDBForwardSlashFromTable(Table entity) {
167 String db = entity.getDbName();
168 db = db.replaceAll("/", "");
169 entity.setDbName(db);
170 return entity;
171
172 }
173
174 public Table additionDBForwardSlashToTable(Table entity) {
175 String dbName = "/" + entity.getDbName();
176 entity.setDbName(dbName);
177 return entity;
178
179 }
180
181 /************************** COLUMN BACK SLASH METHODS *****************************************************************************/
182
183 public Column removeDBForwardSlashFromColumn(Column entity) {
184 String db = entity.getDbName();
185 db = db.replaceAll("/", "");
186 entity.setDbName(db);
187 return entity;
188
189 }
190
191 public Column additionDBForwardSlashToColumn(Column entity) {
192 String dbName = "/" + entity.getDbName();
193 entity.setDbName(dbName);
194 return entity;
195
196 }
197
198 /************************** TRUNCATE TABLE *****************************************************************************/
199
200 public String truncateTable(String dbName, String tableName) {
201
202 log.info("Truncating the table.");
203 String query = "TRUNCATE TABLE " + dbName + "." + tableName + " ;";
204 // prints query to log
205 log.info(query);
206
207 jdbcTemplateObject.execute(query);
208
209 return "sucess";
210 }
211
212 /************************** ALTER TO DROP TABLE *****************************************************************************/
213
214 public String dropTable(String dbName) {
215
216 List<Map<String, Object>> tableNames = new ArrayList<Map<String, Object>>();
217 String query1 = "SELECT table_name FROM information_schema.tables WHERE table_schema='"
218 + dbName + "';";
219
220 tableNames = jdbcTemplateObject.queryForList(query1);
221
222 for(int i =0; i <tableNames.size(); i ++)
223 {
224 for (Entry<String, Object> entry : tableNames.get(i).entrySet())
225 {
226 String query = "DROP TABLE " + dbName + "." + ((String)entry.getValue()) + " ;";
227
228 log.info(query);
229
230 jdbcTemplateObject.execute(query);
231 }
232 }
233
234 return "sucess";
235 }
236
237 /************************** ALTER TO ADD KVP *****************************************************************************/
238
239 public String addKVP(String dbName, String tableName, String newEntity) {
240
241 String query = "ALTER TABLE " + dbName + "." + tableName + " ADD "
242 + newEntity + " VARCHAR (60) DEFAULT NULL;";
243
244 // prints query to log
245 log.info(query);
246
247 jdbcTemplateObject.execute(query);
248
249 return "sucess";
250 }
251
252 /************************** ALTER TO DROP KVP *****************************************************************************/
253
254 public String dropKVP(String dbName, String tableName, String newEntity) {
255
256 String query = "ALTER TABLE " + dbName + "." + tableName + " DROP "
257 + newEntity + " ;";
258
259 // prints query to log
260 log.info(query);
261
262 jdbcTemplateObject.execute(query);
263
264 return "sucess";
265 }
266
267 /************************** PULLING DATA **************************************************************************/
268
269 public boolean isTableInDb(String dbName, String tableName) {
270 List<Map<String, Object>> tableNames = new ArrayList<Map<String, Object>>();
271 String query = "SELECT table_name FROM information_schema.tables WHERE table_schema='"
272 + dbName + "' AND table_name= '"+ tableName + "';";
273
274 tableNames = jdbcTemplateObject.queryForList(query);
275
276 if (!tableNames.isEmpty()) {
277 log.info("table was found");
278 return true;
279 }
280 log.info("table wasn't found");
281 return false;
282
283 }
284
285 // pulls columns from the database
286 public List<Map<String, Object>> getTablesFromDB(String dbName,
287 String[] tableNames) {
288 // output list
289 List<Map<String, Object>> database = new ArrayList<Map<String, Object>>();
290
291 // checks tablenames against column tables
292 for (int i = 0; i < tableNames.length; i++) {
293 String query = "SELECT * FROM " + dbName + "." + tableNames[i] + " WHERE dateModified = (SELECT MAX(dateModified) FROM " + dbName + "." + tableNames[i]+");";
294
295 database.addAll(jdbcTemplateObject.queryForList(query));
296 }
297 return database;
298 }
299
300 /************************** WRITING TO TABLES **************************************************************************/
301
302 public void writeToTables(ArrayList<Table> createList , Timestamp now) {
303 String[] sql = new String[createList.size()];
304 for (int i = 0; i < createList.size(); i++) {
305
306 //temporary local instance of kvp map
307 ModelMap local = createList.get(i).getKvp();
308 // start building sql statement
309 String query = "INSERT INTO " + createList.get(i).getName()
310 + "(`name`, `columnCount`, `dbName`, `description`, `tags`, "
311 + "`dateModified`, `uniqueIdentifier`";
312 // if local is not empty add in column for dynamic columns
313 if ( local != null) {
314 query += ", `dynamic_cols`";
315 }
316 // add in values for the columns
317 query +=" ) VALUES ('" + createList.get(i).getName() + "', '"
318 + createList.get(i).getTableColumnCounter() + "', '"
319 + createList.get(i).getDbName() + "', '"
320 + createList.get(i).getDescription() + "', '"
321 + createList.get(i).getTags() + "', '"
322 + now + "', '"
323 + createList.get(i).getUniqueIdentity() + "' ";
324
325 // build the text string that holds all the dynamic columns
326 int k = 0;
327 if ( local != null) {
328 String dynam_cols = ",'";
329 for (Entry<String, Object> entry : local.entrySet()) {
330 dynam_cols += (String)entry.getKey() + ":" + (String)entry.getValue() ;
331 k++;
332 if (k < local.size()){
333 dynam_cols += "|";
334 }
335 }
336 dynam_cols += "'";
337 query += dynam_cols;
338 }
339 // adds end marker to insert statement
340 query += " ); ";
341 // prints query to log
342 log.info(query);
343 jdbcTemplateObject.execute(query);
344 // adds query to sql commands to be batch executed
345 sql[i] = query;
346 }
347
348 }
349
350 /************************** WRITING TO TABLES_COLUMNS **************************************************************************/
351
352 public void writeToTables_Column(ArrayList<Column> createList, Timestamp now) {
353 String[] sql = new String[createList.size()];
354 for (int i = 0; i < createList.size(); i++) {
355
356 //temporary local instance of kvp map
357 ModelMap local = createList.get(i).getKvp();
358 // start building sql statement
359 String query = "INSERT INTO " + createList.get(i).getTableName()+"_Column"
360 + " ( `name`, `tableName`, `dbName`, `description`,"
361 + " `tags`, `dateModified`, `uniqueIdentifier`";
362 // if local is not empty add in column for dynamic columns
363 if ( local != null) {
364 query += ", `dynamic_cols`";
365 }
366 // add in values for the columns
367 query +=" ) VALUES ('" + createList.get(i).getName() + "', '"
368 + createList.get(i).getTableName() + "', '"
369 + createList.get(i).getDbName() + "', '"
370 + createList.get(i).getDescription() + "', '"
371 + createList.get(i).getTags() + "', '"
372 + now + "', '"
373 + createList.get(i).getUniqueIdentity() + "' ";
374
375 // build the text string that holds all the dynamic columns
376 int k = 0;
377 if ( local != null) {
378 String dynam_cols = ",'";
379 for (Entry<String, Object> entry : local.entrySet()) {
380 dynam_cols += (String)entry.getKey() + ":" + (String)entry.getValue() ;
381 k++;
382 if (k < local.size()){
383 dynam_cols += "|";
384 }
385 }
386 dynam_cols += "'";
387 query += dynam_cols;
388 }
389 // adds end marker to insert statement
390 query += ");";
391 // prints query to log
392 log.info(query);
393 jdbcTemplateObject.execute(query);
394 // adds query to sql commands to be batch executed
395 sql[i] = query;
396 }
397
398 }
399}