Java Code Examples for java.sql.Statement.getGeneratedKeys()

The following are Jave code examples for showing how to use getGeneratedKeys() of the java.sql.Statement class. You can vote up the examples you like. Your votes will be used in our system to get more good examples.
Example 1
Project: BibliotecaPS   File: StatementRegressionTest.java   Source Code and License Vote up 7 votes
/**
 * Check the update count and returned keys for an INSERT query using a Statement object. If expectedUpdateCount < 0 then runs Statement.execute() otherwise
 * Statement.executeUpdate().
 */
public void testBug71672Statement(int testStep, Connection testConn, String query, int expectedUpdateCount, int[] expectedKeys) throws SQLException {
    Statement testStmt = testConn.createStatement();

    if (expectedUpdateCount < 0) {
        assertFalse(testStep + ". Stmt.execute() result", testStmt.execute(query, Statement.RETURN_GENERATED_KEYS));
    } else {
        assertEquals(testStep + ". Stmt.executeUpdate() result", expectedUpdateCount, testStmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS));
    }

    ResultSet testRS = testStmt.getGeneratedKeys();
    for (int k : expectedKeys) {
        assertTrue(testStep + ". Row expected in generated keys ResultSet", testRS.next());
        assertEquals(testStep + ". Wrong generated key", k, testRS.getInt(1));
    }
    assertFalse(testStep + ". No more rows expected in generated keys ResultSet", testRS.next());
    testRS.close();
    testStmt.close();
}
 
Example 2
Project: the-vigilantes   File: StatementRegressionTest.java   Source Code and License Vote up 6 votes
/**
 * Check the update count and returned keys for an INSERT query using a Statement object. If expectedUpdateCount < 0 then runs Statement.execute() otherwise
 * Statement.executeUpdate().
 */
public void testBug71672Statement(int testStep, Connection testConn, String query, int expectedUpdateCount, int[] expectedKeys) throws SQLException {
    Statement testStmt = testConn.createStatement();

    if (expectedUpdateCount < 0) {
        assertFalse(testStep + ". Stmt.execute() result", testStmt.execute(query, Statement.RETURN_GENERATED_KEYS));
    } else {
        assertEquals(testStep + ". Stmt.executeUpdate() result", expectedUpdateCount, testStmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS));
    }

    ResultSet testRS = testStmt.getGeneratedKeys();
    for (int k : expectedKeys) {
        assertTrue(testStep + ". Row expected in generated keys ResultSet", testRS.next());
        assertEquals(testStep + ". Wrong generated key", k, testRS.getInt(1));
    }
    assertFalse(testStep + ". No more rows expected in generated keys ResultSet", testRS.next());
    testRS.close();
    testStmt.close();
}
 
