Blog

Java program to illustrate the process of executing SQL statements in JDBC

Java program to illustrate the process of executing SQL statements in JDBC

Simple program of java jdbc : JAVA PROGRAM USING JDBC TO EXTRACT NAME OF THOSE STUDENTS FROM TABLE

Implement CRUD (Create/Insert, Read/Select, Update, Delete) operations for student table. Ask for user input where applicable.

Write a program to illustrate the process of executing SQL statements in JDBC?

Java CRUD Operations in a single program / SQL statements in JDBC

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.util.*; 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 *
 * @author achyut1324
 */
public class Question2 {
    public static void main(String[] args) throws SQLException {
     //****************** Database Connection ******************* //
        String url = "jdbc:mariadb://localhost:3306/onlinelab";
        String username = "root";
        String password = "";
        Connection connection = DriverManager.getConnection(
                url, username, password);
        Statement statement = connection.createStatement();
      //****************** Database Connection End ******************* //
      
        Scanner sc= new Scanner(System.in);
        System.out.println("Choose the Operation you want to do: ");
        System.out.println("A. Insert Into Database \nB. Delete From Database \nC.Upadate Database \nD. Read Database \n Choose :");
        
        char option= sc.next().charAt(0);
        switch(option){
            case 'A':
        //****************** Insert Code ******************* //
            System.out.print("Enter Name: ");  
            String name= sc.next();  
            System.out.print("Enter District: ");  
            String district= sc.next();
            System.out.print("Enter age: ");  
            int age= sc.nextInt();  
 
        
        String sql = String.format(
            "insert into student (name, district, age) values ('%s', '%s' , %d)", 
            name,
            district,
            age
        );
        int rowsInserted = statement.executeUpdate(sql);
        if(rowsInserted > 0)
            System.out.println("Insert successful");
        else 
            System.out.println("Insert failed");
        
    //****************** Insert Code End******************* //
        break;
            case 'B':
     //****************** Delete Code******************* //
        System.out.print("Enter Id to delete : ");  
        int id= sc.nextInt(); 

        String delsql = String.format("delete from student where id = %d",id);
        int rowsDeleted = statement.executeUpdate(delsql);
        if(rowsDeleted > 0)
            System.out.println("Delete successful");
        else 
            System.out.println("Delete failed");
//****************** Delete Code End******************* //
        break;
        
            case 'C':
//****************** Update Code ******************* //
        System.out.print("Enter Id to Update : ");  
        int uid= sc.nextInt();
        
        System.out.print("Enter Name: ");  
        String uname= sc.next();  
        
        System.out.print("Enter District: ");  
        String udistrict= sc.next();
        
        System.out.print("Enter age: ");  
        int uage= sc.nextInt();
        
        
        String updatesql = String.format("update student set name = '%s', district = '%s', age ='%d' where id = %d",uname,
            udistrict,
            uage,uid);
        int rowsUpdated = statement.executeUpdate(updatesql);
        if(rowsUpdated > 0)
            System.out.println("Update successful");
        else 
            System.out.println("Update failed");
        statement.close();
        connection.close();
        break;
 //****************** Update Code End ******************* //
      
            case 'D':
 //****************** Read Code ******************* //
        String readsql = "select * from student";
        ResultSet rs = statement.executeQuery(readsql);
        
        while(rs.next()) {
            System.out.printf("%d %s %s \n",
                rs.getInt("id"),
                rs.getString("name"),
                rs.getString("district"),
                rs.getInt("age")
            );
        }
        statement.close();
        connection.close();
                
        break;
        }
    }
}

Output of the Program

run:
Choose the Operation you want to do:
A. Insert Into Database
B. Delete From Database
C.Upadate Database
D. Read Database
Choose :
D
1 ram katmandu
3 gita lamjung
4 achyur ktm
6 a b
7 Ram nepal
8 aasasdag dsada
9 ramma ktm
BUILD SUCCESSFUL (total time: 6 seconds)