| /******************************************************************************** |
| * Copyright (c) 2018 Mettenmeier GmbH |
| * |
| * See the NOTICE file(s) distributed with this work for additional |
| * information regarding copyright ownership. |
| * |
| * This program and the accompanying materials are made available under the |
| * terms of the Eclipse Public License 2.0 which is available at |
| * http://www.eclipse.org/legal/epl-2.0 |
| * |
| * SPDX-License-Identifier: EPL-2.0 |
| ********************************************************************************/ |
| package org.eclipse.openk.sp.db.config; |
| |
| import java.io.IOException; |
| import java.io.InputStream; |
| import java.nio.charset.StandardCharsets; |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.PreparedStatement; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.text.MessageFormat; |
| import java.util.Properties; |
| |
| import org.apache.commons.io.IOUtils; |
| import org.apache.log4j.Logger; |
| import org.eclipse.openk.sp.util.FileHelper; |
| import org.springframework.stereotype.Service; |
| |
| @Service |
| public class DbInitializer { |
| public static final Logger LOGGER = Logger.getLogger(DbInitializer.class); |
| private String dbUrl = "jdbc:postgresql://localhost:5432/BP"; |
| private String dbDriver = "org.postgresql.Driver"; |
| private String dbUsername = "bp"; |
| private String dbPass = "bp"; |
| private String dbSchema = "public"; |
| private String dbScriptDir = "postgres"; |
| |
| private FileHelper fh = new FileHelper(); |
| |
| public static final String APP_PROPERTIES_NAME = "/application.properties"; |
| private static final String SQL_SCRIPT_DELETE_KEY = "sql.scripts.delete"; |
| private static final String SQL_SCRIPT_INIT_KEY = "sql.scripts.init"; |
| private static final String SQL_SCRIPT_HIST_KEY = "sql.scripts.hist"; |
| |
| public void configureDatabase() |
| throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException, IOException { |
| Properties property = fh.loadPropertiesFromResource(APP_PROPERTIES_NAME); |
| dbUrl = property.getProperty("db.url"); |
| dbDriver = property.getProperty("db.driver"); |
| dbUsername = property.getProperty("db.username"); |
| dbPass = property.getProperty("db.password"); |
| dbSchema = property.getProperty("db.schema"); |
| |
| if (dbDriver.equals("oracle.jdbc.OracleDriver")) { |
| dbScriptDir = "oracle"; |
| } |
| |
| try { |
| if (Boolean.parseBoolean(property.getProperty("db.redeploy"))) { |
| this.initializeDatabase(); |
| } |
| } catch (Exception e) { |
| LOGGER.error(e, e); |
| } |
| } |
| |
| protected void initializeDatabase() |
| throws IOException, InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException { |
| Properties property = fh.loadPropertiesFromResource(APP_PROPERTIES_NAME); |
| // run "delete from" script |
| this.loopOverSqlFiles(property.getProperty(SQL_SCRIPT_DELETE_KEY).split(",")); |
| // run "hist" script |
| this.loopOverSqlFiles(property.getProperty(SQL_SCRIPT_HIST_KEY).split(",")); |
| // run "init" script |
| this.loopOverSqlFiles(property.getProperty(SQL_SCRIPT_INIT_KEY).split(",")); |
| |
| } |
| |
| protected void loopOverSqlFiles(String[] sqlFileNames) |
| throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException { |
| for (String sqlScriptName : sqlFileNames) { |
| LOGGER.debug("run script: /META-INF/sql/" + dbScriptDir + "/" + sqlScriptName + ".sql"); |
| this.runSQLStatementByFile("/META-INF/sql/" + dbScriptDir + "/" + sqlScriptName + ".sql"); |
| } |
| } |
| |
| protected void runSQLStatementByFile(String fileName) |
| throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException { |
| Connection conn = this.getJdbcConnection(); |
| PreparedStatement stmt = null; |
| try { |
| conn.setAutoCommit(false); |
| String sql = this.readSqlFromFile(fileName); |
| // replace schema name with configured name |
| sql = sql.replaceAll("'", "''"); |
| sql = MessageFormat.format(sql, dbSchema); |
| String[] sqlRow = sql.split("[\\r\\n]+"); |
| int a = 0; |
| for (String sqlLine : sqlRow) { |
| LOGGER.trace("SQL: " + sqlLine); |
| if (!sqlLine.isEmpty() && !sqlLine.startsWith("--") && sqlLine.charAt(0) != '/') { |
| |
| stmt = conn.prepareStatement(this.cutAndTrimSQL(sqlLine)); |
| stmt.execute(); |
| conn.commit(); |
| stmt.close(); |
| } |
| |
| // close and reopen connection after 50 records to avoid that all cursors are in |
| // use |
| if (a > 50) { |
| conn.close(); |
| conn = this.getJdbcConnection(); |
| conn.setAutoCommit(false); |
| a = 0; |
| } |
| a++; |
| } |
| |
| } catch (Exception e) { |
| LOGGER.error(e, e); |
| conn.rollback(); |
| } finally { |
| if (stmt != null) { |
| stmt.close(); |
| } |
| conn.close(); |
| } |
| } |
| |
| protected String readSqlFromFile(String fileName) throws IOException { |
| String result = ""; |
| InputStream is = fh.loadFileFromResource(fileName); |
| if (is != null) { |
| result = IOUtils.toString(is, StandardCharsets.UTF_8.name()); |
| } |
| return result; |
| } |
| |
| protected Connection getJdbcConnection() |
| throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException { |
| try { |
| Class.forName(dbDriver).newInstance(); |
| return DriverManager.getConnection(dbUrl, dbUsername, dbPass); |
| } catch (SQLException e) { |
| LOGGER.error(e, e); |
| throw e; |
| } |
| } |
| |
| protected void closeConnection(Connection conn) throws SQLException { |
| try { |
| conn.close(); |
| } catch (SQLException e) { |
| LOGGER.error(e, e); |
| throw e; |
| } |
| } |
| |
| protected void closeStatement(Statement stmt) throws SQLException { |
| try { |
| if (stmt != null && !stmt.isClosed()) { |
| stmt.close(); |
| } |
| } catch (SQLException e) { |
| LOGGER.error(e, e); |
| throw e; |
| } |
| } |
| |
| /** |
| * Method to trim() and cut off ending ; for oracle scripts. |
| * |
| * @param sql |
| * @param schema |
| * @return |
| */ |
| protected String cutAndTrimSQL(String sql) { |
| sql = sql.trim(); |
| if (dbScriptDir.equalsIgnoreCase("Oracle") && !sql.isEmpty() && sql.charAt(sql.length() - 1) == ';') { |
| return sql.substring(0, sql.length() - 1).trim(); |
| } |
| return sql.trim(); |
| } |
| |
| public String getSchemaName() { |
| return dbSchema; |
| } |
| |
| } |