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.

Creating Swing Forms Using JFormPane

JFormPane is a class that allows to create swing forms fast and easily. Let's see how it's done by creating an example where we collect user info:

Creating the form fields

Whenever we want to create a form, we must create the fields that are part of that form first, to achive this, we use the FormField class. We are going to create six FormField objects to collect the user first name, last name, password, weight, birthday and to find out is he/she likes ice-cream:

FormField firstNameField = new FormField("First Name", FormField.Type.TEXT);
FormField lastNameField = new FormField("Last Name");
FormField passwordField = new FormField("Password", FormField.Type.PASSWORD);
FormField birthdayField = new FormField("Birthday", FormField.Type.DATE);
FormField weightField = new FormField("Weight", FormField.Type.NUMBER);
FormField iceCreamField = new FormField("Likes Ice-cream?", FormField.Type.BOOLEAN);

As seen in the example above, the FormField class constructor takes two parameters: the name of the field, and the type of the field. If you ommit the type of the field, the FormField class will set it to text.

Creating a new JFormPane

We then add the form fields to an array. That array is the one we pass to the FormField constructor.

FormField[] fields = {
	firstNameField,
	lastNameField,
	passwordField,
	birthdayField,
	weightField,
	iceCreamField
};

JFormPane formPane = new JFormPane(fields);
JOptionPane.showMessageDialog(null, formPane);

This will give us a shiny new form as the one below:

Reading the form values

To read the form values we use the getValues() method, that will return an Object array. Once we read the values, we can cast the results into their original type, as follows:

Object[] results = formPane.getValues();

String firstName = (String) results[0];
String lastName = (String) results[1];
String password = (String) results[2];
Date birthday = (Date) results[3];
Double weight = (Double) results[4];
Boolean likesIceCream = (Boolean) results[5];

Using combos

A more advanced feature of JFormPane are the use of combos as a datatype. Combos allow the user to select an item from a multiple option combo box. The next image illustrates this:

In order to add a combo field to our form we must create an array with the options the user can choose from, and add the options to the FormField, as follows:

String[] colors = {
	"Yellow",
	"Red",
	"Blue"
};

FormField colorField = new FormField("Favourite Color", FormField.Type.COMBOBOX);
colorField.setCombos(colors);

That's it, in order to be able to use JFormPane, you will have to download the melqOS jar (v. 0.95 or higher) available at http://sourceforge.net/projects/melq/, and its dependencies (JCalendar and JSqlParser).
API documentation can be found at http://melq.sourceforge.net/api/.

Feel free to post any comments!