Apply Dynamic JDBC in ADF Application (Step-by-Step)

In some situations the business require that ADF application can connect to different database users depending on the login user. For example if user1 login, the ADF application will connect to database user hr/hr. If user2 login, the ADF application will connect to database user hr2/hr2.
This is call Dynamic JDBC.

In this url you can see how to apply this dynamic JDBC, but in my post I will simplify this url by creating a sample ADF application and go step by step for how to apply dynamic JDBC to this ADF application.

I use JDeveloper 11.1.2.4.0.

Note: From Step1 to Step5 it just steps for creating a simple ADF application. Applying the Dynamic JDBC to this simple application is starting from Step6.

Step1:
Create a simple ADF Application connect to "hr" database, this application will contains only 1 ViewObject (Employees) and will contains 2 jspx pages (login.jspx and emp.jspx)

Step2:
From faces-config.xml --> Navigation Rules define outcome to the two jspx pages.
login.jspx---> login
emp.jspx----> emp

Step3:

- In the login.jspx page add two inputTexts (Username and Password) and one command button for login.

- Bind Username inputText and Password inputText to the back bean.

- Create action for the login command button to read the username and password then navigate to emp.jspx page.

	<af:document title="login.jspx" id="d1">
	<af:form id="f1" defaultCommand="cb1">
	<af:panelFormLayout id="pfl1">
	<af:inputText label="Username" id="it1" autoSubmit="true" binding="#{login.usernameComponent}"/>
	<af:inputText label="Password" id="it2" autoSubmit="true" binding="#{login.passwordComponent}"/>
	<f:facet name="footer">
	<af:commandButton text="Login" id="cb1" action="#{login.loginAction}"/>
	</f:facet>
	</af:panelFormLayout>
	</af:form>
	</af:document>

view raw login.xml hosted with ❤ by GitHub

Step4:

In the emp.jspx drag EmployeesView1 to the page and add logout link to the page 

The back bean should be:

	public class LoginBean
	{
	private RichInputText usernameComponent;
	private RichInputText passwordComponent;
	public LoginBean()
	{
	}
	public void setUsernameComponent(RichInputText usernameComponent)
	{
	this.usernameComponent = usernameComponent;
	}
	public RichInputText getUsernameComponent()
	{
	return usernameComponent;
	}
	public void setPasswordComponent(RichInputText passwordComponent)
	{
	this.passwordComponent = passwordComponent;
	}
	public RichInputText getPasswordComponent()
	{
	return passwordComponent;
	}
	public String loginAction()
	{
	String username = (String) getUsernameComponent().getValue();
	String password = (String) getPasswordComponent().getValue();
	System.out.println("Username= " + username + " - Password= " + password);
	return "emp";//
	}
	public String logoutAction()
	{
	try
	{
	FacesContext context = FacesContext.getCurrentInstance();
	HttpSession session = (HttpSession)context.getExternalContext().getSession(true);
	if ( session != null )
	{
	session.invalidate();
	}
	}
	catch (Exception e)
	{
	System.out.println("Session Invalidated...");
	}
	return "login";
	}
	}

view raw login.java hosted with ❤ by GitHub

Step5:
Run login.jspx page.

Now we have created a simple ADF application and we will now apply dynamic jdbc to this application.

Step6:
Open AppModule and set "Connection Type" to JDBC URL for both AppModuleShared and AppModuleLocal

Step7:
In the login button action we will store username/password it in session.
The login button action will be:

	public String loginAction()
	{
	String username = (String) getUsernameComponent().getValue();
	String password = (String) getPasswordComponent().getValue();
	System.out.println("Username= " + username + " - Password= " + password);
	FacesContext ctx = FacesContext.getCurrentInstance();
	HttpSession session = (HttpSession) ctx.getExternalContext().getSession(true);
	session.setAttribute("dbUser", username);
	session.setAttribute("dbPassword", password);
	return "emp";// outcome to emp.jspx page
	}

view raw loginAtion.java hosted with ❤ by GitHub

