CURD Operation in java using JDBC

First Create Database:

neelakandan@neelakandan-HP-Laptop-15s-eq2xxx:~$ sudo -i -u postgres
[sudo] password for neelakandan:         
postgres@neelakandan-HP-Laptop-15s-eq2xxx:~$ psql
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.

postgres=# \c curd_op
You are now connected to database "curd_op" as user "postgres".
curd_op=# CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);
CREATE TABLE
curd_op=# \dt
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | users | table | postgres
(1 row)

curd_op=# select * from users;
 id | name | email 
----+------+-------
(0 rows)

curd_op=# GRANT ALL PRIVILEGES ON TABLE users TO neel0;
GRANT
curd_op=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO neel;
ALTER DEFAULT PRIVILEGES
curd_op=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO neel0;
ALTER DEFAULT PRIVILEGES
curd_op=# GRANT USAGE, SELECT, UPDATE ON SEQUENCE users_id_seq TO neel0;
GRANT
curd_op=# select * from users;
 id | name |    email    
----+------+-------------
  1 | neel | [e-mail]
(1 row)

JDBC 5 Steps :
**
Step 1:import driver**

import java.sql.*;

Step 2: Load the driver

Step 3: Establish the Connection

String url = "jdbc:postgresql://localhost:5432/curd_op";
        String user = "neel0";
        String pass = "neel0";

            Connection con = DriverManager.getConnection(url, user, pass);

Step 4:Create a Statement and ExecuteQuery

String insert = "insert into users(name,email)values('neel','[e-mail]')";
            con.createStatement().executeUpdate(insert);
            System.out.println("updated");

            ResultSet read = con.createStatement().executeQuery("select * from users");
            while (read.next()) {
                System.out.println(read.getString("name") + " " + read.getString("email"));
            }

Step 5:Close the Connection
con.close();

Add PostgreSQL JDBC Driver to your project
.

Linux use terminal to download: wget https://jdbc.postgresql.org/download/postgresql-42.7.3.jar

Open Eclipse

Right-click your project in the Package Explorer.

Select Build Path → Configure Build Path.

In the dialog:

Go to the Libraries tab.Click Add External JARs…Browse and select the downloaded postgresql-xx.jar file.Click Apply and Close.
wget https://jdbc.postgresql.org/download/postgresql-42.7.3.jar

Code for JDBC CURD

package Interview_practice;

import java.sql.*;

public class curd_op {

    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/curd_op";
        String user = "neel0";
        String pass = "neel0";
        try {
            Connection con = DriverManager.getConnection(url, user, pass);
            System.out.println("connected");

            String insert = "insert into users(name,email)values('neel','[e-mail]')";
            con.createStatement().executeUpdate(insert);
            System.out.println("updated");

            ResultSet read = con.createStatement().executeQuery("select * from users");
            while (read.next()) {
                System.out.println(read.getString("name") + " " + read.getString("email"));
            }
            String update = "update users set name='neelakandan' where id=1";
            con.createStatement().executeUpdate(update);
            System.out.println("updated");

            String delete = "delete from users where id =1";
            con.createStatement().executeUpdate(delete);
            System.out.println("deleted");
            con.close();

        } catch (SQLException e) {

            e.printStackTrace();
        }

    }
}

Output:
connected
updated
neel [e-mail]
neel [e-mail]
neel [e-mail]
updated
deleted

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class jdbc_crud {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        String user = "neel";
        String pass = "neel1862";
        String[][] name = { { "Java", "Neel" }, { "Python", "Alice" }, { "C++", "Bob" }, { "Go", "Charlie" },
                { "Rust", "David" } };
        try {
            Connection con = DriverManager.getConnection(url, user, pass);
            Statement s = con.createStatement();
//          String insert="insert into users (book,author) values ('java','neel')";
//          s.executeUpdate(insert);
//          System.out.println("insert sucessful");

            PreparedStatement ps = con.prepareStatement("insert into users (book,author) values (?,?)");
            for (String[] book : name) {
                ps.setString(1, book[0]);
                ps.setString(2, book[1]);
                ps.addBatch();

            }
            ps.executeBatch();

            ResultSet rs = s.executeQuery("select * from users");
            while (rs.next()) {
                System.out.println(rs.getString("book") + " " + rs.getString("author"));

            }
            String update = "update users set book='notjava' where id=1";
            s.executeUpdate(update);
            System.out.println("updated");
            String delete = "delete from users where id=1";
            s.executeUpdate(delete);
            System.out.println("delete");

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }

}

Java Neel
Python Alice
C++ Bob
Go Charlie
Rust David
updated
delete

Comments

[Vigneshwaralingam]

Thank you bro . it's looks like easy to understand.