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.model;
21
22 import oracle.jdbc.driver.OracleDriver;
23 import oracle.jdbc.pool.OracleConnectionPoolDataSource;
24 import org.waarp.common.database.DbAdmin;
25 import org.waarp.common.database.DbConnectionPool;
26 import org.waarp.common.database.DbConstant;
27 import org.waarp.common.database.DbPreparedStatement;
28 import org.waarp.common.database.DbRequest;
29 import org.waarp.common.database.DbSession;
30 import org.waarp.common.database.data.DbDataModel;
31 import org.waarp.common.database.exception.WaarpDatabaseNoConnectionException;
32 import org.waarp.common.database.exception.WaarpDatabaseNoDataException;
33 import org.waarp.common.database.exception.WaarpDatabaseSqlException;
34 import org.waarp.common.logging.WaarpLogger;
35 import org.waarp.common.logging.WaarpLoggerFactory;
36
37 import java.sql.Connection;
38 import java.sql.DriverManager;
39 import java.sql.SQLException;
40 import java.sql.Types;
41 import java.util.Timer;
42
43
44
45
46 public abstract class DbModelOracle extends DbModelAbstract {
47
48
49
50 private static final WaarpLogger logger =
51 WaarpLoggerFactory.getLogger(DbModelOracle.class);
52
53 private static final DbType type = DbType.Oracle;
54
55 protected static class DbTypeResolverOracle
56 extends DbModelAbstract.DbTypeResolver {
57
58 @Override
59 public final String getType(final int sqlType) {
60 return DBType.getType(sqlType);
61 }
62
63 @Override
64 public final DbType getDbType() {
65 return type;
66 }
67 }
68
69 static {
70 dbTypeResolver = new DbTypeResolverOracle();
71 }
72
73 protected OracleConnectionPoolDataSource oracleConnectionPoolDataSource;
74 protected DbConnectionPool pool;
75
76 @Override
77 public final DbType getDbType() {
78 return type;
79 }
80
81
82
83
84
85
86
87
88
89
90
91
92 protected DbModelOracle(final String dbserver, final String dbuser,
93 final String dbpasswd, final Timer timer,
94 final long delay)
95 throws WaarpDatabaseNoConnectionException {
96 this();
97
98 try {
99 oracleConnectionPoolDataSource = new OracleConnectionPoolDataSource();
100 } catch (final SQLException e) {
101
102 oracleConnectionPoolDataSource = null;
103 return;
104 }
105 oracleConnectionPoolDataSource.setURL(dbserver);
106 oracleConnectionPoolDataSource.setUser(dbuser);
107 oracleConnectionPoolDataSource.setPassword(dbpasswd);
108 pool = new DbConnectionPool(oracleConnectionPoolDataSource, timer, delay);
109 logger.info("Some info: MaxConn: {} LogTimeout: {} ForceClose: {}",
110 pool.getMaxConnections(), pool.getLoginTimeout(),
111 pool.getTimeoutForceClose());
112 }
113
114
115
116
117
118
119
120
121
122
123 protected DbModelOracle(final String dbserver, final String dbuser,
124 final String dbpasswd)
125 throws WaarpDatabaseNoConnectionException {
126 this();
127
128 try {
129 oracleConnectionPoolDataSource = new OracleConnectionPoolDataSource();
130 } catch (final SQLException e) {
131
132 oracleConnectionPoolDataSource = null;
133 return;
134 }
135 oracleConnectionPoolDataSource.setURL(dbserver);
136 oracleConnectionPoolDataSource.setUser(dbuser);
137 oracleConnectionPoolDataSource.setPassword(dbpasswd);
138 pool = new DbConnectionPool(oracleConnectionPoolDataSource);
139 logger.warn(
140 "Some info: MaxConn: " + pool.getMaxConnections() + " LogTimeout: " +
141 pool.getLoginTimeout() + " ForceClose: " + pool.getTimeoutForceClose());
142 }
143
144
145
146
147
148
149 protected DbModelOracle() throws WaarpDatabaseNoConnectionException {
150 if (DbModelFactory.classLoaded.contains(type.name())) {
151 return;
152 }
153 try {
154 DriverManager.registerDriver(new OracleDriver());
155 DbModelFactory.classLoaded.add(type.name());
156 } catch (final SQLException e) {
157
158 logger.error(
159 "Cannot register Driver " + type.name() + ' ' + e.getMessage());
160 DbConstant.error(e);
161 throw new WaarpDatabaseNoConnectionException(
162 "Cannot load database drive:" + type.name(), e);
163 }
164 }
165
166 @Override
167 public final void releaseResources() {
168 if (pool != null) {
169 try {
170 pool.dispose();
171 } catch (final SQLException ignored) {
172
173 }
174 }
175 pool = null;
176 }
177
178 @Override
179 public final int currentNumberOfPooledConnections() {
180 if (pool != null) {
181 return pool.getActiveConnections();
182 }
183 return DbAdmin.getNbConnection();
184 }
185
186 @Override
187 public final Connection getDbConnection(final String server,
188 final String user,
189 final String passwd)
190 throws SQLException {
191 if (pool == null) {
192 return super.getDbConnection(server, user, passwd);
193 }
194 synchronized (this) {
195 try {
196 return pool.getConnection();
197 } catch (final SQLException e) {
198
199 oracleConnectionPoolDataSource = new OracleConnectionPoolDataSource();
200 oracleConnectionPoolDataSource.setURL(server);
201 oracleConnectionPoolDataSource.setUser(user);
202 oracleConnectionPoolDataSource.setPassword(passwd);
203 pool.resetPoolDataSource(oracleConnectionPoolDataSource);
204 try {
205 return pool.getConnection();
206 } catch (final SQLException e2) {
207 pool.dispose();
208 pool = null;
209 return super.getDbConnection(server, user, passwd);
210 }
211 }
212 }
213 }
214
215 protected enum DBType {
216 CHAR(Types.CHAR, " CHAR(3) "), VARCHAR(Types.VARCHAR, " VARCHAR2(4000) "),
217 NVARCHAR(Types.NVARCHAR, " VARCHAR2(" + MAX_KEY_VARCHAR + ") "),
218 LONGVARCHAR(Types.LONGVARCHAR, " VARCHAR2(4000) "),
219 BIT(Types.BIT, " CHAR(1) "), TINYINT(Types.TINYINT, " SMALLINT "),
220 SMALLINT(Types.SMALLINT, " SMALLINT "), INTEGER(Types.INTEGER, " INTEGER "),
221 BIGINT(Types.BIGINT, " NUMBER(38,0) "), REAL(Types.REAL, " REAL "),
222 DOUBLE(Types.DOUBLE, " DOUBLE PRECISION "),
223 VARBINARY(Types.VARBINARY, " RAW(" + (MAX_BINARY * 2) + ") "),
224 DATE(Types.DATE, " DATE "), TIMESTAMP(Types.TIMESTAMP, " TIMESTAMP(3) ");
225
226 public final int type;
227
228 public final String constructor;
229
230 DBType(final int type, final String constructor) {
231 this.type = type;
232 this.constructor = constructor;
233 }
234
235 public static String getType(final int sqltype) {
236 switch (sqltype) {
237 case Types.CHAR:
238 return CHAR.constructor;
239 case Types.VARCHAR:
240 return VARCHAR.constructor;
241 case Types.NVARCHAR:
242 return NVARCHAR.constructor;
243 case Types.LONGVARCHAR:
244 return LONGVARCHAR.constructor;
245 case Types.BIT:
246 return BIT.constructor;
247 case Types.TINYINT:
248 return TINYINT.constructor;
249 case Types.SMALLINT:
250 return SMALLINT.constructor;
251 case Types.INTEGER:
252 return INTEGER.constructor;
253 case Types.BIGINT:
254 return BIGINT.constructor;
255 case Types.REAL:
256 return REAL.constructor;
257 case Types.DOUBLE:
258 return DOUBLE.constructor;
259 case Types.VARBINARY:
260 return VARBINARY.constructor;
261 case Types.DATE:
262 return DATE.constructor;
263 case Types.TIMESTAMP:
264 return TIMESTAMP.constructor;
265 default:
266 return null;
267 }
268 }
269 }
270
271 @Override
272 public void resetSequence(final DbSession session, final long newvalue)
273 throws WaarpDatabaseNoConnectionException {
274 final String action = "DROP SEQUENCE " + DbDataModel.fieldseq;
275 final String action2 =
276 "CREATE SEQUENCE " + DbDataModel.fieldseq + " MINVALUE " +
277 (DbConstant.ILLEGALVALUE + 1) + " START WITH " + newvalue;
278 final DbRequest request = new DbRequest(session);
279 try {
280 request.query(action);
281 request.query(action2);
282 } catch (final WaarpDatabaseNoConnectionException e) {
283 logger.warn("ResetSequence Error", e);
284 } catch (final WaarpDatabaseSqlException e) {
285 logger.warn("ResetSequence Error", e);
286 } finally {
287 request.close();
288 }
289
290 logger.warn(action);
291 }
292
293 @Override
294 public long nextSequence(final DbSession dbSession)
295 throws WaarpDatabaseNoConnectionException, WaarpDatabaseSqlException,
296 WaarpDatabaseNoDataException {
297 long result;
298 final String action =
299 "SELECT " + DbDataModel.fieldseq + ".NEXTVAL FROM DUAL";
300 final DbPreparedStatement preparedStatement =
301 new DbPreparedStatement(dbSession);
302 try {
303 preparedStatement.createPrepareStatement(action);
304
305 preparedStatement.executeQuery();
306 if (preparedStatement.getNext()) {
307 try {
308 result = preparedStatement.getResultSet().getLong(1);
309 } catch (final SQLException e) {
310 throw new WaarpDatabaseSqlException(e);
311 }
312 return result;
313 } else {
314 throw new WaarpDatabaseNoDataException(
315 "No sequence found. Must be initialized first");
316 }
317 } finally {
318 preparedStatement.realClose();
319 }
320 }
321
322 @Override
323 protected final String validConnectionString() {
324 return "select 1 from dual";
325 }
326
327 @Override
328 public final String limitRequest(final String allfields, final String request,
329 final int nb) {
330 if (nb == 0) {
331 return request;
332 }
333 return "select " + allfields + " from ( " + request +
334 " ) where rownum <= " + nb;
335 }
336 }