KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > riotfamily > revolt > support > DatabaseUtils


1 /* ***** BEGIN LICENSE BLOCK *****
2  * Version: MPL 1.1
3  * The contents of this file are subject to the Mozilla Public License Version
4  * 1.1 (the "License"); you may not use this file except in compliance with
5  * the License. You may obtain a copy of the License at
6  * http://www.mozilla.org/MPL/
7  *
8  * Software distributed under the License is distributed on an "AS IS" basis,
9  * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
10  * for the specific language governing rights and limitations under the
11  * License.
12  *
13  * The Original Code is Riot.
14  *
15  * The Initial Developer of the Original Code is
16  * Neteye GmbH.
17  * Portions created by the Initial Developer are Copyright (C) 2006
18  * the Initial Developer. All Rights Reserved.
19  *
20  * Contributor(s):
21  * Felix Gnass [fgnass at neteye dot de]
22  *
23  * ***** END LICENSE BLOCK ***** */

24 package org.riotfamily.revolt.support;
25
26 import java.sql.Connection JavaDoc;
27 import java.sql.DatabaseMetaData JavaDoc;
28 import java.sql.ResultSet JavaDoc;
29 import java.sql.SQLException JavaDoc;
30 import java.util.ArrayList JavaDoc;
31 import java.util.Iterator JavaDoc;
32
33 import javax.sql.DataSource JavaDoc;
34
35 import org.riotfamily.revolt.DatabaseOutOfSyncException;
36 import org.riotfamily.revolt.definition.Column;
37 import org.riotfamily.revolt.definition.Database;
38 import org.riotfamily.revolt.definition.Identifier;
39 import org.riotfamily.revolt.definition.Table;
40 import org.springframework.dao.DataAccessException;
41 import org.springframework.jdbc.core.ConnectionCallback;
42 import org.springframework.jdbc.core.JdbcTemplate;
43 import org.springframework.util.StringUtils;
44
45 /**
46  * Utility class that retieves database meta data via JDBC.
47  * @author Felix Gnass [fgnass at neteye dot de]
48  */

49 public class DatabaseUtils {
50
51     /**
52      * Returns the JDBC-URL for the given DataSource.
53      */

54     public static String JavaDoc getUrl(DataSource JavaDoc dataSource) {
55         JdbcTemplate template = new JdbcTemplate(dataSource);
56         return (String JavaDoc) template.execute(new ConnectionCallback() {
57             public Object JavaDoc doInConnection(Connection JavaDoc connection)
58                     throws SQLException JavaDoc, DataAccessException {
59
60                 DatabaseMetaData JavaDoc metaData = connection.getMetaData();
61                 return metaData.getURL();
62             }
63         });
64     }
65
66     /**
67      * Returns whether the DataSource contains any tables.
68      */

69     public static boolean anyTablesExist(DataSource JavaDoc dataSource) {
70         JdbcTemplate template = new JdbcTemplate(dataSource);
71         Boolean JavaDoc result = (Boolean JavaDoc) template.execute(new ConnectionCallback() {
72             public Object JavaDoc doInConnection(Connection JavaDoc connection)
73                     throws SQLException JavaDoc, DataAccessException {
74
75                 DatabaseMetaData JavaDoc metaData = connection.getMetaData();
76                 ResultSet JavaDoc rs = metaData.getTables(null, null, "_", null);
77                 return Boolean.valueOf(rs.next());
78             }
79         });
80         return result.booleanValue();
81     }
82
83     /**
84      * Returns whether the specified table exists.
85      */

86     public static boolean tableExists(DataSource JavaDoc dataSource,
87             final Identifier table) {
88
89         JdbcTemplate template = new JdbcTemplate(dataSource);
90         Boolean JavaDoc result = (Boolean JavaDoc) template.execute(new ConnectionCallback() {
91             public Object JavaDoc doInConnection(Connection JavaDoc connection)
92                     throws SQLException JavaDoc, DataAccessException {
93
94                 DatabaseMetaData JavaDoc metaData = connection.getMetaData();
95                 String JavaDoc pattern = getSearchPattern(metaData, table);
96                 ResultSet JavaDoc rs = metaData.getTables(null, null, pattern, null);
97                 return Boolean.valueOf(rs.next());
98             }
99         });
100         return result.booleanValue();
101     }
102
103     /**
104      * Checks whether the database schema matches the given model.
105      */

106     public static void validate(DataSource JavaDoc dataSource, final Database model)
107             throws DatabaseOutOfSyncException {
108
109         JdbcTemplate template = new JdbcTemplate(dataSource);
110         template.execute(new ConnectionCallback() {
111             public Object JavaDoc doInConnection(Connection JavaDoc connection)
112                     throws SQLException JavaDoc, DataAccessException {
113
114                 DatabaseMetaData JavaDoc metaData = connection.getMetaData();
115                 validate(metaData, model);
116                 return null;
117             }
118         });
119     }
120
121     private static String JavaDoc getSearchPattern(DatabaseMetaData JavaDoc metaData,
122             Identifier identifier) throws SQLException JavaDoc {
123
124         String JavaDoc escape = metaData.getSearchStringEscape();
125         String JavaDoc pattern = identifier.getName().replaceAll("_", escape + "_");
126
127         if (identifier.isQuoted()) {
128             if (metaData.storesUpperCaseQuotedIdentifiers()) {
129                 pattern = pattern.toUpperCase();
130             }
131             else if (metaData.storesLowerCaseQuotedIdentifiers()) {
132                 pattern = pattern.toLowerCase();
133             }
134         }
135         else {
136             if (metaData.storesUpperCaseIdentifiers()) {
137                 pattern = pattern.toUpperCase();
138             }
139             else if (metaData.storesLowerCaseIdentifiers()) {
140                 pattern = pattern.toLowerCase();
141             }
142         }
143         return pattern;
144     }
145
146     private static void validate(DatabaseMetaData JavaDoc metaData, Database model)
147             throws SQLException JavaDoc, DatabaseOutOfSyncException {
148
149         Iterator JavaDoc it = model.getTables().iterator();
150         while (it.hasNext()) {
151             Table table = (Table) it.next();
152             String JavaDoc pattern = getSearchPattern(metaData, table);
153             ResultSet JavaDoc rs = metaData.getColumns(null, null, pattern, "%");
154             ArrayList JavaDoc columns = new ArrayList JavaDoc();
155             while (rs.next()) {
156                 columns.add(new Column(rs.getString("COLUMN_NAME")));
157             }
158             if (columns.isEmpty()) {
159                 throw new DatabaseOutOfSyncException("Table "
160                         + table.getName() + " does not exist");
161             }
162             if (!columns.containsAll(table.getColumns())) {
163                 ArrayList JavaDoc missing = new ArrayList JavaDoc(table.getColumns());
164                 missing.removeAll(columns);
165                 throw new DatabaseOutOfSyncException("Table "+ table.getName()
166                         + " does not have the following column(s): "
167                         + StringUtils.collectionToCommaDelimitedString(missing));
168             }
169             if (!table.getColumns().containsAll(columns)) {
170                 ArrayList JavaDoc redundant = new ArrayList JavaDoc(columns);
171                 redundant.removeAll(table.getColumns());
172                 throw new DatabaseOutOfSyncException("Table "+ table.getName()
173                         + " has additionally the following unspecified column(s): "
174                         + StringUtils.collectionToCommaDelimitedString(redundant));
175             }
176         }
177     }
178 }
179
Popular Tags