KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > core > persist > db > mssql > MSSQLDatabaseImpl


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: MSSQLDatabaseImpl.java,v 1.22 2007/01/07 06:14:54 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.mssql;
23
24 import java.sql.CallableStatement JavaDoc;
25 import java.sql.Connection JavaDoc;
26 import java.sql.PreparedStatement JavaDoc;
27 import java.sql.SQLException JavaDoc;
28 import java.sql.Statement JavaDoc;
29 import java.util.Iterator JavaDoc;
30 import java.util.Map JavaDoc;
31 import java.util.logging.Level JavaDoc;
32 import java.util.logging.Logger JavaDoc;
33
34 import org.opensubsystems.core.data.BasicDataObject;
35 import org.opensubsystems.core.data.ModifiableDataObject;
36 import org.opensubsystems.core.error.OSSConfigException;
37 import org.opensubsystems.core.error.OSSDatabaseAccessException;
38 import org.opensubsystems.core.error.OSSException;
39 import org.opensubsystems.core.persist.db.DatabaseConnectionFactoryImpl;
40 import org.opensubsystems.core.persist.db.DatabaseImpl;
41 import org.opensubsystems.core.persist.db.DatabaseTransactionFactoryImpl;
42 import org.opensubsystems.core.util.DatabaseUtils;
43 import org.opensubsystems.core.util.Log;
44
45 /**
46  * Management layer for MS SQL Server database (www.microsoft.com/sqlserver)
47  *
48  * Currently we are using @@IDENTITY to select the last generated value from
49  * a table. This may not work if the table contains trigger as demonstrated
50  * by this article
51  * http://www.sqlteam.com/item.asp?ItemID=319
52  * If this becomes an issue for some particular scenario, we can use one of the
53  * alternatives available since SQL Server 2000, such as
54  *
55  * SELECT @@IDENTITY
56  * This is everyone's favorite function, unchanged from earlier versions of SQL
57  * Server. It returns the last IDENTITY value produced on a connection, regardless
58  * of the table that produced the value, and regardless of the scope of the
59  * statement that produced the value.
60  *
61  * SELECT IDENT_CURRENT('tablename')
62  * This new function returns the last IDENTITY value produced in a table,
63  * regardless of the connection that created the value, and regardless of the
64  * scope of the statement that produced the value.
65  *
66  * SELECT SCOPE_IDENTITY()
67  * This new function returns the last IDENTITY value produced on a connection
68  * and by a statement in the same scope, regardless of the table that produced
69  * the value.
70  *
71  * @version $Id: MSSQLDatabaseImpl.java,v 1.22 2007/01/07 06:14:54 bastafidli Exp $
72  * @author Julo Legeny
73  * @code.reviewer Miro Halas
74  * @code.reviewed 1.17 2006/04/05 05:03:08 bastafidli
75  */
76 public class MSSQLDatabaseImpl extends DatabaseImpl
77 {
78    // Cached values ////////////////////////////////////////////////////////////
79
80    /**
81     * Logger for this class
82     */
83    private static Logger JavaDoc s_logger = Log.getInstance(MSSQLDatabaseImpl.class);
84
85    // Constructors /////////////////////////////////////////////////////////////
86
87    /**
88     * Default constructor for empty database.
89     *
90     * @throws OSSException - problem connecting to database
91     */
92    public MSSQLDatabaseImpl(
93    ) throws OSSException
94    {
95       super();
96    }
97    
98    // Database administration //////////////////////////////////////////////////
99
100    /**
101     * {@inheritDoc}
102     */
103    protected void createUser(
104       Connection JavaDoc cntAdminDBConnection
105    ) throws OSSException
106    {
107       // Now create the user
108 PreparedStatement JavaDoc pstmQuery = null;
109       try
110       {
111          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
112          String JavaDoc strDatabaseURL = DatabaseConnectionFactoryImpl.getInstance().getDatabaseURL();
113          String JavaDoc strDatabaseName = "";
114          
115          // There can be used 2 various JDBC drivers: original MS driver and
116 // open source jTDS driver. URL is different for both drivers so we
117 // have to retrieve database name by 2 diffrent ways
118 if (strDatabaseURL.indexOf(":jtds:") > 0)
119          {
120             // There is used open source jTDS driver. Parse DB name.
121 strDatabaseName = strDatabaseURL.substring(
122                                  strDatabaseURL.lastIndexOf("/") + 1, strDatabaseURL.length());
123          }
124          else if (strDatabaseURL.indexOf(":microsoft:") > 0)
125          {
126             // There is used original MS driver. Parse DB name.
127 strDatabaseName = strDatabaseURL.substring(
128                                  strDatabaseURL.lastIndexOf("DatabaseName=") +
129                                  "DatabaseName=".length(), strDatabaseURL.indexOf(";",
130                                  strDatabaseURL.lastIndexOf("DatabaseName=")));
131          }
132          else if (strDatabaseURL.indexOf(":bea:") > 0)
133          {
134             // There is used internal BEA WebLogic driver. Parse DB name.
135 strDatabaseName = strDatabaseURL.substring(
136                                  strDatabaseURL.lastIndexOf("DatabaseName=") +
137                                  "DatabaseName=".length(), strDatabaseURL.length());
138          }
139          else
140          {
141             throw new OSSConfigException("Unsupported URL: " + strDatabaseURL);
142          }
143          
144          buffer.append("USE ");
145          buffer.append(strDatabaseName);
146          buffer.append("; EXEC sp_addlogin '");
147          buffer.append(DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser());
148          buffer.append("', '");
149          buffer.append(DatabaseConnectionFactoryImpl.getInstance().getDatabasePassword());
150          buffer.append("', '");
151          buffer.append(strDatabaseName);
152          buffer.append("', 'us_english'");
153          buffer.append("; EXEC sp_adduser '");
154          buffer.append(DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser());
155          buffer.append("' ; GRANT ALL TO ");
156          buffer.append(DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser());
157
158
159          pstmQuery = cntAdminDBConnection.prepareStatement(buffer.toString());
160
161          if (pstmQuery.execute())
162          {
163             // Close any results
164 pstmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
165          }
166          
167          // At this point we don't know if this is just a single operation
168 // and we need to commit or if it is a part of bigger transaction
169 // and the commit is not desired until all operations proceed.
170 // Therefore let the DatabaseTransactionFactory resolve it
171 DatabaseTransactionFactoryImpl.getInstance().commitTransaction(
172                                                  cntAdminDBConnection);
173          
174          s_logger.log(Level.FINER, "Database user "
175                              + DatabaseConnectionFactoryImpl.getInstance().getDatabaseUser()
176                              + " with password "
177                              + DatabaseConnectionFactoryImpl.getInstance().getDatabasePassword()
178                              + " created.");
179       }
180       catch (SQLException JavaDoc sqleExc)
181       {
182          try
183          {
184             // At this point we don't know if this is just a single operation
185 // and we need to commit or if it is a part of bigger transaction
186 // and the commit is not desired until all operations proceed.
187 // Therefore let the DatabaseTransactionFactory resolve it
188 DatabaseTransactionFactoryImpl.getInstance().rollbackTransaction(
189                cntAdminDBConnection);
190          }
191          catch (SQLException JavaDoc sqleExc2)
192          {
193             // Ignore this
194 s_logger.log(Level.WARNING,
195                                 "Failed to rollback changes for creation of user.",
196                                 sqleExc2);
197          }
198          s_logger.log(Level.SEVERE,
199                              "Unable to create default database user.",
200                              sqleExc);
201          throw new OSSDatabaseAccessException("Unable to create default database user.",
202                                               sqleExc);
203       }
204       finally
205       {
206          DatabaseUtils.closeStatement(pstmQuery);
207       }
208    }
209
210    /**
211     * {@inheritDoc}
212     */
213    public void startDatabaseServer() throws OSSException
214    {
215       // TODO: Feature: Implement starting database server
216 }
217
218    /**
219     * {@inheritDoc}
220     */
221    public void createDatabaseInstance() throws OSSException
222    {
223       // TODO: Feature: Implement creating database instance
224 }
225
226    /**
227     * {@inheritDoc}
228     */
229    public void stop(
230    ) throws OSSException
231    {
232       s_logger.entering(this.getClass().getName(), "stop");
233
234       // TODO: Feature: Implement this so we can safely stop the database when
235 // the application is finished.
236 super.stop();
237
238       s_logger.entering(this.getClass().getName(), "stop");
239    }
240
241    /**
242     * {@inheritDoc}
243     */
244    public int getDatabaseType()
245    {
246       return DatabaseImpl.MSSQL_DATABASE_TYPE;
247    }
248
249    /**
250     * {@inheritDoc}
251     */
252    public String JavaDoc getDatabaseTypeIdentifier()
253    {
254       return DatabaseImpl.MSSQL_DATABASE_TYPE_IDENTIFIER;
255    }
256
257    /**
258     * {@inheritDoc}
259     */
260    public String JavaDoc getCurrentTimestampFunctionCall()
261    {
262       return "CURRENT_TIMESTAMP";
263    }
264
265    /**
266     * {@inheritDoc}
267     */
268    public String JavaDoc getSQLCountFunctionCall()
269    {
270       // For MS SQL is best for performance use COUNT(*)
271 return "count(*)";
272    }
273
274    /**
275     * {@inheritDoc}
276     */
277    public boolean preferCountToLast(
278    )
279    {
280       // For MS SQL Server 2000 it is not faster to execute count(*) than to do
281 // last() therefore we disable this supporting for now
282 return false;
283    }
284
285    /**
286     * {@inheritDoc}
287     */
288    public boolean hasSelectListRangeSupport(
289    )
290    {
291       // MS SQL Server 2000 supports rows limitation by using clause TOP
292 // But based on our tests it has worse performance that not using it
293 // therefore we disable this supporting for now
294 return false;
295    }
296
297    /**
298     * {@inheritDoc}
299     */
300    public Object JavaDoc[] getSQLAnalyzeFunctionCall(
301       Map JavaDoc mapTableNames
302    )
303    {
304       // MSSQL Server uses UPDATE STATISTICS <table_name> WITH ALL to update
305 // indexes and increase performance.
306
307       String JavaDoc[] arrReturn = new String JavaDoc[mapTableNames.size()];
308       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
309       Iterator JavaDoc itItem;
310       int iIndex = 0;
311
312       itItem = mapTableNames.values().iterator();
313       while (itItem.hasNext())
314       {
315         // construct analyze query for each table from the array
316 buffer.append("update statistics ");
317         buffer.append((String JavaDoc)itItem.next());
318         buffer.append(" with all; ");
319         // add constructed query to the output array
320 arrReturn[iIndex++] = buffer.toString();
321         // delete buffer for next usage
322 buffer.delete(0, buffer.length());
323
324       }
325
326       return new Object JavaDoc[] {arrReturn, Boolean.TRUE};
327    }
328
329    /**
330     * {@inheritDoc}
331     */
332    public boolean isCallableStatement(
333       String JavaDoc strQuery
334    )
335    {
336       return strQuery.indexOf("EXEC ") != -1;
337    }
338
339    /**
340     * {@inheritDoc}
341     */
342    public void insertAndFetchGeneratedValues(
343       Connection JavaDoc dbConnection,
344       PreparedStatement JavaDoc insertStatement,
345       boolean bIsInDomain,
346       String JavaDoc strTableName,
347       int iIndex,
348       BasicDataObject data
349    ) throws SQLException JavaDoc,
350             OSSException
351    {
352       MSSQLDataUtils.insertAndFetchGeneratedValues((CallableStatement JavaDoc)insertStatement,
353                                                  iIndex, data);
354    }
355
356    /**
357     * {@inheritDoc}
358     */
359    public void updatedAndFetchGeneratedValues(
360       String JavaDoc strDataName,
361       Connection JavaDoc dbConnection,
362       PreparedStatement JavaDoc updateStatement,
363       boolean bIsInDomain,
364       String JavaDoc strTableName,
365       int iIndex,
366       ModifiableDataObject data
367    ) throws SQLException JavaDoc,
368             OSSException
369    {
370       MSSQLDataUtils.updateAndFetchGeneratedValues((CallableStatement JavaDoc)updateStatement,
371                                                    iIndex, data);
372    }
373 }
374
Popular Tags