BikeMapper.java

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.database;

import model.object.Bike;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import resource.log.ProjectLogger;


/**
 *
 * @author Yoldark34 <yoldark@gmail.com>
 */
public class BikeMapper extends AbstractMapper {

	/**
	 * Return all bikes in database
	 *
	 * @return ArrayList<Bike>
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ArrayList<Bike> getAllBikes() throws SQLException, ClassNotFoundException {
		DbConnection adapter = DbConnection.getDbConnection();
		adapter.executeSelectQuery("Select * from " + DataBaseElements.BIKE);
		return (ArrayList<Bike>) adapter.getModelsFromRequest(this);
	}

	/**
	 * Insert bike if id == -1 or update bikes instead
	 *
	 * @param bike
	 * @return int number of rows
	 */
	public int save(Bike bike) {
		int nbRows = 0;
		String query = "";
		if (bike.getId() != -1) {
			//Update do not exist for now because Bike have only one field.
		} else {
			query = "INSERT INTO " + DataBaseElements.BIKE + " (";
			//query += "`" + DataBaseElements.BIKE_ID + "`, `";
			query += ") VALUES (";
			//query += "'" + bike.getId() + "'";//Autoincrement
			query += ")";
		}

		try {
			DbConnection adapter = DbConnection.getDbConnection();
			nbRows = adapter.executeUpdateQuery(query);
		} catch (Exception e) {
		}
		return nbRows;
	}

	/**
	 * get all bikes stocked in the storage belong to this terminal
	 *
	 * @param terminalId
	 * @return int nuumber of bikes
	 */
	public int getAvailableBikesForThisTerminal(int terminalId) {
		String query;
		Bike result = new Bike();

		query = "SELECT ";
		query += "count( * ) AS 'numberOfBikes'";
		query += " FROM ";
		query += DataBaseElements.TERMINAL + " " + DataBaseElements.ALIAS_TERMINAL + ", ";
		query += DataBaseElements.STOCK + " " + DataBaseElements.ALIAS_STOCK + ", ";
		query += DataBaseElements.STORAGE + " " + DataBaseElements.ALIAS_STORAGE + ", ";
		query += DataBaseElements.BIKEUSAGETYPE + " " + DataBaseElements.ALIAS_BIKEUSAGETYPE + ", ";
		query += DataBaseElements.BIKEUSAGE + " " + DataBaseElements.ALIAS_BIKEUSAGE + " ";
		query += " WHERE ";
		query += DataBaseElements.ALIAS_STOCK + "." + DataBaseElements.STOCK_ID + " = " + DataBaseElements.ALIAS_STORAGE + "." + DataBaseElements.STORAGE_IDSTOCK;
		query += " AND ";
		query += DataBaseElements.ALIAS_STORAGE + "." + DataBaseElements.STORAGE_ID + " = " + DataBaseElements.ALIAS_BIKEUSAGE + "." + DataBaseElements.BIKEUSAGE_IDENDSTORAGE;
		query += " AND ";
		query += DataBaseElements.ALIAS_BIKEUSAGETYPE + "." + DataBaseElements.BIKEUSAGETYPE_ID + " = " + DataBaseElements.ALIAS_BIKEUSAGE + "." + DataBaseElements.BIKEUSAGE_IDBIKEUSAGETYPE;
		query += " AND ";
		query += DataBaseElements.ALIAS_BIKEUSAGETYPE + "." + DataBaseElements.BIKEUSAGETYPE_NAME + " = '" + DataBaseElements.BikeUsageType.STOCKING + "'";
		query += " AND ";
		query += DataBaseElements.ALIAS_BIKEUSAGE + "." + DataBaseElements.BIKEUSAGE_ENDDATE + " is NULL";
		query += " AND ";
		query += DataBaseElements.ALIAS_TERMINAL + "." + DataBaseElements.TERMINAL_ID + " = " + terminalId;
		query += " AND ";
		query += DataBaseElements.ALIAS_TERMINAL + "." + DataBaseElements.TERMINAL_IDSTOCK + " = " + DataBaseElements.ALIAS_STOCK + "." + DataBaseElements.STOCK_ID;

		try {
			DbConnection adapter = DbConnection.getDbConnection();
			adapter.executeSelectQuery(query);
			result = (Bike) adapter.getModelFromRequest(this);
		} catch (SQLException | ClassNotFoundException ex) {
			ProjectLogger.log(this, Level.SEVERE, "Erreur d'exécution de la requête de la fonction getAvailableBikesForThisTerminal", ex);
		}

		return result.getNumberOfBikes();
	}

