Introduction to JDBC ​

Before introducing JDBC, let's briefly discuss relational databases.

When a program runs, data is stored in memory. Once the program terminates, data usually needs to be saved to disk, whether to a local disk or a server over the network, eventually writing the data to disk files.

Defining the storage format of data is a big issue. If we define the storage format ourselves, for example, to save the grades of all students in a class:

You could save it as a text file, with each student on a separate line, separated by commas:

Michael,99
Bob,85
Bart,59
Lisa,87

Alternatively, you could use JSON format, which is also a text file:

[
    {"name": "Michael", "score": 99},
    {"name": "Bob", "score": 85},
    {"name": "Bart", "score": 59},
    {"name": "Lisa", "score": 87}
]

You can define various storage formats, but this raises some problems:

1. Storage and retrieval must be implemented manually, and while JSON is standardized, custom formats can be quite diverse.
2. It is not possible to perform fast queries. All data must be read into memory for manual traversal, but sometimes the data size far exceeds memory capacity (e.g., a 40GB Blu-ray movie), making it impossible to load all data into memory.

To facilitate data storage and retrieval, and to enable direct conditional queries, databases were developed—software specifically designed for centralized data storage and querying.

The History of Databases ​

The history of database software is long, dating back to the 1950s. It has evolved from network databases and hierarchical databases to relational databases, which became widely used in the 1970s based on the relational model.

The relational model is founded on a complex mathematical theory but is conceptually easy to understand. Consider an example of a school:

Suppose there is an elementary school with three grades. These grades can be represented in an Excel spreadsheet:

Each grade has several classes, which can also be represented in a table:

These two tables have a mapping relationship, where a specific Grade_ID in the Class table corresponds to multiple classes in the Grade table.

In relational databases, this one-to-many relationship between tables forms the foundation.

A query to find all classes in a specific grade could be written as:

SELECT * FROM classes WHERE grade_id = '1';

The result is also presented as a table:

Similarly, a record in the Class table can be linked to multiple records in the Student table.

Note: This tutorial does not cover the details of relational databases. For a thorough learning of relational databases and basic SQL queries, refer to an SQL course.

NoSQL ​

You might have heard of NoSQL databases, which are often touted for their speed and scalability compared to relational databases. Some may wonder if NoSQL can replace SQL altogether. Don't be misled—understanding SQL is essential for grasping NoSQL concepts.

Database Categories ​

Since we are using relational databases, we need to choose one. The most commonly used relational databases are:

Paid Commercial Databases ​

1. Oracle: A high-end option.
2. SQL Server: A Microsoft product tailored for Windows.
3. DB2: An IBM product known for its enterprise use.
4. Sybase: Once a close partner of Microsoft, but has declined in popularity.

These databases are closed-source and require payment, but support is available when issues arise. In the web world, where thousands of database servers may need to be deployed, it’s not practical to spend heavily on licensing. Consequently, companies like Google, Facebook, and domestic giants choose free, open-source databases:

Free Open-Source Databases ​

1. MySQL: Widely used and well-supported.
2. PostgreSQL: Has a more academic focus but is highly capable.
3. SQLite: Suitable for embedded applications.

As a Java developer, MySQL is the best choice due to its widespread adoption and ease of troubleshooting.

Installing MySQL ​

To proceed with the tutorial, download and install MySQL Community Server from the official MySQL website. Choose the appropriate version for your platform.

During installation, you will be prompted to set a password for the root user. Make sure to remember it.

On Windows, choose UTF-8 encoding during installation to handle Chinese characters properly.

On Mac or Linux, edit the MySQL configuration file to set the default encoding to UTF-8:

[client]
default-character-set = utf8

[mysqld]
default-storage-engine = INNODB
character-set-server = utf8
collation-server = utf8_general_ci

After restarting MySQL, check the encoding using the command line:

$ mysql -u root -p
Enter password: 
...

mysql> show variables like '%char%';

If you see utf8, the encoding is set correctly. If MySQL version ≥ 5.5.3, consider using utf8mb4 for full Unicode support, including emojis.

What is JDBC? ​

JDBC stands for Java DataBase Connectivity, a standard interface for accessing databases using Java.

Java applications don't directly connect to databases via TCP; they access databases through the JDBC interface, which is implemented by a JDBC driver.

For example, to access MySQL, you need to write code using the JDBC interface. The JDBC interface is part of the Java standard library, while the JDBC driver is provided by the database vendor.

The architecture ensures that Java code can access various databases via their respective drivers, maintaining uniformity:

Java App
   |
   ▼
JDBC Interface ◀── JDK
   |
   ▼
JDBC Driver ◀──── Vendor
   |
   ▼
Database

In practice, the MySQL JDBC driver is a Java-based .jar file. The Java code references the standard java.sql package, and the driver handles network communication with the MySQL server.

Advantages of Using JDBC ​

1. Unified interface for various databases, with no need for database-specific development.
2. Java programs depend only on the java.sql package, not the specific database driver.
3. The underlying database can be replaced with minimal changes to the code.