Organization of Java project
The project is organized in Eclipse as follow:
- model: package of all entity classes
- dao: package of all DAO classes
- test.unit: package of JUnit test classes corresponding to the DAO classes.
- view.room: package of all view classes related to room management
- view.user: package of all view classes related to user management
- view.booking: of all view classes related to booking management
- view.stat.room: package of all view classes related to room statistics
Code for module edit room
Room.java:
package model;
import java.io.Serializable;
public class Room implements Serializable{
private int id;
private String name;
private String type;
private float price;
private String des;
public Room() {
super();
}
public Room(String name, String type, float price, String des) {
super();
this.name = name;
this.type = type;
this.price = price;
this.des = des;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public String getDes() {
return des;
}
public void setDes(String des) {
this.des = des;
}
}
User.java:
package model;
import java.io.Serializable;
public class User implements Serializable{
private int id;
private String username;
private String password;
private String name;
private String position;
public User() {
super();
}
public User(String username, String password, String name, String position) {
super();
this.username = username;
this.password = password;
this.name = name;
this.position = position;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPosition() {
return position;
}
public void setPosition(String position) {
this.position = position;
}
}
DAO.java: You should modify the value of the variable dbUrl, username (root) and password (xxx) on three highlighted lines to connect to your DB
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
public class DAO {
public static Connection con;
public DAO(){
if(con == null){
String dbUrl =
"jdbc:mysql://localhost:3307/hotel?autoReconnect=true&useSSL=false";
String dbClass = "com.mysql.jdbc.Driver";
try {
Class.forName(dbClass);
con = DriverManager.getConnection (dbUrl, "root", "xxx");
}catch(Exception e) {
e.printStackTrace();
}
}
}
}
UserDAO.java
package dao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import model.User;
public class UserDAO extends DAO{
public UserDAO() {
super();
}
public boolean checkLogin(User user) {
boolean result = false;
String sql = "SELECT position FROM tblUser WHERE username = ? AND
password = ?";
try {
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
ResultSet rs = ps.executeQuery();
if(rs.next()) {
user.setName(rs.getString("name"));
user.setPosition(rs.getString("position"));
result = true;
}
}catch(Exception e) {
e.printStackTrace();
}
return result;
}
}
RoomDAO.java:
package dao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import model.Room;
public class RoomDAO extends DAO{
public RoomDAO() {
super();
}
/**
* search all rooms in the tblRoom whose name contains the @key
* @param key
* @return list of room whose name contains the @key
*/
public ArrayList<Room> searchRoom(String key){
ArrayList<Room> result = new ArrayList<Room>();
String sql = "SELECT * FROM tblRoom WHERE name LIKE ?";
try{
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, "%" + key + "%");
ResultSet rs = ps.executeQuery();
while(rs.next()){
Room rm = new Room();
rm.setId(rs.getInt("id"));
rm.setName(rs.getString("name"));
rm.setType(rs.getString("type"));
rm.setPrice(rs.getFloat("price"));
rm.setDes(rs.getString("des"));
result.add(rm);
}
}catch(Exception e){
e.printStackTrace();
}
return result;
}
/**
* update the @room
* @param rm
*/
public boolean updateRoom(Room rm){
String sql = "UPDATE tblroom SET name=?, type=?, price=?, des=?
WHERE id=?";
try{
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, rm.getName());
ps.setString(2, rm.getType());
ps.setFloat(3, rm.getPrice());
ps.setString(4, rm.getDes());
ps.setInt(5, rm.getId());
ps.executeUpdate();
}catch(Exception e){
e.printStackTrace();
return false;
}
return true;
}
}
LoginFrm.java
package view.user;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JTextField;
import dao.UserDAO;
import model.User;
public class LoginFrm extends JFrame implements ActionListener{
private JTextField txtUsername;
private JPasswordField txtPassword;
private JButton btnLogin;
public LoginFrm(){
super("Login");
txtUsername = new JTextField(15);
txtPassword = new JPasswordField(15);
txtPassword.setEchoChar('*');
btnLogin = new JButton("Login");
JPanel pnMain = new JPanel();
pnMain.setSize(this.getSize().width-5, this.getSize().height-20);
pnMain.setLayout(new BoxLayout(pnMain,BoxLayout.PAGE_AXIS));
pnMain.add(Box.createRigidArea(new Dimension(0,10)));
JLabel lblHome = new JLabel("Login");
lblHome.setAlignmentX(Component.CENTER_ALIGNMENT);
lblHome.setFont (lblHome.getFont ().deriveFont (20.0f));
pnMain.add(lblHome);
pnMain.add(Box.createRigidArea(new Dimension(0,20)));
JPanel pnUsername = new JPanel();
pnUsername.setLayout(new FlowLayout());
pnUsername.add(new JLabel("Username:"));
pnUsername.add(txtUsername);
pnMain.add(pnUsername);
JPanel pnPass = new JPanel();
pnPass.setLayout(new FlowLayout());
pnPass.add(new JLabel("Password:"));
pnPass.add(txtPassword);
pnMain.add(pnPass);;
pnMain.add(btnLogin);
pnMain.add(Box.createRigidArea(new Dimension(0,10)));
btnLogin.addActionListener(this);
this.setSize(400,200);
this.setLocation(200,10);
this.setContentPane(pnMain);
this.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
}
public void actionPerformed(ActionEvent e) {
if((e.getSource() instanceof JButton)
&&(((JButton)e.getSource()).equals(btnLogin))) {
User user = new User();
user.setUsername(txtUsername.getText());
user.setPassword(txtPassword.getText());
UserDAO ud = new UserDAO();
if(ud.checkLogin(user)) {
if(user.getPosition().equalsIgnoreCase("manager")) {
(new ManagerHomeFrm()).setVisible(true);
this.dispose();
}else if(user.getPosition().equalsIgnoreCase("seller")) {
(new SellerHomeFrm()).setVisible(true);
this.dispose();
}else
JOptionPane.showMessageDialog(this,
"The function of the role " + user.getPosition()
+ " is under construction!");
}else {
JOptionPane.showMessageDialog(this,
"Incorrect username and/or password!");
}
}
}
public static void main(String[] args) {
LoginFrm myFrame = new LoginFrm();
myFrame.setVisible(true);
}
}
ManagerHomeFrm.java:
package view.user;
import java.awt.BorderLayout;
import java.awt.Component;
import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.Box;
import javax.swing.BoxLayout;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import model.User;
import view.room.ManageRoomFrm;
public class ManagerHomeFrm extends JFrame implements ActionListener{
private JButton btnHotel, btnRoom, btnStat;
private User user;
public ManagerHomeFrm(User user) {
super("Manager home");
this.user = user;
JPanel listPane = new JPanel();
listPane.setLayout(new BoxLayout(listPane, BoxLayout.PAGE_AXIS));
JPanel lblPane = new JPanel();
lblPane.setLayout(new BoxLayout(lblPane, BoxLayout.LINE_AXIS));
lblPane.add(Box.createRigidArea(new Dimension(450, 0)));
JLabel lblUser = new JLabel("Loged in as: " + user.getName());
lblUser.setAlignmentX(Component.RIGHT_ALIGNMENT);
lblPane.add(lblUser);
listPane.add(lblPane);
listPane.add(Box.createRigidArea(new Dimension(0,20)));
JLabel lblHome = new JLabel("Manager's home");
lblHome.setAlignmentX(Component.CENTER_ALIGNMENT);
lblHome.setFont (lblHome.getFont ().deriveFont (28.0f));
listPane.add(lblHome);
listPane.add(Box.createRigidArea(new Dimension(0,20)));
btnHotel = new JButton("Hotel management");
btnHotel.setAlignmentX(Component.CENTER_ALIGNMENT);
btnHotel.addActionListener(this);
listPane.add(btnHotel);
listPane.add(Box.createRigidArea(new Dimension(0,10)));
btnRoom = new JButton("Room management");
btnRoom.setAlignmentX(Component.CENTER_ALIGNMENT);
btnRoom.addActionListener(this);
listPane.add(btnRoom);
listPane.add(Box.createRigidArea(new Dimension(0,10)));
btnStat = new JButton("View statistic");
btnStat.setAlignmentX(Component.CENTER_ALIGNMENT);
btnStat.addActionListener(this);
listPane.add(btnStat);
this.setSize(600,300);
this.setLocation(200,10);
this.add(listPane, BorderLayout.CENTER);
this.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
}
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
if((e.getSource() instanceof JButton)&&
(((JButton)e.getSource()).equals(btnRoom))) {
(new ManageRoomFrm(user)).setVisible(true);
this.dispose();
}else {
JOptionPane.showMessageDialog(this,
"This function is under construction!");
}
}
}
ManageRoomFrm.java:
package view.room;
import java.awt.BorderLayout;
import java.awt.Component;
import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.Box;
import javax.swing.BoxLayout;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import model.User;
public class ManageRoomFrm extends JFrame implements ActionListener{
private JButton btnAdd, btnEdit, btnDel;
private User user;
public ManageRoomFrm(User user) {
super("Room management");
this.user = user;
JPanel listPane = new JPanel();
listPane.setLayout(new BoxLayout(listPane, BoxLayout.PAGE_AXIS));
JPanel lblPane = new JPanel();
lblPane.setLayout(new BoxLayout(lblPane, BoxLayout.LINE_AXIS));
lblPane.add(Box.createRigidArea(new Dimension(450, 0)));
JLabel lblUser = new JLabel("Loged in as: " + user.getName());
lblUser.setAlignmentX(Component.RIGHT_ALIGNMENT);
lblPane.add(lblUser);
listPane.add(lblPane);
listPane.add(Box.createRigidArea(new Dimension(0,20)));
JLabel lblHome = new JLabel("Room management");
lblHome.setAlignmentX(Component.CENTER_ALIGNMENT);
lblHome.setFont (lblHome.getFont ().deriveFont (28.0f));
listPane.add(lblHome);
listPane.add(Box.createRigidArea(new Dimension(0,20)));
btnAdd = new JButton("Add room");
btnAdd.setAlignmentX(Component.CENTER_ALIGNMENT);
btnAdd.addActionListener(this);
listPane.add(btnAdd);
listPane.add(Box.createRigidArea(new Dimension(0,10)));
btnEdit = new JButton("Edit room");
btnEdit.setAlignmentX(Component.CENTER_ALIGNMENT);
btnEdit.addActionListener(this);
listPane.add(btnEdit);
listPane.add(Box.createRigidArea(new Dimension(0,10)));
btnDel = new JButton("Delete room");
btnDel.setAlignmentX(Component.CENTER_ALIGNMENT);
btnDel.addActionListener(this);
listPane.add(btnDel);
this.setSize(600,300);
this.setLocation(200,10);
this.add(listPane, BorderLayout.CENTER);
this.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
}
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
if((e.getSource() instanceof JButton)&&
(((JButton)e.getSource()).equals(btnEdit))) {
(new SearchRoomFrm(user)).setVisible(true);
this.dispose();
}else {
JOptionPane.showMessageDialog(this,
"This function is under construction!");
}
}
}
SearchRoomFrm.java:
package view.room;
import java.awt.Component;
import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.util.ArrayList;
import javax.swing.Box;
import javax.swing.BoxLayout;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;
import dao.RoomDAO;
import model.Room;
import model.User;
public class SearchRoomFrm extends JFrame implements ActionListener{
private ArrayList<Room> listRoom;
private JTextField txtKey;
private JButton btnSearch;
private JTable tblResult;
private User user;
private SearchRoomFrm mainFrm;
public SearchRoomFrm(User user){
super("Search room to edit");
this.user = user;
mainFrm = this;
listRoom = new ArrayList<Room>();
JPanel pnMain = new JPanel();
pnMain.setSize(this.getSize().width-5, this.getSize().height-20);
pnMain.setLayout(new BoxLayout(pnMain,BoxLayout.Y_AXIS));
pnMain.add(Box.createRigidArea(new Dimension(0,10)));
JLabel lblHome = new JLabel("Search a room to edit");
lblHome.setAlignmentX(Component.CENTER_ALIGNMENT);
lblHome.setFont (lblHome.getFont ().deriveFont (20.0f));
pnMain.add(lblHome);
pnMain.add(Box.createRigidArea(new Dimension(0,20)));
JPanel pn1 = new JPanel();
pn1.setLayout(new BoxLayout(pn1,BoxLayout.X_AXIS));
pn1.setSize(this.getSize().width-5, 20);
pn1.add(new JLabel("Room name: "));
txtKey = new JTextField();
pn1.add(txtKey);
btnSearch = new JButton("Search");
btnSearch.addActionListener(this);
pn1.add(btnSearch);
pnMain.add(pn1);
pnMain.add(Box.createRigidArea(new Dimension(0,10)));
JPanel pn2 = new JPanel();
pn2.setLayout(new BoxLayout(pn2,BoxLayout.Y_AXIS));
tblResult = new JTable();
JScrollPane scrollPane= new JScrollPane(tblResult);
tblResult.setFillsViewportHeight(false);
scrollPane.setPreferredSize(new
Dimension(scrollPane.getPreferredSize().width, 250));
tblResult.addMouseListener(new MouseAdapter() {
public void mouseClicked(MouseEvent e) {
int column = tblResult.getColumnModel().
getColumnIndexAtX(e.getX()); // get the coloum of the button
int row = e.getY()/tblResult.getRowHeight(); // get row
// *Checking the row or column is valid or not
if (row < tblResult.getRowCount() && row >= 0 &&
column < tblResult.getColumnCount() && column >= 0) {
(new EditRoomFrm(user,
listRoom.get(row))).setVisible(true);
mainFrm.dispose();
}
}
});
pn2.add(scrollPane);
pnMain.add(pn2);
this.add(pnMain);
this.setSize(600,300);
this.setLocation(200,10);
this.setVisible(true);
this.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
}
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
JButton btnClicked = (JButton)e.getSource();
if(btnClicked.equals(btnSearch)){
if((txtKey.getText() == null)||(txtKey.getText().length() == 0))
return;
RoomDAO rd = new RoomDAO();
listRoom = rd.searchRoom(txtKey.getText().trim());
String[] columnNames = {"Id", "Name", "Type", "Price",
"Description"};
String[][] value = new String[listRoom.size()][5];
for(int i=0; i<listRoom.size(); i++){
value[i][0] = listRoom.get(i).getId() +"";
value[i][1] = listRoom.get(i).getName();
value[i][2] = listRoom.get(i).getType();
value[i][3] = listRoom.get(i).getPrice() +"";
value[i][4] = listRoom.get(i).getDes();
}
DefaultTableModel tableModel =
new DefaultTableModel(value, columnNames) {
@Override
public boolean isCellEditable(int row, int column) {
//unable to edit cells
return false;
}
};
tblResult.setModel(tableModel);
}
}
}
EditRoomFrm.java:
package view.room;
import java.awt.Component;
import java.awt.Dimension;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.Box;
import javax.swing.BoxLayout;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextField;
import dao.RoomDAO;
import model.Room;
import model.User;
import view.user.ManagerHomeFrm;
public class EditRoomFrm extends JFrame implements ActionListener{
private Room room;
private JTextField txtId, txtName, txtType, txtPrice, txtDes;
private JButton btnUpdate, btnReset;
private User user;
public EditRoomFrm(User user, Room room){
super("Edit a room");
this.user = user;
this.room = room;
JPanel pnMain = new JPanel();
pnMain.setSize(this.getSize().width-5, this.getSize().height-20);
pnMain.setLayout(new BoxLayout(pnMain,BoxLayout.Y_AXIS));
pnMain.add(Box.createRigidArea(new Dimension(0,10)));
JLabel lblHome = new JLabel("Edit a room");
lblHome.setAlignmentX(Component.CENTER_ALIGNMENT);
lblHome.setFont (lblHome.getFont ().deriveFont (20.0f));
pnMain.add(lblHome);
pnMain.add(Box.createRigidArea(new Dimension(0,20)));
txtId = new JTextField(15);
txtId.setEditable(false);
txtName = new JTextField(15);
txtType = new JTextField(15);
txtPrice = new JTextField(15);
txtDes = new JTextField(15);
btnUpdate = new JButton("Update");
btnReset = new JButton("Reset");
JPanel content = new JPanel();
content.setLayout(new GridLayout(6,2));
content.add(new JLabel("Room ID:")); content.add(txtId);
content.add(new JLabel("Room name:")); content.add(txtName);
content.add(new JLabel("Type:")); content.add(txtType);
content.add(new JLabel("Price:")); content.add(txtPrice);
content.add(new JLabel("Description:")); content.add(txtDes);
content.add(btnUpdate); content.add(btnReset);
pnMain.add(content);
btnUpdate.addActionListener(this);
btnReset.addActionListener(this);
initForm();
this.setContentPane(pnMain);
this.setSize(600,300);
this.setLocation(200,10);
this.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
}
private void initForm(){
if(room != null){
txtId.setText(room.getId()+"");
txtName.setText(room.getName());
txtType.setText(room.getType());
txtPrice.setText(room.getPrice()+"");
txtDes.setText(room.getDes());
}
}
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
JButton btnClicked = (JButton)e.getSource();
if(btnClicked.equals(btnReset)){
initForm();
return;
}
if(btnClicked.equals(btnUpdate)){
btnUpdateClick();
}
}
private void btnUpdateClick(){
room.setName(txtName.getText());
room.setType(txtType.getText());
room.setPrice(Float.parseFloat(txtPrice.getText()));
room.setDes(txtDes.getText());
RoomDAO rd = new RoomDAO();
if(rd.updateRoom(room)) {
JOptionPane.showMessageDialog(this,
"The room is succeffully updated!");
(new ManagerHomeFrm(user)).setVisible(true);
this.dispose();
}
}
}
Module booking room
This section presents only the classes in the DAO layer, the classes in the view layer are considered as your exercises.
RoomDAO.java, add methods:
public ArrayList<Room> searchFreeRoom(Date checkin, Date checkout){
ArrayList<Room> result = new ArrayList<Room>();
String sql = "SELECT * FROM tblRoom WHERE id NOT IN (SELECT idroom
FROM tblBookedRoom WHERE checkout > ? AND checkin < ?)";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try{
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, sdf.format(checkin));
ps.setString(2, sdf.format(checkout));
ResultSet rs = ps.executeQuery();
while(rs.next()){
Room rm = new Room();
rm.setId(rs.getInt("id"));
rm.setName(rs.getString("name"));
rm.setType(rs.getString("type"));
rm.setPrice(rs.getFloat("price"));
rm.setDes(rs.getString("des"));
result.add(rm);
}
}catch(Exception e){
e.printStackTrace();
}
return result;
}
ClientDAO.java
package dao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import model.Client;
public class ClientDAO extends DAO{
/**
* search all clients in the tblClient whose name contains the @key
* @param key
* @return list of client whose name contains the @key
*/
public ArrayList<Client> searchClient(String key){
ArrayList<Client> result = new ArrayList<Client>();
String sql = "SELECT * FROM tblclient WHERE name LIKE ?";
try{
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, "%" + key + "%");
ResultSet rs = ps.executeQuery();
while(rs.next()){
Client client = new Client();
client.setId(rs.getInt("id"));
client.setName(rs.getString("name"));
client.setIdCard(rs.getString("idcard"));
client.setAddress(rs.getString("address"));
client.setTel(rs.getString("tel"));
client.setEmail(rs.getString("email"));
client.setNote(rs.getString("note"));
result.add(client);
}
}catch(Exception e){
e.printStackTrace();
}
return result;
}
/**
* add a new @client into the DB
* @param client
*/
public void addClient(Client client){
String sql = "INSERT INTO tblclient(name, idcard, address, tel, email,
note) VALUES(?,?,?,?,?,?)";
try{
PreparedStatement ps = con.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
ps.setString(1, client.getName());
ps.setString(2, client.getIdCard());
ps.setString(3, client.getAddress());
ps.setString(4, client.getTel());
ps.setString(5, client.getEmail());
ps.setString(6, client.getNote());
ps.executeUpdate();
//get id of the new inserted client
ResultSet generatedKeys = ps.getGeneratedKeys();
if (generatedKeys.next()) {
client.setId(generatedKeys.getInt(1));
}
}catch(Exception e){
e.printStackTrace();
}
}
}
BookingDAO.java:
package dao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import model.BookedRoom;
import model.Booking;
import model.Client;
public class BookingDAO extends DAO{
public BookingDAO() {
super();
}
/**
* Insert a new booking into the database, including its booked rooms. All are added in a single transaction.
* @param b
* @return
*/
public boolean addBooking(Booking b) {
String sqlAddBooking = "INSERT INTO tblBooking(idcreator, idclient, bookingdate, saleoff, note) VALUES(?,?,?,?,?)";
String sqlAddBookedRoom = "INSERT INTO tblBookedRoom(idbooking, idroom, checkin, checkout, price, saleoff, ischeckin) VALUES(?,?,?,?,?,?,?)";
String sqlCheckbookedRoom = "SELECT * FROM tblBookedRoom WHERE idroom = ? AND checkout > ? AND checkin < ?";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
boolean result = true;
try {
con.setAutoCommit(false);
PreparedStatement ps = con.prepareStatement(sqlAddBooking,
Statement.RETURN_GENERATED_KEYS);
ps.setInt(1, b.getCreator().getId());
ps.setInt(2, b.getClient().getId());
ps.setString(3, sdf.format(b.getBookedDate()));
ps.setFloat(4, b.getSaleoff());
ps.setString(5, b.getNote());
ps.executeUpdate();
//get id of the new inserted booking
ResultSet generatedKeys = ps.getGeneratedKeys();
if (generatedKeys.next()) {
b.setId(generatedKeys.getInt(1));
//insert booked rooms
for(BookedRoom br: b.getBookedRoom()) {
//check if the room is available at the period
ps = con.prepareStatement(sqlCheckbookedRoom);
ps.setInt(1, br.getRoom().getId());
ps.setString(2, sdf.format(br.getCheckin()));
ps.setString(3, sdf.format(br.getCheckout()));
ResultSet rs = ps.executeQuery();
if(rs.next()) {//unavailable
result = false;
try {
con.rollback();
con.setAutoCommit(true);
}catch(Exception ex) {
result = false;
ex.printStackTrace();
}
return result;
}
//insert booked room
ps = con.prepareStatement(sqlAddBookedRoom,
Statement.RETURN_GENERATED_KEYS);
ps.setInt(1, b.getId());
ps.setInt(2, br.getRoom().getId());
ps.setString(3, sdf.format(br.getCheckin()));
ps.setString(4, sdf.format(br.getCheckout()));
ps.setFloat(5, br.getPrice());
ps.setFloat(6, br.getSaleoff());
ps.setBoolean(7, br.isChecked());
ps.executeUpdate();
//get id of the new inserted booking
generatedKeys = ps.getGeneratedKeys();
if (generatedKeys.next()) {
br.setId(generatedKeys.getInt(1));
}
}
}
//con.commit();//set this line into comment in JUnit test mode
}catch(Exception e) {
result = false;
try {
con.rollback();
}catch(Exception ex) {
result = false;
ex.printStackTrace();
}
e.printStackTrace();
}finally {
try {
//con.setAutoCommit(true);//set this line into comment in JUnit test mode
}catch(Exception ex) {
result = false;
ex.printStackTrace();
}
}
return result;
}
}
Module view room statistic
This section presents only the classes in the DAO layer, the classes in the view layer are considered as your exercises.
RoomStatDAO.java:
package dao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import model.RoomStat;
public class RoomStatDAO extends DAO{
public RoomStatDAO() {
super();
}
public ArrayList<RoomStat> getRoomStat(Date startDate, Date endDate){
ArrayList<RoomStat> result = new ArrayList<RoomStat>();
String sql = "SELECT a.id, a.name, a.type, a.des, (SELECT SUM(DATEDIFF(LEAST(b.checkout, ?), GREATEST(b.checkin, ?))) FROM tblBookedRoom b WHERE b.idroom = a.id AND b.checkout > ? AND b.checkin < ? AND b.ischeckin = 1 GROUP BY b.idroom) as days, (SELECT SUM(DATEDIFF(LEAST(b.checkout, ?), GREATEST(b.checkin, ?))*b.price) FROM tblBookedRoom b WHERE b.idroom = a.id AND b.checkout > ? AND b.checkin < ? AND b.ischeckin = 1 GROUP BY b.idroom) as income FROM tblRoom a ORDER BY income DESC, days DESC";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, sdf.format(endDate));
ps.setString(2, sdf.format(startDate));
ps.setString(3, sdf.format(startDate));
ps.setString(4, sdf.format(endDate));
ps.setString(5, sdf.format(endDate));
ps.setString(6, sdf.format(startDate));
ps.setString(7, sdf.format(startDate));
ps.setString(8, sdf.format(endDate));
ResultSet rs = ps.executeQuery();
while(rs.next()) {
RoomStat r = new RoomStat();
r.setId(rs.getInt("id"));
r.setName(rs.getString("name"));
r.setType(rs.getString("type"));
r.setDes(rs.getString("des"));
r.setTotalDay(rs.getInt("days"));
r.setTotalIncome(rs.getFloat("income"));
result.add(r);
}
}catch(Exception e) {
e.printStackTrace();
}
return result;
}
}
BookingDAO.java, add the following methods:
/**
* get list of booking involved the room whose @idroom is given between @startDate and @endDate
* @param idroom
* @param startDate
* @param endDate
* @return
*/
public ArrayList<Booking> getBookingOfRoom(int idroom, Date startDate, Date endDate){
ArrayList<Booking> result = new ArrayList<Booking>();
String sql = "SELECT a.id as idbookedroom, GREATEST(a.checkin,?) as checkin, LEAST(a.checkout,?) as checkout, a.price, a.saleoff as roomsaleoff, b.id as idbooking, b.saleoff as bookingsaleoff, c.id as idclient, c.name, c.address, c.idcard, c.tel FROM tblBookedRoom a, tblBooking b, tblClient c WHERE a.idroom = ? AND a.ischeckin = 1 AND a.checkout > ? AND a.checkin < ? AND b.id = a.idbooking AND c.id = b.idclient";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try{
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, sdf.format(startDate));
ps.setString(2, sdf.format(endDate));
ps.setInt(3, idroom);
ps.setString(4, sdf.format(startDate));
ps.setString(5, sdf.format(endDate));
ResultSet rs = ps.executeQuery();
//a == null ? b : (b == null ? a : (a.before(b) ? a : b));
while(rs.next()){
Booking b = new Booking();
b.setId(rs.getInt("idbooking"));
b.setSaleoff(rs.getFloat("bookingsaleoff"));
//client
Client c = new Client();
c.setId(rs.getInt("idclient"));
c.setName(rs.getString("name"));
c.setAddress(rs.getString("address"));
c.setIdCard(rs.getString("idcard"));
b.setClient(c);
//booked room
BookedRoom br = new BookedRoom();
br.setId(rs.getInt("idbookedroom"));
br.setSaleoff(rs.getFloat("roomsaleoff"));
br.setPrice(rs.getFloat("price"));
br.setCheckin(rs.getDate("checkin"));
br.setCheckout(rs.getDate("checkout"));
b.getBookedRoom().add(br);
result.add(b);
}
}catch(Exception e){
e.printStackTrace();
}
return result;
}
Software: Art of Design and Programming 