Problema difícil: Mysql(foreign key) e SQLException

4 de dezembro de 2010 8 respostas

programm3r 4 de dezembro de 2010

Pessoal, boa tarde,

eu tenho mais um probleminha pra vcs me ajudarem a resolver, eu estou com um problema no meu banco que é o MySQL.
No ECLIPSE, eu estou fazendo a inserção de dados em duas tabelas:

a tabela disciplina, que contém apenas as colunas: código (int not null auto_increment) -> PRIMARY KEY
e contém a coluna nome (varchar(20), not null)

E tenho mais uma tabela, a de professsores, ques contém as colunas:

codigo (int not null auto_increment) -> PRIMARY KEY

nome (varchar(20) not null)

idade (int(2))

sexo (char(1))

coddisciplina (foreign key) REFERENCES -> disciplina.codigo

OK, agora vamos supor que eu tenho isso em disciplina:
1, portugues
2, matematica

Bem, quando eu quero inserir um novo professor, eu tenho que linká-lo à tabela de disciplina, pq todo professor ensina uma matéria…enfim
Aqui é que começa o problema, no JAVA ou no banco, se eu fizer isso:
insert into professor values(‘Roberto’, 25, ‘M’, 10)
Na tabela de professor vai constar:
1
Roberto
25
M
10

E na tabela de disciplina não vai constar nada.
Isso me dá três conclusões
primeiro, não podia inserir o registro na tabela de professores, pq o CODDISCIPLINA que foi passado(10) não existe na tabela de disciplina.
Segundo, existe algum problema de relacionamento de chave estrangeira no Mysql que não trata isso. Ou trata, mas parcialmente. Perceba que como o coddisciplina passado foi incorreto, isso fez com que esse registro não fosse parar na tabela de disciplina, mas parou em professor.
E terceiro, eu queria saber não só como resolver isso, mas tbm como IDENTIFICAR ESSE PROBLEMA através do import de java.sql.SQLException.

Se eu conseguir identificar esse problema no java, eu faço com que, quando vc for inserir um professor e lincá-lo à uma matéria que não existe, isso faria com que o java trigasse um pop-up ou jogasse uma msg no console dizendo ‘MATÉRIA NÃO ENCONTRADA. O PROFESSOR NÃO SERÁ CADASTRADO’.

qualquer sugetão que possa me colocar no caminho certo, eu sou imensamente grato.
PS: skill de iniciante

8 Respostas

Eder_Peixoto 4 de dez. de 2010

Olá!

Esse tipo de verificação pode ser realizada pelo SGBD. Qual engine do MySQL está utilizando?

Eder_Peixoto 4 de dez. de 2010

Documentação do MySQL que trata sobre os tipos de engine.

Para verificação de relacionamentos entre tabelas, pode utilizar o InnoDB. Pode ser que você esteja utilizando o MyISAM, pois ele NÃO faz checagem de relacionamentos, mas ocupa menos espaço no disco.

santanna106 4 de dez. de 2010

vc está usando qual versão do MySQL?

vc pode postar os scripts de criação das tabelas?

Ev3rton 4 de dez. de 2010

Olá,

O que você quer é não poder inserir um registro na tabela professores sem que ele tenha uma chave estrangeira válida apontando para um registro na tabela disciplinas?

Se for isso, você pode criar um método para realizar essa validação. Um método que verifique se o coddisciplina informado na inserção de um professor realmente existe na tabela disciplinas, caso este não exista você poderia disparar uma exceção (uma que você mesmo tenha criado).

Não sei se entendi muito bem o seu problema, mas espero ter ajudado.

[]'s

programm3r 5 de dez. de 2010

Eder Peixoto:
Olá!
Esse tipo de verificação pode ser realizada pelo SGBD. Qual engine do MySQL está utilizando?

Olá Eder, estou usando o Mysql 5.1.41 com a extensão PHP mysqli. Ele usa a engine MyISAM para melhor performance do MySQL.

Eder Peixoto:
Documentação do MySQL que trata sobre os tipos de engine.
Para verificação de relacionamentos entre tabelas, pode utilizar o InnoDB. Pode ser que você esteja utilizando o MyISAM, pois ele NÃO faz checagem de relacionamentos, mas ocupa menos espaço no disco.

Esta é minha engine mas o amigo abaixo mencionou a checagem através de um método....Mesmo assim a sua dica..Vale uma lida, vou dar uma caçada em relação ao assunto abordado aqui. Só uma pergunta rápida, qual engine do MySQL verifica por relacionamentos externos válidos entre tabelas através das FKs por padrão?

santanna106:
vc está usando qual versão do MySQL? vc pode postar os scripts de criação das tabelas?

