Java Database Connectivity (JDBC) Fundamentals

What is JDBC?

JDBC (Java Database Connectivity) is a set of classes and interfaces written in Java that provide a complete API for programming databases from different vendors (e.g., Microsoft SQL Server, Oracle, MySQL, Interbase, Microsoft Access, IBM DB2, PostgreSQL) using SQL (Structured Query Language).

JDBC performs several key functions:

• Connects to the database (hereafter DB), which can be local (on your PC) or remote (on another PC).
• Sends SQL statements to the database.
• Manipulates the records within the database.
• Reflects the outcome of the execution of SQL statements.

JDBC vs. ODBC: Key Differences

JDBC differs from ODBC (Open Database Connectivity), Microsoft's widely used API for accessing various database providers from multiple platforms. Here are the key distinctions:

• ODBC is written in C.
• The ODBC driver manager and the driver itself must be installed on all client computers.
• JDBC is written entirely in Java, allowing for 100% pure Java applications.
• When using ODBC with large databases, performance may decrease due to conversions between Java and C calls.

JDBC Classes and Methods Explained

JDBC uses the same methods and classes regardless of the specific driver used to connect to a database provider. The only change is the driver name, making it simple to modify applications to switch database vendors. The generic code for connecting to a database involves these steps:

• Register the JDBC driver using the Class.forName() classloader:

  Class.forName("nombre_del_driver");

• Connect to the database using the Connection interface, which opens a session or connection with the specified database. The DriverManager.getConnection() method attempts to select the appropriate JDBC driver registered in the system:

  Connection con = DriverManager.getConnection("BD_url", "user", "password");

• Execute SQL statements. The Statement interface allows you to run SQL statements and retrieve the generated output:

  Statement select = con.createStatement();

• The ResultSet interface represents a set of data resulting from a SQL query. To access the records, it initially points a cursor before the first record, and the resultSet.next() method is used to move through the records. Note that ResultSet is read-only:

  ResultSet names = select.executeQuery("SELECT * FROM table ORDER BY id");

Statement Methods for SQL Queries

The Statement interface provides different methods depending on the type of SQL statement being executed:

• executeQuery(String sql): Returns a ResultSet for SELECT queries.
• executeUpdate(String sql): Executes an INSERT, UPDATE, or DELETE statement, returning the number of affected rows.

Common JDBC Drivers and Connection Strings

JDBC supports various drivers. Some of the most important include:

• Microsoft SQL Server JDBC Driver

  A Type 4 JDBC driver (available for SQL Server 2005 and 2000 from Microsoft) provides database connectivity through the standard JDBC API in J2EE (Java 2 Enterprise Edition).

  Class.forName: com.microsoft.jdbc.sqlserver.SQLServerDriver

  Connection: jdbc:microsoft:sqlserver://<HOST>:<port>;DatabaseName=[dbname];User=[username];Password=[password]

• JDBC-ODBC Bridge Driver

  This bridge allows connection to Microsoft Access databases.

  Class.forName: sun.jdbc.odbc.JdbcOdbcDriver

  Connection: jdbc:odbc:<ruta_a_la_BD>

  Connecting to Microsoft Access Databases

  Connection to Microsoft Access databases can be done in two ways:

  • By setting the System DSN in the ODBC Control Panel:

    /*
     * Connection string: AccessBD is the name configured for the database connection
     * in the ODBC Control Panel.
     */
    String url = "jdbc:odbc:AccessBD";

  • By using the physical path to the database in the connection string (no ODBC Control Panel configuration needed):

    // Connection string with the physical path to the DB
    String db = "D:\\folder\\subfolder\\subfolder\\base_de_datos.mdb";
    // If the database is in the Java application's folder, you can write:
    // String db = "base_de_datos.mdb";
    String url = "jdbc:odbc:MS Access Database;DBQ=" + db;

• MySQL JDBC Driver (MM.MySQL)

  Class.forName: org.gjt.mm.mysql.Driver

  Connection: jdbc:mysql://<HOST>:<port>/<BD>