Table of Contents (Click any Topic to view first)
- JDBC (Java Database Connectivity)
- Connecting to Databases
- Executing SQL Queries
- Handling Database Transactions
JDBC is a Java API that allows Java programs to interact with relational databases. It provides methods for querying and updating data in a database and handling transactions. Here's an overview of JDBC concepts:
To connect to a database using JDBC, you need to follow these steps:
1. Load the JDBC driver: You need to load the appropriate JDBC driver for your database. The driver class implements the JDBC interfaces and provides methods for connecting to the database.
2. Establish a connection: Use the DriverManager.getConnection() method to establish a connection to the database. You need to provide the URL of the database, username, and password as parameters to this method.
Example:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnection {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "username";
String password = "password";
try {
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println("Connected to the database");
// Use the connection for executing queries or updates
} catch (SQLException e) {
System.out.println("Connection failed");
e.printStackTrace();
}
}
}
Once you have established a connection to the database, you can execute SQL queries using Statement or PreparedStatement objects.
Example with Statement:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLQuery {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "username";
String password = "password";
try (Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement()) {
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
System.out.println("Name: " + name + ", Age: " + age);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
A database transaction is a sequence of operations that must be executed as a single unit of work. JDBC provides methods for managing transactions using Connection objects.
Example:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TransactionExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "username";
String password = "password";
try (Connection connection = DriverManager.getConnection(url, username, password)) {
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
statement.executeUpdate("INSERT INTO users (name, age) VALUES ('John', 30)");
statement.executeUpdate("UPDATE users SET age = 31 WHERE name = 'John'");
connection.commit();
System.out.println("Transaction committed successfully");
} catch (SQLException e) {
System.out.println("Transaction failed");
e.printStackTrace();
}
}
}
In this example, we disable auto-commit mode using connection.setAutoCommit(false). Then, we execute multiple SQL statements within a single transaction and commit the transaction using connection.commit(). If any statement fails, we can roll back the transaction using connection.rollback().
Conclusion
JDBC is a powerful API for Java developers to interact with relational databases. By understanding JDBC concepts such as connecting to databases, executing SQL queries, and handling transactions, developers can build robust and efficient database-driven applications in Java.
