JDBC Code Generator

JDBC code generator generates JDBC code from a specification jdl (JDBC description language) file. This specification file contains only (almost) tables definition and SQL statements, the code generator wrap these statements with the appropriate JDBC code.

Example

Lets say that you want to have a table of test messages and you want to be able to add new messages and to querey the existing ones. The following jdl file declare this table and the described functionality:

#mid is the primary key
msgs(mid){
	long	mid
	string	txt
}
getMsgs	: select * from msgs
newMsg	: insert into msgs values(?,?)

The generated code contains the Transaction class which can be used in the following way:

Transaction t = null;
try {
	t = new Transaction(); 
	ResultSet msgs = t.getMsgs();
	//do something with msgs
} catch (Exception e) {
}finally{
	if(t != null)t.commit();
}

There are some advantages when using the code generator:

Code is much more easier to maintain, just put all your tables and SQL statements in a single file and the code generator will do the rest.
Code is less error prone, when you write only the SQL statements and don't deal with all the overhead of the warping JDBC code it is less likely that you make a mistake.
Efficiency, the generated code uses a connection pool which make access to the DB much more efficient.

JDBC Description Language Syntax

The jdl file consists of table declarations and function declarations:

Table Declaration

Table declaration is done as follow:

$table_name $key{
	$type $name
	$type $name
	.
	.
	.
}

Where $table_name is the name of the table, $key is an optional key declaration (comma separated column names inside parentheses), $type is the type of the field and $name is the name of the field.

NOTE: You can create an identity field (auto increment field) by specifying a name which is not one of the columns name as the table key.

Don't use SQL's keywords (such as id) to name tables or fields.

Index Declaration

You can declare indexes you want to create on your tables. This is done inside the table declaration as follow:

@($col_name, ...)

Where $col_name is the name of the column you want to index, you can specifies as many columns as you want.

Function Declaration

Function declaration is done as follow:

$function_name : $sql_statement

Where $function_name is the name of the function and $sql_statement is an SQL statement where you can put a question mark (?) for the unknown values.

Comments

A '#' sign at the beginning of a line will comment out this line.

Full Syntax

The syntax is given in the EBNF format:

jdl		::=	{def}
def		::=	table
		|	ID ":" command
table		::=	ID [key] "{" {idx} {col} "}"
key		::=	"(" ID {"," ID} ")"
col		::=	type ID
idx		::=	"@" "(" ID {"," ID} ")"
type		::=	"bool"
		|	"int"
		|	"long"
		|	"double"
		|	"string"
		|	"string" "(" NUM ")"
command		::=	select
		|	insert
		|	update
		|	delete
select		::=	"select" ["distinct"] {selExp} "from" ID {"," ID} {selOp}
insert		::=	"insert" "into" ID "values" "(" val {"," val} ")"
update		::=	"update" ID "set" newval {"," newval} [updateOp]
delete		::=	"delete" "from" ID [deleteOp]
selExp		::=	"*"
		|	"count" "(" "*" ")"
		|	colId
selOp		::=	where
		|	groupBy
		|	orderBy
		|	limit
updateOp	::=	where
deleteOp	::=	where
where		::=	"where" exp
limit		::=	"limit" NUM
groupBy		::=	"group" "by" "(" colId ")"
orderBy		::=	"order" "by" ID order
order		::=
		|	"asc"
		|	"desc"
newval		::=	ID "=" val
exp		::=	term "<" term
		|	term ">" term
		|	term "=" term
		|	term "!=" term
		|	exp "and" exp
		|	exp "or" exp
		|	"(" exp ")"
term		::=	val
		|	colId
val		::=	NUM
		|	STR
		|	"?"
colId		::=	ID
		|	ID "." ID