Error in CRUD UPDATE command - java.sql.Sqlexception: No value specified for Parameter 8

Question

I am building a Java system with connection to Mysql database, but I stopped at a certain point because I can’t locate the error. My update method happens an error in Junit, but I can’t figure out what it is.

Follows parts of the code:

package com.sistemacliente.DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.sistemacliente.domain.Balada;
import com.sistemacliente.domain.Cliente;
import com.sistemacliente.factory.ConexaoFactory;

public class ClienteDAO {


    public void salvar(Cliente c) throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO cliente");
        sql.append("(nome,sobrenome,telefone,email,data,valor,baladas_codigo)");
        sql.append("VALUES (?,?,?,?,?,?,?)");

        Connection conexao = ConexaoFactory.conectar();

        PreparedStatement comando = conexao.prepareStatement(sql.toString());
        comando.setString(1, c.getNome());
        comando.setString(2, c.getSobrenome());
        comando.setInt(3, c.getTelefone());
        comando.setString(4, c.getEmail());
        comando.setDate(5, c.getData());
        comando.setDouble(6, c.getValor());
        comando.setInt(7, c.getBalada().getCodigo());

        comando.executeUpdate();

    }

    public ArrayList<Cliente> listar() throws SQLException {

        StringBuilder sql = new StringBuilder();
        sql.append(
                "SELECT c.codigo, c.nome, c.sobrenome, c.telefone, c.email, c.data, c.valor, b.codigo, b.descricao ");
        sql.append("FROM cliente c   ");
        sql.append("INNER JOIN balada b ON b.codigo = c.baladas_codigo");

        Connection conexao = ConexaoFactory.conectar();

        PreparedStatement comando = conexao.prepareStatement(sql.toString());

        ResultSet resultado = comando.executeQuery();

        ArrayList<Cliente> lista = new ArrayList<Cliente>();

        while (resultado.next()) {
            Balada b = new Balada();
            b.setCodigo(resultado.getInt("b.codigo"));
            b.setDescricao(resultado.getString("b.descricao"));

            Cliente c = new Cliente();
            c.setCodigo(resultado.getInt("c.codigo"));
            c.setNome(resultado.getString("c.nome"));
            c.setSobrenome(resultado.getString("c.sobrenome"));
            c.setTelefone(resultado.getInt("c.telefone"));
            c.setEmail(resultado.getString("c.email"));
            c.setData(resultado.getDate("c.data"));
            c.setValor(resultado.getDouble("c.valor"));
            c.setBalada(b);

            lista.add(c);
        }
        return lista;

    }

    public void excluir(Cliente c) throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM cliente ");
        sql.append("WHERE codigo = ?");

        Connection conexao = ConexaoFactory.conectar();

        PreparedStatement comando = conexao.prepareStatement(sql.toString());
        comando.setInt(1, c.getCodigo());
        comando.executeUpdate();

    } 

    public void atualizar(Cliente c) throws SQLException {

        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE cliente");
        sql.append("SET codigo = ? , nome = ? , sobrenome = ? , telefone = ? ,"
                + " email = ? ,  data = ? , valor = ? , baladas_codigo = ? ");
        sql.append("WHERE codigo =  ? ");

        Connection conexao = ConexaoFactory.conectar();

        PreparedStatement comando = conexao.prepareStatement(sql.toString());

        comando.setString(1, c.getNome());
        comando.setString(2, c.getSobrenome());
        comando.setInt(3, c.getTelefone());
        comando.setString(4, c.getEmail());
        comando.setDate(5, c.getData());
        comando.setDouble(6, c.getValor());
        comando.setInt(7, c.getCodigo());

        comando.executeUpdate();

    } 
}

And here’s Junit’s part:

    @Test
    public void atualizar() throws SQLException {

        Cliente c = new Cliente();
        c.setCodigo(7);
        c.setNome("Koppa");
        c.setSobrenome("Troppa");
        c.setTelefone(11111);
        c.setEmail("[email protected]");
        c.setData(new Date(2016/07/10));
        c.setValor(5.00);

        Balada b = new Balada();
        b.setCodigo(14);
        c.setBalada(b);

        ClienteDAO dao = new ClienteDAO();
        dao.atualizar(c);

    }
}

