Java Database Connectivity (JDBC)

The Java JDBC API enables Java applications to connect to relational databases via a standard API, so your Java applications become independent (almost) of the database the application uses.

JDBC standardizes how to connect to a database, how to execute queries  against it, how to navigate the result of such a query, and how to exeucte  updates in the database. JDBC does not standardize the SQL sent to the  database. This may still vary from database to database. The JDBC API consists of the following core parts: JDBC Drivers Connections Statements Result Sets There are four basic JDBC use cases around which most JDBC work evolves: Query the database (read data from it). Query the database meta data. Update the database. Perform transactions. JDBC Drivers: A JDBC driver is a collection of Java classes that enables you to connect to a  certain database. For instance, MySQL will have its own JDBC driver. A JDBC  driver implements a lot of the JDBC interfaces. When your code uses a given  JDBC driver, it actually just uses the standard JDBC interfaces. The concrete  JDBC driver used is hidden behind the JDBC interfaces. Thus you can plugin a  new JDBC driver without your code noticing it. Of course, the JDBC drivers may vary a little in the features they support. Connections: Once a JDBC driver is loaded and initialized, you need to connect to the  database. You do so by obtaining a Connection to the database via the JDBC  API and the loaded driver. All communication with the database happens via  a connection. An application can have more than one connection open to a  database at a time. This is actually very common. Statements: A Statement is what you use to execute queries and updates against the  database. There are a few different types of statements you can use. Each  statement corresponds to a single query or update. ResultSets: When you perform a query against the database you get back a ResultSet.  You can then traverse this ResultSet to read the result of the query. Common JDBC Use Cases: Query the database: One of the most common use cases is to read data from a database. Reading  data from a database is called querying the database. Update the database: Another very common JDBC use case is to update the database. Updating the  database means writing data to it. In other words, adding new records or  modifying (updating) existing records. Perform transactions: Transactions is anoter common use case. A transaction groups multiple  updates and possibly queries into a single action. Either all of the actions are  executed, or none of them are. Database Connectivity: Before you can read or write data in a database via JDBC, you need to open a  connection to the database. This text will show you how to do that. Loading the JDBC Driver: The first thing you need to do before you can open a database connection is  to load the JDBC driver for the database. Actually, from Java 6 this is no  longer necessary, but doing so will not fail. You load the JDBC driver like this: Class.forName("driverClassName"); Each JDBC driver has a primary driver class that initializes the driver when it  is loaded. For instance, to load the Mysql driver, you write this: Class.forName("com.mysql.jdbc.Driver"); You only have to load the driver once. You do not need to load it before every connection opened. Only before the first connection opened. Opening the Connection: To open a database connection you use the java.sql.DriverManager class. You  call its getConnection() method, like this: Connection connect = DriverManager .getConnection("jdbc:mysql://localhost/feedback?user=sqluser&password=yourpassword"); The url is the url to your database. You should check the documentation for  your database and JDBC driver to see what the format is for your specific  database. The url shown above is for a H2Database. The user and password parameters are the user name and password for your  database. Closing the Connection: Once you are done using the database connection you should close it. This is  done by calling the Connection.close() method, like this: connection.close(); Querying a database means searching through its data. You do so be sending QL statements to the database. To do so, you first need a connection which we did above. Once you have an open  connection, you need to create a Statement object, like this: Statement statement = connection.createStatement(); Once you have created the Statement you can use it to execute SQL queries, like this: String sql = "select * from employee"; ResultSet result = statement.executeQuery(sql); When you execute an SQL query you get back a ResultSet. The ResultSet  contains the result of your SQL query. The result is returned in rows with  columns of data. You iterate the rows of the ResultSet like this: while(result.next()) { String name = result.getString("name"); long age = result.getLong ("age"); } The ResultSet.next() method moves to the next row in the ResultSet, if there  are anymore rows. If there are anymore rows, it returns true. If there were no  more rows, it will return false. You need to call next() at least one time before you can read any data. Before the first next() call the ResultSet is positioned before the first row. You can get column data for the current row by calling some of the getXXX()  methods, where XXX is a primitive data type. For instance: result.getString ("columnName"); result.getLong ("columnName"); result.getInt ("columnName"); result.getDouble ("columnName"); result.getBigDecimal("columnName"); etc. The column name to get the value of is passed as parameter to any of these  getXXX() method calls. You can also pass an index of the column instead, like this: result.getString (1); result.getLong (2); result.getInt (3); result.getDouble (4); result.getBigDecimal(5); etc. For that to work you need to know what index a given column has in the  ResultSet. You can get the index of a given column by calling the  ResultSet.findColumn() method, like this: int columnIndex = result.findColumn("columnName"); If iterating large amounts of rows, referencing the columns by their index  might be faster than by their name. When you are done iterating the ResultSet you need to close both the  ResultSet and the Statement object that created it (if you are done with it,  that is). You do so by calling their close() methods, like this: result.close(); statement.close(); Of course you should call these mehtods inside a finally block to make sure  that they are called even if an exception occurs during ResultSet iteration.  Please refer try-with-resources section in post Exception Handling. Update Database: In order to update the database you need to use a Statement. But, instead of  calling the executeQuery()method, you call the executeUpdate() method. There are two types of updates you can perform on a database: Update record values Delete records The executeUpdate() method is used for both of these types of updates. Updating Records: Here is an update record value example: Statement statement = connection.createStatement(); String sql = "update people set name='John' where id=123"; int rowsAffected = statement.executeUpdate(sql); The rowsAffected returned by the statement.executeUpdate(sql) call, tells  how many records in the database were affected by the SQL statement. Deleting Records: Here is a delete record example: Statement statement = connection.createStatement(); String sql = "delete from people where id=123"; int rowsAffected = statement.executeUpdate(sql); Again, the rowsAffected returned by the statement.executeUpdate(sql) call,  tells how many records in the database were affected by the SQL statement. Let's put all together including connecting and querying the database in an  example below. import java.sql.*; public class JavaMysqlDemo { public static void main(String[] args) throws ClassNotFoundException { String myUrl = "jdbc:mysql://localhost:3306"; Class.forName("com.mysql.jdbc.Driver"); // create our mysql database connection and create the java statement try (Connection conn = DriverManager.getConnection(myUrl, "root", "password"); Statement st = conn.createStatement()) { // our SQL SELECT query. // if you only need a few columns, specify them by name instead of using "*" String query = "SELECT * FROM sugarcrm.accounts;"; // execute the query, and get a java resultset try (ResultSet rs = st.executeQuery(query)) { // iterate through the java resultset while (rs.next()) { String id = rs.getString("id"); String name = rs.getString("name"); String dateEntered = rs.getString("date_entered"); Date dateModified = rs.getDate("date_modified"); // print the results System.out.format("%s, %s, %s, %s,\n", id, name, dateEntered, dateModified); } } } catch (SQLException e) { e.printStackTrace(); } } }

