info.michael-simons.eu

JPA Criteria Query or plain SQL

Note: The following stuff is not Hibernate specific but is true for every JPA2 provider and falls into the category of JPA criteria query.

Some days ago i just saw this video Christin Gorman about Hibernate. Although i don’t agree with her and i actually do like hibernate as a great tool, there are certainly some flaws and pitfalls in some of the designs, especially if the stuff you want to select isn’t totally easy to get.

I’m writing on a little scheduler whose data model looks like so:

There are tasks that have an one-to-many relation to parameters which are their default parameters. Jobs consists of a task (a many-to-one relation) and optional a list of concrete parameters (an one-to-many relation from jobs to job_parameters).

There is a foreign key that ensures that concrete parameters can only be from the jobs task.

Nothing fancy so far on the database site. The JPA mapping already get’s kinda complicated as i want to reproduce the constraint on job_parameters (on columns parameter_id and task_id) that references a unique constraint on tasks so i must use an @EmbeddedId on the task parameter entity.

That’s not the hard part.

I want a query that returns the names of all default parameter for a given job and have not been used on that job. In SQL i would write it down like so:

SELECT defaultParameter.name FROM sch_jobs jobs 
  JOIN sch_tasks tasks ON tasks.id = jobs.task_id
  JOIN sch_task_parameters defaultParameter ON defaultParameter.task_id = tasks.id
 WHERE jobs.id = :job_id
   AND defaultParameter.name LIKE '%' || '%'
   AND NOT EXISTS (
	   SELECT '' FROM sch_job_parameters concreteParameters
	    WHERE concreteParameters.job_id = jobs.id
	      AND concreteParameters.task_parameter_id = defaultParameter.id
   );

and be done with it.

If i would for whatever reason use a JPA Query along with the typesafe JPA Metamodel the method that gets me the list of unused parameters looks like this:

final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
final CriteriaQuery<SchedulerDefaultParameter> criteriaQuery = cb.createQuery(SchedulerDefaultParameter.class);
 
// The "root" of the query... See how it differs from the result type?
final Root<SchedulerJob> jobs = criteriaQuery.from(SchedulerJob.class);
// Joining to other entities
final Join<SchedulerTask, SchedulerDefaultParameter> defaultParameter = jobs.join(SchedulerJob_.task).join(SchedulerTask_.parameters);
 
// Preparing the subquery for the not exists clause		
final Subquery<SchedulerConcreteParameter> subQuery = criteriaQuery.subquery(SchedulerConcreteParameter.class);
final Root<SchedulerConcreteParameter> concreteParameter = subQuery.from(subQuery.getResultType());
 
return entityManager.createQuery(
		criteriaQuery.select(defaultParameter).where(
				cb.and(
						cb.equal(jobs.get(SchedulerJob_.id), jobId),
						cb.like(cb.function("lower", String.class, defaultParameter.get(SchedulerDefaultParameter_.name)), name.toLowerCase() + "%"),
						cb.not(cb.exists(
								subQuery.select(concreteParameter).where(
										cb.and(
												cb.equal(concreteParameter.get(SchedulerConcreteParameter_.job), jobs),
												cb.equal(concreteParameter.get(SchedulerConcreteParameter_.defaultParameter), defaultParameter)														
										)
								)
							)
						)
				)
		)
).getResultList();

What do i have now? A complete typesafe query that gives me a compilation error as soon as i change the mapping in an incompatible way.

And the price? As soon as i have written this post i forget how this stuff actually works.

As much as i like the automatic generation of standard joins and lookups and stuff that JPA respectively Hibernate in my case does, i’d never advocate to enforce some rules “only use JPA criteria” just for sake of compile time query checking.

I think that many people forget about the one true advantage of using sql: Telling the database what you want to have and not how you want to have it selected…

Filed in English posts, Java | Comments (0) »

Twitter Bootstrap: Make the default pills more usable

The default basic pills from Bootstrap are somewhat useless as they don’t change their state (class=”active”). I use the following javascript snippet to change that:

$('ul.nav.nav-pills li a').click(function() {			
    $(this).parent().addClass('active').siblings().removeClass('active');			
});

Filed in English posts, Shortcuts | Comments (1) »

