JBossDeveloper

type-safety is limited because nothing can ensure that the database table or column names are correct in the metadata class.

I think you're very much underestimating the value of centralizing the column and table names in one place, where they can be:

• validated by the framework at startup time, instead of at query execution time,


• easily changed without the need for global text search/replace, and


• auto-completed by the IDE at query authoring time.

Of course, once tooling arrives, there are even better possibilities.

In my experience, most Java developers feel uncomfortable embedding SQL strings in Java code. This is especially true for dynamic queries, such as in search screens, where you need to construct the query using string manipulation at runtime. In the typesafe API, that's far more natural.

William Draï wrote on Nov 25, 2009 12:21:


I guess I would be satisfied enough with something like this :

public void login(String un, String pw) {
   Tuple tuple = session.execute("select name from user where username = :username and password = :password")
                        .setParameter("username", un)
                        .setParameter("password", pw)
                        .getRow();
   String name = tuple.get("name");
   user = new User(un, name, pw);
}

Which, BTW, looks extremely similar to JdbcTemplate...

Gavin King wrote on Nov 25, 2009 15:52:


In my experience, most Java developers feel uncomfortable embedding SQL strings in Java code.

Yes, I agree, and that is why I want to see if I create a Scala DSL to deal with this...

This is especially true for dynamic queries, such as in search screens, where you need to construct the query using string manipulation at runtime. In the typesafe API, that's far more natural.

Totally agree, but I feel that Java syntax is not as clean as Scala's for this kind of job...

Seems I shot myself in the foot :) Maybe I should switch to Spring after all.

William Draï wrote on Nov 26, 2009 19:52:


Seems I shot myself in the foot :) Maybe I should switch to Spring after all.

I don't understand. You hate the ability to write typesafw SQL that much?

using new Object[]{...} vs. the setParameter() calls saves lines of code.

And is difficult to read and untypesafe. Methods with long argument lists are a code smell. They are a hint that you are missing an object from your design. They make code more difficult to refactor and format nicely.

Unfortunately, I have to take responsibility for the new Object[] { ... } thing, since I it's a pattern I invented back when I knew less about object-oriented programming :-( It's something Spring copied from Hibernate 1.x, back in the days before varargs. It's something we removed in Hibernate2 and JPA.

My typesafe API prevents you from writing:

s.createQuery("select u.name from users u where u.name=? and u.age=?, 21, "gavin").execute()

The equivalent in my API simply won't compile. Sure, that requires a few extra lines of code, and for this very simple query it's difficult to immediately see the advantage (the advantage comes in future maintainability of the code).

OK, so with a couple of tweaks to my API, I discovered a nice pattern, where you can define your query in a subclass of Query. This gives your SQL query a nice typesafe interface, as well as letting the Java compiler check your SQL syntax:

class LoginQuery extends Query {
     
     From<users> user = from(users.table);
     
     Expression<String> name = user.get(users.name);
     Expression<Integer> age = user.get(users.age);
     
     Parameter<String> username = parameter(String.class);
     Parameter<String> password = parameter(String.class);
     
     {
          select(name, age);
          where( and( eq( user.get(users.username), username ), 
                    eq( user.get(users.password), password ) ) );
          orderBy(name, age);
     }
     
}

To execute this query is really easy:

     public void testLoginQuery(Session session) {
          
          LoginQuery loginQuery = new LoginQuery();
          
          Iterable<Tuple> result = session.prepare(loginQuery)
               .set(loginQuery.username, "gavin")
               .set(loginQuery.password, "foobar")
               .result();
          
          for (Tuple tuple: result) {
               String name = tuple.get(loginQuery.name);
                        int age = tuple.get(loginQuery.age);
          }
     }

Of course, the calls to set() and get() are completely typesafe.

Arbi Sookazian wrote on Nov 26, 2009 20:35:

Gavin King wrote on Nov 26, 2009 20:06:

William Draï wrote on Nov 26, 2009 19:52:


Seems I shot myself in the foot :) Maybe I should switch to Spring after all.

I don't understand. You hate the ability to write typesafw SQL that much?

I don't understand either.  WDrai, plz elaborate...

I think what William means is he shot himself in the foot because his proposal for an API better than JdbcTemplate is an API identical to JdbcTemplate.

The problem is not that I don't like this typesafe querying facility, quite the contrary, especially in the latest proposal.

I totally agree on the value of typesafe queries when all the application uses raw SQL. But for JPA-mostly apps with a few highly optimized SQL queries (like most JEE apps now) it is plain overkill. Moreover this typesafe query language will never be able to express database-specific SQL queries or manually optimized queries (you know, the one query for which you need an Oracle expert DBA for 2 days). For the very few cases where I have needed raw SQL, I can say that I just didn't care of having one or two more try/catch statements.

Also this thread has strangely diverged from 'how to sell CDI to the community' to 'how to write a SQL query without SQL'. Maybe I'm absolutely wrong but I don't think a JDBC extension can be the killer feature for CDI. Seam does not have it but I don't think its relatively low adoption has anything to do with this.

Thanks Francisco, that's exactly what I meant ;)

I don't know why, but I remembered Spring templates as constructs with lots of strange inner classes. I must have mixed this up with something else.