package nxt util import nxt Db import java sql Connection import java

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
package nxt.util;
import nxt.Db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public abstract class DbTable<T> {
protected abstract String table();
protected abstract T load(Connection con, ResultSet rs) throws SQLException;
protected abstract void save(Connection con, T t) throws SQLException;
protected abstract void delete(Connection con, T t) throws SQLException;
protected String defaultSort() {
return "ORDER BY height DESC";
}
public T get(Long id) {
try (Connection con = Db.getConnection();
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM " + table() + " WHERE id = ?")) {
pstmt.setLong(1, id);
return get(con, pstmt);
} catch (SQLException e) {
throw new RuntimeException(e.toString(), e);
}
}
public T getBy(String columnName, String value) {
try (Connection con = Db.getConnection();
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM " + table()
+ " WHERE " + columnName + " = ?")) {
pstmt.setString(1, value);
return get(con, pstmt);
} catch (SQLException e) {
throw new RuntimeException(e.toString(), e);
}
}
protected final T get(Connection con, PreparedStatement pstmt) throws SQLException {
try (ResultSet rs = pstmt.executeQuery()) {
T t = null;
if (rs.next()) {
t = load(con, rs);
}
if (rs.next()) {
throw new RuntimeException("Multiple records found");
}
return t;
}
}
public List<T> getManyBy(String columnName, Long value) {
try (Connection con = Db.getConnection();
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM " + table()
+ " WHERE " + columnName + " = ? " + defaultSort())) {
pstmt.setLong(1, value);
return getManyBy(con, pstmt);
} catch (SQLException e) {
throw new RuntimeException(e.toString(), e);
}
}
public List<Long> getManyIdsBy(String targetColumnName, String filterColumnName, Long value) {
try (Connection con = Db.getConnection();
PreparedStatement pstmt = con.prepareStatement("SELECT " + targetColumnName + " FROM " + table()
+ " WHERE " + filterColumnName + " = ? ")) {
pstmt.setLong(1, value);
List<Long> result = new ArrayList<>();
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
result.add(rs.getLong(targetColumnName));
}
}
return result;
} catch (SQLException e) {
throw new RuntimeException(e.toString(), e);
}
}
public List<T> getManyBy(String columnName, Boolean value) {
try (Connection con = Db.getConnection();
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM " + table()
+ " WHERE " + columnName + " = ? ")) {
pstmt.setBoolean(1, value);
return getManyBy(con, pstmt);
} catch (SQLException e) {
throw new RuntimeException(e.toString(), e);
}
}
public List<T> getAll() {
try (Connection con = Db.getConnection();
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM " + table() + " " + defaultSort())) {
return getManyBy(con, pstmt);
} catch (SQLException e) {
throw new RuntimeException(e.toString(), e);
}
}
public final List<T> getManyBy(Connection con, PreparedStatement pstmt) {
try {
List<T> result = new ArrayList<>();
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
result.add(load(con, rs));
}
}
return result;
} catch (SQLException e) {
throw new RuntimeException(e.toString(), e);
}
}
public int getCount() {
try (Connection con = Db.getConnection();
PreparedStatement pstmt = con.prepareStatement("SELECT COUNT(*) FROM " + table());
ResultSet rs = pstmt.executeQuery()) {
rs.next();
return rs.getInt(1);
} catch (SQLException e) {
throw new RuntimeException(e.toString(), e);
}
}
public void insert(T t) {
try (Connection con = Db.getConnection()) {
save(con, t);
} catch (SQLException e) {
throw new RuntimeException(e.toString(), e);
}
}
public final void delete(T t) {
if (t == null) {
return;
}
try (Connection con = Db.getConnection()) {
delete(con, t);
} catch (SQLException e) {
throw new RuntimeException(e.toString(), e);
}
}
public final void truncate() {
try (Connection con = Db.getConnection();
Statement stmt = con.createStatement()) {
stmt.executeUpdate("SET REFERENTIAL_INTEGRITY FALSE");
stmt.executeUpdate("TRUNCATE TABLE " + table());
stmt.executeUpdate("SET REFERENTIAL_INTEGRITY TRUE");
} catch (SQLException e) {
throw new RuntimeException(e.toString(), e);
}
}
}