Wheres my Mistake?

Question

I want to get the actual created Primary Key. I need it instantly for another Method but it returns an error. But it returns a SQLE. Ive no idea wheres my Mistake. I hope i gave you enaugh information. (The System.out.println(id) is just for me to check if it returns the right PrimaryKey)

CreateTable:

CREATE TABLE "MitarbeiterInfo" ("Vorname" TEXT, "Nachname" TEXT, "Geburtsdatum" CHAR, "Wohnadresse" TEXT, "Postleitzahl" TEXT, "Eintrittsdatum" CHAR, "Handynummer" TEXT, "Email" TEXT, "ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL )

GuiClass:

public void actionPerformed(ActionEvent e) {
            new Datenbank().mitarbeiterHinzufügen(
                    textField.getText(),textField_1.getText(),textField_2.getText(),textField_3.getText(),
                    textField_4.getText(),textField_5.getText(),textField_6.getText(),textField_7.getText());
            refreshTable();
        }

DatabaseClass:

public void mitarbeiterHinzufügen(String v, String n, String g, String w, String p, String e, String h, String email){
    conn=Datenbank.dbConnector();


    try {
        String query="insert into MitarbeiterInfo (Vorname,Nachname,Geburtsdatum,Wohnadresse,Postleitzahl,Eintrittsdatum,Handynummer,Email,ID) values (?,?,?,?,?,?,?,?,?)";
        PreparedStatement pst=conn.prepareStatement(query);
        pst.setString(1, v);
        pst.setString(2, n);
        pst.setString(3, g);
        pst.setString(4, w);
        pst.setString(5, p);
        pst.setString(6, e);
        pst.setString(7, h);
        pst.setString(8, email);

        pst.execute();

        String identitiy = "SELECT IDENTITY_VAL_LOCAL() FROM MitarbeiterInfo";
        ResultSet rs = pst.executeQuery(identitiy);
        rs.next();
        int id = rs.getInt("1");
        System.out.println(id);


        JOptionPane.showMessageDialog(null, "Mitarbeiter hinzugefügt");

        pst.close();
    } catch (Exception b) {
        b.printStackTrace();
    }
}

That error ocurs:

java.sql.SQLException: not implemented by SQLite JDBC driver
at org.sqlite.Unused.unused(Unused.java:31)
at org.sqlite.PrepStmt.executeQuery(PrepStmt.java:596)
***at Datenbank.mitarbeiterHinzufügen(Datenbank.java:69)***
at GUI$3.actionPerformed(GUI.java:138)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

you should mark the line "Datenbank.mitarbeiterHinzufügen(Datenbank.java:69)" in your sourcecode. And add a java tag to the post — Marged, Jun 07 '15 at 18:57
Doesn't _'not implemented by SQLite'_ give you a clue? I'd hope you at least read the message before posting it here. — , Jun 07 '15 at 18:59
You have `9 ?` in your `PreparedStatement`,but you're setting only 8 values!!! — Am_I_Helpful, Jun 07 '15 at 19:02
Well but i implemted it. Maybe that driver does not support that method? — rubine, Jun 07 '15 at 19:02
Have you considered using generated keys? http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc — user3707125, Jun 07 '15 at 19:32

Rajesh · Answer 1 · 2015-06-07T19:15:13.917

1

In java.sql.ResultSet, below method's are defined for getInt:

getInt(int columnIndex)
getInt(String columnLabel)

I find problem with below line in your code:

int id = rs.getInt("1");

You should get value with one of the options:

int id = rs.getInt(1);

Or

int id = rs.getInt("Column_name");

edited Jun 07 '15 at 19:15

answered Jun 07 '15 at 19:09

Rajesh

2,135
1
12
14

1

@shekhar, no, @Rajesh is correct; it should be `rs.getInt(1)` instead of `rs.getInt("1")`. There is no column labeled "1" in the result set. – Mick Mnemonic Jun 07 '15 at 19:15
@rubine-Did you combinedly try both of us' suggestions? I suggest you to make 2 changes as mentioned in my answer and Rajesh's answer, and then try the same. – Am_I_Helpful Jun 07 '15 at 19:20
1

Fire query to db directly and include- query+result with your question. – Rajesh Jun 07 '15 at 19:20
I didn't get what I asked for; Can you change your id/identitiy selection query to - `SELECT ID FROM MitarbeiterInfo` – Rajesh Jun 07 '15 at 19:32
May be its really difficult for me to understand - "dosnt work for me". I have asked to fire select query directly n share query+result but what's shared is DDL. – Rajesh Jun 07 '15 at 19:44

Mick Mnemonic · Accepted Answer · 2015-06-07T19:40:07.250

0

Try

SELECT last_insert_rowid() FROM MitarbeiterInfo instead.

The exception you're getting seems to imply that IDENTITY_VAL_LOCAL() is not supported by SQLite.

edited Jun 07 '15 at 19:40

answered Jun 07 '15 at 19:34

Mick Mnemonic

7,808
2
26
30

Could you try creating a new prepared statement for your query instead of reusing `pst`? – Mick Mnemonic Jun 07 '15 at 20:01
i tried that like this String identitiy = "SELECT last_insert_rowid() FROM MitarbeiterInfo"; PreparedStatement pstm=conn.prepareStatement(query); ResultSet rs = pstm.executeQuery(identitiy); rs.next(); int id = rs.getInt(1); System.out.println(id); dosnt work too – rubine Jun 07 '15 at 20:08
Okay then, could you try `pstm.execute(); rs = pstm.getResultSet();`. Perhaps `executeQuery` is not supported.. – Mick Mnemonic Jun 07 '15 at 20:16
i edited you answer ^ i dont know how you mean that with pstm.getResultSet(); and thanks for your patient – rubine Jun 07 '15 at 20:28
I solved it with your help my falure was PreparedStatement pstm=conn.prepareStatement(query); – rubine Jun 07 '15 at 20:55
Correct it should be PreparedStatement pstm=conn.prepareStatement(identitiy); Thank you Mich Mnemonic – rubine Jun 07 '15 at 20:56

Wheres my Mistake?

2 Answers2