How to implement a class to use Preparedstatement through it?

Question

I have the following class Connect.java

package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class Conecta {
    //Pode ser alterado para Mysql...
    private String DRIVER = "com.mysql.jdbc.Driver";
    private String BD = "testes";
    private String URL = "jdbc:mysql://localhost:3306/"+BD;
    private String USERNAME = "root";
    private String PASSWORD = "";
    private Connection conexao;
    private Statement stm; //trocar por PreparedStatement

    private String msg;

    public Conecta() {
        this.msg = this.iniciaConexao();

    }

    public Conecta(String bd, String user, String senha) {
        this.BD = bd;
        this.USERNAME = user;
        this.PASSWORD = senha;
        this.msg = this.iniciaConexao();

    }

    public String iniciaConexao() {
        try {
            Class.forName(this.DRIVER);
            this.conexao = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // Definimos o objeto responsável por executar os comandos
            this.stm = this.getConexao().createStatement();
            return "sucesso";

        } catch (ClassNotFoundException e) {
            this.conexao = null;
            return "Não foi possivel encontrar o driver de banco: " + e.getMessage();
        } catch (SQLException e) {
            this.conexao = null;
            return "Erro!" + e.getMessage();
        }
    }

    public PreparedStatement getPS(String sql) {
        try {
            return this.getConexao().prepareStatement(sql);
        } catch (SQLException ex) {
            System.err.println("Erro: "+ex);
            return null;
        }
    }


    public String fechaConexao() {
        try {
            if (this.getConexao() != null) {
                this.getConexao().close();
                this.conexao = null;
            }
            if (this.getStm() != null) {
                this.stm = null;
            }
            return "Conexão Encerrada";
        } catch (SQLException ex) {
            return "Houve erro no fechamento da conexão! "+ex.getMessage();
        }
    }

    public Connection getConexao() {
        return conexao;
    }

    public Statement getStm() {
        return stm;
    }

    public String getMsg() {
        return msg;
    }


    public boolean insert(String sql) throws SQLException {

        this.stm = this.getConexao().prepareStatement(sql);


        return false;
        //Obter um statement
        //Statement stmt = con.getConection().createStatement();
        //executar o comando de Update seguido de um select
        //res = stmt.executeUpdate(sql);
    }

}

I need to initialize it in my main class for entering data into my database by making use of Preparedstatement, passing the parameters to the Connect class..

I’m trying to do it this way:

public static void main(String[] args) {

        try {
            Conecta c = new Conecta();


            String nomeCraque = "Coca";
            String sql = "insert into produto (nome) values (?)";

            c.getPS(sql).setString(1, "Cerveja");
            c.getPS(sql).execute();

            //DUUUUUUUVIDA AQUI, como executar?
            //PreparedStatement ps = c.getStm().execute(sql);
               // c.getStm().execute(sql);


            c.fechaConexao();


        } catch (Exception ex) {
            System.err.println("Erro:" + ex.getStackTrace());
        }
    }

They could help me solve this problem. What I need to modify in my classes?

If necessary I can provide more details.

Thanks in advance.

Answer 1

Agnaldo Junior if I understand well what you want is to use the right Preparedstatement!

So come on

public static void main(String[] args) {
    try {
        Conecta c = new Conecta();

        String nomeCraque = "Coca";
        String sql = "insert into produto(nome) values(?)";

        //Criando o objeto PreparedStatement
        PreparedStatement ps = c.getPS(sql)
        //Adicionando os dados ao Objeto PS.
        //O número indica a posição da coluna na sequencia o valor que será inserido neste campo.
        ps.setString(1,nomeCraque);
        ps.executeUpdate();
        ps.close();
        c.fechaConexao();


    } catch (Exception ex) {
        System.err.println("Erro:" + ex.getStackTrace());
    }
}

See if that solves your problem...

Answer 2

You can do it like this:

public static void main(String[] args) {

    try {
        Conecta c = new Conecta();

        String nomeCraque = "Coca";
        String sql = "insert into produto (nome) values (?)";

        //Cria um PreparedStatement para a sql definida na variável sql
        PreparedStatement preparedStatement = dbConnection.prepareStatement(sql);
        //Inclui o parâmetro "Cerveja" do tipo String no index 1 de parâmetros
        preparedStatement.setString(1, "Cerveja");
        //Executa a consulta
        preparedStatement.executeUpdate();

        c.fechaConexao();


    } catch (Exception ex) {
        System.err.println("Erro:" + ex.getStackTrace());
    }
}

---

See the link @diegofm commented on in their question, because whenever we work with database connections (and some other resources) we need to close them.