Using JDBC to connect to a database from Java program

Objective
Our objective is to learn how to connect to a database from a Java program and then, using SQL statements to manipulate on the database.

Download
- You need to install MySQL on your computer, and possibly a MySQL Workbench to easy to create a database and its tables from a GUI (see the topic http://coderandcode.blogspot.com/2013/08/sql-basic.html)
- You also need to download Eclipse http://www.eclipse.org/downloads/, you could download the standard version or the IDE for Java EE developers (this version will be needed in web applications)
- You have to download a MySQL driver to connect to a database from a Java program: http://dev.mysql.com/downloads/connector/j/. And the put the .jar (in the downloaded package) into the eclipse root directory/plugins
- Another way to config it is put the .jar file in a project in Eclipse: right click on JRE lib of the project -> choose the build path -> choose edit -> click on the "add extension jar files" and the point out to the path to the .jar file.

Connection
First of all, you have to connect to a database before using it. Assume that we have a database for a hotel management with five tables as follows:
- tblHotel: contains information about hotels
- tblRoom: contains information about rooms of the hotels
- tblUser: contains information about the user of the application, including the client of the hotels
- tblBooking: contains information about bookings of clients and the perspective rooms during a period of time
- tblBill: contains information about the payment of booking and/or cancel.


 And five entity classes corresponding to these five tables are:



You could easily implement these entity classes because they have only attributes, constructor(s) and getter/setter methods.
Now we could start to connect to this database and implement some methods to service the business action of application. Note that these methods could be placed into a DAO (Data Access Object) or a normal control class.

Get Connection
public Connection getConnection(String dbClass, String dbUrl, String userName, String password) throws SQLException {
    Connection conn = null;   
    try {
        Class.forName(dbClass);
        Connection conn = DriverManager.getConnection (dbUrl, userName, password);
    }catch(ClassNotFoundException e) {
        e.printStackTrace();
    }catch(SQLException e) {
        throws e;
    }
    return conn;
  }

 where:
dbClass = "com.mysql.jdbc.Driver";
String dbUrl =  "jdbc:mysql://localhost:3306/hotelmanagement";

Add a new room into DB
public void addRoom(int hotelID, Room r)throws SQLException{
    String sql = "INSERT INTO tblRoom(name, idHotel, type, displayPrice, description) VALUES(?,?,?,?,?)";
    PreparedStatement prstm = null;

    try {
      prstm = conn.prepareStatement(sql);
      prstm.setString(1, r.getName());
      prstm.setInt(2, hotelID);
      prstm.setString(3, r.getType());
      prstm.setFloat(4, r.getDisplayPrice());
      prstm.setString(5, r.getDescription());
      prstm.executeUpdate();
    } catch (SQLException e ) {
        throw e;
    }
}

Search for free rooms 
This method enables us to find some free rooms in e period of time. The return value is an array of Room objects.
public Room[] searchFreeRoom(Date sd, Date ed)throws SQLException{
    Room[] result = null;
    String sql = "SELECT * FROM tblRoom WHERE id NOT IN (SELECT RoomId from tblBooking WHERE ((StartDate BETWEEN ? AND ?) OR EndDate BETWEEN ? AND ?) OR (? BETWEEN StartDate AND EndDate) OR (? BETWEEN StartDate AND EndDate)))";
    try {
        PreparedStatement prstm = conn.prepareStatement(sql);
        prstm.setDate(1, sd); prstm.setDate(3, sd); prstm.setDate(5, sd);
        prstm.setDate(2, ed); prstm.setDate(4, ed); prstm.setDate(6, ed);

        ResultSet rs = prstm.executeQuery();
        if (rs.last()) {
            result = new Room[rs.getRow()];
               rs.beforeFirst();
        }
        int count = 0;
        while (rs.next()) {
             result[count] = new Room(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getFloat(4), rs.getString(5));
            count++;
        }
    } catch (SQLException e ) { throw e;}
    return result;
}

Note that if you would like to return a List<Room> instead of a Room[], you do not need the pies of code to count the number of return resultSet to initiate the size of the return array:
        if (rs.last()) {
            result = new Room[rs.getRow()];
               rs.beforeFirst();
        }

Book a free room
public void bookRoom(Booking b)throws SQLException{
    String sql = INSERT INTO tblBooking(idRoom, idUser, startDate, endDate, price, description) VALUES(?, ?, ?, ?, ?, ?)";
    try {
        PreparedStatement prstm = conn.prepareStatement(sql);
   
        prstm.setInt(1, b.getRoom().getId());
        prstm.setInt(2, b.getUser().getId());
        prstm.setDate(3, b.getStartDate());
        prstm.setDate(4, b.getEndDate());
        prstm.setFloat(5, b.getPrice());
        prstm.setString(6, b.getDescription());
        prstm.executeUpdate();

    } catch (SQLException e ) {
        throw e;
    }
}

Calculation of the total income during a period of time
public double totalIncomeByPeriod(Date sd, Date ed)throws SQLException{
    double result = 0;
    String sql = SELECT SUM(b.amount)FROM tblBooking a INNER JOIN tblBill b ON b.idBooking = a.id WHERE  ((a.startDate BETWEEN ? AND ?) AND (a.endDate BETWEEN ? AND ?))" ;
    try {
        PreparedStatement prstm = conn.prepareStatement(sql);
   
        prstm.setDate(1, sd); prstm.setDate(3, sd);
        prstm.setDate(2, ed); prstm.setDate(4, ed);
        ResultSet rs = prstm.executeQuery();

        if(rs.next()){
           result = rs.getDouble(1);
        }
    } catch (SQLException e ) {
        throw e;
    }
    return result;
}

Search and rank the rooms which is booked the most
public Room[] searchHotRoom() throws SQLException{
    Room[] result = null;
    String sql = "SELECT a.* FROM tblRoom a ORDER BY (SELECT SUM(DATEDIFF(DAY, b.startDate, b.endDate)) FROM tblBooking b WHERE b.roomId = a.id) DESC";
    try {
        PreparedStatement prstm = conn.prepareStatement(sql);
        ResultSet rs = prstm.executeQuery();
        if (rs.last()) {
            result = new Room[rs.getRow()];
               rs.beforeFirst();
        }
        int count = 0;
        while (rs.next()) {
             result[count] = new Room(rs.getInt(1), rs.getString(2),
                      rs.getString(3), rs.getFloat(4), rs.getString(5));
            count++;
        }
    } catch (SQLException e ) {
        throw e;
    }
    return result;
}