JavaDB:Introduction to JDBC

From Juneday education
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 ( rs.next() ) {
    System.out.println(rs. getString("Name") + " " +
                      (rs. getBoolean("HTTPS") ? "HTTPS support" : "HTTP only") );
  }
} catch (SQLException sqle) {
  System.err.println(sqle.getMessage());
}

Introduction

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.

Requirements

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

Reading one row from a database

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;
BEGIN TRANSACTION;
CREATE TABLE books(title TEXT NOT NULL PRIMARY KEY, pages INTEGER);
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);
COMMIT;

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 BookApplication.java:

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());
      System.exit(1);
    }
  }
  
  // args - not used
  // Compile and run:
  // javac BookApplication.java
  // 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'");
      rs.next(); // 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'");
      rs.next();
      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 BookApplication.java && java -cp ".:sqlite.jar" BookApplication
  • Windows/Cygwin:
    • javac BookApplication.java && java -cp ".;sqlite.jar" BookApplication

Source code: https://github.com/progund/javadb/tree/master/JDBC-intro/basic-example

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. https://raw.githubusercontent.com/progund/javadb/master/JDBC-intro/basic-example/BookApplication.java .

Reading many rows from a database

Now, let's say we are interested in reading all books from the database. We are interested in the complete information about each book (the title and the number of pages). So, the program should list all books, and we call it BookLister

For this, it is convenient to create a class, Book, which let's us create objects representing a single book.

This time, we'll have to do the following steps:

  1. Create a connection
    1. Again in a static block
  2. Create a Statement from the Connection
  3. Issue a select query for all columns of all books, and store the result in a ResultSet
    1. SELECT * FROM books
  4. While the ResultSet has one more row, go to that row
    1. Create a Book from the ResultSet using rs.getString("title") and rs.getInt("pages")
    2. Add the Book to e.g. a List<Book>

Here's the code for the Java program BookLister (in the file BookLister.java):

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class BookLister {

  private static Connection con;

  static {
    try {
      con = DriverManager.getConnection("jdbc:sqlite:books.db");
    } catch (Exception e) {
      System.err.println("Error getting connection: " + e.getMessage());
      System.exit(1);
    }
  }

  public static void main(String[] args) {
    try {
      Statement st = con.createStatement();
      ResultSet rs = st.executeQuery("SELECT * FROM books");

      // Let's store the books in a list
      List<Book> bookShelf = new ArrayList<>();
      
      // We don't know how many rows we get, but we do
      // know that we have to move the cursor to each
      // available row:
      while (rs.next()) { // while rs.next() returns true, there's one more row

        // A book is created with a string title and an int pages:
        Book book = new Book(rs.getString("title"), rs.getInt("pages"));
        bookShelf.add(book);
      }

      System.out.println("The whole book shelf: ");
      System.out.println(bookShelf);
      System.out.println("==========");
      // Books printed on separate lines:
      for (Book aBook : bookShelf) {
        System.out.println(aBook);
      }
    } catch (SQLException e) {
      System.err.println("Database exception: " + e.getMessage());
    }
  }
}

Here's the Book class (in the file Book.java):

public class Book {
  private String title;
  private int pages;

  public Book(String title, int pages) {
    this.title = title;
    this.pages = pages;
  }

  public String title() {
    return title;
  }

  public int pages() {
    return pages;
  }

  @Override
  public String toString() {
    return title + " (" + pages + " pages)";
  }
}

To compile and run:

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

Result of running the program:

$ javac BookLister.java && java -cp .:sqlite.jar BookLister
The whole book shelf: 
[Java for students (300 pages), Java for professors (25 pages), SQL for beginners (150 pages), SQL for professionals (400 pages), Birds - the coolest animals (834 pages)]
==========
Java for students (300 pages)
Java for professors (25 pages)
SQL for beginners (150 pages)
SQL for professionals (400 pages)
Birds - the coolest animals (834 pages)

Lecture slides and videos

English videos

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

Swedish videos

Links

External links

Chapter links

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

« PreviousBook TOCNext »