Search This Blog

Friday, 21 August 2015

JDBC Datasource creation on Precise Ubuntu box using Vagrant Oracle XE

Preparation 
  • Download Ubuntu-Vagrant-Oracle-xe
  • Modify the Vagrantfile
The major changes in the vagrangfile are Oracle XE on Vagrant Ubuntu box
  • Memory size 1024MB 
  • Select the hostonly network adapter with a private IP. 
 The vagrant file:

# -*- mode: ruby -*-
# vi: set ft=ruby :

Vagrant.configure("2") do |config|
  config.vm.box = "precise64"
  config.vm.box_url = "http://files.vagrantup.com/precise64.box"
  config.vm.hostname = "oracle.vybhava.com"
  config.vm.synced_folder ".", "/home/vagrant/vagrant-ubuntu-oracle-xe", :mount_options => ["dmode=777","fmode=666"]
  config.vm.network "private_network", ip: "192.168.33.105"
  config.vm.network :forwarded_port, guest: 1521, host: 1521

  config.vm.provider :virtualbox do |vb|
    vb.customize ["modifyvm", :id,
                  "--name", "oracle",
                  # Oracle claims to need 512MB of memory available minimum
                  "--memory", "1024",
                  # Enable DNS behind NAT
                  "--natdnshostresolver1", "on"]
  end

  config.vm.provision :shell, :inline => "echo \"America/New_York\" | sudo tee /etc/timezone && dpkg-reconfigure --frontend noninteractive tzdata"
  config.vbguest.auto_update = false
  ....
... # remain code as it is no changes
end

Configure DataSource on WebLogic administration console

Step-by-Step JDBC Datasource creation process is as follows
  1. login to your WebLogic domain and navigate to the domain structure section select datasource
1. Select Datasource in the work area select New button

1.a Select Generic Data source

2. Enter JDBC Datasource properties : name, JNDI name, Database type

3. Select Database driver

4.XA driver selection nothing to select in the transaction



5. Connection pool parameters

6.a. Connection pool Test confirmation

6.c. Test Configuration Message - Succeeded

7. Target the data source to Cluster for HA

8. Completion of Datasource configuration

Sample Java program to validate the datasource given below:

package vybhava.technologies.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Hashtable;
import javax.naming.Context;
import javax.naming.InitialContext;

/**
 * This example demonstrates obtaining a database connection using a
 * DataSource object.
 * Create a DataSource using any DB name it as myDs and its JNDI as jdbc/myDs
 * To compile:  javac -d . SimpleSql.java
 * Execute : java vybhava.technologies.jdbc.SimpleSql
 */
public class SimpleSql {

  public static void main(String argv[])
      throws Exception {

    java.sql.Connection conn = null;
    java.sql.Statement stmt = null;
    ResultSet rs = null;

    try {

      // ============== Get a database connection ==================

      Context ctx = null;

      // Put connection properties in to a hashtable.
      Hashtable ht = new Hashtable();
      ht.put(Context.INITIAL_CONTEXT_FACTORY,"weblogic.jndi.WLInitialContextFactory");
      ht.put(Context.PROVIDER_URL, "t3://192.168.33.104:7011,192.168.33.104:7012");

      // Get a context for the JNDI lookup
      ctx = new InitialContext(ht);
      // Look up the data source
      javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup("jdbc/myDs1");
      //Get a database connection from the data source
      conn = ds.getConnection();
      conn.setAutoCommit(true);

      System.out.println("Making connection...\n");

      // ============== Execute SQL statements ======================
      stmt = conn.createStatement();

      try {
        stmt.execute("drop table empdemo");
        System.out.println("Table empdemo dropped.");
      } catch (SQLException e) {
        System.out.println("Table empdemo doesn't need to be dropped.");
      }

      stmt.execute("create table empdemo (empid int, name varchar(30), dept int)");
      System.out.println("Table empdemo created.");

      int numrows = stmt.executeUpdate("insert into empdemo values (0, 'John Smith', 12)");
   numrows = stmt.executeUpdate("insert into empdemo values (1, 'Pavan Devarakonda', 12)");
   numrows = stmt.executeUpdate("insert into empdemo values (2, 'Suresh', 13)");
      System.out.println("Number of rows inserted = " + numrows);

      stmt.execute("select * from empdemo");

      rs = stmt.getResultSet();
      System.out.println("Querying data...");
      while (rs.next()) {
        System.out.println("  ID: " + rs.getString("empid") +
            "\n  Name: " + rs.getString("name") +
            "\n  Dept: " + rs.getString("dept"));
      }

      numrows = stmt.executeUpdate("delete from empdemo where empid = 0");
      System.out.println("Number of rows deleted = " + numrows);
    } catch (Exception e) {
      System.out.println("Exception was thrown: " + e.getMessage());
    } finally {
   // ========== Close JDBC objects, including the connection ======= 
      try {
        if (stmt != null) {
          stmt.close();
          stmt = null;
        }
      } catch (SQLException e) {
        throw e;
      } finally {
        try {
          if (conn != null) {
            conn.close();
            conn = null;
          }
        } catch (SQLException e) {
          throw e;
        }
      }
    }
  }
}
 Finally you need to compile the above program and execute that. You can see the commands in the comments.

JDBC Datasource validating  with SimpleSql.java compile and execution

WebLogic Books

  • Oracle WebLogic Server 12c: Administration Handbook
  • WebLogic Diagnostic Framework
  • Advanced WebLogic Server Automation
  • Oracle SOA Suite 11g Administrator's Handbook

Popular Posts