How to retrieve tables of custom object types with JDBC

The following is not only a personal reminder for me but also a first try to answer some questions on Stack Overflow…

You can create custom object types in an Oracle database like so:

CREATE TYPE t_demo_object AS OBJECT (
  some_number  NUMBER,
  some_string  varchar2(32)
)
/

Those types can have member functions, custom constructors and some pretty neat features inside oracle.

There also exists tables of them like so

CREATE TYPE t_demo_objects IS TABLE OF t_demo_object
/

They can be used to do stuff like pipelined functions.

Often those types are used as results of stored functions and procedures, probably like so:

CREATE OR REPLACE PROCEDURE p_generate_demo_objects(p_num IN NUMBER, p_data OUT t_demo_objects) AS 
BEGIN
	p_data := t_demo_objects();
	p_data.extend(p_num);
	FOR i IN 1..p_num LOOP
		p_data(i) := t_demo_object(i, 'Index ' || i);
	END LOOP;
END p_generate_demo_objects;
/

There was a comment by sande, asking for an example on how to pass ARRAYs to stored procedures, so here is a stored procedure receiving data as well as a table to store it:

CREATE TABLE demo (
	some_number  NUMBER,
	some_string  varchar2(32)
);
/
 
CREATE OR REPLACE PROCEDURE p_receive_demo_objects(p_data IN t_demo_objects) AS 
BEGIN
	FOR idx IN p_data.first .. p_data.last LOOP
		INSERT INTO demo(some_number, some_string) VALUES(p_data(idx).some_number, p_data(idx).some_string);
	END LOOP;
	COMMIT;
END p_receive_demo_objects;
/

As long as this is used only within the database, everything is fine with Oracle Object Types… But often a procedure like this will be called from the outside through jdbc.

How to retrieve this object type? As an java.sql.Array of java.sql.Structs.

I’m doing it like so with minimal dependencies on the Oracle JDBC driver:

package tests.jdbc;
 
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.Struct;
import java.sql.Types;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
 
public class OracleTableOfResult {
	public static void main(String...a) throws Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		Connection connection = DriverManager.getConnection("jdbc:oracle:thin:<USER>/<PASS>@<DATABASEHOST>:1521:<SERVICE>");
 
		final String typeName = "T_DEMO_OBJECT";
		final String typeTableName = "T_DEMO_OBJECTS";
 
		// Get a description of your type (Oracle specific)
		final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection);		
		final ResultSetMetaData metaData = structDescriptor.getMetaData();
 
		// Call the procedure (or whatever else) that returns the table of a custom type
		CallableStatement cs = connection.prepareCall("{call p_generate_demo_objects(?, ?)}");
		cs.setInt(1, 5);
		// Result is an java.sql.Array...
		cs.registerOutParameter(2, Types.ARRAY, typeTableName);		
		cs.execute();
 
		// ...who's elements are java.sql.Structs
		Object[] data = (Object[]) ((Array) cs.getObject(2)).getArray();
		for(Object tmp : data) {
			Struct row = (Struct) tmp;
			// Attributes are index 1 based...
			int idx = 1;
			for(Object attribute : row.getAttributes()) {				
				System.out.println(metaData.getColumnName(idx) + " = " + attribute);											
				++idx;
			}
			System.out.println("---");
		}
		cs.close();	
 
		// See sandes comment, passing arrays of Objects
		// We need the array descriptor as well
		final ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(typeTableName.toUpperCase(), connection);
		// Create generic object array
 
		// Create ARRAY from array of STRUCTS
		final ARRAY demoObjectsFromJava = new ARRAY(
			arrayDescriptor, 
			connection, 
			new STRUCT[] {
			    // STRUCTS are created with the struct descriptor and a generic object array containing the values of the
			    // attributes of the T_DEMO_OBJECT
			    new STRUCT(structDescriptor, connection, new Object[] {23, "Testobject 1"}),
			    new STRUCT(structDescriptor, connection, new Object[] {42, "Testobject 2"})
			}
		);
		cs = connection.prepareCall("{call p_receive_demo_objects(?)}");
		// setObject with the designated sql type
		cs.setObject(1, demoObjectsFromJava, Types.ARRAY);
		cs.execute();
		// no output here, have a look in table demo
		cs.close();
 
		connection.close();
	}
}