Example 3
Project: SistemaAlmoxarifado   File: LogErroDAO.java   Source Code and License Vote up 6 votes
public static LogErro create(LogErro logErro) throws SQLException {
    Statement stm
            = Database.createConnection().
                    createStatement();
    String sql
            = "INSERT INTO logs_erros (`usuario`, `data`, `erro`) VALUES ('"
            + logErro.getUsuario().getId() + "','"
            + new Timestamp((System.currentTimeMillis())) + "','"
            + logErro.getErro() + "')";

    stm.execute(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stm.getGeneratedKeys();
    rs.next();
    int key = rs.getInt(1);
    logErro.setId(key);
    return logErro;
}
 
Example 4
Project: SistemaAlmoxarifado   File: UsuarioDAO.java   Source Code and License Vote up 6 votes
public static Usuario create(Usuario usuario) throws SQLException {
    Statement stm
            = Database.createConnection().
                    createStatement();
    String sql
            = "INSERT INTO usuarios (`email`, `senha`, `status`, `admin`, `servidor`) VALUES ('"
            + usuario.getEmail() + "','"
            + usuario.getSenha() + "','"
            + usuario.isAtivo() + "','"
            + usuario.isAdmin() + "','"
            + usuario.getServidor().getId() + "')";

    stm.execute(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stm.getGeneratedKeys();
    rs.next();
    usuario.setId(rs.getInt(1));
    return usuario;
}
 
Example 5
Project: OpenVertretung   File: StatementRegressionTest.java   Source Code and License Vote up 6 votes
/**
 * Check the update count and returned keys for an INSERT query using a Statement object. If expectedUpdateCount < 0 then runs Statement.execute() otherwise
 * Statement.executeUpdate().
 */
public void testBug71672Statement(int testStep, Connection testConn, String query, int expectedUpdateCount, int[] expectedKeys) throws SQLException {
    Statement testStmt = testConn.createStatement();

    if (expectedUpdateCount < 0) {
        assertFalse(testStep + ". Stmt.execute() result", testStmt.execute(query, Statement.RETURN_GENERATED_KEYS));
    } else {
        assertEquals(testStep + ". Stmt.executeUpdate() result", expectedUpdateCount, testStmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS));
    }

    ResultSet testRS = testStmt.getGeneratedKeys();
    for (int k : expectedKeys) {
        assertTrue(testStep + ". Row expected in generated keys ResultSet", testRS.next());
        assertEquals(testStep + ". Wrong generated key", k, testRS.getInt(1));
    }
    assertFalse(testStep + ". No more rows expected in generated keys ResultSet", testRS.next());
    testRS.close();
    testStmt.close();
}
 
Example 6
Project: SistemaAlmoxarifado   File: ProdutoDAO.java   Source Code and License Vote up 6 votes
public static Produto create(Produto produto) throws SQLException {
    Statement stm
            = Database.createConnection().
                    createStatement();
    String sql
            = "INSERT INTO produtos (`descricao`, `unidade`, `estoque_minimo`) VALUES ('"
            + produto.getDescricao() + "','"
            + produto.getUnidade().getId() + "','"
            + produto.getEstoque_minimo() + "')";

    stm.execute(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stm.getGeneratedKeys();
    rs.next();
    int key = rs.getInt(1);
    produto.setId(key);
    return produto;
}
 
Example 7
Project: SistemaAlmoxarifado   File: EntradaItemDAO.java   Source Code and License Vote up 6 votes
public static EntradaItem create(EntradaItem entradaItem) throws SQLException {
    Statement stm
            = Database.createConnection().
                    createStatement();
    String sql
            = "INSERT INTO entrada_itens (`produto`, `entrada`, `quantidade`,"
            + "`validade`, `lote`, `valor_unitario`) VALUES ('"
            + entradaItem.getProduto().getId() + "','"
            + entradaItem.getEntradaId() + "','"
            + entradaItem.getQtd() + "','"
            + entradaItem.getValidade() + "','"
            + entradaItem.getLote() + "','"
            + entradaItem.getValor_unitario() + "')";

    stm.execute(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stm.getGeneratedKeys();
    rs.next();
    int key = rs.getInt(1);
    entradaItem.setId(key);
    return entradaItem;
}
 
Example 8
Project: SistemaAlmoxarifado   File: ServidorDAO.java   Source Code and License Vote up 6 votes
public static Servidor create(Servidor servidor) throws SQLException {
    Statement stm
            = Database.createConnection().
                    createStatement();
    String sql
            = "INSERT INTO servidores (`nome`, `siape`, `funcao`, `setor`) VALUES ('"
            + servidor.getNome() + "','"
            + servidor.getSiape() + "','"
            + servidor.getFuncao() + "','"
            + servidor.getSetor().getId() + "')";

    stm.execute(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stm.getGeneratedKeys();
    rs.next();
    servidor.setId(rs.getInt(1));
    return servidor;
}
 
Example 9
Project: waterrower-workout   File: DatabaseConnectionService.java   Source Code and License Vote up 6 votes
private int getAutoGeneratedKey(Statement statement) throws SQLException {
    int key = 0;

    ResultSet resultSet = statement.getGeneratedKeys();
    if (resultSet == null) {
        Log.warn(SQL, "No auto generated key found.");
        return -1;
    }

    while(resultSet.next())
        key = resultSet.getInt(1);

    Log.debug(SQL, "The auto generated key is '"+key+"'.");

    return key;
}
 
Example 10
Project: SistemaAlmoxarifado   File: HistoricoRelatorioDAO.java   Source Code and License Vote up 6 votes
public static HistoricoRelatorio create(HistoricoRelatorio historico) throws SQLException {
    Statement stm
            = Database.createConnection().
                    createStatement();
    String sql
            = "INSERT INTO historico_relatorios (`produto`, `mes`, "
            + "`quantidade`, `valortotal`) VALUES ('"
            + historico.getProduto().getId() + "','"
            + historico.getMes() + "','"
            + historico.getQtd() + "','"
            + historico.getValorTotal() + "')";

    stm.execute(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stm.getGeneratedKeys();
    rs.next();
    historico.setId(rs.getInt(1));
    return historico;
}
 
Example 11
Project: SistemaAlmoxarifado   File: EmpenhoItemDAO.java   Source Code and License Vote up 6 votes
public static EmpenhoItem create(EmpenhoItem item) throws SQLException {
    Statement stm = Database.createConnection().createStatement();
    String sql
            = "INSERT INTO itens_empenho (`empenho`, `natureza_despesa`, `produto`,"
            + "`sequencia`, `quantidade`, `valor_unitario`, `item_processo`) VALUES ('"
            + item.getEmpenhoId() + "','"
            + item.getNaturezaDespesa().getId() + "','"
            + item.getProduto().getId() + "','"
            + item.getSequencia() + "','"
            + item.getQtd() + "','"
            + item.getValorUnitario() + "','"
            + item.getItemProcesso() + "')";

    stm.execute(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stm.getGeneratedKeys();
    rs.next();
    item.setId(rs.getInt(1));
    return item;
}
 
Example 12
Project: SistemaAlmoxarifado   File: EmpenhoDAO.java   Source Code and License Vote up 5 votes
public static Empenho create(Empenho empenho) throws SQLException {
    Statement stm
            = Database.createConnection().
                    createStatement();
    String sql
            = "INSERT INTO empenhos (`fornecedor`, `emissao`, `numero`, `observacao`, `valor_total`) VALUES ('"
            + empenho.getFornecedor().getId() + "','"
            + empenho.getEmissao() + "','"
            + empenho.getNumero() + "','"
            + empenho.getObservacao() + "','"
            + empenho.getValorTotal() + "')";

    stm.execute(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stm.getGeneratedKeys();
    rs.next();
    int key = rs.getInt(1);
    empenho.setId(key);
    
    if(empenho.getItens() != null){
        for (EmpenhoItem item : empenho.getItens()) {
            item.setEmpenhoId(key);
            EmpenhoItemDAO.create(item);
            
            if(ProdutoFornecedorDAO.checkNotExist(
                    empenho.getFornecedor().getId(), 
                    item.getProduto().getId()))
                ProdutoFornecedorDAO.create(
                        new ProdutoFornecedor(empenho.getFornecedor().getId(), 
                                item.getProduto().getId()));
        }
    }
    
    return empenho;
}
 
Example 13
Project: SistemaAlmoxarifado   File: VisitanteDAO.java   Source Code and License Vote up 5 votes
public static Visitante create(Visitante visitante) throws SQLException {
    Statement stm
            = Database.createConnection().
                    createStatement();
    String sql
            = "INSERT INTO visitantes (`quantidade`, `setor`) VALUES ('"
            + visitante.getQtd() + "','"
            + visitante.getSetor().getId() + "')";

    stm.execute(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stm.getGeneratedKeys();
    rs.next();
    visitante.setId(rs.getInt(1));
    return visitante;
}
 
Example 14
Project: redesocial   File: DAOBase.java   Source Code and License Vote up 5 votes
/**
 * Retorna o ID que foi gerado no banco de dados
 * @param stmt comando enviado
 * @return ID gerado pelo banco de dados
 * @throws SQLException 
 */
protected Integer getId(Statement stmt) throws SQLException {
    ResultSet chavesGeradas = stmt.getGeneratedKeys();
    
    if (chavesGeradas.next()){
        return chavesGeradas.getInt(1);
    } else {
        return null;
    }
}
 
Example 15
Project: SistemaAlmoxarifado   File: UnidadeDAO.java   Source Code and License Vote up 5 votes
public static Unidade create(Unidade unidade) throws SQLException {
    Statement stm
            = Database.createConnection().
                    createStatement();
    String sql
            = "INSERT INTO unidades (`nome`, `abreviacao`) VALUES ('"
            + unidade.getNome() + "','"
            + unidade.getAbreviacao() + "')";

    stm.execute(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stm.getGeneratedKeys();
    rs.next();
    unidade.setId(rs.getInt(1));
    return unidade;
}
 
Example 16
Project: SistemaAlmoxarifado   File: FornecedorDAO.java   Source Code and License Vote up 5 votes
public static Fornecedor create(Fornecedor fornecedor) throws SQLException {
    Statement stm
            = Database.createConnection().
                    createStatement();
    String sql
            = "INSERT INTO fornecedores (`razao_social`, `nome_fantasia`, `cnpj`) VALUES ('"
            + fornecedor.getRazaoSocial() + "','"
            + fornecedor.getNomeFantasia() + "','"
            + fornecedor.getCnpj() + "')";
    stm.execute(sql, Statement.RETURN_GENERATED_KEYS);
    ResultSet rs = stm.getGeneratedKeys();
    rs.next();
    int key = rs.getInt(1);
    fornecedor.setId(key);
    
    if(fornecedor.getEndereco() != null){
        for (Endereco end : fornecedor.getEndereco()) {
            end.setFornecedorId(key);
            EnderecoDAO.create(end);
        }
    }
    
    if(fornecedor.getContato() != null){
        for (Contato contato : fornecedor.getContato()) {
            contato.setFornecedorId(key);
            ContatoDAO.create(contato);
        }
    }
    

    return fornecedor;
}
 
Example 17
Project: iDataBaseConnection   File: DataBase.java   Source Code and License Vote up 5 votes
@Override
public int exeSQLInsert(String sql) {
	// TODO Auto-generated method stub
	ConnectionObject conn = ConnectionManager.borrowConnectionObject(connInfo);
	try 
	{
		Statement stat = conn.getConnection().createStatement();
		//stat.executeUpdate(sql);
		int autoGeneratedKeys = 0;
		stat.executeUpdate(sql);
		ResultSet rs = stat.getGeneratedKeys();
		if(rs.next())
		{
			autoGeneratedKeys = rs.getInt(1);
		}
		stat.close();
		return autoGeneratedKeys;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		DBCException.logException(DBCException.E_SQL, e);
		return 0;
	}
	finally
	{
		ConnectionManager.returnConnectionObject(conn);
	}
}
 
Example 18
Project: ProyectoPacientes   File: StatementRegressionTest.java   Source Code and License Vote up 4 votes
public void testBug39956() throws Exception {
    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    ResultSet enginesRs = this.conn.createStatement().executeQuery("SHOW ENGINES");

    while (enginesRs.next()) {
        if ("YES".equalsIgnoreCase(enginesRs.getString("Support")) || "DEFAULT".equalsIgnoreCase(enginesRs.getString("Support"))) {

            String engineName = enginesRs.getString("Engine");

            if ("CSV".equalsIgnoreCase(engineName) || "BLACKHOLE".equalsIgnoreCase(engineName) || "FEDERATED".equalsIgnoreCase(engineName)
                    || "MRG_MYISAM".equalsIgnoreCase(engineName) || "PARTITION".equalsIgnoreCase(engineName) || "EXAMPLE".equalsIgnoreCase(engineName)
                    || "PERFORMANCE_SCHEMA".equalsIgnoreCase(engineName) || engineName.endsWith("_SCHEMA")) {
                continue; // not supported
            }

            if ("ARCHIVE".equalsIgnoreCase(engineName) && !versionMeetsMinimum(5, 1, 6)) {
                continue;
            }

            String tableName = "testBug39956_" + engineName;

            Connection twoConn = getConnectionWithProps("sessionVariables=auto_increment_increment=2");

            try {
                for (int i = 0; i < 2; i++) {
                    createTable(tableName, "(k int primary key auto_increment, p varchar(4)) ENGINE=" + engineName);

                    ((com.mysql.jdbc.Connection) twoConn).setRewriteBatchedStatements(i == 1);

                    this.pstmt = twoConn.prepareStatement("INSERT INTO " + tableName + " (p) VALUES (?)", Statement.RETURN_GENERATED_KEYS);
                    this.pstmt.setString(1, "a");
                    this.pstmt.addBatch();
                    this.pstmt.setString(1, "b");
                    this.pstmt.addBatch();
                    this.pstmt.executeBatch();

                    this.rs = this.pstmt.getGeneratedKeys();

                    this.rs.next();
                    assertEquals("For engine " + engineName + ((i == 1) ? " rewritten " : " plain "), 1, this.rs.getInt(1));
                    this.rs.next();
                    assertEquals("For engine " + engineName + ((i == 1) ? " rewritten " : " plain "), 3, this.rs.getInt(1));

                    createTable(tableName, "(k int primary key auto_increment, p varchar(4)) ENGINE=" + engineName);
                    Statement twoStmt = twoConn.createStatement();
                    for (int j = 0; j < 10; j++) {
                        twoStmt.addBatch("INSERT INTO " + tableName + " (p) VALUES ('" + j + "')");
                    }

                    twoStmt.executeBatch(); // No getGeneratedKeys() support in JDBC spec, but we allow it...might have to rewrite test if/when we don't
                    this.rs = twoStmt.getGeneratedKeys();

                    int key = 1;

                    for (int j = 0; j < 10; j++) {
                        this.rs.next();
                        assertEquals("For engine " + engineName + ((i == 1) ? " rewritten " : " plain "), key, this.rs.getInt(1));
                        key += 2;
                    }
                }
            } finally {
                if (twoConn != null) {
                    twoConn.close();
                }
            }
        }
    }
}
 
Example 19
Project: BibliotecaPS   File: StatementRegressionTest.java   Source Code and License Vote up 4 votes
private void checkOpenResultsFor44056(Statement newStmt) throws SQLException {
    this.rs = newStmt.getGeneratedKeys();
    assertEquals(0, ((com.mysql.jdbc.Statement) newStmt).getOpenResultSetCount());
    this.rs.close();
    assertEquals(0, ((com.mysql.jdbc.Statement) newStmt).getOpenResultSetCount());
}
 
Example 20
Project: OpenVertretung   File: StatementRegressionTest.java   Source Code and License Vote up 4 votes
public void testBug39956() throws Exception {
    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    ResultSet enginesRs = this.conn.createStatement().executeQuery("SHOW ENGINES");

    while (enginesRs.next()) {
        if ("YES".equalsIgnoreCase(enginesRs.getString("Support")) || "DEFAULT".equalsIgnoreCase(enginesRs.getString("Support"))) {

            String engineName = enginesRs.getString("Engine");

            if ("CSV".equalsIgnoreCase(engineName) || "BLACKHOLE".equalsIgnoreCase(engineName) || "FEDERATED".equalsIgnoreCase(engineName)
                    || "MRG_MYISAM".equalsIgnoreCase(engineName) || "PARTITION".equalsIgnoreCase(engineName) || "EXAMPLE".equalsIgnoreCase(engineName)
                    || "PERFORMANCE_SCHEMA".equalsIgnoreCase(engineName) || engineName.endsWith("_SCHEMA")) {
                continue; // not supported
            }

            if ("ARCHIVE".equalsIgnoreCase(engineName) && !versionMeetsMinimum(5, 1, 6)) {
                continue;
            }

            String tableName = "testBug39956_" + engineName;

            Connection twoConn = getConnectionWithProps("sessionVariables=auto_increment_increment=2");

            try {
                for (int i = 0; i < 2; i++) {
                    createTable(tableName, "(k int primary key auto_increment, p varchar(4)) ENGINE=" + engineName);

                    ((com.mysql.jdbc.Connection) twoConn).setRewriteBatchedStatements(i == 1);

                    this.pstmt = twoConn.prepareStatement("INSERT INTO " + tableName + " (p) VALUES (?)", Statement.RETURN_GENERATED_KEYS);
                    this.pstmt.setString(1, "a");
                    this.pstmt.addBatch();
                    this.pstmt.setString(1, "b");
                    this.pstmt.addBatch();
                    this.pstmt.executeBatch();

                    this.rs = this.pstmt.getGeneratedKeys();

                    this.rs.next();
                    assertEquals("For engine " + engineName + ((i == 1) ? " rewritten " : " plain "), 1, this.rs.getInt(1));
                    this.rs.next();
                    assertEquals("For engine " + engineName + ((i == 1) ? " rewritten " : " plain "), 3, this.rs.getInt(1));

                    createTable(tableName, "(k int primary key auto_increment, p varchar(4)) ENGINE=" + engineName);
                    Statement twoStmt = twoConn.createStatement();
                    for (int j = 0; j < 10; j++) {
                        twoStmt.addBatch("INSERT INTO " + tableName + " (p) VALUES ('" + j + "')");
                    }

                    twoStmt.executeBatch(); // No getGeneratedKeys() support in JDBC spec, but we allow it...might have to rewrite test if/when we don't
                    this.rs = twoStmt.getGeneratedKeys();

                    int key = 1;

                    for (int j = 0; j < 10; j++) {
                        this.rs.next();
                        assertEquals("For engine " + engineName + ((i == 1) ? " rewritten " : " plain "), key, this.rs.getInt(1));
                        key += 2;
                    }
                }
            } finally {
                if (twoConn != null) {
                    twoConn.close();
                }
            }
        }
    }
}