Program 14 : JDBC CRUD Operations

Implement a Java program to connect to a database using JDBC, perform basic CRUD operations, and handle database connectivity.

Revised Code

// DbConnect.java

// import java.sql.*;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DbConnect {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/exam";
        String user = "mcauser1";
        String password = "msrit@2024";
        
		// DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
        
        // Use try-with-resources for automatic resource management
        try ( Connection conn = DriverManager.getConnection(url, user, password);
             Statement st = conn.createStatement()) {
            
            System.out.println("Connection successful!");
            
            // 1. Start Transaction by disabling auto-commit
            conn.setAutoCommit(false);
            System.out.println("--- Transaction started. ---");
            
            try {
                // Display initial table values
                System.out.println("\nInitial State of 'job' table:");
                displayJobs(conn);
                
                // 2. Perform Operations using Statement
                String updateSQL = "UPDATE job SET Area='Bangalore' WHERE EmpNo=124";
                int rowsUpdated = st.executeUpdate(updateSQL);
                System.out.println("\nRows updated: " + rowsUpdated);

                String deleteSQL = "DELETE FROM job WHERE EmpNo=128";
                int rowsDeleted = st.executeUpdate(deleteSQL);
                System.out.println("Rows deleted: " + rowsDeleted);

                // Display changed values before rollback
                System.out.println("\nState of 'job' table before rollback:");
                displayJobs(conn);

                // 3. Rollback the Transaction
                System.out.println("\n--- Rolling back changes... ---");
                conn.rollback();

                System.out.println("\nFinal state of 'job' table after rollback:");
                displayJobs(conn);

            } catch (SQLException e) {
                System.err.println("ransaction failed. Rolling back...");
                // Rollback changes if any error occurs
                conn.rollback();
                e.printStackTrace();
            }

        } catch (SQLException e) {
            System.err.println("Database connection or operation failed!");
            e.printStackTrace();
        }
    }

    // Helper method to display table contents to avoid code repetition
    private static void displayJobs(Connection conn) throws SQLException {
        String selectSQL = "SELECT EmpNo, Email, Area FROM job";
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery(selectSQL)) {
            
            boolean found = false;
            while (rs.next()) {
                found = true;
                // Use column names for better readability
                System.out.printf("EmpNo=%s, Email=%s, Area=%s%n",
                        rs.getString("EmpNo"),
                        rs.getString("Email"),
                        rs.getString("Area"));
            }
            if (!found) {
                System.out.println("No records found.");
            }
        }
    }
}

When the Jar file in in the same directory.

javac -cp ".;mysql-connector-j-9.4.0.jar" DbConnect.java

use : in Linux, in Windows ; to separate path.

---

In Java, the "path" for libraries is called the classpath. The JAR file doesn't need to be in the same folder as your .java program file. You just need to tell Java where to find it when you compile and run your code.

You use the -cp (or -classpath) flag to tell Java where to find your JAR files.

my_project/
├── lib/
│   └── mysql-connector-j.jar
└── src/
    └── DbConnect.java

1. To Compile (javac):

You need to include both the JAR file and the directory where your compiled .class files will go (we'll use the current directory, .).

# From inside the 'my_project' folder
# Format: javac -cp "path/to/jar:." path/to/source/file.java
javac -cp "lib/mysql-connector-j.jar:." src/DbConnect.java

2. To Run (java):

You must include the JAR and the directory containing your compiled .class files (in this case, they are now in the my_project folder).

# Format: java -cp "path/to/jar:." YourMainClass
java -cp "lib/mysql-connector-j.jar:." DbConnect

Using an IDE (Recommended)

All major IDEs handle the classpath automatically. You just need to add the JAR to the project's build path.

• IntelliJ IDEA: Right-click your project -> Open Module Settings -> Libraries -> Click + -> Java -> Select your JAR file.

• Eclipse: Right-click your project -> Build Path -> Configure Build Path... -> Libraries tab -> Add External JARs... -> Select your JAR file.

• VS Code: In the "JAVA PROJECTS" explorer view -> Referenced Libraries -> Click the + icon and select your JAR file.

After doing this, when you click the "Run" button, the IDE automatically constructs the correct java -cp ... command for you.

Previous Code

// DbConnect.java

import java.sql.*;

public class DbConnect
{
  public static void main(String args[])
  {
	try
	{    // Load the driver class
		// Class.forName("com.mysql.cj.jdbc.Driver");
		
		DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());

		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/exam","mcauser1","msrit@2024");
											//"root","Toor@2024"

     		System.out.println("Connected");

     		Statement st = conn.createStatement();

	// Display the table values
     		ResultSet rs = st.executeQuery("select * from job");
     		while(rs.next())
     		{
     		    System.out.println("EmpNo=" + rs.getString(1) + ", Email =" + rs.getString(2) );
      		}

	// Starting Transaction	
		String startTransaction = "start Transaction";
		st.executeUpdate(startTransaction);
	
	// Update A Value
		String updateSQL = "UPDATE job SET Area='Bangalore' WHERE EmpNo=124";
		int rowsUpdated = st.executeUpdate(updateSQL);
		System.out.println("Rows updated :" + rowsUpdated);	

	// Delete a Row
		String deleteSQL = "DELETE FROM job WHERE EmpNo=128";
		int rowsDeleted = st.executeUpdate(deleteSQL);
		System.out.println("Rows deleted :" + rowsDeleted);

	// Display changed values
     		ResultSet rs1 = st.executeQuery("select * from job");
     		while(rs1.next())
     		{
     		    System.out.println("EmpNo=" + rs1.getString(1) + ", Email =" + rs1.getString(2) );
      		}

	// Rollback the Changes
		String endTransaction = "rollback";
		st.executeUpdate(endTransaction);
		
  	}
	catch(SQLException s)
	{
     		System.out.println(s);
	}
  }
}