Tuesday, July 17, 2012

How to access Weblogic Server Datasource from jsp

Recently i was working on a project and I faced some security issues regarding the connection string. I was using the DriverManager.getConnection method>

Connection con=DriverManager.getConnection("jdbc:oracle:thin:tom/tom@l91c214gx:1521:orcl");
Later I searched and found a way to handle that. I accessed the Weblogic Server Datasource. Pulled the connection information from the datasource and made a connection to the database. :)


You can find the code below. Change the jdbc/wb_datasource to your datasource name. Always prefix JNDI Name with jdbc like jdbc/wb_datasource
eg: pool = (DataSource) env.lookup ("jdbc/xxxx");



<%@ page import="java.sql.*" %>
<%@ page import="java.util.Hashtable"%>
<%@ page import="javax.naming.*"%>
<%@ page import="javax.sql.DataSource"%>
<%@ page import="java.util.*"%>


<%try{

 Context env = null;
    DataSource pool = null;
    Hashtable ht = new Hashtable( );

    ht.put(Context.INITIAL_CONTEXT_FACTORY,"weblogic.jndi.WLInitialContextFactory");
    ht.put(Context.PROVIDER_URL,"t3://localhost:7001");
    
    env = new InitialContext(ht);

    //Lookup this DataSouce at the top level of the WebLogic JNDI tree
    pool = (DataSource) env.lookup ("jdbc/wb_datasource"); 

 //get a java.sql.Connection from the pool
    Connection conn = pool.getConnection( );

 // change the sql_retrive string according to your need
 String sql_retrive = "select * from test" ;



 Statement st1=conn.createStatement();
 st1.execute(sql_retrive);

 ResultSet rs=st1.getResultSet();
 
 ResultSetMetaData rsm = rs.getMetaData( );
 
 int colCount =  rsm.getColumnCount( );

 %> 
 <table><tr>
 <%
  //print column names
        for (int i = 1; i <=colCount; ++i) { %>
            <th><%=rsm.getColumnName(i)%> </th>
 <%  } %>
           </tr>
          <% while( rs.next( )){ %>
                <tr>
    <%for (int i = 1;  i <=colCount; ++i) { %>
                <td>    <%= rs.getString(i) %> </td>
    <%} //for loop ended%>  
                </tr>
          <%} //while loop ended%>
 </table><% 
}
conn.close();
st1.close();
}
catch(Exception ex){

out.println(ex);

}
%>

(Read more inside ..)