Sunday, November 27, 2011

Making SQL queries using DBManager

The DBManager class contained in MelqOS library can help us to build SQL queries without the need of creating the SQL sentence, DBManager will create it for us.

Using DBManager class to query the database has several advantages, shuch as:

  • There is no need to create the SQL statement.
  • There is no need to process the ReturnStatement, because DBManager will process it for us, and will give us the result in a list of lists.
  • There is no need to handle connections, DBManager will handle them for us.
  • It’s very easy to use, it’s easier than creating queries by hand.
  • It supports prepared statements (see PreparedStatementFactory class).

Let’s see an example of how to use DBManager:

First we have to connect to the database:

DBManager dbManager = new DBManager();
//We connect to a PostgreSQL database
dbManager.connect("localhost", "dbName", "user", "passwd", DBManager.DBVendor.POSTGRESQL);

Now that we have connected to the database, it’s time for a query.
Let’s assume that we have a table in our database named User, and we want to select the columns firstName, lastName, phone and height. We can do so, with the following code:

String[] cols = {"firstName", "lastName", "phone", "heigth"};
List<List<Object>> results = dbManager.select("User", cols);

The above query will return all the rows of the table User. But what if we want only the users who’s age is greater than 18 and who’s last name is Newton?. The will have to add a new parameter to our select statment like this:

String[] cols = {"firstName", "lastName", "phone", "heigth"};
String[] conds = {"age > 18", "lastName = 'Newton'"};
List<List<Objectgt;> results = dbManager.select("User", cols, conds);

Queries done by DBManager class are returned in a list of lists (List<List<Object>>), where the big list contains other lists that represnt the rows. Each row list contains as many elements as specified in the select statement.

Right now only PostgreSQL and Oracle are supported, if you wish support for another database engine please contact me. If you have developed support for another database engine please share it with me so I can publish it. Thanks!

That’s it for now. In other posts I will give insights in how to insert, update and delete data. Feel free to read the javadoc for more complex uses of DBManager class, such as using GROUP BY clauses and using prepared statements to query data.

I’ll be more than happy to answer your questions or read your comments.