Basic usage

Configuring the database connection

Most things in Dalesbred happen through an instance of Database. It takes care of managing JDBC connections, so in a typical application you should only configure a single instance — unless you need to connect multiple databases. The easiest way to get hold of one is to specify the settings manually:

Database db = Database.forUrlAndCredentials("jdbc:example-url", "login", "password");

Note that this performs no connection pooling and is therefore probably not your preferred way of configuring the system in production. In a container you’ll probably want to lookup a named DataSource from JNDI:

Database db = Database.forJndiDataSource("java:comp/env/jdbc/ExampleDb");

Alternatively, you might setup a DataSource yourself, in which case you can just create a Database out of that:

Database db = Database.forDataSource(myDataSource);

If you are using Spring Framework, see the Spring-section on how to integrate Dalesbred with it.

Finding stuff

Running queries resulting basic types is simple:

List<Integer> newIds = db.findAll(Integer.class,
    "select id from department where created_date > ?", date);

There are a couple of ways to fetch results with multiple columns. First, you could just create a matching constructor:

List<Department> departments =
    db.findAll(Department.class, "select id, name from department");

public final class Department {
    private final int id;
    private final String name;

    @DalesbredInstantiator
    public Department(int id, String name) {
        this.id = id;
        this.name = name;
    }

    ...
}
The DalesbredInstantiator annotation for constructor is optional, but helps Dalesbred to make an unambiguous decision when there are multiple constructors. It also serves as a useful documentation. Finally, it can be configured as an entry-point for static analyzers so they don’t complain about unused constructor.

Instead of constructor, you can also use @DalesbredInstantiator on a static method that returns an instance of the class.

Second option is to bind values using fields or setters. The following example uses the default constructor for instantiation, field-binding for id and setter for name:

List<Department> departments =
    db.findAll(Department.class, "select id, name from department");

...

public final class Department {
    public int id;
    private String name;

    public void setName(String name) {
        this.name = name;
    }
}

If you have nested objects, you can bind to them as well as long as all objects in the path are instantiated:

List<Employee> departments =
    db.findAll(Employee.class, "select id, first_name as \"name.first\", last_name as \"name.last\" from employee");

...

public final class Employee {
    public int id;
    public final Name name = new Name();
}

public final class Name {
    public String first;
    public String last;
}

You can also convert the results directly to a map:

Map<Integer, String> namesByIds = db.findMap(
        Integer.class, String.class, "select id, name from department");

// first column is used for key, rest for instantiating the value
Map<Integer, Department> departmentsByIds = db.findMap(
        Integer.class, Department.class, "select id, id, name from department");

If for some reason you don’t want to map the results into your own class, you can ask for a ResultTable, which is basically a detached representation of a ResultSet:

ResultTable employees = db.findTable("select * from employee");

Alternatively, you can supply your own RowMapper or ResultSetProcessor-implementation in place of the class and handle the result sets manually, but usually this should be unnecessary.

Updates

Normal updates are straightforward, since we don’t need to do much work to map the results:

int modifiedRows = db.update("delete from user where id=?", 42);

If you plan to return stuff from updates, they are queries as far as Dalesbred is concerned:

int id = db.findUniqueInt("insert into department (name) values ('foo') returning id");

Queries

SqlQuery vs. query parameters

All methods come in two variants: there’s an implementation that takes an SqlQuery as a parameter and another implementation that takes a String and a variable number of parameters. The latter is just convenience method for the further, meaning that the following code fragments are identical in functionality:

import static org.dalesbred.query.SqlQuery.query;

SqlQuery query = query("select id, name from department where update_timestamp > ?", date);
db.findAll(Department.class, query);

db.findAll(Department.class,
    "select id, name from department where update_timestamp > ?", date);

Normally you want to use the latter form, but every once in a while it’s useful to be able to pass the query around with its parameters. In those cases you’d want to use the first form. An example is when you build the query dynamically:

db.findAll(Department.class, buildDepartmentQuery(form));

Named queries

In addition to using positional parameters in your SQL statements, you can also you named parameters:

import static org.dalesbred.query.SqlQuery.namedQuery;

Map<String,Object> values = new HashMap<>();
values.put("firstName", "John");
values.put("lastName", "Doe");

db.findAll(Department.class, namedQuery("select id from employee " +
                                        " where first_name = :firstName " +
                                        "   and last_name = :lastName", values));

Instead of Maps, you can also pass just regular objects to namedQuery as the source of values. The parameter names are mapped to properties or fields of the objects. Finally, if you want detailed control, you can pass your own implementation of VariableResolver to resolve the variables.

Building queries dynamically

There’s no high-level API for building queries, but QueryBuilder helps you constructing dynamic queries. It’s basically just a StringBuilder that also keeps track of parameters. Therefore you can say code like:

QueryBuilder qb = new QueryBuilder("select id, name, status from document");
if (status != null)
    qb.append(" where status=?", status);

db.findAll(Document.class, qb.build());
The benefit of using static queries is that IDEA and Dalesbred IDEA plugin know how to analyze them: they can be validated against the database schema and result classes. When building queries dynamically, you lose these benefits. Consider building a higher level abstraction on top of QueryBuilder if you need many dynamic queries.

Transactions

