Java/Oracle: Call and use the database views

Hello,

In this first post on ‘database entity’, I would write some words concerning the call and use of the database views in java code. In this post, I will not present the details of view’s creation in database, for this point, see the official documentation of your database, for Oracle 10g CREATE VIEW: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8004.htm.

The main advantages of view are:

• Hide certain columns or certain rows, not useful,
• Hide certain columns or certain rows, for reasons of confidentiality,
• Break down a complex SQL query into a sequence of simple SQL queries,
• Hide the “joints” between tables, presenting data in “flat”,
• Renaming tables and columns in tables, with simpler names,
• Rename the tables and table columns, with names in the language of the user,
• Facilitate the documentation of queries, allowing to understand the different “intermediate steps”,
• The possibilities to change the conditions of a query on the fly, without modification of the persistence layer (SQL),
• Stop the SQL statements of several pages, replacing a “large” query, complex and unmanageable, with several requests “reasonable” understandable,

In general, a specific use of a database does not need to see all the data in the database, for reasons of confidentiality but also simply to avoid polluting the user with information that do not concern not. For example, if several applications uses the same database, and the same tables, a view could allow to filter the access to specific confidential columns (salary column in a employees table).

An important note concerning the MATERIALIZED view:
In management systems database relational type, a view is a virtual table representing the result of a query on the database. Unlike a standard view in a materialized view data is duplicated. It is used primarily for optimization and performance if the associated query is particularly complex or cumbersome, or to replication table.

The ‘freshness’ of data in the materialized view depends on the options selected when created. The discrepancy between the data from the master table and the materialized view can be zero (synchronous refresh) or a planned duration: hours, days, etc.. Depending on the context there are different types of materialized view available: on primary key, rowid, and more or less complex with aggregate functions, subqueries, joins, etc…

So, see the official documentation of your database, for Oracle 10g CREATE MATERIALIZED VIEW : http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm.

First, we will create a VIEW V_JAVABLOG_EXAMPLEVIEW1 in our database (Oracle 10g in my case), named MYDATABASE:

REM MYDATABASE V_JAVABLOG_EXAMPLEVIEW1

CREATE OR REPLACE FORCE VIEW "MYDATABASE"."V_JAVABLOG_EXAMPLEVIEW1" ("COLUMN11", "COLUMN22", "COLUMN33") AS 
(
	SELECT distinct TBL1.COLUMN1 as COLUMN11, TBL2.COLUMN2 as COLUMN22, TBL3.COLUMN3 as COLUMN33
	FROM 
		MYTABLE1 TBL1 
		,MYTABLE2 TBL2
		,MYTABLE3 TBL3
	WHERE
		TBL1.class = 'Filter1'
		AND TBL1.tbl2id = TBL2.id
		AND TBL2.tbl3id = TBL3.id
);

This view allows the reading in database and returning of 3 columns TBL1.COLUMN1, TBL2.COLUMN2 and TBL3.COLUMN3. The view must be created by the owner of database schema and accessible by the database user used by the application (appliative user).

CREATE ROLE r_myaplication_role;
GRANT r_myaplication_role TO MYDATABASE.MYDATABASE_APP;
GRANT r_myaplication_role TO MYDATABASE.MYDATABASE_ADMIN;
...
CREATE SYNONYM V_JAVABLOG_EXAMPLEVIEW1 FOR MYDATABASE.V_JAVABLOG_EXAMPLEVIEW1;
...
GRANT SELECT ON V_JAVABLOG_EXAMPLEVIEW1 TO r_myaplication_role;
...

Then, in the java code, (for example in the persistence layer using Hibernate ORM), we could have the below code to exploit the above Oracle view:

@SuppressWarnings("unchecked")
@Override
@Transactional(propagation=Propagation.SUPPORTS, readOnly=true)
public List<MyObject> getMyObjects() throws DataAccessException {

	List<MyObject> output = new ArrayList<MyObject>();

	String sqlQueryStr = "select distinct * from V_JAVABLOG_EXAMPLEVIEW1 order by COLUMN11 asc"; 

	Session currSession = getHibernateTemplate().getSessionFactory().openSession();
	try{
		currSession.beginTransaction();
			
		if (logger.isDebugEnabled()) {
			logger.debug("SQL executed : " + sqlQueryStr);
		} // end-if
			
		SQLQuery sqlQuery = currSession.createSQLQuery(sqlQueryStr);
		List<Object[]> sqlResults = sqlQuery.list();

		for (Object[] currResult : sqlResults) {
			MyObject currMyObject = convertResultToObject(currResult);
			output.add(currMyObject);
		} // end-for
	} catch (Exception e) {
		if(logger.isErrorEnabled()) {
			logger.error("An exception occured in the function getMyObjects", e);
		} // end-if
	} finally {
		currSession.close();
	} // end-try

	return output;
}

We need a simple method convertResultToObject to convert the result of view to a specific object:

private final MyObject convertResultToObject(Object[] currResult) {
	final int COLUMN_COLUMN11 = 0;
	final int COLUMN_COLUMN22 = 1;
	final int COLUMN_COLUMN33 = 2;

	MyObject currMyObject =null;
	if (null != currResult) {
		currMyObject = new MyObject();
			
		if (null != currResult[COLUMN_COLUMN11]) {
			int value11 = ((BigDecimal)currResult[COLUMN_COLUMN11]).intValue();
			currMyObject.setColumnValue11(value11);
		} // end-if

		if (null != currResult[COLUMN_COLUMN22]) {
			int value22 = ((BigDecimal)currResult[COLUMN_COLUMN22]).intValue();
			currMyObject.setColumnValue22(value22);
		} // end-if

		if (null != currResult[COLUMN_COLUMN133]) {
			String value33 = ((String)currResult[COLUMN_COLUMN133]);
			currMyObject.setColumnValue33(value33);
		} // end-if

		/*
		// Example of 1 char
		String strType = (String)currResult[...];
		if (null != strType && strType.length()>0) {
			char charType = strType.charAt(0);
			currMyObject.setColumn...(charType);
		} // end-if
		
		// Example of boolean
		if (null != currResult[...]) {
			boolean myValue = 1 == ((BigDecimal)currResult[....]).intValue();
			currMyObject.setColumn...(myValue);
		} // end-if
		
		// Example of datetime
		if (null != currResult[...]) {
			long timeStamp = ((Timestamp)currResult[...]).getTime();
			Calendar c = Calendar.getInstance();
			c.setTimeInMillis(timeStamp);
			currMyObject.setColumn...(c);
		} // end-if
		*/

	} //end-if

	return currMyObject;
}

A last paragraph concerning the use of SQL in java code, ít is similar than the previous code.

public interface GenericDao<T, PK extends Serializable> {
	PK create(T persistentObject);

	T get(PK id);

	List<T> getAll();

	void update(T persistentObject);
	
	void createOrUpdate(T persistentObject);

	void delete(T persistentObject);
}

@Transactional(propagation=Propagation.MANDATORY)
public class GenericDaoImpl<T, PK extends Serializable> extends HibernateDaoSupport implements GenericDao<T, PK> {
...
}

public interface MyDao extends GenericDao<MyObject, String> {
...
}

@Repository("myDao")
@Scope("singleton")
public class MyDaoHibernateImpl extends GenericDaoImpl<MyObject, String> implements MyDao {

....

}

So, the above class MyDaoHibernateImpl could contains several methods using HQL, SQL like:

Method using HQL:

...
@SuppressWarnings("unchecked")
@Override
@Transactional(propagation=Propagation.MANDATORY, readOnly=true)
public List<MyObject> findMyObject(String value1) throws DataAccessException {
	StringBuffer hql = new StringBuffer("select myObject from MyObject myObject ");
	hql.append(" where myObject.filter1=:filter1value");
	Query query = getSession().createQuery(hql.toString());
	//
	query.setString("filter1value", value1);
	List<MyObject> myObjects = query.list();
	return myObjects;
}
...