Step8:
Create new Filter (DynamicJDBCBindingFilter.java) extends ADFBindingFilter.

	import java.io.IOException;
	import javax.servlet.FilterChain;
	import javax.servlet.ServletException;
	import javax.servlet.ServletRequest;
	import javax.servlet.ServletResponse;
	import javax.servlet.http.HttpServletRequest;
	import javax.servlet.http.HttpSession;
	import oracle.adf.model.servlet.ADFBindingFilter;
	import oracle.jbo.JboException;
	import oracle.jbo.client.Configuration;
	public class DynamicJDBCBindingFilter extends ADFBindingFilter
	{
	public DynamicJDBCBindingFilter()
	{
	}
	public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException
	{
	HttpSession session = ((HttpServletRequest) request).getSession(false);
	if (session != null && session.getAttribute("loggedin") == null)
	{
	String usrName = (String) session.getAttribute("dbUser");
	String pswd = (String) session.getAttribute("dbPassword");
	if (usrName != null && session.getAttribute("loggedin") == null)
	{
	try
	{
	if (usrName == null || usrName.length() == 0 || pswd == null || pswd.length() == 0)
	{
	throw new JboException("Blank User name or Password");
	}
	session.setAttribute(Configuration.DB_USERNAME_PROPERTY, usrName);
	session.setAttribute(Configuration.DB_PASSWORD_PROPERTY, pswd);
	super.doFilter(request, response, chain);
	session.setAttribute("loggedin", "loggedin");
	}
	catch (oracle.jbo.JboException e)
	{
	try
	{
	session.setAttribute("loggedin", null);
	session.invalidate();
	}
	catch (Exception e2)
	{
	}
	request.getRequestDispatcher("/faces/login.jspx").forward(request, response);
	}
	}
	else
	{
	super.doFilter(request, response, chain);
	}
	}
	else
	{
	super.doFilter(request, response, chain);
	}
	}
	}

view raw DynamicJDBCBindingFilter.java hosted with ❤ by GitHub

Step9:
Create Class (DynamicJDBCEnvInfoProvider) as

	import java.util.Hashtable;
	import oracle.jbo.common.ampool.EnvInfoProvider;
	import oracle.jbo.client.Configuration;
	public class DynamicJDBCEnvInfoProvider implements EnvInfoProvider
	{
	private final String mJDBCUserName;
	private final String mJDBCPassword;
	public DynamicJDBCEnvInfoProvider(String jdbcUserName, String jdbcPassword)
	{
	mJDBCUserName = jdbcUserName;
	mJDBCPassword = jdbcPassword;
	}
	public Object getInfo(String info, Object connEnvironment)
	{
	if(mJDBCUserName != null)
	{
	((Hashtable)connEnvironment).put(Configuration.DB_USERNAME_PROPERTY, mJDBCUserName);
	}
	if(mJDBCPassword != null)
	{
	((Hashtable)connEnvironment).put(Configuration.DB_PASSWORD_PROPERTY, mJDBCPassword);
	}
	return null;
	}
	public void modifyInitialContext(Object p0)
	{
	}
	public int getNumOfRetries()
	{
	return 0;
	}
	}

view raw DynamicJDBCEnvInfoProvider.java hosted with ❤ by GitHub

Step10:
Create Class (DynamicJDBCHttpSessionCookieImpl) as

	import oracle.jbo.common.ampool.ApplicationPool;
	import oracle.jbo.http.HttpSessionCookieImpl;
	import oracle.jbo.common.ampool.EnvInfoProvider;
	public class DynamicJDBCHttpSessionCookieImpl extends HttpSessionCookieImpl
	{
	public DynamicJDBCHttpSessionCookieImpl(java.lang.String applicationId,
	java.lang.String sessionId, ApplicationPool pool)
	{
	super(applicationId, sessionId, pool);
	}
	public DynamicJDBCHttpSessionCookieImpl(java.lang.String applicationId,
	java.lang.String sessionId, ApplicationPool pool, java.security.Principal userPrincipal,
	javax.servlet.http.HttpServletRequest request)
	{
	super(applicationId, sessionId, pool, userPrincipal, request);
	}
	public DynamicJDBCHttpSessionCookieImpl(java.lang.String applicationId,
	java.lang.String sessionId, ApplicationPool pool, java.security.Principal userPrincipal,
	javax.servlet.http.HttpSession session)
	{
	super(applicationId, sessionId, pool, userPrincipal, session);
	}
	public void setEnvInfoProvider(EnvInfoProvider envInfoProvider)
	{
	if (envInfoProvider != null)
	{
	super.setEnvInfoProvider(envInfoProvider);
	}
	}
	}

view raw DynamicJDBCHttpSessionCookieImpl.java hosted with ❤ by GitHub

Step11: 
Create Class (DynamicJDBCSessionCookieFactory) as

	import oracle.jbo.common.ampool.SessionCookie;
	import oracle.jbo.common.ampool.ApplicationPool;
	import oracle.jbo.common.ampool.EnvInfoProvider;
	import java.util.Properties;
	import java.util.Hashtable;
	import oracle.jbo.http.HttpSessionCookieFactory;
	import oracle.jbo.client.Configuration;
	import javax.servlet.http.HttpSession;
	public class DynamicJDBCSessionCookieFactory extends HttpSessionCookieFactory
	{
	public SessionCookie createSessionCookie(String name, String value, ApplicationPool pool, Properties properties)
	{
	SessionCookie cookie = super.createSessionCookie(name, value, pool, properties);
	if (properties != null)
	{
	HttpSession session = (HttpSession) properties.get(HTTP_SESSION);
	if (session != null)
	{
	Hashtable env = pool.getEnvironment();
	env.remove(Configuration.DB_USERNAME_PROPERTY);
	env.remove(Configuration.DB_PASSWORD_PROPERTY);
	pool.setUserName(null);
	pool.setPassword(null);
	EnvInfoProvider provider = new DynamicJDBCEnvInfoProvider(
	(String) session.getAttribute(Configuration.DB_USERNAME_PROPERTY),
	(String) session.getAttribute(Configuration.DB_PASSWORD_PROPERTY));
	cookie.setEnvInfoProvider(provider);
	}
	}
	return cookie;
	}
	public Class getSessionCookieClass()
	{
	try
	{
	return Class.forName("view.DynamicJDBCHttpSessionCookieImpl");// path to DynamicJDBCHttpSessionCookieImpl class
	}
	catch (ClassNotFoundException cnfe)
	{
	cnfe.printStackTrace();
	return null;
	}
	}
	}

