CODEWITHSUNDEEP
javajdbc
Bippo
Bippo

Reputation: 51

Java JDBC adding automatic value to database

I'm working with Java JDBC with Apache Derby data base.
I have a table called `company`` with the values :
id, comp_name, password, email.

This method should create a new row of company with name, password, and email received from the user but the ID should be given automatically from the database and increment itself each time a new company is added to the database.

I just can't figure out how to make this work, I obviously get a error

"column 'ID' cannot accept a NULL value."

because the update occours before the ID is setted.

Code:

public void createCompany(Company company) {
    Connection con = null;

    try {
        con = ConnectionPool.getInstance().getConnection();
        String sql = "INSERT INTO company (comp_name, password, email) VALUES (?,?,?)";
        PreparedStatement pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, company.getCompName());
        pstmt.setString(2, company.getPassword());
        pstmt.setString(3, company.getEmail());
        pstmt.executeUpdate();
        ResultSet rs = pstmt.getGeneratedKeys();
        rs.next();
        company.setId(rs.getLong(1));
        pstmt.getConnection().commit();

    } catch (SQLException e) {

        e.printStackTrace();
    } finally {
        ConnectionPool.getInstance().returnCon(con);
    }

Upvotes: 3

Views: 101

Answers (3)

Felix Gerber
Felix Gerber

Reputation: 1651

A problem could be that you made a mistake by creating your table. You could create your table like this:

   CREATE TABLE company
   (
      ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
      comp_name VARCHAR(50),
      email VARCHAR(50),
      password VARCHAR (50)
   )

IF you want other values to be not NULL you could add NOT NULL to their lines:

   password VARCHAR (50) NOT NULL

Delte your old table and execute the the SQl above on your DB. After that you can use your code without changes.

Upvotes: 1

Jiri Tousek
Jiri Tousek

Reputation: 12440

You're doing almost everything right, you just need to let the database assign an unique ID to each inserted row:

CREATE TABLE my_table (
    id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    ...
);

Upvotes: 2

Rahman
Rahman

Reputation: 3795

During creation of that table you have to write following DDL

CREATE TABLE MAPS
(
comp_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
comp_name VARCHAR(24) NOT NULL,
password VARCHAR(26)
)

Ref : https://db.apache.org/derby/docs/10.0/manuals/develop/develop132.html

Upvotes: 2

Related Questions