Failed to update to registry

Asked

Viewed 104 times

0

I am having the following problem, when I try to change a registry made by my application, it creates a new record instead of changing.

My question is, if when I do the trigger of insertion of the sequence, i should switch to be triggered for the UPDATES as well. If yes, how could I do ?

My table with the sequence and the trigger:

CREATE TABLE TURMAALUNO
(
  IdTurmaAluno INTEGER NOT NULL,
  IdTurma INTEGER NOT NULL,
  IdAluno INTEGER NOT NULL,  
  CONSTRAINT TURMAALUNO PRIMARY KEY (IdTurmaAluno),
  FOREIGN KEY (IdTurma)
  REFERENCES TURMA (IdTurma),
  FOREIGN KEY (IdAluno)
  REFERENCES ALUNO (IdAluno)
);
/
CREATE SEQUENCE SEQ_ID_TURMAALUNO
MINVALUE 1
MAXVALUE 9999999999
START WITH 1
INCREMENT BY 1
NOCACHE
CYCLE;
/
CREATE OR REPLACE TRIGGER TRG_ID_TURMAALUNO BEFORE INSERT ON TURMAALUNO FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF :NEW.IDTURMAALUNO IS NULL THEN SELECT SEQ_ID_TURMAALUNO.NEXTVAL INTO :NEW.IDTURMAALUNO FROM DUAL; END IF; END COLUMN_SEQUENCES; END;
/

Edit: If necessary, I am leaving the codes of my DAO, used in the java application.

executaQuery:

public static List<String[]> executaQuery(String sql) 
    {
        try 
        {
            List<String[]> dados = new ArrayList();
            Statement st = ConexaoOracle.getConexao().createStatement();
            ResultSet rs = st.executeQuery(sql);
            //PreparedStatement ps = ConexaoOracle.getConexao().prepareStatement(sql);
            //ResultSet rs = ps.executeQuery(); 
            int numeroColunas = rs.getMetaData().getColumnCount();
            while(rs.next()) 
            {
                String[] linha = new String[numeroColunas];
                for(int i = 1; i <= numeroColunas; i++)
                {
                    linha[i-1] = rs.getString(i);
                }
                dados.add(linha);
            }
            return dados;
        } 

        catch (Exception e) 
        {
            JOptionPane.showMessageDialog(null,  "Não foi possível consultar o banco de dados");
            e.printStackTrace();
            return new ArrayList();
        }
    }

DAO class:

package dao;

import bd.ConexaoOracle;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import pojo.PojoTurmaAluno;
import mensagens.TelaErroException;

public class DaoTurmaAluno {

    private final PojoTurmaAluno pojoTurmaAluno;
    private final String SQL_INCLUIR = "INSERT INTO TURMAALUNO VALUES (?, ?, ?)";
    private final String SQL_ALTERAR = "UPDATE TURMAALUNO SET IDTURMA = ?, IDALUNO = ? WHERE IDTURMAALUNO = ?";
    private final String SQL_EXCLUIR = "DELETE FROM TURMAALUNO WHERE IDTURMAALUNO = ?";
    private final String SQL_CONSULTAR = "SELECT * FROM TURMAALUNO WHERE IDTURMAALUNO = ?";
    public static final String SQL_PESQUISA = "SELECT IDTURMAALUNO, TURMA.DESCRICAOTURMA, ALUNO.NOMEALUNO FROM TURMAALUNO INNER JOIN TURMA ON TURMA.IDTURMA = TURMAALUNO.IDTURMA INNER JOIN ALUNO ON ALUNO.IDALUNO = TURMAALUNO.IDALUNO ORDER BY IDTURMAALUNO";
    private static final String SEQUENCE = "SEQ_ID_TURMAALUNO";

    private TelaErroException telaErro = new TelaErroException();

    public DaoTurmaAluno(PojoTurmaAluno pojoTurmaAluno) {
        this.pojoTurmaAluno = pojoTurmaAluno;
    }

    public boolean incluir() {
        try {
            PreparedStatement ps = ConexaoOracle.getConexao().prepareStatement(SQL_INCLUIR);
            pojoTurmaAluno.setIdTurmaAluno(ConexaoOracle.pegaSequences(SEQUENCE));
            ps.setInt(1, pojoTurmaAluno.getIdTurmaAluno());
            ps.setInt(2, pojoTurmaAluno.getPojoTurma().getIdTurma());
            ps.setInt(3, pojoTurmaAluno.getPojoAluno().getIdAluno());
            ps.executeUpdate();//Atualiza no BD.
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            telaErro.pegaVariavel("inclusão", "incluir", "turma aluno!", e);
            telaErro.setVisible(true);
            return false;
        }
    }

    public boolean alterar() {
        try {
            PreparedStatement ps = ConexaoOracle.getConexao().prepareStatement(SQL_ALTERAR);
            ps.setInt(1, pojoTurmaAluno.getPojoTurma().getIdTurma());
            ps.setInt(2, pojoTurmaAluno.getPojoAluno().getIdAluno());
            ps.setInt(3, pojoTurmaAluno.getIdTurmaAluno());
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            telaErro.pegaVariavel("alteração", "alterar", "turma aluno!", e);
            telaErro.setVisible(true);
            return false;
        }
    }

