Problem with database insertion

Asked

Viewed 72 times

1

I have in my system the following code:

    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("idDep");
            }
            r.close();
            state.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DependenteDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    return result;
}

My problem is that when the system gets on the line 

state.execute(sql);

The code does not run in my database, and soon after the system jumps straight to the line

return result;

I’ve tried everything, but I can’t find where my mistake is. Does anyone have any idea what it might be?

java mysql jdbc

  • If you are going for the return it is because some exception is thrown, no? What is being written in the log?

  • I even looked at the log to see if there was any Exception, but apache does not accuse any.

  • Placed a breakpoint in the catch to be able to follow and see what the exception is?

  • Set up the log in your application because there is definitely an exception being released, as the coloegas mentioned. Without this exception it becomes difficult to know the problem. It can be by inconsistent data, some column or table with wrong names and so on.

  • @Raphaelrosa by the two commented lines is likely to be giving error, since it generates a statement invalid. I have not tested, but try to uncomment the lines and test again or, continuing error, then yes inform in your question the error.

  • Please read this: http://pt.wikipedia.org/wiki/Injeção_de_SQL and then take a look at this: https://xkcd.com/327/

Show 1 more comment

1 answer

2


Your code has several problems. The first one is that when commenting on the date fields in the first SQL, it got misshapen. She waits 9 fields in INSERT, but you only pass 7. Also it was missing a close-parentheses in SQL, since it was commented along with the last date.

Then use the PreparedStatement to avoid problems of injection of SQL:

XKCD

Third, use the syntax of Try-with-Resources.

Fourth, this will only work if you have the logging properly configured:

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

As this was probably generated automatically, I believe you should not have one logging appropriate, so it is better to either set up the logging or trade for something simpler like a printStackTrace(), or relaunch the exception or make some error handling. I decided to use in the code below the printStackTrace().

With that, your code looks like this:

public int insertDependente(Dependente dependente) {
    if (dependente == null) return 0;
    try (Connection conn = new Conexao().getConnection()) {
        String sql = "INSERT INTO Dependente (IdAssoc, NomeDep, SobrenomeDep, RgDep, CpfDep, DtNascDep, emailDep, tipoDep, DtCriacaoDep) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement state = conn.prepareStatement(sql)) {
            state.setInt(1, dependente.getAssociado());
            state.setString(2, dependente.getNome());
            state.setString(3, dependente.getSobrenome());
            state.setString(4, dependente.getRg());
            state.setString(5, dependente.getCpf());
            state.setDate(6, new java.sql.Date(dependente.getNascimento().getTime()));
            state.setString(7, dependente.getEmail());
            state.setString(8, dependente.getTipoDep());
            state.setDate(9, new java.sql.Date(new java.util.Date().getTime()));
            state.execute();
        }

        String sql2 = "SELECT IdDep FROM Dependente WHERE CpfDep = ?";
        try (PreparedStatement state = conn.prepareStatement(sql2)) {
            state.setInt(1, dependente.getCpf());
            try (ResultSet r = state.executeQuery()) {
                int result = 0;
                while (r.next()) {
                    result = r.getInt("idDep");
                }
                return result;
            }
        }
    } catch (SQLException ex) {
        ex.printStackTrace();
        return 0;
    }
}

Browser other questions tagged java mysql jdbc

You are not signed in. Login or sign up in order to post.