一.会议室预定模块
a.某公司有多个会议室,以房间号区分
b.如果某部门需要预定会议室,则会提交预定请求。
(包含预定开始使用时间,预定结束使用,所预定会议室房间号) 1.设计一个表,保存会议室预定信息
2.要求用sql语句及java代码判断在2003-3-10 下午 3:00--4:00 3号会议室 是否空闲。
1.有关sql语句 2.java的判断代码
a.某公司有多个会议室,以房间号区分
b.如果某部门需要预定会议室,则会提交预定请求。
(包含预定开始使用时间,预定结束使用,所预定会议室房间号) 1.设计一个表,保存会议室预定信息
2.要求用sql语句及java代码判断在2003-3-10 下午 3:00--4:00 3号会议室 是否空闲。
1.有关sql语句 2.java的判断代码
if(状态==0){
空闲
}else{
不空
}
room(id,room_number) schedule(id,roomid,start_time,end_time,state)select sc.state from
schedule sc left join room ro
on sc.roomid=ro.id where ro.room_number='3'
and sc.start_time < "end" and sc.end_time > "start"; 至于"end" 和 "start"的大小问题,可以有业务逻辑判断
if exists (select name from dbo.sysobjects where name = 'Boardroom')
drop table Boardroom
GOcreate table Boardroom(
id int identity(1,1) primary key,--序号
sqe varchar(20) null, --会议室编号
memo varchar(100) null, --描述
)--预定会议室表[Reserve board room]
if exists (select name from dbo.sysobjects where name = 'rbroom')
drop table rbroom
GOcreate table rbroom(
id int identity(1,1) primary key, --序号
rsqe varchar(20) null, --会议室编号
begintime datetime null, --预定开始使用时间
endtime datetime null, --预定结束使用
memo varchar(100) null, --描述
)--一触发器,删除会议室信息时,同时删除会议室预定信息
if exists (select name from sysobjects where name='TrbRoomDelete' and type='tr')
drop trigger TrbRoomDelete create trigger TrbRoomDelete
on Boardroom
for delete
as
delete rbroom from deleted Boardroom inner join rbroom on Boardroom.sqe=rbroom.rsqe
go insert into Boardroom(sqe,memo) values('001','第一会议室')
insert into Boardroom(sqe,memo) values('002','第二会议室')insert into rbroom(rsqe,begintime,endtime,memo) values('002','2007-02-02','2007-02-03','会议时间两天')
insert into rbroom(rsqe,begintime,endtime) values('001','2007-02-02','2007-02-03')--有数据说明不空闲
select id from rbroom where begintime>=convert(datetime,'1987-5-5') and endtime<=convert(datetime,'1985-5-5')
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class BoardRoom { BoardRoom(){}
//连接数据库
private Connection connDB(){
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url="jdbc:microsoft:sqlserver://url";
//mydb为数据库
String user="";
String password="";
Connection conn= DriverManager.getConnection(url,user,password);
return conn;
} catch (SQLException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return null;
}
private int insertDate(String sql){
Connection conn=connDB();
Statement state=null;
try {
state = conn.createStatement();
int up=state.executeUpdate(sql);
return up;
} catch (SQLException e) {
e.printStackTrace();
}finally{
//关闭连接
try {
state.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return 0;
}
private boolean isEx(String sql){
Connection conn=connDB();
Statement state=null;
ResultSet rs=null;
try {
state = conn.createStatement();
rs=state.executeQuery(sql);
if(rs!=null)
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
}finally{
//关闭连接
try {
state.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return false;
}
public static void main(String a[]){
//初始化信息的SQL语句
String sql1="insert into Boardroom(sqe,memo) values('003','第一会议室')";
String sql2="insert into Boardroom(sqe,memo) values('005','第二会议室')";
String sql3="insert into rbroom(rsqe,begintime,endtime,memo) values('005','2007-02-02 13:20:00','2007-02-02 14:20:00','会议时间两天')";
String sql4="insert into rbroom(rsqe,begintime,endtime) values('003','2007-02-02','2007-02-03')";
BoardRoom br=new BoardRoom();
br.insertDate(sql1);
br.insertDate(sql2);
br.insertDate(sql3);
br.insertDate(sql4);
//判断2007-02-02 13:20:00 到 2007-02-02 14:20:00 会议室 005 是否在使用
String sql5="select id from rbroom where begintime>=convert(datetime,'2007-02-02 13:10:00') and endtime<=convert(datetime,'2007-02-02 14:20:00') and rsqe='005'";
boolean bb=br.isEx(sql5);
if(bb){
System.out.print("该会议室在该段时间内在使用");
}else{
System.out.print("该会议室在该段时间内在没使用");
}
//判断2007-02-02 13:20:00 到 2007-02-02 14:20:00 会议室 005 是否在使用
String sql6="select id from rbroom where begintime>=convert(datetime,'2007-02-02 15:10:00') and endtime<=convert(datetime,'2007-02-02 16:20:00') and rsqe='005'";
bb=br.isEx(sql6);
if(bb){
System.out.println("该会议室在该段时间内在使用");
}else{
System.out.println("该会议室在该段时间内在没使用");
}
}
}