Java Database

Connectivity (JDBC)
Advance Programming
JDBC
• JDBC (Java Database Connectivity) API allows Java programs to
connect to databases

• Database access is the same for all database vendors

• The JVM uses a JDBC driver to translate generalized JDBC calls into
vendor specific database calls
Java Database Driver
• Think of a database as just another device connected to your computer
• like other devices it has a driver program to relieves you of having to do
low level programming to use the database
• the driver provides you with a high level api to the database
• Make sure you install the driver along with the DBMS. (Install connectorJ
for MySQL).
• In order to use this driver in your code, you can add the driver in eclipse by
adding an external jar file containing the library.
• i.e. add the jar file of connectorJ in Project properties of Database project.
Adding the JDBC Driver in intellij
IDEA
Open Project Structure:
• Go to File > Project Structure (or press Ctrl+Alt+Shift+S).
Add the Library:
• In the Project Structure window, select Modules from the left sidebar.
• Select your module (usually named the same as your project).
• Click on the Dependencies tab.
• Click the + icon to add a new dependency and choose JARs or directories.
• Navigate to the location where you downloaded the mysql-connector-java-
x.x.x.jar file and select it.
• Click OK and Apply to add the JDBC driver to your project.
Database Driver

DB Client Server
Java
Application
Data Source

JDBC
JDBC Driver
API
Basic steps to use a database in
Java
1. Load the database driver
2. Establish a connection
3. Create JDBC Statements (SQL queries)
4. Execute JDBC statements
5. Use result sets (tables) to navigate through the results
6. Close the connection
Connecting to a Database
• JDBC Driver – MySQL Server
Class.forName (“com.mysql.cj.jdbc.Driver”);
con = DriverManager.getConnection
(“jdbc:mysql://localhost:3306/databasename”, uid, passwd);

• JDBC Driver – Oracle Server

Class.forName (“oracle.jdbc.driver.OracleDriver”);
con = DriverManager.getConnection (
“jdbc:oracle:thin:@bonsai.ite.gmu.edu:1521:ite”,
“accountname", “password”);
Create JDBC statement(s)
• java.sql.Statement
• Statement is an interface in the java.sql package.
• It is used to execute SQL statements without any parameters.
Statement stmt = con.createStatement() ;
• PreparedStatement is a subinterface of Statement in the java.sql
package.
• It is used to execute SQL statements that contain parameters.
• It provides built-in protection against SQL injection attacks.
PreparedStatement preparedStatement = con.prepareStatement(sql);
 Creating Tables
• Creating a Coffee table
CREATE TABLE COFFEES (COF_NAME VARCHAR(32), ID INTEGER, PRICE FLOAT,
SALES INTEGER, TOTAL INTEGER)

• Creating JDBC statements SQL query

Statement stmt = con.createStatement ();
• Execute a statement
stmt.executeUpdate (“CREATE TABLE COFFEES “ + “(COF_NAME VARCHAR(32),
ID INTEGER, PRICE FLOAT, “ + “SALES INTEGER, TOTAL INTEGER)”);
Execute Statements
• This uses executeUpdate because the SQL statement contained in
createTableCoffees is a DDL (data definition language) statement

• Statements that create a table, alter a table, or drop a table are all
examples of DDL statements and are executed with the method
executeUpdate

• executeUpdate is also used to execute SQL statements that update a

table
Execute Statements
• In practice, executeUpdate is used far more often to update tables
than it is to create them because a table is created once but may be
updated many times

• The method used most often for executing SQL statements is

executeQuery

• executeQuery is used to execute SELECT statements, which comprise

the vast majority of SQL statements
Entering Data into a Table

Statement stmt = con.createStatement();

stmt.executeUpdate ( "INSERT INTO COFFEES " +
"VALUES ('Colombian', 101, 7.99, 0, 0)");
stmt.executeUpdate ( "INSERT INTO COFFEES " +
"VALUES ('French_Roast', 49, 8.99, 0, 0)" );
stmt.executeUpdate ( "INSERT INTO COFFEES " +
"VALUES ('Espresso', 150, 9.99, 0, 0)" );
stmt.executeUpdate ( "INSERT INTO COFFEES " +
"VALUES ('Colombian_Decaf', 101, 8.99, 0, 0)" );
stmt.executeUpdate ( "INSERT INTO COFFEES " +
"VALUES ('French_Roast_Decaf', 49, 9.99, 0, 0)" );
Getting Data From a Table

ResultSet rs = stmt.executeQuery ("SELECT COF_NAME, PRICE FROM COFFEES");

while (rs.next())
{
String s = rs.getString ("COF_NAME");
float n = rs.getFloat ("PRICE");
System.out.println (s + " " + n);
}
import java.sql.Connection; Connection connection =
import java.sql.DriverManager; DriverManager.getConnection(url, username, password);
import java.sql.SQLException;
import java.sql.Statement; // Create a statement object
Statement statement = connection.createStatement();
public class CreateTableExample {
public static void main(String[] args) { // Execute the create table query
// MySQL database connection details statement.executeUpdate(createTableQuery);
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "your-username"; System.out.println("Employee table created
successfully!");
String password = "your-password";

// Close the statement and connection

// SQL statement to create the employee table
statement.close();
String createTableQuery = "CREATE TABLE employee ("
connection.close();
+ "id INT PRIMARY KEY AUTO_INCREMENT,"
} catch (SQLException e) {
+ "name VARCHAR(100),"
System.out.println("Error creating employee table: " +
+ "age INT," e.getMessage());
+ "salary DECIMAL(10, 2)" }
+ ")"; }
}
try {
// Establish a connection to the MySQL database
Insert Data
import java.sql.*; statement.setString(2, "John Doe");
statement.setInt(3, 30);
public class InsertDataExample { statement.setString(4, "Manager");
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase"; // Execute the query
String username = "root"; int rowsInserted = statement.executeUpdate();
String password = "password";
if (rowsInserted > 0) {
String insertQuery = "INSERT INTO employees (id, name, System.out.println("Data inserted successfully.");
age, designation) VALUES (?, ?, ?, ?)"; } else {
System.out.println("Data insertion failed.");
try (Connection connection = }
DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
PreparedStatement statement =
connection.prepareStatement(insertQuery)) { e.printStackTrace();
}
// Set values for the parameters }
statement.setInt(1, 1); }
Select Data – read data from
table
import java.sql.*; while (resultSet.next()) {
int id = resultSet.getInt("id");
public class ReadEmployeeData { String name = resultSet.getString("name");
public static void main(String[] args) { int age = resultSet.getInt("age");
// Database connection details String designation = resultSet.getString("designation");
String url =
"jdbc:mysql://localhost:3306/your_database_name"; System.out.println("ID: " + id);
String username = "your_username"; System.out.println("Name: " + name);
String password = "your_password"; System.out.println("Age: " + age);
System.out.println("Designation: " + designation);
// SQL query to select data from the table System.out.println("--------------------------");
String sql = "SELECT * FROM employee"; }

try (Connection connection = } catch (SQLException e) {

DriverManager.getConnection(url, username, password);
e.printStackTrace();
Statement statement = connection.createStatement();
}
ResultSet resultSet = statement.executeQuery(sql)) {
}
}
// Iterate over the result set and retrieve data