Question 289720 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to check whether user exist in database

friends. I’m creating a mini bank application that user makes input with his name and id and then have several options. My problem is that i want to check if the user exists inside the insert() method to avoid duplicate and to have a opportunity to update the same user’s balance while deposit and withdraw.

What i try with is this piece of code :

public boolean checkIfExist(Long id) throws SQLException { 
     
        Connection conn = null;  
        PreparedStatement ps = null;  
        ResultSet rs = null;  
                  
        try {             
            conn = connect();  
            ps=conn.prepareStatement("select * from customers where id=?");  
            ps.setLong(1, customerID);  
            rs=ps.executeQuery();
            if(rs != null) {
                return true;
            }
        } catch (Exception e) { 
            System.out.println(e);
        } finally {  
            if (rs!=null) {  
                rs.close();  
            }
            if (ps!=null) {  
                ps.close();  
            }
            if (conn!=null) {  
                conn.close();  
            }   
        }  
        return false;
    }

and use it this way :

public void insert() throws SQLException {
        String sql = "INSERT INTO customers(id, name)" +
                     "VALUES(?, ?)";

        Connection conn = null;
        PreparedStatement ps = null;

        boolean ifExist = checkIfExist(customerID);

        if(!ifExist) { 
            try {
                conn = connect();
                ps = conn.prepareStatement(sql);
                ps.setLong(1, customerID);
                ps.setString(2, customerName);
                ps.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }
        }
    }

But i get "true" everytime when execute it and cant insert a new customer. I guess the problem is in "if(rs != null)" but i dont know how to do it right.

Tags:

2

Answers


  1. 0

    Duplicate question
    java jdbc check if value exists before inserting into table

    Try like in above link. "select count(*) from customers where id=?"

    Also useful article
    https://www.baeldung.com/jdbc-check-table-exists

    Login or Signup to reply.
  2. 0

    CheckIF Exist method

    • The purpose of this method is to check if a customer with a given ID already exist in the databse.

    • It uses a SQL query to select records from the "customers" table where the ID matches the provided ID.

    • The "executequery" method returns a "resultset" that represent the data retrieved from the database.

    • Instead of checking if the "resultset" is not null ,it uses "rs.next()" to check if there is at least one row in the result set.

    • If "rs.next()" returns "true" it means there is at least one matching customer, and the method returns "true". Otherwise, it returns "false".

    insert method

    • This method is responsible for inserting a new customer into the database.

    • It first checks if a customer with the same ID already exists by calling the checkIfExist method.

    • If checkIfExist returns false, it proceeds to execute an SQL INSERT statement to add the new customer to the database.

    • If checkIfExist returns true, it skips the insertion step, avoiding duplicate entries in the database.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search