java.sql.SQLException: [SQLITE_CONSTRAINT]

Question

I am using SQLite in my java application, I have create some tables in my database. when I insert element into people table (ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME CHAR(50) NOT NULL UNIQUE),
I got the exception below :

java.sql.SQLException: [SQLITE_CONSTRAINT] Abort due to constraint violation (column NAME is not unique) at org.sqlite.DB.newSQLException(DB.java:383) at org.sqlite.DB.newSQLException(DB.java:387) at org.sqlite.DB.execute(DB.java:342) at org.sqlite.DB.executeUpdate(DB.java:363) at org.sqlite.PrepStmt.executeUpdate(PrepStmt.java:85) at epfl.lca1.spism.DesktopClient$1.run(Myclass.java:71) at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:251) at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:705) at java.awt.EventQueue.access$000(EventQueue.java:101) at java.awt.EventQueue$3.run(EventQueue.java:666) at java.awt.EventQueue$3.run(EventQueue.java:664) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76) at java.awt.EventQueue.dispatchEvent(EventQueue.java:675) at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:211) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:128) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:117) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:113) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:105) at java.awt.EventDispatchThread.run(EventDispatchThread.java:90)

and my code is :

final PreparedStatement s;
    Connection db = openOrCreateDatabase();
    try {
        s = db.prepareStatement("INSERT INTO People"+
                                "(Name) VALUES"+ 
                                "(?)");
    SwingUtilities.invokeLater(new Runnable() {
        @Override
        public void run() {
            System.out.printf("A: %d\n", Thread.currentThread().getId());

            for (People p : Peoplearray) {
                try {
                    s.setString(1,p.toString());
                    s.executeUpdate(); // line 71
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    });
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

maybe some element insert two times, how can I ignore inserting existed one?

Answer 1

SQLite's INSERT can be uses with a conflict resolution clause to specify what happens when you have a constraint violation. This will just ignore any attempt to insert a duplicate value:

INSERT OR IGNORE INTO People (Name) VALUES (?)