Basic DB operations JDBC

By: Unknown on 6:07 AM

Lets see how basic DB operations can be done like

• insertion
• retrieval
• updation
• deletion

Lets create a table first in the oracle : "create table users(nm varchar(20),pw varchar(2),em varchar(20));"

Insert.java

import java.sql.*;
public class Insert {

    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;
        int rows_affected = 0;
        String classnm = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "system";
        String password = "oracle";
        String sql = "insert into users values(?,?,?)";

        try {
            Class.forName(classnm);
            con = DriverManager.getConnection(url, username, password);
            ps = con.prepareStatement(sql);
            ps.setString(1, "abc");
            ps.setString(2, "xyz");
            ps.setString(3, "abc@xyz.com");
            rows_affected = ps.executeUpdate();
            ps.close();            
            con.close();
         }
            catch (Exception e) {
            System.out.println("Error--> " + e);
        }
            if (rows_affected == 1) {
                System.out.println("Inserted Sucessfully");
            } else {
                System.out.println("Try again");
            }
        }

    }

Retrieve.java

import java.sql.*;
public class Retrieve {

    public static void main(String[] args) {

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int rows_affected = 0;
        String classnm = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "system";
        String password = "oracle";
        String sql = "select * from users";

        try {

            Class.forName(classnm);
            con = DriverManager.getConnection(url, username, password);
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
           //loops until rs has records
            while (rs.next()) {

                System.out.println("Name :" + rs.getString("nm"));
                System.out.println("Password :" + rs.getString("pw"));
                System.out.println("Email :" + rs.getString("em"));
            }
            ps.close();
            rs.close();
            con.close();

        } catch (Exception e) {

            System.out.println("Error--> " + e);

        }
    }

}

Update.java

import java.sql.*;
public class Update {
    public static void main(String[] args) {

        Connection con = null;
        PreparedStatement ps = null;
        int rows_affected = 0;
        String classnm = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "system";
        String password = "oracle";
        String sql = "update users set pw=? where em=?";

        try {

            Class.forName(classnm);
            con = DriverManager.getConnection(url, username, password);
            ps = con.prepareStatement(sql);
            ps.setString(1, "def");
            ps.setString(2, "abc@xyz.com");
            rows_affected = ps.executeUpdate();
            ps.close();
            con.close();

        } catch (Exception e) {
            System.out.println("Error---> " + e);

        }

        if (rows_affected == 1) {
            System.out.println("Updated Succesfully");

        } else {
            System.out.println("Try again");
        }
    }
}

Delete.java

import java.sql.*;

public class Delete {

    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;
        int rows_affected = 0;
        String classnm = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "system";
        String password = "oracle";
        String sql = "delete from users where em=?";

        try {
            Class.forName(classnm);
            con = DriverManager.getConnection(url, username, password);
            ps = con.prepareStatement(sql);
            ps.setString(1, "abc@xyz.com");
            rows_affected = ps.executeUpdate();
            ps.close();
            con.close();
        } catch (Exception e) {
            System.out.println("Error---> " + e);
        }
        if (rows_affected == 1) {
            System.out.println("Deleted Succesfully");
        } else {
            System.out.println("Try again");
        }

    }

}

Note: The values can be given dynamically by using Scanner class or graphically using Swings but i have shown static way of doing that just to demonstrate how actually the stuff goes. I will cover them in next posts.