Out of box: Oracle proxy user with ADF Application

I was facing long time with oracle proxy user and ADF application module problem. Therefore, i need to share how i resolved that problem. Main purpose of that proxy connection: We need to audit user action in oracle database side (audit trail).

Problem: I have an implemented Oracle Proxy User connection into Application Module. Same as this (https://blogs.oracle.com/imc/entry/how_to_use_database_proxy). But after i implemented, there is coming one strange problem with application module session. It means, Our application module always replicated by different session and connection.

Solution: First of all, many thanks for Vijai, who is helped me on Oracle Forum.
This is our piece of code for Proxy Connection in the Application Module implementation class:

    @Override
    protected void prepareSession(Session session) {
        String username =
            ADFContext.getCurrent().getSecurityContext().getUserName().toUpperCase();

        Statement st =
            getDBTransaction().createPreparedStatement("rollback", 0);

        try {
            if (st.getConnection() instanceof PoolConnection) {
                PoolConnection poolConnection =
                    (PoolConnection)st.getConnection();
                OracleConnection connection =
                    (OracleConnection)poolConnection.checkConnection();


                if (connection.isProxySession() &&
                    username.equals(connection.getUserName())) {
                    // no proxying required, we already have a connection that is proxied for this user
                    super.prepareSession(session);
                    return;
                }

                clearStatementCache(poolConnection);

                if (connection.isProxySession()) {
                    connection.close(OracleConnection.PROXY_SESSION);
                }

                Properties properties = new Properties();
                properties.put(OracleConnection.PROXY_USER_NAME, username);
                connection.openProxySession(OracleConnection.PROXYTYPE_USER_NAME,
                                            properties);

                clearStatementCache(poolConnection);

            } else {
                throw new JboException("Not Pool Connection");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        super.prepareSession(session);
    }


    /**
     * Clear out any cached statements
     */
    @Override
    protected void beforeDisconnect() {
        Statement st =
            getDBTransaction().createPreparedStatement("rollback", 0);
        try {
            PoolConnection poolConnection = (PoolConnection)st.getConnection();

            clearStatementCache(poolConnection);

            poolConnection.close(OracleConnection.PROXY_SESSION);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        super.beforeDisconnect();
    }


    private void clearStatementCache(PoolConnection poolConnection) throws SQLException {
        poolConnection.clearStatementCache();

        OracleConnection connection =
            (OracleConnection)poolConnection.checkConnection();

        if (connection.getExplicitCachingEnabled()) {
            connection.purgeExplicitCache();
        }
        if (connection.getImplicitCachingEnabled()) {
            connection.purgeImplicitCache();
        }
    }

And if you want use one connection, you can make all AM to child connection of that AM.

Tips:
1. Before i could not import weblogic.jdbc.wrapper.PoolConnection class. Problem is my model project hasn't any technology scope. It means, We have to select ADF Business Component in technology scope of project properties.

2. Also we was getting different error from weblogic side. (Error: java.lang.ClassCastException: weblogic.jdbc.wrapper.JTAConnection_weblogic_jdbc_wrapper_XAConnection_oracle_jdbc_driver_LogicalConnection cannot be cast to weblogic.jdbc.wrapper.PoolConnection). After i changed XA datasource to NON-XA connection, it was work. In our case, it must be (oracle.jdbc.OracleDriver).

3. Additional tip: Regarding to http://javaosdev.blogspot.com/2011/01/adf-closed-statement-again.html that post. Because i get following error "Caused By: java.sql.SQLRecoverableException: Closed Statement". In this case, We have to turn off statement cache from connection pool. (Give parameter Statement Cache Size : 0).



If i found additional tips from real world problem, i will update it.

Thank you,
Erdenebayar

Linkedin: http://www.linkedin.com/in/erdenebayare
Twitter: http://twitter.com/#!/erdenebayare

Java Mail Client with GMAIL SMTP and PURE SMTP

This post shows you small example of java mail client with gmail smtp and pure smtp server.

 package ia.demo.utils;  
 import ia.demo.exception.fault.EmptyRecipientException;  
 import java.util.Properties;  
 import javax.mail.AuthenticationFailedException;  
 import javax.mail.Authenticator;  
 import javax.mail.Message;  
 import javax.mail.MessagingException;  
 import javax.mail.PasswordAuthentication;  
 import javax.mail.Session;  
 import javax.mail.Transport;  
 import javax.mail.internet.InternetAddress;  
 import javax.mail.internet.MimeMessage;  
 /**  
  * Mail sending class  
  *  
  * @author Erdenebayar  
  */  
 public class MailUtil {  
  /* GMAIL SMTP CONFIGURATION */  
  static String GMAIL_HOST = "smtp.gmail.com";  
  static String GMAIL_FROM = "yourmail@gmail.com";  
  static String GMAIL_PASS = "yourpass";  
  static String GMAIL_PORT = "587";  
  
/* PURE SMTP CONFIGURATION */  
  static final String SMTP_HOST_NAME = "mail.server.com";  
  static final String SMTP_AUTH_USER = "yourmail@server.com";  
  static final String SMTP_FROM_ADDRESS = "yourmail@server.com";  
  static final String SMTP_AUTH_PWD = "yourpass";  
 
public static void main(String[] aaa) throws MessagingException {  
   String emailMsgTxt = "Message from Java";  
   final String emailSubjectTxt = "You are receiving mail from Java system";  
   String[] to = {"recipient1@yahoo.com", "recipient2@outlook.com"};  
   sendMailWithGmail(to, emailMsgTxt, emailSubjectTxt);  
   sendMailWithSmtp(to, emailMsgTxt, emailSubjectTxt);  
  }

  /**  
   * Email configured with GMAIL SMTP server  
   *  
   * @param recipients - List of recipients mail address  
   * @param subjectText - Subject of mail  
   * @param bodyText - Body of mail  
   * @throws MessagingException  
   */
public static void sendMailWithGmail(String[] recipients, String subjectText, String bodyText) throws MessagingException {  
   if (recipients.length > 0) {  
    Properties props = System.getProperties();  
    props.put("mail.smtp.starttls.enable", "true"); // added this line  
    props.put("mail.smtp.host", GMAIL_HOST);  
    props.put("mail.smtp.user", GMAIL_FROM);  
    props.put("mail.smtp.password", GMAIL_PASS);  
    props.put("mail.smtp.port", GMAIL_PORT);  
    props.put("mail.smtp.auth", "true");  
    Session session = Session.getDefaultInstance(props, null);  
    MimeMessage message = new MimeMessage(session);  
    message.setFrom(new InternetAddress(GMAIL_FROM));  
    InternetAddress[] toAddress = new InternetAddress[recipients.length];  
    // To get the array of addresses  
    for (int i = 0; i < recipients.length; i++) { // changed from a while loop  
     toAddress[i] = new InternetAddress(recipients[i]);  
    }  
    System.out.println(Message.RecipientType.TO);  
    for (int i = 0; i < toAddress.length; i++) { // changed from a while loop  
     message.addRecipient(Message.RecipientType.TO, toAddress[i]);  
    }  
    message.setSubject(subjectText);  
    message.setText(bodyText);  
    Transport transport = session.getTransport("smtp");  
    transport.connect(GMAIL_HOST, GMAIL_FROM, GMAIL_PASS);  
    transport.sendMessage(message, message.getAllRecipients());  
    transport.close();  
   } else {  
    throw new EmptyRecipientException("There are no recipient");  
   }  
  }

  /**  
   * Email configured with PURE SMTP server  
   *  
   * @param recipients - List of recipients mail address  
   * @param subjectText - Subject of mail  
   * @param bodyText - Body of mail  
   * @throws MessagingException  
   * @throws AuthenticationFailedException  
   */  
  public static void sendMailWithSmtp(String recipients[], String subjectText, String bodyText) throws MessagingException, AuthenticationFailedException {  
   if (recipients.length > 0) {  
    boolean debug = false;  
    //Set the host smtp address  
    Properties props = new Properties();  
    props.put("mail.smtp.host", SMTP_HOST_NAME);  
    props.put("mail.smtp.auth", "true");  
    Authenticator auth = new SMTPAuthenticator();  
    Session session = Session.getDefaultInstance(props, auth);  
    session.setDebug(debug);  
    // create a message  
    Message msg = new MimeMessage(session);  
    // set the from and to address  
    InternetAddress addressFrom = new InternetAddress(SMTP_FROM_ADDRESS);  
    msg.setFrom(addressFrom);  
    InternetAddress[] addressTo = new InternetAddress[recipients.length];  
    for (int i = 0; i < recipients.length; i++) {  
     addressTo[i] = new InternetAddress(recipients[i]);  
    }  
    msg.setRecipients(Message.RecipientType.TO, addressTo);  
    // Setting the Subject and Content Type  
    msg.setSubject(subjectText);  
    msg.setContent(bodyText, "text/plain");  
    Transport.send(msg);  
   } else {  
    throw new EmptyRecipientException("There are no recipient");  
   }  
  }  
  private static class SMTPAuthenticator extends javax.mail.Authenticator {  
   @Override  
   public PasswordAuthentication getPasswordAuthentication() {  
    String username = SMTP_AUTH_USER;  
    String password = SMTP_AUTH_PWD;  
    return new PasswordAuthentication(username, password);  
   }  
  }  
 }  

Thank you,
Erdenebayar

Linkedin: http://www.linkedin.com/in/erdenebayare
Twitter: http://twitter.com/#!/erdenebayare