JNDI多数据源实现方案

最新推荐文章于 2019-08-20 19:13:15 发布

yangyinbo

最新推荐文章于 2019-08-20 19:13:15 发布

阅读量3.4k

点赞数

分类专栏：架构和项目文章标签： null mysql 数据库 servlet html sql

本文链接：https://blog.csdn.net/yangyinbo/article/details/5863104

版权

架构和项目专栏收录该内容

5 篇文章 0 订阅

订阅专栏

目的：

实现多数据源的配置访问。

配置文件示例：
<id="querycodedict" ds="sysds"> select * from code_dict</id>

本文并没有使用此配置文件进行模拟，但整个过程完全相似，如果以此配置文件为准，需要编写对此文件的解析过程

服务器：

tomcat5.5

数据库：

mysql5.0

准备：

建立两个数据库

数据库1:javatest

mysql> GRANT ALL PRIVILEGES ON *.* TO javauser@localhost   IDENTIFIED BY 'javadude' WITH GRANT OPTION;
mysql> create database javatest;
mysql> use javatest;
mysql> create table testdata (
    ->   id int not null auto_increment primary key,
    ->   foo varchar(25), 
    ->   bar int);
mysql> insert into testdata values(null, 'hello', 12345);
Query OK, 1 row affected (0.00 sec)

mysql> select * from testdata;
+----+-------+-------+
| ID | FOO   | BAR   |
+----+-------+-------+
|  1 | hello | 12345 |
+----+-------+-------+
1 row in set (0.00 sec)

mysql>

数据库2：systest
mysql>  GRANT ALL PRIVILEGES ON *.* TO sysuser@localhost IDENTIFIED BY 'javadude' WITH GRANT OPTION;
Query OK, 0 rows affected (0.06 sec)
mysql> create database systest;
Query OK, 1 row affected (0.02 sec)
mysql> use systest;
Database changed

mysql> create table code_dict(
    -> id int not null auto_increment primary key,
    -> foo varchar(25),
    ->  bar int);
Query OK, 0 rows affected (0.14 sec)
mysql>  insert into code_dict values(null, 'hello', 12345);
Query OK, 1 row affected (0.05 sec)
mysql> select * from  code_dict;
+----+-------+-------+
| id | foo   | bar   |
+----+-------+-------+
|  1 | hello | 12345 |
+----+-------+-------+
1 row in set (0.00 sec)
mysql>

编写配置文件：
tomcat家目录下有conf文件夹，其中有一个context.xml文件
配置如下：
<!-- The contents of this file will be loaded for each web application --> <Context path="/DBTest" docBase="DBTest"        debug="5" reloadable="true" crossContext="true">  <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"               maxActive="100" maxIdle="30" maxWait="10000"               username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"               url="jdbc:mysql://localhost:3306/javatest?autoReconnect=true"/>                <Resource name="jdbc/SysDB" auth="Container" type="javax.sql.DataSource"         maxActive="100" maxIdle="30" maxWait="10000"         username="sysuser" password="javadude" driverClassName="com.mysql.jdbc.Driver"         url="jdbc:mysql://localhost:3306/systest?autoReconnect=true"/></Context> 
添加jbdc驱动：
tomcat家目录下有common文件夹，将mysq驱动放入其中的lib下
编写代码测试：
建立一个web工程，编写一个servlet进行测试
web.xml文件配置如下：
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" 
	xmlns="http://java.sun.com/xml/ns/javaee" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
	http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
	

  <servlet>
    <servlet-name>JNDIServlet</servlet-name>
    <servlet-class>JNDIServlet</servlet-class>
  </servlet>

  <servlet-mapping>
    <servlet-name>JNDIServlet</servlet-name>
    <url-pattern>/servlet/JNDIServlet</url-pattern>
  </servlet-mapping>
  
  <description>MySQL Test App</description>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/TestDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/SysDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
</web-app>
 
编写java代码，JNDIBinding类中的代码在servlet中doGet方法中调用 
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;

