JDBC in Java

JDBC (Java Database Connectivity) is the standard Java API for connecting to relational databases and running SQL. With JDBC, a Java program can open a connection to a database, send SQL statements such as SELECT and INSERT, and read the results. The API lives in the packages java.sql and javax.sql, which are part of the JDK. JDBC is database-independent: you write to the same interfaces whether the database is MySQL, PostgreSQL, Oracle or SQL Server, and a driver supplied by each database vendor does the real work. Frameworks such as Hibernate, Spring Data and MyBatis are all built on top of JDBC. In this lesson, you will learn why JDBC exists, how its parts fit together, and run your first query.

Why JDBC?

Every database speaks its own network protocol. Without a common API, a program written for MySQL would have to be rewritten for Oracle. JDBC solves this with one rule: your code talks to interfaces; the driver implements them.

The JDBC Architecture

The Main Interfaces and Classes

Example: A First Query

Let's see an example that creates a table, inserts three students and reads them back:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
public class FirstQuery {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:h2:mem:school";
        try (Connection con = DriverManager.getConnection(url);
             Statement st = con.createStatement()) {
 
            st.execute("CREATE TABLE student ("
                + "id INT PRIMARY KEY, name VARCHAR(50), marks INT)");
            int rows = st.executeUpdate("INSERT INTO student VALUES "
                + "(1, 'Navika', 91), (2, 'Srihitha', 84), (3, 'Advitha', 78)");
            System.out.println(rows + " rows inserted");
 
            String sql = "SELECT id, name, marks FROM student "
                + "ORDER BY marks DESC";
            try (ResultSet rs = st.executeQuery(sql)) {
                while (rs.next()) {
                    System.out.println(rs.getInt("id") + "  "
                        + rs.getString("name") + "  " + rs.getInt("marks"));
                }
            }
        }
    }
}

3 rows inserted
1  Navika  91
2  Srihitha  84
3  Advitha  78

Explanation:

1. The URL jdbc:h2:mem:school says "use the H2 driver, an in-memory database named school". For MySQL it would look like jdbc:mysql://localhost:3306/school.
2. DriverManager.getConnection(url) finds a driver that understands the URL and opens a Connection.
3. createStatement() gives an object that can send SQL.
4. executeUpdate runs SQL that changes data and returns how many rows were affected: 3.
5. executeQuery runs a SELECT and returns a ResultSet. rs.next() moves to the next row and returns false when there are no more; getInt and getString read a column of the current row.
6. try-with-resources closes the ResultSet, the Statement and the Connection, in that order.

JDBC Versions

The current version is 4.3.

Common Mistakes

Forgetting the driver JAR. JDBC is in the JDK, but the driver isn't. Without it you get "No suitable driver found".

Not closing connections. Each one uses resources in the database; leaks eventually stop the application from connecting at all.

Building SQL by joining strings with user input. This allows SQL injection; use PreparedStatement.

Interview Corner

Practice

1. Easy: Add a fourth student to FirstQuery and print only students with marks above 80.
2. Harder: Change the query to print the average marks using SELECT AVG(marks) FROM student, reading the value with getDouble(1).

Key Takeaways