Launch_queries

... and now some queries to the database !

(Return to examples index)

Everytime we want to do something with our database, we'll create a new instance to it, and we'll use its access methods. For now, we'll make some queries, and that means using sql() method.

A query (a select or an update) is an object that contains an user-friendly description, a querystring, some parameters (optionally) and a way to interpret the resultset obtained (in case of a select). Let's count the users in the database, to illustrate it:

import net.sourceforge.milk.Mappings;
import net.sourceforge.milk.sql.Select;

...

// First, we create the query. Note Mappings.toInteger(), 
// this method provides a class that maps a resultset to an integer
// (we'll see this later).
final Select<Integer> queryCountUsers = new Select<Integer>("Count all users", 
    "select count(*) from user", Mappings.toInteger());

// Now, the execute the query
final Integer numberOfUsers = new IssuesDb().sql().run(queryCountUsers);

// Done!

The only thing that needs to be explained is the mapping of the resultset. As queryCountUsers returns an Integer (specified in the generic type), we must provide a ResultHandler<integer> that turns the resultset into an integer.</integer>

In case we want to obtain a list, a map, an object, etc. we have to specify different ResultHandlers that creates this types of results.

We'll make some more queries to find out what other ResultHandlers are provided (obviously you can create you custom ResultHandlers when needed).

import net.sourceforge.milk.Mappings;
import net.sourceforge.milk.sql.Select;
import net.sourceforge.milk.tests.common.Queries;
import net.sourceforge.milk.types.Data;
import net.sourceforge.milk.types.DataList;
import net.sourceforge.milk.types.DataMap;

import org.junit.Assert;

...

// Let's get a row representing the user with id = 1
final Select<Data> queryFindUser = new Select<Data>("Find user", 
    "select * from user where userId=?", new Object[] { 1 }, 
    Mappings.toData());
final Data userData = new IssuesDb().sql().run(queryFindUser);
Assert.assertEquals(userData.get("userid"), Integer.valueOf(1)); 
// column lookup is case insensitive


// Now, we'll fetch a list of rows
final Select<DataList> queryAllUsers = new Select<DataList>("Select all users", 
    "select * from user", Mappings.toDataList());
final DataList dataList = new IssuesDb().sql().run(Queries.queryAllUsers);
final Data dataOfFirstUser = dataList.get(0);

// And now, a map of rows, using userId as the key for each row
final Select<DataMap> queryMapOfAllUsers = new Select<DataMap>(
    "Select all users", 
    "select * from user", 
    Mappings.toMapOfData("userId")); // userId will the the column to create the keys
final DataMap datamap = new IssuesDb().sql().run(queryMapOfAllUsers);
final Data user = datamap.get(1); // get user with userId = 1
Assert.assertEquals(user.get("userId"), Integer.valueOf(1));

Note we never create or manipulate connections, so the framework is responsible for openning and closing them. All is transparent for us.

Let's add a new step. Let's obtain objects from queries ...