microfolie/src/db/Database.java

package db;

import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.logging.Logger;

import db.table.EspaceTable;
import entity.Espace;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class Database {
	
	private static final Logger LOGGER = Logger.getLogger(Database.class.getName());
	private static Connection db;
	
	static {
		ini();
		unset();
		setup();
		execute("Insert into Espace(LIBELLE) Values('test')");
		//execute("Insert into Espace(LIBELLE) Values('test')");
		EspaceTable et = EspaceTable.getInstance();
		Espace e = new Espace();
		e.id = 8;
		e.libelle = "bjr";
		et.save(e);
	}
	
	public static void ini() {
		try{
		    Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
		} catch(ClassNotFoundException e){
		    LOGGER.warning("Unable to find Derby driver");
		    throw new IllegalStateException("Unable to connect to the database", e);
		}
		try {
			db = DriverManager.getConnection("jdbc:derby:microfolie;create=true");
		} catch (SQLException e) {
			LOGGER.warning(e.getMessage());
			throw new IllegalStateException("Unable to connect to the database", e);
		}
	}
	
	public static void close() {
		if(db != null) {
			try {
				db.close();
				db = null;
			} catch (SQLException e) {
				LOGGER.warning(e.getMessage());
				throw new IllegalStateException("Unable to close database connection", e);
			}	
		}
	}
	
	public static void setup() {
		if(isSet()) {
			return;
		}
		LOGGER.info("Creating table");
		ManageTable.create.forEach((table, sql) -> {
			LOGGER.info("Creating table " + table);
			execute(sql);
		});
	}
	
	public static void unset() {
		if(!isSet()) {
			return;
		}
		LOGGER.info("Dropping table");
		ManageTable.drop.forEach((table, sql) -> {
			LOGGER.info("Dropping table " + table);
			execute(sql);
		});
	}
	
	public static boolean query(String sql) {
		try(Statement st = db.createStatement()) {
			st.executeQuery(sql);
			return true;
		} catch (SQLException | IllegalStateException e) {
			LOGGER.warning(e.getMessage());
			return false;
		}
	}
	
	public static <T> Optional<T> query(String sql, Mapper<T> mapper) {
		try(Statement st = db.createStatement()) {
			ResultSet result = st.executeQuery(sql);
			T obj = mapper.map(result);
			return Optional.of(obj);
		} catch (SQLException | IllegalStateException e) {
			LOGGER.warning(e.getMessage());
			return Optional.empty();
		}
	}
	
	public static <T> Optional<T> query(String sql, List<Object> params, Mapper<T> mapper) {
		try(PreparedStatement st = db.prepareStatement(sql)) {
			int i = 1;
			for(Object param : params) {
				st.setObject(i++, param);
			}
			ResultSet result = st.executeQuery();
			T obj = mapper.map(result);
			return Optional.of(obj);
		} catch (SQLException | IllegalStateException e) {
			LOGGER.warning(e.getMessage());
			return Optional.empty();
		}
	}
	
	public static boolean execute(String sql) {
		try(Statement st = db.createStatement()) {
			int result = st.executeUpdate(sql);
			return result == 1;
		} catch (SQLException e) {
			LOGGER.warning(e.getMessage());
			return false;
		}
	}
	
	public static boolean execute(String sql, List<Object> params) {
		try(PreparedStatement st = db.prepareStatement(sql)) {
			int i = 1;
			for (Object param : params) {
				st.setObject(i++, param);
			}
			int result = st.executeUpdate();
			return result == 1;
		} catch (SQLException e) {
			LOGGER.warning(e.getMessage());
			return false;
		}
	}
	
	public static Map<String, Object> insert(String sql, List<Object> params, String[] idField) {
		Map<String, Object> ids = new HashMap<>();
		try(PreparedStatement st = db.prepareStatement(sql, idField)) {
			// Ajout parametre
			int i = 1;
			for(Object param : params) {
				st.setObject(i++, param);
			}
			// Execution requete
			int result = st.executeUpdate();
			if(result != 1) {
				return ids;
			}
			// Recuperation des ids genere
			ResultSet rs = st.getGeneratedKeys();
			rs.next();
			i = 1;
			for (String field : idField) {
				ids.put(field, rs.getObject(i));
				i++;
			}
			return ids;
		} catch (SQLException e) {
			LOGGER.warning(e.getMessage());
			return ids;
		}
	}
	
	public static boolean isSet() {
		return query("Select * From Espace");
	}
	
	public static Connection getDb() {
		return db;
	}

}