Olá santanna106, vou postar aqui o script exportado do MySQL. Possuo tbm as classes que manipulam esses dados das tabelas através do meu trabalho em JAVA. Se for de seu interesse, poste aqui para que eu mostre a classe(java).

//codigo tabelas:
//tabela disciplina

create table disciplina(codigo int not null auto_increment, nome varchar(20) not null,
constraint PK_DISCIPLINA PRIMARY KEY(codigo))


//tabela professor
create table professor(codigo int not null auto_increment, nome varchar(20),
idade int(2), sexo char(1), coddisciplina int not null,
constraint PK_PROFESSOR PRIMARY KEY(codigo),
constraint FK_DISCIPLINA FOREIGN KEY(coddisciplina) REFERENCES disciplina.codigo
)

Ev3rton:
Olá,
O que você quer é não poder inserir um registro na tabela professores sem que ele tenha uma chave estrangeira válida apontando para um registro na tabela disciplinas?

Exato!

Se for isso, você pode criar um método para realizar essa validação. Um método que verifique se o coddisciplina informado na inserção de um professor realmente existe na tabela disciplinas, caso este não exista você poderia disparar uma exceção (uma que você mesmo tenha criado).

Não sei se entendi muito bem o seu problema, mas espero ter ajudado.

[]'s

Ajudou e muito. Agora estou no caminho certo. Mas a dica válida que vc me sugere seria de criar um método para lidar com a relação de professores e disciplina....OK...mas eu faço isso dentro do java? ou seria melhor dentro do MySQL? Caso seja no MySQL, como fazer com que ele dispare a exceção de forma que o java entenda e me retorne o pop-up/System.out.println com o erro?

Agradeço à todos pelas dicas e sugestões. Caso eu ainda não tenha sido claro sobre algo, me retornem e eu explico de uma outra maneira.
[]'s

Ev3rton 5 de dez. de 2010

Eu faria em dentro do Java, mas acho que isso também pode ser implementado no banco utilizando trigger/stored procedure.

Dentro do java ficaria assim:

1- Você criaria um método para verificar o coddisciplina que recebesse como parametro o código a ser verificado e retornasse um boolean.
2- O método faria uma consulta para verificar se existe o código na tabela disciplinas, o comando SQl ficaria assim:
SELECT * FROM disciplinas WHERE coddisciplina = parametro_da_chamada_do_metodo.
3- Se você executasse o comando e não recebesse nenhum resultado então você retornaria false.
4- Se recebesse algum resultado retornaria true.

Dentro do seu código de inclusão você chamaria esse método, caso ele retornasse false então você cancelaria a inclusão e exibiria uma mensagem informando que o código da disciplina não é válido, caso retornasse true você continuaria com a inclusão.

[]'s

programm3r 6 de dez. de 2010

Ev3rton:
programm3r:

Ajudou e muito. Agora estou no caminho certo. Mas a dica válida que vc me sugere seria de criar um método para lidar com a relação de professores e disciplina…OK…mas eu faço isso dentro do java? ou seria melhor dentro do MySQL? Caso seja no MySQL, como fazer com que ele dispare a exceção de forma que o java entenda e me retorne o pop-up/System.out.println com o erro?

Eu faria em dentro do Java, mas acho que isso também pode ser implementado no banco utilizando trigger/stored procedure.

Dentro do java ficaria assim:

1- Você criaria um método para verificar o coddisciplina que recebesse como parametro o código a ser verificado e retornasse um boolean.
2- O método faria uma consulta para verificar se existe o código na tabela disciplinas, o comando SQl ficaria assim:
SELECT * FROM disciplinas WHERE coddisciplina = parametro_da_chamada_do_metodo.
3- Se você executasse o comando e não recebesse nenhum resultado então você retornaria false.
4- Se recebesse algum resultado retornaria true.

Dentro do seu código de inclusão você chamaria esse método, caso ele retornasse false então você cancelaria a inclusão e exibiria uma mensagem informando que o código da disciplina não é válido, caso retornasse true você continuaria com a inclusão.

[]'s

Eu tentei fazer do jeito que eu achei que funcionaria mas…não deu certo dentro do java.
Abaixo, o eboço de como ficou o método de verificar se a disciplina já existe, através do codigo de disciplina informado e os métodos de incluir professor e incluir disciplina:

//os getters e setters de:
//disciplina:
package univag;

public class Disciplina {
	private long codigo;
	private String nome;
	
	public String getNome(){
		return nome;
	}
	public void setNome(String nome){
		this.nome=nome;
	}
	public long getCodigo(){
		return codigo;
	}
	public void setCodigo(long codigo){
		this.codigo=codigo;
	}
}


//professor:
package univag;
import univag.Disciplina;

public class Professor {
	private String nome;
	private long codigo;
	private int idade;
	private char sexo;
	private Disciplina disciplina;
	private int coddisciplina;
	
