JDBC CRUD application[java]

By: Unknown on 1:52 AM

Lets create a small CRUD application in JDBC using core java.Our app consists of a menu which holds the following options

• Create
• Retrieve
• Update
• Delete

Lets create a table called jobs in oracle  first.

create table jobs(id varchar(30),title varchar(30),min_sal varchar(30),max_sal varchar(30));

Sourcefile

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class CRUDapplication {
    public static void main(String... a) {
        Connection con;
        ResultSet rs;
        PreparedStatement ps;
        String ex;
        Scanner s = new Scanner(System.in);
        while (true) {
            try {
                System.out.println("***CRUD APPLICATION***");
                int opt = 0;
                int result;
                String jid, jtit;

                int minsal, maxsal;

                System.out.println("1.Create");
                System.out.println("2.Retrieve");
                System.out.println("3.Update");
                System.out.println("4.Delete");
                System.out.println("Enter Your Choice");

                opt = s.nextInt();
                switch (opt) {

                    case 1:
                        System.out.println("Enter JOB ID: ");
                        jid = s.next();
                        System.out.println("Enter JOB TITLE: ");
                        jtit = s.next();
                        System.out.println("Enter MIN SALARY: ");
                        minsal = s.nextInt();
                        System.out.println("Enter MAX SALARY: ");
                        maxsal = s.nextInt();
                        try {
                            Class.forName("oracle.jdbc.driver.OracleDriver");
                            con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "oracle");
                            ps = con.prepareStatement("insert into jobs values(?,?,?,?)");
                            ps.setString(1, jid);
                            ps.setString(2, jtit);
                            ps.setInt(3, minsal);
                            ps.setInt(4, maxsal);
                            result = ps.executeUpdate();
                            if (result == 1) {
                                System.out.println("Inserted Successfully");
                            }
                        } catch (Exception e) {
                            System.out.println("Error---> " + e);
                        }
                        break;
                    case 2:
                        try {
                            Class.forName("oracle.jdbc.driver.OracleDriver");
                            con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "oracle");
                            ps = con.prepareStatement("select * from jobs");
                            rs = ps.executeQuery();
                            while (rs.next()) {
                                System.out.println("JOB ID: " + rs.getString(1));
                                System.out.println("JOB TITLE: " + rs.getString(2));
                                System.out.println("MIN SALARY: " + rs.getInt(3));
                                System.out.println("MAX SALARY: " + rs.getInt(4));
                            }
                        } catch (Exception e) {
                            System.out.println("Error---> " + e);
                        }
                        break;
                    case 3:
                        System.out.println("Enter JOB ID to be updated: ");
                        jid = s.next();
                        System.out.println("Enter new JOB TITLE: ");
                        jtit = s.next();
                        System.out.println("Enter new MIN SALARY: ");
                        minsal = s.nextInt();
                        System.out.println("Enter new MAX SALARY: ");
                        maxsal = s.nextInt();
                        try {
                            Class.forName("oracle.jdbc.driver.OracleDriver");
                            con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "oracle");
                            ps = con.prepareStatement("update jobs set title=?,min_sal=?,max_sal=? where id=?");
                            ps.setString(1, jtit);
                            ps.setInt(2, minsal);
                            ps.setInt(3, maxsal);
                            ps.setString(4, jid);
                            result = ps.executeUpdate();
                            if (result == 1) {
                                System.out.println("Updated Successfully");
                            } else {
                                System.out.println("Invalid id");
                            }
                        } catch (Exception e) {
                            System.out.println("Error---> " + e);
                        }
                        break;

                    case 4:
                        System.out.println("Enter JOB ID tobe deleted: ");
                        jid = s.next();
                        try {
                            Class.forName("oracle.jdbc.driver.OracleDriver");
                            con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "oracle");
                            ps = con.prepareStatement("delete from jobs where id=?");
                            ps.setString(1, jid);
                            result = ps.executeUpdate();
                            if (result == 1) {
                                System.out.println("Deleted Successfully");
                            } else {
                                System.out.println("Invalid id");
                            }
                        } catch (Exception e) {
                            System.out.println("Error---> " + e);
                        }
                        break;

                }

            } catch (Exception e) {
                System.out.println("Error--> " + e);
            }
            System.out.println("Enter n to exit s to continue");
            ex = s.next();
            if (ex.equals("n")) {
                break;
            } else if (ex.equals("s")) {
                continue;
            }
        }
    }
}

SCREENSHOTS

Create

Retrieve

Update

Delete