KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > core > persist > db > mysql > MySQLDatabaseImpl


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: MySQLDatabaseImpl.java,v 1.25 2007/01/24 06:12:31 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.mysql;
23
24 import java.sql.Connection JavaDoc;
25 import java.sql.PreparedStatement JavaDoc;
26 import java.sql.SQLException JavaDoc;
27 import java.sql.Statement JavaDoc;
28 import java.util.Iterator JavaDoc;
29 import java.util.Map JavaDoc;
30 import java.util.logging.Level JavaDoc;
31 import java.util.logging.Logger JavaDoc;
32
33 import org.opensubsystems.core.data.BasicDataObject;
34 import org.opensubsystems.core.data.ModifiableDataObject;
35 import org.opensubsystems.core.error.OSSDatabaseAccessException;
36 import org.opensubsystems.core.error.OSSException;
37 import org.opensubsystems.core.persist.db.DatabaseConnectionFactoryImpl;
38 import org.opensubsystems.core.persist.db.DatabaseImpl;
39 import org.opensubsystems.core.persist.db.DatabaseTransactionFactoryImpl;
40 import org.opensubsystems.core.util.DatabaseUtils;
41 import org.opensubsystems.core.util.Log;
42
43 /**
44  * Management layer for My SQL database (www.mysql.com)
45  *
46  * TODO: Improve: Consider storing all data in UTF-8
47  * See http://uwstopia.nl/blog/2007/01/simple-utf-8-and-mysql-how-to
48  * - Make sure you append CHARACTER SET 'UTF8' to each CREATE TABLE statement.
49  * Example: CREATE TABLE test (id SERIAL PRIMARY KEY, somefield VARCHAR(4))
50  * CHARACTER SET 'UTF8';. This can be done at the database level as well (when
51  * using CREATE DATABASE), but most of the times this is done for you by an
52  * external party (hoster, sysadmins, ...)
53  * - Issue the following query immediately after you established a database
54  * connection (most likely somewhere in your initialization routines):
55  * SET NAMES='UTF8'
56  *
57  * @version $Id: MySQLDatabaseImpl.java,v 1.25 2007/01/24 06:12:31 bastafidli Exp $
58  * @author Julo Legeny
59  * @code.reviewer Miro Halas
60  * @code.reviewed 1.20 2006/04/05 05:03:08 bastafidli
61  */