Here is the error generated:

java.sql.Sqlexception: No value specified for Parameter 8

Screenshot of the error:

Answer 1

1. There’s no point in using the StringBuilder to build a String each time each method is called, always the same String will be built. Use a String Pre-made fixed is much better and simpler.

2. Note that if an exception is made, the ResultSet, the PreparedStatement and/or the Connection are not closed anywhere, and therefore are kept open, which can cause various problems. Even if you call the methods close() explicitly, should take care to ensure that they are called even if the execution is aborted with an exception (and hence the use of finally should be remembered). However, it is for this type of situation that the syntax Try-with-Resources was conceived in Java 7.

3. Set the type to be ArrayList<AlgumaCoisa> is not considered good programming practice when List<AlgumaCoisa> is sufficient. The main reason is that using ArrayList<AlgumaCoisa> (except in the constructor) means that you are coding for an implementation, whereas with List<AlgumaCoisa> You’re coding for an interface. The good practices of object-oriented software development say that the ideal is to code for an interface and not for an implementation.

4. Use the diamond syntax that Java 7 introduced to avoid repeating generic types in constructors.

5. This code right here:

   StringBuilder sql = new StringBuilder();
   sql.append("UPDATE cliente");
   sql.append("SET codigo = ? , nome = ? , sobrenome = ? , telefone = ? ,"
           + " email = ? ,  data = ? , valor = ? , baladas_codigo = ? ");
   sql.append("WHERE codigo =  ? ");
   

   Produces the following String:

   UPDATE clienteSET codigo = ? , nome = ? , sobrenome = ? , telefone = ? , email = ? ,  data = ? , valor = ? , baladas_codigo = ? WHERE codigo =  ? 
   

   This String has two problems. The first problem is that when using the StringBuilder, you didn’t pay attention to the spaces and ended up producing a clienteSET all together, which obviously gives a signaling error in SQL.

   The second problem is that it makes no sense for you to put the client code twice, since the client code entered in the WHERE (the client to be changed) is the same SET. You’re not trying to change the client code here, and it only makes sense to put in SET what you want to change. So being the first codigo = ? shouldn’t be there.

6. Your update code does not define all fields that should be defined. Failed to fill in the ballad code field and also missed the first client code (but according to item 5 above, this should not be in the same SQL).

Here’s what your code looks like with all these problems fixed:

package com.sistemacliente.DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.sistemacliente.domain.Balada;
import com.sistemacliente.domain.Cliente;
import com.sistemacliente.factory.ConexaoFactory;

public class ClienteDAO {

    private static final String SQL_SALVAR = "INSERT INTO cliente (nome, sobrenome, telefone, email, data, valor, baladas_codigo) VALUES (?, ?, ?, ?, ?, ?, ?)";

    public void salvar(Cliente c) throws SQLException {   
        try (
            Connection conexao = ConexaoFactory.conectar();
            PreparedStatement comando = conexao.prepareStatement(SQL_SALVAR);
        ) {
            comando.setString(1, c.getNome());
            comando.setString(2, c.getSobrenome());
            comando.setInt(3, c.getTelefone());
            comando.setString(4, c.getEmail());
            comando.setDate(5, c.getData());
            comando.setDouble(6, c.getValor());
            comando.setInt(7, c.getBalada().getCodigo());
            comando.executeUpdate();
        }
    }

    private static final String SQL_LISTAR = "SELECT c.codigo, c.nome, c.sobrenome, c.telefone, c.email, c.data, c.valor, b.codigo, b.descricao FROM cliente c INNER JOIN balada b ON b.codigo = c.baladas_codigo";

