JNDI Data Source / Prepared Statement

JNDI Data Source / Prepared Statement - Java - Programmation

Marsh Posté le 27-10-2011 à 16:06:36    

Bonjour,
 
Pouvez vous m'aider à executer un Query à l'aide PreparedStatement sachant que ma connexion passe par une DataSource JNDI.
 
Context.xml
 

Code :
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <Context>
  3.     <!-- Specify a JDBC datasource -->
  4.    
  5. <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
  6.                maxActive="100" maxIdle="30" maxWait="10000"
  7.                username="root" password="admin" driverClassName="com.mysql.jdbc.Driver"
  8.                url="jdbc:mysql://localhost:3306/myfirstdb"/>
  10. </Context>


 
 
 
 
 
 
La Classe ConnexionBDD qui Fonctionne tres bien
 

Code :
  1. package interactiondb;
  3. import java.sql.Connection;
  4. import java.sql.SQLException;
  5. import java.sql.DriverManager;
  6. import java.sql.SQLException;
  7. import java.util.logging.Level;
  8. import java.util.logging.Logger;
  10. import javax.sql.DataSource;
  11. import javax.naming.Context;
  12. import javax.naming.InitialContext;
  13. import javax.naming.NamingException;
  15. import com.mysql.jdbc.PreparedStatement;
  20. public class ConnexionBDD
  21. {
  23. private static Connection connexion;
  24. static DataSource dataSource=null;
  25. public Context ctx;
  27. public ConnexionBDD()
  28. {
  30.     
  31.  this.connexion = null;
  33.  
  34.  try
  35.  {
  38.   ctx = new InitialContext();
  39.   dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/TestDB" );
  43.  }
  44.  catch (NamingException e)
  45.  {
  46.  // TODO Auto-generated catch block
  47.  e.printStackTrace();
  48.  }
  51. }
  54. public Connection getConnexion()
  55. {
  57.  try
  58.  {
  60.   connexion= dataSource.getConnection();
  61.  }
  62.  catch (SQLException e)
  63.  {
  64.   // TODO Auto-generated catch block
  65.   e.printStackTrace();
  66.  }
  68.  return this.connexion;
  69. }
  72. }


 
 
 
 
 
Et enfin la classe CRUD
 

Code :
  1. package interactiondb;
  3. import java.sql.ResultSet;
  4. import java.sql.SQLException;
  6. import com.mysql.jdbc.Connection;
  7. import com.mysql.jdbc.PreparedStatement;
  8. import java.util.logging.Logger;
  9. import java.util.logging.Level;
  12. import model.Employees;
  13. //import model.Member;
  14. import interactiondb.ConnexionBDD;
  16. public class CRUD
  18. {
  20. ConnexionBDD connect = new ConnexionBDD();
  21. Connection con=null;
  22. PreparedStatement pstmt;
  25. public CRUD ()
  26. {
  28.  System.out.println("Etape 1" );
  30.  con = (Connection) connect.getConnexion();
  32.  System.out.println("Etape 2" );
  35. }
  37. // A Verifier
  39. public int login(String login, String password)
  40. {
  42.  int count=0;
  44.  try
  45.  {
  47.   System.out.println("Etape 3" );
  49.   pstmt=(PreparedStatement) con.prepareStatement("select * from member"
  50.     + " where login='" + login  + "' and password='" + password  + "'" );
  52.   System.out.println("Etape 4" );
  55.   ResultSet result = pstmt.executeQuery();
  57.   System.out.println("Etape 5" );
  60.   // Exploitation des résultats
  61.   while (result.next())
  62.   {
  63.    count++;
  64.   }
  65.  }
  66.  catch (Exception e)
  67.  {
  69.   Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, e);
  70.  }
  72.  try
  73.  {
  74.   pstmt.close();
  75.  }
  76.  catch (SQLException e)
  77.  {
  78.   Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, e);
  79.  }
  80.  return count;
  83. }
  85. /////////////////////////////
  88. public Employees edit(int matricola)
  89. {
  91.  Employees listemployees = null;
  93.  try
  94.  {
  95.   pstmt=(PreparedStatement) con.prepareStatement("select * from employees"
  96.     + " where matricola='" + matricola  + "'" );
  97.   ResultSet result = pstmt.executeQuery();
  99.   // Exploitation des résultats
  100.   while (result.next())
  101.   {
  102.    listemployees = new Employees(result.getInt("matricola" ),result.getString("dataas" ),result.getString("nome" ),result.getString("cognome" ),result.getString("datafa" ));                                            //////////////////////
  103.   }
  104.  }
  105.  catch (Exception e)
  106.  {
  108.   Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, e);
  109.  }
  111.  try
  112.  {
  113.   pstmt.close();
  114.  }
  115.  catch (SQLException e)
  116.  {
  117.   Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, e);
  118.  }
  119.  return listemployees;
  122. }
  128. public int delete(int matricola) throws SQLException
  129. {
  130.  int resultat=0;
  132.  try
  133.  {
  135.   pstmt=(PreparedStatement) con.prepareStatement("delete from 'employees' where 'matricola'="+ matricola);
  136.   resultat = pstmt.executeUpdate();
  139.  }
  140.  catch (Exception e)
  141.  {
  143.   Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, e);
  144.  }
  146.  try
  147.  {
  148.   pstmt.close();
  149.  }
  150.  catch (SQLException e)
  151.  {
  152.   Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, e);
  153.  }
  155.  return resultat;
  156. }
  159. public int update(Employees employee) throws SQLException
  160. {
  162.  int resultat=0;
  164.  Employees emp = new Employees(employee.getmatricola(),employee.getdataas(),employee.getnome(),employee.getcognome(),employee.getdatafa());
  165.  try
  166.  {
  167.   pstmt=(PreparedStatement) con.prepareStatement("UPDATE employees SET dataas = '" + emp.getdataas() + "', nome = '" + emp.getnome() + "', cognome = '" + emp.getcognome() + "', datafa = '" + emp.getdatafa() + "' WHERE matricola = '" + emp.getmatricola() + "'" );
  168.   resultat = pstmt.executeUpdate();
  171.  }
  172.  catch (Exception e)
  173.  {
  175.   Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, e);
  176.  }
  180.  try
  181.  {
  182.   pstmt.close();
  183.  }
  184.  catch (SQLException e)
  185.  {
  186.   Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, e);
  187.  }
  189.  return resultat;
  191. }
  193. public int insert(Employees p) throws SQLException
  194. {
  196.  int resultat=0;
  198.  try
  199.  {
  200.   pstmt=(PreparedStatement) con.prepareStatement("INSERT INTO employees(matricola,dataas,nome,cognome,datafa) " +
  201.     "VALUES (" + p.getmatricola() + ", '" + p.getdataas() + "', '" + p.getnome() + "', '" + p.getcognome() + "','" + p.getdatafa() + "')" );
  202.   resultat = pstmt.executeUpdate();
  205.  }
  206.  catch (Exception e)
  207.  {
  209.   Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, e);
  210.  }
  212.  try
  213.  {
  214.   pstmt.close();
  215.  }
  216.  catch (SQLException e)
  217.  {
  218.   Logger.getLogger(CRUD.class.getName()).log(Level.SEVERE, null, e);
  219.  }
  221.  return resultat;
  223. }
  226. }



---------------
Manou1980
Reply

Marsh Posté le 27-10-2011 à 16:06:36   

Reply

Marsh Posté le 31-10-2011 à 13:57:35    

Le problème est resolu!!! Il fallait remplacer les import suivant:
 
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
 
par les import
 
import java.sql.Connection;
import java.sql.PreparedStatement;

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed