Basic database operations
ActiveJDBC architecture has two layers:
- Lower layer: Base and DB classes
- Higher layer: Models as described in the ActiveRecord pattern.
The first layer is provided by two classes,
These two classes mirror each other and provide two functions:
- Database connection management
- Convenience access over plain JDBC API.
The differences and similarities of the
DB are described on the Database connection management page.
The goal of this page is to familiarize the user with the rest of the functions of these two classes.
Moving forward, all examples will be based on the Base class, considering only one database connection. The reader can extrapolate the below examples for the
DBclass if more than one connection isd needed.
The rest of the documentation (all other pages) related to database access describe a
Higher layer access to data, see the documentation page.
See description on a page Batch operations
When accessing a database, you can run into exceptions (surprise!). While
DB classes wrap all checked JDBC exceptions into subclasses of a
RuntimeException, there is still a need to manage exceptions. Additionally, the DB class is an instance of a Closeable interface, and the
Base.open(..) methods return an instance of a
DB, which makes it easy to write code such as:
The code above will automatically close the JDBC connection when it exits.
The same as above, but with Lambdas;
If you are running tests against a test database using
DBSpec or its subclasses, the test database is always empty. This makes it harder to check the values of some table during a test. Here is a simple hack:
Base.findAll("select * from users").dump(); will simply pull all records from the
users table and will dump them to STDIO.
This is a convenience method for testing.
Reading entire dataset.
When you need to read an entire dataset from a table(s), you can use this method:
The query is anything you would type into a database console. You can use joins, sorts, etc. The important thing to keep in mind is that you will read entire dataset into that list.
Each map will have keys that map to the columns of the underlying resultset.
This example gives you a hint that the
DBclasses are used by models under the hood!
WARNING: The example above will load a entire resultset in heap, be careful with your query!
Streaming large resultsets
The example above will get you all records at once. What if you have millions of records to process? The example below will allow you to do just that:
See more at: Base.find(..).
Reading a single value
Sometimes you need to do just that!
The code above will be returning a first column of the first row returned from the database and will ignore all other values!
Reading a single column
Just like the
firstCell, this method will ignore any other values beyond a first column, so ensure your quiery only selects a single column.
The base and DB classes also provide an API for batch operations.
For more information, follow to the Base JavaDoc.
How to comment
The comment section below is to discuss documentation on this page.
If you have an issue, or discover bug, please follow instructions on the Support page