Ajuda na query SQL

Infraestrutura Banco de Dados
, , , ,
#1

Olá, estou com um pequeno problema.

Criei uma tabela horario no banco de dados (MYSQL) que contem horaInicio (‘07:00’ até ‘20:30’) e horaFinal (‘07:30’ até ‘21:00’).

Fora isso tenho outra tabela conforme abaixo:

CREATE TABLE agenda_reuniao(
id bigint auto_increment,
usuario varchar(255),
assunto varchar(255),
dataMesAno DATE,
horaInicio TIME,
horaFinal TIME,
sala varchar(255),
primary key(id)
);

Estou utilizando a seguinte QUERY que me retorna os horários disponíveis e eu mostro isso na tela no JAVA.

SELECT horarioInicio, horarioFinal FROM horario WHERE NOT EXISTS(
SELECT horaInicio, horaFinal FROM agenda_reuniao WHERE agenda_reuniao.sala = ‘Sala Serra’ AND
agenda_reuniao.dataMesAno = ‘2017-11-14’ AND horario.horarioInicio BETWEEN horaInicio AND
horaFinal AND horario.horarioFinal BETWEEN horaInicio AND horaFinal );

O problema que estou enfrentando é o seguinte, eu vou e crio uma nova reunião com as informações abaixo (tudo através do java):

INSERT INTO agenda_reuniao VALUES (null,‘vamonfardini’, ‘teste’, ‘2017-11-14’, ‘10:00’, ‘17:00’, ‘Sala Serra’ );

Até ai está tudo certo, então eu vou tentar criar uma outra reunião com as informações (tudo através do java):

INSERT INTO agenda_reuniao VALUES (null,‘vamonfardini’, ‘teste’, ‘2017-11-14’, ‘08:00’, ‘20:00’, ‘Sala Serra’ );

E ai que está meu problema, as duas reuniões estão gravadas para o mesmo dia, conflitando o horário sendo que uma é das 10:00 as 17:00 e a outra das 08:00 as 20:00.

Gostaria de alguma forma (no qual não consegui pensar) de coibir isso de acontecer através da QUERY que estou utilizando para me retornar os horários disponíveis.

Agradeço desde já e me desculpe se fui confuso.

#2

não sei, talvez uma alternativa sua pensando pelo lado lógico, você esta inserindo na mesma “Sala Serra” duas reuniões ! esta certo ? uma reunião em cada sala mesmo que na mesma data , horario incio/fim, faria mais sentido , acredito eu , não que não possa ! talvez o que você poderia fazer também é filtrar pelo assunto, essa
subquery sua ai pelo visto esta filtrando todas reuniões que não estão faixa de hora inicio/final que você passou na query principal ? esta certo ?

#3

É que eu coloquei isso como um exemplo…

Eu estou fazendo um sistema de agendamento de reuniões e essa QUERY foi feita para buscar no banco de dados os horários que estão disponíveis para o agendamento de uma nova reunião, eu pego o retorno e coloco na tela através do java para o usuário escolher o melhor horário.

Os usuários vão tentar agendar varias reuniões na mesma sala conforme o exemplo da Sala Serra.

Eu gostaria de fazer alguma coisa do tipo:

Agendei uma reunião das 15:00 as 17:00 e qualquer outra reunião que for agendada após isso seja algum horário das 07:00 até 15:00 ou algum horário das 17:00 até as 21:00.

E não como hoje está sendo feito que agendo uma das 15:00 as 17:00 e outra das 13:00 as 19:00 .

Não sei se consegui ser claro.

#4

agora entendi sua lógica…vamos pensar, pera ai !

#5

Só para tentar ser claro, essa QUERY foi feita apenas para me dar o retorno de quais horários estão disponíveis para um novo agendamento…

entendeu?

#6

Estou pensando nesse problema a 2 meses e não consegui chegar a uma conclusão, por isso vim pedir ajuda… =/

#7

esse ponto que fiquei meio confuso, você quer não permitir que nenhuma outra reunião seja marcada neste horario das 15:00 as 17:00 se já estiver uma agendada neste intervalo de horário, seria isso ?

#8

desculpe, na verdade nao é não permitir, é filtrar as reuniões, desconsidera a pergunta acima !

#9

Após o seu primeiro insert, sua query NAO vai retornar os blocos de horário depois das 10 e antes das 17. Você precisa comparar se todos os horários da próxima reuniao (segundo insert) estao livres.

#10

@AbelBueno esta certo ! fez o primeiro insert, no segundo vai ter que tratar antes de inserir, fazendo
o select trazendo o resultado e comparando, se já existir algo no intervalo fornecido você faz o que é necessário. no segundo insert, antes vai ter que rolar um select ai tipo ->

SELECT horaInicio, horaFinal FROM agenda etc..etc.. , 
se ele vier vazio, vai ter que tratar no seu  resultSet antes de permitir outro insert,  tipo:
if (resulset.next) {
  veio alguma coisa .. entendeu ??!!!
}
#11

Não entendi muito bem, fiquei um pouco confuso …

Eu teria que tratar esse problema até para o primeiro insert já que tudo é feito pela aplicação

Veja se eu entendi um pouco, faço um select que me retorna o intervalo entre dois horários, caso tenha informação, eu trato ela …

