1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 package goldengate.common.database.model;
22
23 import goldengate.common.logging.GgInternalLogger;
24 import goldengate.common.logging.GgInternalLoggerFactory;
25
26 import java.sql.Connection;
27 import java.sql.DriverManager;
28 import java.sql.SQLException;
29 import java.sql.Types;
30
31 import org.h2.jdbcx.JdbcConnectionPool;
32
33 import goldengate.common.database.DbAdmin;
34 import goldengate.common.database.DbConstant;
35 import goldengate.common.database.DbPreparedStatement;
36 import goldengate.common.database.DbRequest;
37 import goldengate.common.database.DbSession;
38 import goldengate.common.database.data.DbDataModel;
39 import goldengate.common.database.exception.GoldenGateDatabaseNoConnectionException;
40 import goldengate.common.database.exception.GoldenGateDatabaseNoDataException;
41 import goldengate.common.database.exception.GoldenGateDatabaseSqlException;
42
43
44
45
46
47
48 public abstract class DbModelH2 extends DbModelAbstract {
49
50
51
52 private static final GgInternalLogger logger = GgInternalLoggerFactory
53 .getLogger(DbModelH2.class);
54
55 public static DbType type = DbType.H2;
56
57 protected static JdbcConnectionPool pool;
58
59
60
61
62 @Override
63 public DbType getDbType() {
64 return type;
65 }
66
67
68
69
70
71
72
73
74 public DbModelH2(String dbserver, String dbuser, String dbpasswd) throws GoldenGateDatabaseNoConnectionException {
75 this();
76 pool = JdbcConnectionPool.create(dbserver, dbuser, dbpasswd);
77 pool.setMaxConnections(DbConstant.MAXCONNECTION);
78 pool.setLoginTimeout(DbConstant.DELAYMAXCONNECTION);
79 logger.warn("Some info: MaxConn: "+pool.getMaxConnections()+" LogTimeout: "+pool.getLoginTimeout());
80 }
81
82
83
84
85
86 protected DbModelH2() throws GoldenGateDatabaseNoConnectionException {
87 if (DbModelFactory.classLoaded) {
88 return;
89 }
90 try {
91 DriverManager.registerDriver(new org.h2.Driver());
92 DbModelFactory.classLoaded = true;
93 } catch (SQLException e) {
94
95 logger.error("Cannot register Driver " + type.name()+ "\n"+e.getMessage());
96 DbSession.error(e);
97 throw new GoldenGateDatabaseNoConnectionException(
98 "Cannot load database drive:" + type.name(), e);
99 }
100 }
101
102 @Override
103 public void releaseResources() {
104 if (pool != null)
105 pool.dispose();
106 }
107
108 @Override
109 public int currentNumberOfPooledConnections() {
110 if (pool != null)
111 return pool.getActiveConnections();
112 return DbAdmin.getNbConnection();
113 }
114
115 @Override
116 public Connection getDbConnection(String server, String user, String passwd)
117 throws SQLException {
118 if (pool != null)
119 return pool.getConnection();
120 return super.getDbConnection(server, user, passwd);
121 }
122
123
124
125 protected static enum DBType {
126 CHAR(Types.CHAR, " CHAR(3) "),
127 VARCHAR(Types.VARCHAR, " VARCHAR(254) "),
128 LONGVARCHAR(Types.LONGVARCHAR, " LONGVARCHAR "),
129 BIT(Types.BIT, " BOOLEAN "),
130 TINYINT(Types.TINYINT, " TINYINT "),
131 SMALLINT(Types.SMALLINT, " SMALLINT "),
132 INTEGER(Types.INTEGER, " INTEGER "),
133 BIGINT(Types.BIGINT, " BIGINT "),
134 REAL(Types.REAL, " REAL "),
135 DOUBLE(Types.DOUBLE, " DOUBLE "),
136 VARBINARY(Types.VARBINARY, " BINARY "),
137 DATE(Types.DATE, " DATE "),
138 TIMESTAMP(Types.TIMESTAMP, " TIMESTAMP "),
139 CLOB(Types.CLOB, " CLOB "),
140 BLOB(Types.BLOB, " BLOB ");
141
142 public int type;
143
144 public String constructor;
145
146 private DBType(int type, String constructor) {
147 this.type = type;
148 this.constructor = constructor;
149 }
150
151 public static String getType(int sqltype) {
152 switch (sqltype) {
153 case Types.CHAR:
154 return CHAR.constructor;
155 case Types.VARCHAR:
156 return VARCHAR.constructor;
157 case Types.LONGVARCHAR:
158 return LONGVARCHAR.constructor;
159 case Types.BIT:
160 return BIT.constructor;
161 case Types.TINYINT:
162 return TINYINT.constructor;
163 case Types.SMALLINT:
164 return SMALLINT.constructor;
165 case Types.INTEGER:
166 return INTEGER.constructor;
167 case Types.BIGINT:
168 return BIGINT.constructor;
169 case Types.REAL:
170 return REAL.constructor;
171 case Types.DOUBLE:
172 return DOUBLE.constructor;
173 case Types.VARBINARY:
174 return VARBINARY.constructor;
175 case Types.DATE:
176 return DATE.constructor;
177 case Types.TIMESTAMP:
178 return TIMESTAMP.constructor;
179 case Types.CLOB:
180 return CLOB.constructor;
181 case Types.BLOB:
182 return BLOB.constructor;
183 default:
184 return null;
185 }
186 }
187 }
188
189 @Override
190 public void createTables(DbSession session) throws GoldenGateDatabaseNoConnectionException {
191
192 String createTableH2 = "CREATE TABLE IF NOT EXISTS ";
193 String primaryKey = " PRIMARY KEY ";
194 String notNull = " NOT NULL ";
195
196
197 String action = createTableH2 + DbDataModel.table + "(";
198 DbDataModel.Columns[] ccolumns = DbDataModel.Columns
199 .values();
200 for (int i = 0; i < ccolumns.length - 1; i ++) {
201 action += ccolumns[i].name() +
202 DBType.getType(DbDataModel.dbTypes[i]) + notNull +
203 ", ";
204 }
205 action += ccolumns[ccolumns.length - 1].name() +
206 DBType.getType(DbDataModel.dbTypes[ccolumns.length - 1]) +
207 primaryKey + ")";
208 logger.warn(action);
209 DbRequest request = new DbRequest(session);
210 try {
211 request.query(action);
212 } catch (GoldenGateDatabaseNoConnectionException e) {
213 logger.warn("CreateTables Error", e);
214 return;
215 } catch (GoldenGateDatabaseSqlException e) {
216 logger.warn("CreateTables Error", e);
217 return;
218 } finally {
219 request.close();
220 }
221
222
223 action = "CREATE INDEX IF NOT EXISTS IDX_RUNNER ON "+ DbDataModel.table + "(";
224 DbDataModel.Columns[] icolumns = DbDataModel.indexes;
225 for (int i = 0; i < icolumns.length-1; i ++) {
226 action += icolumns[i].name()+ ", ";
227 }
228 action += icolumns[icolumns.length-1].name()+ ")";
229 logger.warn(action);
230 try {
231 request.query(action);
232 } catch (GoldenGateDatabaseNoConnectionException e) {
233 logger.warn("CreateTables Error", e);
234 return;
235 } catch (GoldenGateDatabaseSqlException e) {
236 return;
237 } finally {
238 request.close();
239 }
240
241
242 action = "CREATE SEQUENCE IF NOT EXISTS " + DbDataModel.fieldseq +
243 " START WITH " + (DbConstant.ILLEGALVALUE + 1);
244 logger.warn(action);
245 try {
246 request.query(action);
247 } catch (GoldenGateDatabaseNoConnectionException e) {
248 logger.warn("CreateTables Error", e);
249 return;
250 } catch (GoldenGateDatabaseSqlException e) {
251 logger.warn("CreateTables Error", e);
252 return;
253 } finally {
254 request.close();
255 }
256 }
257
258
259
260
261
262
263 @Override
264 public void resetSequence(DbSession session, long newvalue) throws GoldenGateDatabaseNoConnectionException {
265 String action = "ALTER SEQUENCE " + DbDataModel.fieldseq +
266 " RESTART WITH " + newvalue;
267 DbRequest request = new DbRequest(session);
268 try {
269 request.query(action);
270 } catch (GoldenGateDatabaseNoConnectionException e) {
271 logger.warn("ResetSequences Error", e);
272 return;
273 } catch (GoldenGateDatabaseSqlException e) {
274 logger.warn("ResetSequences Error", e);
275 return;
276 } finally {
277 request.close();
278 }
279 logger.warn(action);
280 }
281
282
283
284
285
286
287 @Override
288 public long nextSequence(DbSession dbSession)
289 throws GoldenGateDatabaseNoConnectionException,
290 GoldenGateDatabaseSqlException, GoldenGateDatabaseNoDataException {
291 long result = DbConstant.ILLEGALVALUE;
292 String action = "SELECT NEXTVAL('" + DbDataModel.fieldseq + "')";
293 DbPreparedStatement preparedStatement = new DbPreparedStatement(
294 dbSession);
295 try {
296 preparedStatement.createPrepareStatement(action);
297
298 preparedStatement.executeQuery();
299 if (preparedStatement.getNext()) {
300 try {
301 result = preparedStatement.getResultSet().getLong(1);
302 } catch (SQLException e) {
303 throw new GoldenGateDatabaseSqlException(e);
304 }
305 return result;
306 } else {
307 throw new GoldenGateDatabaseNoDataException(
308 "No sequence found. Must be initialized first");
309 }
310 } finally {
311 preparedStatement.realClose();
312 }
313 }
314
315
316
317
318 @Override
319 protected String validConnectionString() {
320 return "select 1";
321 }
322
323 @Override
324 public String limitRequest(String allfields, String request, int nb) {
325 return request+" LIMIT "+nb;
326 }
327
328 }