1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package org.waarp.common.database.data;
21
22 import com.fasterxml.jackson.databind.JsonNode;
23 import com.fasterxml.jackson.databind.node.ObjectNode;
24 import org.waarp.common.database.DbConstant;
25 import org.waarp.common.database.DbPreparedStatement;
26 import org.waarp.common.database.DbSession;
27 import org.waarp.common.database.exception.WaarpDatabaseException;
28 import org.waarp.common.database.exception.WaarpDatabaseNoConnectionException;
29 import org.waarp.common.database.exception.WaarpDatabaseNoDataException;
30 import org.waarp.common.database.exception.WaarpDatabaseSqlException;
31 import org.waarp.common.database.model.DbModelAbstract;
32 import org.waarp.common.json.JsonHandler;
33
34 import java.io.IOException;
35 import java.io.InputStream;
36 import java.io.Reader;
37 import java.sql.Date;
38 import java.sql.PreparedStatement;
39 import java.sql.ResultSet;
40 import java.sql.SQLException;
41 import java.sql.Timestamp;
42 import java.sql.Types;
43
44
45
46
47
48
49
50
51
52
53 public abstract class AbstractDbData {
54 private static final String NO_ROW_FOUND = "No row found";
55 public static final String JSON_MODEL = "@model";
56
57
58
59
60 public enum UpdatedInfo {
61
62
63
64 UNKNOWN,
65
66
67
68 NOTUPDATED,
69
70
71
72 INTERRUPTED,
73
74
75
76 TOSUBMIT,
77
78
79
80 INERROR,
81
82
83
84 RUNNING,
85
86
87
88 DONE
89 }
90
91
92
93
94
95
96
97
98
99 protected DbValue[] primaryKey;
100 protected DbValue[] otherFields;
101 protected DbValue[] allFields;
102
103 protected boolean isSaved;
104
105
106
107 protected final DbSession dbSession;
108
109
110
111
112
113
114 protected AbstractDbData(final DbSession dbSession) {
115 this.dbSession = dbSession;
116 initObject();
117 }
118
119
120
121
122 protected AbstractDbData() {
123 dbSession = null;
124 initObject();
125 }
126
127
128
129
130
131
132
133
134 protected abstract void initObject();
135
136
137
138
139 protected abstract String getWherePrimaryKey();
140
141
142
143
144 protected abstract void setPrimaryKey();
145
146 protected abstract String getSelectAllFields();
147
148 protected abstract String getTable();
149
150 protected abstract String getInsertAllValues();
151
152 protected abstract String getUpdateAllFields();
153
154
155
156
157
158
159
160
161 public boolean exist() throws WaarpDatabaseException {
162 if (dbSession == null) {
163 return false;
164 }
165 final DbPreparedStatement preparedStatement =
166 new DbPreparedStatement(dbSession);
167 try {
168 preparedStatement.createPrepareStatement(
169 "SELECT " + primaryKey[0].getColumn() + " FROM " + getTable() +
170 " WHERE " + getWherePrimaryKey());
171 setPrimaryKey();
172 setValues(preparedStatement, primaryKey);
173 preparedStatement.executeQuery();
174 return preparedStatement.getNext();
175 } finally {
176 preparedStatement.realClose();
177 }
178 }
179
180
181
182
183
184
185 public void select() throws WaarpDatabaseException {
186 if (dbSession == null) {
187 throw new WaarpDatabaseNoDataException(NO_ROW_FOUND);
188 }
189 final DbPreparedStatement preparedStatement =
190 new DbPreparedStatement(dbSession);
191 try {
192 preparedStatement.createPrepareStatement(
193 "SELECT " + getSelectAllFields() + " FROM " + getTable() + " WHERE " +
194 getWherePrimaryKey());
195 setPrimaryKey();
196 setValues(preparedStatement, primaryKey);
197 preparedStatement.executeQuery();
198 if (preparedStatement.getNext()) {
199 getValues(preparedStatement, allFields);
200 isSaved = true;
201 } else {
202 throw new WaarpDatabaseNoDataException(NO_ROW_FOUND);
203 }
204 } finally {
205 preparedStatement.realClose();
206 }
207 }
208
209
210
211
212
213
214 public void insert() throws WaarpDatabaseException {
215 if (isSaved) {
216 return;
217 }
218 if (dbSession == null) {
219 isSaved = true;
220 return;
221 }
222 setToArray();
223 final DbPreparedStatement preparedStatement =
224 new DbPreparedStatement(dbSession);
225 try {
226 preparedStatement.createPrepareStatement(
227 "INSERT INTO " + getTable() + " (" + getSelectAllFields() +
228 ") VALUES " + getInsertAllValues());
229 setValues(preparedStatement, allFields);
230 final int count = preparedStatement.executeUpdate();
231 if (count <= 0) {
232 throw new WaarpDatabaseNoDataException(NO_ROW_FOUND);
233 }
234 isSaved = true;
235 } finally {
236 preparedStatement.realClose();
237 }
238 }
239
240
241
242
243
244
245 public void update() throws WaarpDatabaseException {
246 if (isSaved) {
247 return;
248 }
249 if (dbSession == null) {
250 isSaved = true;
251 return;
252 }
253 setToArray();
254 final DbPreparedStatement preparedStatement =
255 new DbPreparedStatement(dbSession);
256 try {
257 preparedStatement.createPrepareStatement(
258 "UPDATE " + getTable() + " SET " + getUpdateAllFields() + " WHERE " +
259 getWherePrimaryKey());
260 setValues(preparedStatement, allFields);
261 final int count = preparedStatement.executeUpdate();
262 if (count <= 0) {
263 throw new WaarpDatabaseNoDataException(NO_ROW_FOUND);
264 }
265 isSaved = true;
266 } finally {
267 preparedStatement.realClose();
268 }
269 }
270
271 public final DbValue[] getAllFields() {
272 return allFields;
273 }
274
275
276
277
278
279
280 public void delete() throws WaarpDatabaseException {
281 if (dbSession == null) {
282 return;
283 }
284 final DbPreparedStatement preparedStatement =
285 new DbPreparedStatement(dbSession);
286 try {
287 preparedStatement.createPrepareStatement(
288 "DELETE FROM " + getTable() + " WHERE " + getWherePrimaryKey());
289 setPrimaryKey();
290 setValues(preparedStatement, primaryKey);
291 final int count = preparedStatement.executeUpdate();
292 if (count <= 0) {
293 throw new WaarpDatabaseNoDataException(NO_ROW_FOUND);
294 }
295 isSaved = false;
296 } finally {
297 preparedStatement.realClose();
298 }
299 }
300
301
302
303
304
305
306 public abstract void changeUpdatedInfo(UpdatedInfo info);
307
308
309
310
311 protected abstract void setToArray() throws WaarpDatabaseSqlException;
312
313
314
315
316
317
318 protected abstract void setFromArray() throws WaarpDatabaseSqlException;
319
320
321
322
323
324
325
326
327 public static void validateLength(final byte[]... values)
328 throws WaarpDatabaseSqlException {
329 for (final byte[] value : values) {
330 if (value != null && value.length > DbModelAbstract.MAX_BINARY * 2) {
331 throw new WaarpDatabaseSqlException(
332 "BINARY value exceed max size: " + value.length + " (" +
333 DbModelAbstract.MAX_BINARY + ")");
334 }
335 }
336 }
337
338
339
340
341
342
343
344
345
346 public static void validateLength(final int type, final String... values)
347 throws WaarpDatabaseSqlException {
348 for (final String value : values) {
349 if (value == null) {
350 continue;
351 }
352 switch (type) {
353 case Types.VARCHAR:
354 if (value.length() > DbModelAbstract.MAX_VARCHAR) {
355 throw new WaarpDatabaseSqlException(
356 "VARCHAR value exceed max size: " + value.length() + " (" +
357 DbModelAbstract.MAX_VARCHAR + ")");
358 }
359 break;
360 case Types.NVARCHAR:
361 if (value.length() > DbModelAbstract.MAX_KEY_VARCHAR) {
362 throw new WaarpDatabaseSqlException(
363 "VARCHAR as KEY value exceed max size: " + value.length() +
364 " (" + DbModelAbstract.MAX_KEY_VARCHAR + ")");
365 }
366 break;
367 case Types.LONGVARCHAR:
368 if (value.length() > DbModelAbstract.MAX_LONGVARCHAR) {
369 throw new WaarpDatabaseSqlException(
370 "LONGVARCHAR value exceed max size: " + value.length() + " (" +
371 DbModelAbstract.MAX_LONGVARCHAR + ")");
372 }
373 break;
374 default:
375 break;
376 }
377 }
378 }
379
380
381
382
383
384
385
386
387
388
389 public static void setTrueValue(final PreparedStatement ps,
390 final DbValue value, final int rank)
391 throws WaarpDatabaseSqlException {
392 try {
393 final String svalue;
394 switch (value.type) {
395 case Types.VARCHAR:
396 if (value.getValue() == null) {
397 ps.setNull(rank, Types.VARCHAR);
398 break;
399 }
400 svalue = (String) value.getValue();
401 validateLength(Types.VARCHAR, svalue);
402 ps.setString(rank, svalue);
403 break;
404 case Types.NVARCHAR:
405 if (value.getValue() == null) {
406 ps.setNull(rank, Types.VARCHAR);
407 break;
408 }
409 svalue = (String) value.getValue();
410 validateLength(Types.NVARCHAR, svalue);
411 ps.setString(rank, svalue);
412 break;
413 case Types.LONGVARCHAR:
414 if (value.getValue() == null) {
415 ps.setNull(rank, Types.LONGVARCHAR);
416 break;
417 }
418 svalue = (String) value.getValue();
419 validateLength(Types.LONGVARCHAR, svalue);
420 ps.setString(rank, svalue);
421 break;
422 case Types.BIT:
423 if (value.getValue() == null) {
424 ps.setNull(rank, Types.BIT);
425 break;
426 }
427 ps.setBoolean(rank, (Boolean) value.getValue());
428 break;
429 case Types.TINYINT:
430 if (value.getValue() == null) {
431 ps.setNull(rank, Types.TINYINT);
432 break;
433 }
434 ps.setByte(rank, (Byte) value.getValue());
435 break;
436 case Types.SMALLINT:
437 if (value.getValue() == null) {
438 ps.setNull(rank, Types.SMALLINT);
439 break;
440 }
441 ps.setShort(rank, (Short) value.getValue());
442 break;
443 case Types.INTEGER:
444 if (value.getValue() == null) {
445 ps.setNull(rank, Types.INTEGER);
446 break;
447 }
448 ps.setInt(rank, (Integer) value.getValue());
449 break;
450 case Types.BIGINT:
451 if (value.getValue() == null) {
452 ps.setNull(rank, Types.BIGINT);
453 break;
454 }
455 ps.setLong(rank, (Long) value.getValue());
456 break;
457 case Types.REAL:
458 if (value.getValue() == null) {
459 ps.setNull(rank, Types.REAL);
460 break;
461 }
462 ps.setFloat(rank, (Float) value.getValue());
463 break;
464 case Types.DOUBLE:
465 if (value.getValue() == null) {
466 ps.setNull(rank, Types.DOUBLE);
467 break;
468 }
469 ps.setDouble(rank, (Double) value.getValue());
470 break;
471 case Types.VARBINARY:
472 if (value.getValue() == null) {
473 ps.setNull(rank, Types.VARBINARY);
474 break;
475 }
476 final byte[] bvalue = (byte[]) value.getValue();
477 validateLength(bvalue);
478 ps.setBytes(rank, bvalue);
479 break;
480 case Types.DATE:
481 if (value.getValue() == null) {
482 ps.setNull(rank, Types.DATE);
483 break;
484 }
485 ps.setDate(rank, (Date) value.getValue());
486 break;
487 case Types.TIMESTAMP:
488 if (value.getValue() == null) {
489 ps.setNull(rank, Types.TIMESTAMP);
490 break;
491 }
492 ps.setTimestamp(rank, (Timestamp) value.getValue());
493 break;
494 case Types.CLOB:
495 if (value.getValue() == null) {
496 ps.setNull(rank, Types.CLOB);
497 break;
498 }
499 ps.setClob(rank, (Reader) value.getValue());
500 break;
501 case Types.BLOB:
502 if (value.getValue() == null) {
503 ps.setNull(rank, Types.BLOB);
504 break;
505 }
506 ps.setBlob(rank, (InputStream) value.getValue());
507 break;
508 default:
509 throw new WaarpDatabaseSqlException(
510 "Type not supported: " + value.type + " at " + rank);
511 }
512 } catch (final ClassCastException e) {
513 throw new WaarpDatabaseSqlException(
514 "Setting values casting error: " + value.type + " at " + rank, e);
515 } catch (final SQLException e) {
516 DbConstant.error(e);
517 throw new WaarpDatabaseSqlException(
518 "Setting values in error: " + value.type + " at " + rank, e);
519 }
520 }
521
522
523
524
525
526
527
528
529
530
531 protected final void setValues(final DbPreparedStatement preparedStatement,
532 final DbValue[] values)
533 throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
534 final PreparedStatement ps = preparedStatement.getPreparedStatement();
535 for (int i = 0; i < values.length; i++) {
536 final DbValue value = values[i];
537 setTrueValue(ps, value, i + 1);
538 }
539 }
540
541
542
543
544
545
546
547
548
549 public static void getTrueValue(final ResultSet rs, final DbValue value)
550 throws WaarpDatabaseSqlException {
551 try {
552 switch (value.type) {
553 case Types.VARCHAR:
554 case Types.NVARCHAR:
555 case Types.LONGVARCHAR:
556 value.setValue(rs.getString(value.getColumn()));
557 break;
558 case Types.BIT:
559 value.setValue(rs.getBoolean(value.getColumn()));
560 break;
561 case Types.TINYINT:
562 value.setValue(rs.getByte(value.getColumn()));
563 break;
564 case Types.SMALLINT:
565 value.setValue(rs.getShort(value.getColumn()));
566 break;
567 case Types.INTEGER:
568 value.setValue(rs.getInt(value.getColumn()));
569 break;
570 case Types.BIGINT:
571 value.setValue(rs.getLong(value.getColumn()));
572 break;
573 case Types.REAL:
574 value.setValue(rs.getFloat(value.getColumn()));
575 break;
576 case Types.DOUBLE:
577 value.setValue(rs.getDouble(value.getColumn()));
578 break;
579 case Types.VARBINARY:
580 value.setValue(rs.getBytes(value.getColumn()));
581 break;
582 case Types.DATE:
583 value.setValue(rs.getDate(value.getColumn()));
584 break;
585 case Types.TIMESTAMP:
586 value.setValue(rs.getTimestamp(value.getColumn()));
587 break;
588 case Types.CLOB:
589 value.setValue(rs.getClob(value.getColumn()).getCharacterStream());
590 break;
591 case Types.BLOB:
592 value.setValue(rs.getBlob(value.getColumn()).getBinaryStream());
593 break;
594 default:
595 throw new WaarpDatabaseSqlException(
596 "Type not supported: " + value.type + " for " +
597 value.getColumn());
598 }
599 } catch (final SQLException e) {
600 DbConstant.error(e);
601 throw new WaarpDatabaseSqlException(
602 "Getting values in error: " + value.type + " for " +
603 value.getColumn(), e);
604 }
605 }
606
607
608
609
610
611
612
613
614
615
616 protected void getValues(final DbPreparedStatement preparedStatement,
617 final DbValue[] values)
618 throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException {
619 final ResultSet rs = preparedStatement.getResultSet();
620 for (final DbValue value : values) {
621 getTrueValue(rs, value);
622 }
623 setFromArray();
624 }
625
626
627
628
629 public String asJson() {
630 final ObjectNode node = getJson();
631 return JsonHandler.writeAsString(node);
632 }
633
634
635
636
637
638
639 public ObjectNode getJson() {
640 final ObjectNode node = JsonHandler.createObjectNode();
641 node.put(JSON_MODEL, getClass().getSimpleName());
642 for (final DbValue value : allFields) {
643 switch (value.type) {
644 case Types.VARCHAR:
645 case Types.NVARCHAR:
646 case Types.LONGVARCHAR:
647 node.put(value.getColumn(), (String) value.getValue());
648 break;
649 case Types.BIT:
650 node.put(value.getColumn(), (Boolean) value.getValue());
651 break;
652 case Types.TINYINT:
653 node.put(value.getColumn(), (Byte) value.getValue());
654 break;
655 case Types.SMALLINT:
656 node.put(value.getColumn(), (Short) value.getValue());
657 break;
658 case Types.INTEGER:
659 node.put(value.getColumn(), (Integer) value.getValue());
660 break;
661 case Types.BIGINT:
662 node.put(value.getColumn(), (Long) value.getValue());
663 break;
664 case Types.REAL:
665 node.put(value.getColumn(), (Float) value.getValue());
666 break;
667 case Types.DOUBLE:
668 node.put(value.getColumn(), (Double) value.getValue());
669 break;
670 case Types.VARBINARY:
671 node.put(value.getColumn(), (byte[]) value.getValue());
672 break;
673 case Types.DATE:
674 node.put(value.getColumn(), ((Date) value.getValue()).getTime());
675 break;
676 case Types.TIMESTAMP:
677 node.put(value.getColumn(), ((Timestamp) value.getValue()).getTime());
678 break;
679 case Types.CLOB:
680 case Types.BLOB:
681 default:
682 node.put(value.getColumn(), "Unsupported type=" + value.type);
683 }
684 }
685 return node;
686 }
687
688
689
690
691
692
693
694
695
696
697 public void setFromJson(final ObjectNode node, final boolean ignorePrimaryKey)
698 throws WaarpDatabaseSqlException {
699 DbValue[] list = allFields;
700 if (ignorePrimaryKey) {
701 list = otherFields;
702 }
703 for (final DbValue value : list) {
704 if ("UPDATEDINFO".equalsIgnoreCase(value.getColumn())) {
705 continue;
706 }
707 final JsonNode item = node.get(value.getColumn());
708 if (item != null && !item.isMissingNode() && !item.isNull()) {
709 isSaved = false;
710 switch (value.type) {
711 case Types.VARCHAR:
712 case Types.NVARCHAR:
713 case Types.LONGVARCHAR:
714 final String svalue = item.asText();
715 validateLength(value.type, svalue);
716 value.setValue(svalue);
717 break;
718 case Types.BIT:
719 value.setValue(item.asBoolean());
720 break;
721 case Types.TINYINT:
722 case Types.SMALLINT:
723 case Types.INTEGER:
724 value.setValue(item.asInt());
725 break;
726 case Types.BIGINT:
727 value.setValue(item.asLong());
728 break;
729 case Types.REAL:
730 case Types.DOUBLE:
731 value.setValue(item.asDouble());
732 break;
733 case Types.VARBINARY:
734 try {
735 final byte[] bvalue = item.binaryValue();
736 validateLength(bvalue);
737 value.setValue(bvalue);
738 } catch (final IOException e) {
739 throw new WaarpDatabaseSqlException(
740 "Issue while assigning array of bytes", e);
741 }
742 break;
743 case Types.DATE:
744 value.setValue(new Date(item.asLong()));
745 break;
746 case Types.TIMESTAMP:
747 value.setValue(new Timestamp(item.asLong()));
748 break;
749 case Types.CLOB:
750 case Types.BLOB:
751 default:
752 throw new WaarpDatabaseSqlException(
753 "Unsupported type: " + value.type);
754 }
755 }
756 }
757 setFromArray();
758 }
759 }