Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package org.biblionum.ouvrage.modele; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.logging.Level; import java.util.logging.Logger; import javax.sql.DataSource; import org.apache.commons.dbutils.BeanProcessor; import org.biblionum.authentification.Utilisateur; import org.biblionum.authentification.modele.UtilisateurModele; /** * * @author Stan */ public class OuvrageModele { private Connection con; private PreparedStatement stm; private ResultSet rs; private String requete; /** * Java method that creates the generated table * * @param con (open java.sql.Connection) * @throws SQLException */ public boolean createTableUtilisateur(DataSource ds) throws SQLException { con = ds.getConnection(); Statement statement = con.createStatement(); String sql = "CREATE TABLE utilisateur(id int AUTO_INCREMENT, " + "`auteur` VARCHAR(255), " + "`editeur` VARCHAR(255), " + "`annee_edition` INT, " + "`resume` VARCHAR(255), " + "`nb_page` INT, " + "`emplacement` VARCHAR(255), " + "`couverture` VARCHAR(255), " + "`ouvrageTipeid` INT, " + "`categorieOuvrageid` INT, " + "`niveauid_niveau` INT, " + "`filiereid` INT, " + "`titre` VARCHAR(255),)"; statement.execute(sql); statement.close(); con.close(); return true; } /** * Java method that inserts a row in the generated sql table and returns the * new generated id * * @param con (open java.sql.Connection) * @param auteur * @param editeur * @param annee_edition * @param resume * @param nb_page * @param emplacement * @param couverture * @param ouvrageTipeid * @param categorieOuvrageid * @param niveauid_niveau * @param filiereid * @param titre * @return id (database row id [id]) * @throws SQLException */ public int insertIntoUtilisateur(DataSource ds, String auteur, String editeur, int annee_edition, String resume, int nb_page, String emplacement, String couverture, int ouvrageTipeid, int categorieOuvrageid, int niveauid_niveau, int filiereid, String titre) throws SQLException { con = ds.getConnection(); int generatedId = -1; String sql = "INSERT INTO ouvrage (auteur, editeur, annee_edition, resume, nb_page, emplacement, " + "couverture, ouvrageTipeid, categorieOuvrageid, niveauid_niveau, filiereid, titre" + ")" + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement statement = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); statement.setString(1, auteur); statement.setString(2, editeur); statement.setInt(3, annee_edition); statement.setString(4, resume); statement.setInt(5, nb_page); statement.setString(6, emplacement); statement.setString(7, couverture); statement.setInt(8, ouvrageTipeid); statement.setInt(9, categorieOuvrageid); statement.setInt(10, niveauid_niveau); statement.setInt(11, filiereid); statement.setString(12, titre); statement.execute(); ResultSet auto = statement.getGeneratedKeys(); if (auto.next()) { generatedId = auto.getInt(1); } else { generatedId = -1; } statement.close(); con.close(); return generatedId; } /** * Java method that updates a row in the generated sql table * * @param con (open java.sql.Connection) * @param auteur * @param editeur * @param annee_edition * @param resume * @param nb_page * @param emplacement * @param couverture * @param ouvrageTipeid * @param categorieOuvrageid * @param niveauid_niveau * @param filiereid * @param titre * @return boolean (true on success) * @throws SQLException */ public boolean updateUtilisateur(DataSource ds, int keyId, String auteur, String editeur, int annee_edition, String resume, int nb_page, String emplacement, String couverture, int ouvrageTipeid, int categorieOuvrageid, int niveauid_niveau, int filiereid, String titre) throws SQLException { con = ds.getConnection(); String sql = "SELECT * FROM ouvrage WHERE id = ?"; PreparedStatement statement = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); statement.setInt(1, keyId); ResultSet entry = statement.executeQuery(); entry.last(); int rows = entry.getRow(); entry.beforeFirst(); if (rows == 0) { entry.close(); statement.close(); con.close(); return false; } entry.next(); if (auteur != null) { entry.updateString("auteur", auteur); } if (editeur != null) { entry.updateString("editeur", editeur); } entry.updateInt("annee_edition", annee_edition); if (resume != null) { entry.updateString("resume", resume); } entry.updateInt("nb_page", nb_page); if (emplacement != null) { entry.updateString("emplacement", emplacement); } if (couverture != null) { entry.updateString("couverture", couverture); } entry.updateInt("ouvrageTipeid", ouvrageTipeid); entry.updateInt("categorieOuvrageid", categorieOuvrageid); entry.updateInt("niveauid_niveau", niveauid_niveau); entry.updateInt("filiereid", filiereid); if (titre != null) { entry.updateString("titre", titre); } entry.updateRow(); entry.close(); statement.close(); con.close(); return true; } /** * Java method that deletes a row from the generated sql table * * @param con (open java.sql.Connection) * @param keyId (the primary key to the row) * @throws SQLException */ public void deleteFromUtilisateur(DataSource ds, int keyId) throws SQLException { con = ds.getConnection(); String sql = "DELETE FROM ouvrage WHERE id = ?"; PreparedStatement statement = con.prepareStatement(sql); statement.setInt(1, keyId); statement.executeUpdate(); statement.close(); con.close(); } /** * get utilisatuer par type et categorie */ public ArrayList<Ouvrage> getOuvrage(DataSource ds, int type, int categorie) { ArrayList<Ouvrage> list = new ArrayList<Ouvrage>(); try { con = ds.getConnection(); stm = con.prepareStatement("SELECT * FROM ouvrage WHERE CategorieOuvrageid=? AND OuvrageTypeid=?"); stm.setInt(1, categorie);//type user prof 1 stm.setInt(2, type); rs = stm.executeQuery(); BeanProcessor bp = new BeanProcessor(); list = (ArrayList) bp.toBeanList(rs, Ouvrage.class); con.close(); rs.close(); } catch (SQLException ex) { Logger.getLogger(UtilisateurModele.class.getName()).log(Level.SEVERE, null, ex); } return list; } }