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



Difference between revisions of "JavaDB:Introduction to JDBC"

From Juneday education
Jump to: navigation, search
(Requirements: Fixed link)
(A complete example: Example listing all books)
 
(8 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
=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.
 +
 +
<source lang="Java">
 +
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());
 +
}
 +
</source>
 
=Introduction=
 
=Introduction=
 
This chapter introduces you to the basics of using the JDBC API for using databases with Java.
 
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=
 
=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]].
 
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 <code>int</code> value, so that we can use the <code>></code> and <code><</code> 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 <code>pages</code> value from the result as a Java <code>int</code>.
 +
 +
Here's the database:
 +
<source lang="SQL">
 +
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;
 +
</source>
 +
 +
We have created the SQLite3 database called <code>books.db</code> using the SQL above from the file <code>books.sql</code>:
 +
<pre>
 +
$ sqlite3 books.db < books.sql
 +
</pre>
 +
 +
We have downloaded the SQLite JDBC driver (a JAR file needed in order to talk to the SQLite database software) in the file <code>sqlite.jar</code>.
 +
 +
We have created the following Java program in <code>BookApplication.java</code>:
 +
<source lang="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());
 +
    }
 +
  }
 +
}
 +
</source>
 +
Note the steps:
 +
# Create the Connection to the database, using the class DriverManager
 +
## This is done in a <code>static</code> block, to ensure it has happened before the program starts
 +
# Use the Connection to create a Statement
 +
# Use the Statement to execute a query, and save the result in a ResultSet
 +
# Move the cursor of the ResultSet to the first row
 +
# Use the method call <code>getInt("pages")</code> to get an <code>int</code> with the value of the pages column from the row of the ResultSet, and store it in a regular Java <code>int</code> variable
 +
# Use the <code>int</code> 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 <code>int</code>).
 +
 +
To compile and run:
 +
* Mac OS/GNU/Linux
 +
** <code>javac BookApplication.java && java -cp ".:sqlite.jar" BookApplication</code>
 +
* Windows/Cygwin:
 +
** <code>javac BookApplication.java && java -cp ".;sqlite.jar" BookApplication</code>
 +
 +
Source code: [https://github.com/progund/javadb/tree/master/JDBC-intro/basic-example 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 <code>BookLister</code>
 +
 +
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:
 +
# Create a connection
 +
## Again in a static block
 +
# Create a Statement from the Connection
 +
# Issue a select query for all columns of all books, and store the result in a ResultSet
 +
## <code>SELECT * FROM books</code>
 +
# While the ResultSet has one more row, go to that row
 +
## Create a Book from the ResultSet using <code>rs.getString("title")</code> and <code>rs.getInt("pages")</code>
 +
## Add the Book to e.g. a <code>List<Book></code>
 +
 +
Here's the code for the Java program <code>BookLister</code> (in the file <code>BookLister.java</code>):
 +
<source lang="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());
 +
    }
 +
  }
 +
}
 +
</source>
 +
Here's the <code>Book</code> class (in the file <code>Book.java</code>):
 +
<source lang="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)";
 +
  }
 +
}
 +
</source>
 +
 +
To compile and run:
 +
* Mac OS / GNU/Linux
 +
** <code>javac BookLister.java && java -cp ".:sqlite.jar" BookLister</code>
 +
* Windows/Cygwin
 +
** <code>javac BookLister.java && java -cp ".;sqlite.jar" BookLister</code>
 +
 +
Result of running the program:
 +
<pre>
 +
$ 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)
 +
</pre>
  
 
=Lecture slides and videos=
 
=Lecture slides and videos=
 +
==English videos==
 
No English videos yet, but we are planning to make some soon!
 
No English videos yet, but we are planning to make some soon!
* [https://vimeo.com/194838475 Introduktion till JDBC 1/3 (Swedish)]
 
* [https://vimeo.com/194838509 Introduktion till JDBC 2/3 (Swedish)]
 
* [https://vimeo.com/194838527 Introduktion till JDBC 3/3 (Swedish)]
 
  
* Lecture slides (PDF) [[:Media:L10 Accessing databases from application.pdf|Accessing databases from Java]]
+
==Swedish videos==
 +
* [https://vimeo.com/couchmode/channels/1416386 Java DB - Introduktion till JDBC (Full playlist)] | [https://vimeo.com/194838475 Java DB - Introduktion till JDBC 1/3] | [https://vimeo.com/194838509 2/3] | [https://vimeo.com/194838527 3/3] | Lecture slides (English, PDF) [[:Media:L10 Accessing databases from application.pdf|Accessing databases from Java]]
  
 
=Links=
 
=Links=
Line 20: Line 270:
 
==Chapter links==
 
==Chapter links==
 
After this chapter you should move on to the [[JavaDB:Exercise - Introduction to JDBC|Exercise - Introduction to JDBC]] chapter.
 
After this chapter you should move on to the [[JavaDB:Exercise - Introduction to JDBC|Exercise - Introduction to JDBC]] chapter.
 +
 +
{{Nav links|prev=More_programming_with_Java#Database_and_JDBC_related|TOC=More_programming_with_Java#Database_and_JDBC_related|next=JavaDB:Exercise - Introduction to JDBC}}

Latest revision as of 17:49, 8 March 2019

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 »