    public List<Cliente> listar() throws SQLException {
        List<Cliente> lista = new ArrayList<>();

        try (
            Connection conexao = ConexaoFactory.conectar();
            PreparedStatement comando = conexao.prepareStatement(SQL_LISTAR);
            ResultSet resultado = comando.executeQuery();
        ) {
            while (resultado.next()) {
                Balada b = new Balada();
                b.setCodigo(resultado.getInt("b.codigo"));
                b.setDescricao(resultado.getString("b.descricao"));

                Cliente c = new Cliente();
                c.setCodigo(resultado.getInt("c.codigo"));
                c.setNome(resultado.getString("c.nome"));
                c.setSobrenome(resultado.getString("c.sobrenome"));
                c.setTelefone(resultado.getInt("c.telefone"));
                c.setEmail(resultado.getString("c.email"));
                c.setData(resultado.getDate("c.data"));
                c.setValor(resultado.getDouble("c.valor"));
                c.setBalada(b);

                lista.add(c);
            }
        }
        return lista;
    }

    private static final String SQL_EXCLUIR = "DELETE FROM cliente WHERE codigo = ?";

    public void excluir(Cliente c) throws SQLException {
        try (
            Connection conexao = ConexaoFactory.conectar();
            PreparedStatement comando = conexao.prepareStatement(SQL_EXCLUIR);
        ) {
            comando.setInt(1, c.getCodigo());
            comando.executeUpdate();
        }
    }

    private static final String SQL_ATUALIZAR = "UPDATE cliente SET nome = ?, sobrenome = ?, telefone = ?, email = ?, data = ?, valor = ?, baladas_codigo = ? WHERE codigo = ?";

    public void atualizar(Cliente c) throws SQLException {
        try (
            Connection conexao = ConexaoFactory.conectar();
            PreparedStatement comando = conexao.prepareStatement(SQL_ATUALIZAR);
        ) {
            comando.setString(1, c.getNome());
            comando.setString(2, c.getSobrenome());
            comando.setInt(3, c.getTelefone());
            comando.setString(4, c.getEmail());
            comando.setDate(5, c.getData());
            comando.setDouble(6, c.getValor());
            comando.setInt(7, c.getBalada().getCodigo());
            comando.setInt(8, c.getCodigo());
            comando.executeUpdate();
        }
    } 
}

Answer 2

Well, it’s been a while since I programmed in java, but I believe your mistake is here:

    StringBuilder sql = new StringBuilder();
    sql.append("UPDATE cliente");
    sql.append("SET codigo = ? , nome = ? , sobrenome = ? , telefone = ? ,"
            + " email = ? ,  data = ? , valor = ? , baladas_codigo = ? ");
    sql.append("WHERE codigo =  ? ");

First point, that it is not recommended to update the code when it is the primary key and especially when it is in condition WHERE. And notice that Voce passed 9 times the ?, then it will expect you to pass 9 values later what in case Voce did not. You put only 7, missing the baladas_codigo:

    comando.setString(1, c.getNome());
    comando.setString(2, c.getSobrenome());
    comando.setInt(3, c.getTelefone());
    comando.setString(4, c.getEmail());
    comando.setDate(5, c.getData());
    comando.setDouble(6, c.getValor());
    comando.setInt(7, c.getCodigo());

Right would be something close to it:

 public void atualizar(Cliente c) throws SQLException {

    StringBuilder sql = new StringBuilder();
    sql.append("UPDATE cliente");
    sql.append("SET nome = ? , sobrenome = ? , telefone = ? ,"
            + " email = ? ,  data = ? , valor = ? , baladas_codigo = ? ");
    sql.append("WHERE codigo =  ? ");

    Connection conexao = ConexaoFactory.conectar();

    PreparedStatement comando = conexao.prepareStatement(sql.toString());

    comando.setString(1, c.getNome());
    comando.setString(2, c.getSobrenome());
    comando.setInt(3, c.getTelefone());
    comando.setString(4, c.getEmail());
    comando.setDate(5, c.getData());
    comando.setDouble(6, c.getValor());
    comando.setDouble(7, c.getBaladas_codigo());// Aqui é só um exemplo, poi pelo que vi, esta em outra tabela, ou você retira do SQL ou passa de alguma forma

    comando.setInt(8, c.getCodigo());

    comando.executeUpdate();

} 

I believe it is the mistake that is happening. Anything comments here, if I could not explain well!