|
1
28 package net.sf.jguard.ext.database;
29
30 import java.sql.Connection  ;
31 import java.sql.DatabaseMetaData ;
32 import java.sql.PreparedStatement ;
33 import java.sql.ResultSet ;
34 import java.sql.SQLException ;
35 import java.util.Arrays ;
36 import java.util.Iterator ;
37 import java.util.List ;
38 import java.util.Properties ;
39 import java.util.logging.Logger ;
40
41
47 public class DatabaseUtils {
48 private static final Logger logger = Logger.getLogger(DatabaseUtils.class.getName());
49
50
64 public static void createRequiredDatabaseEntities(Properties props, ConnectionFactory connFactory, List sequencesNames,
65 List tablesNames, List foreignkeysNames) {
66
67 createEntities(props, connFactory, sequencesNames, "SEQUENCE_PRESENCE");
69
70 createEntities(props, connFactory, tablesNames, "TABLE_PRESENCE");
72
73 createForeignKeys(props, connFactory, foreignkeysNames);
75
76 }
77
78 private static String handleSensitiveCase(String original, DatabaseMetaData dbMetaData) throws SQLException {
79 boolean storesLowerCaseIdentifiers = dbMetaData.storesLowerCaseIdentifiers();
80 logger.finest(" database store lower case identifiers =" + storesLowerCaseIdentifiers);
81 boolean storesUpperCaseIdentifiers = dbMetaData.storesUpperCaseIdentifiers();
82 logger.finest(" database store upper case identifiers =" + storesUpperCaseIdentifiers);
83 if (storesLowerCaseIdentifiers) {
84 original = original.toLowerCase();
85 } else if (storesUpperCaseIdentifiers) {
86 original = original.toUpperCase();
87 }
88
89 return original;
90
91 }
92
93 private static void createForeignKeys(Properties props, ConnectionFactory connectionFactory, List foreignkeysNames) {
94 Connection conn = null;
95 try {
96 conn = connectionFactory.getConnection();
97 DatabaseMetaData dbMetaData = conn.getMetaData();
98
99 for (int i = 0; i < foreignkeysNames.size(); i++) {
100 String foreignKeyName = (String ) foreignkeysNames.get(i);
101 String query = ((String ) props.get(foreignKeyName));
102 if (query != null) {
105 try {
106 List words = Arrays.asList(query.split(" "));
112
118 String fkTableName = (String ) words.get(2);
119 String constraintName = (String ) words.get(5);
120 String fKeyColumnName = (String ) words.get(8); fKeyColumnName = fKeyColumnName.replace('(', ' ');
122 fKeyColumnName = fKeyColumnName.replace(')', ' ').trim();
123 String pKeyTableName = (String ) words.get(10);
124 int pKeyIndex = pKeyTableName.indexOf("(");
125 if (pKeyIndex>0){
128 pKeyTableName = pKeyTableName.substring(0,pKeyIndex);
129 }
130
131 ResultSet rs = dbMetaData.getImportedKeys(null, null, handleSensitiveCase(fkTableName, dbMetaData));
132 boolean foreignKeyFound = false;
133 while (rs.next()) {
134 logger.finest("importedKeys for=" + fkTableName);
135 String pkeyTableName2 = rs.getString("PKTABLE_NAME");
136 logger.finest("PKTABLE_NAME=" + pkeyTableName2);
137 String pkeyColumnName2 = rs.getString("PKCOLUMN_NAME");
138 logger.finest("PKCOLUMN_NAME=" + pkeyColumnName2);
139 String fkeyTableName2 = rs.getString("FKTABLE_NAME");
140 logger.finest("FKTABLE_NAME=" + fkeyTableName2);
141 String fkeyColumnName2 = rs.getString("FKCOLUMN_NAME");
142 logger.finest("FKCOLUMN_NAME=" + fkeyColumnName2);
143 String constraintName2 = rs.getString("FK_NAME");
144 logger.finest("FK_NAME=" + constraintName2);
145 String pkeyName2 = rs.getString("PK_NAME");
146 logger.finest("PK_NAME=" + pkeyName2);
147 if (fkTableName.equalsIgnoreCase(fkeyTableName2) && fKeyColumnName.equalsIgnoreCase(fkeyColumnName2)
148 && pKeyTableName.equalsIgnoreCase(pkeyTableName2)
149 && constraintName.equalsIgnoreCase(constraintName2)) {
150 foreignKeyFound = true;
152 break;
153 }
154
155 }
156
157 if (foreignKeyFound) {
158 continue;
159 }
160
162 logger.finest("entity=" + foreignKeyName + " query=" + query);
163 PreparedStatement ps2 = conn.prepareStatement(query);
164 ps2.execute();
165 } catch (SQLException sqle) {
166 logger.severe("entity=" + foreignKeyName + " cannot be created . " + sqle.getMessage());
167 }
168
169 } else {
170 logger.info(foreignKeyName + " entry is not present in the properties file ");
171 }
172
173 }
174 } catch (SQLException e) {
175 logger.severe(" database metadata cannot be grabbed from the SQL connection ");
176 } finally {
177 try {
178 conn.close();
179 } catch (SQLException e) {
180 logger.severe(" connexion cannot be closed " + e.getMessage());
181 }
182 }
183
184 }
185
186
194 private static void createEntities(Properties props, ConnectionFactory connectionFactory, List entitiesNames,
195 String presenceQueryKey) {
196 Connection conn = null;
197 String presenceQuery = props.getProperty(presenceQueryKey);
198 try {
199 conn = connectionFactory.getConnection();
200 DatabaseMetaData dbMetaData = conn.getMetaData();
201 for (int i = 0; i < entitiesNames.size(); i++) {
202 String entityName = (String ) entitiesNames.get(i);
203
204 if ((String ) props.get(entityName) != null) {
207 try {
208 String replacedPresenceQuery = presenceQuery.replaceFirst("\\?", handleSensitiveCase(entityName,
210 dbMetaData));
211 PreparedStatement ps1 = conn.prepareStatement(replacedPresenceQuery);
212 logger.finest(replacedPresenceQuery);
213 ResultSet rs1 = ps1.executeQuery();
214 if (rs1.next()) {
215 logger.finest(" entity " + entityName + " has been detected : " + rs1.getObject(1));
217 continue;
218 }
219 } catch (SQLException e) {
220 logger.finest(" entity " + entityName + " does not exists and will be created ");
221 }
222 try {
223 String query2 = (String ) props.get(entityName);
225 logger.finest("entity=" + entityName + " query2=" + query2);
226 PreparedStatement ps2 = conn.prepareStatement(handleSensitiveCase(query2, dbMetaData));
227 ps2.execute();
228 } catch (SQLException sqle) {
229 logger.severe("entity=" + entityName + " cannot be created . " + sqle.getMessage());
230 throw new RuntimeException (sqle);
231 }
232
233 } else {
234 logger.info(entityName + " entry is not present in the properties file ");
235 }
236
237 }
238 } catch (SQLException e) {
239 logger.severe(e.getMessage());
240 } finally {
241 try {
242 conn.close();
243 } catch (SQLException e) {
244 logger.severe(" connexion cannot be closed " + e.getMessage());
245 }
246 }
247 }
248
249
261 public static boolean isEmpty(Properties props, ConnectionFactory connectionFactory, List selectQueries) {
262 if (props == null) {
263 throw new IllegalArgumentException (" properties is null ");
264 }
265 if (connectionFactory == null) {
266 throw new IllegalArgumentException (" connectionFactory is null ");
267 }
268 boolean empty = true;
269 Connection conn = null;
270 try {
271 conn = connectionFactory.getConnection();
272 PreparedStatement pst = null;
273 ResultSet rs = null;
274 Iterator it = selectQueries.iterator();
275 while (it.hasNext()) {
276 try {
277 String key = (String ) it.next();
278 String query = (String ) props.getProperty(key);
279 pst = conn.prepareStatement(query);
280 rs = pst.executeQuery();
281 if (rs.next() != false) {
282 logger.info(" there are some principals in database ");
283 empty = false;
284 break;
285 }
286
287 } catch (SQLException e) {
288 logger.finest(e.getMessage());
289 }
290 }
291 } finally {
292 try {
293 conn.close();
294 } catch (SQLException e) {
295 }
296 }
297 return empty;
298 }
299
300 }
301 |
Popular Tags |
Java API By Example, From Geeks To Geeks.