Insert not working properly

Question

I have the following code in my application

    public int insert_dependente(Dependente dependente) {
    ResultSet r;
    int result = 0;
    if (!(dependente == null)) {
        try {
            Connection conn = new Conexao().getConnection();
            String sql = "insert into Dependente "
                    + "(IdAssoc,"
                    + "NomeDep,"
                    + "SobrenomeDep,"
                    + "RgDep,"
                    + "CpfDep,"
                    + " DtNascDep,"
                    + " emailDep,"
                    + " tipoDep,"
                    + " DtCriacaoDep)"
                    + " VALUES (" + dependente.getAssociado() + ","
                    + "'" + dependente.getNome() + "',"
                    + "'" + dependente.getSobrenome() + "',"
                    + "'" + dependente.getRg() + "',"
                    + "'" + dependente.getCpf() + "',"
                    //+ new java.sql.Date(dependente.getNascimento().getTime()).toString() + ","
                    + "'" + dependente.getEmail() + "',"
                    + "'" + dependente.getTipoDep() + "'";
                    //+ new java.sql.Date(new java.util.Date().getTime()).toString() + ")";
            Statement state; 
            state = conn.createStatement();
            state.execute(sql);
            state.close();

            String sql2 = "SELECT IdDep FROM Dependente WHERE CpfDep = " + dependente.getCpf();
            state = conn.createStatement();
            r = state.executeQuery(sql2);
            while (r.next()) {
                result = r.getInt("idAssoc");
            }
            r.close();
            state.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DependenteDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    return result;
}

The problem is that the values are never inserted in my database. And when the system reaches the line

state.execute(sql);

the system ignores everything and jump straight to the line

return result;

I've tried everything but I can't find my error. Can anybody please help me?

Answer 1

I assume, executing your SQL statement raises an exception. It should be somewhere in the log, as you catch it and log it.

The reason: Your SQL insert statement is missing the closing bracket: ).

Apart from that, it is easier and safer to use a PreparedStatement instead of programatically concatenating the final SQL statement out of several variables (see also Should I be using PreparedStatements for all my database inserts in Java? for the reason):

String sql = "INSERT INTO Dependente" +   
             + " (IdAssoc, NomeDep, SobrenomeDep, RgDep, CpfDep, DtNascDep, emailDep, tipoDep, DtCriacaoDep)" +
             + " VALUES (?,?,?,?,?,?,?,?,?)";

try (PrepareStatement pstmt = con.prepareStatement(sql)) {
    pstmt.setLong(1, dependente.getAssociado());
    pstmt.setString(2, dependente.getNome());
    pstmt.setString(3, dependente.getSobrenome());
    ...

    pstmt.executeUpdate();
} // automatically closes statement (see try-with-resource above)

Answer 2

You commented out the bracket at the end of your INSERT statement:

 String sql = "insert into Dependente "
     + "(IdAssoc,"
     + "NomeDep,"

    ...

    //+ new java.sql.Date(new java.util.Date().getTime()).toString() + ")";

This is why you get an SQLException and your code execution is interrupted. You even pass the exception on to your logger, you should be able to find it in your logs:

Logger.getLogger(DependenteDAO.class.getName()).log(Level.SEVERE, null, ex);

Use this code instead:

String sql = "insert into Dependente "
    + "(IdAssoc,"
    + "NomeDep,"
    + "SobrenomeDep,"
    + "RgDep,"
    + "CpfDep,"
    + " DtNascDep,"
    + " emailDep,"
    + " tipoDep,"
    + " DtCriacaoDep)"
    + " VALUES (" + dependente.getAssociado() + ","
    + "'" + dependente.getNome() + "',"
    + "'" + dependente.getSobrenome() + "',"
    + "'" + dependente.getRg() + "',"
    + "'" + dependente.getCpf() + "',"
    + "'" + dependente.getEmail() + "',"
    + "'" + dependente.getTipoDep() + "')";