Java Web数据源详解

点击这里使用RSS订阅本Blog： <link rel="alternate" href="http://feed.feedsky.com/softwave" type="application/rss+xml" title="RSS 2.0"> <script language="javascript"> </script><script language="javascript" src="http://www.feedsky.com/jsout/publishlist_v2.js?burl=softwave&out_html=true"></script>

Java Web连接数据库一般有直接JDBC和数据源两种方式，

1、JDBC：

在MySQL中创建数据库：

drop database if exists login;
create database login;
use login;
create table user (
username varchar ( 50 ) not null ,
password varchar ( 50 ),
primary key (username)
);

insert into user (username,password) values ("CoderDream"," 12345678 ");

测试代码：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test{
public static void main(String[]args){
try {
// A:1、2都可以
// Class.forName("org.gjt.mm.mysql.Driver"); // 1
Class.forName( " com.mysql.jdbc.Driver " ); // 2

// B:连接数据库，用户名为：root，密码为空
Connectionconn = DriverManager.getConnection(
" jdbc:mysql://localhost:3306/login " , " root " , "" );

// C:创建Statement
Statementstmt = conn.createStatement();

// D:查询数据库中用户CoderDream的密码字段
Stringsql = " selectpasswordfromuserwhereusername='CoderDream' " ;

// E:得到结果集
ResultSetrs = stmt.executeQuery(sql);

// F:处理结果集，简单的输出password
while (rs.next()){
System.out.print(rs.getString( " password " )); // 输出结果为 12345678
}

// G:关闭资源
rs.close();
stmt.close();
conn.close();
} catch (Exceptione){
e.printStackTrace();
}
}
}

在Struts中一般会写一个公用类，用于连接数据库：
A、创建数据库：ADDRESSBOOKSAMPLE.sql

DROP DATABASE IF EXISTS ADDRESSBOOKSAMPLE;
CREATE DATABASE ADDRESSBOOKSAMPLE;
USE ADDRESSBOOKSAMPLE;
CREATE TABLE ADDRESSBOOK_TABLE(ID INT ( 4 )AUTO_INCREMENT NOT NULL PRIMARY KEY ,NAME VARCHAR ( 25 ),PHONE VARCHAR ( 10 ),ADDRESS VARCHAR ( 50 ));
INSERT INTO ADDRESSBOOK_TABLE VALUES ( 1 , ' Wang ' , ' 56671234 ' , ' Beijing,Haidian ' );
INSERT INTO ADDRESSBOOK_TABLE VALUES ( 2 , ' Zhang ' , ' 45664568 ' , ' Shanghai,Pudong ' );
INSERT INTO ADDRESSBOOK_TABLE VALUES ( 3 , ' Cheng ' , ' 56643456 ' , ' Tianjing ' );
INSERT INTO ADDRESSBOOK_TABLE VALUES ( 4 , ' Zhao ' , ' 56789988 ' , ' Hainan ' );
INSERT INTO ADDRESSBOOK_TABLE VALUES ( 5 , ' Cao ' , ' 56498543 ' , ' Heibei ' );

B、公用类：DbUtil.java

package addressbook.model;

import java.sql.Connection;
import java.sql.DriverManager;

/**
*<strong>DbUtil</strong>isautilityclasstocreateaconnectiontoour
*sampledatabase.
*/
public class DbUtil{
static StringdriverName = " com.mysql.jdbc.Driver " ;
static StringdbUrl = " jdbc:mysql:// " ;

public DbUtil(){

}

public static ConnectionconnectToDb(StringhostName,StringdatabaseName)
throws Exception{
Connectionconnection = null ;
StringconnName = dbUrl + hostName + " :3306 " + " / " + databaseName;
Class.forName(driverName).newInstance();
connection = DriverManager.getConnection(connName, " root " , "" );
return connection;
}

public static ConnectionconnectToDb(StringdatabaseName) throws Exception{
return (connectToDb( " localhost " ,databaseName));
}

public static ConnectionconnectToDb() throws Exception{
return (connectToDb( " localhost " , " addressbooksample " ));
}
}

C、JavaBean中调用公用类：

public void insert() throws Exception{
Connectioncon = DbUtil.connectToDb();
PreparedStatementpStmt = null ;
try {
pStmt = con.prepareStatement( " INSERTINTO " + Constants.TABLENAME
+ " (name,phone,address) " + " values(?,?,?) " );
con.setAutoCommit( false );

pStmt.setString( 1 ,name);
pStmt.setString( 2 ,phone);
pStmt.setString( 3 ,address);
pStmt.executeUpdate();
con.commit();
} catch (Exceptionex){
try {
con.rollback();
} catch (SQLExceptionsqlex){
sqlex.printStackTrace(System.out);
}
throw ex;
} finally {
try {
pStmt.close();
con.close();
} catch (Exceptione){
e.printStackTrace();
}
}
}

public static Vectorsearch(StringstrSql) throws Exception{
VectoraddressbookBeans = new Vector();
Connectioncon = DbUtil.connectToDb();
PreparedStatementpStmt = null ;
ResultSetrs = null ;
try {
pStmt = con.prepareStatement(strSql);
rs = pStmt.executeQuery();
while (rs.next()){
addressbookBeans.add( new AddressBookBean(rs.getString( " NAME " ),
rs.getString( " PHONE " ),rs.getString( " ADDRESS " )));
}
return addressbookBeans;
} finally {
try {
rs.close();
pStmt.close();
con.close();
} catch (Exceptione){
e.printStackTrace();
}
}
}

2、一般数据源配置

在struts-config.xml的<data-sources>标签中加入：

< data-sources >
< data-source type ="org.apache.commons.dbcp.BasicDataSource" >
< set-property property ="autoCommit" value ="true" />
< set-property property ="description"
value ="MySQLDataSource" />
< set-property property ="driverClassName"
value ="com.mysql.jdbc.Driver" />
< set-property property ="maxCount" value ="10" />
< set-property property ="minCount" value ="2" />
< set-property property ="username" value ="root" />
< set-property property ="password" value ="" />
< set-property property ="url"
value ="jdbc:mysql://localhost:3306/addressbooksample" />
</ data-source >
</ data-sources >

同时导入以下包：
mysql-connector-java-5.1.0-bin.jar
commons-dbcp-1.2.2.jar
commons-pool-1.3.jar

注意：这里有个一个属性“ property ="driverClassName"” 千万要写成"driverClassName"，而不是“driverClass”，否则会找不到！
孙卫琴的《精通Struts》就是漏掉了Name，让我调试了一个下午。
org.apache.commons.dbcp.BasicDataSource

protected synchronized DataSourcecreateDataSource()
throws SQLException{

// LoadtheJDBCdriverclass
if (driverClassName != null ){
try {
Class.forName(driverClassName);
} catch (Throwablet){
Stringmessage = " CannotloadJDBCdriverclass' " +
driverClassName + " ' " ;
logWriter.println(message);
t.printStackTrace(logWriter);
throw new SQLNestedException(message,t);
}
}

// CreateaJDBCdriverinstance
Driverdriver = null ;
try {
driver = DriverManager.getDriver(url);
} catch (Throwablet){
Stringmessage = " CannotcreateJDBCdriverofclass' " +
(driverClassName != null ? driverClassName: "" ) +
" 'forconnectURL' " + url + " ' " ;
logWriter.println(message);
t.printStackTrace(logWriter);
throw new SQLNestedException(message,t);
}
}

从源代码我们可以看到，如果不是“driverClassName”，就会得不到相应的类名，后面的getDriver(url)就会抛出异常：

[ERROR]ActionServlet - Initializingapplicationdatasourceorg.apache.struts.action.DATA_SOURCE < org.apache.commons.dbcp.SQLNestedException:CannotcreateJDBCdriverof class '' for connectURL ' jdbc:mysql://localhost:3306/addressbooksample ' > org.apache.commons.dbcp.SQLNestedException: CannotcreateJDBCdriverof class ' ' for connectURL ' jdbc:mysql://localhost:3306/addressbooksample '
atorg.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java: 1150 )
atorg.apache.commons.dbcp.BasicDataSource.setLogWriter(BasicDataSource.java: 959 )
atorg.apache.struts.action.ActionServlet.initModuleDataSources(ActionServlet.java: 778 )
atorg.apache.struts.action.ActionServlet.init(ActionServlet.java: 331 )
atjavax.servlet.GenericServlet.init(GenericServlet.java: 212 )
atorg.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java: 1139 )
atorg.apache.catalina.core.StandardWrapper.load(StandardWrapper.java: 966 )
atorg.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java: 3956 )
atorg.apache.catalina.core.StandardContext.start(StandardContext.java: 4230 )
atorg.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java: 760 )
atorg.apache.catalina.core.ContainerBase.addChild(ContainerBase.java: 740 )
atorg.apache.catalina.core.StandardHost.addChild(StandardHost.java: 544 )
atorg.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java: 920 )
atorg.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java: 883 )
atorg.apache.catalina.startup.HostConfig.deployApps(HostConfig.java: 492 )
atorg.apache.catalina.startup.HostConfig.start(HostConfig.java: 1138 )
atorg.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java: 311 )
atorg.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java: 120 )
atorg.apache.catalina.core.ContainerBase.start(ContainerBase.java: 1022 )
atorg.apache.catalina.core.StandardHost.start(StandardHost.java: 736 )
atorg.apache.catalina.core.ContainerBase.start(ContainerBase.java: 1014 )
atorg.apache.catalina.core.StandardEngine.start(StandardEngine.java: 443 )
atorg.apache.catalina.core.StandardService.start(StandardService.java: 448 )
atorg.apache.catalina.core.StandardServer.start(StandardServer.java: 700 )
atorg.apache.catalina.startup.Catalina.start(Catalina.java: 552 )
atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)
atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java: 39 )
atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java: 25 )
atjava.lang.reflect.Method.invoke(Method.java: 597 )
atorg.apache.catalina.startup.Bootstrap.start(Bootstrap.java: 295 )
atorg.apache.catalina.startup.Bootstrap.main(Bootstrap.java: 433 )
Causedby:java.sql.SQLException:Nosuitabledriver
atjava.sql.DriverManager.getDriver(DriverManager.java: 264 )
atorg.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java: 1143 )

30 more

提示class ' '不能连接URL！

数据源配置好了以后，我们就可以在Action中得到DataSource。
在InsertAction的execute()方法中：

DataSourceds = getDataSource(request);
Connectioncon = ds.getConnection();

bean.insert(con);

这样，改写Bean中的insert()方法和search()方法，传入参数增加一个“Connection con”：

public void insert(Connectioncon) throws Exception{
PreparedStatementpStmt = null ;
try {
pStmt = con.prepareStatement( " INSERTINTO " + Constants.TABLENAME
+ " (name,phone,address) " + " values(?,?,?) " );
con.setAutoCommit( false );

pStmt.setString( 1 ,name);
pStmt.setString( 2 ,phone);
pStmt.setString( 3 ,address);
pStmt.executeUpdate();
con.commit();
} catch (Exceptionex){
try {
con.rollback();
} catch (SQLExceptionsqlex){
sqlex.printStackTrace(System.out);
}
throw ex;
} finally {
try {
pStmt.close();
con.close();
} catch (Exceptione){
e.printStackTrace();
}
}
}

public static Vectorsearch(Connectioncon,StringstrSql) throws Exception{
VectoraddressbookBeans = new Vector();
PreparedStatementpStmt = null ;
ResultSetrs = null ;
try {
pStmt = con.prepareStatement(strSql);
rs = pStmt.executeQuery();
while (rs.next()){
addressbookBeans.add( new AddressBookBean(rs.getString( " NAME " ),
rs.getString( " PHONE " ),rs.getString( " ADDRESS " )));
}
return addressbookBeans;
} finally {
try {
rs.close();
pStmt.close();
con.close();
} catch (Exceptione){
e.printStackTrace();
}
}
}

这样就不需使用数据库工具类 DbUtil 了。

3、插件方式，任何地方都可以得到数据源。

以一般方式配置的数据源有一个局限性，就是只能在Action中得到，因为要用到HttpServletRequest作为参数来得到数据源。

解决的办法是写一个插件，这个插件实现了Struts的PlugIn接口。

通过这个插件，我们可以在任何类中（包括Action、JavaBean和其他类）直接得到数据源，并建立连接：

package addressbook.plug;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.struts.action.ActionServlet;
import org.apache.struts.action.PlugIn;
import org.apache.struts.config.ModuleConfig;