Exception Handling

What is an exception?

An Exception can be anything which interrupts the normal flow of the program. When an exception occurs program processing gets terminated and doesn’t continue further. In such cases we get a system generated error message. The good thing about exceptions is that they can be handled. We will cover the handling part later in this same tutorial.

Exception can occur at runtime (known as runtime exceptions or Unchecked Exception) as well as at compile-time (known Compile-time exceptions or Checked Exceptions).

Reasons for Exceptions:

There can be several reasons for an exception. For example, following situations can cause an exception – Opening a non-existing file, Network connection problem, Operands being manipulated are out of prescribed ranges, class file missing which was supposed to be loaded and so on.

Advantages of Exception Handling:

Exception handling allows us to control the normal flow of the program by using exception handling in program. It throws an exception whenever a calling method encounters an error providing that the calling method takes care of that error.

It also gives us the scope of organizing and differentiating between different error types using a separate block of codes. This is done with the help of try-catch blocks.

Why to handle exception?

If an exception is raised, which has not been handled by programmer then program execution can get terminated and system prints a non user friendly error message.

Ex:-Take a look at the below system generated exception.

An exception generated by the system is given below Exception in thread "main" 

java.lang.ArithmeticException: / by zero at ExceptionDemo.main(ExceptionDemo.java:5)

