KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > core > persist > db > driver > hsqldb > HsqlDBDatabaseTestSchema


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: HsqlDBDatabaseTestSchema.java,v 1.15 2007/01/07 06:15:33 bastafidli Exp $
7  *
8  * This program is free software; you can redistribute it and/or modify
9  * it under the terms of the GNU General Public License as published by
10  * the Free Software Foundation; version 2 of the License.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program; if not, write to the Free Software
19  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20  */
21
22 package org.opensubsystems.core.persist.db.driver.hsqldb;
23
24 import java.sql.CallableStatement JavaDoc;
25 import java.sql.Connection JavaDoc;
26 import java.sql.PreparedStatement JavaDoc;
27 import java.sql.ResultSet JavaDoc;
28 import java.sql.SQLException JavaDoc;
29 import java.sql.Statement JavaDoc;
30 import java.util.logging.Level JavaDoc;
31 import java.util.logging.Logger JavaDoc;
32
33 import org.opensubsystems.core.util.DatabaseUtils;
34 import org.opensubsystems.core.util.Log;
35 import org.opensubsystems.core.error.OSSDatabaseAccessException;
36 import org.opensubsystems.core.error.OSSException;
37 import org.opensubsystems.core.persist.db.DatabaseConnectionFactory;
38 import org.opensubsystems.core.persist.db.driver.DatabaseTestSchema;
39
40 /**
41  * This class encapsulates details about creation and upgrade
42  * of database schema required to test database driver functionality
43  * for tables which are HSQLDB database specific
44  *
45  * @version $Id: HsqlDBDatabaseTestSchema.java,v 1.15 2007/01/07 06:15:33 bastafidli Exp $
46  * @author Miro Halas
47  * @code.reviewer Miro Halas
48  * @code.reviewed Initial revision
49  */
50 public class HsqlDBDatabaseTestSchema extends DatabaseTestSchema
51 {
52    /*
53       These tables are database specific
54       
55       CREATE TABLE GENERATEDKEY_TEST
56       (
57          TEST_KEY INTEGER IDENTITY,
58          TEST_VALUE VARCHAR(50) NOT NULL
59       )
60    */
61    
62    // Cached values ////////////////////////////////////////////////////////////
63
64    /**
65     * Logger for this class
66     */
67    private static Logger JavaDoc s_logger = Log.getInstance(HsqlDBDatabaseTestSchema.class);
68
69    // Constructors /////////////////////////////////////////////////////////////
70
71    /**
72     * Default constructor.
73     *
74     * @throws OSSException - error occured.
75     */
76    public HsqlDBDatabaseTestSchema(
77    ) throws OSSException
78    {
79       super();
80    }
81   
82
83    // Lifecycle events /////////////////////////////////////////////////////////
84
85    /**
86     * {@inheritDoc}
87     */
88    public void create(
89       Connection JavaDoc cntDBConnection,
90       String JavaDoc strUserName
91    ) throws SQLException JavaDoc
92    {
93       // First create any generic tables
94 super.create(cntDBConnection, strUserName);
95
96       // Now try to create any database specific tables
97 Statement JavaDoc stmQuery = null;
98       try
99       {
100          stmQuery = cntDBConnection.createStatement();
101
102          if (stmQuery.execute("CREATE TABLE GENERATEDKEY_TEST" + NL +
103                               "(" + NL +
104                               " TEST_KEY INTEGER IDENTITY," + NL +
105                               " TEST_VALUE VARCHAR(50) NOT NULL" + NL +
106                               ")"))
107          {
108             // Close any results
109 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
110          }
111          s_logger.log(Level.FINEST, "Table GENERATEDKEY_TEST created.");
112          /*
113          if (stmQuery.execute("grant all on GENERATEDKEY_TEST to " + strUserName))
114          {
115             // Close any results
116             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
117          }
118          Log.getLogger().log(Level.FINEST,
119                              "Access for table GENERATEDKEY_TEST set for user "
120                              + strUserName);
121          */
122       }
123       catch (SQLException JavaDoc sqleExc)
124       {
125          s_logger.log(Level.WARNING, "Failed to create database test schema.",
126                              sqleExc);
127          throw sqleExc;
128       }
129       finally
130       {
131          DatabaseUtils.closeStatement(stmQuery);
132       }
133    }
134
135   /**
136    * {@inheritDoc}
137    */
138   public void createDataSource(
139      DatabaseConnectionFactory dbConnectionFactory,
140      String JavaDoc strDataSourceName,
141      String JavaDoc strDatabaseDriver,
142      String JavaDoc strDatabaseURL,
143      String JavaDoc strUserName,
144      String JavaDoc strUserPassword
145   ) throws OSSDatabaseAccessException
146   {
147       // For HsqlDB has to be created data source with another URL (another database name)
148 dbConnectionFactory.addDataSource(strDataSourceName,
149                                         strDatabaseDriver,
150                                         strDatabaseURL + "2", // there will be used db name OSS2
151 strUserName,
152                                         strUserPassword);
153   }
154
155    /**
156     * {@inheritDoc}
157     */
158    public String JavaDoc getInsertGeneratedKey(
159    )
160    {
161       return "insert into generatedkey_test(test_key, test_value) values (null, ?)";
162    }
163
164    /**
165     * {@inheritDoc}
166     */
167    public int[] executeInsertGeneratedKey2(
168       Connection JavaDoc dbConnection,
169       String JavaDoc strValue
170    ) throws SQLException JavaDoc
171    {
172       PreparedStatement JavaDoc insertStatement = null;
173       CallableStatement JavaDoc callStatement = null;
174       ResultSet JavaDoc rsResults = null;
175       int iInsertCount = 0;
176       int iGeneratedKey = 0;
177       int[] returnValues = null;
178       
179       try
180       {
181          insertStatement = dbConnection.prepareStatement(getInsertGeneratedKey());
182          insertStatement.setString(1, strValue);
183          iInsertCount = insertStatement.executeUpdate();
184
185          callStatement = dbConnection.prepareCall("call identity()");
186
187          rsResults = callStatement.executeQuery();
188          if (rsResults.next())
189          {
190             iGeneratedKey = rsResults.getInt(1);
191
192             returnValues = new int[2];
193             returnValues[0] = iInsertCount;
194             returnValues[1] = iGeneratedKey;
195          }
196       }
197       finally
198       {
199          DatabaseUtils.closeStatement(insertStatement);
200          DatabaseUtils.closeResultSetAndStatement(rsResults, callStatement);
201       }
202       
203       return returnValues;
204    }
205
206    /**
207     * {@inheritDoc}
208     */
209    public int executeUpdateTestValue(
210       Connection JavaDoc dbConnection,
211       String JavaDoc strOldValue,
212       String JavaDoc strNewValue
213    ) throws SQLException JavaDoc
214    {
215       PreparedStatement JavaDoc updateStatement = null;
216       int iUpdateCount = 0;
217
218       try
219       {
220          updateStatement = dbConnection.prepareStatement(
221               "update TRANSACTION_TEST set TEST_VALUE = ? where TEST_VALUE = ?");
222          updateStatement.setString(1, strNewValue);
223          updateStatement.setString(2, strOldValue);
224             
225          // here is the bug in SAP DB which is not seen in HSQLDB, if there is
226 // called stored procedure without output parameters, there is not
227 // returned number of updated records
228 iUpdateCount = updateStatement.executeUpdate();
229       }
230       finally
231       {
232          DatabaseUtils.closeStatement(updateStatement);
233       }
234       
235       return iUpdateCount;
236    }
237
238
239    /**
240     * {@inheritDoc}
241     */
242    public int[] executeInsertRow(
243       Connection JavaDoc dbConnection,
244       String JavaDoc strValue)
245    throws SQLException JavaDoc
246    {
247       PreparedStatement JavaDoc insertStatement = null;
248       int iInsertCount = 0;
249       int iInsertCountReturnedFromSP = 0;
250       int[] returnValues = null;
251       
252       try
253       {
254          insertStatement = dbConnection.prepareStatement(getInsertGeneratedKey());
255          insertStatement.setString(1, strValue);
256          iInsertCount = insertStatement.executeUpdate();
257
258          returnValues = new int[2];
259
260          // value (number of affected rows) returned from insertStatement.executeUpdate();
261 returnValues[0] = iInsertCount;
262          
263          // value (number of inserted rows) returned from stored procedure. It will be always 0
264 // here, because Hsql doesn't support stored procedures.
265 returnValues[1] = iInsertCountReturnedFromSP;
266       }
267       finally
268       {
269          DatabaseUtils.closeStatement(insertStatement);
270       }
271       // HsqlDB doesn't support stored procedures
272 return returnValues;
273    }
274
275    /**
276     * {@inheritDoc}
277     */
278    public void createTestUser(
279       Connection JavaDoc cntAdminDBConnection,
280       String JavaDoc strDatabaseURL,
281       String JavaDoc strUserName,
282       String JavaDoc strUserPassword
283    ) throws SQLException JavaDoc
284    {
285       Statement JavaDoc stmQuery = null;
286       try
287       {
288          String JavaDoc strCreateUserQuery = "CREATE USER " + strUserName + " PASSWORD "
289                                      + strUserPassword + " ADMIN";
290
291          stmQuery = cntAdminDBConnection.createStatement();
292
293          if (stmQuery.execute(strCreateUserQuery))
294          {
295             // Close any results
296 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
297          }
298       }
299       finally
300       {
301          DatabaseUtils.closeStatement(stmQuery);
302       }
303    }
304
305    /**
306     * {@inheritDoc}
307     */
308    public void dropTestUser(
309       Connection JavaDoc cntAdminDBConnection,
310       String JavaDoc strDatabaseURL,
311       String JavaDoc strUserName
312    ) throws SQLException JavaDoc
313    {
314       Statement JavaDoc stmQuery = null;
315       try
316       {
317          String JavaDoc strDropUserQuery = "DROP USER " + strUserName;
318
319          stmQuery = cntAdminDBConnection.createStatement();
320
321          if (stmQuery.execute(strDropUserQuery))
322          {
323             // Close any results
324 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
325          }
326       }
327       finally
328       {
329          DatabaseUtils.closeStatement(stmQuery);
330       }
331    }
332 }
333
Popular Tags