🗃️ Database & SQL in Java — A Complete Guide
Working with databases is a core part of Java application development. Java provides multiple tools and APIs to connect to databases, run SQL queries, and manage data efficiently.
This guide covers:
Basics of SQL
Connecting Java to a database
JDBC (Java Database Connectivity)
CRUD operations (Create, Read, Update, Delete)
Prepared statements
Connection pooling
ORM frameworks (Hibernate/JPA)
Best practices
1. What Is SQL?
SQL (Structured Query Language) is used to manage and query relational databases such as:
MySQL
PostgreSQL
Oracle
SQL Server
SQLite
Common SQL operations:
CREATE (add new data)
SELECT (retrieve data)
UPDATE (modify data)
DELETE (remove data)
2. How Java Connects to Databases
Java communicates with databases through:
✔️ JDBC (Java Database Connectivity)
A low-level API that allows Java to send SQL commands directly.
✔️ ORM (Object Relational Mapping)
Frameworks like Hibernate / JPA map Java objects to database tables.
3. Using JDBC (Core Method)
JDBC steps:
✔️ Step 1: Add Database Driver
For example, for MySQL (in Maven):
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.33</version>
</dependency>
✔️ Step 2: Establish Connection
String url = "jdbc:mysql://localhost:3306/mydb";
String user = "root";
String password = "password";
Connection con = DriverManager.getConnection(url, user, password);
✔️ Step 3: Create & Execute SQL Statement
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
✔️ Step 4: Process Results
while (rs.next()) {
System.out.println(rs.getString("name"));
}
✔️ Step 5: Close Resources
rs.close();
stmt.close();
con.close();
4. CRUD Operations in JDBC
🔹 Insert (CREATE)
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, "Alice");
ps.setString(2, "alice@example.com");
ps.executeUpdate();
🔹 Read (SELECT)
String sql = "SELECT * FROM users WHERE id = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, 5);
ResultSet rs = ps.executeQuery();
🔹 Update (UPDATE)
String sql = "UPDATE users SET email=? WHERE id=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, "newemail@example.com");
ps.setInt(2, 5);
ps.executeUpdate();
🔹 Delete (DELETE)
String sql = "DELETE FROM users WHERE id=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, 5);
ps.executeUpdate();
5. Why Use PreparedStatement?
✔️ Prevents SQL Injection
Because variables are bound safely.
✔️ Faster for repeated queries
Precompiled by the database.
6. Connection Pooling
Opening database connections repeatedly is slow.
Connection pooling keeps a set of reusable connections.
Popular libraries:
HikariCP (fastest, most common)
Apache DBCP
C3P0
Example (HikariCP):
HikariDataSource ds = new HikariDataSource();
ds.setJdbcUrl(url);
ds.setUsername(user);
ds.setPassword(password);
Connection con = ds.getConnection();
7. ORM Frameworks (Hibernate / JPA)
ORM lets you work with objects instead of SQL.
Example Entity:
@Entity
public class User {
@Id
@GeneratedValue
private Long id;
private String name;
private String email;
}
Save Object (No SQL needed):
User user = new User("Alice", "alice@example.com");
session.save(user);
ORM features:
Automatic table mapping
Query language (JPQL/HQL)
Faster development
Handles complex relationships (One-to-Many, Many-to-Many)
8. Best Practices
✔️ Always use PreparedStatement
Prevents SQL injection.
✔️ Close connections in finally or use try-with-resources
try (Connection con = ds.getConnection()) {
// work
}
✔️ Use connection pooling
Improves performance.
✔️ Validate user input
Do not trust client data.
✔️ Use transactions for multiple queries
con.setAutoCommit(false);
try {
// multiple queries
con.commit();
} catch (Exception e) {
con.rollback();
}
✔️ For large apps, prefer ORM + JPA
Cleaner and faster development.
Summary
Concept Meaning
JDBC Direct SQL in Java
ORM Java objects mapped to DB tables
PreparedStatement Secure, efficient SQL execution
Connection Pool Reusable DB connections
SQL Language for database operations
JPA/Hibernate Popular ORM frameworks
Learn Full Stack JAVA Course in Hyderabad
Read More
Testing REST APIs with Postman
Integrating Thymeleaf with Spring Boot
Exception Handling in Spring Boot
Spring Boot Annotations You Must Know
Visit Our Quality Thought Institute in Hyderabad
Subscribe by Email
Follow Updates Articles from This Blog via Email
No Comments