ExceptionDemo : The class name main : The method name ExceptionDemo.java : 

The filename java:5 : Line number

For a novice user the above message won’t be easy to understand. In order to let them know that what went wrong we use exception handling in java program. We handle such conditions and then prints a user friendly warning message to user, which lets them correct the error as most of the time exception occurs due to bad data provided by user.

Types of exceptions

There are two types of exceptions

1. Checked exceptions
2. Unchecked exceptions

Checked exceptions:

All exceptions other than Runtime Exceptions are known as Checked exceptions as the compiler checks them during compilation to see whether the programmer has handled them or not. If these exceptions are not handled/declared in the program, it will give compilation error.

Examples of Checked Exceptions,

• ClassNotFoundException
• IllegalAccessException
• NoSuchFieldException
• EOFException etc.

Unchecked Exceptions:

Runtime Exceptions are also known as Unchecked Exceptions as the compiler do not check whether the programmer has handled them or not but it’s the duty of the programmer to handle these exceptions and provide a safe exit.

These exceptions need not be included in any method’s throws list because compiler does not check to see if a method handles or throws these exceptions.

Examples of Unchecked Exceptions:

• ArithmeticException
• ArrayIndexOutOfBoundsException
• NullPointerException
• NegativeArraySizeException etc.

Exception hierarchy:

Exception handling in Java:

Exceptions can be handled in 2 different ways.

• try-catch
• throw 
• throws

There is a huge difference between these 2 methods- the latter swallows the exception (showing it, admittedly), whereas the first one will let it propagate.

So if you call the first method and "do something" fails, then the caller will have to handle the exception and it can be raw exception which may be easily understandable. If you call the second method and "do something" fails, then the caller may or may not see an exception. But the good thing about it is you can handle it in nice manner and throw meaningful exception that the caller understands it. If you will not throw the caught exception then caller cannot see this exception at all.

Let's explore more about these exception handling types.

Throwing Exceptions ( throw ):

If a method needs to be able to throw an exception, it has to declare the exception(s) thrown in the method signature, and then include a throw-statement in the method. 

Here is an example: 

  public void divide(int numberToDivide, int numberToDivideBy) throws BadNumberException {
    if (numberToDivideBy == 0) {
      throw new BadNumberException("Cannot divide by 0");
    }
    return numberToDivide / numberToDivideBy;
  }

When an exception is thrown the method stops execution right after the "throw" statement. Any statements following the "throw" statement are not executed. In the example above the "return numberToDivide / numberToDivideBy;" statement is not executed if a BadNumberException is thrown. The program resumes execution when the exception is caught somewhere by a "catch" block. Catching exceptions is explained later.

You can throw any type of exception from your code, as long as your method signature declares it. You can also make up your own exceptions. Exceptions are regular Java classes that extends java.lang.Exception, or any of the other built-in exception classes. If a method declares that it throws an exception A, then it is also legal to throw subclasses of A.

Catching Exceptions (try-catch ):

If a method calls another method that throws checked exceptions, the calling method is forced to either pass the exception on, or catch it. Catching the exception is done using a try-catch block.

Here is an example:

public void callDivide() {
 try {
  int result = divide(2, 1);
  System.out.println(result);
 } catch(BadNumberException e) { //do something clever with the exception 
  System.out.println(e.getMessage());

 }
 System.out.println("Division attempt done");
}

The BadNumberException parameter e inside the catch-clause points to the exception thrown from the divide method, if an exception is thrown.

