Understanding JDBC and building database applications with it

Background

JDBC stands for Java database connectivity. This includes connecting to DB in Java, running queries and processing results.

A relational DB that has tables consisting of rows and columns. You can interact with a relational DB with -

• JDBC APIs. You get a connection, create a statement and get result set of the query.
• Use JPA(Java persistence API). This uses a concept called ORM (Object relational mapping) where you map Java objects to tables and operate on these objects. For eg. hibernate is one such framework.
• SQL (Structured query language) is used to interact with the relational DB. 

 In this post we are going to understand JDBC.

Interfaces in JDBC

All JDBC classes in Java are part of java.sql.* package.  There are 4 important interfaces that you need to understand -

1. Driver : Know how to get connection from DB
2. Connection : Knows how to interact with DB
3. Statement : Knows how to run SQL on the DB
4. ResultSet : Knows the result returned by the SQL query from the DB.

 To see a sample Java code on how to connect to a DB from Java you can see one of my previous posts -

• Connect to a database in Java

Above code snippet uses mysql DB but you can use any DB really. There are some common things that we sill see in a moment.

NOTE : You no longer have to explicitly load the driver class using Class.ForName(driver). From JDBC 4 driver class is automatically loaded from the class path. 

Building DB application with JDBC

Let's start by looking at how JDBC url looks like and is constructed -

 As you can see JDBC URL is split into 3 parts -

1. 1st part is the jdbc protocol
2. 2nd part is the name of the DB. For eg. mysql, postgres, derby or oracle
3. 3rd part is respective DB specific format

You have already seem mysql connection string in code above -

• jdbc:mysql://localhost:3306/testDB

 Some other examples are -

• jdbc:postgresql://localhost/testDB
• jdbc:oracle:thin:@192.168.1.45:1699:testDB
• jdbc:derby:testDB

 Once you know the URL first step is to load the DB specific driver. As mentioned before traditionally you needed to explicitly load the driver using -

• Class.ForName(driver)

But since JDBC 4 you don't. Java loads automatically for you if it's present in the classpath.  Once driver is loaded next you need to get the Connection from it. You can do so with -

• Connection conn = DriverManager.getConnection("jdbc:derby:testDB");

 NOTE : If you get exception like "java.sql.SQLException: No suitable driver found for..." then the driver is not present on the classpath. Add it.

Once you have the connection you can get the Statement from it as follows -

• Statement stmt = conn.createStatement();

  Once you have the statement you are all set to execute queries on DB -

• ResultSet rs = stmt.executeQuery("select * from countries");
• int res = stmt.executeUpdate("insert into countries values(1, 'India')");

 NOTE : ResultSet points to a location before 1st row when it is result. To access the data you need to call rs.next() which returns a boolean stating if more result is present. If it does you can access it via rs.getInt(1) etc.

NOTE : Column indexes start with 1. So something like rs.getInt(0)will throw SQL exception.

Once you have processed the result set never forget to close the resources and that include your -

• ResultSet
• Statement
• Connection

NOTE : It is very important to close resources in the right order. If you don't want to close it manually you can always use it under try with resource statements so that Java closes them for you. If doing manually you can close it in finally statement with null checks.

Why do we use a DataSource instead of a DriverManager?

 Always use a datasource over DriverManager as-

Client app need not know about the DB details, username , password. App server will take care of it. With datasource all you need is a jndi name properties of which can be configured at app level.

App server takes care of creating and closing connections. You don’t have to manage it in your client application.

Data source has support for creating pool of connection whereas data manager does not.

 Why do we use a PreparedStatement instead of a Statement

You should always use PreparedStatement instead of a Statement. PreparedStatement is subclass of Statement. This has multiple reasons -

• Performance: A PreparedStatement figures out a plan to run the SQL and remembers it. Helps when same query is run multiple times.
  
  • Databases like sql server or oracle databases  have execution plan cache. They can resue the execution plan while running the same query again. This saved efforts in rebuilding execution plan. However this will work only when the sql query is exactly the same. If it has different values than db treats it as different query. In case of Prepared statements in which we use bind parameters we use placeholders so that the query remains the same and subsequently the execution plan. Bind parameters are hidden to the database query optimizer.
