API Overview API Index Package Overview Direct link to this page
JavaOnTracks 0.1.2
  net.jot.db. JOTDBManager View Javadoc
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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497

/**
------------------------------------
JavaOnTracks          Thibaut Colar
tcolar-jot AT colar DOT net
Artistic Licence 2.0
http://www.javaontracks.net
------------------------------------
 */
package net.jot.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;

import net.jot.logger.JOTLogger;
import net.jot.persistance.JOTStatementFlags;

/**
 * Main database manager object. Gives simple access to the loaded pooled databases.
 *@author     tcolar
 *@created    October 15, 2001
 */
public class JOTDBManager {
	// singleton

	private final static JOTDBManager dbManager = new JOTDBManager();
	private Hashtable dbs = new Hashtable();
	private Vector existingTables = new Vector();

	/**
	 *
	 *@return    The instance value
	 */
	public static final JOTDBManager getInstance()
	{
		return dbManager;
	}

	private JOTDBManager()
	{
	}

	/**
	 * returns a connection to the 'default' database
	 * @return
	 * @throws java.lang.Exception
	 */
	public JOTTaggedConnection getConnection() throws Exception
	{
		return getConnection("default");
	}

	/**
	 * Gets a connection to a specified/named db.
	 *
	 *@param  dbName         Description of Parameter
	 *@return                The connection value
	 *@exception  Exception  Description of Exception
	 */
	public JOTTaggedConnection getConnection(String dbName) throws Exception
	{
		JOTTaggedConnection con = null;
		try
		{
			JOTLogger.log(JOTLogger.CAT_DB, JOTLogger.DEBUG_LEVEL, this, "Trying to connect to database: " + dbName);

			JOTDBPool pool = (JOTDBPool) dbs.get(dbName);
			if (pool != null)
			{
				con = pool.retrieveConnection();
			} else
			{
				throw new Exception("No pool found for database: " + dbName + " !!!");
			}
			if (con == null)
			{
				throw new Exception("Connection is null");
			}
		} catch (Exception e)
		{
			JOTLogger.logException(JOTLogger.CAT_DB, JOTLogger.ERROR_LEVEL, this, "Could not get connection  to database: " + dbName, e);
			throw (e);
		}
		JOTLogger.log(JOTLogger.CAT_DB, JOTLogger.DEBUG_LEVEL, this, "Got Connection " + dbName + "/" + con.getId());

		return con;
	}

	/**
	 * Release a connection to the DB pool
	 *
	 *@param  con     Description of Parameter
	 */
	public void releaseConnection(JOTTaggedConnection con)
	{
		String name = con.getPoolName();
		JOTDBPool pool = (JOTDBPool) dbs.get(name);

		if (pool != null)
		{
			pool.releaseConnection(con);
		}
		JOTLogger.log(JOTLogger.CAT_DB, JOTLogger.DEBUG_LEVEL, this, "Released Connection " + name + "/" + con.getId());
	}

	/**
	 * Load a database configuration from a DBSetup object
	 *
	 *@param  setup  Description of Parameter
	 */
	public void loadDb(String name, JOTDBJDBCSetup setup) throws Exception
	{
		// use the dbName as the pool identifier
		if (dbs.contains(name))
		{
			JOTLogger.log(JOTLogger.CAT_DB, JOTLogger.DEBUG_LEVEL, this, "Flushing pool of " + name + "to load new db");
			JOTDBPool pool = (JOTDBPool) dbs.get(name);
			pool.dropAll();
			pool.shutdown();
			dbs.remove(name);
		} else
		{
			// load the driver
			try
			{
				if (Class.forName(setup.getDriver()) == null)
				{
					throw new Exception("Failed to get driver class: null");
				}
				JOTLogger.log(JOTLogger.CAT_DB, JOTLogger.INFO_LEVEL, this, "Adding db in pool: " + name);
				dbs.put(name, new JOTDBPool(name, setup));
				JOTLogger.log(JOTLogger.CAT_DB, JOTLogger.INFO_LEVEL, this, "Loaded the db driver succesfully: " + setup.getDriver());
				if (!tableExists(name, "JOTCOUNTERS", false))
				{
					JOTTaggedConnection con = getConnection(name);
					try
					{
						JOTLogger.log(JOTLogger.CAT_DB, JOTLogger.INFO_LEVEL, this, "Creating jotcounters table.");
						update(con, "CREATE TABLE JOTCOUNTERS(name varchar(40) NOT NULL, val varchar(10))");
						update(con, "ALTER TABLE JOTCOUNTERS ADD PRIMARY KEY (name)");
					} catch (SQLException e)
					{
						JOTLogger.logException(JOTLogger.CAT_DB, JOTLogger.INFO_LEVEL, "JOTDBModel", "Error creating jotcounters", e);
						throw (new Exception("Error creating jotcounters", e));
					} finally
					{
						releaseConnection(con);
					}

				}

			} catch (Exception e)
			{
				JOTLogger.logException(JOTLogger.CAT_DB, JOTLogger.ERROR_LEVEL, this, "Loading the db failed: " + setup.getDriver(), e);
				throw (new Exception("Loading the driver failed: " + setup.getDriver(), e));
			}
		}
	}

