Working with Java Database Connectivity

What is JDBC?

Java Database Connectivity (JDBC) is a Java API that is a part of the Java Standard Edition from Oracle. It allows a client to connect with a database and execute queries. These queries are saved into an object meant to be a representation of the table created by the query and easily useable in your Java application. Personally, I don't have much familiarity with other methods for connecting with a relational database in Java but I found JDBC very reliable and easy to use and I would recommend it to any Java developer looking for this functionality.

Setting up a connection

Most of the setup for JDBC is done in the YML file. You only need to a few lines of code to get together everything you'll need to get started querying your database.

database:
   name: SchoolDatabase
   connection: jdbc:sqlserver:path-to-database
   queries:
      students: SELECT * FROM students
      teachers: SELECT * FROM teachers

Here is a sample of what the settings may look like in a YML file. From here, these settings could be referenced in a config file to be used throughout your application. One important note is that you do not need to use a YML file to establish a JDBC connection, but I have found that managing everything is much easier this way and eliminates any instances of D.R.Y. if you have to change a connection string or query.

Now we are ready to start executing queries! First I'll just throw the code at you and then talk about what all the different parts mean.

try {
   Connection conn = DriverManager.getConnection(database.connection, root, password);
   Statement stmt = conn.createStatement();
   ResultSet rs;

   rs = stmt.executeQuery(database.queries.student);
   while ( rs.next() ) {
      String lastName = rs.getString("Lname");
      System.out.println(lastName);
   }
   conn.close();
   } catch (Exception e) {
      System.err.println("Got an exception! ");
      System.err.println(e.getMessage());
  }

So that's it! That is all it takes to both query a database using JDBC and handle the appropriate exception. But there are a lot of things there that you may have never seen before.

Connection: Connection is pretty self explanatory, it represents the connection to the database that we are using. In the YML file, we created the path to the database, however if there is a root or password associated then those will also have to be included as well.

DriverManager: While Connection is meant to represent our connection to the database, the DriverManager is what actually establishes it.

Statement: Now that we have successfully connected to our database, we can use a Statement object to actually run some queries.

ResultSet: We use Statement to run the queries we want and the output comes in the form of a ResultSet. This holds the resulting data in a format similar to a table that we would see in a relational database. We can iterate through every row using rs.next() and in the line String lastName = rs.getString("Lname"); the string "Lname" would be the column header and the value at that column in the current row would be what gets stored in lastName.

Conclusion

This has been a very brief overview of what is possible with JDBC, but there is much more still to be uncovered. In addition to what I showed, you can also call stored procedures and use a RowMapper function to determine how certain rows are added to a ResultSet (similar to a SQL WHERE). I encourage everyone to do more research into this technology if you think that you may use this in your personal or professional development.

Happy Coding!!