In the original question is also room for improving the pl/sql procedure.

You can fill the resulting object procedural with a loop but you can also use a “bulk collect into”:

SELECT t_demo_object(colum_a, column_b) bulk collect INTO p_data FROM some_table;

which should be considerable faster on large datasets.

Note that it is not possible to use package level array types of record types in JDBC:

Oracle JDBC does not support RAW, DATE, and PL/SQL RECORD as element types.

Accessing PL/SQL Index-by Tables

Sadly, we have a legacy application that makes extensive use of such types… We had to rewrite most of the procedures.

Filed in English posts, Oracle | Comments (34) »

Oracle, JBDC, CallableStatements and named parameters

Update: Please take a note of comment #5 by DSurber. He works for Oracle and sheds some light on named parameters. In short: The names of the parameter do not refer to named placeholders in statement (like in JPA for example) but to PL/SQL named parameters!. Thank you very much.

You might have wondered what happened here:

Well easy, i had fun with the Oracle JDBC Driver and named parameters. I modified one call, added a parameter and set this parameter not in order… No problem, i thought, as i was using a callable statement with named parameter. As i it turns out, named parameters (“:blah”) are not so named, after all, but the order of setting them does matter, see the following tests:

package tests.jdbc;
 
import java.sql.*;
 
import org.junit.*;
 
public class OracleCallableStatementTest {
	protected static Connection connection;
 
	@BeforeClass
	public static void setup() throws Exception {	
		try{
			Class.forName("oracle.jdbc.OracleDriver");
			connection = DriverManager.getConnection("jdbc:oracle:thin:<USER>/<PASS>@<DATABASEHOST>:1521:<SERVICE>");
			PreparedStatement ps = connection.prepareStatement("Create table callable_statement_test(column1 varchar(16), column2 varchar(16))");
			ps.executeUpdate();
			ps.close();
			ps = connection.prepareStatement(
					"CREATE OR REPLACE procedure p_callable_test (p1 IN VARCHAR, p2  IN  VARCHAR) AS " + 
							"BEGIN " + 
							"  INSERT INTO callable_statement_test(column1, column2) values(p1, p2); " + 
							"END;"
					);
			ps.executeUpdate();
			ps.close();
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		}		
	}
 
	@Before
	public void prepareTable() throws SQLException {
		Statement st = connection.createStatement();
		st.executeUpdate("Truncate table callable_statement_test");
		st.close();
	}
 
	private void verify() throws SQLException {
		final Statement st = connection.createStatement();
		ResultSet rs = st.executeQuery("Select * from callable_statement_test");
		rs.next();
 
		String column1 = rs.getString("column1");
		String column2 = rs.getString("column2");
 
		rs.close();
		st.close();		
 
		Assert.assertEquals("1", column1);
		Assert.assertEquals("2", column2);		
	}
 
	@Test
	public void testCallableStatementParameterInOrder() throws SQLException {		
		final CallableStatement ps = connection.prepareCall("{Call p_callable_test(:p1, :p2)}");		
		ps.setString("p1", "1");	
		ps.setString("p2", "2");					
		ps.execute();
		ps.close();
 
		verify();
	}	
 
	@Test
	public void testCallableStatementParameterNoOrder() throws SQLException {
		final CallableStatement ps = connection.prepareCall("{Call p_callable_test(:p1, :p2)}");
		ps.setString("p2", "2");	
		ps.setString("p1", "1");							
		ps.execute();
		ps.close();		
 
		verify();
	}	
 
	@AfterClass
	public static void tearDown() throws SQLException {
		if(connection == null)
			throw new RuntimeException("No connection...");
 
		PreparedStatement ps = connection.prepareStatement("Drop procedure p_callable_test");
		ps.executeUpdate();
		ps.close();
 
		ps = connection.prepareStatement("Drop table callable_statement_test");
		ps.executeUpdate();
 
		connection.close();
	}
}

In this test i actually used the same names of parameters like in the stored procedure and you clearly see p1 should be inserted in column1 and p2 in column2.

