Checking for duplicates with ResultSet.getString

Question

May I know the best way to validate a variable by checking if there are any duplicates in my database?

I've tried several different ways already, but whenever the first if condition is not fulfilled, a blank page would be shown

Edit: I've thought about setting username to a unique value, but that would come into conflict with my primary key, staff_id

<%-- Checking for duplicates - Reading --%>
<%
if(request.getParameter("Check") != null){
        try{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String conURL1= "jdbc:odbc:HOD_DATA";
        Connection con1 = DriverManager.getConnection(conURL1);
        Statement st1 = con1.createStatement();
        String query = "select username from Staff where username = '"+sUsername+"'";
        ResultSet rs = st1.executeQuery(query);

        while(rs.next()){
        result = rs.getString("username");

        if(result.equals(sUsername)) {
        response.setHeader("Refresh", "3; URL=StaffReg.jsp");   
        out.println("You may not use this username.");

        }

        else if(rs.wasNull()) { 
        response.setHeader("Refresh", "3; URL=StaffReg.jsp");
        out.println("You may use this username.");

        }


        }
        rs.close();
        st1.close();
        con1.close();
        }

        catch(Exception e){
        out.println(e);
        }
}

        %>

This doesn't make sense. The query already checks the condition you're asserting. And there is nothing here that checks for duplicates. — user207421, Feb 07 '15 at 08:35
I tried that already. The checking never ever goes to the 2nd condition even if it's `else` — Ashton, Feb 07 '15 at 08:36
@EJP What would you suggest for the checking of duplicates? Thanks a lot! — Ashton, Feb 07 '15 at 08:43
you can simply get count of it and check if result count is greater than 0 then it already presents else not — Ameya Deshpande, Feb 07 '15 at 08:55
If you get any result all from this, it's a duplicate. But you're approaching from the wrong end. You should (a) define the column with a UNIQUE key, (b) attempt an *INSERT,* and (c) if *that* fails with the appropriate exception, you have a duplicate. — user207421, Feb 07 '15 at 09:03

score 0 · Accepted Answer · answered Feb 08 '15 at 07:20

You can get the count and check if count > 0

Here's an example.Hope this will help.

public int checkUsername(String sUsername) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int count =0;
        try{
            conn = DatabaseManager.con();
            String query="select count(*) from Staff where username=? ";
            pstmt=conn.prepareStatement(query);
            pstmt.setString(1,sUsername);
            rs=pstmt.executeQuery();

            while (rs.next()) {
                count=rs.getInt("count(*)");

            }

        }
        catch(Exception e){
            e.printStackTrace();
        }
        finally {
            if (rs != null)
                DatabaseManager.rs_close(rs);
            if (pstmt != null)
                DatabaseManager.stmt_close(pstmt);
            if (conn != null)
                DatabaseManager.con_close(conn);
        }
        return count;

    }

//Call the method and check.

if(checkUsername("Amanda")>0 ){

                System.out.println("user exists");


            }

Checking for duplicates with ResultSet.getString

1 Answers1