info.michael-simons.eu

#WJAX 2012

Another year, another W-JAX. It seems to become a jour-fix, being in Munich in November.

As last year i only can recommend staying in the Westin Grand if your company is willing to afford this. You’ll have a much better conference experience than commuting throughout the city for your hotel or hostel. When i started going to conferences i often choose a cheaper hotel, probably outside and most often, i didn’t attend the later session because I was already tired from the conference marathon, just wanting to put my feet a little and wasn’t willing traveling to a city for hours in the night. Also you can take a break much easier.

Enough of that. I only wrote one short post called Old and tired? last year, this year i’m trying to review the talks i visited a little bit, like i did in 2010.

Angry Duke – Physics Games mit JavaFX by Anton Epple @monacotoni

My conference started on wednesday with a great talk by Anton Epple about integrating Box2D respectively JBox2D with JavaFX. My first though was: Ok, time to get those old mathematics back into my brain and try something funny with (J)Box2D. Haven’t done such things in a while.

I was really impressed how easy it seams to recreate something like Angry Birds dubbed Angry Nerds with JavaFX. I was actually fiddling around with JavaFX the last months and i must say this is great stuff. I was always found of Java Swing (I’m project lead of a Desktop GIS project, so no surprises there) and i think JavaFX is the way to go. If Apple eventually allows interpreted stuff on iOS than the possibilities seems endless.

Spring-Data JPA: Datenzugriffsschichten richtig gemacht by Oliver Gierke @olivergierke

So another Spring spin-off, Spring-Data. Spring-Data is an umbrella project that encapsulates data access in general and provides support for relational as well as non-relational databases.

Olivers talk was especially about using Spring-Data JPA to reduce the cruft work of building repositories.

I did like the extensive live coding and saw already many things i could improve in my own projects, not especially related to Spring-Data.

It’s impressive what Spring-Data JPA can do but i’m not convinced of another layer of data access if not needed. I tend to get along with JPA very well. Complex SQL queries are defined as named native queries and apart from that i like the Criteria Query in combination with the metamodel not as bad as Oliver 😉

Natural User Interface Design by Prof. Wolfgang Henseler @prof_henseler

Command line evolved to Graphical User Interface (GUI) evolves to Natural User Interface (NUI) or in short: Times are a-changin. Questions raised in the keynote: What is the definition of intuitive and stuff like that. What’s intuitive to me must not be intuitive to someone else… The quintessence for me: Don’t try to force my model the world on other people. The less one needs to switch the mental model, the more intuitive stuff gets. But in the end: This should be common sense.

I would expect the raise of natural user interfaces together with all the new powerful technologies. New tools change our view of the tools, new views change the tools. And so on.

Cool Java by Arno Haase

Arno stood in for Roman Roelofsen. The talk was nice but mostly about libraries. The one thing new for me: There is not only a “magic” readObject that can manipulate an object while deserializing but also a readResolve that can and should be used to enforce singletons. See some more info here.

In 60 Minuten von der Excel- zu JEE-Anwendung by Konstantin Diener

This talk had potential as it was labeled “with code”. I would have loved to see more actual coding but was disappointed. All i got was an (impressive) enumeration what has been done. A pity, as we have some similar requirements.

Gute Zeilen, schlechte Zeilen. Regeln für wartbare Programme by Dirk Weil

Nice title, but i guess it could be too less to apologize to RTL if Dirk publishes the logo of this talk 😉 Anyway, first halve of the talk was pretty much about codestyle and tools to check it, the second about coding itself. I’m so glad i’m not the only one who is personally embarrassed when i see someone using something like “if(something) return true” or not using De Morgan and stuff like that.

But in the end, many things Dirk mentioned boil down to common sense as well and this is something i’m missing pretty much often in junior programmers.

Hochverfügbare JEE-Architekturen für Online-Portale am Beispiel von ElsterOnline by Johannes Rödel

Johannes’ talk didn’t have much code in it but was pretty interesting nevertheless. Johannes talked about the setup of Germany tax offices for the online tax “Elster Online” and about the metrics they use to evaluate if the portal is highly available or not.

He gave the following anecdote and tip: They wanted to change their setup to use a hardware load balancer between 2 releases but the business didn’t approve the same hardware for integration tests at first. Going production it all went messy. Something was wrong with the internet provider and the balancer and in the end nobody really knew what was going on as it couldn’t have been tested. Lesson learned: Use the same setup in integration test as in production. If this is not possible, don’t use the latest and greatest stuff in production.

Security Patterns – Mehr als nur Authentifzierung und Autorisierung by Mike Wiesner @mikewiesner

I’m always interested in talks about security and also trying to improve stuff so Mikes talk was very welcome.

Mike told us who to prevent common flaws in authentication and authorization through using and correctly configuring frameworks, pretty much – you guess it – also common sense. But its worth mentioning that he also emphasized that the application should not bow to the security (you know the phrase “this cannot be done because of security concerns”).

Most interesting to me where injections (again) but not SQL-Injections this time but injections into queues, for example those of Spring-Integration. You guess it: Everything that comes from “out there” should not be regarded as too friendly 😉

Git Culture by Matthew McCullough @matthewmccull

A keynote about culture and happiness. In english. What could go wrong? Nothing. Matthew had a fantastic talk about how bringing a culture of direct responsibility can help grow a business, in this case Github. He had some great examples that this not only works for a (not so small anymore) startup, but also for companies like Gore. I can proudly say that although I course some times about processes in my company, i’m glad to work at Enerko Informatik because the company cares for its employees as well and holds our ideas high.

Essence (also again, see webcon some weeks ago): Changing ones behavior will change facts in the long run.

I really like talks that bring the technical side and the more humanistic or philosophical side of our profession together. Well done Matthew.

Import continuous delivery by Jevgeni Kabanov @ekabanov

A very ambitious talk about how to use Jenkins, Sonatype Nexus and LiveRebel to automatically build your stuff, test the build, have it reviewed by QA and finally live deployed to production. Although i did not understand everything and we have no need for such a setup at the moment, that was all i needed to finally occupy myself with Jenkins for my team.

Practical Git by Matthew McCullough @matthewmccull

I’m using git for my own projects for about 2 and a half year now and i never regretted the switch from SVN. At work the situation is quite different (somewhere there is still a CVS repository that hounds me (also, the code inside) what i’m not prepared). We still at SVN but after Matthews talk i have some more arguments at hand.

That is to say my own experience with git already changed my way i use branches and i can fully confirm Matthews views about branches. Explaining this to people who are used to SVN can be quite hard. As is branching and merging in SVN.

Good show with a lot of live hacking and talking afterwards.

W-JAX 2012 was quite good, met a lot of kind people and heard interesting stuff. Now i’m looking forward to the Spring workshop tomorrow.

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

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) »