analytics

Sunday, February 23, 2014

Working with Data sources

Here in this small example I will show how to look up an Oracle Data source
And get the connection to update the tables in a schema.

Let’s consider we have created the data sources in Weblogic application server.
Here we have created one Member Data Source with JNDI name “MemberDataSource”
This data source contains one schema say ods_membership which contains the Table called MEMBERSHIP_FUNCTIONS.

Now through java we will do JNDI LOOKUP of the data source, once we look up the data source we will create the connection and finally we will update the mentioned table.
(In similar way we can insert or query the database tables) 


package com.purejava4all.datasource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;


public class UpdateTable {

       public static String serviceUrl = null;
       public static Properties props = null;

       Connection con = null;
       PreparedStatement stmt = null;
       public static void setUp(){

              // Host Port information of the Database
              serviceUrl = "t3://10.20.333.70:9006";
              props = new Properties();
              props.put(Context.INITIAL_CONTEXT_FACTORY,
                           "weblogic.jndi.WLInitialContextFactory");
              props.put(Context.PROVIDER_URLserviceUrl);

              System.out.println("setUp:serviceUrl::" + serviceUrl);
              System.out.println("===setUp Done===");

       }

       public static void main(String ags[]){
              setUp();
              Connection con = null;
              PreparedStatement stmt = null;
      
              try {
       // Creating the InitialContext
       Context ctx = new InitialContext(props);
       System.out.println("===got the Context===>>"+ctx);
             
       // Doing JNDI Look Up of the Data Source
       DataSource dataSource = (DataSource) ctx.lookup("jdbc/MemberDataSource");
      
       System.out.println("===got the dataSource===>>"+dataSource);
       // Creating Connection from the Data Source
       con = dataSource.getConnection();
       System.out.println("===got the con===>>"+con);
      
       StringBuffer query = new StringBuffer();
      
       // Building the query
       query.append("update ods_membership. MEMBERSHIP_FUNCTIONS set ACTIVE_STATUS = ‘N’ where MEMBERSHIP_TYPE in ('ROMS','BOLS')");
      
       stmt = con.prepareStatement(query.toString());
       int count  = stmt.executeUpdate();
       System.out.println("===Update Done===>>"+count);
catch (SQLException e) {
       e.printStackTrace();
}catch(Exception ex){
       ex.printStackTrace();
}
finally {
       try{
       con.close();
       stmt.close();
       }catch(Exception e){
              e.printStackTrace();
       }
       }
      
}
      

}

No comments:

Post a Comment