java使用tomcat数据源连接mysql

主要是用tomcat提供的数据源 ，访问mysql数据库，进行sql查询、调用存储过程

写的很简单 用Servlet提供输出

import java.io.IOException;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.undergrowth.DataHandle;/** * Servlet implementation class MysqlTestServlet */@WebServlet("/MysqlTestServlet")public class MysqlTestServlet extends HttpServlet {private static final long serialVersionUID = 1L;           /**     * @see HttpServlet#HttpServlet()     */    public MysqlTestServlet() {        super();        // TODO Auto-generated constructor stub    }/** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubresponse.setContentType("text/xml; charset=gbk");   response.setCharacterEncoding("utf-8"); try {String whatString=DataHandle.transferXml("select * from grade");//response.getWriter().write("MySql中文\n");//System.out.println(whatString);//用于调用存储过程使用List params=new ArrayList();params.add("好好学习");String proce=DataHandle.callProcedure("test_proce", params);System.out.println(proce);//用于输出xmlresponse.getWriter().write(whatString);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubthis.doGet(request, response);}}

数据处理类

package com.undergrowth;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.List;public class DataHandle {/* * 将查询出来的结果转换为String */public static String transfer(String sql) throws SQLException{String result="没有结果";ResultSet rs=ConnectData.getInstance().executeQuery(sql);ResultSetMetaData resultSetMetaData=rs.getMetaData();if(rs!=null){result="";while (rs.next()) {for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {result+=resultSetMetaData.getColumnName(i+1)+":"+rs.getString(i+1)+"\t";}result+="\n";}}return result;}/** * 将查询出来的结果转换为xml格式 * @param sql * @return * @throws SQLException */public static String transferXml(String sql) throws SQLException{String result="没有结果";ResultSet rs=ConnectData.getInstance().executeQuery(sql);ResultSetMetaData resultSetMetaData=rs.getMetaData();if(rs!=null){result="";result+="<?xml version='1.0' encoding='GBK' ?><results>";while (rs.next()) {result+="<result>";for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {result+="<"+resultSetMetaData.getColumnName(i+1)+">"+rs.getString(i+1)+"</"+resultSetMetaData.getColumnName(i+1)+">";}result+="</result>";}result+="</results>";}return result;}/** * 执行存储过程 * @param name * @param params * @return */public static String callProcedure(String name,List params){String resultString=ConnectData.getInstance().executeProce(name, params, true);return resultString;}}

连接数据库 

package com.undergrowth;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;public class ConnectData {/** * 连接数据库的辅助类 单例模式构建 *  * @author undergrowth *  */private DataSource dataSource;private Connection conn;private Statement stmt;private ResultSet rs;private PreparedStatement ps;private CallableStatement cs;private ConnectData() {}// 使用volatile关键字 保证在多个线程中获取oDbAssist无误private volatile static ConnectData oDbAssist = null;/** * 双层锁 *  * @return */public static ConnectData getInstance() {if (oDbAssist == null) {synchronized (ConnectData.class) {if (oDbAssist == null) {oDbAssist = new ConnectData();oDbAssist.getDataSourceByJndi("test");}}}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里面获取数据源 *    jndi从tomcat里面获取数据源 *  * @return boolean flag 用于标示是否获取到数据源 */private boolean getDataSourceByJndi(String jndiDataSource) {boolean flag = false;String type = "tomcat";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;} catch (NamingException e) {// TODO Auto-generated catch blocke.printStackTrace();}} 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;} catch (NamingException e) {// TODO Auto-generated catch blocke.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 = " ( ? ";elsetempStr = 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 blocke.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 = " ( ? ";elsetempStr = 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 blocke.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 blocke.printStackTrace();}}}

tomcat的context中添加

<Resource name="test"        auth="Container"        type="javax.sql.DataSource"        driverClassName="com.mysql.jdbc.Driver"        url="jdbc:mysql://127.0.0.1:3306/under_test"        username="xxx"        password="xxx"        maxActive="100"        maxIdle="30"        maxWait="10000" />