Transaction callbacks

To perform a bunch of operations in transaction, use TransactionCallback or VoidTransactionCallback:

db.withTransaction(tx -> {
    // transactional operations
    ...
    return result;
});

db.withVoidTransaction(tx -> {
    // transactional operations
    ...
});

Optionally, you can also pass Isolation or Propagation for these calls.

External transaction manager

If you are using Spring Framework, Dalesbred can integrate with Spring’s transaction-manager. Consult the Spring-section for details.

Implicit transactions

If you make calls to Database without and explicit transaction, by default a new transaction is started for each call. You can disallow this: in this case exceptions are thrown for calls without an active transaction:

db.setAllowImplicitTransactions(false);

Nested transactions

Nested transactions are supported if your database supports them:

db.withTransaction(Propagation.NESTED, tx -> {
    ...
});

Miscellaneous features

Explicit instantiators

Normally Dalesbred will automatically detect the best way to instantiate your classes based on database results. This can sometimes lead to surprising results. If you wish to be more explicit, you can annotate your preferred constructor with DalesbredInstantiator. This will cause Dalesbred to ignore all other constructors.

Large objects

You can stream large objects (blobs and clobs) to database by just passing InputStream or Reader to query. Similarly you can read them by asking back for InputStream or Reader.

try (InputStream in = new FileInputStream(name)) {
    db.update("insert into my_file (name, contents) values (?,?)", name, in);
}

try (InputStream in = db.findUnique(InputStream.class,
                        "select contents from my_file where name=?", name)) {
    ...
}
Note that the returned InputStream or Reader is only valid for the duration of the active transaction.

Custom type-conversions

Sometimes you need to convert database values to your own custom types and vice versa. To do that, you can register your functions to TypeConversionRegistry:

TypeConversionRegistry conversions = db.getTypeConversionRegistry();

// register conversions from database and to database types separately
conversions.registerConversionFromDatabase(
    String.class, EmailAddress.class, MyConversions::stringToEmail);
conversions.registerConversionToDatabase(
    EmailAddress.class, String.class, MyConversions::emailToString);

// or register both conversions with one call
conversions.registerConversions(
    String.class, EmailAddress.class, MyConversions::stringToEmail, MyConversions::emailToString);

Integrations

Java

Dalesbred provides built-in type-conversions for the following classes:

Model type Database type

java.net.URI

← →

String

java.net.URL

← →

String

java.util.TimeZone

← →

String

Short/Integer/Long/Float/Double

←

Number

BigInteger/BigDecimal

←

Number

BigInteger

→

BigDecimal

String/java.io.Reader

←

Clob

byte/java.io.InputStream

←

Blob

org.w3c.dom.Document

←

SQLXML

java.time.Instant

← →

Timestamp

java.time.LocalDateTime

← →

Timestamp

java.time.LocalTime

← →

Time

java.time.ZoneId

← →

String

java.time.LocalDate

← →

java.util.Date/java.sql.Date

Kotlin

Dalesbred has no required dependencies on Kotlin, but comes with a set of extension methods to make Kotlin use nicer. Just import everything from org.dalesbred.integration.kotlin and you’re good to go:

import org.dalesbred.integration.kotlin.*

...

fun findEmployees() = db.findAll<Employee>("""
      select id, name, salary
        from employee
      order by name, id
    """)

Joda-Time

If from Joda-Time is detected on classpath, Dalesbred will automatically register type-conversions between Joda-Time's DateTime, LocalDate and LocalTime to java.sql.Timestamp, java.sql.Date and java.sql.Time.

Spring

Dalesbred has support for integration with Spring Framework and its transaction management. To integrate Dalesbred, create a configuration class inheriting from DalesbredConfigurationSupport and specify beans for DataSource and PlatformTransactionManager. A minimal configuration would therefore be something like the following:

@Configuration
@EnableTransactionManagement
public class MyDatabaseConfiguration extends DalesbredConfigurationSupport {

    @Bean
    public DataSource dataSource() {
        return new JndiDataSourceLookup().getDataSource("jdbc/my-database");
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }
}

After this you can inject Database normally in your beans.

IntelliJ IDEA

If you’re using IntelliJ IDEA, check out Dalesbred IDEA Plugin, which provides inspections for common errors (e.g. mismatch between query parameters and query).

Test support

By including the dalesbred-junit artifact in your project as a test dependency, you’ll get support for writing transactional test cases:

public class MyTest {

    private final Database db =
        TestDatabaseProvider.databaseForProperties("testdb.properties");

    @Rule
    public final TransactionalTests tx = new TransactionalTests(db);

    @Test
    public void simpleTest() {
        assertEquals("hello, world!",
            db.queryForUnique(String.class "select 'hello, world!'");
    }
}

More examples

Check out the test cases under dalesbred/src/test/kotlin for more usage examples.

Downloading

Gradle

repositories {
    mavenCentral()
}

dependencies {
    implementation("org.dalesbred:dalesbred:1.3.5")
}

Maven

<dependency>
    <groupId>org.dalesbred</groupId>
    <artifactId>dalesbred</artifactId>
    <version>1.3.5</version>
</dependency>

Migration from 0.x

If you are migrating from an old version of Dalesbred, be sure to check the migration guide.