1 package net.sf.persism;
2
3 import net.sf.persism.annotations.*;
4
5 import java.lang.annotation.Annotation;
6 import java.lang.reflect.Field;
7 import java.lang.reflect.Method;
8 import java.lang.reflect.Modifier;
9 import java.sql.*;
10 import java.text.MessageFormat;
11 import java.util.*;
12 import java.util.concurrent.ConcurrentHashMap;
13
14 import static net.sf.persism.Util.*;
15
16 /**
17 * DB and POJO related Metadata collected based connection url
18 *
19 * @author Dan Howard
20 * @since 3/31/12 4:19 PM
21 */
22 final class MetaData {
23
24 private static final Log log = Log.getLogger(MetaData.class);
25
26 // properties for each class - static because this won't need to change between MetaData instances (collection is unmodifiable)
27 private static final Map<Class<?>, Collection<PropertyInfo>> propertyMap = new ConcurrentHashMap<>(32);
28
29 // column to property map for each class
30 private final Map<Class<?>, Map<String, PropertyInfo>> propertyInfoMap = new ConcurrentHashMap<>(32);
31 private final Map<Class<?>, Map<String, ColumnInfo>> columnInfoMap = new ConcurrentHashMap<>(32);
32
33 // SQL for updates/inserts/deletes/selects for each class
34 private final Map<Class<?>, String> updateStatementsMap = new ConcurrentHashMap<>(32);
35 private final Map<Class<?>, String> deleteStatementsMap = new ConcurrentHashMap<>(32);
36 private final Map<Class<?>, String> selectStatementsMap = new ConcurrentHashMap<>(32);
37
38 // key - class, value - map key: columns to include, value: associated INSERT statement - this handles defaults on columns which may not need to be specified
39 private final Map<Class<?>, Map<String, String>> insertStatements = new ConcurrentHashMap<>(32);
40
41 // key - class, value - map key: changed columns, value: associated UPDATE statement
42 private final Map<Class<?>, Map<String, String>> variableUpdateStatements = new ConcurrentHashMap<>(32);
43
44 // Where clauses for primary key queries for tables
45 private final Map<Class<?>, String> primaryWhereClauseMap = new ConcurrentHashMap<>(32);
46
47 // Where ID IN (?, ?, ?) kinds of queries when no SQL is used.
48 private final Map<Class<?>, Map<Integer, String>> primaryInClauseMap = new ConcurrentHashMap<>(32);
49
50 // SQL parsed from SQL.where() - key is WHERE, value is parsed where clause
51 Map<Class<?>, Map<String, String>> whereClauses = new ConcurrentHashMap<>(32);
52
53 // WHERE clauses defined by JOIN operations (maintained by SessionHelper)
54 Map<JoinInfo, Map<String, String>> childWhereClauses = new ConcurrentHashMap<>(32);
55
56 // table/view for each class
57 private final Map<Class<?>, TableInfo> tableOrViewMap = new ConcurrentHashMap<>(32);
58
59 // list of tables in the DB
60 private final Set<TableInfo> tables = new HashSet<>();
61
62 // list of views in the DB
63 private final Set<TableInfo> views = new HashSet<>();
64
65 static final Map<String, MetaData> metaData = new ConcurrentHashMap<>(4);
66
67 private final ConnectionType connectionType;
68
69 // the "extra" characters that can be used in unquoted identifier names (those beyond a-z, A-Z, 0-9 and _)
70 // Was using DatabaseMetaData getExtraNameCharacters() but some drivers don't provide these and still allow
71 // for non-alphanumeric characters in column names. We'll just use a static set.
72 private static final String EXTRA_NAME_CHARACTERS = "`~!@#$%^&*()-+=/|\\{}[]:;'\".,<>*";
73 private static final String SELECT_FOR_COLUMNS = "SELECT * FROM {0}{1}{2} WHERE 1=0";
74 private static final String SELECT_FOR_COLUMNS_WITH_SCHEMA = "SELECT * FROM {0}{1}{2}.{3}{4}{5} WHERE 1=0";
75
76 private MetaData(Connection con, String sessionKey) throws SQLException {
77
78 log.debug("MetaData CREATING instance [%s] ", sessionKey);
79
80 connectionType = ConnectionType.get(sessionKey);
81 if (connectionType == ConnectionType.Other) {
82 log.warn(Message.UnknownConnectionType.message(con.getMetaData().getDatabaseProductName()));
83 }
84 populateTableList(con);
85 }
86
87 static synchronized MetaData getInstance(Connection con, String sessionKey) throws SQLException {
88
89 if (sessionKey == null) {
90 sessionKey = con.getMetaData().getURL();
91 }
92
93 if (metaData.get(sessionKey) == null) {
94 metaData.put(sessionKey, new MetaData(con, sessionKey));
95 }
96 log.debug("MetaData getting instance %s", sessionKey);
97 return metaData.get(sessionKey);
98 }
99
100 // Should only be called IF the map does not contain the column meta information yet.
101 // Version for Tables
102 private synchronized <T> Map<String, PropertyInfo> determinePropertyInfo(Class<T> objectClass, TableInfo table, Connection connection) {
103 // double check map
104 if (propertyInfoMap.containsKey(objectClass)) {
105 return propertyInfoMap.get(objectClass);
106 }
107
108 // Not for @NotTable classes
109 assert objectClass.getAnnotation(NotTable.class) == null;
110
111 String sd = connectionType.getKeywordStartDelimiter();
112 String ed = connectionType.getKeywordEndDelimiter();
113
114 ResultSet rs = null;
115 Statement st = null;
116 try {
117 st = connection.createStatement();
118 // gives us real column names with case.
119 String sql;
120 if (isEmpty(table.schema())) {
121 sql = MessageFormat.format(SELECT_FOR_COLUMNS, sd, table.name(), ed);
122 } else {
123 sql = MessageFormat.format(SELECT_FOR_COLUMNS_WITH_SCHEMA, sd, table.schema(), ed, sd, table.name(), ed);
124 }
125 if (log.isDebugEnabled()) {
126 log.debug("determineColumns: %s", sql);
127 }
128 rs = st.executeQuery(sql);
129 Map<String, PropertyInfo> columns = determinePropertyInfoFromResultSet(objectClass, rs);
130 propertyInfoMap.put(objectClass, columns);
131 return columns;
132 } catch (SQLException e) {
133 throw new PersismException(e.getMessage(), e);
134 } finally {
135 cleanup(st, rs);
136 }
137 }
138
139 private <T> Map<String, PropertyInfo> determinePropertyInfoFromResultSet(Class<T> objectClass, ResultSet rs) throws SQLException {
140 ResultSetMetaData rsmd = rs.getMetaData();
141 Collection<PropertyInfo> properties = getPropertyInfo(objectClass);
142
143 int columnCount = rsmd.getColumnCount();
144
145 Map<String, PropertyInfo> columns = new LinkedHashMap<>(columnCount);
146 for (int j = 1; j <= columnCount; j++) {
147 String realColumnName = rsmd.getColumnLabel(j);
148 String columnName = realColumnName.toLowerCase().replace("_", "").replace(" ", "");
149 // also replace these characters
150 for (int x = 0; x < EXTRA_NAME_CHARACTERS.length(); x++) {
151 columnName = columnName.replace("" + EXTRA_NAME_CHARACTERS.charAt(x), "");
152 }
153 PropertyInfo foundProperty = null;
154 for (PropertyInfo propertyInfo : properties) {
155 String checkName = propertyInfo.propertyName.toLowerCase().replace("_", "");
156 if (checkName.equalsIgnoreCase(columnName)) {
157 foundProperty = propertyInfo;
158 break;
159 } else {
160 // check annotation against column name
161 Column column = (Column) propertyInfo.getAnnotation(Column.class);
162 if (column != null) {
163 if (column.name().equalsIgnoreCase(realColumnName)) {
164 foundProperty = propertyInfo;
165 break;
166 }
167 }
168 }
169 }
170
171 if (foundProperty != null) {
172 columns.put(realColumnName, foundProperty);
173 } else {
174 log.warn(Message.NoPropertyFoundForColumn.message(realColumnName, objectClass));
175 }
176 }
177 return columns;
178 }
179
180 @SuppressWarnings({"JDBCExecuteWithNonConstantString", "SqlDialectInspection"})
181 private synchronized <T> Map<String, ColumnInfo> determineColumnInfo(Class<T> objectClass, TableInfo table, Connection connection) {
182 if (columnInfoMap.containsKey(objectClass)) {
183 return columnInfoMap.get(objectClass);
184 }
185
186 Statement st = null;
187 ResultSet rs = null;
188 Map<String, PropertyInfo> properties = getTableColumnsPropertyInfo(objectClass, connection);
189 String sd = connectionType.getKeywordStartDelimiter();
190 String ed = connectionType.getKeywordEndDelimiter();
191
192 String schemaName = table.schema();
193 String tableName = table.name();
194
195 try {
196 st = connection.createStatement();
197 String sql;
198 if (isEmpty(schemaName)) {
199 sql = MessageFormat.format(SELECT_FOR_COLUMNS, sd, tableName, ed);
200 } else {
201 sql = MessageFormat.format(SELECT_FOR_COLUMNS_WITH_SCHEMA, sd, schemaName, ed, sd, tableName, ed);
202 }
203 log.debug("determineColumnInfo %s", sql);
204 rs = st.executeQuery(sql);
205
206 // Make sure primary keys sorted by column order in case we have more than 1
207 // then we'll know the order to apply the parameters.
208 Map<String, ColumnInfo> map = new LinkedHashMap<>(32);
209
210 boolean primaryKeysFound = false;
211
212 // Grab all columns and make first pass to detect primary auto-inc
213 ResultSetMetaData rsMetaData = rs.getMetaData();
214 for (int i = 1; i <= rsMetaData.getColumnCount(); i++) {
215 // only include columns where we have a property
216 if (properties.containsKey(rsMetaData.getColumnLabel(i))) {
217 ColumnInfo columnInfo = new ColumnInfo();
218 columnInfo.columnName = rsMetaData.getColumnLabel(i);
219 columnInfo.autoIncrement = rsMetaData.isAutoIncrement(i);
220 columnInfo.primary = columnInfo.autoIncrement;
221 columnInfo.sqlColumnType = rsMetaData.getColumnType(i);
222 columnInfo.sqlColumnTypeName = rsMetaData.getColumnTypeName(i);
223 columnInfo.columnType = JavaType.convert(columnInfo.sqlColumnType);
224 columnInfo.length = rsMetaData.getColumnDisplaySize(i);
225
226 if (!primaryKeysFound) {
227 primaryKeysFound = columnInfo.primary;
228 }
229
230 PropertyInfo propertyInfo = properties.get(rsMetaData.getColumnLabel(i));
231 Annotation annotation = propertyInfo.getAnnotation(Column.class);
232
233 if (annotation != null) {
234 Column col = (Column) annotation;
235 if (col.hasDefault()) {
236 columnInfo.hasDefault = true;
237 }
238
239 if (col.primary()) {
240 columnInfo.primary = true;
241 if (!isDataClassATable(objectClass)) {
242 log.warn(Message.PrimaryAnnotationOnViewOrQueryMakesNoSense.message(objectClass, propertyInfo.propertyName));
243 }
244 }
245
246 if (col.autoIncrement()) {
247 columnInfo.autoIncrement = true;
248 if (!columnInfo.columnType.isEligibleForAutoinc()) {
249 // This will probably cause some error or other problem. Notify the user.
250 log.warn(Message.ColumnAnnotatedAsAutoIncButNAN.message(columnInfo.columnName, columnInfo.columnType));
251 }
252 }
253
254 columnInfo.readOnly = col.readOnly();
255
256 if (!primaryKeysFound) {
257 primaryKeysFound = columnInfo.primary;
258 }
259 }
260
261 map.put(columnInfo.columnName, columnInfo);
262 }
263 }
264 rs.close();
265
266 DatabaseMetaData dmd = connection.getMetaData();
267
268 if (objectClass.getAnnotation(View.class) == null) {
269
270 if (isEmpty(schemaName)) {
271 rs = dmd.getPrimaryKeys(null, connectionType.getSchemaPattern(), tableName);
272 } else {
273 rs = dmd.getPrimaryKeys(null, schemaName, tableName);
274 }
275 int primaryKeysCount = 0;
276 while (rs.next()) {
277 ColumnInfo columnInfo = map.get(rs.getString("COLUMN_NAME"));
278 if (columnInfo != null) {
279 columnInfo.primary = true;
280
281 if (!primaryKeysFound) {
282 primaryKeysFound = columnInfo.primary;
283 }
284 }
285 primaryKeysCount++;
286 }
287
288 if (primaryKeysCount == 0 && !primaryKeysFound) {
289 log.warn(Message.DatabaseMetaDataCouldNotFindPrimaryKeys.message(table));
290 }
291 }
292
293 /*
294 Get columns from database metadata since we don't get Type from resultSetMetaData
295 with SQLite. + We also need to know if there's a default on a column.
296 */
297 if (isEmpty(schemaName)) {
298 rs = dmd.getColumns(null, connectionType.getSchemaPattern(), tableName, null);
299 } else {
300 rs = dmd.getColumns(null, schemaName, tableName, null);
301 }
302 int columnsCount = 0;
303 while (rs.next()) {
304 ColumnInfo columnInfo = map.get(rs.getString("COLUMN_NAME"));
305 if (columnInfo != null) {
306 if (!columnInfo.hasDefault) {
307 columnInfo.hasDefault = containsColumn(rs, "COLUMN_DEF") && rs.getString("COLUMN_DEF") != null;
308 }
309
310 // Do we not have autoinc info here? Yes.
311 // IS_AUTOINCREMENT = NO or YES
312 if (!columnInfo.autoIncrement) {
313 columnInfo.autoIncrement = containsColumn(rs, "IS_AUTOINCREMENT") && "YES".equalsIgnoreCase(rs.getString("IS_AUTOINCREMENT"));
314 }
315
316 // Re-assert the type since older version of SQLite could not detect types with empty resultsets
317 // It seems OK now in the newer JDBC driver.
318 // See testTypes unit test in TestSQLite
319 if (containsColumn(rs, "DATA_TYPE")) {
320 columnInfo.sqlColumnType = rs.getInt("DATA_TYPE");
321 if (containsColumn(rs, "TYPE_NAME")) {
322 columnInfo.sqlColumnTypeName = rs.getString("TYPE_NAME");
323 }
324 columnInfo.columnType = JavaType.convert(columnInfo.sqlColumnType);
325 }
326 }
327 columnsCount++;
328 }
329 rs.close();
330
331 if (columnsCount == 0) {
332 // Shouldn't this be a fail? It would mean the user connecting to the DB
333 // has no permission to get the column meta-data
334 // It's a warning because it is possible to specify the column information
335 // with annotations rather than having Persism discover it.
336 log.warn(Message.DatabaseMetaDataCouldNotFindColumns.message(table));
337 }
338
339 // FOR Oracle which doesn't set autoinc in metadata even if we have:
340 // "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
341 // Apparently that's not enough for the Oracle JDBC driver to indicate this is autoinc.
342 // If we have a primary that's NUMERIC and HAS a default AND autoinc is not set then set it.
343 if (connectionType == ConnectionType.Oracle) {
344 Optional<ColumnInfo> autoInc = map.values().stream().filter(e -> e.autoIncrement).findFirst();
345 if (autoInc.isEmpty()) {
346 // Do a second check if we have a primary that's numeric with a default.
347 Optional<ColumnInfo> primaryOpt = map.values().stream().filter(e -> e.primary).findFirst();
348 if (primaryOpt.isPresent()) {
349 ColumnInfo primary = primaryOpt.get();
350 if (primary.columnType.isEligibleForAutoinc() && primary.hasDefault) {
351 primary.autoIncrement = true;
352 primaryKeysFound = true;
353 }
354 }
355 }
356 }
357
358 if (!primaryKeysFound && isDataClassATable(objectClass)) {
359 // Should we fail-fast? Actually no, we should not fail here.
360 // It's very possible the user has a table that they will never
361 // update, delete or select (by primary).
362 // They may only want to do read operations with specified queries and in that
363 // context we don't need any primary keys. (same with insert)
364 log.warn(Message.NoPrimaryKeyFoundForTable.message(table));
365 }
366
367 columnInfoMap.put(objectClass, map);
368 return map;
369
370 } catch (SQLException e) {
371 throw new PersismException(e.getMessage(), e);
372 } finally {
373 cleanup(st, rs);
374 }
375 }
376
377 private static boolean isDataClassATable(Class<?> objectClass) {
378 return objectClass.getAnnotation(Table.class) != null || (objectClass.getAnnotation(View.class) == null && objectClass.getAnnotation(NotTable.class) == null);
379 }
380
381 static <T> Collection<PropertyInfo> getPropertyInfo(Class<T> objectClass) {
382 if (propertyMap.containsKey(objectClass)) {
383 return propertyMap.get(objectClass);
384 }
385 return determinePropertyInfo(objectClass);
386 }
387
388 private static synchronized <T> Collection<PropertyInfo> determinePropertyInfo(Class<T> objectClass) {
389 if (propertyMap.containsKey(objectClass)) {
390 return propertyMap.get(objectClass);
391 }
392
393 Map<String, PropertyInfo> propertyInfos = new HashMap<>(32);
394
395 List<Field> fields = new ArrayList<>(32);
396
397 // getDeclaredFields does not get fields from super classes.....
398 fields.addAll(Arrays.asList(objectClass.getDeclaredFields()));
399 Class<?> sup = objectClass.getSuperclass();
400 log.debug("fields for %s", sup);
401 while (!sup.equals(Object.class) && !sup.equals(PersistableObject.class)) {
402 fields.addAll(Arrays.asList(sup.getDeclaredFields()));
403 sup = sup.getSuperclass();
404 log.debug("fields for %s", sup);
405 }
406
407 Method[] methods = objectClass.getMethods();
408
409 for (Field field : fields) {
410 // Skip static fields
411 if (Modifier.isStatic(field.getModifiers())) {
412 continue;
413 }
414 // log.debug("Field Name: %s", field.getName());
415 String propertyName = field.getName();
416 // log.debug("Property Name: *%s* ", propertyName);
417
418 PropertyInfo propertyInfo = new PropertyInfo();
419 propertyInfo.propertyName = propertyName;
420 propertyInfo.field = field;
421 Annotation[] annotations = field.getAnnotations();
422 for (Annotation annotation : annotations) {
423 propertyInfo.annotations.put(annotation.annotationType(), annotation);
424 }
425
426 for (Method method : methods) {
427 String propertyNameToTest = field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
428 // log.debug("property name for testing %s", propertyNameToTest);
429 if (propertyNameToTest.startsWith("Is") && propertyNameToTest.length() > 2 && Character.isUpperCase(propertyNameToTest.charAt(2))) {
430 propertyNameToTest = propertyName.substring(2);
431 }
432
433 String[] candidates = {"set" + propertyNameToTest, "get" + propertyNameToTest, "is" + propertyNameToTest, field.getName()};
434
435 if (Arrays.asList(candidates).contains(method.getName())) {
436 //log.debug(" METHOD: %s", method.getName());
437
438 annotations = method.getAnnotations();
439 for (Annotation annotation : annotations) {
440 propertyInfo.annotations.put(annotation.annotationType(), annotation);
441 }
442
443 // OR added to fix to builder pattern style when your setters are just the field name
444 if (method.getName().equalsIgnoreCase("set" + propertyNameToTest) || method.getParameterCount() > 0) {
445 propertyInfo.setter = method;
446 } else {
447 propertyInfo.getter = method;
448 }
449 }
450 }
451
452 propertyInfo.isJoin = propertyInfo.getAnnotation(Join.class) != null;
453 propertyInfos.put(propertyName.toLowerCase(), propertyInfo);
454 }
455
456 // Remove any properties found with the NotColumn annotation
457 // http://stackoverflow.com/questions/2026104/hashmap-keyset-foreach-and-remove
458 Iterator<Map.Entry<String, PropertyInfo>> it = propertyInfos.entrySet().iterator();
459 while (it.hasNext()) {
460 Map.Entry<String, PropertyInfo> entry = it.next();
461 PropertyInfo info = entry.getValue();
462 // added support for transient
463 if (info.getAnnotation(NotColumn.class) != null || Modifier.isTransient(info.field.getModifiers())) {
464 it.remove();
465 }
466 }
467
468 Collection<PropertyInfo> properties = Collections.unmodifiableCollection(propertyInfos.values());
469 propertyMap.put(objectClass, properties);
470
471 // If a view or query - warn if we find any setters
472 if (objectClass.getAnnotation(NotTable.class) != null || objectClass.getAnnotation(View.class) != null) {
473 List<String> setters = new ArrayList<>();
474 for (PropertyInfo propertyInfo : properties) {
475 if (propertyInfo.setter != null) {
476 setters.add(propertyInfo.propertyName);
477 }
478 }
479
480 if (setters.size() > 0) {
481 log.warn(Message.SettersFoundInReadOnlyObject.message(objectClass, setters));
482 }
483 }
484
485 return properties;
486 }
487
488 private static final String TABLE = "TABLE";
489 private static final String VIEW = "VIEW";
490 private static final String[] tableTypes = {TABLE, VIEW};
491
492 // Populates the tables list with table names from the DB.
493 // This list is used for discovery of the table name from a class.
494 // ONLY to be called from Init in a synchronized way.
495 // NULL POINTER WITH
496 // http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/5c74094a-8506-4278-ac1c-f07d1bfdb266
497 // solution:
498 // http://stackoverflow.com/questions/8988945/java7-sqljdbc4-sql-error-08s01-on-getconnection
499 private void populateTableList(Connection con) throws PersismException {
500 try (ResultSet rs = con.getMetaData().getTables(null, connectionType.getSchemaPattern(), null, tableTypes)) {
501 String name;
502 while (rs.next()) {
503 name = rs.getString("TABLE_NAME");
504 if (VIEW.equalsIgnoreCase(rs.getString("TABLE_TYPE"))) {
505 views.add(new TableInfo(name, rs.getString("TABLE_SCHEM"), connectionType));
506 } else {
507 tables.add(new TableInfo(name, rs.getString("TABLE_SCHEM"), connectionType));
508 }
509 }
510 } catch (SQLException e) {
511 throw new PersismException(e.getMessage(), e);
512 }
513 }
514
515 /**
516 * @param object
517 * @param connection
518 * @return sql update string
519 * @throws NoChangesDetectedForUpdateException if the data object implements Persistable and there are no changes detected
520 */
521 String getUpdateStatement(Object object, Connection connection) throws PersismException, NoChangesDetectedForUpdateException {
522
523 String sql;
524 if (object instanceof Persistable<?> pojo) {
525 Map<String, PropertyInfo> changes = getChangedProperties(pojo, connection);
526 if (changes.size() == 0) {
527 throw new NoChangesDetectedForUpdateException();
528 }
529
530 Class<?> objectClass = object.getClass();
531 String key = changes.keySet().toString();
532 if (variableUpdateStatements.containsKey(objectClass) && variableUpdateStatements.get(objectClass).containsKey(key)) {
533 sql = variableUpdateStatements.get(objectClass).get(key);
534 } else {
535 sql = determineUpdateStatement(pojo, connection);
536 }
537
538 if (log.isDebugEnabled()) {
539 log.debug("getUpdateStatement for %s for changed fields is %s", objectClass, sql);
540 }
541 return sql;
542 }
543
544 if (updateStatementsMap.containsKey(object.getClass())) {
545 sql = updateStatementsMap.get(object.getClass());
546 } else {
547 sql = determineUpdateStatement(object, connection);
548 }
549 if (log.isDebugEnabled()) {
550 log.debug("getUpdateStatement for: %s %s", object.getClass(), sql);
551 }
552 return sql;
553 }
554
555 // Used by Objects not implementing Persistable since they will always use the same update statement
556 private synchronized String determineUpdateStatement(Object object, Connection connection) {
557 if (updateStatementsMap.containsKey(object.getClass())) {
558 return updateStatementsMap.get(object.getClass());
559 }
560
561 Class<?> objectClass = object.getClass();
562 var columns = getColumns(objectClass, connection);
563 Map<String, PropertyInfo> propertyMap;
564 if (object instanceof Persistable<?> pojo) {
565 propertyMap = getChangedProperties(pojo, connection);
566 } else {
567 propertyMap = getTableColumnsPropertyInfo(objectClass, connection);
568 }
569
570 String updateStatement = buildUpdateString(object, propertyMap.keySet().stream().filter(col -> !columns.get(col).readOnly).toList().iterator(), connection);
571
572 if (object instanceof Persistable<?>) {
573 String key = propertyMap.keySet().toString();
574 if (variableUpdateStatements.containsKey(objectClass) && variableUpdateStatements.get(objectClass).containsKey(key)) {
575 return variableUpdateStatements.get(objectClass).get(key);
576 }
577
578 variableUpdateStatements.putIfAbsent(objectClass, new HashMap<>());
579 variableUpdateStatements.get(objectClass).put(key, updateStatement);
580 } else {
581 updateStatementsMap.put(objectClass, updateStatement);
582 }
583
584
585 if (log.isDebugEnabled()) {
586 log.debug("determineUpdateStatement for %s is %s", objectClass, updateStatement);
587 }
588
589 return updateStatement;
590 }
591
592 String getInsertStatement(Object object, Connection connection) throws PersismException {
593 String sql;
594 String key = getColumnsForInsert(object, connection).stream().map(columnInfo -> columnInfo.columnName).toList().toString();
595 Class<?> objectClass = object.getClass();
596 if (insertStatements.containsKey(objectClass) && insertStatements.get(objectClass).containsKey(key)) {
597 sql = insertStatements.get(objectClass).get(key);
598 } else {
599 sql = determineInsertStatement(object, connection);
600 }
601 if (log.isDebugEnabled()) {
602 log.debug("getInsertStatement for: %s %s", objectClass, sql);
603 }
604 return sql;
605 }
606
607 private synchronized String determineInsertStatement(Object object, Connection connection) {
608 List<ColumnInfo> columnsForInsert = getColumnsForInsert(object, connection);
609 String key = columnsForInsert.stream().map(columnInfo -> columnInfo.columnName).toList().toString();
610 Class<?> objectClass = object.getClass();
611
612 if (insertStatements.containsKey(objectClass) && insertStatements.get(objectClass).containsKey(key)) {
613 return insertStatements.get(objectClass).get(key);
614 }
615
616 String sd = connectionType.getKeywordStartDelimiter();
617 String ed = connectionType.getKeywordEndDelimiter();
618
619 TableInfo tableInfo = getTableInfo(objectClass);
620 String tableName = tableInfo.name();
621 String schemaName = tableInfo.schema();
622
623 StringBuilder sbi = new StringBuilder();
624 sbi.append("INSERT INTO ");
625 if (isNotEmpty(schemaName)) {
626 sbi.append(sd).append(schemaName).append(ed).append(".");
627 }
628 sbi.append(sd).append(tableName).append(ed).append(" (");
629
630 StringBuilder sbp = new StringBuilder();
631 sbp.append(" VALUES (");
632
633 String sep = "";
634
635 for (ColumnInfo column : columnsForInsert) {
636 sbi.append(sep).append(sd).append(column.columnName).append(ed);
637 sbp.append(sep).append("?");
638 sep = ", ";
639 }
640
641 if (connectionType == ConnectionType.MSSQL) {
642 // check if we have a sequence or other non auto-inc primary and use OUTPUT inserted.[COL] to get the value back.
643 Optional<ColumnInfo> primary = getPrimaryNonAutoIncColumn(object, connection);
644 if (primary.isPresent()) {
645 ColumnInfo col = primary.get();
646 sbi.append(") ").append("OUTPUT inserted.").append(sd).append(col.columnName).append(ed).append(sbp).append(") ");
647 } else {
648 sbi.append(") ").append(sbp).append(") ");
649 }
650 } else {
651 sbi.append(") ").append(sbp).append(") ");
652 }
653
654 String insertStatement;
655 insertStatement = sbi.toString();
656
657 if (log.isDebugEnabled()) {
658 log.debug("determineInsertStatement for %s is %s", object.getClass(), insertStatement);
659 }
660
661 insertStatements.putIfAbsent(objectClass, new HashMap<>());
662 insertStatements.get(objectClass).put(key, insertStatement);
663
664 return insertStatement;
665 }
666
667 private List<ColumnInfo> getColumnsForInsert(Object object, Connection connection) {
668 Map<String, ColumnInfo> columns = getColumns(object.getClass(), connection);
669 Map<String, PropertyInfo> properties = getTableColumnsPropertyInfo(object.getClass(), connection);
670 return columns.values().stream().
671 filter(col -> !col.autoIncrement).
672 filter(col -> !(col.hasDefault && properties.get(col.columnName).getValue(object) == null)).
673 filter(col -> !col.readOnly).
674 toList();
675 }
676
677 private Optional<ColumnInfo> getPrimaryNonAutoIncColumn(Object object, Connection connection) {
678 Map<String, ColumnInfo> columns = getColumns(object.getClass(), connection);
679 Map<String, PropertyInfo> properties = getTableColumnsPropertyInfo(object.getClass(), connection);
680 return columns.values().stream().
681 filter(col -> col.primary && col.hasDefault && properties.get(col.columnName).getValue(object) == null).
682 findFirst();
683 }
684
685 String getDeleteStatement(Class<?> objectClass, Connection connection) {
686 if (deleteStatementsMap.containsKey(objectClass)) {
687 return deleteStatementsMap.get(objectClass);
688 }
689 return determineDeleteStatement(objectClass, connection);
690 }
691
692 String getDefaultDeleteStatement(Class<?> objectClass, Connection connection) {
693 return getDeleteStatement(objectClass, connection) + getWhereClause(objectClass, connection);
694 }
695
696 private synchronized String determineDeleteStatement(Class<?> objectClass, Connection connection) {
697 if (deleteStatementsMap.containsKey(objectClass)) {
698 return deleteStatementsMap.get(objectClass);
699 }
700
701 String sd = connectionType.getKeywordStartDelimiter();
702 String ed = connectionType.getKeywordEndDelimiter();
703
704 TableInfo tableInfo = getTableInfo(objectClass);
705 String tableName = tableInfo.name();
706 String schemaName = tableInfo.schema();
707
708 StringBuilder sb = new StringBuilder();
709 sb.append("DELETE FROM ");
710 if (isNotEmpty(schemaName)) {
711 sb.append(sd).append(schemaName).append(ed).append(".");
712 }
713 sb.append(sd).append(tableName).append(ed);
714 String deleteStatement = sb.toString();
715
716 if (log.isDebugEnabled()) {
717 log.debug("determineDeleteStatement for %s is %s", objectClass, deleteStatement);
718 }
719
720 deleteStatementsMap.put(objectClass, deleteStatement);
721 return deleteStatement;
722 }
723
724 String getPrimaryInClause(Class<?> objectClass, int paramCount, Connection connection) {
725 if (primaryInClauseMap.containsKey(objectClass) && primaryInClauseMap.get(objectClass).containsKey(paramCount)) {
726 return primaryInClauseMap.get(objectClass).get(paramCount);
727 }
728 return determinePrimaryInClause(objectClass, paramCount, connection);
729 }
730
731 private synchronized String determinePrimaryInClause(Class<?> objectClass, int paramCount, Connection connection) {
732 if (primaryInClauseMap.containsKey(objectClass) && primaryInClauseMap.get(objectClass).containsKey(paramCount)) {
733 return primaryInClauseMap.get(objectClass).get(paramCount);
734 }
735
736 Map<Integer, String> map = primaryInClauseMap.get(objectClass);
737 if (map == null) {
738 map = new HashMap<>();
739 }
740
741 String sd = connectionType.getKeywordStartDelimiter();
742 String ed = connectionType.getKeywordEndDelimiter();
743 String andSep = "";
744
745 String query = " WHERE ";
746
747 List<String> primaryKeys = getPrimaryKeys(objectClass, connection);
748
749 StringBuilder sb = new StringBuilder(query);
750 int groups = paramCount / primaryKeys.size();
751 for (String column : primaryKeys) {
752 String sep = "";
753 sb.append(andSep).append(sd).append(column).append(ed).append(" IN (");
754 for (int j = 0; j < groups; j++) {
755 sb.append(sep).append("?");
756 sep = ", ";
757 }
758 sb.append(")");
759 andSep = " AND ";
760 }
761 query = sb.toString();
762
763 map.put(paramCount, query);
764 primaryInClauseMap.put(objectClass, map);
765
766 return query;
767 }
768
769
770 String getWhereClause(Class<?> objectClass, Connection connection) {
771 if (primaryWhereClauseMap.containsKey(objectClass)) {
772 return primaryWhereClauseMap.get(objectClass);
773 }
774 return determineWhereClause(objectClass, connection);
775 }
776
777 private synchronized String determineWhereClause(Class<?> objectClass, Connection connection) {
778 if (primaryWhereClauseMap.containsKey(objectClass)) {
779 return primaryWhereClauseMap.get(objectClass);
780 }
781
782 String sep = "";
783
784 StringBuilder sb = new StringBuilder();
785 String sd = connectionType.getKeywordStartDelimiter();
786 String ed = connectionType.getKeywordEndDelimiter();
787
788 List<String> primaryKeys = getPrimaryKeys(objectClass, connection);
789 if (primaryKeys.size() == 0) {
790 throw new PersismException(Message.TableHasNoPrimaryKeysForWhere.message(getTableInfo(objectClass).name()));
791 }
792
793 sb.append(" WHERE ");
794
795 sep = "";
796 for (String column : primaryKeys) {
797 sb.append(sep).append(sd).append(column).append(ed).append(" = ?");
798 sep = " AND ";
799 }
800
801 String where = sb.toString();
802 if (log.isDebugEnabled()) {
803 log.debug("determineWhereClause: %s %s", objectClass.getName(), where);
804 }
805 primaryWhereClauseMap.put(objectClass, where);
806 return where;
807 }
808
809 /*
810 * Default SELECT including WHERE Primary Keys - should only be called for tables
811 */
812 String getDefaultSelectStatement(Class<?> objectClass, Connection connection) {
813 assert objectClass.getAnnotation(View.class) == null;
814 assert objectClass.getAnnotation(NotTable.class) == null;
815
816 return getSelectStatement(objectClass, connection) + getWhereClause(objectClass, connection);
817 }
818
819 /**
820 * SQL SELECT COLUMNS ONLY - make public? or put a delegate somewhere else?
821 *
822 * @param objectClass
823 * @param connection
824 * @return
825 */
826 String getSelectStatement(Class<?> objectClass, Connection connection) {
827 if (selectStatementsMap.containsKey(objectClass)) {
828 return selectStatementsMap.get(objectClass);
829 }
830 return determineSelectStatement(objectClass, connection);
831 }
832
833 private synchronized String determineSelectStatement(Class<?> objectClass, Connection connection) {
834
835 if (selectStatementsMap.containsKey(objectClass)) {
836 return selectStatementsMap.get(objectClass);
837 }
838
839 String sd = connectionType.getKeywordStartDelimiter();
840 String ed = connectionType.getKeywordEndDelimiter();
841
842 TableInfo tableInfo = getTableInfo(objectClass);
843 String tableName = tableInfo.name();
844 String schemaName = tableInfo.schema();
845
846 StringBuilder sb = new StringBuilder();
847 sb.append("SELECT ");
848
849 String sep = "";
850
851 Map<String, ColumnInfo> columns = getColumns(objectClass, connection);
852 for (String column : columns.keySet()) {
853 ColumnInfo columnInfo = columns.get(column);
854 sb.append(sep).append(sd).append(columnInfo.columnName).append(ed);
855 sep = ", ";
856 }
857 sb.append(" FROM ");
858 if (isNotEmpty(schemaName)) {
859 sb.append(sd).append(schemaName).append(ed).append('.');
860 }
861 sb.append(sd).append(tableName).append(ed);
862
863
864 String selectStatement = sb.toString();
865
866 if (log.isDebugEnabled()) {
867 log.debug("determineSelectStatement for %s is %s", objectClass, selectStatement);
868 }
869
870 selectStatementsMap.put(objectClass, selectStatement);
871
872 return selectStatement;
873 }
874
875 private String buildUpdateString(Object object, Iterator<String> it, Connection connection) throws PersismException {
876 String sd = connectionType.getKeywordStartDelimiter();
877 String ed = connectionType.getKeywordEndDelimiter();
878
879 TableInfo tableInfo = getTableInfo(object.getClass());
880 String tableName = tableInfo.name();
881 String schemaName = tableInfo.schema();
882
883 List<String> primaryKeys = getPrimaryKeys(object.getClass(), connection);
884
885 StringBuilder sb = new StringBuilder();
886 sb.append("UPDATE ");
887 if (isNotEmpty(schemaName)) {
888 sb.append(sd).append(schemaName).append(ed).append(".");
889 }
890 sb.append(sd).append(tableName).append(ed).append(" SET ");
891 String sep = "";
892
893 Map<String, ColumnInfo> columns = getColumns(object.getClass(), connection);
894 while (it.hasNext()) {
895 String column = it.next();
896 ColumnInfo columnInfo = columns.get(column);
897 if (columnInfo.autoIncrement || columnInfo.primary) {
898 log.debug("buildUpdateString: skipping " + column);
899 } else {
900 sb.append(sep).append(sd).append(column).append(ed).append(" = ?");
901 sep = ", ";
902 }
903 }
904 sb.append(" WHERE ");
905 sep = "";
906 for (String column : primaryKeys) {
907 sb.append(sep).append(sd).append(column).append(ed).append(" = ?");
908 sep = " AND ";
909 }
910 return sb.toString();
911 }
912
913 Map<String, PropertyInfo> getChangedProperties(Persistable<?> persistable, Connection connection) throws PersismException {
914
915 Persistable<?> original = (Persistable<?>) persistable.readOriginalValue();
916
917 Map<String, PropertyInfo> columns = getTableColumnsPropertyInfo(persistable.getClass(), connection);
918
919 if (original == null) {
920 // Could happen in the case of cloning or other operation - so it's never read, so it never sets original.
921 return columns;
922 } else {
923 Map<String, PropertyInfo> changedColumns = new LinkedHashMap<>(columns.keySet().size());
924
925 for (String column : columns.keySet()) {
926 PropertyInfo propertyInfo = columns.get(column);
927
928 Object newValue = propertyInfo.getValue(persistable);
929 Object orgValue = propertyInfo.getValue(original);
930 if (!Objects.equals(newValue, orgValue)) {
931 changedColumns.put(column, propertyInfo);
932 }
933 }
934 return changedColumns;
935 }
936
937 }
938
939 <T> Map<String, ColumnInfo> getColumns(Class<T> objectClass, Connection connection) throws PersismException {
940 // Realistically at this point this objectClass will always be in the map since it's defined early
941 // when we get the table name, but I'll double-check it for determineColumnInfo anyway.
942 if (columnInfoMap.containsKey(objectClass)) {
943 return columnInfoMap.get(objectClass);
944 }
945 return determineColumnInfo(objectClass, getTableInfo(objectClass), connection);
946 }
947
948 <T> Map<String, PropertyInfo> getQueryColumnsPropertyInfo(Class<T> objectClass, ResultSet rs) throws PersismException {
949 try {
950 return determinePropertyInfoFromResultSet(objectClass, rs);
951 } catch (SQLException e) {
952 throw new PersismException(e.getMessage(), e);
953 }
954 }
955
956 <T> Map<String, PropertyInfo> getTableColumnsPropertyInfo(Class<T> objectClass, Connection connection) throws PersismException {
957 if (propertyInfoMap.containsKey(objectClass)) {
958 return propertyInfoMap.get(objectClass);
959 }
960 return determinePropertyInfo(objectClass, getTableInfo(objectClass), connection);
961 }
962
963 <T> TableInfo getTableInfo(Class<T> objectClass) {
964 if (tableOrViewMap.containsKey(objectClass)) {
965 return tableOrViewMap.get(objectClass);
966 }
967
968 return determineTableInfo(objectClass);
969 }
970
971 private synchronized <T> TableInfo determineTableInfo(Class<T> objectClass) {
972 if (tableOrViewMap.containsKey(objectClass)) {
973 return tableOrViewMap.get(objectClass);
974 }
975
976 String tableName;
977 String schemaName = null;
978 TableInfo foundInfo = null;
979
980 Table tableAnnotation = objectClass.getAnnotation(Table.class);
981 View viewAnnotation = objectClass.getAnnotation(View.class);
982
983 if (tableAnnotation != null) {
984 tableName = tableAnnotation.value();
985 if (tableName.contains(".")) {
986 schemaName = tableName.substring(0, tableName.indexOf("."));
987 tableName = tableName.substring(tableName.indexOf(".") + 1);
988 }
989
990 boolean found = false;
991 for (TableInfo table : tables) {
992 if (table.name().equalsIgnoreCase(tableName)) {
993 if (schemaName != null) {
994 if (table.schema().equalsIgnoreCase(schemaName)) {
995 foundInfo = table;
996 found = true;
997 break;
998 }
999 } else {
1000 foundInfo = table;
1001 found = true;
1002 break;
1003 }
1004 }
1005 }
1006 if (schemaName == null) {
1007 final String table = tableName;
1008 if (tables.stream().filter(tableInfo -> tableInfo.name().equalsIgnoreCase(table)).count() > 1) {
1009 throw new PersismException(Message.MoreThanOneTableOrViewInDifferentSchemas.message("TABLE", table));
1010 }
1011 }
1012 if (!found) {
1013 throw new PersismException(Message.CouldNotFindTableNameInTheDatabase.message(tableName, objectClass.getName()));
1014 }
1015 } else if (viewAnnotation != null && isNotEmpty(viewAnnotation.value())) {
1016 tableName = viewAnnotation.value();
1017 if (tableName.contains(".")) {
1018 schemaName = tableName.substring(0, tableName.indexOf("."));
1019 tableName = tableName.substring(tableName.indexOf(".") + 1);
1020 }
1021
1022 boolean found = false;
1023 for (TableInfo view : views) {
1024 if (view.name().equalsIgnoreCase(tableName)) {
1025 if (schemaName != null) {
1026 if (view.schema().equalsIgnoreCase(schemaName)) {
1027 foundInfo = view;
1028 found = true;
1029 break;
1030 }
1031 } else {
1032 foundInfo = view;
1033 found = true;
1034 break;
1035 }
1036 }
1037 }
1038 if (schemaName == null) {
1039 final String table = tableName;
1040 if (views.stream().filter(tableInfo -> tableInfo.name().equalsIgnoreCase(table)).count() > 1) {
1041 throw new PersismException(Message.MoreThanOneTableOrViewInDifferentSchemas.message("VIEW", table));
1042 }
1043 }
1044 if (!found) {
1045 throw new PersismException(Message.CouldNotFindViewNameInTheDatabase.message(tableName, objectClass.getName()));
1046 }
1047 } else {
1048 foundInfo = guessTableOrView(objectClass);
1049 }
1050 tableOrViewMap.put(objectClass, foundInfo);
1051 return foundInfo;
1052 }
1053
1054 // Returns the table/view name found in the DB in the same case as in the DB.
1055 // throws PersismException if we cannot guess any table/view name for this class.
1056 private <T> TableInfo guessTableOrView(Class<T> objectClass) throws PersismException {
1057 Set<String> guesses = new LinkedHashSet<>(6); // guess order is important
1058 List<TableInfo> guessedTables = new ArrayList<>(6);
1059
1060 String className = objectClass.getSimpleName();
1061
1062 Set<TableInfo> list;
1063 boolean isView = false;
1064 if (objectClass.getAnnotation(View.class) != null) {
1065 list = views;
1066 isView = true;
1067 } else {
1068 list = tables;
1069 }
1070
1071 addTableGuesses(className, guesses);
1072 for (TableInfo table : list) {
1073 for (String guess : guesses) {
1074 if (guess.equalsIgnoreCase(table.name())) {
1075 guessedTables.add(table);
1076 }
1077 }
1078 }
1079 if (guessedTables.size() == 0) {
1080 throw new PersismException(Message.CouldNotDetermineTableOrViewForType.message(isView ? "view" : "table", objectClass.getName(), guesses));
1081 }
1082
1083 if (guessedTables.size() > 1) {
1084 Set<String> multipleGuesses = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
1085 multipleGuesses.addAll(guessedTables.stream().map(TableInfo::name).toList());
1086 throw new PersismException(Message.CouldNotDetermineTableOrViewForTypeMultipleMatches.message(isView ? "view" : "table", objectClass.getName(), guesses, multipleGuesses));
1087 }
1088 return guessedTables.get(0);
1089 }
1090
1091 private void addTableGuesses(String className, Collection<String> guesses) {
1092 // PascalCasing class name should make
1093 // PascalCasing
1094 // PascalCasings
1095 // Pascal Casing
1096 // Pascal Casings
1097 // Pascal_Casing
1098 // Pascal_Casings
1099 // Order is important.
1100
1101 String guess;
1102 String pluralClassName;
1103 String pluralClassName2 = null;
1104
1105 if (className.endsWith("y")) {
1106 // supply - supplies, category - categories
1107 pluralClassName = className.substring(0, className.length() - 1) + "ies";
1108 pluralClassName2 = className + "s"; // holiday
1109 } else if (className.endsWith("x")) {
1110 // tax - taxes, mailbox - mailboxes
1111 pluralClassName = className + "es";
1112 } else {
1113 pluralClassName = className + "s";
1114 }
1115
1116 guesses.add(className);
1117 guesses.add(pluralClassName);
1118 if (pluralClassName2 != null) {
1119 guesses.add(pluralClassName2);
1120 }
1121
1122 guess = camelToTitleCase(className);
1123 guesses.add(guess); // name with spaces
1124 guesses.add(guess.replaceAll(" ", "_")); // name with spaces changed to _
1125
1126 guess = camelToTitleCase(pluralClassName);
1127 guesses.add(guess); // plural name with spaces
1128 guesses.add(guess.replaceAll(" ", "_")); // plural name with spaces changed to _
1129
1130 if (pluralClassName2 != null) {
1131 guess = camelToTitleCase(pluralClassName2);
1132 guesses.add(guess); // plural name with spaces
1133 guesses.add(guess.replaceAll(" ", "_")); // plural name with spaces changed to _
1134 }
1135 }
1136
1137 List<String> getPrimaryKeys(Class<?> objectClass, Connection connection) throws PersismException {
1138 // todo cache? called by Session and SessionHelper.
1139 // ensures meta-data will be available because this method could be called before getting the table info object
1140 TableInfo tableInfo = getTableInfo(objectClass);
1141
1142 List<String> primaryKeys = new ArrayList<>(4);
1143 Map<String, ColumnInfo> map = getColumns(objectClass, connection);
1144 for (ColumnInfo col : map.values()) {
1145 if (col.primary) {
1146 primaryKeys.add(col.columnName);
1147 }
1148 }
1149 if (log.isDebugEnabled()) {
1150 log.debug("getPrimaryKeys for %s %s", tableInfo.name(), primaryKeys);
1151 }
1152 return primaryKeys;
1153 }
1154
1155 ConnectionType getConnectionType() {
1156 return connectionType;
1157 }
1158
1159 }