• Security: To prevent SQL injection. It's a famous hacking technique. Go ahead read it up on google.
• Readability: No String concatenations in building queries.

Related Links

• Connect to a database in Java(OSFG) 
• Whats new in Java 8?(OSFG)
• Whats new in Java7?(OSFG) 
• Why do we use a DataSource instead of a DriverManager?(SO) 
• Parameterized queries and bind parameters

Walking a directory using Streams API in java8

Background

In one of the previous posts we saw how we can traverse a directory in Java as part of NIO.2 APIs introduced in Java 8 using walkFileTree method and Path and FileVisitor arguments.

• Java File I/O (NIO.2)

 In this post we will try to do the same thing but with stream APIs introduced in Java8.

Walking a directory using Streams API

In this we will use Files.walk(path) method that returns a Stream<Path> instance and traverse the directories in dept first pattern. This is lazy implementation which mean child directories are not actually loaded until it's parent is traversed.

In previous post we say how we can filter .java files using PathMatcher interface. In this post we will see how easy it is with Streams.

        Path path = Paths.get("/Users/athakur/Desktop/testDir");
        
        try {
            Files.walk(path)
            .filter(p -> p.toString().endsWith(".java"))
            .forEach(System.out::println);
            } catch (IOException e) {
                e.printStackTrace();
            }

and the output is : 

/Users/athakur/Desktop/testDir/innerTestDir1/test2.java
/Users/athakur/Desktop/testDir/innerTestDir1/test3.java
/Users/athakur/Desktop/testDir/innerTestDir2/test4.java
/Users/athakur/Desktop/testDir/test1.java

File structure is as follows -

• testDir
• test1.java
• innerTestDir1
• test2.java
• test3.java
• innerTestDir2
• test4.java

As you can see it does a depth first search.

NOTE : By default the directories depth searched is Integer.MAX_VALUE. Keeping it default and having a deep/large directory structure may take a lot of time to search. So the walk method has an overloaded method that takes in integer parameter (walk(Path,int)) denoting dept of directories to be searched.

NOTE : Unlike earlier NIO.2 methods walk by default will not traverse symbolic links. This is to avoid traversing unnecessary paths or cyclic paths. But if you do wish to track symbolic links you can provide FOLLOW_LINKS option as a vararg to the walk() method. However you should provide a depth is this case to avoid unnecessary traversals. Also walk method keeps track of path traversed and if t detects a loop it will throws FileSystemLoopException.

Files class has other helpful methods as well for searching, listing and printing files -

        try {

            System.out.println("Printing Java regular files");
            Files.find(path, 10, (p, a) -> p.toString().endsWith(".java") && a.isRegularFile())
                    .forEach(System.out::println);
            System.out.println("Printing non directories files");
            // traverses only 1 depth
            Files.list(path).filter(p -> !Files.isDirectory(p)).map(p -> p.toAbsolutePath())
                    .forEach(System.out::println);

        } catch (IOException e) {
            e.printStackTrace();
        }
       

Output :
Printing Java regular files
/Users/athakur/Desktop/testDir/innerTestDir1/test2.java
/Users/athakur/Desktop/testDir/innerTestDir1/test3.java
/Users/athakur/Desktop/testDir/innerTestDir2/test4.java
/Users/athakur/Desktop/testDir/test1.java
Printing non directories files
/Users/athakur/Desktop/testDir/test1.java

NOTE : Notice how Files.list() traverses only 1 depth unlike Files.find() which traverses till the depth provided. Also notice how each method throws IOException since file may not actually be present.

Following picture shows difference between legacy file APIs and NIO2 APIs -

Related Links

• Java File I/O (NIO.2)(OSFG)
• Whats new in Java 8?(OSFG)