Struts連接數據庫一般有直接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
commons-pool-1.3.jar
注意：這里有個一個屬性“
property="driverClassName"”千萬要 寫成"driverClassName"，而不是“driverClass”，否則會找不到！
孫衛琴的《精通Struts》就是漏掉了Name，讓我調試了一個下午。
org.apache.commons.dbcp.BasicDataSource
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 數據源問題
源代碼：
1 JDBC版：addressbookV1.zip
2普通配置版：addressbookV2.zip
3PlugIn版： 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綠色版。
posted on 2008-03-26 13:47
CoderDream 閱讀(3870)
評論(5) 編輯 收藏 所屬分類:
Java-22.Struts