	public String getNome(){
		return nome;
	}
	public void setNome(String nome){
		this.nome=nome;
	}
	public long getCodigo(){
		return codigo;
	}
	public void setCodigo(long codigo){
		this.codigo=codigo;
	}
	public int getIdade(){
		return idade;
	}
	public void setIdade(int idade){
		this.idade=idade;
	}
	public char getSexo(){
		return sexo;
	}
	public void setSexo(char sexo){
		this.sexo=sexo;
	}
	
	public Disciplina getDisciplina(){
		return disciplina;
	}
	public void setDisciplina(Disciplina disciplina){
		this.disciplina=disciplina;
	}
		
}

//classe ProfessorBD:
import java.sql.*;

import org.apache.log4j.Logger;
import java.io.*;
import java.util.Scanner;
import java.util.ArrayList;
import java.util.List;

public class ProfessorBD {

	private Connection conexao = null;
	private Logger log = Logger.getLogger(ProfessorBD.class.getName());
	
	public ProfessorBD(){
		try {
			conexao = Conexao.conectar(ConstantesJDBC.MYSQL_DRIVER,
					ConstantesJDBC.MYSQL_URL, ConstantesJDBC.MYSQL_USER,
					ConstantesJDBC.MYSQL_PASS);

			conexao.setAutoCommit(false);
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	
	public boolean incluirProfessor(Professor professor) throws SQLException{
		Statement query = null;
		query = conexao.createStatement();
		Disciplina disciplina = professor.getDisciplina();
		
		String sql = "INSERT INTO professor(NOME, IDADE, SEXO,CODDISCIPLINA)"+ "VALUES('"+professor.getNome()+"',"+professor.getIdade()+",'"+ professor.getSexo()+"',"+disciplina.getCodigo()+")";
		log.info("SQL=" + sql);

		query.executeUpdate(sql);

		conexao.commit();
		
		return true;
	}
}

//classe DisciplinaBD:
import java.sql.Connection;
import java.sql.*;

import org.apache.log4j.Logger;

import java.io.*;
import java.util.Scanner;

public class DisciplinaBD {

	private Connection conexao = null;
	private Logger log = Logger.getLogger(DisciplinaBD.class.getName());
	
	public DisciplinaBD(){
		try {
			conexao = Conexao.conectar(ConstantesJDBC.MYSQL_DRIVER,
					ConstantesJDBC.MYSQL_URL, ConstantesJDBC.MYSQL_USER,
					ConstantesJDBC.MYSQL_PASS);

			conexao.setAutoCommit(false);
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	
	public boolean incluirDisciplina(Disciplina disciplina) throws SQLException{
		Statement query = null;
		query = conexao.createStatement();
		
		String sql = " INSERT INTO disciplina (NOME) "+ "VALUES ('"+ disciplina.getNome()+"' )";
		log.info("SQL=" + sql);

		query.executeUpdate(sql);

		conexao.commit();
		
		return true;
	}
}


//classe principal:
import univag.Conexao;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import java.sql.ResultSet;


public class TelaConsole {

	private Scanner teclado = new Scanner(System.in);

	public TelaConsole(){

		boolean isContinua = true;

		while (isContinua) {

			imprimirMenu();

			int op = teclado.nextInt();
			System.out.println("op = " + op);

			switch (op) {

			case 0:
				System.out.println("Escolheu 0 Sair....");
				isContinua = false;
				break;

			case 1:
				System.out.println("Escolheu 1");
				incluirDisciplina();
				break;
				
			case 2:
				System.out.println("Opção: Inserir Professor");
				incluirProfessor();

			case 3:
				imprimirMenu3();
				
				int op3 = teclado.nextInt();
				System.out.println("Digite o nome da disciplina?"+op3);
				
				break;

			default:
				System.out.println("opcao invalida!");
				break;
			}
		}

	}

	public void incluirDisciplina() {

		DisciplinaBD disciplinaBD = new DisciplinaBD();
		Disciplina disciplina = new Disciplina();

		System.out.println("Digite o nome da disciplina?");
		String nomeDisciplina = teclado.next();

		disciplina.setNome(nomeDisciplina);

		try {
			boolean resultado = disciplinaBD.incluirDisciplina(disciplina);

			if (resultado == true) {
				System.out.println("Disciplina incluida OK!");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void incluirProfessor(){
		ProfessorBD professorBD = new ProfessorBD();
		Disciplina disciplina = new Disciplina();
		Professor professor = new Professor();
		
		System.out.println("Digite o nome do professor");
		String nome = teclado.next();
		professor.setNome(nome);
		
		System.out.println("Digite a idade");
		int idade=Integer.parseInt(teclado.next());
		professor.setIdade(idade);
		
		System.out.println("Digite o sexo M/F");
		char sexo = teclado.next().charAt(0);
		professor.setSexo(sexo);
		
		System.out.println("Digite o codigo da disciplina");
		int codigoDisciplina = teclado.nextInt();
		disciplina.setCodigo(codigoDisciplina);
		
		try{
			boolean check = professorBD.verificaDisciplina(professor); 
			//se o codigo existe, insere o professor na disciplina existente
			if (check == true){
			professor.setDisciplina(disciplina);
			}else 
				System.out.print("Codigo disciplina nao encontrado!");
			
			}
			catch (SQLException a){
			a.printStackTrace();
		}
		
		try {
			boolean resultado = professorBD.incluirProfessor(professor);

			if (resultado == true) {
				System.out.println("Professor incluído com sucesso!");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	public void imprimirMenu() {

		System.out.println("\n ###  Menu ###");
		System.out.println("0 - Sair");
		System.out.println("1 - Incluir Disciplina");
		System.out.println("2 - Incluir Professor");
		System.out.println("3 - Alterar Professor");
		System.out.println("4 - Excluir Professor");
		System.out.println("5 - Buscar Professor");
		System.out.println("6 - Listar Professor");
		
		System.out.println("Digite Sua Opção");
	}

	public void imprimirMenu3(){
		System.out.println(" ### Sub-Menu ###");
		System.out.println("1 - Pelo Codigo");
		System.out.println("2 - Listar Todos");
		System.out.println("Digite Sua Opção");
	}
	
	
	
	public static void main(String[] args){
		new TelaConsole();
	}
}

/*ao executar a classe principal
com o menu 2 - inserir professor
e entrar com os dados, independente
de o codigo inserido for existente
ou não na tabela disciplina, o console
dá output de erro:
Exception in thread "main" java.lang.NullPointerException
Referente à: */
String sql = " SELECT * FROM disciplina WHERE CODIGO= "+disciplina.getCodigo(); //de verificaDisciplina(Professor professor);
boolean check = professorBD.verificaDisciplina(professor);  //de incluirProfessor() dentro do main[];
incluirProfessor(); //dentro do case2 (main[]);
new TelaConsole(); //dentro da chamada do main[], últimas linhas.

Provavelmente o erro está dentro de verificaDisciplina.
Pode me ajudar a entender pq q independente do código digitado existir ou não dentro da tabela disciplina, ele ainda não insere o professor?
Obrigado pelas respostas até agora,

no aguardo.

Ev3rton 6 de dez. de 2010

Olá,

Eu não entendi muito bem o seu código e não encontrei o verificaDisciplina, então vou te mostrar como eu implementaria:

Classe para criar a conexão:

public class ConnectionFactory {
    private static final String URL = "jdbc:mysql://localhost/escola";
    private static final String USER = "root";
    private statc final String PASSWORD = "";
    public Connection getConnection() {
        try {
            return DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
             e.printStackTrace();
        }
    }
}

Classe para gravar objeto professor no banco:

public class DAOProfessor {
    private Connection con;
    public DAOProfessor() {
        con = new ConnectionFactory().getConnection();
    }
    public void incluir(Professor p) {
        if(verificarDisciplina(p.getCodDisciplina()) {
            String sql = "INSERT INTO professores (nome, cpf, coddisciplina) VALUES (?, ?, ?)";
            try {
                //Dê preferencia para o PreparedStatement, assim você evita aquelas concatenações de Strings no meio do comando SQL
                PreparedStatement stmt = con.prepareStatement(sql);
                //Cada ? no na string sql é uma lacuna que vou preencher com os valores que preciso
                stmt.setString(1, p.getNome());
                stmt.setString(2, p.getCpf());
                stmt.setInt(3, p.getCodDisciplina());
                stmt.execute();
                stmt.close();
             } catch(SQLException e) {
                e.printStackTrace();
             }
        }
    }
}

A classe Disciplina com o método para fazer a verificação:

public class DAODisciplina {
    private Connection con;
    public DAODisciplina() {
        con = new ConnectionFactory().getConnection();
    }
    public boolean verificarDisciplina(int codDisciplina) {
        //Um comando SQL que usa uma função para contar o número de linhas do resultado
        String sql = "SELECT COUNT(*) AS ext FROM disciplinas WHERE coddisciplina=?";
        try {
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setInt(1, codDisciplina);
            ResultSet rs = stmt.executeQuery();
            rs.first();
            if(rs.getInt("ext") == 1) {
                //Retorna true para dizer que há uma disciplina com aquele código, caso contrário retorna false
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return false;
    }
}

Acho que seria isso para fazer a verificação.

[]'s

Criado 4 de dezembro de 2010

Ultima resposta 6 de dez. de 2010

Respostas 8

Participantes 4

8 Respostas

Topicos relacionados