2 Replies Latest reply on Oct 19, 2009 10:44 PM by blabno

    How to get connection in java class from datasource to execute sql

    Val Sw Expert
      Hi,

      I need to do some validation from database table using regular expression in query, which I cannot do using entity query directly, can it be done using entity query directly ?
      example : select upper(regexp_replace(ITEM_NAME, '\\W', '')) from ITEM_TABLE

      I have a class which works fine with-in seam, but I need suggestion on how to get connection details from persistence/xxx-ds.xml instead of hardcoding as in code below.

      Please suggest if there is any better way around

      Code:
      -----
      public class ValidateUtil {
           Statement st = null;
           public ValidateUtil() throws Exception {
                Class.forName("oracle.jdbc.OracleDriver");
                System.out.println("Driver Loaded.");
                String url = "jdbc:oracle:thin:@<host>:1521/<SID>";

                Connection conn = DriverManager.getConnection(url, "<user>", "<pwd>");
                System.out.println("Got Connection.");
                st = conn.createStatement();
           }

           public void executeSQLCommand(String sql) throws Exception {
                st.executeUpdate(sql);
           }
           
           public boolean checkData(String sql, String strCompare) throws Exception {
                
                ResultSet rs = st.executeQuery(sql);
                ResultSetMetaData metadata = rs.getMetaData();
                
                boolean boolDup = false;
                while (rs.next()) {
                     for (int i = 0; i < metadata.getColumnCount(); i++) {
                          Object value = rs.getObject(i + 1);     
                          ......
                               if (value.toString().trim().equalsIgnoreCase(strCompare)) {
                                    boolDup = true;
                               }
                     ....
                }     
                return (boolDup);
           }
      }