郁闷，我们老师非让我们把Javabean里面的连接语句拆开写达到同样的效果，目的是为了以后别的jsp也可以调用这里面的功能，郁闷死了，看了一个星期也没看懂怎么拆开它。要分成connect(), executeSQL(sql), getHeader(id), getRecord(row, col),等等。代码如下：
----lab6b.jsp-------<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%><jsp:useBean id="links" class="webtech.lab6.links" /><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd"><html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Lab 6</title>
</head>
<body> <h3>The Result was:</h3>
<table border="1">
<%=links.getLinks()%>
</table>
</body>
</html>-------links.java-------------package webtech.lab6;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;public class links {
public String getLinks() {
String result = "<tr><td>Nothing Happened</td></tr>";
String query = "select * from links;";
String url = "jdbc:mysql://mysql0.ee.surrey.ac.uk:3306/webtech";
String driver = "org.gjt.mm.mysql.Driver";
String uname = "webtech";
String dpass = "webtech";
Connection dbconn = null;
try {
Class.forName(driver);
dbconn = DriverManager.getConnection(url, uname, dpass);
Statement statement = dbconn.createStatement();
ResultSet results = statement.executeQuery(query);
if(results != null) {
ResultSetMetaData metadata = results.getMetaData();
int columns = metadata.getColumnCount();
result = " <tr>\n\r";
for (int i = 1; i <= columns; i++) {
result += " <td>" + metadata.getColumnLabel(i) + "</td>\n\r";
}
result += " </tr>\n\r";
/* Loop through the columns and append data to our table */
while(results.next()) {
result += " <tr>\n\r";
for (int i = 1; i <= columns; i++) {
result += " <td>" + results.getObject(i).toString() + "</td>\n\r";
}
result += " </tr>\n\r";
}
}
results.close();
statement.close();
} catch (ClassNotFoundException e) {
result = " <tr><td> Error creating database drive class!";
result += " <br/>" + e.toString() + "</td></tr>";
} catch (SQLException e) {
result = " <tr><td> Error processing the SQL!";
result += " <br/>" + e.toString()+ "</td></tr>";
} finally {
try {
if (dbconn != null) {
dbconn.close(); }
} catch (SQLException e) {
result = " <tr><td> Error in closing connection.";
result += " <br/>" + e.toString() + "</td></tr>";
}
}
return result;
}
}
----lab6b.jsp-------<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%><jsp:useBean id="links" class="webtech.lab6.links" /><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd"><html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Lab 6</title>
</head>
<body> <h3>The Result was:</h3>
<table border="1">
<%=links.getLinks()%>
</table>
</body>
</html>-------links.java-------------package webtech.lab6;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;public class links {
public String getLinks() {
String result = "<tr><td>Nothing Happened</td></tr>";
String query = "select * from links;";
String url = "jdbc:mysql://mysql0.ee.surrey.ac.uk:3306/webtech";
String driver = "org.gjt.mm.mysql.Driver";
String uname = "webtech";
String dpass = "webtech";
Connection dbconn = null;
try {
Class.forName(driver);
dbconn = DriverManager.getConnection(url, uname, dpass);
Statement statement = dbconn.createStatement();
ResultSet results = statement.executeQuery(query);
if(results != null) {
ResultSetMetaData metadata = results.getMetaData();
int columns = metadata.getColumnCount();
result = " <tr>\n\r";
for (int i = 1; i <= columns; i++) {
result += " <td>" + metadata.getColumnLabel(i) + "</td>\n\r";
}
result += " </tr>\n\r";
/* Loop through the columns and append data to our table */
while(results.next()) {
result += " <tr>\n\r";
for (int i = 1; i <= columns; i++) {
result += " <td>" + results.getObject(i).toString() + "</td>\n\r";
}
result += " </tr>\n\r";
}
}
results.close();
statement.close();
} catch (ClassNotFoundException e) {
result = " <tr><td> Error creating database drive class!";
result += " <br/>" + e.toString() + "</td></tr>";
} catch (SQLException e) {
result = " <tr><td> Error processing the SQL!";
result += " <br/>" + e.toString()+ "</td></tr>";
} finally {
try {
if (dbconn != null) {
dbconn.close(); }
} catch (SQLException e) {
result = " <tr><td> Error in closing connection.";
result += " <br/>" + e.toString() + "</td></tr>";
}
}
return result;
}
}
import java.sql.SQLException;import javax.sql.DataSource;public class MyDataSource {
private DataSource dataSource;
private final ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
//这里用的是注入，如果不用注入，就用jndi找就行了
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
} public Connection getCurrentConnection() {
Connection conn = tl.get();
try {
if (conn == null) {
conn = this.dataSource.getConnection();
tl.set(conn);
}
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
package org.flying11.daoI;import org.apache.log4j.Logger;import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;import org.flying11.common.ReflectUtils;
import org.flying11.dao.BaseDAO;public abstract class BaseDAOI<E extends Serializable, PK> implements
BaseDAO<E, PK> {
private static final Logger logger = Logger.getLogger(BaseDAOI.class); protected abstract Connection getConnection(); public int add(E clazz) {
String table=ReflectUtils.getClassSimpleName(clazz.getClass());
String[] colums=ReflectUtils.getPropertys(clazz.getClass());
Map<String,String> map=ReflectUtils.getValues(clazz);
try{
String cnames="";
for(int i=0,len=colums.length;i<len;i++){
cnames+=colums[i]+",";
}
String values="'";
for(int i=0,len=colums.length;i<len;i++){
values+=map.get(colums[i])+"','";
}
String sql="insert "+table+"("+cnames.substring(0,cnames.length()-1)+")"
+ " values ("+ values.substring(0,values.length()-2) +")";
PreparedStatement ps=getConnection().prepareStatement(sql);
logger.info(sql);
int i=ps.executeUpdate();
return i;
}catch(SQLException e){
throw new RuntimeException(e);
}
} public int delById(PK id) {
// TODO Auto-generated method stub
return 0;
} public int findById(PK id) {
// TODO Auto-generated method stub
return 0;
} public List<E> list() {
// TODO Auto-generated method stub
return null;
} public int update(E clazz) {
// TODO Auto-generated method stub
return 0;
}}
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
package org.flying11.daoI;import java.sql.Connection;import org.flying11.common.MyDataSource;
import org.flying11.dao.UserDAO;
import org.flying11.entity.Users;public class UserDAOI extends BaseDAOI<Users, Long> implements UserDAO {
private MyDataSource dataSource; public void setDataSource(MyDataSource dataSource) {
this.dataSource = dataSource;
} protected Connection getConnection() {
return this.dataSource.getCurrentConnection();
}
}
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
package org.flying11.dao;import java.io.Serializable;
import java.util.List;public interface BaseDAO<E extends Serializable,PK> {
int add(E clazz);
int delById(PK id);
int update(E clazz);
int findById(PK id);
List<E> list();
}
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
package org.flying11.dao;import org.flying11.entity.Users;public interface UserDAO extends BaseDAO<Users, Long> {
}＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
package org.flying11.common;import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;import org.apache.log4j.Logger;public class ReflectUtils {
private static final Logger logger = Logger.getLogger(ReflectUtils.class); public static String[] getPropertys(Class<?> clazz) {
Method[] methods = clazz.getMethods();
List<String> re = new ArrayList<String>();
for (int i = 0, len = methods.length; i < len; i++) {
String name = methods[i].getName();
if (name.startsWith("get") && "getClass" != name) {
re.add((char) (name.charAt(3) + 32) + name.substring(4));
}
}
return re.toArray(new String[1]);
} public static String getClassSimpleName(Class<?> clazz) {
return clazz.getSimpleName();
} public static Map<String, String> getValues(Object obj) {
Method[] methods = obj.getClass().getMethods();
Map<String, String> re = new HashMap<String, String>();
try {
for (int i = 0, len = methods.length; i < len; i++) {
String name = methods[i].getName();
if (name.startsWith("get") && "getClass" != name) {
String key = (char) (name.charAt(3) + 32)
+ name.substring(4);
re.put(key, getValue(methods[i], obj));
}
}
} catch (Exception e) {
e.printStackTrace();
}
return re;
} private static String getValue(Method m, Object obj) throws Exception {
String typeName = m.getReturnType().getName();
Object value = m.invoke(obj, null);
if (value != null) {
if (typeName == Date.class.getName()) {
Date date = (Date) value;
if (date != null)
return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")
.format(date);
}
return value.toString();
}
return "";
}
}这个例子是不能运行的，你得改，因为我用的 spring的DI如果你用tomcat做服务器数据源你也得写上
存为Context.xml，注意大小写，当然这是java在所有地方都要求注意的，放到你工程的META-INF文件夹下<?xml version="1.0" encoding="UTF-8"?>
<Context path="/video" docBase="/video" crossContext="true" reloadable="true" debug="1">
<Resource name="jdbc/dataSource" auth="Container"
type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://192.168.1.197:3306/video"
username="flying" password="abc123" maxActive="10" maxIdle="10" maxWait="5000"/>
</Context>在你的工程web.xml中加上<resource-ref>
<res-ref-name>jdbc/dataSource</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>你还是得改，这只是个思路
import java.util.Set;
import java.util.Iterator;
import java.util.List;
import java.util.HashSet;
import java.util.HashMap;
import java.util.ArrayList;
import java.sql.*;
import com.mysql.jdbc.Driver;
public class BusinessService{
private String dbUrl ="jdbc:mysql://localhost:3306/SAMPLEDB";
private String dbUser="root";
private String dbPwd="1234"; public BusinessService() throws Exception{
//加载MySQL数据库驱动程序
Class.forName("com.mysql.jdbc.Driver");
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
} public Connection getConnection()throws Exception{
//获得一个数据库连接
return java.sql.DriverManager.getConnection(dbUrl,dbUser,dbPwd);
} /**
* 持久化参数指定的Customer对象，并且级联持久化与它关联的Order对象。
* 如果Customer对象的name属性为null，或者Order对象的orderNumber属性为null，
* 会抛出BusinessException
*/
public void saveCustomer(Customer customer) throws Exception {
Connection con=null;
PreparedStatement stmt=null;
try {
con=getConnection(); //获得数据库连接 //开始一个数据库事务
con.setAutoCommit(false); //以下是业务逻辑代码，检查客户姓名是否为空
if(customer.getName()==null)
throw new BusinessException("客户姓名不允许为空"); //以下是数据访问代码，持久化Customer对象 //为新的CUSTOMERS记录分配惟一的ID
long customerId=getNextId(con,"CUSTOMERS");
//把Customer对象映射为面向关系的SQL语句
stmt=con.prepareStatement("insert into CUSTOMERS(ID,NAME,AGE) values(?,?,?)");
stmt.setLong(1,customerId);
stmt.setString(2,customer.getName());
stmt.setInt(3,customer.getAge());
stmt.execute(); Iterator iterator =customer.getOrders().iterator();
while (iterator.hasNext() ) {
//以下是业务逻辑代码，检查订单编号是否为空
Order order=(Order)iterator.next();
if(order.getOrderNumber()==null)
throw new BusinessException("订单编号不允许为空"); //以下是数据访问代码，级联持久化Order对象 //为新的ORDERS记录分配惟一的ID
long orderId=getNextId(con,"ORDERS");
//把Order对象映射为面向关系的SQL语句
stmt=con.prepareStatement("insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID)values(?,?,?,?)");
stmt.setLong(1,orderId);
stmt.setString(2,order.getOrderNumber());
stmt.setDouble(3,order.getPrice());
stmt.setLong(4,customerId);
stmt.execute();
} //提交数据库事务
con.commit(); }catch(Exception e){
e.printStackTrace();
try{//如果出现异常，撤销整个事务
con.rollback();
}catch(SQLException sqlex){
sqlex.printStackTrace(System.out);
}
throw e;
}finally{
try{
stmt.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
} /**
* 更新参数指定的Customer对象
*/
public void updateCustomer(Customer customer) throws Exception {
Connection con=null;
PreparedStatement stmt=null;
try {
con=getConnection(); //获得数据库连接 //开始一个数据库事务
con.setAutoCommit(false); //以下是数据访问代码，更新Customer对象 //把Customer对象映射为面向关系的SQL语句
stmt=con.prepareStatement("update CUSTOMERS set NAME=?,AGE=? where ID=?");
stmt.setString(1,customer.getName());
stmt.setInt(2,customer.getAge());
stmt.setLong(3,customer.getId().longValue());
stmt.execute(); //提交数据库事务
con.commit(); }catch(Exception e){
try{//如果出现异常，撤销整个事务
con.rollback();
}catch(SQLException sqlex){
sqlex.printStackTrace(System.out);
}
throw e;
}finally{
stmt.close();
con.close();
}
}
/**
* 删除参数指定的Customer对象，并且级联删除与它关联的Order对象。
*/
public void deleteCustomer(Customer customer) throws Exception {
Connection con=null;
PreparedStatement stmt=null;
try {
con=getConnection(); //获得数据库连接 //开始一个数据库事务
con.setAutoCommit(false); //先删除和Customer对象关联的Order对象
stmt=con.prepareStatement("delete from ORDERS where "
+"CUSTOMER_ID=?" );
stmt.setLong(1,customer.getId().longValue());
stmt.executeUpdate(); //删除Customer对象
stmt=con.prepareStatement("delete from CUSTOMERS where "
+"ID=?" );
stmt.setLong(1,customer.getId().longValue());
stmt.executeUpdate(); //提交数据库事务
con.commit(); }catch(Exception e){
try{//如果出现异常，撤销整个事务
con.rollback();
}catch(SQLException sqlex){
sqlex.printStackTrace(System.out);
}
throw e;
}finally{
try{
stmt.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
/*
* 根据OID加载一个Customer对象
*/
public Customer loadCustomer (long customerId) throws Exception {
Connection con=null;
PreparedStatement stmt=null;
ResultSet rs=null;
try {
con=getConnection(); //获得数据库连接 //以下是数据访问代码，加载Customer对象
stmt=con.prepareStatement("select ID,NAME,AGE from CUSTOMERS where ID=?");
stmt.setLong(1,customerId);
rs=stmt.executeQuery(); if(rs.next()) {
Customer customer=new Customer();
customer.setId(new Long(rs.getLong(1)));
customer.setName(rs.getString(2));
customer.setAge(rs.getInt(3));
return customer;
}else{
throw new BusinessException("OID为"+customerId+"的Customer对象不存在");
} }finally{
try{
rs.close();
stmt.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
} /*
* 按照姓名查询满足条件的Customer对象，同时加载与它关联的Order对象
*/
public List findCustomerByName(String name) throws Exception{
HashMap map=new HashMap();
List result=new ArrayList(); Connection con=null;
PreparedStatement stmt=null;
ResultSet rs=null;
try{
con=getConnection(); //获得数据库连接 String sqlString=" select c.ID CUSTOMER_ID,c.NAME,c.AGE,o.ID ORDER_ID, "
+"o.ORDER_NUMBER,o.PRICE "
+"from CUSTOMERS c left outer join ORDERS o "
+"on c.ID =o.CUSTOMER_ID where c.NAME=?";
stmt = con.prepareStatement(sqlString);
stmt.setString(1,name); //绑定参数
rs=stmt.executeQuery();
while (rs.next())
{
//编历JDBC ResultSet结果集
Long customerId =new Long( rs.getLong(1));
String customerName= rs.getString(2);
int customerAge= rs.getInt(3);
Long orderId =new Long( rs.getLong(4));
String orderNumber= rs.getString(5);
double price=rs.getDouble(6); //映射Customer对象
Customer customer=null;
if(map.containsKey(customerId))
//如果在map中已经存在OID匹配的Customer对象，就获得此对象的引用，这样
//就避免创建重复的Customer对象。
customer=(Customer)map.get(customerId);
else{
//如果在map中不存在OID匹配的Customer对象，就创建一个Customer对象，
//然后把它保存到map中。
customer=new Customer();
customer.setId(customerId);
customer.setName(customerName);
customer.setAge(customerAge);
map.put(customerId,customer);
} //映射Order对象
Order order=new Order();
order.setId(orderId);
order.setOrderNumber(orderNumber);
order.setPrice(price); //建立Customer对象与Order对象的关联关系
customer.getOrders().add(order);
order.setCustomer(customer);
}
//把map中所有的Customer对象加入到result集合中
Iterator iter =map.values().iterator();
while ( iter.hasNext() ) {
result.add(iter.next());
}
return result;
}finally{
try{
rs.close();
stmt.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
} /**
* 生成一个新的主键值，取值为表的当前最大主键值+1，如果表不包含记录，就返回1
*/
private long getNextId(Connection con,String tableName) throws Exception {
long nextId=0;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = con.prepareStatement("select max(ID) from "+tableName);
rs = stmt.executeQuery();
if ( rs.next() ) {
nextId = rs.getLong(1) + 1;
if ( rs.wasNull() ) nextId = 1;
}
else {
nextId = 1;
}
return nextId;
}finally {
try{
rs.close();
stmt.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
}