环境:jdk1.7+oracle11g+Tomcat7.0
1.在tomcat中添加oracle数据库资源
1.1JDBC驱动
将oracle11g的JDBC驱动添加到$CATALINA_HOME/lib目录下(本人用的是ojdbc14.jar)
1.2配置context.xml
修改$CATALINA_HOME/conf/context.xml文件,context.xml文件会被在这个Tomcat部署的所有web应用加载
<!-- The contents of this file will be loaded for each web application -->
<Context>
<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->
<Resource
name="jdbc/orcl"
type="javax.sql.DataSource"
username="yourusername"
password="yourpassword"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@192.168.1.208:1521:orcl"
maxActive="200"
maxIdle="10"
maxWait="10000"
/>
</Context>
2.配置工程中web.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
<display-name>JNDIDemo</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<span style="color:#ff0000;"><resource-ref>
<res-ref-name>jdbc/iqs</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref></span>
</web-app>
3.编写获取JNDI数据源类
package cc.dorado.spence.jndi;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class JdbcUtil {
private static DataSource dataSource;
static{
init();
}
/**
* 私有构造函数
*/
private JdbcUtil(){};
private static void init(){
try {
Context initContext = new InitialContext();
dataSource = (DataSource) initContext.lookup("java:comp/env/jdbc/iqs");
System.out.println("lookup成功");
} catch (NamingException e) {
System.out.println("lookup失败");
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection tmp = null;
try {
tmp = dataSource.getConnection();
System.out.println("获得连接成功");
} catch (SQLException e) {
System.out.println("获得连接失败");
e.printStackTrace();
}
return tmp;
}
public static void closeConnection(Connection conn,Statement stmt,ResultSet rs){
try {
if(rs!=null){
rs.close();
rs=null;
}
if(stmt!=null){
stmt.close();
stmt=null;
}
if(conn!=null){
conn.close();
conn=null;
}
} catch (SQLException e) {
System.out.println("关闭数据库出错");
e.printStackTrace();
}
}
}
package cc.dorado.spence.jndi;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import cc.dorado.spence.jndi.JdbcUtil;
public class SqlUtil {
private Statement stmt;
private ResultSet rs;
public SqlUtil(){
try {
this.stmt = JdbcUtil.getConnection().createStatement();
} catch (SQLException e) {
System.out.println("连接失败");
e.printStackTrace();
}
}
public boolean executeNoQuery(String sql){
try {
return stmt.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public ResultSet executeQuery(String sql){
this.rs = null;
try {
rs=stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public void close(){
JdbcUtil.closeConnection(JdbcUtil.getConnection(), this.stmt, this.rs);
}
}
4.测试页面, 测试数据库是否连接成功的代码一定要再jsp页面和servlet中进行测试
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="cc.dorado.spence.jndi.SqlUtil" %>
<%@ page import="java.sql.*" %>
<!DOCTYPE HTML>
<html>
<head>
<title>JNDI数据源测试</title>
</head>
<body>
<%
String sql = "SELECT t.yhmc,t.xb FROM BASE_USER t";
SqlUtil s = new SqlUtil();
ResultSet r = s.executeQuery(sql);
try {
while(r.next()){
String yhmc = r.getString("yhmc");
String xb = r.getString("xb");
out.println(yhmc+"---"+xb);
}
} catch (SQLException e) {
e.printStackTrace();
}
%>
</body>
</html>
测试结果: admin---男