com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '' for key 'PRIMARY'

Question

I am getting this exception from today onwards. Yesterday things were fine. I will post my code :

public void enterStaff() throws ClassNotFoundException, SQLException {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        connect = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/project?"
                        + "user=root&password=virus");
        statement = connect.createStatement();

        preparedStatement = connect
                .prepareStatement("SELECT count(*)FROM information_schema.tables\n"
                        + "WHERE table_schema = 'project' AND table_name = 'staff'");
        rs = preparedStatement.executeQuery();
        rs.next();
        int chk = rs.getInt(1);

        if (chk != 1) {
            preparedStatement = connect
                    .prepareStatement("create table staff (staffname varchar(30) primary key);");
            preparedStatement.executeUpdate();
        }

        preparedStatement = connect
                .prepareStatement("insert into staff values(?);");
        preparedStatement.setString(1, addSubName.getText());

        preparedStatement.executeUpdate();
    } catch (ClassNotFoundException | SQLException e) {
        throw e;
    } finally {
        close2();
    }

}

private void close2() {
    try {

        if (statement != null) {
            statement.close();
        }

        if (connect != null) {
            connect.close();
        }
    } catch (SQLException e) {

    }
}

I am trying to create a table with only one column and that column is the primary key. When the code is run for the first time the table is getting created. But value is not getting entered. No exception is shown then. But when I try to enter value for the second time, the exception comes up.

How can I correct this ? If anyone needs to see some more code I will post it.

This is the code through which the user enters the data :

    addSubName = new TextField();
    addSubName.setPromptText("Staff Name");
    addSubName.setPrefSize(200, 30);

    final Button b2 = new Button("Add");
    b2.setFont(Font.font("Calibri", FontWeight.BOLD, 17));
    b2.setPrefSize(70, 30);
    b2.setStyle(" -fx-base: #0066ff;");
    b2.setTextFill(Color.BLACK);
    b2.setOnAction(new EventHandler<ActionEvent>() {
        @Override
        public void handle(ActionEvent e) {

            data.add(new Staff(addSubName.getText()));
            addSubName.clear();

            try {
                enterStaff();
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(AddStaff.class.getName()).log(Level.SEVERE, null, ex);
            } catch (SQLException ex) {
                Logger.getLogger(AddStaff.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    });

The exception details is :

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
at addstaff.AddStaff.enterStaff(AddStaff.java:149)
at addstaff.AddStaff$2.handle(AddStaff.java:100)
at addstaff.AddStaff$2.handle(AddStaff.java:92)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:69)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:217)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:170)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:38)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:37)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:35)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:53)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:28)
at javafx.event.Event.fireEvent(Event.java:171)
at javafx.scene.Node.fireEvent(Node.java:6867)
at javafx.scene.control.Button.fire(Button.java:179)
at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:193)
at com.sun.javafx.scene.control.skin.SkinBase$4.handle(SkinBase.java:336)
at com.sun.javafx.scene.control.skin.SkinBase$4.handle(SkinBase.java:329)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:64)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:217)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:170)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:38)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:37)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:35)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:35)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:92)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:53)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:33)
at javafx.event.Event.fireEvent(Event.java:171)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3311)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3151)
at javafx.scene.Scene$MouseHandler.access$1900(Scene.java:3106)
at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1563)
at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2248)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:250)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:173)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:292)
at com.sun.glass.ui.View.handleMouseEvent(View.java:530)
at com.sun.glass.ui.View.notifyMouse(View.java:924)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.access$100(WinApplication.java:17)
at com.sun.glass.ui.win.WinApplication$3$1.run(WinApplication.java:67)
at java.lang.Thread.run(Thread.java:744)

Answer 1

you shoud remove the ; in your sql query:

wrong query:

prepareStatement("create table staff (staffname varchar(30) primary key);");

preparedStatement = connect.prepareStatement("insert into staff values(?);");

correct query is:

prepareStatement("create table staff (staffname varchar(30) primary key)");

preparedStatement = connect.prepareStatement("insert into staff values(?)");

CHECK YOUR INSERT QUERY

And, I think your INSERT the values in the db, that query is too wrong, did mention the tablename only, you should not mention the columnname. so you should must add the tablename with your query.

wrong query:

insert into staff values(?)

correct query:

    INSERT INTO table_name
    VALUES (value1,value2,value3,...);

---

refer this link:

http://www.w3schools.com/sql/sql_insert.asp

UPDATE:1

wrong code:

statement = connect.createStatement();
preparedStatement = connect
                .prepareStatement("SELECT count(*)FROM information_schema.tables\n"
                        + "WHERE table_schema = 'project' AND table_name = 'staff'");
        rs = preparedStatement.executeQuery();
        rs.next();

you should change like:sample

Class.forName(driverName).newInstance();
con=DriverManager.getConnection(connectionUrl+dbName,user,password);
st = con.createStatement();
String sql="SELECT * FROM employees";
rs=st.executeQuery(sql);

Note:

1. you should not call the sql query.
2. And, you are using preparedStatement to call, you should must chage to statement
3. the preparedStatement is can't resolved.

like,

string sql=....sql query...;
statement.executeQuery(sql) 

update:2:-->sample

public class User 
{
private String empname;
public String getEmpName()
{
    return empname;
}
public void setEmpName(String empname)
{
    this.empname=empname;
}

---

public void addUser(User user)
{
    try
    {
        PreparedStatement ps;
        ps=connection.prepareStatement("INSERT INTO employee (empname,empaddress,depname) VALUES (?,?,?)");
        ps.setString(1, user.getEmpName());
        ps.setString(2, user.getEmpAddress());
        ps.setString(3, user.getDepName());
        ps.executeUpdate();
    }
    catch(Exception e)
    {
        System.out.println(e);
    }
}