DBClassCode

package dboperationdemo;
import java.sql.*;
import java.util.Scanner;

public class DBOperationDemo {
    static Connection conn;
    static Statement stmt;
    static DatabaseMetaData metadata = null;


    public static void main(String[] args) {
        loadDriver();
        createConnection();
        createStatement();
        //insertData();
        //readData();
        //System.out.println("Data after update operation.................");
        //updateData();
        //System.out.println("Data after delete operation.................");
        //deleteData();
        //createTable("AllStudents");
        System.out.println("Data with MetaData");
        readDataWithMetadata();
        System.out.println();
        readDataByeCourseName();
    }
    static void loadDriver(){
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("Driver loaded successfully....!!");
        }
        catch (Exception ex){
            System.out.println("Driver load ERROR: " + ex);
        }
    }
    static void createConnection(){
        try{
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "***********");
            System.out.println("Connection established....!!");
        }
        catch(SQLException ex){
            System.out.println("Connection  ERROR: " + ex);
        }
    }

    static void createStatement(){
        try{
        stmt = conn.createStatement();
        System.out.println("Statement created successfully....!!! ");
        }
        catch(SQLException ex){
            System.out.println("Statement  ERROR: " + ex);
        }
    }
    static void insertData(){
        String insertQuery = "insert into course values (88, 'MySQL')";
        try{
            stmt.executeUpdate(insertQuery);
             System.out.println("Data inserted.....!!");
        }
        catch(SQLException ex){
             System.out.println("Insertion  ERROR: " + ex);
        }
        }
    static void readData(){
        String readQuery = "select * from course";
        try{
            ResultSet rs = stmt.executeQuery(readQuery);
            while(rs.next()){
                String cName = rs.getString(2);
                int cid = rs.getInt(1);

                System.out.println("Course ID = " + cid + ", Course Name = " + cName);
            }

        }
        catch(SQLException ex){
            System.out.println("Data read  ERROR: " + ex);
        }
    }
    static void updateData(){
        String updateQuery = "update course set CourseName = 'Algorithm' where courseID = 11";
        try{
            stmt.executeUpdate(updateQuery);
            readData();
        }
        catch(SQLException ex){
            System.out.println("Update ERROR: " + ex);
        }
    }

    static void deleteData(){
        String deleteQuery = "delete from course where courseID = 11";
        try{
            stmt.executeUpdate(deleteQuery);
            System.out.println("Deleted successfully....!!");
            readData();
        }
        catch(SQLException ex){
            System.out.println("Deletion ERROR: " + ex);
        }
    }
    static void createTable(String tName){
        String createTableQuery = "create table " +tName + " (id int primary key, name varchar(20), city varchar(20))";
        try{
            stmt.executeUpdate(createTableQuery);
            System.out.println("Table created successfully....!!");
        }
        catch(SQLException ex){
            System.out.println("Table creation ERROR: " + ex);
        }
    }

    public static void readDataWithMetadata(){
        String selectQuery = "select * from coure";
        try{
            ResultSet rs = stmt.executeQuery(selectQuery);
            int noOfColoumn = metadata.getColumnCount();
            for(int i =1; i <= noOfColoumn; i++){
                System.out.printf("%-10s\t",metadata.getColumnName(i));
                System.out.println();
            }
                while(rs.next()){
                    for (int i =1; i <= noOfColoumn; i++)
                        System.out.printf("%-10s\t",rs.getObject(i));
                    System.out.println();

                    DatabaseMetaData dbMetaData = conn.getMetaData();
                    System.out.println("databse URL: " +dbMetaData.getURL());
                    System.out.println("database username: " + dbMetaData.getUserName());
                    System.out.println("database product name: " + dbMetaData.getDatabaseProductName());
                    System.out.println("database product version: " + dbMetaData.getDatabaseProductVersion());

                }
            }
         catch(SQLException ex){
            System.out.println("Meta Data Readd ERROR! "+ex);
        }
    }




    public static void readDataByeCourseName(){
        Scanner input = new Scanner(System.in);
        System.out.println("Please Enter course id");
        int id = input.nextInt();
        System.out.println("Please enter course name");
        String course = input.next();
        String query = "Select * from Course where courseID = ? and CourseName = ?";
        try{
            PreparedStatement  preparedStmt = conn.prepareStatement(query);
            preparedStmt.setInt(1,id);
            preparedStmt.setString(2,course);
            ResultSet rs = preparedStmt.executeQuery();
            while(rs.next()){
                System.out.println(rs.getInt(1));
                System.out.println(rs.getString(2));
            }
        }
        catch(SQLException ex){
            System.out.println("Prepared Statment ERROR! "+ex);
        }
    }
}