    public boolean excluir() {
        try {
            PreparedStatement ps = ConexaoOracle.getConexao().prepareStatement(SQL_EXCLUIR);
            ps.setInt(1, pojoTurmaAluno.getIdTurmaAluno());
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            telaErro.pegaVariavel("exclusão", "excluir", "turma aluno!", e);
            telaErro.setVisible(true);
            return false;
        }
    }

    public boolean consultar() {
        try {
            PreparedStatement ps = ConexaoOracle.getConexao().prepareStatement(SQL_CONSULTAR);
            ps.setInt(1, pojoTurmaAluno.getIdTurmaAluno());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                pojoTurmaAluno.setIdTurmaAluno(rs.getInt("IDTURMAALUNO"));
                pojoTurmaAluno.getPojoTurma().setIdTurma(rs.getInt("IDTURMA"));
                pojoTurmaAluno.getPojoAluno().setIdAluno(rs.getInt("IDALUNO"));
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            telaErro.pegaVariavel("consulta", "consultar", "turma aluno!", e);
            telaErro.setVisible(true);
            return false;
        }
    }
}

Connection class:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

public class ConexaoOracle {

    public static Connection conexao;

    public static Connection getConexao() {
        try {
            if (conexao == null) {

                Class.forName("oracle.jdbc.driver.OracleDriver"); //@127.0.0.1 ou @localhost          
                conexao = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "Aluno", "Aluno01");
            }
            return conexao;
        } catch (ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null, "Não foi possível encontrar o driver de acesso ao banco de dados!");
            e.printStackTrace();
            return null;
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Não foi possível conectar com  o banco de dados!");
            e.printStackTrace();
            return null;
        }
    }

    public static int pegaSequences(String sequence) {
        try {
            String SQL = "SELECT " + sequence + ".NEXTVAL FROM DUAL";
            PreparedStatement ps = ConexaoOracle.getConexao().prepareStatement(SQL);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            } else {
                return -1;
            }
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "Não foi possível obter " + sequence + "!");
            return -1;
        }
    }

    public static List<Object[]> consultarComboBox(String sql) {
        try {
            List<Object[]> retorno = new ArrayList();
            PreparedStatement ps = ConexaoOracle.getConexao().prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Object[] linha = new Object[2];
                linha[0] = rs.getInt(1);
                linha[1] = rs.getString(2);
                retorno.add(linha);
            }
            return retorno;
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "Não foi possível consultar dados para o ComboBox");
            return null;
        }
    }

    public static List<String[]> executaQuery(String sql) {
        try {
            List<String[]> dados = new ArrayList();
            Statement st = ConexaoOracle.getConexao().createStatement();
            ResultSet rs = st.executeQuery(sql);
            //PreparedStatement ps = ConexaoOracle.getConexao().prepareStatement(sql);
            //ResultSet rs = ps.executeQuery(); 
            int numeroColunas = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                String[] linha = new String[numeroColunas];
                for (int i = 1; i <= numeroColunas; i++) {
                    linha[i - 1] = rs.getString(i);
                }
                dados.add(linha);
            }
            return dados;
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Não foi possível consultar o banco de dados");
            e.printStackTrace();
            return new ArrayList();
        }
    }
}
  • if the problem is being "I try to change a registry made by my application, it creates a new registry instead of changing" put the code that is trying to make the change, not Trigger pro autoincrement. And no, autoincrement Rigger doesn’t have to be in the update

  • apparently, it is ok. puts the example of a record that should be changed and another, very strange, was inserted

  • @Rovannlinhalis for example, it kind of "duplicates" the record, would it be interesting to show what is done to query ? Since to change I consult first ?

  • yes, put all the information that can help

  • @Rovannlinhalis put what I found necessary !

  • What is pojoTurmaAluno? In addition, return true for success and false for error is a bad practice of programming, as the exceptions were invented exactly so that it did not need to be done. And read this question and your answers also.

  • @Victorstafusa pojoTurmaAluno is my pojo class, which carries the data, but there does not arrow the problem, she just owns get and Setter. About that question there, I will read carefully, already liked the part of not need to be giving close(); very boom!

  • It is that you are always setting in the same pojo instance. I would recommend you to use a different instance each time.

  • @Victorstafusa as so ?

  • You seem to always be calling the setters in the same instance in the method consultar(). Thus, it is natural that it will overwrite values. However, just looking at what you posted, you can’t be sure. I suggest I put in the entire DAO code.

  • @Victorstafusa I put !

  • How and where you instate this DAO?

  • @Victorstafusa normal, Daoturmaaluno daoTurma = new Daoturmaaluno(he gets a pojo); and dps calls the methods I need, but that’s not a problem, I’m sure, I was thinking here, if Derrepende is not my run query or update, could you look ? Is that I tried to adapt an example of Firebird, maybe there is something missing

  • I insist on showing the class that creates the DaoTurmaAluno. I think it’s also possible that there’s something wrong with the class ConexaoOracle. It would be good for you to post these classes to make sure everything is all right and we understand better where the problem is.

  • @Victorstafusa are there, connection and DAO

  • 1

    I even tried to formulate a response with this code that you gave, but I came to the conclusion that without seeing what code creates and uses the DAO, it would probably be an incomplete and insufficient response. There’s a lot of things wrong with your code, and you can’t fix it without touching a layer higher up. However, the main problem does not seem to be clearly reproducible only in the code you posted.

Show 11 more comments
No answers

Browser other questions tagged

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