Connecting Java to a MySQL Database — ODBC and JDBC

Most real programs must store and fetch data from a database. In this course the database is MySQL (a relational database that keeps data in tables). A Java program does not talk to the database directly; it uses a connectivity layer — a piece of software that carries requests to the database and brings results back.

ODBC and JDBC — the two connectivity standards

• ODBC (Open Database Connectivity) is a general, language-independent standard for connecting any application to any database through a driver. It is common in office/desktop tools.
• JDBC (Java Database Connectivity) is Java's own API for database access. It is a set of classes and interfaces (in the package java.sql) that lets Java programs run SQL commands on a database. Because it is built for Java, JDBC is the natural choice for Java programs; ODBC can also be used (through a JDBC-ODBC bridge) but JDBC with a MySQL driver is the standard, modern way.

The five steps of a JDBC program

Connecting to MySQL from Java always follows the same sequence:

1. Load the driver — the software that knows how to talk to MySQL.
2. Establish a connection — open a link to the database, giving its address, username and password.
3. Create a statement — an object used to send SQL.
4. Execute the SQL — run a query (SELECT) or an update (INSERT/UPDATE/DELETE) and read the results.
5. Close the connection to free resources.

import java.sql.*;                       // JDBC classes live here

public class ShowStudents {
    public static void main(String[] args) throws Exception {
        // 1. load the MySQL driver
        Class.forName("com.mysql.cj.jdbc.Driver");

        // 2. establish a connection (address, user, password)
        Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/school", "root", "pass123");

        // 3. create a statement object
        Statement st = con.createStatement();

        // 4. execute a query; results come back in a ResultSet
        ResultSet rs = st.executeQuery("SELECT name, marks FROM student");
        while (rs.next()) {              // move row by row
            System.out.println(rs.getString("name") + " - " + rs.getInt("marks"));
        }

        // 5. close the connection
        con.close();
    }
}

Key JDBC objects