import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class JNDIServlet extends HttpServlet
{

	/**
	 * The doGet method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException
	{

		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		out
				.println("<!DOCTYPE HTML PUBLIC /"-//W3C//DTD HTML 4.01 Transitional//EN/">");
		out.println("<HTML>");
		out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");
		out.println("  <BODY>");
		out.print("    This is ");
		out.print(this.getClass());
		out.println(", using the GET method");
		try
		{out.println("get JNDI");
			JNDIBinding.getJNDI(request.getParameter("name"));
		} catch (NamingException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		out.println("  </BODY>");
		out.println("</HTML>");
		out.flush();
		out.close();
	}

	/**
	 * The doPost method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to post.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException
	{
		
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		out
				.println("<!DOCTYPE HTML PUBLIC /"-//W3C//DTD HTML 4.01 Transitional//EN/">");
		out.println("<HTML>");
		out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");
		out.println("  <BODY>");
		out.print("    This is ");
		out.print(this.getClass());
		out.println(", using the POST method");
		
		try
		{out.println("get JNDI");
			JNDIBinding.getJNDI(request.getParameter("name"));
		} catch (NamingException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		out.println("  </BODY>");
		out.println("</HTML>");
		out.flush();
		out.close();
	}

}
 
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;

/***
 * 
 * 设想方案：
 * 多个数据库
 * 同一个配置文件中可以编写不同数据源的sql在其中
 * 代码中指明sql的名称，根据名称得到数据源标识及对应sql
 * 根据数据源标识得到相应的数据库连接，之后即可对相应的数据库进行操作 
 * 
 * 配置文件示例：
 * <id="querycodedict" ds="sysds"> select * from code_dict</id>
 * 
 * @author Administrator
 *
 */
public class JNDIBinding
{

	/***
	 * 以下代码很简单的模拟了根据传入数据不同得到不同数据库连接，进行不同的操作
	 * @param obj
	 * @throws NamingException
	 * @throws SQLException
	 */
	static void getJNDI(Object obj) throws NamingException, SQLException
	{
		System.out.println(obj);
		Context initContext = new InitialContext();
		//两个不同的数据源配置
		String ardb = "jdbc/TestDB";
		String sysdb = "jdbc/SysDB";
		String jndi = null;
		String sql = null;
		//如果传入的参数是123则查询javatest库,不是则查询systest库
		if (obj.equals("123"))
		{
			jndi = ardb;
			sql = "select * from testdata";
		} 
		else
		{
			jndi = sysdb;
			sql = "select * from code_dict";
		}
		//查找数据源
		DataSource ds = (DataSource) initContext.lookup("java:/comp/env/"+ jndi);

		//获得连接
		Connection conn = ds.getConnection();
		if (conn == null)
		{
			System.out.println("conn is null");
			return;
		} else
		{
			System.out.println("conn is not null");
		}

		Statement stmt = null; // Or PreparedStatement if needed
		ResultSet rs = null;
		try
		{

			stmt = conn.createStatement();
			System.out.println(sql);
			rs = stmt.executeQuery(sql);
			while (rs != null && rs.next())
			{
				System.out.println(rs.getInt("id"));

			}
			rs.close();
			rs = null;
			stmt.close();
			stmt = null;
			conn.close(); // Return to connection pool
			conn = null; // Make sure we don't close it twice
		} catch (SQLException e)
		{
			e.getStackTrace();
		} finally
		{

			if (rs != null)
			{
				try
				{
					rs.close();
				} catch (SQLException e)
				{
					;
				}
				rs = null;
			}
			if (stmt != null)
			{
				try
				{
					stmt.close();
				} catch (SQLException e)
				{
					;
				}
				stmt = null;
			}
			if (conn != null)
			{
				try
				{
					conn.close();
				} catch (SQLException e)
				{
					;
				}
				conn = null;
			}
		}

	}
}
 
在地址栏中输入以下内容
http://localhost:8081/DBTest/servlet/JNDIServlet?name=123
运行后中控制台中可以看到查询结果

另特别说明：

此过程中遇到过找不到驱动问题，最后看官方文档发现驱动应该放在common目录下的lib下，如在网上找贴学习，此错误容易遇到

yangyinbo

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
JNDI多数据源实现方案

使用jndi进行了多数据源的配置访问配置文件示例： select * from code_dict
复制链接

扫一扫

专栏目录

JNDI多数据源实现方案

“相关推荐”对你有帮助么？