view raw DynamicJDBCSessionCookieFactory.java hosted with ❤ by GitHub

Step12:
In web.xml update adfBindings and make it point to DynamicJDBCBindingFilter

	<filter>
	<filter-name>adfBindings</filter-name>
	<filter-class>view.DynamicJDBCBindingFilter</filter-class>
	</filter>

view raw web.xml hosted with ❤ by GitHub

then add filter mapping to adfBindings filter

	<filter-mapping>
	<filter-name>adfBindings</filter-name>
	<url-pattern>*.jsp</url-pattern>
	<dispatcher>FORWARD</dispatcher>
	<dispatcher>REQUEST</dispatcher>
	<dispatcher>INCLUDE</dispatcher>
	</filter-mapping>
	<filter-mapping>
	<filter-name>adfBindings</filter-name>
	<url-pattern>*.jspx</url-pattern>
	<dispatcher>FORWARD</dispatcher>
	<dispatcher>REQUEST</dispatcher>
	<dispatcher>INCLUDE</dispatcher>
	</filter-mapping>
	<filter-mapping>
	<filter-name>adfBindings</filter-name>
	<url-pattern>*.jsf</url-pattern>
	<dispatcher>FORWARD</dispatcher>
	<dispatcher>REQUEST</dispatcher>
	<dispatcher>INCLUDE</dispatcher>
	</filter-mapping>

view raw web.xml hosted with ❤ by GitHub

Step13:
In AppModule update jbo.ampool.sessioncookiefactoryclass in AppModuleLocal and AppModuleShare to point to DynamicJDBCSessionCookieFactory

Step14:

Now run login.jspx and login with database username/password = hr/hr you will successfully login and  get the data

Logout then login again with wrong password as hr/hr1 you will get "Invalid username/password".

You can download the sample application from here

Get JDeveloper and ADF Icons and Styles

Some developers want to get some icons which used in ADF components or JDeveloper. Examples of these icons (edit icon, LOV icon, undo icon,...) or get some css files which used for styling.

You can find these icons and css files on the jar file adf-richclient-impl-11.jar

You can find this jar in the path like:

C:\Oracle\Middleware\oracle_common\modules\oracle.adf.view_11.1.1\adf-richclient-impl-11.jar

You can extract this jar file then open "adf" folder and you will find 2 folders:

1-  "images" this folder will store icons.
2- "styles" this folder will store css files.

ADF Table Filter Case Insensitive and Search by Contains Instead of Start With

In ADF table if you enable column filter this filter by default will search by "start with" and it will be case sensitive . For example in table employees if you have employee name = Hermann and you try to search by "man", the search will return no result

but if you search by "Herm" it will return result

If you try to search by "herm" it will return no result.

This is because the filter by default is case sensitive and will search by start with.

In many cases we need the all tables filters be case insensitive and search by contains not start with. You can search by case insensitive by setting filterFeatures="caseInsensitive" for  each af:column but you need to do this in all af:column in your application and it will not be a proper solution.

You can search with contains by typing your string between '%', but we need something to be more easy than typing %.

To make the the ADF table filter be case insensitive and search by contains follow these steps:

1- In model project create new class "CustomViewImpl" this class should extend oracle.jbo.server.ViewObjectImpl

2- In this class override getCriteriaItemClause(ViewCriteriaItem viewCriteriaItem) and write this code:

	public String getCriteriaItemClause(ViewCriteriaItem viewCriteriaItem)
	{
	if (viewCriteriaItem.getAttributeDef().getJavaType().getName().equals("java.lang.String"))
	{
	viewCriteriaItem.setValue("%" + viewCriteriaItem.getValue() + "%");
	}
	viewCriteriaItem.setUpperColumns(true);
	return super.getCriteriaItemClause(viewCriteriaItem);
	}

view raw CustomViewImpl.java hosted with ❤ by GitHub

3- Go to Model --> Project Properties --> ADF Business Components --> Base Classes --> View Object --> change Object to point to your CustomViewImpl class

4- Open ViewObject --> Java --> Java Classes --> Generate View Object Class

5- If you open the generated ViewImpl class you will find it extend the CustomViewImpl class

6- Run the application and test.

You can download the sample application from here

(This sample developed in JDev 11.1.2.4.0)