Never confuse education with intelligence, you can have a PhD and still be an idiot.
- Richard Feynman -

JavaDB:Introduction to JDBC

From Juneday education
Revision as of 16:29, 8 March 2019 by Rikard (Talk | contribs) (A complete example: Added code and explanation)

Jump to: navigation, search

Full frontal - Code up-front

We don't expect you to understand the following code, but believe that it's always good to be exposed to code early on.

try (Statement stm = con.createStatement() ) { // try-with-resources
  String query = "SELECT Name, HTTPS FROM municipalities LIMIT 5";
  ResultSet rs = null;
  rs = stm. executeQuery(query);

  while ( ) {
    System.out.println(rs. getString("Name") + " " +
                      (rs. getBoolean("HTTPS") ? "HTTPS support" : "HTTP only") );
} catch (SQLException sqle) {


This chapter introduces you to the basics of using the JDBC API for using databases with Java.

We will introduce some of the interfaces in java.sql like:

  • Connection
  • Statement
  • ResultSet

And some of the classes:

  • DriverManager
  • SQLException

We encourage you to read the external resources (further reading links) below, after watching the video.


In order to fully understand this chapter, we assume you have basic knowledge of SQL and SQLite. If you feel that you need to freshen up on SQL, we recommend our book Introduction to Databases.

A complete example

Let's start with a very small and simplified example. Say that we have a small database with books. Each book has a title and a number of pages.

Mission: We want to write a Java program which gets data about books from the database and compares the number of pages of two books.

Problem: We need to get the number of pages as a Java int value, so that we can use the > and < operators for comparing int values.

Solution: Use JDBC to connect to the database, create and execute an SQL query, get a result set back, and get the pages value from the result as a Java int.

Here's the database:

PRAGMA foreign_keys=OFF;
INSERT INTO "books" VALUES('Java for students',300);
INSERT INTO "books" VALUES('Java for professors',25);
INSERT INTO "books" VALUES('SQL for beginners',150);
INSERT INTO "books" VALUES('SQL for professionals',400);
INSERT INTO "books" VALUES('Birds - the coolest animals',834);

We have created the SQLite3 database called books.db using the SQL above from the file books.sql:

$ sqlite3 books.db < books.sql

We have downloaded the SQLite JDBC driver (a JAR file needed in order to talk to the SQLite database software) in the file sqlite.jar.

We have created the following Java program in

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.DriverManager;

public class BookApplication {

  private static Connection con;

  static {
    try {
      con = DriverManager.getConnection("jdbc:sqlite:books.db");
    } catch (Exception e) {
      System.err.println("Error getting connection: " + e.getMessage());
  // args - not used
  // Compile and run:
  // javac
  // on Mac OS or GNU/Linux:
  // java -cp ".:sqlite.jar" BookApplicaion
  // on Windows/Cygwin:
  // java -cp ".;sqlite.jar" BookApplication
  public static void main(String[] args) {
    try { // we can get SQLException when dealing with JDBC...
      // How many pages does the book "Java for students" have?
      Statement st = con.createStatement();
      ResultSet rs = st.executeQuery("SELECT pages FROM books WHERE title='Java for students'");; // Move cursor to the first row in the result (even if there's only one row)
      // to get a value from the result set into Java, we can choose
      // the type we want it as. The getInt() method gives us an int!
      int javaPages = rs.getInt("pages");
      System.out.println("The Java book has " + javaPages + " pages.");

      rs = st.executeQuery("SELECT pages FROM books WHERE title='SQL for beginners'");;
      int sqlPages = rs.getInt("pages");
      System.out.println("The SQL book has " + sqlPages + " pages.");

      // Good that we have the pages as int-variables!
      // now we can do this:
      if (javaPages > sqlPages) {
        System.out.println("The Java book has more pages.");
      } else if (sqlPages > javaPages) {
        System.out.println("The SQL book has more pages.");
      } else {
        System.out.println("Both books have the same number of pages.");
    } catch (SQLException e) {
      System.err.println("Database exception: " + e.getMessage());

Note the steps:

  1. Create the Connection to the database, using the class DriverManager
    1. This is done in a static block, to ensure it has happened before the program starts
  2. Use the Connection to create a Statement
  3. Use the Statement to execute a query, and save the result in a ResultSet
  4. Move the cursor of the ResultSet to the first row
  5. Use the method call getInt("pages") to get an int with the value of the pages column from the row of the ResultSet, and store it in a regular Java int variable
  6. Use the int variable as you would in any typical Java program

What you achieved here, is to connect to an existing SQLite3 database from Java, issue a SELECT statement, and from the result set get a value from a named column of a row as a Java type (in this case int).

To compile and run:

  • Mac OS/GNU/Linux
    • javac && java -cp ".:sqlite.jar" BookApplication
  • Windows/Cygwin:
    • javac && java -cp ".;sqlite.jar" BookApplication

Source code:

Note that you must click on the file you want to download, then on "Raw", to get the URL for downloading the file, e.g. .

Lecture slides and videos

English videos

No English videos yet, but we are planning to make some soon!

Swedish videos


External links

Chapter links

After this chapter you should move on to the Exercise - Introduction to JDBC chapter.

« PreviousBook TOCNext »