	/**
	 * get rented all rented bikes for now
	 *
	 * @return ArrayList<Bike>
	 */
	public ArrayList<Bike> getRentedBikes() {
		String query;
		ArrayList<Bike> results = new ArrayList<>();

		query = "SELECT ";
		query += DataBaseElements.ALIAS_BIKE + "." + DataBaseElements.BIKE_ID;
		query += " FROM ";
		query += DataBaseElements.TERMINAL + " " + DataBaseElements.ALIAS_TERMINAL + ", ";
		query += DataBaseElements.STOCK + " " + DataBaseElements.ALIAS_STOCK + ", ";
		query += DataBaseElements.STORAGE + " " + DataBaseElements.ALIAS_STORAGE + ", ";
		query += DataBaseElements.BIKEUSAGETYPE + " " + DataBaseElements.ALIAS_BIKEUSAGETYPE + ", ";
		query += DataBaseElements.BIKE + " " + DataBaseElements.ALIAS_BIKE + ", ";
		query += DataBaseElements.BIKEUSAGE + " " + DataBaseElements.ALIAS_BIKEUSAGE + " ";
		query += " WHERE ";
		query += DataBaseElements.ALIAS_STOCK + "." + DataBaseElements.STOCK_ID + " = " + DataBaseElements.ALIAS_STORAGE + "." + DataBaseElements.STORAGE_IDSTOCK;
		query += " AND ";
		query += DataBaseElements.ALIAS_STORAGE + "." + DataBaseElements.STORAGE_ID + " = " + DataBaseElements.ALIAS_BIKEUSAGE + "." + DataBaseElements.BIKEUSAGE_IDENDSTORAGE;
		query += " AND ";
		query += DataBaseElements.ALIAS_BIKEUSAGETYPE + "." + DataBaseElements.BIKEUSAGETYPE_ID + " = " + DataBaseElements.ALIAS_BIKEUSAGE + "." + DataBaseElements.BIKEUSAGE_IDBIKEUSAGETYPE;
		query += " AND ";
		query += DataBaseElements.ALIAS_BIKE + "." + DataBaseElements.BIKE_ID + " = " + DataBaseElements.ALIAS_BIKEUSAGE + "." + DataBaseElements.BIKEUSAGE_IDBIKE;
		query += " AND ";
		query += DataBaseElements.ALIAS_BIKEUSAGETYPE + "." + DataBaseElements.BIKEUSAGETYPE_NAME + " = '" + DataBaseElements.BikeUsageType.RENTING + "'";
		query += " AND ";
		query += DataBaseElements.ALIAS_BIKEUSAGE + "." + DataBaseElements.BIKEUSAGE_ENDDATE + " is NULL";

		try {
			DbConnection adapter = DbConnection.getDbConnection();
			adapter.executeSelectQuery(query);
			results = (ArrayList<Bike>) adapter.getModelsFromRequest(this);
		} catch (SQLException | ClassNotFoundException ex) {
			ProjectLogger.log(this, Level.SEVERE, "Erreur d'exécution de la requête de la fonction getAvailableBikesForThisTerminal", ex);
		}

		return results;
	}

	@Override
	public Object populateModel(ResultSet row) throws SQLException {
		Bike obj = new Bike();
		if (this.hasColumn(DataBaseElements.BIKE_ID, row)) {
			obj.setId(row.getInt(DataBaseElements.BIKE_ID));
		}
		if (this.hasColumn("numberOfBikes", row)) {
			obj.setNumberOfBikes(row.getInt("numberOfBikes"));
		}
		return obj;
	}

	@Override
	Object getEmptyModel() {
		return new Bike();
	}
}