E devo fazer esse tratamento na parte de salvar as informações no banco e não na parte da query que informei pois ela só me retorna os horários disponíveis.

Seria isso?

#12

Sim, você precisa tratar isso na aplicaçao. Antes de inserir cada reuniao, você deve verificar se aquele bloco de horário está ocupado.

Pelo que entendi do seu esquema, acho que ficaria mais simples você transformar seu select assim:

SELECT 
	horario.horaInicio, horario.horaFinal,
    agenda_reuniao.id, agenda_reuniao.usuario, 			  
    agenda_reuniao.assunto
FROM 
	horario 
    LEFT JOIN agenda_reuniao
  	ON	agenda_reuniao.sala = 'Sala Serra' 
  	AND agenda_reuniao.dataMesAno = '2017-11-14'
  	AND horario.horaInicio BETWEEN agenda_reuniao.horaInicio AND agenda_reuniao.horaFinal 		AND horario.horaFinal BETWEEN agenda_reuniao.horaInicio AND agenda_reuniao.horaFinal
ORDER BY 1,2

Isso sempre te trará todos os horários. Se a sala estiver ocupada naquele horário, os campos terao valor, senao os campos vao estar nulos.
Daí fica mais fácil na sua aplicaçao verificar se a nova tentativa de reuniao tem algum horário em conflito com alguma reuniao existente.

#13

Entendeu ? se não , da um toque !

#14

@Monfardini, a verificação do conflito de horário precisa ser feita em SQL? Não pode ser feita em Java antes da informação ser inserida no banco de dados?

#15

exatamente, depois de agendar a primeira reunião, pra agendar a segunda ele vai precisar consultar antes
no banco se já tem existe alguma agendada , pra isso ele pode ser o retorno do ResulSet se ele veio vazio ou não, se veio vazio é porque não tem nada agendado, algo tipo assim ->

INSERT INTO agenda_reuniao VALUES (null,‘vamonfardini’, ‘teste’, ‘2017-11-14’, ‘10:00’, ‘17:00’, ‘Sala Serra’ );

inseriu a primeira reunião, antes de inserir a segunda, vai ter que fazer ->

");

apos ter feito este select, ele vai te retornar um ResultSet vazio ou não, se vier vazio permite agendar reunão no horário, caso contrario não, algo assim ->

 Statement stmt = con.createStatement();
 ResultSet rs = stmt.executeQuery("

SELECT
horario.horaInicio, horario.horaFinal,
agenda_reuniao.id, agenda_reuniao.usuario,
agenda_reuniao.assunto
FROM
horario
LEFT JOIN agenda_reuniao
ON agenda_reuniao.sala = 'Sala Serra’
AND agenda_reuniao.dataMesAno = '2017-11-14’
AND horario.horaInicio BETWEEN agenda_reuniao.horaInicio AND agenda_reuniao.horaFinal AND horario.horaFinal BETWEEN agenda_reuniao.horaInicio AND agenda_reuniao.horaFinal
ORDER BY 1,2

");

if (rs.next() == false) {
  System.out.println("ResultSet veio vazio, permite agendar reunião no horario desejado ");
}

algo assim, entendeu !

#16

Acabei fazendo uma alteração de Left Join para Full Join, porque achei mais fácil para tratar no java, conforme abaixo:

SELECT
horarioInicio,
horarioInicio,
agenda_reuniao.usuario,
agenda_reuniao.assunto
FROM
horario
FULL JOIN agenda_reuniao
ON agenda_reuniao.sala = ‘Sala Serra’
AND agenda_reuniao.dataMesAno = ‘2017-11-14’
AND horarioInicio BETWEEN agenda_reuniao.horaInicio AND agenda_reuniao.horaFinal
AND horarioFinal BETWEEN agenda_reuniao.horaInicio AND agenda_reuniao.horaFinal

Sendo assim ele me retorna apenas o que está agendado, conforme imagem:
banco

E após isso tratei pelo java assim:

String[] list = new String[29];

  	while (resultSet.next()) {
  		for (int i = 0; i < 29; i++) {
  			list[i] = resultSet.getString("usuario");
  			if (list[i] != null) {
  				JOptionPane.showMessageDialog(this,
  						"O intervalo de horário que foi escolhido já possui uma reunião agendada",
  						"Salvar Reunião", JOptionPane.ERROR_MESSAGE);
  				return false;
  			}
  		}

  	}

Único problema foi que tive que setar na mão o tamanho do array:

String[] list = new String[29];

E aqui

for (int i = 0; i < 29; i++)

#17

sim…também deu certo ! não teve jeito, teve que rolar ai um condicional pra comparar o retorno.
eu só não entendi o que você jogou no seu String[] list = new String[29]; , e depois no while o list[i] recebeu o resultSet(“usuario”); ai comparou ele com diferente de null porque ? depois me fala se puder !

#18

Joguei todo o resultado da QUERY da coluna usuario dentro deste list.

Ai comparei ele com null para saber se tinha algum usuário agendado reunião.

#19

entendi !! valeu

#20

Mas encontrei um problema galera, achei que estava funcionando mas não funcionou direito…

Minha logica estava toda errada e eu acabei comendo bola :s .