public class Conn implements PlugIn{

private static DataSourcedataSource = null ;
private Connectionconn = null ;
private PreparedStatementpreStmt = null ;
private Statementstmt = null ;

// 得到数据源
public void init(ActionServletservlet,ModuleConfigconfig){
dataSource = (DataSource)servlet.getServletContext().getAttribute(
" org.apache.struts.action.DATA_SOURCE " );
}

public Conn() throws SQLException{
if (dataSource != null ){
conn = dataSource.getConnection();
}
}

public ResultSetexecuteQuery(Stringsql){
ResultSetrs = null ;

try {
if (stmt == null ){
stmt = conn.createStatement();
}

rs = stmt.executeQuery(sql);
} catch (SQLExceptione){
e.printStackTrace();
}

return rs;
}

public void executeUpdate(Stringsql) throws SQLException{
if (stmt == null ){
stmt = conn.createStatement();
}

stmt.executeUpdate(sql);
}

public ConnectiongetConn(){
return conn;
}

public void prepareStatement(StringsqlStr) throws SQLException{
preStmt = conn.prepareStatement(sqlStr);
}

public void setString( int index,Stringvalue) throws SQLException{
preStmt.setString(index,value);
}

public void setInt( int index, int value) throws SQLException{
preStmt.setInt(index,value);
}

public void setBoolean( int index, boolean value) throws SQLException{
preStmt.setBoolean(index,value);
}

public void setLong( int index, long value) throws SQLException{
preStmt.setLong(index,value);
}

public void setFloat( int index, float value) throws SQLException{
preStmt.setFloat(index,value);
}

public void setBytes( int index, byte []value) throws SQLException{
preStmt.setBytes(index,value);
}

public void clearPreStmt() throws SQLException{
preStmt.clearParameters();
preStmt = null ;
}

public ResultSetexecuteQuery() throws SQLException{
if (preStmt != null ){
return preStmt.executeQuery();
} else {
return null ;
}
}

public void executeUpdate() throws SQLException{
if (preStmt != null ){
preStmt.executeUpdate();
}
}

public void close(){
try {
if (stmt != null ){
stmt.close();
stmt = null ;
}

if (preStmt != null ){
preStmt.close();
preStmt = null ;
}

if (conn != null ){
conn.close();
conn = null ;
System.out.println( " ****aconnectionisclosed**** " );
}
} catch (Exceptione){
System.err.println(e.getMessage());
}
}

public void destroy(){
}
}

在struts-config.xml中配置数据源：

< data-sources >
< data-source key ="org.apache.struts.action.DATA_SOURCE"
type ="org.apache.commons.dbcp.BasicDataSource" >
< set-property property ="autoCommit" value ="true" />
< set-property property ="description"
value ="MySQLDataSource" />
< set-property property ="driverClassName"
value ="com.mysql.jdbc.Driver" />
< set-property property ="maxCount" value ="10" />
< set-property property ="minCount" value ="2" />
< set-property property ="username" value ="root" />
< set-property property ="password" value ="" />
< set-property property ="url"
value ="jdbc:mysql://localhost:3306/addressbooksample" />
</ data-source >
</ data-sources >

同时在struts-config.xml文件的最后配置PlugIn

< plug-in className ="addressbook.plug.Conn" ></ plug-in >

这样，我们在JavaBean和其他类中就可以直接得到数据源的Connection了：

Connectioncon = new Conn().getConn();

注意：
在Struts1.3中已经取消了<data-sources>标签，也就是说只能在1.2版中配置，因为Apache不推荐在struts-config.xml中配置数据源。

参考资料：
1、在struts中以无参数的javabeans的方式调用struts-config.xml中配置的数据源
2、 struts数据源管理器
3、 struts 数据源问题

源代码：
1 JDBC版：addressbookV1.zip
2 普通配置版：addressbookV2.zip
3 PlugIn版： addressbookV3.zip

PS: 可以直接将源代码导入eclipse，然后加入Struts 1.2的所有包和另外3个包 mysql-connector-java-5.1.0-bin.jar 、 commons-dbcp-1.2.2.jar 、 commons-pool-1.3.jar 。
数据库推荐使用BeanSoft的 MySQL绿色版。

转自【http://www.blogjava.net/coderdream/archive/2008/03/26/188687.html】

<script type="text/javascript" src="http://www.google.com/reader/ui/publisher.js"></script><script type="text/javascript" src="http://www.google.com/reader/public/javascript/user/00697638153916680411/state/com.google/broadcast?n=5&callback=GRC_p(%7Bc%3A%22green%22%2Ct%3A%22%5Cu8FD9%5Cu4E9B%5Cu6587%5Cu7AE0%5Cu4E5F%5Cu503C%5Cu5F97%5Cu4E00%5Cu770B%22%2Cs%3A%22false%22%7D)%3Bnew%20GRC"></script>

Java Web数据源详解

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义