Can someone help me with some beginners JDBC ?

Question

Hi, As a C# developer new to Java, i thought it might be easiest if i simply show a bit of C# code so i can see what the equivalent Java JDBC calls are:

String myConnectionString = "...";
String mySql = "select name from people where id = @myId";
int myId = 123456;

List<Field> fields = new List<Field>();
using (SqlConnection conn = new SqlConnection(myConnectionString)
{
  conn.Open();
  using (SqlCommand cmd = new SqlCommand(mySql,conn))
  {
    cmd.Parameters.AddWithValue("@myId", myId);
    using(SqlDataReader rdr = cmd.ExecuteReader())
    {
      while (rdr.Read())
      {
        String name = rdr.GetString(0);
        fields.Add(name);
      }
    }
  }
}

Now i know that the using statements above will safely close the database if anything goes wrong, whereas with java it's a bit more complicated (try..finally or something). And in java, i'm not sure what exactly needs to be disposed or closed (or whatever) - the connection only? Or the statement as well?

If you could give me a leg up, that'd be great. Thanks a lot

edit: I really like the code here: (http://stackoverflow.com/questions/1909662/java-exception-handling-style)

Connection conn = MyDatabaseLayer.getConnection();
try {
  ... use conn ...
}
finally {
  conn.close();
}

However, do i need to do any further exception handling to ensure the statement and reader and all that other stuff gets closed? Or is closing the connection enough?

Answer 1

You indeed need a try-finally block here. The Java equivalent of the using keyword will be introduced in the upcoming Java 7. A Java port of your code would look like:

// Prepare.
String url = "...";
String sql = "SELECT name FROM people WHERE id = ?";
int id = 123456;
List<String> names = new ArrayList<String>();

// Declare before try.
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;

try {
    // Acquire inside try.
    connection = DriverManager.getConnection(url);
    statement = connection.prepareStatement(sql); 
    statement.setInt(1, id);
    resultSet = statement.executeQuery();

    // Process results.
    while (resultSet.next()) {
        names.add(resultSet.getString("name"));
    }
} finally { 
    // Close in reversed order in finally.
    if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
    if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
    if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
}

When not using connection pooling, closing the Connection alone would in most cases also close the Statement and ResultSet. Although not strictly specified in the JDBC API, the average JDBC driver would implicitly do that. But this is not the normal JDBC idiom. You should really close all the resources explicitly. This makes your code safely reusable for the case that you'd like to introduce connection pooling.

See also:

• Equivalent of "using" keyword in Java
• JDBC tutorial
• JDBC connection pooling practices
• JDBC connectivity with MySQL

Answer 2

Here's a rough explanation of the steps, as liberally copied from some random page:

first, you load the driver. This will be a class in your driver jar file. In many environments you get this, actually, from a datasource, this is sort of old fashioned, but probably better to see the nuts and bolts.

Class.forName("com.imaginary.sql.msql.MsqlDriver");

then you get a connection:

  Connection conn = DriverManager.getConnection("jdbc:msql://www.myserver.com:1114/....", "user1", "password");

The url string tends to be different for different db vendors. Luckily we don't swap databases too often, so you only need to look it up once. Now you can finally use the damned thing.

PreparedStatement ps = conn.prepareStatement("SELECT * FROM FOO WHERE A=?", 1);

A prepared statement gets cached, so you can use it with inseted parameters. It will work with a plain SQL statement, but for that you can just use a statement. You can also just call conn.executeQuery(...) to get a resultSet, which is what you want.

 ResultSet rs = ps.executeQuery();

Now you can loop through the rs, and get whatever:

while (rs.next())
{
..
}

ResultSets also have ResultSetmetadata which gives you things like the column names, # of columns (but not the total # of results, which would be too easy).

As for try catch, you need to close your statement/result set after you use them. Every time. Otherwise bad things will happen. Like leaving open resources on your db. Since your db connect method can throw errors, you rap the whole thing in a try catch, and close your statement (and connection, if you've made it here) in a finally block.

This is why people use ORM frameworks in java.