If no exception is thrown by any of the methods called or statements executed inside the try-block, the catch-block is simply ignored. It will not be executed.

If an exception is thrown inside the try-block, for instance from the divide method, the program flow of the calling method, callDivide, is interrupted just like the program flow inside divide. The program flow resumes at a catch-block in the call stack that can catch the thrown exception. In the example above the "System.out.println(result);" statement will not get executed if an exception is thrown fromt the divide method. Instead program execution will resume inside the "catch (BadNumberException e) { }" block.

If an exception is thrown inside the catch-block and that exception is not caught, the catch-block is interrupted just like the try-block would have been.

When the catch block is finished the program continues with any statements following the catch block. In the example above the "System.out.println("Division attempt done");" statement will always get executed.


Propagating Exceptions ( throws ):

You don't have to catch exceptions thrown from other methods. If you cannot do anything about the exception where the method throwing it is called, you can just let the method propagate the exception up the call stack to the method that called this method. If you do so the method calling the method that throws the exception must also declare to throw the exception.

For example if you are trying to read a file with FileReader class, it will ask you to handle FileNotFoundException. It propagate this exception you have to throw it like below,

public void readFile() throws FileNotFoundException  {
    FileReader reader = new FileReader("C:\\temp\\file.txt");

    System.out.println("Reading the file");
}

The program execution is interrupted if the file is not really found in the specified location in the filesystem. is thrown from the divide method. Thus the "System.out.println("Reading the file");". The exception is propagated to the method that calls readFile. Program execution doesn't resume until a catch-block somewhere in the call stack catches the exception. All methods in the call stack between the method throwing the exception and the method catching it have their execution stopped at the point in the code where the exception is thrown or propagated.

Additional to these exception handling Java also provide a way of closing the resources that we use and ensures there are no leaks. To handle the closing of resources effectively Java provides try-catch with resources, which is useful in closing the resources.


try-catch with resources:


The try-with-resources statement is a try statement that declares one or more resources. A resource is an object that must be closed after the program is finished with it. The try-with-resources statement ensures that each resource is closed at the end of the statement. Any object that implements java.lang.AutoCloseable, which includes all objects which implement java.io.Closeable, can be used as a resource.

The following example reads the first line from a file. It uses an instance of BufferedReader to read data from the file. BufferedReader is a resource that must be closed after the program is finished with it:

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;

public class StreamsDemo {

  public static void main(final String args[]) throws IOException {

    try (BufferedReader reader = new BufferedReader(new FileReader("C:\\temp\\input-file.txt"));
        BufferedWriter writer = new BufferedWriter(new FileWriter("C:\\temp\\output-file.txt"))) {
      String line;
      while ((line = reader.readLine()) != null) {
        writer.write(line);
      }
    }
  }
}

In this example, the resource declared in the try-with-resources statement is a BufferedReader. The declaration statement appears within parentheses immediately after the try keyword. The class BufferedReader, in Java SE 7 and later, implements the interface java.lang.AutoCloseable. Because the BufferedReader instance is declared in a try-with-resource statement, it will be closed regardless of whether the try statement completes normally or abruptly (as a result of the method BufferedReader.readLine throwing an IOException).

try-catch-finally:

You can attach a finally-clause to a try-catch block. The code inside the finally clause will always be executed, even if an exception is thrown from within the try or catch block. If your code has a return statement inside the try or catch block, the code inside the finally-block will get executed before returning from the method. Here is how a finally clause looks:

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;

public class StreamsDemo {

  public static void main(final String args[]) throws IOException {
    BufferedReader reader = null;
    try {
      reader = new BufferedReader(new FileReader("C:\\temp\\input-file.txt"));
      String line;
      while ((line = reader.readLine()) != null) {
        System.out.println(line);
      }
    } catch (final IOException e) {
      // do something clever with the exception
    } finally {
      if (reader != null) {
        try {
          reader.close();
        } catch (final IOException e) {
          // do something clever with the exception
        }
      }
      System.out.println("--- File End ---");
    }
  }

}

No matter whether an exception is thrown or not inside the try or catch block the code inside the finally-block is executed. The example above shows how the file reader is always closed, regardless of the program flow inside the try or catch block.

Note: If an exception is thrown inside a finally block, and it is not caught, then that finally block is interrupted just like the try-block and catch-block is. That is why the previous example had the reader.close() method call in the finally block wrapped in a try-catch block.

Multi-Threading

Mutithreading, Parallel execution or Concurrency in other words is nothing but “spawning” (creating) multiple Threads and each Thread is carrying out the work that your application is meant to do. one Thread can only do one particular task at a time. This is a bottleneck if your entire application just runs on one Thread right?

Java multithreading allows you to do multiple tasks at the same time. This is possible because modern day computers have multiple CPUs. One CPU can work on one Thread at a time (unless your CPUs have hyper-threading, in which case it can handle two at a time). So this means that if your computer has 4 CPUs with hyper-threading technologies, your code could potentially handle 8 Threads at the same time.

Java Thread is a lightweight process that executes some task. Java provides multithreading support with the Thread class and an application can create multiple threads executing concurrently.


There are 2 types of Threads available in Java,

User threads: are threads which are created by the application or user. They are high priority threads. JVM (Java Virtual Machine) will not exit until all user threads finish their execution. JVM wait for these threads to finish their task. These threads are foreground threads.

Daemon threads: are threads which are mostly created by the JVM. These threads always run in background. These threads are used to perform some background tasks like garbage collection and house-keeping tasks. These threads are less priority threads. JVM will not wait for these threads to finish their execution. JVM will exit as soon as all user threads finish their execution. JVM doesn’t wait for daemon threads to finish their task.

When we start an application, main is the first user thread created and we can create multiple user threads as well as daemon threads. When all the user threads are executed, JVM terminates the program.

We can set different priorities to different Threads but it doesn’t guarantee that higher priority thread will execute first than lower priority thread. Thread scheduler is the part of Operating System implementation and when a Thread is started, it’s execution is controlled by Thread Scheduler and JVM doesn’t have any control on it’s execution.

There are 3 ways which you can create a Java Thread. Let's explore one by one.

By Extending Java Thread class:

import java.lang. * ;
public class WorkerThread extends Thread {

        @Override
 public void run() {....
 }
}

By Implementing Java Thread class:

import java.lang. * ;
public class WorkerThread implements Thread {

        @Override
 public void run() {....
 }
}

One liner statement to create new Thread:

Thread t = new Thread(new Runnable(){
    @Override
    public void run() {
    }
});

You can chose the right way of creating a thread based on your need. The only difference between the first two methods is that in second method you are implementing Runnable interface. So that it gives you a greater flexibility to your class WorkerThread to extend some other class. But this is not possible with first method as you cannot extend multiple classes.

Finally the 3rd method is one liner implementation means single time use only. It is not reusable as like first 2 methods. Go for the the 3rd method if it is only one time use.

Let's see some examples of creating threads and using them.

Creating thread with extending Thread class:

public class WorkerThread extends Thread {

  @Override
  public void run() {
    System.out.println("The user created thread Id is: " + Thread.currentThread().getId());
  }

  public static void main(final String[] args) {

    System.out.println("This is the main thread with Id:" + Thread.currentThread().getId());
    final WorkerThread thread = new WorkerThread();
    thread.start();
  }
}

Output:

This is the main thread with Id:1
The user created thread Id is: 11

Creating thread with implementing Runnable:

public class RunnableThread implements Runnable {

  @Override
  public void run() {
    System.out.println("The user created thread Id is: " + Thread.currentThread().getId());
  }

  public static void main(final String[] args) {

    System.out.println("This is the main thread with Id:" + Thread.currentThread().getId());
    final RunnableThread runnable = new RunnableThread();
    final Thread thread = new Thread(runnable);
    thread.start();
  }
}

