1   package org.wcb.resources;
2   
3   import org.wcb.model.util.SpringUtil;
4   import org.wcb.model.service.IServicesConstants;
5   import org.apache.commons.dbcp.BasicDataSource;
6   
7   import java.sql.Connection;
8   import java.sql.SQLException;
9   import java.sql.Statement;
10  import java.io.InputStream;
11  import java.io.BufferedReader;
12  import java.io.InputStreamReader;
13  import java.io.IOException;
14  import java.util.logging.Logger;
15  import java.util.logging.Level;
16  
17  /**
18   * <small>
19   * <p/>
20   * Copyright (c)  2006  wbogaardt.
21   * Permission is granted to copy, distribute and/or modify this document
22   * under the terms of the GNU Free Documentation License, Version 1.2
23   * or any later version published by the Free Software Foundation;
24   * with no Invariant Sections, no Front-Cover Texts, and no Back-Cover
25   * Texts.  A copy of the license is included in the section entitled "GNU
26   * Free Documentation License".
27   * <p/>
28   * $File:  $ <br>
29   * $Change:  $ submitted by $Author: wbogaardt $ at $DateTime: Apr 20, 2006 8:48:46 AM $ <br>
30   * </small>
31   *
32   * @author wbogaardt
33   * @version 1
34   *          Date: Apr 20, 2006
35   *          Time: 8:48:46 AM
36   */
37  
38  
39  public class HSQLDatabaseInitializer {
40  
41      private Connection conn;
42      private Logger LOG = Logger.getLogger(HSQLDatabaseInitializer.class.getName());
43  
44      public HSQLDatabaseInitializer() {
45          try
46          {
47              initDatabase();
48          }
49          catch (SQLException ex) {
50              LOG.log(Level.WARNING, "Database table already exists");
51          }
52      }
53  
54      private void initDatabase() throws SQLException {    // note more general exception
55          BasicDataSource dao = (BasicDataSource) SpringUtil.getApplicationContext().getBean(IServicesConstants.DATA_SOURCE);
56          conn = dao.getConnection();
57          if (dao.getDriverClassName().equalsIgnoreCase("org.hsqldb.jdbcDriver")) {
58              try {
59                  update("SET WRITE_DELAY 2");
60                  loadFile("org/wcb/resources/hsql/createtable.sql");
61              }
62              catch (SQLException sqle) {
63                  LOG.log(Level.WARNING, "Failed to create tables. " + sqle.getMessage());
64              }
65          }
66      }
67  
68      public void clearDatabase() throws SQLException {
69          BasicDataSource dao = (BasicDataSource) SpringUtil.getApplicationContext().getBean(IServicesConstants.DATA_SOURCE);
70          conn = dao.getConnection();
71          if (dao.getDriverClassName().equalsIgnoreCase("org.hsqldb.jdbcDriver")) {
72              try {
73                  loadFile("org/wcb/resources/hsql/deletetable.sql");
74                  loadFile("org/wcb/resources/hsql/createtable.sql");
75                  loadFile("org/wcb/resources/hsql/insertdata.sql");
76              }
77              catch (SQLException sqle) {
78                  LOG.log(Level.WARNING, "Failed to create tables. ", sqle);
79              }
80          }
81      }
82  
83      private void loadFile(String filename) throws SQLException {
84          InputStream ips;
85          ClassLoader cl = Thread.currentThread().getContextClassLoader();
86          try {
87              ips = cl.getResourceAsStream(filename);
88  
89              BufferedReader br = new BufferedReader(new InputStreamReader(ips));
90              String sqlcmd;
91              while ((sqlcmd = br.readLine()) != null) {
92                  update(sqlcmd);
93              }
94          }
95          catch (IOException ioe) {
96              LOG.log(Level.WARNING, "Unable to read file for " + filename + " - ", ioe);
97          }
98      }
99  
100     public void shutdown() throws SQLException {
101 
102         Statement st = conn.createStatement();
103 
104         // db writes out to files and performs clean shuts down
105         // otherwise there will be an unclean shutdown
106         // when program ends
107         st.execute("SHUTDOWN");
108         conn.close();    // if there are no other open connection
109     }
110 
111     public synchronized void update(String expression) throws SQLException {
112 
113         Statement st;
114 
115         st = conn.createStatement();    // statements
116 
117         int i = st.executeUpdate(expression);    // run the query
118 
119         if (i == -1) {
120             LOG.log(Level.INFO, "db error : " + expression);
121         }
122 
123         st.close();
124     }
125 }
126