Insert, Update, Delete using JDBC and MySQL by G Krishna

Objective: WRITE A JDBC CODE FOR SELECT, INSERT, DELETE OPERATIONS ON A DATABASE.

Dependency: 

<dependency> //  Either Add this in the maven project or add the jar file manually.
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
</dependency>

Structure of Table : ( I am using XAMMP for control penal)

Connection and Select:

package com.lcgk.jdbc; // Set yours,

import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;

//import java.sql.SQLException;

class MysqlCon
{  
public static void main(String args[])
{  
try
	{  
		Class.forName("com.mysql.jdbc.Driver");  
		Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/gk","root","");  //here gk is database name, root is username and password  
		Statement stmt=con.createStatement();  
		ResultSet rs=stmt.executeQuery("select * from login");  
		while(rs.next())  
		System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));  
		con.close();  
	}
catch(Exception e)
	{ 
		System.out.println(e);
	}  
}  
}  

Insertion:

package com.lcgk.jdbc;
import java.sql.*;


	public class InsertionData {

	public static void main(String[] args) {
		        try {  
		            Class.forName("com.mysql.jdbc.Driver");  
		            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/gk", "root", "");  
		            
		            // Creating a PreparedStatement to insert data
		            String insertQuery = "INSERT INTO login (SN, un, pass) VALUES (?, ?, ?)";
		            PreparedStatement pstmt = con.prepareStatement(insertQuery);
		            
		            // Set values for each column
		            pstmt.setInt(1, 3);  // Assuming SN is of type INT
		            pstmt.setString(2, "username");  // Assuming un is of type VARCHAR
		            pstmt.setString(3, "password");  // Assuming pass is of type VARCHAR
		            
		            // Execute the insert statement
		            int rowsInserted = pstmt.executeUpdate();
		            
		            if (rowsInserted > 0) {
		                System.out.println("Data inserted successfully.");
		            } else {
		                System.out.println("Failed to insert data.");
		            }
		            
		            con.close();  
		        } catch(Exception e) { 
		            System.out.println(e);
		        }  
		    }  
		}

Updation :

package com.lcgk.jdbc;
import java.sql.*;

public class UpdationData {

	public static void main(String[] args) {  
        try {  
            Class.forName("com.mysql.jdbc.Driver");  
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/gk", "root", "");  
            
            // Creating a PreparedStatement to update data
            String updateQuery = "UPDATE login SET un = ? WHERE SN = ?";
            PreparedStatement pstmt = con.prepareStatement(updateQuery);
            
            // Set new values for the "un" column and specify the row to update based on the "SN" column
            pstmt.setString(1, "new_username");  // Assuming "new_username" is the new value for the "un" column
            pstmt.setInt(2, 1);  // Assuming you want to update the row where "SN" = 1
            
            // Execute the update statement
            int rowsUpdated = pstmt.executeUpdate();
            
            if (rowsUpdated > 0) {
                System.out.println("Data updated successfully.");
            } else {
                System.out.println("No rows were updated.");
            }
            
            con.close();  
        } catch(Exception e) { 
            System.out.println(e);
        }  
    }  
}

Deletion:

package com.lcgk.jdbc;

import java.sql.*;

public class DeletionData {

	public static void main(String[] args) {
        try {  
            Class.forName("com.mysql.jdbc.Driver");  
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/gk", "root", "");  
            
            // Creating a PreparedStatement to delete data
            String deleteQuery = "DELETE FROM login WHERE SN = ?";
            PreparedStatement pstmt = con.prepareStatement(deleteQuery);
            
            // Set the value for the condition based on which rows will be deleted
            pstmt.setInt(1, 3);  // Assuming you want to delete the row where "SN" = 1
            
            // Execute the delete statement
            int rowsDeleted = pstmt.executeUpdate();
            
            if (rowsDeleted > 0) {
                System.out.println("Data deleted successfully.");
            } else {
                System.out.println("No rows were deleted.");
            }
            
            con.close();  
        } catch(Exception e) { 
            System.out.println(e);
        }  
    }  
}