Use Date type field in Java with MySQL

0

I'm having a little problem, I'm doing a test using the Date field in Java, and I'm having trouble executing it. Here are the codes I'm using: 1st the Code to create the table in MySQL

Create table clientes (
cli_id Integer NOT NULL AUTO_INCREMENT,
cli_nome Varchar(200),
cli_dt_inclusao Date,
cli_endereco Varchar(200),
cli_bairro Varchar(80),
cli_email Varchar(200),
cli_tel Varchar(15),
cli_cidade Varchar(200),
cli_uf Varchar(2), Primary Key (cli_id)) ENGINE = MyISAM;

2nd Class clienteDAL

public class clienteDAL extends conexaoMySQL {
public void incluirCliente(clienteDTO cliente) throws Exception
{
    //Prepara a conexão com o MySQL
    abrirBD();
    sql = "INSERT INTO clientes (cli_id, cli_nome, cli_dt_inclusao, cli_endereco, cli_bairro, cli_email, cli_tel, cli_cidade, cli_uf) VALUES (null, ?, ?, ?, ?, ?, ?, ?, ?)";
    ps = con.prepareStatement(sql);
    //Busca os valores da classe clientesDTO
    ps.setLong(1, cliente.getCli_id());
    ps.setString(2, cliente.getCli_nome());
    ps.setDate(3, (java.sql.Date) cliente.getCli_dt_inclusao());
    ps.setString(4, cliente.getCli_endereco());
    ps.setString(5, cliente.getCli_bairro());
    ps.setString(6, cliente.getCli_email());
    ps.setString(7, cliente.getCli_tel());
    ps.setString(8, cliente.getCli_cidade());
    ps.setString(8, cliente.getCli_uf());
    ps.execute();
    fecharBD();
}

3rd Class clientDTO

public class clienteDTO {
    private long cli_id;
    private String cli_nome;
    private Date cli_dt_inclusao;
    private String cli_endereco;
    private String cli_bairro;
    private String cli_email;
    private String cli_tel;
    private String cli_cidade;
    private String cli_uf;

    public long getCli_id()
    {
        return cli_id;
    }    
    public void setCli_id(int cli_id)
    {
        this.cli_id = cli_id;
    } 

    public String getCli_nome()
    {
        return cli_nome;
    }    
    public void setCli_nome(String cli_nome)
    {
        this.cli_nome = cli_nome;
    }

    public Date getCli_dt_inclusao()
    {
        return this.cli_dt_inclusao;
    }    
    public void setCli_dt_inclusao(Date cli_dt_inclusao)
    {
        this.cli_dt_inclusao = cli_dt_inclusao;
    }

    public String getCli_endereco()
    {
        return cli_endereco;
    }    
    public void setCli_endereco(String cli_endereco)
    {
        this.cli_endereco = cli_endereco;
    }

    public String getCli_bairro()
    {
        return cli_bairro;
    }    
    public void setCli_bairro(String cli_bairro)
    {
        this.cli_bairro = cli_bairro;
    }

    public String getCli_email()
    {
        return cli_email;
    }
    public void setCli_email(String cli_email)
    {
        this.cli_email = cli_email;
    }    

    public String getCli_tel()
    {
        return cli_tel;
    }
    public void setCli_tel(String cli_tel)
    {
        this.cli_tel = cli_tel;
    }

    public String getCli_cidade()
    {
        return cli_cidade;
    }
    public void setCli_cidade(String cli_cidade)
    {
        this.cli_cidade = cli_cidade;
    }

