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
– Rovann Linhalis
apparently, it is ok. puts the example of a record that should be changed and another, very strange, was inserted
– Rovann Linhalis
@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 ?
– AlunoOracle
yes, put all the information that can help
– Rovann Linhalis
@Rovannlinhalis put what I found necessary !
– AlunoOracle
What is
pojoTurmaAluno
? In addition, returntrue
for success andfalse
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.– Victor Stafusa
@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!
– AlunoOracle
It is that you are always setting in the same pojo instance. I would recommend you to use a different instance each time.
– Victor Stafusa
@Victorstafusa as so ?
– AlunoOracle
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.– Victor Stafusa
@Victorstafusa I put !
– AlunoOracle
How and where you instate this DAO?
– Victor Stafusa
@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
– AlunoOracle
I insist on showing the class that creates the
DaoTurmaAluno
. I think it’s also possible that there’s something wrong with the classConexaoOracle
. 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.– Victor Stafusa
@Victorstafusa are there, connection and DAO
– AlunoOracle
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.
– Victor Stafusa