package model.dal;

import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

import model.bo.Personne;
import java.sql.*;

public abstract class PersonneDAO {

	public static Connection getConnection() {
		Connection connection = null;
		// ------------------------------
		String url = "jdbc:mysql://localhost:3306/personnedb" + "?autoReconnect=true&useSSL=false";
		String user = "root";
		String pwd = "";
		try {
			connection = DriverManager.getConnection(url, user, pwd);
			connection.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// ------------------------------
		return connection;
	}

	public static ArrayList<Personne> selectAll() {
		ArrayList<Personne> liste = new ArrayList<Personne>();
		Connection connection = getConnection();
		// ------------------------------
		String requete = "select * from personne";
		try {
			Statement state = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			ResultSet result = state.executeQuery(requete);
			while (result.next()) {
				String prenom = result.getString("prenom");
				String nom = result.getString("nom");
				int id = result.getInt("id");
				// System.out.println(prenom);
				Personne p = new Personne(nom, prenom, id);
				liste.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// ------------------------------
		return liste;
	}
	public static Personne selectOne(int id) {
		Personne p = new Personne();
		String requete = "SELECT * FROM personne WHERE id = ?";
		Connection connection = getConnection();
		try {
			PreparedStatement prepare = (PreparedStatement) connection.prepareStatement(requete);
			// je viens binder le 1er ?
			prepare.setInt(1, id);
			ResultSet rs = prepare.executeQuery();
			// je sors le resultat de la pile
			rs.next();
			p.setId(rs.getInt("id"));
			p.setNom(rs.getString("nom"));
			p.setPrenom(rs.getString("prenom"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return p;
	}
	public static void delete(int id) {
		String requete = "DELETE FROM personne WHERE id = ?";
		Connection connection = getConnection();
		try {
			PreparedStatement prepare = (PreparedStatement) connection.prepareStatement(requete);
			// je viens binder le 1er ?
			prepare.setInt(1, id);
			prepare.execute();
			prepare.close();
			connection.commit();


		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void insert(Personne p) {
		String requete = "INSERT INTO personne (nom,prenom) VALUES (?,?)";
		Connection connection = getConnection();
		try {
			PreparedStatement prepare = (PreparedStatement) connection.prepareStatement(requete);
			// je viens binder le 1er ?
			prepare.setString(1, p.getNom());
			prepare.setString(2, p.getPrenom());
			prepare.execute();
			prepare.close();
			connection.commit();


		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void update(Personne p) {
		String requete = "UPDATE personne SET nom = ?,prenom=? WHERE id=?";
		Connection connection = getConnection();
		try {
			PreparedStatement prepare = (PreparedStatement) connection.prepareStatement(requete);
			// je viens binder le 1er ?
			prepare.setString(1, p.getNom());
			prepare.setString(2, p.getPrenom());
			prepare.setInt(3, p.getId());
			prepare.execute();
			prepare.close();
			connection.commit();


		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}