Output:
This is the main thread with Id:1
The user created thread Id is: 11

If you observe above 2 methods of creating a thread is slightly different. In first method we created a class which extends a Java Thread class and we are creating and instance to out class and starting the thread with start() method which is used to start the thread. I will explain this later. But in 2nd method of implementation we are implementing Runnable interface with our own class and creating an instance for that class. But that is not enough here. We have to create an instance of a Thread class which takes our Runnable implementation class as a constructor argument. Then only you can start the thread.


In-line thread creation:

public class InLineThread {

  public static void main(final String[] args) {

    System.out.println("This is the main thread with Id:" + Thread.currentThread().getId());
    final Thread thread = new Thread(new Runnable() {

      @Override
      public void run() {
        System.out.println("The user created thread Id is: " + Thread.currentThread().getId());
      }
    });
    thread.start();
  }
}

Output:
This is the main thread with Id:1
The user created thread Id is: 11

You can create inline threads anywhere inside a method. Above is a one liner statement to create new Thread, Here we are implementing Runnable as an Anonymous Class. This implementation is not reusable.

Note: If you want reusable threads to use multiple times anywhere in your application prefer first 2 ways of creating a thread.

Now, we know how to create a Thread in Java. Let's see some important things to know about threads.


Pausing Execution with Sleep:

Thread.sleep is a method from Thread class which takes milliseconds as an argument causes the current thread to suspend execution for a specified period. This is an efficient means of making processor time available to the other threads of an application or other applications that might be running on a computer system. This method always throw InterruptedException. You can either throw it or catch it. 

import java.util.Date;

public class ThreadsDemo {

  public static void main(final String[] args) throws InterruptedException {

    System.out.println("Started execution at : " + new Date());
    // sleep for 2 seconds
    Thread.sleep(2000);
    System.out.println("Finished execution at : " + new Date());
  }
}

Output:
Started execution at : Mon Jul 17 12:20:40 BST 2017
Finished execution at : Mon Jul 17 12:20:42 BST 2017

Starting a Thread:

start() method is responsible for starting a thread. Even though the thread is created, it is not executed until you explicitly invoke it by start method except main thread. Please refer above examples.

Stopping a thread:

There was a stop() method in Thread class, when Java was first released but that was deprecated later.Thread termination is not so straight forward. A running thread, may own files and sockets. It may hold locks. Abrupt Termination is not always easy: Unpredictable consequences may arise if the thread is in the middle of writing to a file and is killed before it can finish writing. Or what about the monitor locks held by the thread when it is shot in the head?

But we can create our own stop method which is very simple. There are many ways that you can stop a thread but all of them take specific code to do so. A typical way to stop a thread is to have a volatile boolean field that the thread checks every so often:

public class Server implements Runnable {

  private volatile boolean stop;

  public static void main(final String[] args) throws InterruptedException {

    final Server server = new Server();
    final Thread thread = new Thread(server);
    // Start the server thread
    thread.start();
    // Keep server started for 5 seconds
    Thread.sleep(5000);
    // Stop the server
    server.stop();
  }

  @Override
  public void run() {
    System.out.println("Starting server");
    // This checks if the thread has to be stopped
    while (!stop) {
      System.out.println("Server is running");
      try {
        // Sleep for a second every time before checking to stop
        Thread.sleep(1000);
      } catch (final InterruptedException e) {
      }
    }
    System.out.println("Server stopped");
  }

  // Method to stop the server thread
  public void stop() {
    this.stop = true;
  }

}

Output:
Starting server
Server is running
Server is running
Server is running
Server is running
Server is running
Server stopped

In above example, we are starting the server and keeping it alive for 5 seconds and then stopping it. Server is running in a separate thread than main thread and we are controlling server thread from main thread. Means you can increase server life by increase the sleep time in the main thread and stop wherever needed after completing necessary work with server..