package com.logicaldoc.util.dbinit; import java.io.File; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.hsqldb.cmdline.SqlFile; import org.hsqldb.cmdline.SqlToolError; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.logicaldoc.util.io.FileUtil; /** * Database initialisation utility * * @author Michael Scholz * @author Marco Meschieri */ public class DBInit { protected static Logger log = LoggerFactory.getLogger(DBInit.class); private String dbms = ""; private String driver = ""; private String url = ""; private String username = ""; private String password = ""; private Connection con; // List of sql files to be executed private List<String> sqlList = new ArrayList<String>(); /** * A list of sql files to execute * * @param sqlList The list of sql files */ public DBInit(List<String> sqlList) { this.sqlList = sqlList; } public DBInit() { } /** * Executes all the sql files defined in the constructor */ public void execute() { try { doConnection(); for (String sql : sqlList) { try { execute(sql); } catch (Exception e) { log.error("Failed to execute " + sql, e); } } } catch (Throwable e) { log.error(e.getMessage(), e); try { if (con != null) con.close(); } catch (SQLException e1) { e1.printStackTrace(); } } } /** * Execute a SQL statements in the passed string * * @param sql The SQL to execute */ public void executeSql(String sql) { try { doConnection(); PreparedStatement st = con.prepareStatement(sql); st.execute(); st.close(); } catch (Throwable e) { log.error(e.getMessage(), e); log.error("Failed to execute " + sql, e); try { if (con != null) con.close(); } catch (SQLException e1) { e1.printStackTrace(); } } } /** * Executes a single sql file * * @param sqlFile Path of the file to execute(it can be a classpath * resource) * @throws IOException * @throws SqlToolError * @throws SQLException */ private void execute(String sqlFile) throws IOException, SQLException { log.debug("Execute " + sqlFile); System.out.println("Execute " + sqlFile); File file = new File(sqlFile); if (!file.exists() || !file.canRead()) { file = File.createTempFile(file.getName(), ".sql"); file.deleteOnExit(); FileUtil.copyResource(sqlFile, file); } SqlFile sFile = new SqlFile(file, "Cp1252", false); try { sFile.setContinueOnError(true); sFile.setConnection(con); sFile.execute(); } catch (SqlToolError e) { throw new SQLException(e.getMessage()); } } protected void doConnection() { try { Class.forName(driver); con = DriverManager.getConnection(url, username, password); con.setAutoCommit(true); } catch (Exception ex) { log.error(ex.getMessage(), ex); } } protected void rollback() { try { con.rollback(); } catch (Exception ex) { log.error("can't rollback", ex); } } /** * This method returns the state of the connection to the database. * * @return checks true if the database is up and running and well connected */ public boolean isConnected() { try { return !con.isClosed(); } catch (Exception ex) { log.debug("db-connection is open: {}", ex.getMessage(), ex); return false; } } /** * This method tests a connection to the database * * @return true if the database can be connected */ public boolean testConnection() { boolean result = false; try { Class.forName(driver); con = DriverManager.getConnection(url, username, password); result = true; con.close(); } catch (Exception ex) { ex.printStackTrace(); log.error(ex.getMessage(), ex); } return result; } public void setDriver(String driver) { this.driver = driver; } public void setUrl(String url) { this.url = url; } public void setUsername(String username) { this.username = username; } public void setPassword(String password) { this.password = password; } public List<String> getSqlList() { return sqlList; } public String getDbms() { return dbms; } public void setDbms(String dbms) { this.dbms = dbms; } }