	/**
	 * Quick DB query, without having to open/close the connection manually.
	 * Opens a connection, makes the querry and then close it.
	 * It can look like a waste to open and close the connection
	 * for each query, but since the connection are pooled they are in fact
	 * not open  /closed but retrieved/released from the pool.
	 *
	 * The query is gonna be something like "get * from toto where id=?"
	 * Parameters: [5]
	 *
	 *@param  query          Description of Parameter
	 *@param  params         Description of Parameter
	 *@param  con            Description of Parameter
	 *@return                Description of the Returned Value
	 *@exception  Exception  Description of Exception
	 */
	public ResultSet query(JOTTaggedConnection con, String query, Object[] params, JOTStatementFlags flags) throws
			Exception
	{
		return execute(con, query, params, false, flags);
	}

	/**
	 * It's is best/much safer to use query(con,query,params) as the parameters will be safely formatted for you (for example quotes in parameter values issues etc...)
	 *
	 * The query is gonna be something like "get * from toto where id=5"
	 *
	 *@param  query          Description of Parameter
	 *@param  con            Description of Parameter
	 *@return                Description of the Returned Value
	 *@exception  Exception  Description of Exception
	 */
	public ResultSet query(JOTTaggedConnection con, String query, JOTStatementFlags flags) throws
			Exception
	{
		return execute(con, query, false, flags);
	}

	/**
	 * Similar to query(con,query,params) but for the sql "update"
	 * Update is NOT used to make an "SQL update" (confusing name :-)
	 * But usually used to make a query which no result are expected such as an
	 * SQL "insert" command.
	 * Using query() for such a query might result in a backendexception/no results.
	 *
	 *@param  query          Description of Parameter
	 *@param  params         Description of Parameter
	 *@param  con            Description of Parameter
	 *@return                Description of the Returned Value
	 *@exception  Exception  Description of Exception
	 */
	public ResultSet update(JOTTaggedConnection con, String query, Object[] params, JOTStatementFlags flags) throws
			Exception
	{
		return execute(con, query, params, true, flags);
	}

	public ResultSet update(JOTTaggedConnection con, String query, Object[] params) throws
			Exception
	{
		return execute(con, query, params, true, null);
	}

	/**
	 * It's is best/much safer  to use update(con,query,params) as the parameters will be safely formatted for you (for example quotes in parameter values issues etc...)
	 *
	 *@param  query          Description of Parameter
	 *@param  con            Description of Parameter
	 *@return                Description of the Returned Value
	 *@exception  Exception  Description of Exception
	 */
	public ResultSet update(JOTTaggedConnection con, String query, JOTStatementFlags flags) throws
			Exception
	{
		return execute(con, query, true, flags);
	}

	public ResultSet update(JOTTaggedConnection con, String query) throws Exception
	{
		return execute(con, query, true, null);
	}

	/**
	 * This is the equivalent of the nextval function on many database (ex:postgresql sequence)
	 * This is use to have a safe incremental counter(usually a primary key / ID).
	 * It's value is read and then incremented to the next value (for creating unique identifiers).
	 * This function is synchronized/atomic, since it should execute at once to ensure the same value can't be read twice.
	 *
	 *@param  dataId             Description of Parameter
	 *@param  con            Description of Parameter
	 *@return                Description of the Returned Value
	 *@exception  Exception  Description of Exception
	 */
	public synchronized int nextVal(JOTTaggedConnection con, String id) throws Exception
	{
		// if we are not in autocommit(alreday in a trasaction) then leave alone.
		boolean wasAutoCommit = con.getConnection().getAutoCommit();
		// doing this in a transaction and synchronized should be safe
		if (wasAutoCommit)
		{
			con.getConnection().setAutoCommit(false);
			
		}
		JOTLogger.log(JOTLogger.CAT_DB, JOTLogger.DEBUG_LEVEL, this, "Getting nextval of : " + id);
		int curval = 0;
		try
		{
			String[] params =
			{
				id
			};
			ResultSet rs = query(con, "select * from JOTCOUNTERS where name=?", params, null);
			if (rs.next())
			{
				curval = rs.getInt("val");
				String nextval = "" + (curval + 1);
				String[] params2 =
				{
					nextval, id
				};
				update(con, "update JOTCOUNTERS set val=? where name=?", params2, null);
			} else
			{
				//new counter, creating it
				curval = 1;
				String[] params3 =
				{
					"2", id
				};
				update(con, "insert into JOTCOUNTERS (val, name) values(?,?)", params3, null);
			}
		} catch (Exception e)
		{
			throw (e);
		} finally
		{
			if (wasAutoCommit)
			{
				con.getConnection().commit();
				con.getConnection().setAutoCommit(true);
			}
		}
		return curval;
	}

