Lesson Overview
This lesson introduces databases, SQL, and Java Database Connectivity (JDBC). It covers creating databases and tables, connecting Java programs to a database, and performing basic CRUD operations.
What Is a Database?
A database is an organized collection of data stored so it can be searched, updated, and managed efficiently. A database management system (DBMS) controls access to the database.
Many relational databases use SQL, which stands for Structured Query Language.
What Is JDBC?
JDBC stands for Java Database Connectivity. It is a Java API that allows Java programs to connect to databases, run SQL statements, and process results.
Common JDBC tasks include:
- Connect to a database.
- Create a SQL statement or prepared statement.
- Execute a query or update.
- Read returned records.
- Insert, update, or delete data.
- Close database resources.
Basic JDBC Workflow
A typical JDBC program follows this structure:
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery("SELECT * FROM students");
Important JDBC classes and interfaces:
| JDBC item | Purpose |
|---|---|
DriverManager | Creates a connection to the database |
Connection | Represents the active database connection |
Statement | Runs SQL statements |
PreparedStatement | Runs SQL with placeholders; safer for user input |
ResultSet | Holds query results |
SQLException | Represents database-related errors |
SQL Basics
SQL statements are used to create and manage data.
Common SQL commands:
| Command | Purpose |
|---|---|
CREATE DATABASE | Creates a new database |
DROP DATABASE | Deletes a database |
CREATE TABLE | Creates a table |
DROP TABLE | Deletes a table |
INSERT INTO | Adds records |
SELECT | Retrieves records |
UPDATE | Changes existing records |
DELETE FROM | Deletes records |
Activity: SQL Syntax Examples
Connecting Java to a Database
The exact connection string depends on the database and driver being used. For MySQL, the connection string usually follows this pattern:
jdbc:mysql://localhost:3306/database_name
Before running a JDBC program, make sure the database server is running and the correct JDBC driver is included in the project.
Activity: JDBC Connection Template
Activity: Insert Record with PreparedStatement
Activity: Read Records
Activity: Update Record
Activity: Delete Record
Notes on Safe Database Programming
For user input, prefer PreparedStatement instead of building SQL by concatenating strings. Prepared statements reduce errors and help prevent SQL injection.
Always close database resources. The examples above use try-with-resources, which closes resources automatically.
Laboratory Exercise
Try It: JDBC Flow
A real JDBC connection needs a running database server. This example walks through the steps and simulates the output you would get.