Insanityville

Running SQL queries in JAVA

By in-sanity on November 18th, 2009

In a previous post, Connecting to Access database in JAVA, I’ve shared a little snippet of code with you that showed how to connect to an Access DBMS.

With the connection alone you can’t do much to the database excepting the connection. We need to manipulate the data found in the database we connected to.

The manipulation can be done in JAVA using Statement and PreparedStatement objects.

The difference between the two objects consists in the fact that the prepared statement represents a precompiled SQL statement.

Let’s assume a database test.mdb that has a table called users. The fields of the table are as follows:

id INT(11) PRIMARY KEY autoNumber;
USER text;
password text;
lastname text;
firstname text;
address text;

I think that the fields above suffice for this example.

Let’s begin with a simple query that lists the records in the table.

Statement st;
String user, lastname, firstname, address;
int i = 0; // little counter variable
try {
 Statement st1 = con.createStatement ();
 st = st1;
}
catch (SQLException sqlex) {
 sqlex.printStackTrace(); // print the error that occurred
}
String query = "SELECT * FROM users"; // the string that holds the query
ResultSet rs = st.executeQuery(query); // runs the query
while(rs.next()) { // walking though the result set
 user = rs.getString("username"); // retrieving String data
 lastname = rs.getString("lastname"); // retrieving String data
 firstname = rs.getString("firstname"); // retrieving String data
 address = rs.getString("address"); // retrieving String data
 System.out.println("no.  |  username  |  lastname  |  firstname  |  address");
 System.out.println((++i) + "  |  " + user + "  |  " + lastname + "  |  " +
                                      firstname + "  |  " + address);
}

The code above does the querying and the basic display of the returned data. I must say that the code is not tested and may need adjustments or corrections but should work based on e visual inspection .

The PreparedStatement object extends the Statement object and it stores precompiled SQL statements. This object can then be used to efficiently execute this statement multiple times. A simple example of usage is found on the java support website and I will post it here.

 PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
                                     SET SALARY = ? WHERE ID = ?");
 pstmt.setBigDecimal(1, 153833.00);
 pstmt.setInt(2, 110592);

As you can see from the example above, you don’t need to declare the query each time you use different conditions in the query. You can just use the setter methods of the fields in the query.

I hope you have found this post useful. If you find any errors feel free to post a comment with the corrected code.

Show me love

Close Bookmark and Share This Page

Save to Browser Favorites / Bookmarks