	/**
	 *  disconnects all the open DB's and release resources
	 */
	public void shutdown()
	{
		// for all open dbs
		for (Enumeration e = dbs.keys(); e.hasMoreElements();)
		{

			JOTDBPool pool = (JOTDBPool) dbs.get(e.nextElement());
			// stopping the running thread
			pool.shutdown();
		}
	}

	/**
	 * Does the query job itself.
	 * Uses a preparedStement becasue it's safer
	 * (takes care of special charcaters like quotes and such)
	 *
	 *@param  update         Description of Parameter
	 *@param  squeleton      Description of Parameter
	 *@param  params         Description of Parameter
	 *@param  con            Description of Parameter
	 *@return                Description of the Returned Value
	 *@exception  Exception  Description of Exception
	 */
	private ResultSet execute(JOTTaggedConnection con, String squeleton, Object[] params, boolean update, JOTStatementFlags flags) throws Exception
	{

		// empty args list causes an sql exception
		if (params == null || params.length == 0)
		{
			return execute(con, squeleton, update, flags);
		}

		JOTLogger.log(JOTLogger.CAT_DB, JOTLogger.DEBUG_LEVEL, this, "Executing query: " + squeleton);
		if (params != null && params.length > 0)
		{

			String str = "";
			for (int i = 0; i != params.length; i++)
			{
				str += (params[i] == null ? "!!NULL!!" : params[i].toString()) + ", ";
			}
			JOTLogger.log(JOTLogger.CAT_DB, JOTLogger.TRACE_LEVEL, this, "With Parameters: " + str);
		}
		updateAccessTime(con);
		ResultSet rs = null;
		PreparedStatement st = con.getConnection().prepareStatement(squeleton);
		if (flags != null)
		{
			if (flags.getMaxRows() != -1)
			{
				st.setMaxRows(flags.getMaxRows());
			}
		}
		if (params != null)
		{
			for (int i = 0; i != params.length; i++)
			{
				st.setObject(i + 1, params[i]);
			}
		}
		if (update)
		{
			st.executeUpdate();
		} else
		{
			rs = st.executeQuery();
		}

		return rs;
	}

	/**
	 * Esay way to do the query, however this is to use only on direct
	 * querries
	 * Querries constructed from user inout shouldn't use this method
	 * It is not safe (can be hacked), does not use preparedStatement
	 *
	 *@param  query          Description of Parameter
	 *@param  update         Description of Parameter
	 *@param  con            Description of Parameter
	 *@return                Description of the Returned Value
	 *@exception  Exception  Description of Exception
	 */
	private ResultSet execute(JOTTaggedConnection con, String query, boolean update, JOTStatementFlags flags) throws Exception
	{
		JOTLogger.log(JOTLogger.CAT_DB, JOTLogger.DEBUG_LEVEL, this, "Executing query: " + query);

		updateAccessTime(con);
		ResultSet rs = null;

		Connection stdCon = con.getConnection();
		if (stdCon == null)
		{
			throw (new Exception("Failed to get a connection !"));
		}
		Statement st = stdCon.createStatement();
		if (flags != null)
		{
			if (flags.getMaxRows() != -1)
			{
				st.setMaxRows(flags.getMaxRows());
			}
		}

		if (update)
		{
			st.executeUpdate(query);
		} else
		{
			rs = st.executeQuery(query);
		}

		return rs;
	}

	/**
	 *  Updates the last time a connectin was used, information used by the DBPool Manager
	 *
	 *@param  con  Description of the Parameter
	 */
	private void updateAccessTime(JOTTaggedConnection con)
	{
		JOTDBPool pool = (JOTDBPool) dbs.get(con.getPoolName());
		pool.updateAccessTime(con.getId());

	}

	/**
	 * Checks wether a table already exists or not in a DB
	 * unless forceCheck, if the table is found we wont check again
	 * @param storageName
	 * @param table
	 * @return
	 * @throws java.lang.Exception
	 */
	public boolean tableExists(String storageName, String table, boolean forceCheck) throws Exception
	{
		String key = storageName + "::" + table;
		if (!forceCheck && existingTables.contains(key))
		{
			return true;
			// New code, hopefully that works on all DB's
			
		}
		JOTTaggedConnection con = getInstance().getConnection(storageName);
		try
		{
			ResultSet rs = con.getConnection().getMetaData().getTables(null, null, table, null);
			if (rs.next())
			{
				existingTables.add(key);
				return true;
			}
		} catch (Exception e)
		{
			JOTLogger.logException(this, "Table existance check error: ", e);
		} finally
		{
			// make sure we releas the con properly.
			getInstance().releaseConnection(con);
		}
		return false;

		/* Old code - Was kinda ugly to catch an exception
		boolean result=false;
		try
		{
		JOTDBManager.getInstance().query(con, "SELECT COUNT(0) from " + table, null);
		} catch (Exception e)
		{
		result = false;
		} finally
		{
		getInstance().releaseConnection(con);
		}
		return result;*/
	}

	public void unCacheExitingTable(String dBName, String tableName)
	{
		String key = dBName + "::" + tableName;
		existingTables.remove(dbs);
	}
}

Generated By: JavaOnTracks Doclet 0.1.5     ©Thibaut Colar