A Really Clever Way of Doing DB Access - Can It be Done in Java 5?

C

chvid

I have an idea, that I have currently implemented in C# (.NET 1.1), for
interfacing with a database.

I would like to discuss whether it is possible to do a similar thing in
Java 5 using generics and annotations.

The basic idea is that you define a set of methods, that wraps simple
SQL statements, in an interface like this:

(C# pseudo code)

inteface MyDBLayer {
[SQL("SELECT COUNT(*) FROM users")]
public int countUsers();

[SQL("SELECT * FROM users WHERE name = @name LIMIT 1")]
public User getUserByName(String name);

[SQL("SELECT * FROM users", typeof(User))]
public List allUsers();
}

You give the above interface to my framework and it returns a proxy
implementation, that does the following:

For the method public int countUsers() it fires the SQL statement
SELECT COUNT(*) FROM users and reads the first row and first column,
and converts the result to an int. (Becuase the return type of the
method is "int").

For the method public User getUserByName(String name) it fires the SQL
statement SELECT * FROM users WHERE name = @name LIMIT 1, where @name
is the string provided in the name parameter and reads the first row,
creates an object of type User (the method's return type), and calls
its setter properties, according to the attribute names of the SQL
result table, with the values of the first row.

For the method public List allUsers() it creates an implementation of
return type List, fires the SQL statement and maps the resulting rows
in to User objects (where the type User is provided as a parameter).

The advantages of this approach is that you get strong typing without
code generation.

The user code will be simple and clear like this:

MyDBLayer myDBLayer =
(MyDBLayer)framework.createLayer(typeof(MyDBLayer), "db parameters");

int userCount = myDBLayer.countUsers();
User christian = myDBLayer.getUserByName("Christian Hvid");

etc. etc.

Ideally I want to do the following in Java 5 - like this:

inteface MyDBLayer {
@SQL("SELECT COUNT(*) FROM users")
public int countUsers();

@SQL("SELECT * FROM users WHERE name = @name LIMIT 1")
public User getUserByName(String name);

@SQL("SELECT * FROM users")
public List<User> allUsers();
}

But I think I run into the following problems:

1. The parameter name "name" is not available thru reflection in method
public User getUserByName(String name).
2. The type List<User> is "erased" to List in public List<User>
allUsers().

Any suggestions for workarounds?

Any comments on the overall design?

-- Christian
Please use the email-address on my homepage:
http://vredungmand.dk
 
C

Chris Uppal

1. The parameter name "name" is not available thru reflection in method
public User getUserByName(String name).
Click to expand...

I'd just bind by position, first argument binds to first parameter in the
query, etc
2. The type List<User> is "erased" to List in public List<User>
allUsers().
Click to expand...

See below.

Any comments on the overall design?
Click to expand...

Overall it strikes me as rather too "cute". One result is that the framework
will be doing a lot of guessing (or rather will make use of a
defined-and-documented, but nevertheless fairly complicated, set of heuristics
about how to relate the supplied method signatures to database data -- which
comes to the same thing ;-).

However, that's not to say its a /bad/ idea. Here's an example illustrating a
few suggestions (it does compile and "work"). Note that we explicitly decouple
the types of object created from the return types of the method (although we
allow defaults where we can), thus making a virtue of necessity for the erasure
problem.

-- chris

=======================================

import java.lang.annotation.*;
import java.util.*;

//////// framework ///////////

// private marker class for defaults
class NoClassSupplied {}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@interface SQL_ROW
{
String query();
Class itemClass() default NoClassSupplied.class;
}

@Retention(value=RetentionPolicy.RUNTIME)
@Target(value=ElementType.METHOD)
@interface SQL_ROWS
{
String query();
Class itemClass();
Class containerClass() default NoClassSupplied.class;
}

//////// application ///////////

interface User {/*...*/}
class UserImpl implements User {/*...*/}

interface MyDBLayer
{
@SQL_ROW(query = "SELECT COUNT(*) FROM users")
public int countUsers();

@SQL_ROW(
query = "SELECT * FROM users WHERE name = ?",
itemClass = UserImpl.class)
public User getUserByName(String name);

@SQL_ROWS(
query = "SELECT * FROM users",
itemClass = UserImpl.class)
public List<User> allUsers();
}


//////// test ///////////

public class Main
{
public static void main(String args[])
{
for (java.lang.reflect.Method m : MyDBLayer.class.getMethods())
{
System.out.println("method: " + m.getName());
for (Annotation a : m.getAnnotations())
System.out.println("\tannotation: " + a);
}
}
 
T

Tony Morris

I have an idea, that I have currently implemented in C# (.NET 1.1), for
interfacing with a database.

I would like to discuss whether it is possible to do a similar thing in
Java 5 using generics and annotations.

The basic idea is that you define a set of methods, that wraps simple
SQL statements, in an interface like this:

(C# pseudo code)

inteface MyDBLayer {
[SQL("SELECT COUNT(*) FROM users")]
public int countUsers();

[SQL("SELECT * FROM users WHERE name = @name LIMIT 1")]
public User getUserByName(String name);

[SQL("SELECT * FROM users", typeof(User))]
public List allUsers();
}

You give the above interface to my framework and it returns a proxy
implementation, that does the following:

For the method public int countUsers() it fires the SQL statement
SELECT COUNT(*) FROM users and reads the first row and first column,
and converts the result to an int. (Becuase the return type of the
method is "int").

For the method public User getUserByName(String name) it fires the SQL
statement SELECT * FROM users WHERE name = @name LIMIT 1, where @name
is the string provided in the name parameter and reads the first row,
creates an object of type User (the method's return type), and calls
its setter properties, according to the attribute names of the SQL
result table, with the values of the first row.

For the method public List allUsers() it creates an implementation of
return type List, fires the SQL statement and maps the resulting rows
in to User objects (where the type User is provided as a parameter).

The advantages of this approach is that you get strong typing without
code generation.

The user code will be simple and clear like this:

MyDBLayer myDBLayer =
(MyDBLayer)framework.createLayer(typeof(MyDBLayer), "db parameters");

int userCount = myDBLayer.countUsers();
User christian = myDBLayer.getUserByName("Christian Hvid");

etc. etc.

Ideally I want to do the following in Java 5 - like this:

inteface MyDBLayer {
@SQL("SELECT COUNT(*) FROM users")
public int countUsers();

@SQL("SELECT * FROM users WHERE name = @name LIMIT 1")
public User getUserByName(String name);

@SQL("SELECT * FROM users")
public List<User> allUsers();
}

But I think I run into the following problems:

1. The parameter name "name" is not available thru reflection in method
public User getUserByName(String name).
2. The type List<User> is "erased" to List in public List<User>
allUsers().

Any suggestions for workarounds?

Any comments on the overall design?

-- Christian
Please use the email-address on my homepage:
http://vredungmand.dk
Click to expand...

I take it you haven't looked at Hibernate, OJB, JDO, or in the case of .NET,
ObjectSpaces (pending last I checked) or NHibernate?
They each offer a relatively nice solution to Object-Relational Mapping.

<honesty>
My initial reaction to your suggestion is "wtf?"
</honesty>
 
C

chvid

RichGK said:
And what is the advantage of that?
Click to expand...

The advantage is that you don't have to do code generation. And you get
statically typed code without casts. Of course it depends exactly what
you are doing, but code generation in general makes things more
proprietary, more complex and more obscure.

For example. If you do code generation then every time you edit the
definitions that leads to a generated class, the type (potentially)
changes. This is no big deal if it happens in a build step, but if you
work in IDE that is type aware, you in general loose the ability to
dynamically visualise type errors.

Also generated code normally is opaque and has to be edited indirectly
in its definition file. There can be situations where that is less
elegant.
 
C

chvid

I wanted to get hold of the type User of the List so I could avoid
having to specify the same type twice:

@SQL("SELECT * FROM users")
List<User> allUsers();

Rather than:

@SQL(items=User.class, query="SELECT * FROM users")
List<User> allUsers();

Maybe it is simply not possible.
 
A

anonymous

I have an idea, that I have currently implemented in C# (.NET 1.1), for
interfacing with a database.

I would like to discuss whether it is possible to do a similar thing in
Java 5 using generics and annotations.

The basic idea is that you define a set of methods, that wraps simple
SQL statements, in an interface like this:

(C# pseudo code)

inteface MyDBLayer {
[SQL("SELECT COUNT(*) FROM users")]
public int countUsers();

[SQL("SELECT * FROM users WHERE name = @name LIMIT 1")]
public User getUserByName(String name);

[SQL("SELECT * FROM users", typeof(User))]
public List allUsers();
}

You give the above interface to my framework and it returns a proxy
implementation, that does the following:

For the method public int countUsers() it fires the SQL statement
SELECT COUNT(*) FROM users and reads the first row and first column,
and converts the result to an int. (Becuase the return type of the
method is "int").

For the method public User getUserByName(String name) it fires the SQL
statement SELECT * FROM users WHERE name = @name LIMIT 1, where @name
is the string provided in the name parameter and reads the first row,
creates an object of type User (the method's return type), and calls
its setter properties, according to the attribute names of the SQL
result table, with the values of the first row.

For the method public List allUsers() it creates an implementation of
return type List, fires the SQL statement and maps the resulting rows
in to User objects (where the type User is provided as a parameter).

The advantages of this approach is that you get strong typing without
code generation.

The user code will be simple and clear like this:

MyDBLayer myDBLayer =
(MyDBLayer)framework.createLayer(typeof(MyDBLayer), "db parameters");

int userCount = myDBLayer.countUsers();
User christian = myDBLayer.getUserByName("Christian Hvid");

etc. etc.

Ideally I want to do the following in Java 5 - like this:

inteface MyDBLayer {
@SQL("SELECT COUNT(*) FROM users")
public int countUsers();

@SQL("SELECT * FROM users WHERE name = @name LIMIT 1")
public User getUserByName(String name);

@SQL("SELECT * FROM users")
public List<User> allUsers();
}

But I think I run into the following problems:

1. The parameter name "name" is not available thru reflection in method
public User getUserByName(String name).
2. The type List<User> is "erased" to List in public List<User>
allUsers().

Any suggestions for workarounds?

Any comments on the overall design?

-- Christian
Please use the email-address on my homepage:
http://vredungmand.dk
Click to expand...

I fail to see why this is clever, it seems very GUI centric and
non-standard. For my money I'd much rather go with store procedures and
make sure their output could be used by Web Services AND GUI alike.
 
C

chvid

Chris said:
Overall it strikes me as rather too "cute". One result is that the framework
will be doing a lot of guessing (or rather will make use of a
defined-and-documented, but nevertheless fairly complicated, set of heuristics
about how to relate the supplied method signatures to database data -- which
comes to the same thing ;-).
Click to expand...

I don't really see that as a big problem - the scheme is basically:

1. Primitive (or primitive like) types int, double, String, Date - map
from single row, single column.
2. User defined type, that is not a collection - map from a single row.
3. Collection type - map from a full table.

The precise mapping defined and possible customised programatically
using either hookins or annotations.
 
C

Chris Uppal

I wanted to get hold of the type User of the List so I could avoid
having to specify the same type twice:

@SQL("SELECT * FROM users")
List<User> allUsers();

Rather than:

@SQL(items=User.class, query="SELECT * FROM users")
List<User> allUsers();

Maybe it is simply not possible.
Click to expand...

I can't think of any way that doesn't overly restrict your application (e.g.
only allowing it to return arrays).

So, since you are going to have to specify both the return type, and the type
of the objects in the container, we may as well allow them to be different.
E.g. the the method could be declared:

List<User> allUsers();

but actually returns a LinkedList containing instances of class UserImpl.

That's what I meant by "making a virtue of necessity".

-- chris
 
C

Chris Uppal

I wanted to get hold of the type User of the List so I could avoid
having to specify the same type twice: [...]
Maybe it is simply not possible.
Click to expand...

I said it wasn't, but I'd forgotten that the reflection API has been extended.
I think that

java.lang.reflect.Method.getGenericReturnType()

(and its friends) does just what you want.

The type info for generics is recorded in classfiles (despite "erasure"), it's
just not used by the runtime type checking or the bytecode interpretation.
It's there for the compiler to do its static checking, and -- as I've only just
remembered -- accessible to the reflective stuff too

-- chris
 
A

anonymous

Could you expand on that?
Click to expand...

What's to expand? In my experience, approx. half my customers want to
use Web Services because they want to integrate on the XML level. This
means that all the data you see on a web page has to be made available
as xml as well. By extension, this means that I usually have to design
my applications in a way that i have only one 'controller' to support
many views. Placing dynamic sql amidst JSP pages violates this design.
Since many customers also do not want dynamic sql, most of our stuff is
bases on stored procedures. We call them once for each function. Mostly,
they return xml, which is then readily available for Web Services and
for XSLT transformation alike.
Obviously, this is overkill for lots of other situations, where Struts
might be easier. Its just that we do not wish to make two functions to
support one business function, 'just' because we have two different
types of clients. What's next? VRML, Mobile access, WAP: need yet
another type of output. With XML, this is already there.
 
C

chvid

I am sorry I simply don't understand your point wr. to my suggested
design.

What I am suggesting is not UI centric. It has nothing to do with user
interfaces - it is an (form of) object/relational mapping.

It does does support SQL embedded in JSP or similar. In fact quite the
opposite - it prevents you from writing embedded SQL statements
directly and instead forces you to access them thru a method. This
gives a very strict design, where all SQL statements are put in
separate file (the user defined database layer(s)).

And it does not prevent you from using stored procedures, allthough the
examples I gave were dynamic SQL based.

If you want XML, you would, in this context, derive the XML from the
Java objects (that may have relational data mapped into them),
manually, partly, or completely automated by other tools such as
XStream.
What's to expand? In my experience, approx. half my customers want to
Click to expand...
use Web Services because they want to integrate on the XML level. This
means that all the data you see on a web page has to be made available
as xml as well. By extension, this means that I usually have to design
my applications in a way that i have only one 'controller' to support
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

The distinction between a java applet and an application 1
Connected SQLite to my java program but information are not submitted 2
Java Application 1
Hibernate in Java Question 4
How to try a range of hex values in C# code ? 0
Help me fix this bug in my program! Displaying chart data 1
java programming blueJ help 0
VALUE MANIPULATION IN MySQL with JAVA question.. 7

Members online

No members online now.
Total: 35 (members: 1, guests: 34)
Robots: 200

Forum statistics

Threads
473,755
Messages
2,569,534
Members
45,008
Latest member
Rahul737

Latest Threads

Top