项目好的设计思路_使用XML_数据源

本文 主要是关注一个好的设计思路

 用XML配置SQL对象、用XML配置SQL语句、封装数据层、获取数据源、加载外部资源

 在应用加载时 加载SQL语句、SQL对象放在内存中 使用的时候 从内存中获取 加快查询速度

在应用加载时 加载外部配置资源  放在内存中 使用的时候 从内存中加载

在应用加载时 加载数据源 

在应用加载时  在web.xml文件中 配置Servlet进行加载外部资源、数据源等

<servlet>
    <description>This is the description of my J2EE component</description>
    <display-name>This is the display name of my J2EE component</display-name>
    <servlet-name>InitDbQueryServlet</servlet-name>
    <servlet-class>com.undergrowth.db.servlet.InitDbQueryServlet</servlet-class>
    <load-on-startup>1</load-on-startup>
  </servlet>

在InitDbQueryServlet的init方法中 进行加载外部资源和数据源等

public void init() throws ServletException {
		// Put your code here
		//加载资源
		DataSourceResourceUtil.loadResource(this.getServletConfig());
		DataSourceResourceUtil.loadSqlFromXml(this.getServletConfig());
		//加载系统外部配置资源
		SystemUtils.loadApplicationConfig();
		//加载数据源
		OracleDbAssist.getInstance();
		System.out.println("成功加载资源和数据源");
	}

加载SQL配置相关类‘

package com.undergrowth.db.resource;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.ServletConfig;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import com.undergrowth.db.DBQuery;

/**
 * 用于加载数据相关的配置SQL
 * @author undergrowth
 *
 */
public class DataSourceResourceUtil {

	private static Logger logger = LogManager.getLogger(DataSourceResourceUtil.class);
	private static Map<String, DBQuery> dbQueryColls = new HashMap<String, DBQuery>();

	private DataSourceResourceUtil() {
	}

	public static Map<String, DBQuery> getDbQueryColls() {
		return dbQueryColls;
	}