62 public class MySQLDatabaseImpl extends DatabaseImpl
63 {
64    // Cached values ////////////////////////////////////////////////////////////
65

66    /**
67     * Logger for this class
68     */

69    private static Logger JavaDoc s_logger = Log.getInstance(MySQLDatabaseImpl.class);
70
71    // Constructors /////////////////////////////////////////////////////////////
72

73    /**
74     * Default constructor for empty database.
75     *
76     * @throws OSSException - problem connecting to database
77     */

78    public MySQLDatabaseImpl(
79    ) throws OSSException
80    {
81       super();
82    }
83    
84    // Database administration //////////////////////////////////////////////////
85

86    /**
87     * {@inheritDoc}
88     */

89    protected void createUser(
90       Connection JavaDoc cntAdminDBConnection
91    ) throws OSSException
92    {
93       // Now create the user
94
PreparedStatement JavaDoc pstmQuery = null;
95       try
96       {
97          String JavaDoc strDatabaseURL = DatabaseConnectionFactoryImpl.getInstance().getDatabaseURL();
98
99          String JavaDoc strHost = strDatabaseURL.substring(
100                              strDatabaseURL.indexOf("://") + 3,
101                              strDatabaseURL.lastIndexOf("/"));
102          
103          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
104          
105          buffer.append("GRANT Select, Insert, Update, Delete, Index, Alter, " +
106                        "Create, Drop, References ON ");
107          buffer.append(strDatabaseURL.substring(
108                           strDatabaseURL.lastIndexOf("/") + 1, strDatabaseURL.length()));
109          buffer.append(".* TO '");
110          buffer.append(DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser());
111          buffer.append("'@'" + strHost + "' ");
112          buffer.append("IDENTIFIED BY '");
113          buffer.append(DatabaseConnectionFactoryImpl.getInstance().getDatabasePassword());
114          buffer.append("'");
115
116          try
117          {
118             pstmQuery = cntAdminDBConnection.prepareStatement(buffer.toString());
119    
120             if (pstmQuery.execute())
121             {
122                // Close any results
123
pstmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
124             }
125          }
126          finally
127          {
128             DatabaseUtils.closeStatement(pstmQuery);
129             pstmQuery = null;
130          }
131
132          // grant privileges
133
buffer.delete(0, buffer.length());
134          buffer.append("UPDATE mysql.user SET Select_priv = 'Y', Insert_priv = 'Y', " +
135                        "Update_priv = 'Y', Delete_priv = 'Y', Create_priv = 'Y', " +
136                        "Drop_priv = 'Y', Reload_priv = 'N', Shutdown_priv = 'N', " +
137                        "Process_priv = 'N', File_priv = 'N', Grant_priv = 'Y', " +
138                        "References_priv = 'Y', Index_priv = 'Y', Alter_priv = 'Y' " +
139                        "WHERE Host = '");
140          buffer.append(strHost);
141          buffer.append("' AND User = '");
142          buffer.append(DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser());
143          buffer.append("'");
144          
145          try
146          {
147             pstmQuery = cntAdminDBConnection.prepareStatement(buffer.toString());
148    
149             if (pstmQuery.execute())
150             {
151                // Close any results
152
pstmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
153             }
154          }
155          finally
156          {
157             DatabaseUtils.closeStatement(pstmQuery);
158             pstmQuery = null;
159          }
160
161          // At this point we don't know if this is just a single operation
162
// and we need to commit or if it is a part of bigger transaction
163
// and the commit is not desired until all operations proceed.
164
// Therefore let the DatabaseTransactionFactory resolve it
165
DatabaseTransactionFactoryImpl.getInstance().commitTransaction(
166                                                  cntAdminDBConnection);
167          
168          s_logger.log(Level.FINER, "Database user "
169                              + DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser()
170                              + " with password "
171                              + DatabaseConnectionFactoryImpl.getInstance().getDatabasePassword()
172                              + " created.");
173       }
174       catch (SQLException JavaDoc sqleExc)
175       {
176          try
177          {
178             // At this point we don't know if this is just a single operation
179
// and we need to commit or if it is a part of bigger transaction
180
// and the commit is not desired until all operations proceed.
181
// Therefore let the DatabaseTransactionFactory resolve it
182
DatabaseTransactionFactoryImpl.getInstance().rollbackTransaction(
183                cntAdminDBConnection);
184          }
185          catch (SQLException JavaDoc sqleExc2)
186          {
187             // Ignore this
188
s_logger.log(Level.WARNING,
189                                 "Failed to rollback changes for creation of user.",
190                                 sqleExc2);
191          }
192          s_logger.log(Level.SEVERE,
193                              "Unable to create default database user.",
194                              sqleExc);
195          throw new OSSDatabaseAccessException("Unable to create default database user.",
196                                               sqleExc);
197       }
198       finally
199       {
200          DatabaseUtils.closeStatement(pstmQuery);
201       }
202    }
203
204    /**
205     * {@inheritDoc}
206     */

207    public void startDatabaseServer() throws OSSException
208    {
209       // TODO: Feature: Implement starting database server
210
// There are few ways how to start databae server:
211
// 1. To start MySQL from Java why not invoke a simple batch file:
212
// Runtime.getRuntime().exec("MySqlStart.bat");
213
// Not very funky admittedly but quick and easy to write!
214
// 2. start with the Connector/J documentation
215
// http://dev.mysql.com/doc/connector/j/en/index.html
216
// http://dev.mysql.com/doc/connector/j/en/index.html#id2424110
217
// 3. using JBoss - it was released MySQL Connector/MXJ 1.0.2-alpha
218
// of a JMX MBean for deploying and managing MySQL using the
219
// JBoss "jmx-console".
220
// You can download sources and binaries from:
221
// http://dev.mysql.com/downloads/connector/mxj/1.0.html
222
}
223
224    /**
225     * {@inheritDoc}
226     */

227    public void createDatabaseInstance() throws OSSException
228    {
229       // TODO: Feature: Implement creating database instance
230
// using JBoss - it was released MySQL Connector/MXJ 1.0.2-alpha
231
// of a JMX MBean for deploying and managing MySQL using the
232
// JBoss "jmx-console".
233
// You can download sources and binaries from:
234
// http://dev.mysql.com/downloads/connector/mxj/1.0.html
235
}
236
237    /**
238     * {@inheritDoc}
239     */

240    public void stop(
241    ) throws OSSException
242    {
243       // There are few ways how to stop databae server:
244
// 1. To stop MySQL from Java why not invoke a simple batch file:
245
// Runtime.getRuntime().exec("MySqlStart.bat");
246
// Not very funky admittedly but quick and easy to write!
247
// 2. start with the Connector/J documentation
248
// http://dev.mysql.com/doc/connector/j/en/index.html
249
// http://dev.mysql.com/doc/connector/j/en/index.html#id2424110
250
// 3. using JBoss - it was released MySQL Connector/MXJ 1.0.2-alpha
251
// of a JMX MBean for deploying and managing MySQL using the
252
// JBoss "jmx-console".
253
// You can download sources and binaries from:
254
// http://dev.mysql.com/downloads/connector/mxj/1.0.html
255

256       s_logger.entering(this.getClass().getName(), "stop");
257
258       // TODO: Feature: Implement this so we can safely stop the database when
259
// the application is finished.
260
super.stop();
261
262       s_logger.entering(this.getClass().getName(), "stop");
263    }
264
265    /**
266     * {@inheritDoc}
267     */

268    public int getDatabaseType()
269    {
270       return DatabaseImpl.MYSQL_DATABASE_TYPE;
271    }
272
273    /**
274     * {@inheritDoc}
275     */

276    public String JavaDoc getDatabaseTypeIdentifier()
277    {
278       return DatabaseImpl.MYSQL_DATABASE_TYPE_IDENTIFIER;
279    }
280
281    /**
282     * {@inheritDoc}
283     */

284    public boolean preferCountToLast(
285    )
286    {
287       // For MySQL it is generally faster to execute count(*) than to do last().
288
// We can speed up performance by using select LIMIT X, Y that allows us
289
// to retrieve just specified range of items. But when we use LIMIT X, Y
290
// we cannot use last()
291
return true;
292    }
293
294    /**
295     * {@inheritDoc}
296     */

297    public String JavaDoc getCurrentTimestampFunctionCall()
298    {
299       // MySQL requries () for this call
300
return "now()";
301    }
302
303    /**
304     * {@inheritDoc}
305     */

306    public String JavaDoc getSQLCountFunctionCall()
307    {
308       // MySQL has the best performance when COUNT(ID) or COUNT(*) is used. These
309
// commands are the same and use the PRIMARY index. It is better to use
310
// COUNT(*), because when selecting from a VIEW or several tables there can
311
// be a problem, the ID is ambiguous (table name should be also defined to
312
// specify what table the ID belongs to).
313
return "count(*)";
314    }
315
316    /**
317     * {@inheritDoc}
318     */

319    public boolean hasSelectListRangeSupport(
320    )
321    {
322       // MySQL supports rows limitation by using clause LIMIT X, Y
323
return true;
324    }
325
326    /**
327     * {@inheritDoc}
328     */

329    public Object JavaDoc[] getSQLAnalyzeFunctionCall(
330       Map JavaDoc mapTableNames
331    )
332    {
333       // MySQL uses ANALYZE TABLE <table_name1>, <table_name> to update indexes
334
// and increase performance.
335

336       String JavaDoc[] arrReturn = new String JavaDoc[1];
337       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
338       Iterator JavaDoc itItem;
339       int iIndex = 0;
340
341       buffer.append("analyze table ");
342       itItem = mapTableNames.values().iterator();
343       while (itItem.hasNext())
344       {
345          // construct analyze query for each table from the array
346
if (iIndex > 0)
347          {
348             buffer.append(", ");
349          }
350          buffer.append((String JavaDoc)itItem.next());
351       }
352       arrReturn[0] = buffer.toString();
353
354       // The autocommit has to be false for MySQL
355
return new Object JavaDoc[] {arrReturn, Boolean.FALSE};
356    }
357
358    /**
359     * {@inheritDoc}
360     */

361    public boolean isCallableStatement(
362       String JavaDoc strQuery
363    )
364    {
365       // MySQL doesn't support stored procedures
366
return false;
367    }
368
369    /**
370     * {@inheritDoc}
371     */

372    public void insertAndFetchGeneratedValues(
373       Connection JavaDoc dbConnection,
374       PreparedStatement JavaDoc insertStatement,
375       boolean bIsInDomain,
376       String JavaDoc strTableName,
377       int iIndex,
378       BasicDataObject data
379    ) throws SQLException JavaDoc,
380             OSSException
381    {
382       MySQLDataUtils.insertAndFetchGeneratedValues(dbConnection,
383                            insertStatement, bIsInDomain, strTableName, data);
384    }
385
386    /**
387     * {@inheritDoc}
388     */

389    public void updatedAndFetchGeneratedValues(
390       String JavaDoc strDataName,
391       Connection JavaDoc dbConnection,
392       PreparedStatement JavaDoc updateStatement,
393       boolean bIsInDomain,
394       String JavaDoc strTableName,
395       int iIndex,
396       ModifiableDataObject data
397    ) throws SQLException JavaDoc,
398             OSSException
399    {
400       MySQLDataUtils.updatedAndFetchGeneratedValues(strDataName, dbConnection,
401                         updateStatement, bIsInDomain, strTableName, data);
402    }
403 }
404
Popular Tags