Hi again…
We have previously requested guest blog posts from projects. Now that I’ve settled in and have things ordred so that I can manage them, I’m going to run these as we get requests.
This first post is from the Depeche Project. Here you go…:
Depeche is a java library that simplifies low-level database access. It has been developed since 2009 by Adrian Sándor (aka aditsu) to help with his work and personal java projects, partly out of frustration with the existing database libraries and partly for fun. It is loosely inspired from Django framework’s query sets.
Let’s look at a small example first – a program that lists some strings stored in a table, then reads a string from the keyboard and inserts it. With JDBC it takes about 30 lines, I’ll spare you the details. Here’s how it can be done with Depeche:
Provider p = new PGProvider(host, dbname, user, pass); for (Record rec : p.query("entry").fields("data").order("id")) { // alternative: // for (Record rec : p.rawQuery("select data from entry order by id")) { System.out.println(rec.getString("data")); } System.out.println("Enter text:"); String s = new Scanner(System.in).nextLine(); p.query("entry").set("data", s).insert(); // alternative: // p.rawQuery("insert into entry(data) values(?)", s).update();
Some things you can observe from this code:
– Databases are accessed through Provider objects; there are different implementations for different types of databases, customized for their features and peculiarities. This code connects to a PostgreSQL database, but Depeche also supports MySQL, H2, SQLite, MS SQL Server and FileMaker, and more can be added.
– Queries can be built dynamically (using method chaining) with the query method, or you can use SQL directly with rawQuery
– Query results are iterable, they are in fact lists of Record. Record objects are similar to single rows in a ResultSet, but they are standalone.
– Dynamic queries can be used to insert data using the insert method (there’s also update and delete)
– Raw queries can take parameters (passed directly as varargs), and have an update method corresponding to executeUpdate in JDBC
– No checked exceptions are being thrown
More details about queries:
– Query objects do not do anything when constructed, but they are lazily evaluated (executeQuery) as select queries when used as lists, or explicitly using the select method. Alternatively, they can be executed as update queries (executeUpdate) using the update method (also insert and delete for dynamic queries).
– After running a select query, results are automatically cached, and can be cleared explicitly (the query will be lazily evaluated again)
– Other than internal result caching, queries are immutable objects. Every “modification” to a dynamic query (e.g. filtering or ordering results) creates and returns a new query object with no cached results. This makes it very convenient to pass them around to other methods and perform various related queries, such as paging results (using limit and offset).
– Queries can also be run in memory – every query can be converted to a “local query” which keeps the results of the initial query and can select, filter, sort them etc using the same methods, without touching the database. This is very useful for performing multiple queries over a small data set.
You may be wondering by now where are the database connections and prepared statements. They are created automatically when running a select, insert, update or delete. By default, every such method invocation will use a new connection. Even with a connection pool, this is not always desirable, so Depeche also allows reusing the same connection for running multiple statements.
You can not explicitly obtain a connection from a Provider, but you can ask it to run a “task” that requires a connection. This has the advantage that the Provider takes care of all the exception handling and resource closing boilerplate for you. There are two methods: run(DBTask<T>) and transaction(DBTask<T>), the latter obviously running the task as a transaction. DBTask is a Callable-like interface, with one method – T run(Conn), Conn being a wrapper for a Connection object. All the methods mentioned before (select, update, insert etc) have a version that takes a Conn parameter and will use that connection instead of creating a new one. This way, you can run multiple statements using the same Conn object inside a DBTask.
Other features:
– Depeche keeps a detailed model of the database structure (constructed as needed), including multiple-column primary keys and foreign keys, and can programmatically perform various DDL statements, such as creating a table. The model is generally database-neutral, which makes it easy to, for example, copy simple tables (including structure and data) between different database servers (such as MySQL to PostgreSQL)
– The same model also helps to join tables easily through the existing foreign keys, or filter rows by the primary key, without having to specify the columns explicitly. And it makes it straightforward to obtain metadata from tables, queries and records.
– Record objects are also maps of field names to values, and can be created, inserted, updated and deleted on their own (corresponding queries are created and executed automatically)
– While the code example constructed a Provider explicitly, it is also possible to use a properties file for configuration. The configuration file can specify how to create one or more providers using DriverManager, or a DataSource, or JNDI, or C3P0 wrapping any of the previous methods. Even new Provider implementations and custom configuration styles can be created and easily registered.
– Complex raw queries can be “composited” from simple queries – in the same way as using “?” for parameters, you can use “$” for subqueries (which can have their own parameters). This makes it easy to keep track of parentheses, parameter positions and general query structure.
Finally, Depeche is not an ORM – it doesn’t work with custom objects (so there’s no static type checking for fields), but with data structures, mainly queries and records. That means there’s more work involved in mapping records to objects and back, but also more flexibility in working with complex or dynamic database structures and queries.
An ORM based on Depeche, named Depeche-objects (loosely inspired from ActiveObjects) is also under development and already being used in a few projects. It doesn’t have a release yet, but you can get the code from the repository and try it out.
For more details, please refer to Depeche’s web site – http://depeche.sourceforge.net/
All kinds of feedback are welcome.