Java/Database SQL JDBC/OracleDataSource

Get connection from DataSource

 
import javax.sql.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;
public class DataSource {
  public static void main(String[] args){
    try{
      OracleDataSource ods = new OracleDataSource();
      ods.setUser("yourName");
      ods.setPassword("mypwd");
      ods.setDriverType("thin");
      ods.setDatabaseName("ORCL");
      ods.setServerName("localhost");
      ods.setPortNumber(1521);
      Connection conn = ods.getConnection();
    }catch(SQLException se){
      se.printStackTrace();
    }
  }
}

Jndi DataSource

 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import oracle.jdbc.pool.OracleDataSource;
public class JndiDataSource {
  static Connection conn = null;
  static Statement stmt = null;
  static ResultSet rs = null;
  static Context ctx = null;
  static DataSource ds = null;
  public static void main(String args[]) throws Exception {
    String sp = "com.sun.jndi.fscontext.RefFSContextFactory";
    String file = "file:/e:/JNDI";
    String dataSourceName = "jdbc/myDatabase";
    Hashtable env = new Hashtable();
    env.put(Context.INITIAL_CONTEXT_FACTORY, sp);
    env.put(Context.PROVIDER_URL, file);
    ctx = new InitialContext(env);
    bindDataSource(ctx, dataSourceName);
    DataSource ds = null;
    ds = (DataSource) ctx.lookup(dataSourceName);
    Connection conn = ds.getConnection();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT Name FROM Employees");
    while (rs.next())
      System.out.println(rs.getString("name"));
    ctx.close();
    rs.close();
    stmt.close();
    conn.close();
    ctx.close();
    conn.close();
  }
  public static void bindDataSource(Context ctx, String dsn) throws SQLException, NamingException {
    OracleDataSource ods = new OracleDataSource();
    ods.setUser("yourName");
    ods.setPassword("mypwd");
    ods.setDriverType("thin");
    ods.setDatabaseName("ORCL");
    ods.setServerName("localhost");
    ods.setPortNumber(1521);
    ctx.rebind(dsn, ods);
  }
}

Oracle Connection Pool DataSource

 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.sql.PooledConnection;
import oracle.jdbc.pool.OracleConnectionPoolDataSource;
public class ConnPool {
  public static void main(String[] args) throws Exception {
    OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();
    ocpds.setURL("jdbc:oracle:thin:@localhost:1521:ORCL");
    ocpds.setUser("user");
    ocpds.setPassword("password");
    PooledConnection pc_1 = ocpds.getPooledConnection();
    Connection conn_1 = pc_1.getConnection();
    Statement stmt = conn_1.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT count(*) FROM v$session WHERE username = "SYS"");
    rs.next();
    String msg = "Total connections after ";
    System.out.println(msg + "conn_1: " + rs.getString(1));
    Connection conn_2 = pc_1.getConnection();
    stmt = conn_2.createStatement();
    rs = stmt.executeQuery("SELECT count(*) FROM v$session WHERE username = "SYS"");
    rs.next();
    System.out.println(msg + "conn_2: " + rs.getString(1));
    PooledConnection pc_2 = ocpds.getPooledConnection();
    rs = stmt.executeQuery("SELECT count(*) FROM v$session WHERE username = "SYS"");
    rs.next();
    System.out.println(msg + "pc_2: " + rs.getString(1));
    conn_1.close();
    conn_2.close();
    pc_1.close();
    pc_2.close();
  }
}