Incremental key

Question

Incremental key

Navigation

#1 by (1 votes)

1

People, 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 id key must be incremental. In java I did the following method to 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;
        }

I put null , because it is not important since the key will be incremental. But when I run, the eclipse returns me that the Primary key id can not be null .

Does anyone know what I do?

sql java jdbc

asked by anonymous 16.03.2015 / 01:18

1 answer

How can I convert these strings in PHP? Show a div with jQuery

score 1 · Accepted Answer

Your SQL should look like this:

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

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

In addition, use the try-with-resources syntax if you're in Java 7 or higher, or at least put conn.close() inside a finally if it is 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 properly.

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