From d8dc2b6d0d4dbc93da853ef5a7562d6a223ee918 Mon Sep 17 00:00:00 2001 From: polo-pc-greta Date: Sat, 15 Feb 2025 11:52:51 +0100 Subject: release candidate 1 --- src/DAO_Test.java | 38 ++++++++++++++++++++ src/dao/JDBC.java | 90 +++++++++++++++++++++++++++++++++++++++++++++++ src/dao/ResultObject.java | 39 ++++++++++++++++++++ src/dao/SQLexecutor.java | 80 +++++++++++++++++++++++++++++++++++++++++ 4 files changed, 247 insertions(+) create mode 100644 src/DAO_Test.java create mode 100644 src/dao/JDBC.java create mode 100644 src/dao/ResultObject.java create mode 100644 src/dao/SQLexecutor.java diff --git a/src/DAO_Test.java b/src/DAO_Test.java new file mode 100644 index 0000000..aa6a0af --- /dev/null +++ b/src/DAO_Test.java @@ -0,0 +1,38 @@ +/* pour tester les classes */ + +import java.sql.SQLException; +import java.util.HashMap; +import dao.JDBC; +import dao.ResultObject; +import dao.SQLexecutor; + +public class DAO_Test +{ + public static void main(String[] args) throws SQLException, ClassNotFoundException + { + JDBC.setInfos("localhost", "mysql", "tp", "root", ""); + SQLexecutor executor = new SQLexecutor(); + + // SELECT + ResultObject result = executor.executeQuery("SELECT * FROM acces WHERE id = ?", 1); + for(HashMap row : result.getData()) + { + System.out.println("prenom: " + row.get("prenom")); // une entrée + + // foreach sur toutes les entrées + for(HashMap.Entry one_field : row.entrySet()) + { + System.out.print(one_field.getKey() + ": " + one_field.getValue() + "\n"); + } + } + + // INSERT + executor.executeQuery("INSERT INTO acces (prenom, login, password, statut, age) VALUES (?, ?, ?, ?, ?)", "Dylan", "Toto", "Titi", "Etudiant", 22); + + // UPDATE + executor.executeQuery("UPDATE acces SET statut = ? WHERE prenom = ?", "joue à fortnite", "Dylan"); + + // DELETE + executor.executeQuery("DELETE FROM acces WHERE prenom = ?", "Dylan"); + } +} diff --git a/src/dao/JDBC.java b/src/dao/JDBC.java new file mode 100644 index 0000000..714803e --- /dev/null +++ b/src/dao/JDBC.java @@ -0,0 +1,90 @@ +package dao; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.SQLException; + +/** + * connexion à la BDD avec JDBC + * tous les paramètres de connexion et les setters sont statiques + * le nom de la table sélectionnée est dans ModelCRUD + */ +abstract public class JDBC +{ + private static String dbms="mysql"; + private static String strClassName = "com.mysql.cj.jdbc.Driver"; + private static String port = "3306"; + private static String dbname; + private static String login; + private static String password; + private static String host= "localhost"; + private static String strUrl; + + protected Connection getConnection() throws SQLException, ClassNotFoundException + { + Class.forName(strClassName); + return DriverManager.getConnection(strUrl, login, password); +// try { +// Class.forName(strClassName); // chargement du driver +// return DriverManager.getConnection(strUrl, login, password); +// } +// catch(ClassNotFoundException e) { +// System.err.println("Driver non chargé !"); +// e.printStackTrace(); +// } +// catch(SQLException e) { +// e.printStackTrace(); +// } +// return null; + } + + // setters + public static void setHost(String host) { + JDBC.host = host; + } + public static void setDBMS(String dbms) { + JDBC.dbms = dbms; + if(dbms == "mysql") + { + JDBC.strClassName = "com.mysql.cj.jdbc.Driver"; + JDBC.port = "3306"; + } + else if(dbms == "mariadb") + { + JDBC.strClassName = "org.mariadb.jdbc.Driver"; + JDBC.port = "3306"; + } + } + private static void setStrUrl() { + JDBC.strUrl = "jdbc:" + dbms + "://" + host + ":" + port + "/" + dbname; + } + public static void setDbName(String dbName) { + JDBC.dbname = dbName; + } + public static void setLogin(String login) { + JDBC.login = login; + } + public static void setMotdepasse(String motdepasse) { + JDBC.password = motdepasse; + } + public static void setInfos(String host, String dbms, String dbname, String login, String password) + { + JDBC.dbname = dbname; + JDBC.login = login; + JDBC.password = password; + JDBC.host = host; + setDBMS(dbms); + JDBC.setStrUrl(); + } + +// public void close() +// { +// try { +// this.conn.close(); // libérer le slot (même principe que l'attaque DDOS) +// this.conn = null; // pour pouvoir réinstancier avec d'autres paramètres +// } +// catch (SQLException e) { +// e.printStackTrace(); +// } +// } +} diff --git a/src/dao/ResultObject.java b/src/dao/ResultObject.java new file mode 100644 index 0000000..e41af0b --- /dev/null +++ b/src/dao/ResultObject.java @@ -0,0 +1,39 @@ +package dao; + +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.HashMap; +import java.util.List; + +public class ResultObject +{ + private final List> data = new ArrayList<>(); + + public ResultObject(ResultSet rs) throws SQLException + { + ResultSetMetaData metaData = rs.getMetaData(); + int columnCount = metaData.getColumnCount(); + + while(rs.next()) + { + HashMap row = new HashMap<>(); + for (int i = 1; i <= columnCount; i++) + { + row.put(metaData.getColumnName(i), rs.getObject(i)); + } + data.add(row); + } + +// rs.close(); +// rs.getStatement().close(); +// rs.getStatement().getConnection().close(); + + } + + public List> getData() + { + return data; + } +} diff --git a/src/dao/SQLexecutor.java b/src/dao/SQLexecutor.java new file mode 100644 index 0000000..8f7f130 --- /dev/null +++ b/src/dao/SQLexecutor.java @@ -0,0 +1,80 @@ +package dao; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; + +public class SQLexecutor extends JDBC +{ + /** + * Exécute une requête SQL et retourne le résultat si applicable. + * @param sql requête avec des ? + * @param params Object... permet d'ajouter autant de paramètres qu'on veut et ça fait un tableau + * @return Un ResultSet si la requête est un SELECT, sinon null + * @throws SQLException en cas d'erreur SQL + * @throws ClassNotFoundException pour DriverManager.getConnection + */ + public ResultObject executeQuery(String sql, Object... params) throws SQLException, ClassNotFoundException + { + Connection connection = null; + PreparedStatement statement = null; + ResultSet resultSet = null; + + try { + connection = getConnection(); + boolean isSelect = sql.trim().toLowerCase().startsWith("select"); + + if (params.length > 0) + { + int option; + if(isSelect) + { + option = ResultSet.TYPE_SCROLL_INSENSITIVE; + // navigation possible dans le ResultSet: next(), previous(), absolute(); + // ResultSet non modifiable en cas d'accès concurrent en mémoire + } + else + { + option = PreparedStatement.NO_GENERATED_KEYS; + // optimisation en ne retournant pas les clé générés lors d'un INSERT (RETURN_GENERATED_KEYS) + } + statement = connection.prepareStatement(sql, option); + //isSelect ? ResultSet.TYPE_SCROLL_INSENSITIVE : PreparedStatement.NO_GENERATED_KEYS); + this.setParameters(statement, params); + } else { + statement = connection.prepareStatement(sql); + } + + if(isSelect) { + resultSet = statement.executeQuery(); + ResultObject result = new ResultObject(resultSet); + resultSet.close(); + return result; + } else { + statement.executeUpdate(); + return null; + } + } finally { + statement.close(); + connection.close(); +// if (resultSet == null) { +// statement.close(); +// connection.close(); +// } + } + } + + /** + * Affecte les paramètres aux requêtes préparées. + * @param statement + * @param params Object... permet d'ajouter autant de paramètres qu'on veut et ça fait un tableau + * @throws SQLException en cas d'erreur SQL + */ + private void setParameters(PreparedStatement statement, Object... params) throws SQLException + { + for (int i = 0; i < params.length; i++) { + statement.setObject(i + 1, params[i]); + } + } +} -- cgit v1.2.3