Using Java to Connect to a Database

So far, we've looked at how to interact directly with the database using SQL. 0:00 Few users care to interact directly with data and 0:05 we probably don't want them to anyway. 0:07 So we'll need a way to write an application that accesses a database 0:10 using Java. 0:14 Exposing the features that we, as application designers and 0:15 developers, see fit. 0:18 In Java, the standard medium through which a database is accessed is called JDBC. 0:21 JDBC stands for Java Database Connectivity and 0:27 consists of a detailed interface that database vendors. 0:30 Such as SQLite, which we'll use here, should implement. 0:33 This API is contained in the java.sql and javax.sql packages and 0:37 is included in any Java Standard Edition Environment. 0:41 Like the one we have here. 0:46 Because it provides a lightweight and portable file based solution for 0:48 databases, we'll be using SQLite in this particular demo. 0:51 Let's switch to workspaces to connect to a SQLite database in Java. 0:55 I'm in the workspace provided for this video and 1:00 looking at the directory structure, you can see that it has three items. 1:02 The first is a Contact class which we'll return to in a bit. 1:06 The next is the JdbsMain class where we`ll be writing our code. 1:10 And the last is a SQLite jar file. 1:14 This jar file is the vendor specific implementation 1:17 of JDBC that I was referring to earlier. 1:20 It's also called the driver. 1:23 In my JdbcMain class you'll see a main method with a bunch of comments 1:25 begging for your code. 1:29 Let's not wait any longer. 1:31 Under this first to do comment we'll load the proper class into the JVM. 1:33 We'll do this by calling the static forName method in the class Class. 1:37 And the name of the class is org.sqlite.jdbc. 1:44 This will come from that jar file that we'll include on the class path. 1:48 Really all this line of code does is initialize any static behavior. 1:52 If you look at the code of org.sqlite.jdbc.java, which I have 1:57 linked to in the teacher's notes, you'll see a static call to the driver managers. 2:01 Register Driver method. 2:06 This will create a new JDBC object which will now be used for 2:08 any communication with the SQLite database. 2:11 Next, we'll attempt to get a connection to the database. 2:13 You notice that this piece is inside a try catch. 2:17 Right here, 2:21 this is because any interaction with our database may throw a SQL exception, 2:23 including connecting to the database as well as running queries on it. 2:27 So we catch the exception and proceed accordingly. 2:31 You can scroll down to reveal that catch block right there. 2:35 Let's try to establish a database connection, 2:39 so I'll replace null with the following. 2:41 DriverManager.getConnection. 2:44 And then I'll specify jdbc:sqlite: and 2:50 I'm gonna name this database contactmgr, 2:54 abbreviated for Contact Manager, .db. 2:58 Here, we use the try with resources convention, 3:04 by initializing the connection in parentheses after try. 3:08 In these parentheses we can initialize something that implements the auto 3:13 closable interface. 3:17 Which means it has implemented a close method. 3:19 This close method will be called as soon as the try block is done executing. 3:22 Now, since the connection interface extends auto closable, we can use it here. 3:27 Pretty convenient. 3:33 The string specified as a parameter in the getConnection method 3:34 is what's called a connection string. 3:39 Here, you can see that jdbc will be used to connect to a sqlite database. 3:42 The driver manager will know to use the sqlite jdbc class we provided 3:48 because of the prefix jdbc:sqlite. 3:53 Now, I'll save this file here and 3:58 we can test our database connection by compiling and running JdbcMain. 4:01 Let's do that now. 4:06 First, we'll compile with the standard javac command, 4:07 so javac.JdbcMain .java. 4:13 This should generate the byte code for the jvm contained in JdbcMain.class. 4:16 If it doesn't pop up in the side bar, you can right-click the project and 4:21 choose Refresh. 4:25 And then, we see the byte code file that was generated 4:25 as a result of compiling JdbcMain.class. 4:29 And next, we'll run the class with the java command. 4:34 java JdbcMain. 4:37 What we see here is a ClassNotFoundException. 4:43 And in this case it happens on line 13 if I go back up to my 4:48 source code I see this right here is line 13.. 4:52 Now, we get this exception because the class we want to load is contained in 4:58 the jar file, that org.sqlite.jdvc class is contained in the jar file. 5:02 The sqlite jar file right here. 5:09 But we haven't added the jar file to the class path. 5:12 You'll recall from previous courses that we can use the CP option on the command 5:15 line while compiling, or while running, to specify which path or 5:19 paths should be included on the class path. 5:24 Let's run this class with the sqlite jar file contained in the class path. 5:27 Let me clear my console first and I'll use the Java command and here's the CP or 5:33 class path option. 5:38 And I can specify the entire name of that jar file to include it on 5:39 the class path, followed by a colon and a .JdbcMain. 5:44 Now, notice the colon and dot that I used there. 5:49 The colon allows us to specify multiple paths. 5:52 The two paths being the jar file and the dot or the current directory. 5:54 After running this command, as long as we haven't made any coding errors, we should 6:00 be able to refresh the workspace directory and see that a new file has been created. 6:04 And there it is, contactmgr.db. 6:10 This is the single file database that is created by SQLite 6:14 since one didn't exist already. 6:17 So we've now verified the creation of a database. 6:20 Let's create the same contacts table that we did earlier. 6:23 And add a couple rows. 6:26 We can start by creating a java.sql.statement object and 6:28 then execute updates as we please. 6:33 So I'll declare a statement object. 6:36 And create a statement from the connection. 6:40 We'll start by creating the database table with 6:46 the statement.executeUpdate call and 6:49 inside here, I am going to first drop the table. 6:56 So that running this code multiple times doesn't create an issue, 7:02 I will use the drop table if it already exists. 7:06 So drop table if exists contacts. 7:10 Then, I'll create the contacts table just like we did in the SQL playground. 7:15 But again, using the executeUpdate call. 7:19 So here we have CREATE TABLE contacts. 7:26 I'll put this all on one line since Java doesn't support multiline strings. 7:31 So I wanna create an ID that is the integer primary key. 7:36 Let me fix my casing there. 7:40 SQL's case sensitive but I like to keep consistency here. 7:42 That's our primary key. 7:50 We have a first name which is a STRING. 7:51 A last name which is a STRING. 7:56 An email which is a STRING. 8:00 And a phone which is a ten digit integer. 8:03 Great. 8:08 Now, let's add a couple rows to the table just like we did earlier. 8:09 So under this to do comment, I'll do a statement.executeUpdate. 8:13 And this is where I'll drop my INSERT INTO command. 8:21 So remember specify the column names first. 8:29 first name, last name, email, and phone. 8:32 And then, the values, which in this case will be my first and last name. 8:39 Do my email address here, feel free to use your own information. 8:51 And then, a phone number, 773-555 8:56 is always a great phone number, 4535, cool. 9:02 I'm gonna copy and paste it just like I did earlier to save myself some typing. 9:08 I'll throw James Gosling back in there as a nod to Java. 9:13 Let's capitalize that G there. 9:20 Of course, his fake email address james@java.com. 9:25 5555557788. 9:32 Good enough. 9:36 Because we won't see the results upon inserting them, 9:37 let's run a select statement fetching all rows from the contacts table. 9:40 Since this query will return results, 9:44 we store the return value into what's called a ResultSet. 9:46 So I'll create that here. 9:51 ResultSet all abbreviated rs= statement.execute. 9:52 Now, instead of update, we'll say executeQuery. 9:58 And we'll list our SQL statement here. 10:02 I'll select all columns from the contacts table. 10:05 Notice that these SQL statements are identical to what we used in 10:10 the SQL playground. 10:13 It's just that now, we're using Java to actually execute them. 10:14 So next we want to display the results. 10:18 So I'll introduce here the standard way of iterating over a SQL ResultSet. 10:20 We use a standard while loop calling rs.next. 10:25 So I'll do that under the next TO DO comment. 10:30 while(rs.next) opening and closing curly braces. 10:35 A SQL ResultSet is kind of like a music app which not only stores your music but 10:43 also remembers what song you were last listening to 10:47 as well as exactly which part of the song you were on. 10:50 In terms of database results, the ResultSet hangs on to the data returned 10:54 as well as maintaining what's called a cursor, which remembers 10:58 which result you're currently working with kinda like a Java iterator. 11:03 A Java iterator is usually used when defining a custom collection class that 11:07 you want to be able to iterate over using a for-each loop. 11:12 In order to be able to use a for-each loop on an object, 11:16 that object must have implemented the iterable interface. 11:19 Which requires that you code an iterator, managing its position or 11:23 cursor as repeated calls are made to the next method to advance the cursor or 11:27 to the hasNext method to see if any more elements exist. 11:32 Here with the result set calling RS.next moves the cursor to the next position. 11:37 Which, in our case, 11:43 is the next row in the contacts table that came back from our select statement. 11:44 Now, inside the loop, we can grab column values of the row 11:50 the cursor is pointing at by calling methods like getInt or getString. 11:55 Let's do that now. 12:01 Inside this loop, I wanna grab the int id and the string first name and last name. 12:02 So I'm going to make variables to hold those values. 12:08 int id, String firstName, and String lastName. 12:10 To get the id of the result set's current row that it's pointing at, 12:17 I use the resultset.getInt and 12:23 specify the column name that contains that value, id, in this case. 12:27 And then, I can do the same for firstName, except in this case, 12:31 it's going to be getString and again, the column name. 12:36 I'll do the same for lastName. 12:42 Finally, I'll display a formatted line of output that contains the firstName, 12:48 lastName and id in parentheses. 12:53 So I'll use System.out.printf and 12:55 this will be the placeholder for the firstName, placeholder for 12:59 the lastName and in parentheses a placeholder for the integer id. 13:02 firstName, lastName and id. 13:07 Now, let's compile and run this application. 13:13 So I'll compile it using javac JbdcMain.java. 13:17 Looks like all compiled well, so 13:24 let's execute this as a Java application, specifying the classpath so 13:27 that we can list the jar file to be included on the classpath. 13:32 Then, JdbcMain. 13:36 And there are our results, though, 13:39 my display could probably use a little better formatting. 13:41 Though this barely scratches a surface of what you can do with JDBC, it gives you 13:45 an idea of one of the most basic ways to connect to a database using Java. 13:49