Methods using SQL with ‘prepareStatement’, ‘createSQLQuery’:

...
@SuppressWarnings("unchecked")
@Transactional(propagation=Propagation.MANDATORY, readOnly=true)
public List<String> findMyObjects(long secondsInterval) throws DataAccessException {
	List<String> output = new ArrayList<String>();
	Session currSession = getHibernateTemplate().getSessionFactory().openSession();
	try{
		currSession.beginTransaction();

		String sql = "select TBL1.COLUMN1 as COLUMN11 FROM MYTBL1TBL TBL1 WHERE TBL1.COLUMN1 is not null AND myDate < (sysdate - interval '"+secondsInterval+"' second)"; 

		SQLQuery sqlQuery = currSession.createSQLQuery(sql);
		List sqlResults = sqlQuery.list();
			
		// Extract the COLUMN11 
		List<Object[]> resultsAsArray = sqlResults;
		for (Object[] currResult : resultsAsArray) {
			output.add((String) currResult[0]);
		}

	}catch(RuntimeException th){
		logger.error(th);
		throw th;
	} finally {
		currSession.close();
	} // end-try
		
	return output;
}

...
@SuppressWarnings("unchecked")
@Override
@Transactional(propagation=Propagation.MANDATORY, readOnly=false)
public boolean unlockMyObject(String COLUMN1) throws DataAccessException {
	Session currSession = getHibernateTemplate().getSessionFactory().getCurrentSession();
	Transaction tx = currSession.beginTransaction();
		
	try{
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE MYTBL1TBL SET ")
			.append(" LOCKDATE=NULL, LOCK=0, LOCKBY=NULL ")
			.append(" WHERE COLUMN1 =?");
	
		PreparedStatement setLockOnMyObjectSTM = currSession.connection().prepareStatement(sql.toString());
		setLockOnMyObjectSTM.setString(1, COLUMN1 );
		setLockOnMyObjectSTM.addBatch();
		setLockOnMyObjectSTM.executeBatch();

	} catch (Exception e) {
		tx.rollback();
		return false;
	} // end-try

	tx.commit();
	return true;
}

...
@SuppressWarnings("unchecked")
@Transactional(propagation=Propagation.MANDATORY, readOnly=true)
private Object findByCriteria(String[] parameters, boolean count) throws DataAccessException {
	Object output = null;
	Session currSession = getHibernateTemplate().getSessionFactory().openSession();
	try{
		String sql = findByCriteriaGetQuery(parameters, count);
		
		currSession.beginTransaction();
			
		SQLQuery sqlQuery = currSession.createSQLQuery(sql);
		List sqlResults = sqlQuery.list();
			
		output = findByCriteriaBuildOutput(sqlResults, count);

	}catch(RuntimeException th){
		logger.error(th);
		throw th;
	} finally {
		currSession.close();
	} // end-try
		
	return output;
}

@SuppressWarnings("unchecked")
private Object findByCriteriaBuildOutput(List sqlResults, boolean count) {
	Object output = null;
	List<Object[]> resultsAsArray = sqlResults;

	if (!count) {
		ArrayList<MyObject> outputList = new ArrayList<MyObject>();
		output = outputList;
			
		for (Object[] currResult : resultsAsArray) {
			int i = 0;
			MyObject currMyObject = new MyObject();
			currMyObject.setId((String) currResult[i++]);
			//...

			outputList.add(currMyObject);
		} // end-for
	} else {
		BigDecimal bigD = ((BigDecimal)sqlResults.iterator().next();
		output = 0L;
		if(bigD != null){
			output = bigD.longValueExact();
		}

	} //end-if
	return output;
}
...

That’s all!!!!