maven之oracle11gjdbc的连接池、数据源、数据源与jndi简单实例
记录学习的脚步 下面实例代码在oracle的jdbc的例子中 都可以找到 我稍微作了修改 记录学习的脚步
OracleOCIConnectionPool 测试
package com.undergrowth.jdbc.oci;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import oracle.jdbc.oci.OracleOCIConnection;
import oracle.jdbc.pool.OracleOCIConnectionPool;
/**
* 测试oracle的oci协议
* 来源oracle的ojdbc例子
* @author Administrator
*
*/
public class OciTest {
final static Logger LOGGER=LoggerFactory.getLogger(OciTest.class);
public boolean testOci(){
boolean b=true;
OracleOCIConnectionPool cpool=null;
OracleOCIConnection conn1=null,conn2=null;
ResultSet rset =null;
Statement stmt =null;
//常规的写法
/*String url = "jdbc:oracle:oci:@localhost:1521:orcl";*/
//如果客户端和服务器在同一台电脑上 使用ipc 进行通信 如下
/* String url ="jdbc:oracle:oci:@";*/
//使用oracle\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora 中的连接项进行连接 jdbc:oracle:oci8:@ "jdbc:oracle:oci8:@ORCL";
String url ="jdbc:oracle:oci:@ORCL";
try {
String url1 = System.getProperty("JDBC_URL");
if (url1 != null)
url = url1;
// Create an OracleOCIConnectionPool instance with
// default configuration
cpool = new OracleOCIConnectionPool
("u1", "u1", url, null);
// Print out the default configuration for the
// OracleOCIConnectionPool
LOGGER.info
("-- The default configuration for the OracleOCIConnectionPool --");
displayPoolConfig(cpool);
// Get a connection from the pool
conn1 = (OracleOCIConnection)
cpool.getConnection("u1", "u1");
// Create a Statement
stmt = conn1.createStatement ();
// Select the ID, NAME, birthday, age column from the student table
rset = stmt.executeQuery ("SELECT ID, NAME, birthday, age FROM student st WHERE st.id<10");
// Iterate through the result and print the student table
LOGGER.info
("-- Use the connection from the OracleOCIConnectionPool --");
while (rset.next ())
LOGGER.info (rset.getString (1) + " " + rset.getString (2));
LOGGER.info
("-- Use another connection from the OracleOCIConnectionPool --");
// Get another connection from the pool
// with different userID and password
conn2 = (OracleOCIConnection)
cpool.getConnection("u2", "u2");
// Create a Statement
stmt = conn2.createStatement ();
// Select the USER from DUAL to test the connection
rset = stmt.executeQuery ("select to_char(sysdate,'yyyy-mm-dd') from DUAL");
// Iterate through the result and print it out
rset.next ();
LOGGER.info (rset.getString (1));
// Reconfigure the OracleOCIConnectionPool in case the performance
// is too bad. This might happen when many users are trying to connect
// at the same time. In this case, increase MAX_LIMIT to some larger
// number, and also increase INCREMENT to a positive number.
Properties p = new Properties();
p.put (OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT,
Integer.toString(cpool.getMinLimit()));
p.put (OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT,
Integer.toString(cpool.getMaxLimit() * 2)) ;
if (cpool.getConnectionIncrement() > 0)
// Keep the old value
p.put (OracleOCIConnectionPool.CONNPOOL_INCREMENT,
Integer.toString(cpool.getConnectionIncrement()));
else
// Set it to a number larger than 0
p.put (OracleOCIConnectionPool.CONNPOOL_INCREMENT, "1") ;
// Enable the new configuration
cpool.setPoolConfig(p);
// Print out the current configuration for the
// OracleOCIConnectionPool
LOGGER.info
("-- The new configuration for the OracleOCIConnectionPool --");
displayPoolConfig(cpool);
} catch (Exception e) {
// If there is any security exception, ignore it
// and use the default
b=false;
}finally{
// Close the RseultSet
try {
rset.close();
rset = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// Close the Statement
try {
stmt.close();
stmt = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// Close the connections
try {
conn1.close();
conn1 = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
conn2.close();
conn2 = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// Close the OracleOCIConnectionPool
try {
cpool.close();
cpool = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return b;
}
// Display the current status of the OracleOCIConnectionPool
private void displayPoolConfig (OracleOCIConnectionPool cpool)
throws SQLException
{
LOGGER.info (" Min poolsize Limit: " + cpool.getMinLimit());
LOGGER.info (" Max poolsize Limit: " + cpool.getMaxLimit());
LOGGER.info (" Connection Increment: " +
cpool.getConnectionIncrement());
LOGGER.info (" NoWait: " + cpool.getNoWait());
LOGGER.info (" Timeout: " + cpool.getTimeout());
LOGGER.info (" PoolSize: " + cpool.getPoolSize());
LOGGER.info (" ActiveSize: " + cpool.getActiveSize());
}
}
OracleConnectionPoolDataSource测试
package com.undergrowth.jdbc.generic;
/*
* A simple Pooled Connection Sample
* Please compare to PooledConnection2.java
* Please use jdk1.2 or later version
*/
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
class PooledConnection1
{
public static void main (String args [])
throws SQLException
{
// Create a OracleConnectionPoolDataSource instance
OracleConnectionPoolDataSource ocpds =
new OracleConnectionPoolDataSource();
String url = "jdbc:oracle:oci8:@";
try {
String url1 = System.getProperty("JDBC_URL");
if (url1 != null)
url = url1;
} catch (Exception e) {
// If there is any security exception, ignore it
// and use the default
}
// Set connection parameters
ocpds.setURL(url);
ocpds.setUser("u1");
ocpds.setPassword("u1");
// Create a pooled connection
PooledConnection pc = ocpds.getPooledConnection();
// Get a Logical connection
Connection conn = pc.getConnection();
// Create a Statement
Statement stmt = conn.createStatement ();
// Select the NAME columns from the EMPLOYEES table
ResultSet rset = stmt.executeQuery
("select ID, NAME, birthday, age FROM student s where s.id<=100");
// Iterate through the result and print the employee names
while (rset.next ())
System.out.println (rset.getString (1) + " " + rset.getString (2));
// Close the RseultSet
rset.close();
rset = null;
// Close the Statement
stmt.close();
stmt = null;
// Close the logical connection
conn.close();
conn = null;
// Close the pooled connection
pc.close();
pc = null;
}
}
OracleConnectionPoolDataSource同时查看连接的session
package com.undergrowth.jdbc.generic;
/*
* A sample to check how many physical connections
* a pool connection will open. 1 physical connection
* can corresponds to multiple logical connections.
* Only one logical connection get control of the physical
* connection and is valid. As a result, the previous
* logical connection should thrown an exception if used,
* and the latest logical connection is valid.
*
* Please compare to PooledConnection1.java
* Please use jdk1.2 or later version
*/
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import java.util.Properties;
import oracle.jdbc.pool.*;
class PooledConnection2
{
static String url = "jdbc:oracle:oci8:@";
public static void main (String args [])
throws SQLException, java.io.IOException
{
OracleConnectionPoolDataSource ocpds =
new OracleConnectionPoolDataSource();
try {
String url1 = System.getProperty("JDBC_URL");
if (url1 != null)
url = url1;
} catch (Exception e) {
// If there is any security exception, ignore it
// and use the default
}
ocpds.setURL(url);
ocpds.setUser("u1");
ocpds.setPassword("u1");
// Open system
open_sys_conn();
System.out.println("No of Sessions before opening a PooledConnection is " +
count_sessions());
PooledConnection pc = ocpds.getPooledConnection(); // physical connection
System.out.println("No of Sessions before opening first LogicalConnection is " +
count_sessions());
Connection conn1 = pc.getConnection(); // logical connection
System.out.println("No of Sessions before opening Second LogicalConnection is " +
count_sessions());
Connection conn2 = pc.getConnection(); // logical connection
System.out.println("No of Sessions after opening Second LogicalConnection is " +
count_sessions());
// Create a Statement
Statement stmt = conn2.createStatement ();
// Select the NAME columns from the EMPLOYEES table
ResultSet rset = stmt.executeQuery
("select ID, NAME, birthday, age FROM student s where s.id<=10");
// Iterate through the result and print the employee names
while (rset.next ())
System.out.println (rset.getString (1) + " " + rset.getString (2));
// Close the RseultSet
rset.close();
// Close the Statement
stmt.close();
try {
// This should throw an exception as conn1 is invalid
// as control is stolen by conn2
stmt = conn1.createStatement ();
} catch (SQLException se)
{
System.out.println("Exception expected : " + se.getMessage());
}
// Close the connection
conn1.close();
// Close the connection
conn2.close();
// CLose the Pooled Connection
pc.close();
// Close sysconn and sysstmt
close_sys_conn();
}
static Connection sysconn = null;
static Statement sysstmt = null;
private static void open_sys_conn ()
throws SQLException
{
OracleDataSource ods = new OracleDataSource();
ods.setUser("system");
ods.setPassword("sys");
ods.setURL(url);
sysconn = ods.getConnection();
sysstmt = sysconn.createStatement();
ods.close();
ods = null;
}
private static int count_sessions ()
throws SQLException
{
ResultSet sysrs = sysstmt.executeQuery
("select count(*) from V$SESSION");
sysrs.next();
int cnt = sysrs.getInt(1);
sysrs.close();
sysrs = null;
return cnt;
}
private static void close_sys_conn ()
throws SQLException
{
sysstmt.close();
sysstmt = null;
sysconn.close();
sysconn = null;
}
}
OracleDataSource测试
package com.undergrowth.jdbc.generic;
/**
* A Simple DataSource sample without using JNDI.
* Please compare to DataSourceJNDI.java
*
* Please use jdk1.2 or later version
*/
// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
public class DataSource
{
public static void main (String args [])
throws SQLException
{
// Create a OracleDataSource instance explicitly
OracleDataSource ods = new OracleDataSource();
// Set the user name, password, driver type and network protocol
ods.setUser("u1");
ods.setPassword("u1");
ods.setDriverType("oci8");
ods.setNetworkProtocol("ipc");
// Retrieve a connection
Connection conn = ods.getConnection();
getUserName(conn);
// Close the connection
conn.close();
conn = null;
}
static void getUserName(Connection conn)
throws SQLException
{
// Create a Statement
Statement stmt = conn.createStatement ();
// Select the USER column from the dual table
ResultSet rset = stmt.executeQuery ("select USER from dual");
// Iterate through the result and print the USER
while (rset.next ())
System.out.println ("User name is " + rset.getString (1));
// Close the RseultSet
rset.close();
rset = null;
// Close the Statement
stmt.close();
stmt = null;
}
}
DataSourceJNDI使用本地磁盘做jndi的目录
package com.undergrowth.jdbc.generic;
/**
* A Simple DataSource sample with JNDI.
*
* It shows how to bind a logical name to an OracleDataSource
* object, and then how to retrieve the named object of
* OracleDataSource type
*
* This is tested using File System based reference
* implementation of JNDI SPI driver from JavaSoft.
* You need to download fscontext1_2beta3.zip from
* JavaSoft site.
* Include providerutil.jar & fscontext.jar extracted
* from the above ZIP in the classpath.
* Create a directory with JNDI, such as ./JNDI/jdbc
* to hold the logical name
*
* note: 1. Please compare to DataSource.java and
* rm ./JNDI/jdbc/.bindings
* 2. Please use jdk1.2 or later version
*
* usage: java DataSourceJNDI <tmp_dir>
*/
// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
import javax.naming.*;
import javax.naming.spi.*;
import com.sun.jndi.fscontext.RefFSContextFactory;
import java.util.Hashtable;
public class DataSourceJNDI
{
public static void main (String args [])
throws SQLException, NamingException
{
if ( args.length != 1 )
{
System.out.println("usage: java DataSourceJNDI <tmp_dir>");
System.exit(0);
}
// RefFSContextFactory
// Initialize the Context
Context ctx = null;
try {
Hashtable env = new Hashtable (5);
env.put (Context.INITIAL_CONTEXT_FACTORY,
"com.sun.jndi.fscontext.RefFSContextFactory");
env.put (Context.PROVIDER_URL, "file:" + args[0]);
ctx = new InitialContext(env);
} catch (NamingException ne)
{
ne.printStackTrace();
}
do_bind(ctx, "jdbc/sampledb");
do_lookup(ctx, "jdbc/sampledb");
}
static void do_bind (Context ctx, String ln)
throws SQLException, NamingException
{
// Create a OracleDataSource instance explicitly
OracleDataSource ods = new OracleDataSource();
// Set the user name, password, driver type and network protocol
String url = "jdbc:oracle:oci8:@";
try {
String url1 = System.getProperty("JDBC_URL");
if (url1 != null)
url = url1;
} catch (Exception e) {
// If there is any security exception, ignore it
// and use the default
}
ods.setUser("u1");
ods.setPassword("u1");
ods.setURL(url);
// Bind it
System.out.println ("Doing a bind with the logical name : " + ln);
ctx.bind (ln,ods);
}
static void do_lookup (Context ctx, String ln)
throws SQLException, NamingException
{
System.out.println ("Doing a lookup with the logical name : " + ln);
OracleDataSource ods = (OracleDataSource) ctx.lookup (ln);
// Retrieve a connection
Connection conn = ods.getConnection();
getUserName(conn);
// Close the connection
conn.close();
conn = null;
}
static void getUserName(Connection conn)
throws SQLException
{
// Create a Statement
Statement stmt = conn.createStatement ();
// Select the USER column from the dual table
ResultSet rset = stmt.executeQuery ("select USER from dual");
// Iterate through the result and print the employee names
while (rset.next ())
System.out.println ("User name is " + rset.getString (1));
// Close the RseultSet
rset.close();
rset = null;
// Close the Statement
stmt.close();
stmt = null;
}
}
结果
Doing a bind with the logical name : jdbc/sampledb Doing a lookup with the logical name : jdbc/sampledb User name is U1
JNDIDataSource2 从weblogic容器中 取出数据源
package com.undergrowth.jdbc.thin;
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 JNDIDataSource2 {
/**
* @param args
* @throws NamingException
* @throws SQLException
*/
public static void main(String[] args) throws NamingException, SQLException {
// TODO Auto-generated method stub
Hashtable env=new Hashtable();
//jndi工厂
env.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory");
//jndi目录 或者 位置
env.put(Context.PROVIDER_URL, "t3://localhost:7001");
//初始化上下文
Context ct=new InitialContext(env);
//获取数据源
DataSource da=(DataSource) ct.lookup("jndi_test");
//获取连接
Connection connection=da.getConnection();
Statement stmt=connection.createStatement();
ResultSet rset=stmt.executeQuery("select USER from dual");
// Iterate through the result and print the USER
while (rset.next ())
System.out.println ("User name is " + rset.getString (1));
// Close the RseultSet
rset.close();
rset = null;
// Close the Statement
stmt.close();
stmt = null;
connection.close();
ct.close();
}
}
这一个的前提是 在weblogic配置了jndi_test的数据源
posted on 2014-10-08 22:42 liangxinzhi 阅读(929) 评论(0) 编辑 收藏 举报
