/** @return return tableName by index */
public String getTableName(int index) {
List<String> names = makeTableNamesArr();
lg.getLog()
.info(
lg.getTime()
+ " method: getTableName(int index) Table name '"
+ names.get(index)
+ "' returned");
return names.get(index);
}
/**
* @return List<TypeErrorModel> , TypeErrorModel.class represents one row of the table 'typeerror'
*/
public List<TypeErrorModel> getTypeerrorTableContent() {
String sql = "SELECT * FROM typeerror";
lg.getLog().info(lg.getTime() + " method: getTypeerrorTableContent() SQL: " + sql);
RowMapper<TypeErrorModel> mapper =
new RowMapper<TypeErrorModel>() {
public TypeErrorModel mapRow(ResultSet resultSet, int rowNum) throws SQLException {
return TypeErrorModel.createFromResultSet(resultSet);
}
};
List<TypeErrorModel> tableContent = jdbcTemplate.query(sql, mapper);
lg.getLog().info(lg.getTime() + " List form query 'typeerror' created success");
return tableContent;
}
/** @return list of table names exist in data base 'wlogs' */
private List<String> makeTableNamesArr() {
String sql = "SHOW TABLES";
lg.getLog().info(lg.getTime() + " method: makeTableNamesArr() SQL: " + sql);
RowMapper<String> mapper =
new RowMapper<String>() {
public String mapRow(ResultSet resultSet, int i) throws SQLException {
return resultSet.getString(1);
}
};
List<String> names = jdbcTemplate.query(sql, mapper);
lg.getLog().info(lg.getTime() + " Making list of table names result OK");
return names;
}
/** @return return joined tables 'wlogs' and 'typerror' by natural join(by id) */
public List<TypeErrorModel> joinTables(int[] tblNumber) {
if (tblNumber.length < 2) {
throw new NullPointerException(
"Вы выбрали только одну таблицу."
+ "Вы должны выбрать не менее двух таблиц для объединения. Вернитесь назад и сделайте выбор еще раз");
}
String sql =
String.format(
"SELECT * FROM %s NATURAL JOIN %s",
getTableName(tblNumber[0]), getTableName(tblNumber[1]));
lg.getLog().info(lg.getTime() + " method: joinTables(int[]) SQL: " + sql);
RowMapper<TypeErrorModel> mapper =
new RowMapper<TypeErrorModel>() {
public TypeErrorModel mapRow(ResultSet resultSet, int i) throws SQLException {
return TypeErrorModel.createFromResultSet(resultSet);
}
};
List<TypeErrorModel> joinedTables = jdbcTemplate.query(sql, mapper);
lg.getLog().info(lg.getTime() + " Making list of joined columns result OK");
return joinedTables;
}
/* for test use only. should be deleted in production*/
public void sqlInsert() {
try {
lg.getLog().info(lg.getTime() + " method: sqlInsert() " + " Trying to create table 'wlogs'");
jdbcTemplate.execute("DROP TABLE IF EXISTS wlogs");
jdbcTemplate.execute(
"CREATE TABLE IF NOT EXISTS wlogs"
+ "(id INT NOT NULL PRIMARY KEY ,"
+ "occurred_at DATE NOT NULL,"
+ "error_source VARCHAR(20) NOT NULL,"
+ "error_level MEDIUMTEXT NOT NULL,"
+ "error_description LONGTEXT)");
jdbcTemplate.update(
"INSERT INTO wlogs" + " VALUES(1, '2016-02-15', 'event','DEBUG', 'Description id 1')");
jdbcTemplate.update(
"INSERT INTO wlogs" + " VALUES(2, '2016-01-15', 'event','DEBUG', 'Description id 2')");
jdbcTemplate.update(
"INSERT INTO wlogs" + " VALUES(3, '2016-02-16', 'event','DEBUG', 'Description id 3')");
lg.getLog().info(lg.getTime() + " Table wlogs created.");
lg.getLog()
.info(lg.getTime() + " method: sqlInsert() " + " Trying to create table 'typeerror'");
jdbcTemplate.execute("DROP TABLE IF EXISTS typeerror");
jdbcTemplate.execute(
"CREATE TABLE IF NOT EXISTS typeerror(id INT NOT NULL PRIMARY KEY,"
+ "error_level VARCHAR(10) NOT NULL,"
+ "critical VARCHAR(45) NOT NULL )");
jdbcTemplate.update("INSERT INTO typeerror" + " VALUES(1,'DEBUG','MINOR')");
jdbcTemplate.update("INSERT INTO typeerror" + " VALUES(2,'DEBUG','TRIVIAL')");
jdbcTemplate.update("INSERT INTO typeerror" + " VALUES(3,'DEBUG','MAJOR')");
jdbcTemplate.update("INSERT INTO typeerror" + " VALUES(4,'DEBUG','CRITICAL')");
lg.getLog().info(lg.getTime() + " Table 'typeerror' created.");
sqlCheck = " DB updated success";
} catch (Exception e) {
sqlCheck = lg.getTime() + "Have error: " + e;
lg.getLog().error(sqlCheck);
}
}
/* for test use only. should be deleted in production*/
public String sqlInsertCheck() {
sqlInsert();
lg.getLog().info(lg.getTime() + " Finished with result: " + sqlCheck);
return sqlCheck;
}