	/**
	 * 加载配置SQL对象
	 * @param servletConfig
	 */
	public static void loadResource(ServletConfig servletConfig) {
		InputStream is = null;
		try {
			dbQueryColls.clear();
			//加载位于/WEB-INF/dbquery/目录下的SQL配置文件
			Set resourceSet = servletConfig.getServletContext()
					.getResourcePaths("/WEB-INF/dbquery/");
			Iterator resources = resourceSet.iterator();
			logger.info("加载SQL对象,共有" + resourceSet.size());
			//对每一个配置文件进行迭代  提取出SQL配置对象
			while (resources.hasNext()) {
				String fileName = (String) resources.next();
				// ResourceQuery resourceQuery=new ResourceQuery(fileName);
				is = servletConfig.getServletContext().getResourceAsStream(
						fileName);
				// is=resourceQuery.getInputStream();
				addToColls(is);
				logger.info("加载SQL对象，为" + fileName);
			}
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 将加载到的SQL对象存放在内存中 便于后续的查询使用
	 * @param is
	 */
	private static void addToColls(InputStream is) {
		// TODO Auto-generated method stub
		QueryXmlResource xmlResource = new QueryXmlResource();
		List<DBQuery> dbList = xmlResource.unmarshalQuery(is);
		for (Iterator iterator = dbList.iterator(); iterator.hasNext();) {
			DBQuery db = (DBQuery) iterator.next();
			if (!dbQueryColls.containsKey(db.getQueryName())) {
				dbQueryColls.put(db.getQueryName(), db);
			}
		}
	}

	/**
	 * 加载配置的SQL语句
	 * @param servletConfig
	 */
	public static void loadSqlFromXml(ServletConfig servletConfig) {
		InputStream inputStream = null;
		String xml = "/WEB-INF/dbquery/logging_sql.xml";
		inputStream = servletConfig.getServletContext()
				.getResourceAsStream(xml);
		try {
			SAXReader reader = new SAXReader();
			Document document = reader.read(inputStream);
			Element root = document.getRootElement();
			List dataSetList = root.elements("statement");
			if ((null != dataSetList) && (dataSetList.size() > 0)) {
				Iterator it = dataSetList.iterator();
				Element element;
				String name;
				String statement;
				while (it.hasNext()) {
					element = (Element) it.next();
					name = element.attributeValue("name");
					statement = element.getTextTrim();
					//System.out.println(name+"\t"+statement);
					logger.info("加载配置的SQL语句，名称为"+name+"\t"+"语句为"+statement);
				}
			}
			inputStream.close();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

}

Xml的资源查询器

package com.undergrowth.db.resource;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.xerces.parsers.SAXParser;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

import com.undergrowth.db.DBQuery;


/**
 * 从xml文件中加载资源 并进行解析
 * @author undergrowth
 *
 */
public class QueryXmlResource {

	public List<DBQuery> unmarshalQuery(InputStream is){
		QueryXmlBuilder queryBuilder=new QueryXmlBuilder();
		//使用sax解析器进行解析资源
		SAXParser saxParser = new SAXParser();
		saxParser.setContentHandler(queryBuilder);
		try {
			saxParser.parse(new InputSource(is));
		} catch (SAXException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return queryBuilder.getQueryList();
	}
	
	
}

Xml资源解析器

package com.undergrowth.db.resource;

import java.io.CharArrayWriter;
import java.util.ArrayList;
import java.util.List;

import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import com.undergrowth.db.DBQuery;


/**
 * Xml的自定义解析器
 * @author undergrowth
 *
 */
public class QueryXmlBuilder extends DefaultHandler{
	
	private DBQuery dbQuery;
	private CharArrayWriter contents ;
	private QueryXmlStore queryList;

	
	public QueryXmlBuilder(){
		contents = new CharArrayWriter();
		queryList=new QueryXmlStore();
	}
	
	public List<DBQuery> getQueryList(){
		return queryList.getQueryList();
	}

	
	
	@Override
	public void characters(char[] ch, int start, int length)
			throws SAXException {
		// TODO Auto-generated method stub
		contents.write(ch, start, length);
	}



	@Override
	public void endElement(String uri, String localName, String name)
			throws SAXException {
		// TODO Auto-generated method stub
		if("query".equals(localName)){
			dbQuery.setQueryStatement(contents.toString());
		}
		if("DBQuery".equals(localName)){
			queryList.getQueryList().add(dbQuery);
		}
		
	}

	@Override
	public void startElement(String uri, String localName, String name,
			Attributes attributes) throws SAXException {
		// TODO Auto-generated method stub
		if("DBQuery".equals(localName)){
		  dbQuery=new DBQuery();
		  //获取属性值
		  dbQuery.setQueryName(attributes.getValue("queryName"));
		}
	}
	
	
	
}

加载外部资源的工具类

package com.undergrowth.tool;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

/**
 * 系统工具类
 * 加载外部资源
 * @author undergrowth
 *
 */
public class SystemUtils {

	private static Logger logger = LogManager.getLogger(SystemUtils.class);
	private static final Map<String, String> configProperties=new HashMap<String, String>();
	
	/**
	 * 加载系统外部配置资源
	 */
	public  static void loadApplicationConfig() {
		// TODO Auto-generated method stub
		Properties properties = new Properties();
		try {
			InputStream is =  SystemUtils.class.getClassLoader().getResourceAsStream(
			"application.properties");
			properties.load(is);
			for (Map.Entry<Object, Object> property : properties.entrySet()) {
				configProperties.put((String)property.getKey(), (String)property.getValue());
			}
			logger.info("加载系统外部配置资源,总共有"+configProperties.size());
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 获取对应key的配置名
	 * @param key
	 * @return
	 */
	public static String getConfigPropertiesByKey(String key) {
		return configProperties.get(key);
	}
	
	
}

数据层辅助类

执行SQL、函数、存储过程、XML

package com.undergrowth.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Hashtable;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import oracle.xml.sql.query.OracleXMLQuery;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import com.undergrowth.tool.SystemUtils;

/**
 * 连接oracle数据库的辅助类 单例模式构建
 * 
 * @author undergrowth
 * 
 */
public class OracleDbAssist {

	private static Logger logger = LogManager.getLogger(OracleDbAssist.class);

	private DataSource dataSource;
	private Connection conn;
	private Statement stmt;
	private ResultSet rs;
	private PreparedStatement ps;
	private CallableStatement cs;

	private OracleDbAssist() {
	}

	// 使用volatile关键字 保证在多个线程中获取oDbAssist无误
	private volatile static OracleDbAssist oDbAssist = null;

	/**
	 * 双层锁
	 * 
	 * @return
	 */
	public static OracleDbAssist getInstance() {
		if (oDbAssist == null) {
			synchronized (OracleDbAssist.class) {
				if (oDbAssist == null) {
					oDbAssist = new OracleDbAssist();
					oDbAssist.getDataSourceByJndi("SJPC");
				}
			}
		}
		return oDbAssist;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public Statement getStmt() {
		return stmt;
	}

	public void setStmt(Statement stmt) {
		this.stmt = stmt;
	}

	public ResultSet getRs() {
		return rs;
	}

	public void setRs(ResultSet rs) {
		this.rs = rs;
	}

	/**
	 * 通过jndi从weblogic里面获取数据源
	 * 
	 * @return boolean flag 用于标示是否获取到数据源
	 */
	private boolean getDataSourceByJndi(String jndiDataSource) {
		boolean flag = false;
		String type = SystemUtils.getConfigPropertiesByKey("database_type");
		if ("oracle".equals(type)) {
			// 提供初始化jndi上下文的参数
			Hashtable<String, String> env = new Hashtable<String, String>();
			// 初始化工厂
			env.put(Context.INITIAL_CONTEXT_FACTORY,
					"weblogic.jndi.WLInitialContextFactory");
			env.put(Context.PROVIDER_URL, "t3://localhost:7001");
			try {
				Context ctx = new InitialContext(env);
				dataSource = (DataSource) ctx.lookup(jndiDataSource);
				flag = true;
				logger.info("成功获取数据源!!");
			} catch (NamingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				logger.error("加载数据源失败!!");
			}
		} else if ("tomcat".equals(type)) {
			try {
				Context context = new InitialContext();
				Context envContext = (Context) context.lookup("java:/comp/env");
				dataSource = (DataSource) envContext.lookup(jndiDataSource);
				flag = true;
				logger.info("成功获取数据源!!");
			} catch (NamingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return flag;
	}

	

	/**
	 * 获取数据连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {
		if (getConn() == null)
			conn = dataSource.getConnection();
		return getConn();
	}

	/**
	 * 执行sql查询
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String sql) throws SQLException {
		stmt = getConnection().createStatement();
		rs = stmt.executeQuery(sql);
		return rs;
	}

	/**
	 * 
	 * @param name
	 *            存储过程或者函数的名称
	 * @param param
	 *            参数列表
	 * @param flag
	 *            存储过程(1) 或者函数(0)
	 * @return
	 */
	public String executeProce(String name, List params, boolean flag) {
		String result = "";
		if (flag) { //存储过程
			String procedure = "{  call " + name;
			String tempStr = "";
			if (params != null && params.size() > 0) { 
				for (int i = 0; i <= params.size(); i++) {
					if (i == 0)
						tempStr = " ( ? ";
					else
						tempStr = tempStr + ", ?";
				}

				tempStr = tempStr + " ) ";
			}
			procedure = procedure + tempStr + " }";
			try {
				cs = getConnection().prepareCall(procedure);
				cs.registerOutParameter(1, Types.VARCHAR);  
				setParamValue(cs, params);
				cs.execute();  
				result = cs.getString(1);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} else {//函数
			String procedure = "{ ?= call " + name;
			String tempStr = "";
			if (params != null && params.size() > 0) {
				for (int i = 0; i < params.size(); i++) {
					if (i == 0)
						tempStr = " ( ? ";
					else
						tempStr = tempStr + ", ?";
				}

				tempStr = tempStr + " ) ";
			}
			procedure = procedure + tempStr + " }";
			try {
				cs = getConnection().prepareCall(procedure);
				cs.registerOutParameter(1, Types.VARCHAR);  
				setParamValue(cs, params);
				cs.execute();  
				result = cs.getString(1);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}
	
	private void setParamValue(PreparedStatement theStmt, List params)
	{
		for (int i = 0; i < params.size(); i++)
			try {
				theStmt.setObject(i + 2, params.get(i));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	/**
	 * 执行xml查询
	 * @param sql
	 * @return
	 */
	public  String executeXmlQuery(String  sql){
		
		try {
			OracleXMLQuery xmlQuery=new OracleXMLQuery(getConnection(),sql);
			xmlQuery.setRowsetTag("tables");
			xmlQuery.setRowTag("table");
			xmlQuery.useNullAttributeIndicator(true);
			xmlQuery.setDateFormat("yyyy-mm-dd");
			xmlQuery.setEncoding("utf-8");
			return xmlQuery.getXMLString();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
}

模拟的执行类

package com.undergrowth.db.test;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.undergrowth.db.DBQuery;
import com.undergrowth.db.OracleDbAssist;
import com.undergrowth.db.resource.DataSourceResourceUtil;
import com.undergrowth.tool.SystemUtils;

/**
 * 模拟的执行
 * 包括执行SQL语句、返回Xml结果集、执行存储过程、执行函数、输出到文件中
 * @author undergrowth
 *
 */
public class SimulateV2Query {

	private static int count = 0;

	/**
	 * 从内存中获取指定的SQL语句进行查询 将查询结构写入到文件中
	 * 
	 * @param args
	 */
	public static void executeSQL() {
		// TODO Auto-generated method stub
		OracleDbAssist dbAssist = OracleDbAssist.getInstance();
		ResultSet rs = null;
		try {
			DBQuery dbQuery = DataSourceResourceUtil.getDbQueryColls().get(
					"TEST_NOLOGGING2");
			if (dbQuery != null) {
				rs = dbAssist.executeQuery(dbQuery.getQueryStatement());
				iteratorRs(rs);
			} else {
				System.out.println("无法获取数据集");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 执行SQL语句，输出XML文件
	 */
	public static void executeSqlXml() {
		OracleDbAssist dbAssist = OracleDbAssist.getInstance();

		DBQuery dbQuery = DataSourceResourceUtil.getDbQueryColls().get(
				"TEST_NOLOGGING2");
		if (dbQuery != null) {
			String resultString = dbAssist.executeXmlQuery(dbQuery
					.getQueryStatement());
			writeStringToFile(new StringBuilder(resultString));
		} else {
			System.out.println("无法获取数据集");
		}

	}

	/**
	 * 执行存储过程
	 * test_simulate_v2_pro(result_info out varchar2,name in varchar2,age in int)
	 */
	public static void executeProcedure() {
		OracleDbAssist dbAssist = OracleDbAssist.getInstance();
		List params = new ArrayList();
		params.add("模拟V2");
		params.add(100);
		String resultString = dbAssist.executeProce("test_simulate_v2_pro",
				params, true);
		System.out.println("执行存储过程-->"+resultString);

	}

	/**
	 * 执行函数
	 * test_simulate_v2_func(Name in varchar2, age in int) return varchar2 is
	Result varchar2(100)
	 */
	public static void executeFunction() {
		OracleDbAssist dbAssist = OracleDbAssist.getInstance();
		List params = new ArrayList();
		params.add("模拟V2");
		params.add(100);
		String resultString = dbAssist.executeProce("test_simulate_v2_func",
				params, false);
		System.out.println("执行函数-->"+resultString);

	}

	/**
	 * 迭代结果集
	 * 
	 * @param rs
	 * @throws SQLException
	 */
	private static void iteratorRs(ResultSet rs) throws SQLException {
		// TODO Auto-generated method stub
		StringBuilder builder = new StringBuilder();
		count = 0;
		ResultSetMetaData metaData = rs.getMetaData();
		for (int i = 1; i <= metaData.getColumnCount(); i++) {
			// System.out.print(metaData.getColumnName(i)+"\t");
			builder.append(metaData.getColumnName(i) + "\t");
		}
		builder.append("\n");
		while (rs.next()) {
			// 索引从1开始
			for (int i = 1; i <= metaData.getColumnCount(); i++) {
				// System.out.print(rs.getObject(i)+"\t");
				builder.append(rs.getObject(i) + "\t");
			}
			// System.out.println();
			builder.append("\n");
			count++;
		}
		// 将拼接的字符串写入输出流
		writeStringToFile(builder);
	}

	/**
	 * 将获取的记录输出到文件中
	 * 
	 * @param builder
	 */
	private static void writeStringToFile(StringBuilder builder) {
		// TODO Auto-generated method stub
		OutputStream oStream = null;
		ObjectOutputStream oStream2 = null;
		File file = new File(SystemUtils.getConfigPropertiesByKey("log_file"));
		if (!file.exists())
			file.mkdirs();
		try {
			String fileName = getFileName();
			File filePathFile = new File(file
					+ System.getProperty("file.separator") + fileName);
			oStream = new FileOutputStream(filePathFile);
			oStream.write(builder.toString().getBytes());
			// oStream2=new ObjectOutputStream(oStream);
			// oStream2.writeChars(builder.toString());
			// oStream2.writeObject(builder);
			// oStream2.flush();
			oStream.flush();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (oStream2 != null)
				try {
					oStream2.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if (oStream != null)
				try {
					oStream.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
	}

	/**
	 * 获取文件名
	 * 
	 * @return
	 */
	private static String getFileName() {
		Calendar calendar = Calendar.getInstance();
		calendar.setTimeInMillis(System.currentTimeMillis());
		String fileName = calendar.get(Calendar.YEAR) + "_"
				+ (calendar.get(Calendar.MONTH) + 1) + "_"
				+ calendar.get(Calendar.DAY_OF_MONTH) + ".txt";
		return fileName;
	}

	public static int getCount() {
		return count;
	}

}

前台JSP测试

<%@ page language="java" import="java.util.*,com.undergrowth.db.test.*" pageEncoding="utf-8"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://"
			+ request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
	<head>
		<base href="<%=basePath%>">

		<title>模拟V2数据获取</title>
		<meta http-equiv="pragma" content="no-cache">
		<meta http-equiv="cache-control" content="no-cache">
		<meta http-equiv="expires" content="0">
		<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
		<meta http-equiv="description" content="This is my page">
		
	  
	</head>

	<body>
		<%
		SimulateV2Query.executeSQL();
		if(SimulateV2Query.getCount()>0)  out.print("成功获取SQL数据");
		else{
			out.print("获取SQL数据失败");
		}
		//执行查询 返回XML结果集
		SimulateV2Query.executeSqlXml();
		//执行存储过程
		SimulateV2Query.executeProcedure();
		//执行函数
		SimulateV2Query.executeFunction();
		%>
	</body>
</html>

外部配置文件

application.properties

database_type=tomcat
log_file=e\:/log/

配置文件log4j.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

	<!-- Appenders -->
	<appender name="console" class="org.apache.log4j.ConsoleAppender">
		<param name="Target" value="System.out" />
		<layout class="org.apache.log4j.PatternLayout">
			<param name="ConversionPattern" value="%d{yyyy-MMM-dd HH:mm:ss} [%t] %-5p:  %c - %m%n" />
		</layout>
	</appender>
	
	<!-- Root Logger -->
	<root>
		<priority value="info" />
		<appender-ref ref="console" />
	</root>
	
</log4j:configuration>

SQL语句配置文件

logging_sql.xml

<?xml version="1.0" encoding="utf-8" ?>
<statements>
  <statement name="TEST_NOLOGGING2">
	<![CDATA[
		SELECT tn.* from TEST_NOLOGGING2 tn WHERE tn.rule_code<2000
	]]>
	</statement>
</statements>

SQL对象配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<DBQuery queryName="TEST_NOLOGGING2">
<query>
SELECT tn.* from TEST_NOLOGGING2 tn 
</query>
</DBQuery>

测试存储过程

create or replace procedure test_simulate_v2_pro(result_info out varchar2,name in varchar2,age in int) IS
now_time DATE;
begin
  now_time :=SYSDATE;
  result_info := NAME || ',you age is ' || age || ',现在时间:' || to_char(now_time,'yyyy-mm-dd hh24:mi:ss');
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE || Sqlerrm);  
end test_simulate_v2_pro;

测试函数

create or replace function test_simulate_v2_func(Name in varchar2, age in int) return varchar2 is
  Result varchar2(100);
  now_time DATE;
begin
  now_time :=SYSDATE;
  Result := NAME || ',you age is ' || age || ',现在时间:' || to_char(now_time,'yyyy-mm-dd hh24:mi:ss');
  RETURN(Result);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE || Sqlerrm);  
end test_simulate_v2_func;

tomcat的数据源配置

在context.xml中 添加

 <Resource name="SJPC"
       auth="Container"
       type="javax.sql.DataSource"
       driverClassName="oracle.jdbc.driver.OracleDriver"
       url="jdbc:oracle:thin:@ip:1521:sid"
       username="xxx"
       password="xxx"
       maxActive="100"
       maxIdle="30"
       maxWait="10000" />

最后控制台输出

2015-2-14 11:02:04 org.apache.catalina.startup.HostConfig checkResources
信息: Reloading context [/TechnoAccumu]
2015-2-14 11:02:04 org.apache.catalina.loader.WebappClassLoader validateJarFile
信息: validateJarFile(E:\some\tomcat\apache-tomcat-6.0.37\webapps\TechnoAccumu\WEB-INF\lib\geronimo-servlet_3.0_spec-1.0.jar) - jar not loaded. See Servlet Spec 2.3, section 9.7.2. Offending class: javax/servlet/Servlet.class
2015-二月-14 11:02:11 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO :  com.undergrowth.db.resource.DataSourceResourceUtil - 加载SQL对象,共有2
2015-二月-14 11:02:11 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO :  com.undergrowth.db.resource.DataSourceResourceUtil - 加载SQL对象，为/WEB-INF/dbquery/logging_sql.xml
2015-二月-14 11:02:11 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO :  com.undergrowth.db.resource.DataSourceResourceUtil - 加载SQL对象，为/WEB-INF/dbquery/TEST_NOLOGGING2.xml
2015-二月-14 11:02:11 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO :  com.undergrowth.db.resource.DataSourceResourceUtil - 加载配置的SQL语句，名称为TEST_NOLOGGING2	语句为SELECT tn.* from TEST_NOLOGGING2 tn WHERE tn.rule_code<2000
2015-二月-14 11:02:11 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO :  com.undergrowth.tool.SystemUtils - 加载系统外部配置资源,总共有2
2015-二月-14 11:02:11 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] INFO :  com.undergrowth.db.OracleDbAssist - 成功获取数据源!!
成功加载资源和数据源
执行存储过程-->模拟V2,you age is 100,现在时间:2015-02-14 11:04:10
执行函数-->模拟V2,you age is 100,现在时间:2015-02-14 11:04:10