DatabaseConnection


1   // $Id: DatabaseConnection.java 8090 2005-01-17 13:48:25Z  $
2   //
3   //                                   ____.
4   //                       __/\ ______|    |__/\.     _______
5   //            __   .____|    |       \   |    +----+       \
6   //    _______|  /--|    |    |    -   \  _    |    :    -   \_________
7   //   \\______: :---|    :    :           |    :    |         \________>
8   //           |__\---\_____________:______:    :____|____:_____\
9   //                                      /_____|
10  //
11  //              . . . i n   j a h i a   w e   t r u s t . . .
12  //
13  //
14  //  DatabaseConnection
15  //
16  //  30.03.2001  AK  added in jahia.
17  //  01.04.2001  AK  change the package.
18  //
19  
20  package org.jahia.admin.database;
21  
22  import java.sql.Connection  ;
23  import java.sql.DatabaseMetaData  ;
24  import java.sql.DriverManager  ;
25  import java.sql.ResultSet  ;
26  import java.sql.SQLException  ;
27  import java.sql.Statement  ;
28  import java.util.HashMap  ;
29  
30  
31  
32  /**
33   * desc:  It's a class used only by JahiaInstallation and
34   * JahiaAdministration (they only have rights to establish direct connections
35   * to a database and not via the connection pooling). This class mainly
36   * provides you to open test a database connection and to execute sql queries.
37   * All methods returns an <code>int</code>, and not ResultSet or anything
38   * else. The principe is to check only status of connections, queries, etc.
39   *
40   * Copyright:    Copyright (c) 2002
41   * Company:      Jahia Ltd
42   *
43   * @author Alexandre Kraft
44   * @version 1.0
45   */
46  public class DatabaseConnection
47  {
48      private static org.apache.log4j.Logger logger =
49              org.apache.log4j.Logger.getLogger(DatabaseConnection.class);
50  
51      private Statement     theStatement;
52      private Connection    theConnection;
53  
54  
55  
56      /**
57       * Default constructor.
58       * @author  Alexandre Kraft
59       */
60      public DatabaseConnection()
61      {
62          // do nothing :o)
63      } // end constructor
64  
65  
66  
67      /**
68       * Test a database connection, using settings defined by the user via
69       * some inputs like db url, driver, script, etc. The method to test
70       * used is to open the connection, try to create a table test in the
71       * database and to drop this table. It also check if the user has
72       * choosed the script for sqlserver and he have an access database, or,
73       * on the opposite, if he have an access database and he choose the
74       * sqlserver script.
75       * An UTF-8 compliance test can be made by setting to true the
76       * corresponding argument (see the configuration wizard).
77       * The last test executed in this method is to check
78       * if the database selected has already some jahia data inside.
79       * @author  Alexandre Kraft
80       *
81       * @param   script    The database script selected filename.
82       * @param   driver    The JDBC driver for the database.
83       * @param   url       The url where JDBC can found the database.
84       * @param   username  The username required to access the database.
85       * @param   password  The password required to access the database.
86       * @param   runtimeSQL       The first line of the database script.
87       * @param   checkUTF8Compliance  Enabled the UTF-8 test database.
88       * @return  An HashMap containing two Booleans (one for the connection
89       *          test and one for the data inside check (<code>true</code> if
90       *          a test generate error(s), otherwise the return value is
91       *          <code>false</code>) and a String (it's the error message, if
92       *          checks don't generate error(s), the String is simply empty.
93       */
94      public HashMap   databaseTest( String   script,
95                                   String   driver,
96                                   String   url,
97                                   String   username,
98                                   String   password,
99                                   String   runtimeSQL,
100                                  boolean checkUTF8Compliance,
101                                  boolean createTables)
102     {
103         HashMap   hashMap =  new HashMap  ();
104         hashMap.put("testDatabaseTablesAlreadyExists",  Boolean.FALSE);
105         hashMap.put("testDatabaseConnectionError",      Boolean.FALSE);
106         hashMap.put("testDatabaseConnectionMessage",    "");
107 
108         // test to open a database connection...
109         logger.debug("Trying to open a database connection...");
110         int testStatus = 2;
111 
112         try {
113             databaseOpen(driver, url, username, password);
114             testStatus = 0;
115         } catch (ClassNotFoundException   cnfe) {
116             hashMap.put("testDatabaseConnectionError",  Boolean.TRUE); // the connection generate error(s).
117             hashMap.put("testDatabaseConnectionMessage",  "Driver class not found: " + driver + cnfe.getLocalizedMessage());
118             testStatus = 1;
119         } catch (SQLException   sqle) {
120             hashMap.put("testDatabaseConnectionError",  Boolean.TRUE); // the connection generate error(s).
121             hashMap.put("testDatabaseConnectionMessage",
122                         "Error while connecting to the database with url=[" +
123                         url + "] user=[" + username + "] password=[" + password +
124                         "]: " +
125                         sqle.getLocalizedMessage());
126             testStatus = 2;
127         }
128 
129         // make other test only if the connection opened successfully...
130         if(testStatus == 0)
131         {
132             // get the first table name in the script...
133             String          runtimeTableName = runtimeSQL.substring(runtimeSQL.indexOf("jahia_"), runtimeSQL.indexOf("(")).trim();
134             String          dbProductName           =  "";
135 
136             // in first, drop the table by security. if the table exists the results can be false...
137             logger.debug( "Try to talk with the database using table ["+runtimeTableName+"]...");
138 
139             if (createTables) {
140             databaseQuery( "DROP TABLE " + runtimeTableName, true);
141 
142             // okay it's the time to test the *talk* by creating the table and dropping it...
143             testStatus = databaseQuery( runtimeSQL.toString(), false);
144             }
145             // check the UTF-8 compliance only if the table was created successfully.
146             if ((testStatus == 0) && (checkUTF8Compliance)){
147                 // Create 'random' string with Latin, Cyrillic and Chineeze characters
148                 // and insert them to the database.
149                 // My chineeze knowledges are limited but I think that '\u8bed\u8a00'
150                 // characters mean 'language'   :-)
151                 String   testField = "Latin : ���� / Cyrillic : \u0419\u0416 / Chineeze : \u8bed\u8a00";
152                 // FIXME : For coding simplicity 'testfield' is hardcoded here. :(
153                 testStatus += databaseQuery("INSERT INTO " + runtimeTableName +
154                                             "(testfield) VALUES('" + testField + "')", false);
155                 try {
156                     ResultSet   rs = theStatement.executeQuery("SELECT testfield FROM jahia_db_test");
157                     if (rs.next()) {
158                         String   testFieldResult = rs.getString("testfield");
159                         if (!testFieldResult.equals(testField)) {
160                             testStatus++;
161                             hashMap.put("testDatabaseConnectionError", Boolean.TRUE);
162                             hashMap.put("testDatabaseConnectionMessage", "This database seems to be not UTF-8 compliant");
163                             return hashMap;
164                         }
165                     }
166                 } catch (SQLException   sqle) {
167                     logger.debug("Error executing query [SELECT testfield FROM jahia_db_test]", sqle);
168                 }
169             }
170 
171             if (createTables) {
172             testStatus += databaseQuery( "DROP TABLE " + runtimeTableName, false);
173             }
174 
175             // last tests...
176             if(testStatus == 0) {
177                 try {
178                     DatabaseMetaData   dbMetaData =  theConnection.getMetaData();
179                     dbProductName =  dbMetaData.getDatabaseProductName().trim();
180                 } catch (SQLException   sqle) {       // cannot get the metadata from this database (or the product name)...
181                 }
182 
183                 // check if the user has selected sqlserver or access and if it's really this database...
184                 if(script.equals("sqlserver.script") && !dbProductName.equals("Microsoft SQL Server")) {
185                     testStatus  = 1;
186                 } else if(script.equals("msaccess.script") && !dbProductName.equals("ACCESS")) {
187                     testStatus  = 1;
188                 } else {
189                     // FIXME : Is this test still necessary ?
190                     // 'testDatabaseTablesAlreadyExists' entry is never used...
191                     if(databaseQuery("SELECT * FROM " + runtimeTableName, true) == 0) {     // check if the database is already jahia-ified :o)
192                         hashMap.put("testDatabaseTablesAlreadyExists",  Boolean.TRUE);
193                     }
194                 }
195             }
196         }
197 
198         // okay all tests executed...
199         if(testStatus == 0) {
200             logger.debug( "Database responded successfully.");
201         } else {
202             if (!hashMap.containsKey("testDatabaseConnectionError")) {
203                 hashMap.put("testDatabaseConnectionError", Boolean.TRUE);
204             }
205             if (!hashMap.containsKey("testDatabaseConnectionMessage")) {
206                 hashMap.put("testDatabaseConnectionMessage",
207                     "Can't talk with the database. Check your settings.");
208             }
209         }
210 
211         return hashMap;
212     } // end databaseTest
213 
214 
215 
216     /**
217      * Open a database connection, using settings defined by the user like
218      * the database driver, the url, username and password.
219      * @author  Alexandre Kraft
220      *
221      * @param   driver    The JDBC driver for the database.
222      * @param   url       The url where JDBC can found the database.
223      * @param   username  The username required to access the database.
224      * @param   password  The password required to access the database.
225      * @return  <code>0</code> if the connection is open, <code>1</code> if the
226      *          driver class is not found, or <code>2</code> if the connection
227      *          cannot be opened.
228      */
229     public void databaseOpen( String   driver,
230                              String   url,
231                              String   username,
232                              String   password )
233     throws ClassNotFoundException  , SQLException  
234     {
235         // always close a possibly old connection before open a new...
236         databaseClose();
237 
238         // try to open a database connection...
239         Class.forName(driver);
240         theConnection = DriverManager.getConnection(url, username, password);
241         theStatement = theConnection.createStatement();
242     } // end databaseOpen
243 
244 
245 
246     /**
247      * Execute a SQL query. Be careful, this method don't return an ResultSet.
248      * @author  Alexandre Kraft
249      *
250      * @param   sqlCode     The sql query you want to execute.
251      * @return  <code>0</code> if the query generates no error(s) or <code>1</code>
252      *          if there is an exception.
253      */
254     public int databaseQuery( String   sqlCode, boolean quietErrors)
255     {
256         try {
257             theStatement.execute( sqlCode );
258             return 0;
259         } catch (Exception   e) {
260             if (!quietErrors) {
261                 logger.debug("Error executing database query [" + sqlCode + "]", e);
262             }
263             return 1;
264         }
265     } // end databaseQuery
266 
267 
268 
269     /**
270      * Execute a SQL query. Be careful, this method don't return an ResultSet.
271      * @author  Alexandre Kraft
272      *
273      * @param   sqlCode     The sql query you want to execute.
274      *
275      * @throws  Exception   Propagate any exceptions
276      */
277     public void query( String   sqlCode )
278     throws Exception  
279     {
280         theStatement.execute( sqlCode );
281     } // end query
282 
283 
284 
285     /**
286      * Close the current database connection. If the connection statement do
287      * not exists, the exception is simply catched. There is no problem about
288      * this and completely transparent for the user.
289      * @author  Alexandre Kraft
290      */
291     public void databaseClose()
292     {
293         try {
294             theStatement.close();
295         } catch (SQLException   sqle) {
296         } catch (NullPointerException   sqle) {
297         }
298     } // end databaseClose
299 
300 
301 
302     /**
303      * Get the connection of this instance of the class.
304      * @author  Alexandre Kraft
305      *
306      * @return  The connection of this instance of the class
307      */
308     public Connection   getConnection()
309     {
310         return theConnection;
311     } // end getConnection
312 
313 
314 
315     /**
316      * Get the statement of this instance of the class.
317      * @author  Alexandre Kraft
318      *
319      * @return  The statement of this instance of the class
320      */
321     public Statement   getStatement()
322     {
323         return theStatement;
324     } // end getStatement
325 
326 
327 } // end DatabaseConnection
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags