mysql jdbc dbcp_连接数据库 JDBC、DBCP、JNDI

一、JDBC

package com.direct.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class JDBCutil {

/*

* 目前使用的JDBC连接数据库，存在问题：

* 每次连接和释放需要比较多的资源

* 资源浪费比较严重，系统的性能下滑

*

* 连接池的原理：

* 提前设定好，数据库的连接对象，当用户连接数据库时，直接从

* “池子”中取出一个连接即可。

* 当用户使用完毕后，“关闭连接”不是物理关闭，而是把连接放回连接池中

*/

private static final String DRIVER="com.mysql.jdbc.Driver";

private static final String URL="jdbc:mysql://localhost:3306/countryside_buy?userUnicode=true&characterEncoding=utf-8";

private static final String USER="root";

private static final String PWD="123456";

static{

try {

Class.forName(DRIVER);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

//获得链接

public Connection createConn(){

Connection conn = null;

try {

conn = DriverManager.getConnection(URL, USER, PWD);

} catch (SQLException e) {

e.printStackTrace();

}

return conn;

}

//关闭连接

public void closeJ(Connection conn,PreparedStatement ps,ResultSet rs) throws SQLException{

if(rs!=null) rs.close();

if(ps!=null) ps.close();

if(conn!=null) conn.close();

}

}

二，DBCP

2.1 dbcp.properties

username=root

password=123456

driverClassName=com.mysql.jdbc.Driver

maxActive=2000

maxIdle=520

maxWait=5000

url=jdbc\:mysql\://localhost\:3306/countryside_buy

2.2 DBCPutil

package com.direct.util;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.ResourceBundle;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

/*

* DBCP使用步骤：

* 1，下载需要的jar包，放入web项目的lib下面

* 2，在src下面建立配置文件 .properties

* 3, 建立连接数据库工具，获取配置信息，

* 4，commons-dbcp.jar

* commons-pool-1.5.3.jar

* musql-connector-java-5.1.7.jar

*/

public class DBCPutil {

//数据源

private static DataSource ds= null;

//基本数据源

static BasicDataSource bds = new BasicDataSource();

public static Connection getconn() throws SQLException{

if (ds==null) {

ResourceBundle rs = ResourceBundle.getBundle("dbcp");

String username = rs.getString("username");

String password = rs.getString("password");

String driverClassName = rs.getString("driverClassName");

String maxActive = rs.getString("maxActive");

String maxIdle = rs.getString("maxIdle");

String maxWait = rs.getString("maxWait");

String url = rs.getString("url");

bds.setMaxActive(Integer.parseInt(maxActive));

bds.setMaxIdle(Integer.parseInt(maxIdle));

bds.setMaxWait(Integer.parseInt(maxWait));

bds.setUrl(url);

bds.setUsername(username);

bds.setPassword(password);

bds.setDriverClassName(driverClassName);

ds = bds;

return ds.getConnection();

}

return ds.getConnection();

}

//测试

public static void main(String[] args) {

try {

System.out.println(getconn());

} catch (SQLException e) {

e.printStackTrace();

}

}

}

三，JNDI

1，所需放在tomcat安装路径的lib目录下  commons.pool-1.5.3.jar  和  mysql-connector-java-5.1.7-bin.jar

2，tomcat中找到config文件夹下的context.xml

在标签中添加

name="jndiDemo"

auth="Container"

type="javax.sql.DataSource"

driverClassName="com.mysql.jdbc.Driver"

url="jdbc:mysql://localhost:3306/countryside_buy"

username="root"

password="123456"

maxActive="100"

maxIdle="30"

maxWait="3000"

/>

3，项目的web.xml中添加映射

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">

jndiDemo

javax.sql.DataSource

Container

This is the description of my J2EE component

This is the display name of my J2EE component

JndiServlet

com.direct.servlet.JndiServlet

JndiServlet

/JndiServlet

register.jsp

4，jndi类

package com.direct.util;

import java.sql.Connection;

import java.sql.SQLException;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.sql.DataSource;

public class JNDIutil {

/*

* JNDI是tomcat提供的一种标准的连接池

* 1，将驱动放入项目中tomcat的lib目录下

* 2，修改配置文件(tomcat中找到config文件夹下的context.xml)

* 3，在web.xml下配置

*/

public static Connection conn(){

Connection connection = null;

try {

//获得对数据源的引用

Context context = new InitialContext();

DataSource ds = (DataSource)context.lookup("java:comp/env/jndiDemo");

//获得数据库连接对象

connection = ds.getConnection();

} catch (NamingException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

return connection;

}

}

5，servlet 测试jndi

package com.direct.servlet;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.direct.util.JNDIutil;

public class JndiServlet extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

doPost(request, response);

}

public void doPost(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

response.setContentType("text/html");

System.out.println(new JNDIutil().conn());

//jdbc:mysql://localhost:3306/countryside_buy, UserName=root@localhost, MySQL-AB JDBC Driver

}

}

最后开启服务器，访问 jndiServlet 后台即可得到连接，并输出连接地址