SQL: Java Connection

In order to use SQL to perform operations with a database, to do things like sending queries and other statements, you first need to connect to a SQL database. This is accomplished differently in different languages, but for some it’s more complicated than in others. Today we’re going to talk about how to do this in Java, since it requires a few more steps and interacting is a little more complicated. The first thing you’ll want to do is create a separate text file with your ‘connection parameters’, which will look something like this:

dburl=jdbc:[database url]
user=[username]
password=[password]

These are the three pieces of information you might commonly need to connect to a database, but there may be more or less parameters required for your database. It is important to store this information in a separate file ease of keeping the information in one place, as well as for for security, so you can protect the txt file and still have your code available. So once you have this in a text file you can begin creating your connection. We’ll read in your file(we’re no going to cover reading in a text file, just know that it will be stored in a String called paramsFile. Once we have this string we can store it in a new Properties object (a Properties object is a kind of hash table that maps a string to a string, and is designed to be read by certain processes), so that it can be used correctly. But first, in order to read the information from the String it will have to be put through a FileInputStream. Here’s what we have so far:

Properties connectProps = new Properties();
connectProps.load(new FileInputStream(paramsFile));

Before we start building our connection we’re going to start a ‘try’ block, because any connection can become unstable for a variety of reasons, so it’s always important to use error handling. Now we’re going to extract the individual parameters and pass them into a connection:

try{
    String dburl = connectProps.getProperty(“dburl”);
    String username = connectProps.getProperty(“user”);
    String pass = connectProps.getProperty(“password”);
    conn = DriverManager.getConnection(dburl,username,pass);
}catch(Exception e){
    System.out.print(“Connection has Failed”);
}

Of course you can catch a more specific exception if you like, or have a different response, this is just an example of the structure. Now we should have a connection to the database, yay! But wait, how do we use this to make SQL queries? And how do we read the responses? Let’s take a look. The main way to write statements is to use the connection object to .createStatement().
You can then run SQL statements, usually stored as Strings:

String sql = “SELECT * FROM MyTable;”;
stmt = conn.createStatement();
results = stmt.executeQuery(sql); 

There is are tow other useful methods from a statement, .execute and .executeUpdate. .executeUpdate does the same thing but is used for updating or changing the database rather than reading from it. .execute only returns a boolean, indicating if the statement will successfully return a ResultSet or not. If you want to get a little more fancy there is another level of statement, the preparedStatment, which is used for dynamically composing statements. A preparedStatement has most of the functionality of a regular statement, but with more options. For instance you can use variables to fill out particulars in a statement. You need to write the statement ahead of time, but with a “?” In place of each variable. You then use a special method to prepare the statement ahead of time without running it. Once the statement is prepared you can use methods like .setString() or .setInt() depending on what type your variables are. Here we’ll prepare a statement with two variables:

String sql = “SELECT Name FROM Dogs WHERE breed=? AND weight >?;”;
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, breed);
pstmt.setInt(2, weight);
results = pstmt.executeQuery();      

One more important thing to talk about is closing connections and statements. The keen eyed observer might notice that these examples have not been closing statements. If you plan to use a statement to run more queries then that is fine, you don’t need to keep closing and re-opening statements if you don’t need to. But it is good practice to close all your statements and your connection as soon as you are done with them, and at least before terminating your program.

stmt.close();
pstmt.close(
conn.close();