Incremental key

Question

Guys, I’m doing a web service with Java + Postgres. I set my table in Postgres as follows:

CREATE TABLE usuario
(
  id serial NOT NULL,
  nome character varying(40),
  idade integer,
  CONSTRAINT usuario_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE usuario
  OWNER TO postgres;

The key id need to be incremental. In java I did the following method for insert:

public boolean inserirUsuario(Usuario usuario){

        try {


            Connection conn= ConectaPgAdmin.obtemConexao();


            String queryInserir = "INSERT INTO usuario VALUES**(null,?,?)**";

            PreparedStatement ppStm= conn.prepareStatement(queryInserir);

            //ppStm.setInt(1, usuario.getId());
            ppStm.setString(1, usuario.getNome());
            ppStm.setInt(2, usuario.getIdade());

            ppStm.executeUpdate();

            conn.close();


        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return false;
        }

Placed null, because it is not important since the key will be incremental. However when I run, the lock returns to me that the Primary key id can’t be null.

Someone knows what I do?

Answer 1

Your SQL should be like this:

INSERT INTO usuario (nome, idade) VALUES (?, ?)

The reason is that the auto_increment is only applied if you do not specify the id, but in this case you were specifying yes a value of id, which is the value null.

Also, use the syntax Try-with-Resources if you are in Java 7 or higher, or at least put the conn.close() within a block Finally if you are in Java 6 or lower. The reason is that the way it is, if an exception occurs, you will end up with an open zombie connection. It is also important to close the PreparedStatement adequately.

So your code looks like this (Java 7 or higher):

public boolean inserirUsuario(Usuario usuario) {
    String queryInserir = "INSERT INTO usuario (nome, idade) VALUES (?, ?)";
    try (Connection conn = ConectaPgAdmin.obtemConexao();
            PreparedStatement ppStm = conn.prepareStatement(queryInserir)) {
        ppStm.setString(1, usuario.getNome());
        ppStm.setInt(2, usuario.getIdade());
        ppStm.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }
}

Java 6 or lower:

public boolean inserirUsuario(Usuario usuario) {
    String queryInserir = "INSERT INTO usuario (nome, idade) VALUES (?, ?)";
    Connection conn = null;
    PreparedStatement ppStm = null;
    try {
        try {
            conn = ConectaPgAdmin.obtemConexao();
            ppStm = conn.prepareStatement(queryInserir);
            ppStm.setString(1, usuario.getNome());
            ppStm.setInt(2, usuario.getIdade());
            ppStm.executeUpdate();
        } finally {
            if (ppStm != null) ppStm.close();
            if (conn != null) conn.close();
        }
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }
}