1    package geekblog;
2    
3    
4    import java.io.*;
5    import java.sql.*;
6    
7    
8    /**
9     * JDBCDataManager derivative specifically for storing into an HSQLDB in-proc
10    * database. (Only customization is in database creation scripts.)
11    */
12   public class HSQLDataManager
13           extends JDBCDataManager
14   {
15       /**
16        * Constructor placeholder; all real work done in init()
17        */
18       public HSQLDataManager()
19       {
20       }
21   
22   
23       /**
24        * Initializer. Customizes the JDBC URL to point to the file path location
25        * where the webapp is installed, plus customize the DDL for the database to
26        * HSQL needs.
27        */
28       public void init(javax.servlet.ServletContext context)
29       {
30           String path = context.getRealPath("index.jsp");
31           path = path.substring(0, path.lastIndexOf(File.separator)) + File.separator + "WEB-INF";
32   
33           String jdbcURL = "jdbc:hsqldb:" + path + File.separator + "GeekBlog";
34           String jdbcUser = "sa";
35           String jdbcPassword = "";
36   
37           try
38           {
39               Class.forName("org.hsqldb.jdbcDriver");
40               conn = DriverManager.getConnection(jdbcURL, jdbcUser, jdbcPassword);
41           }
42           catch (Exception ex)
43           {
44               context.log("Exception while attempting connection to HSQL....", ex);
45               ex.printStackTrace();
46               throw new RuntimeException(ex);
47           }
48   
49           try
50           {
51             Statement stmt = conn.createStatement();
52             ResultSet rs = stmt.executeQuery("SELECT version FROM dbversion");
53   
54             if (rs.next())
55             {
56               long version = rs.getLong("version");
57               if (version == 1L)
58               {
59                   adaptVersionOneToCurrent(context);
60               }
61             }
62           }
63           catch (SQLException sqlEx)
64           {
65               // If this threw an exception, it's because the database doesn't exist.
66               // Create it
67               createDatabase(context);
68           }
69       }
70   
71       private void createDatabase(javax.servlet.ServletContext context)
72       {
73           context.log("*** Creating database.... ***");
74   
75           try
76           {
77               Statement stmt = conn.createStatement();
78   
79               String[] ddlStatements = new String[]
80               {
81                   "CREATE TABLE entries (ID bigint, posted datetime, title varchar, text varchar)",
82                   "CREATE TABLE articles (entryID bigint)",
83                   "CREATE TABLE comments (entryID bigint, name varchar, email varchar, comment varchar)",
84                   "CREATE TABLE links (linkID integer identity, linkText varchar, linkURL varchar)",
85                   "CREATE TABLE dbversion (version integer)",
86                   "INSERT INTO dbversion VALUES (2)"
87               };
88   
89               for (int i = 0; i < ddlStatements.length; i++)
90                   stmt.executeUpdate(ddlStatements[i]);
91           }
92           catch (SQLException sqlEx2)
93           {
94               sqlEx2.printStackTrace();
95               context.log("*** Error creating database ***", sqlEx2);
96           }
97       }
98   
99       private void adaptVersionOneToCurrent(javax.servlet.ServletContext context)
100      {
101          context.log("*** Adapting database v1 to current .... ***");
102  
103          try
104          {
105              Statement stmt = conn.createStatement();
106  
107              String[] ddlStatements = new String[]
108              {
109                  "CREATE TABLE articles (entryID bigint)",
110                  "UPDATE dbversion SET version = 2"
111              };
112  
113              for (int i = 0; i < ddlStatements.length; i++)
114                  stmt.executeUpdate(ddlStatements[i]);
115          }
116          catch (SQLException sqlEx2)
117          {
118              sqlEx2.printStackTrace();
119              context.log("*** Error creating database ***", sqlEx2);
120          }
121      }
122  }
123