HOME | INDEX

Starting database programming in Java

Database programming is probably one of the most common tasks that you will encounter in your programming career–in fact, during job interviews, it is an expectation that you know how to deal with the most common databases in use–like for example, MySQL, Oracle, MS SQL Server and many others.

Java database programming can get hairy sometimes when you’re doing really complicated tasks–but understanding the basics is a straightforward process. Most java database programs do the following.

1. Load the driver for the database you are using — most database makers provide a java database driver, mostly these are jar files where the java libraries needed to talk to the database are packaged.
2. Get a Connection object – This is the initial handshake with the database, if the database requires that you enter a username and password, this is also the step where you give the database your security credentials. The Connection object is important because you will need it to produce the other objects you will use later in order to send SQL commands to the database. The Connection object is also responsible if you would like to influence the behavior of autocommit–whether you would like to have more control over the rollback and commit activities of the database
3. Fetching records, Inserting records, modifying records and deleting records — You can use either the Statement object or the PreparedStatement object to perform these tasks.
4. Closing all connections

After you have successfully created the database, you can now try out the code. So Launch your favorite editor and code away.

Sample java database code

Below is a boiler-plate code for a Java program which accesses a database. It uses sqlite3.

You need to prepare the database first and create sample.db using sqlite, lest the code will fail. Here’s how to quickly prepare our sample database.

$ sqlite3 sample.db
SQLite version 3.6.12
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> CREATE TABLE user(username varchar(50), email varchar(200), password varchar(20));
sqlite> insert into user values(‘ted’, ‘ted@fornoobs.info’,'I wont tell you’);
sqlite> select * from user;
ted|ted@fornoobs.info|I wont tell you
sqlite> .quit
$

Class.forName(“org.sqlite.JDBC”) – The reason we are calling this is so that we can load the methods of sqlite database functions, specifically the JDBC class found in org/sqlite package (folder). The JDBC class contains the necessary CLI (Command Level Interface) for the sqlite database, we don’t have to call them natively because org.sqlite.JDBC class is already acting as a wrapper for those calls. You could actually load the sqlite Java driver as well by just calling ‘new org.sqlite.JDBC();’

DriverManager.getConnection() – the getConnection() method returns Connection interface (a Connection type). The Connection object is the one responsible for, well, the connection to the database. It is also via the Connection object that you can adjust how database writes are performed, whether it commits automatically or otherwise.

jdbc:sqlite:sample.db – The parameter for the getConnection() method is the database URL, the database URL is composed of 3 parts and each part is separated by a colon (:). Most java database URLs start with jdbc, the second part will vary depending on which database you are using–you will need to look at the documentation of each jdbc driver for the URL, it will be different if you will use say, MySQL or MS SQL or Oracle.

“SELECT * FROM user;” – This is a standard SQL command which will select all records from the user database defined in sample.db, it’s a good idea to test out the command in the command line of sqlite first before writing it here, especially if the SQL command becomes a bit more complicated than simple selects. You will write the SQL statements as parameter to the executeQuery() method of the Statement object. The .executeQuery() returns a ResultSet object which we can use to process the returned CURSOR–a CURSOR means CURrent Set Of Records, it’s a database term, but for now just imagine that they are pretty much like Excel worksheets, they are composed of rows and columns which contains the user information.

rs.next() – The next() method returns actually does 2 things, first, it moves the location of our database pointer (if you can imagine it) so that it fetches the next row, then it returns true if there are more records to process; when you have reached the last record or there are no more records to process, the .next() method will return false–this is the reason I placed it inside a while loop, the loop will be processed only as long as there are records to process. When there are no more records to read, our while loop will exit.

getString() – The getString() method extracts the the contents of the current record. The numbered parameter inside the getString() indicates which column you would like to fetch. The column count for ResultSet objects actually starts at 1–not zero, you need to remember this because it can be confusing sometimes that array counting starts at zero, that is true for arrays, not true for ResultSet objects–always start counting at 1. You can actually use the field name if you like, so instead of writing getString(1), you can write getString(“username”). One more important thing to mention, I only used getString() because the data type of what I’m trying to read from the database is String (varchar actually, which is a String type), if you were reading a numeric value like an integer, you should use getInt() instead–you need to read the java.sql.ResultSet documentation to have a feel of what kinds of data types you can use.

The example is a bit verbose, but it’s representative of most database programming codes that we do Java.

Notes on exception handling blocks

The methods forName(), getConnection(), createStatement(), executeQuery() and next() throws ClassNotFoundException and SQLException(s) respectively. This is a reason why they are inside a try-catch-finally block, Java won’t allow you to compile if Exceptions are not handled properly.

If you want to show appreciation for my efforts dear reader, you could buy me a tall hazel nut Americano ($2) via PayPal. Thanks

java.fornOObs COPYRIGHT © Ted H.
The Stubborn theme is written by Ted H., it's free if you want it, you can either send me a note or you can can grab it at github