#testCallableStatementParameterInOrder succeeds, but #testCallableStatementParameterNoOrder fails. The only difference is that i set p2 before p1… To me, this is a severe bug as named parameters imply a very different behavior.

I strongly advice my colleagues to be very careful about using plain named parameters. The order in which they are set is important!

I could reproduce this behavior with OJDBC 11.1.0.7 and 11.2.0.3 against Oracle 10g and 11g. The example does not work at all with OJDBC 10.2.x.

Filed in English posts, Oracle | Comments (6) »

Java 7, JAAS and Kerberos Single Sign-on vs. newer Windows Systems

Java Authentication and Authorization Service aka JAAS is a pretty neat way to build a pluggable authentication mechanism for a Java application.

My goal was to build a Single Sign-on (SSO) mechanism targeted on Windows machines (Windows XP SP3, Windows 7) that uses the cached kerberos ticket.

The jaas configuration should be pretty simple:

name_of_the_login_context {
    com.sun.security.auth.module.Krb5LoginModule required
    	debug=true
    	doNotPrompt=true    
    	useTicketCache=true        
    	renewTGT=true
    ;    
};

This means: Require and use the Krb5LoginModule module, do not prompt for a user details and use the windows ticket cache. As it turns out, this works out of the box with Java 6 but does not with Java 7.

Java 7 respects a Windows feature that disables the export of Sessions Key for Ticket-Granting Tickets so the native TGT on Windows (XP with SP2, Vista and 7) has an empty session key.

To enable the export of non empty Session keys add the following registry setting on Vista, 7 and Server:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters
Value Name: AllowTgtSessionKey
Value Type: REG_DWORD
Value: 0x01  ( default is 0 )

and this on XP SP2

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\
Value Name: AllowTgtSessionKey
Value Type: REG_DWORD
Value: 0x01

I expected the configuration to work… But it didn’t. It seems there are problems with User Account Control (UAC) and domain users that are local admins (so is my account on my machine). I tried to disable UAC (not acceptable for either me or the customer), adding the setting above to the KDC server, creating a krb5.ini file and some other attempts but no success. The jaas configuration started to work as soon as i removed my account from the local admins. Funny thing is: I readded it and it still works.

While doing my research i found several other irritating behaviors:

As SSO wasn’t working, it tried the following JAAS config:

name_of_the_login_context {
    com.sun.security.auth.module.Krb5LoginModule required
    	debug=true
    	useTicketCache=false
    ;    
};

This should force a login prompt with an adequate javax.security.auth.callback.CallbackHandler. On my Windows 7 machine Java 6 *does* need a krb5.ini file under c:\windows, Java 7 does not.

The exception with Java 6 is: “KrbException: Could not load configuration file C:\Windows\krb5.ini”

So i created one… After that, i had a “KrbException: Message stream modified (41)”, great, thanks. The problem here is the case sensitivity of the realm name. If the domain is FOOBAR and the krb5.ini contains

[realms]
    foobar = {
        kdc = dc.foobar
        admin_server = dc.foobar
        default_domain = foobar
    }

the authentication will fail. If the kdc returns the realm as FOOBAR, the krb5.ini must contain the realm FOOBAR like so

[realms]
    FOOBAR = {
        kdc = dc.foobar
        admin_server = dc.foobar
        default_domain = foobar
    }

Seems to be fun for the whole family… Hopefully this will save someone else some hours of frustration.

tl;dr

• Java 6 on Windows 7 needs a krb5.ini file when useTicketCache == false
• The realm name in krb5.ini is case sensitive
• Kerberos SSO under Windows works only with AllowTgtSessionKey set to 1 under HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters
• Kerberos SSO doesn’t work reliable with Domain Users that are local machine admins due to UAC

Update on the location of the jaas.conf file

I was asked how and where to specifiy the location of the JAAS configuration file. You basically have 3 options (for Java 7).

• The JRE looks for a default jaas.conf in
  “file:${user.home}/.java.login.config”
• You can add configuration files to java.security located in “lib/security” in the JRE base directory like so:
  “login.config.url.1=file:C:/config/.java.login.config”
• Or you can specify the jaas configuration on the command line with:
  “-Djava.security.auth.login.config=path_to_file”

Filed in English posts, Java | Comments (4) »