How to auto Increment a primarykey in JDBC sql

Question

can you please guys help me, i'm having trouble on making my primary key into auto-increment, My table name is books and the column that i want to be auto-increment is serial_no which is a primary key.

public class donate extends javax.swing.JFrame {
    Connection con;
    Statement stmt;
    ResultSet rs;
    PreparedStatement pst;
    DefaultTableModel loginModel = new DefaultTableModel();
    int curRow = 0;

/**
 * Creates new form donate
 */
public donate() {
    initComponents();
    DoConnect();        
    showAll();
}
void showAll(){
   try{
   rs = stmt.executeQuery("SELECT * FROM books");
   while(rs.next())
   {
       String book = rs.getString("book_title");
       String categorie = rs.getString("category");
       String status = rs.getString("book_status");
       String donators = rs.getString("donator");
       int serial_nos = rs.getInt("serial_no");
       loginModel.addRow(new Object[]{book, categorie, status, donators, serial_nos});
   }
   }catch(SQLException err){
        System.out.println(err);
   }
}
void DoConnect( ) {
               try{
                //CONNECT TO THE DATABASE
                String host = "jdbc:derby://localhost:1527/Dafuq7";
                String uName ="Dafuq7";
                String uPass ="Dafuq7";
                con = DriverManager.getConnection(host, uName, uPass);

                //EXECUTE SOME SQL AND LOAD THE RECORDS INTO THE RESULTSET
                        stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                    ResultSet.CONCUR_UPDATABLE);
                String sql = "SELECT * FROM books";
                rs = stmt.executeQuery(sql);
        }   
    catch(SQLException err){
                JOptionPane.showMessageDialog(donate.this, err.getMessage());
    }
}

and here is for may button, which when i input all the data will be submitted to my table books

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    String bookttl = bookt.getText();
    String yourn = yn.getText();
    String categ = cat.getSelectedItem().toString();
    String bstat = bs.getSelectedItem().toString();

    try {
        rs.moveToInsertRow();
        rs.updateString( "book_title", bookttl );
        rs.updateString( "category", yourn );
        rs.updateString( "book_status", categ );
        rs.updateString( "donator", bstat );




        loginModel.addRow(new Object[]{bookttl, yourn, categ, bstat});

        rs.insertRow( );
        stmt.close();
        rs.close();

        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        String sql = "SELECT * FROM books";
        rs = stmt.executeQuery(sql);

    }
    catch (SQLException err) {
        System.out.println(err.getMessage() );
    }// TODO add your handling code here:
}

BTW i found another way around by doing this, grabbing my table and reconstructing it and put this code in the create table script

SERIAL_NO INTEGER default AUTOINCREMENT: start 1 increment 1 not null primary key

Usually, send NULL. Without more information, noone can provide you with a more helpful answer as we have no idea about the database engine being used or the schema. — Boris the Spider
Are you wanting to generate sequential numbers? Or do you want to create a primary key (PK)? The two concepts aren't exactly equivalent. A PK generally shouldn't be /required/ to be sequential, especially if intent is to be contiguous. — user2338816
I just need a sequential ascending value of everytime i enter a value on my database — DOODpls

Gord Thompson Gord Thompson · Accepted Answer · 2014-10-12T13:38:31

Simply define your serial_no column as int primary key generated always as identity and then Derby will automatically assign the numbers for you. Here is some example code:

public static void main(String[] args) {
    try (Connection conn = DriverManager.getConnection(
                "jdbc:derby:C:/__tmp/derbytest;create=true")) {
        String sql;
        sql = "DROP TABLE books";
        try (Statement s = conn.createStatement()) {
            s.executeUpdate(sql);
        } catch (Exception e) {
            // assume table did not previously exist
        }
        sql = "CREATE TABLE books (" +
                "serial_no int primary key " +
                    "generated always as identity, " +
                "title varchar(100))";
        try (Statement s = conn.createStatement()) {
            s.executeUpdate(sql);
        }
        sql = "INSERT INTO books (title) VALUES (?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, "The Book of Foo");
            ps.executeUpdate();
            ps.setString(1, "The Book of Bar");
            ps.executeUpdate();
            ps.setString(1, "The Book of Baz");
            ps.executeUpdate();
        }
        sql = "SELECT * FROM books";
        try (Statement s = conn.createStatement()) {
            try (ResultSet rs = s.executeQuery(sql)) {
                while (rs.next()) {
                    System.out.println(String.format(
                            "%d: %s", 
                            rs.getInt("serial_no"),
                            rs.getString("title")));
                }
            }
        }            
    } catch (SQLException se) {
        se.printStackTrace(System.out);
        System.exit(0);
    }
}

which produces

1: The Book of Foo
2: The Book of Bar
3: The Book of Baz

How to auto Increment a primarykey in JDBC sql

1 Answers