    public String getCli_uf()
    {
        return cli_uf;
    }
    public void setCli_uf(String cli_uf)
    {
        this.cli_uf = cli_uf;
    }

}

4th Test Class to Include

public class TesteIncluir {
    public static void main(String[] args) throws Exception
    {
        clienteDTO cliente = new clienteDTO();
        cliente.setCli_nome("Teste");
        cliente.setCli_dt_inclusao(new java.sql.Date(System.currentTimeMillis()));
        cliente.setCli_endereco("Rua Teste, 15");
        cliente.setCli_bairro("Java Norte");
        cliente.setCli_email("[email protected]");
        cliente.setCli_tel("77777777");
        cliente.setCli_cidade("Java");
        cliente.setCli_uf("JV");
        clienteDAL dal = new clienteDAL();
        dal.incluirCliente(cliente);
        System.out.print("Cliente "+cliente.getCli_nome()+" Cadastrado com sucesso!");
    }

}

When I run the test class, in NetBeans, it returns the following error:

Exception in thread "main" com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: 'Teste' for column 'cli_dt_inclusao' at row 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4118)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)
    at br.uniplan.DAL.clienteDAL.incluirCliente(clienteDAL.java:33)
    at br.uniplan.Testes.TesteIncluir.main(TesteIncluir.java:28)
C:\Users\profe\AppData\Local\NetBeans\Cache.1\executor-snippets\run.xml:53: Java returned: 1
FALHA NA CONSTRUÇÃO (tempo total: 0 segundos)

Where am I going wrong? Hello, I've managed to solve the include, my problem now is in the change item, below is my method change in the DAL class:

public void alterarCliente(clienteDTO cliente) throws Exception
 {
     abrirBD();         
     sql = "UPDATE clientes SET cli_nome = ?, cli_dt_inclusao = ?, cli_endereco = ?, cli_bairro = ?, cli_email = ?, cli_tel = ?, cli_cidade = ?, cli_uf = ? WHERE cli_id = ?";
     ps = con.prepareStatement(sql);
     ps.setLong(1, cliente.getCli_id());
     ps.setString(2, cliente.getCli_nome());
     ps.setDate(3, new java.sql.Date(cliente.getCli_dt_inclusao().getTime()));
     ps.setString(4, cliente.getCli_endereco());
     ps.setString(5, cliente.getCli_bairro());
     ps.setString(6, cliente.getCli_email());
     ps.setString(7, cliente.getCli_tel());
     ps.setString(8, cliente.getCli_cidade());
     ps.setString(9, cliente.getCli_uf());
     ps.execute();
     fecharBD();
 }

Test class for Change, as below:

package br.uniplan.Testes;

public class TestCustomerClient {     public static void main (String [] args) throws Exception     {         clientDTO client = new clientDTO ();         client.setCli_id (2);         client.setCli_name ("Test");         client.setCli_dt_inclusao (new java.sql.Date (System.currentTimeMillis ()));         client.setCli_endereco ("Test Street, 77");         client.setCli_boirro ("South Java");         client.setCli_email ("[email protected]");         client.setCli_tel ("88888888");         client.setCli_city ("Java 8");         client.setCli_uf ("J8");         clientDAL dal = new clientDAL ();         dal.AlterarCliente (client);         System.out.println ("Client" + client.getCli_name () + "Successfully changed!");     } }

When I run it does not give error, but does not make the change in the table. Where can I be wrong?

    
asked by anonymous 11.08.2016 / 22:54

1 answer

1

You're missing the Query SQL parameters, each "query" represents a parameter to be set to Statement , as it does not have ? for the Client id so it does not have to be set to its Statement o correct would look like this:

Query

INSERT INTO clientes (
cli_nome, 
cli_dt_inclusao, 
cli_endereco, 
cli_bairro, 
cli_email, 
cli_tel, 
cli_cidade,
cli_uf
VALUES (?, ?, ?, ?, ?, ?, ?, ?);

Statement

ps.setString(1, cliente.getCli_nome());
ps.setObject(2, cliente.getCli_dt_inclusao());
ps.setString(3, cliente.getCli_endereco());
ps.setString(4, cliente.getCli_bairro());
ps.setString(5, cliente.getCli_email());
ps.setString(6, cliente.getCli_tel());
ps.setString(7, cliente.getCli_cidade());
ps.setString(8, cliente.getCli_uf());
    
12.08.2016 / 17:24