2
* Copyright (c) 2004-2008, University of Oslo
5
* Redistribution and use in source and binary forms, with or without
6
* modification, are permitted provided that the following conditions are met:
7
* * Redistributions of source code must retain the above copyright notice, this
8
* list of conditions and the following disclaimer.
9
* * Redistributions in binary form must reproduce the above copyright notice,
10
* this list of conditions and the following disclaimer in the documentation
11
* and/or other materials provided with the distribution.
12
* * Neither the name of the HISP project nor the names of its contributors may
13
* be used to endorse or promote products derived from this software without
14
* specific prior written permission.
16
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
17
* ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18
* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19
* DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
20
* ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
23
* ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
27
package org.hisp.dhis.integration.rims.util;
29
import java.sql.Connection;
30
import java.sql.PreparedStatement;
31
import java.sql.ResultSet;
32
import java.sql.ResultSetMetaData;
33
import java.sql.SQLException;
34
import java.sql.Statement;
35
import java.util.HashMap;
38
import org.hisp.dhis.integration.rims.api.RIMSTable;
39
import org.hisp.dhis.integration.rims.api.RIMS_Mapping_DataElement;
40
import org.hisp.dhis.integration.rims.api.tables.RIMSDistrict;
41
import org.hisp.dhis.integration.rims.api.tables.RIMSOrgUnit;
42
import org.hisp.dhis.integration.rims.api.tables.RIMS_PHC;
45
* Imported entries are cached, so take care with reusing instances of this class.
46
* @author Leif Arne Storset
49
public abstract class RIMSTableAdapter
52
private Configuration config;
53
private Connection conn;
56
* Get the current connection instance, creating one if necessary.
59
* @throws SQLException if the connection could not be created.
61
private Connection getConnection() throws SQLException
65
conn = config.getConnection();
66
// Hopefully this will prevent SQLExceptions such as
67
// "Could not update; currently locked by user 'admin'"
68
conn.setTransactionIsolation( Connection.TRANSACTION_NONE );
73
public void setConnection( Connection conn )
78
public boolean isData( RIMSOrgUnit orgUnit, int month, int year,
79
RIMS_Mapping_DataElement mappingDataElement )
82
if ( orgUnit instanceof RIMS_PHC )
84
return isData( (RIMS_PHC) orgUnit, month, year, mappingDataElement );
86
else if ( orgUnit instanceof RIMSDistrict )
88
return isData( (RIMSDistrict) orgUnit, month, year, mappingDataElement );
92
throw new IllegalArgumentException ( "orgUnit must be one of" +
93
" RIMS_PHC and RIMSDistrict" );
97
public abstract boolean isData( RIMS_PHC phc, int month, int year,
98
RIMS_Mapping_DataElement mappingDataElement ) throws SQLException;
100
public abstract boolean isData( RIMSDistrict district, int month, int year,
101
RIMS_Mapping_DataElement mappingDataElement ) throws SQLException;
103
public int insertData( RIMSOrgUnit orgUnit, int month, int year,
104
RIMS_Mapping_DataElement mappingDataElement, String value )
107
if ( orgUnit instanceof RIMS_PHC )
109
return insertData( (RIMS_PHC) orgUnit, month, year, mappingDataElement, value );
111
else if ( orgUnit instanceof RIMSDistrict )
113
return insertData( (RIMSDistrict) orgUnit, month, year, mappingDataElement, value );
117
throw new IllegalArgumentException ( "orgUnit must be one of" +
118
" RIMS_PHC and RIMSDistrict" );
122
public abstract int insertData( RIMS_PHC phc, int month, int year,
123
RIMS_Mapping_DataElement mappingDataElement, String value ) throws SQLException;
125
public abstract int insertData( RIMSDistrict district, int month, int year,
126
RIMS_Mapping_DataElement mappingDataElement, String value ) throws SQLException;
128
public int updateData( RIMSOrgUnit orgUnit, int month, int year,
129
RIMS_Mapping_DataElement mappingDataElement, String value )
132
if ( orgUnit instanceof RIMS_PHC )
134
return updateData( (RIMS_PHC) orgUnit, month, year, mappingDataElement, value );
136
else if ( orgUnit instanceof RIMSDistrict )
138
return updateData( (RIMSDistrict) orgUnit, month, year, mappingDataElement, value );
142
throw new IllegalArgumentException ( "orgUnit must be one of" +
143
" RIMS_PHC and RIMSDistrict" );
147
public abstract int updateData( RIMS_PHC phc, int month, int year,
148
RIMS_Mapping_DataElement mappingDataElement, String value ) throws SQLException;
150
public abstract int updateData( RIMSDistrict district, int month, int year,
151
RIMS_Mapping_DataElement mappingDataElement, String value ) throws SQLException;
154
protected int executeUpdate( String query ) throws SQLException
156
return executeUpdate( query, null );
159
public String getDataValue( RIMS_Mapping_DataElement mappingDataElement, RIMSOrgUnit orgUnit, int month, int year ) throws SQLException
161
if ( orgUnit instanceof RIMS_PHC )
163
return getDataValue( mappingDataElement, (RIMS_PHC) orgUnit, month, year );
165
else if ( orgUnit instanceof RIMSDistrict )
167
return getDataValue( mappingDataElement, (RIMSDistrict) orgUnit, month, year );
171
throw new IllegalArgumentException ( "orgUnit must be one of" +
172
" RIMS_PHC and RIMSDistrict" );
176
public abstract String getDataValue( RIMS_Mapping_DataElement mappingDataElement, RIMS_PHC orgUnit, int month, int year ) throws SQLException;
177
public abstract String getDataValue( RIMS_Mapping_DataElement mappingDataElement, RIMSDistrict orgUnit, int month, int year ) throws SQLException;
181
* Checks if there is existing data using the provided query.
185
* @throws SQLException
187
protected boolean existingData( String query ) throws SQLException
189
return existingData( query, null );
193
* Checks if there is existing data using the provided query.
198
* @throws SQLException
200
protected boolean existingData( String query, Object[] params ) throws SQLException
202
PreparedStatement st = null;
208
st = getConnection().prepareStatement( query );
210
if ( params != null )
212
for ( int i = 0; i < params.length; i++ )
214
st.setObject( i + 1, params[i] );
218
rs = st.executeQuery();
234
public void finalize()
239
if ( conn != null && !conn.isClosed() )
244
catch ( SQLException e )
246
// No sense in rethrowing since it is ignored anyhow
251
@SuppressWarnings("unused")
252
private Cache cache = new Cache();
254
private class Cache {
256
* {@code timeout} seconds after the last cache put, getting the cache
257
* will automatically clear it.
259
private int timeout = 60;
260
private long lastPut;
269
cache = new HashMap<String, Object[]>();
273
@SuppressWarnings("hiding")
274
private HashMap<String, Object[]> cache;
275
private String[] columns;
277
private String cacheKey( String query, Object[] params )
279
StringBuffer sb = new StringBuffer();
280
sb.append( query ).append( '\n' );
281
for( Object param: params )
283
sb.append( param.toString() ).append( '\n' );
285
return sb.toString();
289
* Inserts into the cache. Returns a {@link Map} as if
290
* {@link #getFromCache(String, Object[])} had been called.
296
@SuppressWarnings("unused")
297
public Map<String, String> putInCache( String query, Object[] params, ResultSet resultSet )
299
lastPut = System.currentTimeMillis();
303
if ( columns == null )
305
ResultSetMetaData meta = resultSet.getMetaData();
306
columns = new String[ meta.getColumnCount() ];
307
for( int i = 0; i < columns.length; i++ )
309
columns[i] = meta.getColumnName( i + 1 );
313
row = new Object[ columns.length ];
314
for( int i = 0; i < row.length; i++ )
316
row[i] = resultSet.getObject( i + 1 );
317
if ( resultSet.wasNull() )
323
cache.put( cacheKey( query, params ), row );
325
catch ( SQLException e )
327
// TODO Auto-generated catch block
331
return createMap( row );
334
@SuppressWarnings("unused")
335
public Map<String, String> getFromCache( String query, Object[] params )
337
// Clear the cache if we've gone past timeout.
338
if ( System.currentTimeMillis() - lastPut > timeout * 1000 )
344
Object[] row = cache.get( cacheKey( query, params ) );
349
return createMap( row );
352
private Map<String, String> createMap( Object[] row )
354
Map<String, String> ret = new HashMap<String, String>( row.length );
355
for ( int i = 0; i < row.length; i++ )
357
ret.put( columns[i], row[i] + "" );
362
public int getTimeout()
367
public void setTimeout( int timeout )
369
this.timeout = timeout;
374
* Executes the given query with the given parameters.
376
* @param query the query to execute.
377
* @param getColumn the column to read.
378
* @param params the parameters to replace with.
379
* @return the value of the given column, or null if it is NULL or no rows
381
* @throws SQLException
383
protected String executeQuery( String query, String getColumn, Object[] params ) throws SQLException
385
PreparedStatement st = null;
386
ResultSet resultSet = null;
387
Object rimsValue = null;
390
Map<String, String> cachehit = cache.getFromCache( query, params );
391
if ( cachehit != null )
393
return cachehit.get( getColumn ) + "";
399
st = getConnection().prepareStatement( query );
401
for( int i = 0; i < params.length; i++ )
403
st.setObject( i + 1, params[i] );
406
resultSet = st.executeQuery();
407
if( !resultSet.next() )
411
rimsValue = resultSet.getObject( getColumn );
412
if (resultSet.wasNull())
417
//cache.putInCache( query, params, resultSet );
418
// Must cache first since the ResultSet only supports getting data once.
419
//rimsValue = cache.putInCache( query, params, resultSet ).get( "getColumn" );
423
if ( resultSet != null )
429
return rimsValue +"";
433
* Executes the given update with the given parameters.
435
* @param update the update to execute.
436
* @param params the parameters to replace with.
437
* @return the value of the given column, or null if it is NULL.
438
* @throws SQLException
440
protected int executeUpdate( String update, Object[] params ) throws SQLException
442
PreparedStatement st = null;
443
int recordCount = -1;
447
st = getConnection().prepareStatement( update );
449
if ( params != null )
451
for ( int i = 0; i < params.length; i++ )
453
st.setObject( i + 1, params[i] );
457
recordCount = st.executeUpdate();
469
public int truncate() throws SQLException
471
@SuppressWarnings("hiding")
472
Connection conn = getConnection();
473
Statement st = conn.createStatement();
476
rowCount = st.executeUpdate( "DELETE FROM "+ getTableName() );
489
public void commit() throws SQLException
494
conn.setAutoCommit( true );
498
public void beginTransaction() throws SQLException
502
conn.setAutoCommit( false );
506
public void setConfiguration( Configuration config )
508
this.config = config;