CODEWITHSUNDEEP
javadatabasems-access
user2370759
user2370759

Reputation: 37

How do I check if the value already exists in the database?

I am creating a client-server app in Java. On server I have a Microsoft Access database (SQL), and on client I have JFrame form. I have a communication between client and server and it works no problem. My form has a certain text field (let's call it txtField).I need to insert certain data into my database through the form. But before that, it needs to be checked whether the value that's been typed in the txtField already exists in the database. How do I do this, and where to write this code, I guess in the form? Thanks

Upvotes: 1

Views: 7180

Answers (3)

user207421
user207421

Reputation: 310875

I need to insert certain data into my database through the form. But before that, it needs to be checked whether the value that's been typed in the txtField already exists in the database.

No. You need to insert it into the database if it isn't already there. The correct way to do that is to try the insert and catch the failure. Otherwise you are creating a timing-window problem between your check and your insert, during which another client could do the insert.

Upvotes: 3

Transformer
Transformer

Reputation: 379

You should write you query like this:

Insert into [Table](Field1,Field2) 
    Select Top 1 Val1,Val2 
       FROM
         [Table]
   WHERE NOT EXISTS(SELECT * FROM [Table] Where Field1 = Val1 and Field2 = Val2)

Here You can see the example: Conditional Insert Query Based on Data in Target Table

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123464

Here's how I would do it in Java using a parameterized query:

import java.sql.*;

public class JDBCQuery {
    public static void main(String args[]) {
        Connection conn = null;
        PreparedStatement s = null;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection(
                    "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + 
                    "DBQ=C:\\Users\\Public\\Database1.accdb;");

            String txtField = "Java";  // value to insert

            s = conn.prepareStatement(
                    "INSERT INTO Table1 (thing) " + 
                    "SELECT ? AS thing " +
                    "FROM (SELECT COUNT(*) FROM Table1) " +
                    "WHERE NOT EXISTS " +
                        "( " +
                            "SELECT thing FROM Table1 " +
                            "WHERE thing=? " +
                        ")");
            s.setString(1, txtField);
            s.setString(2, txtField);
            if (s.executeUpdate() > 0) {
                System.out.println("The row was inserted.");
            }
            else {
                System.out.println("The row was not inserted.");
            }
        } catch( Exception e ) {
            e.printStackTrace();
        } finally {
            try {
                if (s != null) {
                    s.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch( Exception e ) {
                e.printStackTrace();
            }
        }
    }
}

Upvotes: 0

Related Questions