Simplifying CRUD Operation with JDBC - JAVA DEVELOPMENT Training By Om Sir

Steps For Connectivity Between Java Program and Database

Import the Packages
Load the drivers using the forName() method
Register the drivers using DriverManager
Establish a connection using the Connection class object
Create a statement
Execute the query
Close the connections

example to Check database is connected or not :-

Connect.java file code :-

import java.sql.*;  
class Connect{  
public static void main(String args[]){  
try{  
Class.forName("com.mysql.jdbc.Driver");  
Connection con=DriverManager.getConnection(  
"jdbc:mysql://localhost:3306/user","root","");  
//here user is database name, root is username and password  
if(!con.isClosed())
{
System.out.println("data base connected");
}
else{
System.out.println("not connected");

}
 
con.close();  
}
catch(Exception e){ System.out.println(e);}  
}  
}  

Example 1:-

// This code is for establishing connection with MySQL

// database and retrieving data

// from db Java Database connectivity

*1. import --->java.sql

*2. load and register the driver ---> com.jdbc.

*3. create connection

*4. create a statement

*5. execute the query

*6. process the results

*7. close

import java.io.*;

import java.sql.*;

class GFG {

public static void main(String[] args) throws Exception

{

String url

= "jdbc:mysql://localhost:3306/table_name"; // table details

String username = "roOT"; // MySQL credentials

String password = "";

String query

= "select *from students"; // query to be run

Class.forName(

"com.mysql.cj.jdbc.Driver"); // Driver name

Connection con = DriverManager.getConnection(

url, username, password);

System.out.println(

"Connection Established successfully");

Statement st = con.createStatement();

ResultSet rs

= st.executeQuery(query); // Execute query

rs.next();

String name

= rs.getString("name"); // Retrieve name from db

System.out.println(name); // Print result on console

st.close(); // close statement

con.close(); // close connection

System.out.println("Connection Closed....");

}

Example 2:-

// Java Program to Establish Connection in JDBC

// Importing database

import java.sql.*;

// Importing required classes

import java.util.*;

// Main class

class Main {

// Main driver method

public static void main(String a[])

{

// Creating the connection using Oracle DB

// Note: url syntax is standard, so do grasp

String url = "jdbc:oracle:thin:@localhost:1521:xe";

// Username and password to access DB

// Custom initialization

String user = "system";

String pass = "12345";

// Entering the data

Scanner k = new Scanner(System.in);

System.out.println("enter name");

String name = k.next();

System.out.println("enter roll no");

int roll = k.nextInt();

System.out.println("enter class");

String cls = k.next();

// Inserting data using SQL query

String sql = "insert into student1 values('" + name

+ "'," + roll + ",'" + cls + "')";

// Connection class object

Connection con = null;

// Try block to check for exceptions

try {

// Registering drivers

DriverManager.registerDriver(

new oracle.jdbc.OracleDriver());

// Reference to connection interface

con = DriverManager.getConnection(url, user,

pass);

// Creating a statement

Statement st = con.createStatement();

// Executing query

int m = st.executeUpdate(sql);

if (m == 1)

System.out.println(

"inserted successfully : " + sql);

else

System.out.println("insertion failed");

// Closing the connections

con.close();

}

// Catch block to handle exceptions

catch (Exception ex) {

// Display message when exceptions occurs

System.err.println(ex);

}

Creating, reading, updating, and deleting data in a database is a common task in many applications, and JDBC (Java Database Connectivity) is a Java API that allows you to connect to a database and perform these operations. In this blog post, we will walk through the steps of setting up a simple CRUD (create, read, update, delete) operation using JDBC.

1. Connect to the database

The first step is to establish a connection to the database. You can do this by loading the JDBC driver and creating a connection object.

try {

Class.forName("com.mysql.jdbc.Driver");

Connection con = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/mydb", "username",

"password");

System.out.println("Connection established.");

}

catch (Exception e) {

e.printStackTrace();

}

2. Create a new record

Once you have a connection to the database, you can use the connection object to create a new record in the database. To do this, you will need to use an SQL INSERT statement and execute it using the connection object.

try {

String sql = "INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)";

PreparedStatement statement = con.prepareStatement(sql);

statement.setString(1, "value1");

statement.setString(2, "value2");

statement.setInt(3, 123);

statement.executeUpdate();

System.out.println("Record created.");

} catch (SQLException e) {

e.printStackTrace();

}

3. Read a record

To read a record from the database, you will need to use an SQL SELECT statement and execute it using the connection object. The result of the query will be a ResultSet object that you can use to access the data in the record.

try {

String sql = "SELECT column1, column2, column3 FROM table_name WHERE id = ?";

PreparedStatement statement = con.prepareStatement(sql);

statement.setInt(1, 1);

ResultSet result = statement.executeQuery();

if (result.next()) {

String column1 = result.getString("column1");

String column2 = result.getString("column2");

int column3 = result.getInt("column3");

System.out.println("Column 1: " + column1);

System.out.println("Column 2: " + column2);

System.out.println("Column 3: " + column3);

}

} catch (SQLException e) {

e.printStackTrace();

}

4. Update a record

To update a record in the database, you will need to use an SQL UPDATE statement and execute it using the connection object.

try {

String sql = "UPDATE table_name SET column1 = ?, column2 = ?, column3 = ? WHERE id = ?";

PreparedStatement statement = con.prepareStatement(sql);

statement.setString(1, "new_value1");

statement.setString(2, "new_value2");

statement.setInt(3, 456);

statement.setInt(4, 1);

statement.executeUpdate();

System.out.println("Record updated.");

} catch (SQLException e) {

e.printStackTrace();

}

5. Delete a record

To delete a record from the database, you will need to use an SQL DELETE statement and execute it using the connection object.

try {

String sql = "DELETE FROM table_name WHERE id = ?";

PreparedStatement statement = con.prepareStatement(sql);

statement.setInt(1, 1);

statement.executeUpdate();

System.out.println("Record deleted.");

} catch (SQLException e) {

e.printStackTrace();

}