Failed to update in registry

0

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

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

My table with sequence and 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'm leaving the codes of my DAO, used in the java application.

runQuery:

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();
        }
    }
}
    
asked by anonymous 03.09.2017 / 07:30

0 answers