如何在JDBC sql中自动增加主键

Question

你们能帮帮我吗，我的主键无法自动递增，我的表名是books，我想自动递增的列是serial_no，这是主键。

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());
    }
}

这是五月按钮，当我输入所有数据时，它将被提交到我的台本上

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:
}

顺便说一句，我找到了另一种方法，抓取我的表并重建它，然后将代码放入create table脚本中

SERIAL_NOINTEGER默认AUTOINCREment：开始1增量1不是空主键

Answer 1

只需将您的serial_no列定义为int主键生成始终作为标识，然后Derby将自动为您分配数字。下面是一些示例代码：

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);
    }
}

从而产生

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