Insert problem: String sql is correct, but not inserted by Java, only manually by Mysql! Procedures datetime attributes

-2

Good evening everyone! I'm using java and Mysql. At the moment I'm using Procedure for insertion everything happens in a perfect way the String goes to the bank with all the correct parameters, but something happens that it hits the bank, burns the id and does not insert the values in the bank, I realized this after a few attempts inserting through the method by java, then I made the debug I got String that is sent to the bench I tested it manually in the workbench and even entered normally where I realized that Mysql jumped some Ids.

Thanks in advance for your help! :) DAO Method

  public boolean cadastrarTurma(TurmmaBean turma,LoginBean login){
           String sql ="{call inserir_turma(?,?,?,?,?,?,?,?,?)}";

       try {

            CallableStatement ps1 = null; 
                ps1 = con.prepareCall(sql);
                ps1.setString(1,login.getNome_user());
                ps1.setString(2,login.getSenha());
                ps1.setString(3, turma.getTur_nome());
                ps1.setString(4, turma.getTur_diciplina());
                ps1.setDate(5, new java.sql.Date(turma.getTur_ini_dia().getTime()));
                ps1.setDate(6, new java.sql.Date( turma.getTur_fim_dia().getTime()));
                ps1.setTime(7,new java.sql.Time (turma.getTur_hora_fim().getTime()));
                ps1.setTime(8,new java.sql.Time(turma.getTur_hora_fim().getTime()));
                ps1.setString(9, turma.getTur_dia_semana());
                // Print abaixo para ver o q estava sendo enviado ao banco no momento do teste unitario
                System.out.println(ps1);
                 // Formato da String do ps1 que foi enviada ao banco.
                //call inserir_turma('luis','12345','mateatica quarta','geometria','2018-10-10','2018-10-10','12:00:00','01:00:00','segunda-feira',4);

                ps1.executeUpdate();
                ps1.close();           


                con.close();
                 return true;

            } catch (SQLException e) {
             e.printStackTrace();
                System.out.println("Falha ao inserir os dados !");
                //throw new RuntimeException("Erro 1_D   " + e);            
            }
          return false;


    }

This is my unit test done with Junit where the same says it passed not from the error, but I check in the bank and no value is inserted.

 @Test
    public void testinserirTurma() throws ParseException {
        TurmmaBean tb =  new TurmmaBean();
            LoginBean lgb = new LoginBean();
            Turma tur = new Turma();
// inicio conversão dos campos de datas  e dos campos de hora nos formatos para o banco
            String   tur_ini_dia = "12/12/1989";
            SimpleDateFormat frm1 = new  SimpleDateFormat("yyyy/MM/dd");
               Date c_tur_ini_dia  =  new  SimpleDateFormat("dd/MM/yyyy").parse(tur_ini_dia);
               String dtcnv1 = frm1.format(c_tur_ini_dia);
               c_tur_ini_dia =  frm1.parse(dtcnv1);        


                String tur_fim_dia = "12/12/1989";
                SimpleDateFormat frm2 = new  SimpleDateFormat("yyyy/MM/dd");
        Date c_tur_fim_dia =   new SimpleDateFormat("dd/MM/yyyy").parse(tur_fim_dia);
                String dtcnv2 = frm2.format(c_tur_fim_dia);
                c_tur_fim_dia =  frm2.parse(dtcnv2);


                String tur_hora_inicio ="12:12";
                java.util.Date c_tur_hora_inicio =  new SimpleDateFormat("HH:mm").parse(tur_hora_inicio);
        Time hrCon_ini = new Time(c_tur_hora_inicio.getTime()); 

                String tur_hora_fim ="12:12";
        java.util.Date c_tur_hora_fim =  new SimpleDateFormat("HH:mm").parse(tur_hora_fim);
        Time  hrCon_fim = new Time(c_tur_hora_fim.getTime());

                // fim conversão dos campos de datas  e dos campos de hora nos formatos para o banco

//enviado valores convertidos para o DAO
                lgb.setNome_user("luis");
                lgb.setSenha("12345");
                tb.setTur_nome("TESTE2");
                tb.setTur_diciplina("TESTE2");
                tb.setTur_ini_dia (c_tur_ini_dia);
                tb.setTur_fim_dia(c_tur_fim_dia);
                tb.setTur_hora_inicio(hrCon_ini);
                tb.setTur_hora_fim (hrCon_fim);
                tb.setTur_dia_semana("Segunda");

               if( tur.cadastrarTurma(tb ,lgb)){
                   System.out.println("Salvo com sucesso!!!");
               }else{
                   fail("erro ao inserir!!!");

               }

My procedure takes two additional login and password parameters where it does a select and returns an id that becomes a fk in the other table.

##procedure inserir turma

 delimiter $$
create procedure inserir_turma(
in p_log_nome varchar(15), 
in p_log_senha varchar(15),
in p_tur_nome varchar(50) ,
in P_tur_diciplina varchar(50) ,
in P_tur_inicio date ,
in P_tur_fim date ,
in p_tur_hora_inicio time  ,
in p_tur_hora_fim time ,
in P_tur_dia_semana  varchar(20)
)
begin
DECLARE msg VARCHAR(1000) DEFAULT "sem mensagem";
DECLARE excecao SMALLINT DEFAULT 0;
declare p_tur_fk_perfil_pro1 int(9);
START TRANSACTION;
select  log_login_id into p_tur_fk_perfil_pro1  from login where log_nome= p_log_nome and log_senha  =p_log_senha; 
insert into turma(
tur_nome, 
tur_diciplina, 
tur_inicio, 
tur_fim, 
tur_hora_inicio, 
tur_hora_fim, 
tur_dia_semana,
tur_fk_perfil_pro  
) 
values(
p_tur_nome, 
P_tur_diciplina, 
P_tur_inicio, 
P_tur_fim, 
p_tur_hora_inicio, 
p_tur_hora_fim,
P_tur_dia_semana,
p_tur_fk_perfil_pro1  
);
end$$
    
asked by anonymous 30.11.2018 / 03:52

1 answer

-1

The java code was correct, the error was in the proceure was missing the COMMIT at the end of it !!!!

 delimiter $$
create  procedure inserir_turma(
in p_log_nome varchar(15), 
in p_log_senha varchar(15),
in p_tur_nome varchar(50) ,
in P_tur_diciplina varchar(50) ,
in P_tur_inicio date ,
in P_tur_fim date ,
in p_tur_hora_inicio time  ,
in p_tur_hora_fim time ,
in P_tur_dia_semana  varchar(20)
)
begin
DECLARE msg VARCHAR(1000) DEFAULT "sem mensagem";
DECLARE excecao SMALLINT DEFAULT 0;
declare p_tur_fk_perfil_pro1 int(9);
START TRANSACTION;
select  log_login_id into p_tur_fk_perfil_pro1  from login where log_nome= p_log_nome and log_senha  =p_log_senha; 
insert into turma(
tur_nome, 
tur_diciplina, 
tur_inicio, 
tur_fim, 
tur_hora_inicio, 
tur_hora_fim, 
tur_dia_semana,
tur_fk_perfil_pro  
) 
values(
p_tur_nome, 
P_tur_diciplina, 
P_tur_inicio, 
P_tur_fim, 
p_tur_hora_inicio, 
p_tur_hora_fim,
P_tur_dia_semana,
p_tur_fk_perfil_pro1 
);

IF excecao = 1 THEN

      ROLLBACK;

   ELSE

      COMMIT; ## <-- resolvido 
   END IF;
end$$
    
03.12.2018 / 17:23