实验环境:Eclipse Neon.3 (4.6.3)、MySQL、Tomcat 9.0
一、在web应用下的META-INF下新建context.xml文件,配置数据源。
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="DBPool"
type="javax.sql.DataSource"
auth="Container"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/newsdb"
username="root"
password="root"
maxActive="5"
maxIdle="2"
maxWait="6000" />
</Context>
二、使用JNDI访问数据库连接池
package com.drathin.db;
import java.sql.*;
import javax.naming.*;
import javax.sql.DataSource;
public class Dbpool {
protected static Statement s = null;
protected static ResultSet rs = null;
protected static Connection conn = null;
public static Connection getConnection()
{
try
{
//Context是javax.name包中的一个接口,用于查找数据库连接池的配置文件
Context ctx = new InitialContext(); //向上转型
ctx = (Context) ctx.lookup("java:comp/env");
DataSource ds = (DataSource) ctx.lookup("DBPool");
conn = ds.getConnection();
}catch(Exception e)
{
e.printStackTrace();
//System.out.println("FAil");
}
return conn;
}
public static Statement getS() {
return s;
}
public static void setS(Statement s) {
Dbpool.s = s;
}
public static ResultSet getRs() {
return rs;
}
public static void setRs(ResultSet rs) {
Dbpool.rs = rs;
}
public static Connection getConn() {
return conn;
}
public static void setConn(Connection conn) {
Dbpool.conn = conn;
}
}
三、用JSP脚本测试
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"
import="com.drathin.db.Dbpool"
import="java.sql.*"
%>
<!DOCTYPE html>
<html>
<head>
<title>Tomcat连接池测试</title>
</head>
<body>
<%
Dbpool.setConn(Dbpool.getConnection());
try {
Dbpool.setS(Dbpool.getConn().createStatement());
Dbpool.setRs(Dbpool.getS().executeQuery("select * from user"));
while(Dbpool.getRs().next()){
out.print(" id:" + Dbpool.getRs().getInt(1));
out.print("; username:" + Dbpool.getRs().getString(2));
out.print("; password:" + Dbpool.getRs().getString(3));
out.print("\n");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
%>
</body>
</html>
注意:测试程序切勿用java应用程序,因为这是Tomcat的数据库连接池,如果在eclipse下run运行,会导致Tomcat关闭,而运行抛出异常,也可以用servlet去实现,然后运行在Tomcat下。
package com.drathin.db;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/DBPoolServlet")
public class DBPoolServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public DBPoolServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.getWriter().append("Served at: ").append(request.getContextPath());
Dbpool.setConn(Dbpool.getConnection());
try {
Dbpool.setS(Dbpool.getConn().createStatement());
Dbpool.setRs(Dbpool.getS().executeQuery("select * from user"));
while(Dbpool.getRs().next()){
System.out.print(" id:" + Dbpool.getRs().getInt(1));
System.out.print("; username:" + Dbpool.getRs().getString(2));
System.out.print("; password:" + Dbpool.getRs().getString(3));
System.out.print("\n");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}