Java Web数据源详解
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，密码为空
Connection conn = DriverManager.getConnection(
" jdbc:mysql://localhost:3306/login " , " root " , "" );
// C: 创建 Statement
Statement stmt = conn.createStatement();
// D: 查询数据库中用户 CoderDream 的密码字段
String sql = " select password from user where username ='CoderDream' " ;
// E: 得到结果集
ResultSet rs = stmt.executeQuery(sql);
// F: 处理结果集，简单的输出password
while (rs.next()) {
System.out.print(rs.getString( " password " )); // 输出结果为 12345678
}
// G: 关闭资源
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
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> is a utility class to create a connection to our
* sample database.
*/
public class DbUtil {
static String driverName = " com.mysql.jdbc.Driver " ;
static String dbUrl = " jdbc:mysql:// " ;
public DbUtil() {
}
public static Connection connectToDb(String hostName, String databaseName)
throws Exception {
Connection connection = null ;
String connName = dbUrl + hostName + " :3306 " + " / " + databaseName;
Class.forName(driverName).newInstance();
connection = DriverManager.getConnection(connName, " root " , "" );
return connection;
}
public static Connection connectToDb(String databaseName) throws Exception {
return (connectToDb( " localhost " , databaseName));
}
public static Connection connectToDb() throws Exception {
return (connectToDb( " localhost " , " addressbooksample " ));
}
}C、JavaBean中调用公用类：
public void insert() throws Exception {
Connection con = DbUtil.connectToDb();
PreparedStatement pStmt = null ;
try {
pStmt = con.prepareStatement( " INSERT INTO " + 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 (Exception ex) {
try {
con.rollback();
} catch (SQLException sqlex) {
sqlex.printStackTrace(System.out);
}
throw ex;
} finally {
try {
pStmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static Vector search(String strSql) throws Exception {
Vector addressbookBeans = new Vector();
Connection con = DbUtil.connectToDb();
PreparedStatement pStmt = null ;
ResultSet rs = 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 (Exception e) {
e.printStackTrace();
}
}
}2、一般数据源配置
在struts-config.xml的<data-sources>标签中加入：
<!-- ============ Data Source =================================== -->
< data-sources >
< data-source type ="org.apache.commons.dbcp.BasicDataSource" >
< set-property property ="autoCommit" value ="true" />
< set-property property ="description"
value ="MySQL Data Source" />
<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
注意：这里有个一个属性“
property ="driverClassName"”千万要 写成"driverClassName"，而不是“driverClass”，否则会找不到！
孙卫琴的《精通Struts》就是漏掉了Name，让我调试了一个下午。
protected synchronized DataSource createDataSource()
throws SQLException {
// Load the JDBC driver class
if (driverClassName != null ) {
try {
Class.forName(driverClassName);
} catch (Throwable t) {
String message = " Cannot load JDBC driver class ' " +
driverClassName + " ' " ;
logWriter.println(message);
t.printStackTrace(logWriter);
throw new SQLNestedException(message, t);
}
}
// Create a JDBC driver instance
Driver driver = null ;
try {
driver = DriverManager.getDriver(url);
} catch (Throwable t) {
String message = " Cannot create JDBC driver of class ' " +
(driverClassName != null ? driverClassName : "" ) +
" ' for connect URL ' " + url + " ' " ;
logWriter.println(message);
t.printStackTrace(logWriter);
throw new SQLNestedException(message, t);
}
}
从源代码我们可以看到，如果不是“driverClassName”，就会得不到相应的类名，后面的getDriver(url)就会抛出异常：
[ERROR] ActionServlet - Initializing application data source org.apache.struts.action.DATA_SOURCE < org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL ' jdbc:mysql://localhost:3306/addressbooksample ' > org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class ' ' for connect URL 'jdbc:mysql://localhost:3306/addressbooksample'
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java: 1150 )
at org.apache.commons.dbcp.BasicDataSource.setLogWriter(BasicDataSource.java: 959 )
at org.apache.struts.action.ActionServlet.initModuleDataSources(ActionServlet.java: 778 )
at org.apache.struts.action.ActionServlet.init(ActionServlet.java: 331 )
at javax.servlet.GenericServlet.init(GenericServlet.java: 212 )
at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java: 1139 )
at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java: 966 )
at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java: 3956 )
at org.apache.catalina.core.StandardContext.start(StandardContext.java: 4230 )
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java: 760 )
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java: 740 )
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java: 544 )
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java: 920 )
at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java: 883 )
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java: 492 )
at org.apache.catalina.startup.HostConfig.start(HostConfig.java: 1138 )
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java: 311 )
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java: 120 )
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java: 1022 )
at org.apache.catalina.core.StandardHost.start(StandardHost.java: 736 )
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java: 1014 )
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java: 443 )
at org.apache.catalina.core.StandardService.start(StandardService.java: 448 )
at org.apache.catalina.core.StandardServer.start(StandardServer.java: 700 )
at org.apache.catalina.startup.Catalina.start(Catalina.java: 552 )
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java: 39 )
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java: 25 )
at java.lang.reflect.Method.invoke(Method.java: 597 )
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java: 295 )
at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java: 433 )
Caused by: java.sql.SQLException: No suitable driver
at java.sql.DriverManager.getDriver(DriverManager.java: 264 )
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java: 1143 )
30 more提示class ' '不能连接URL！
数据源配置好了以后，我们就可以在Action中得到DataSource。
在InsertAction的execute()方法中：
DataSource ds = getDataSource(request);
Connection con = ds.getConnection();
bean.insert(con);
这样，改写Bean中的insert()方法和search()方法，传入参数增加一个“Connection con”：
public void insert(Connection con) throws Exception {
PreparedStatement pStmt = null ;
try {
pStmt = con.prepareStatement( " INSERT INTO " + 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 (Exception ex) {
try {
con.rollback();
} catch (SQLException sqlex) {
sqlex.printStackTrace(System.out);
}
throw ex;
} finally {
try {
pStmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static Vector search(Connection con, String strSql) throws Exception {
Vector addressbookBeans = new Vector();
PreparedStatement pStmt = null ;
ResultSet rs = 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 (Exception e) {
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 DataSource dataSource = null ;
private Connection conn = null ;
private PreparedStatement preStmt = null ;
private Statement stmt = null ;
// 得到数据源
public void init(ActionServlet servlet, ModuleConfig config) {
dataSource = (DataSource) servlet.getServletContext().getAttribute(
" org.apache.struts.action.DATA_SOURCE " );
}
public Conn() throws SQLException {
if (dataSource != null ) {
conn = dataSource.getConnection();
}
}
public ResultSet executeQuery(String sql) {
ResultSet rs = null ;
try {
if (stmt == null ) {
stmt = conn.createStatement();
}
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public void executeUpdate(String sql) throws SQLException {
if (stmt == null ) {
stmt = conn.createStatement();
}
stmt.executeUpdate(sql);
}
public Connection getConn() {
return conn;
}
public void prepareStatement(String sqlStr) throws SQLException {
preStmt = conn.prepareStatement(sqlStr);
}
public void setString( int index, String value) 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 ResultSet executeQuery() 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( " **** a connection is closed **** " );
}
} catch (Exception e) {
System.err.println(e.getMessage());
}
}
public void destroy() {
}
}
在struts-config.xml中配置数据源：
<!-- ============ Data Source =================================== -->
< 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 ="MySQL Data Source" />
< 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
<!-- ========== PlugIn Definitions ============================== -->
< plug-in className ="addressbook.plug.Conn" ></ plug-in >这样，我们在JavaBean和其他类中就可以直接得到数据源的Connection了：
Connection con = new Conn().getConn();
注意：
在Struts1.3中已经取消了<data-sources>标签，也就是说只能在1.2版中配置，因为Apache不推荐在struts-config.xml中配置数据源。
参考资料：
1、在struts中以无参数的javabeans的方式调用struts-config.xml中配置的数据源2、struts数据源管理器
3、struts 数据源问题
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
