c3p0-config.xml設定檔 完成查詢資料庫的流程

設定檔

<?xml version="1.0" encoding="UTF-8"?><c3p0-config><!-- 預設配置，只可以出現一次 --><default-config><!-- 連線逾時設定30秒 --> <property name="checkoutTimeout">3000</property><!-- 30秒檢查一次connection的空閑 --><property name="idleConnectionTestPeriod">30</property><!--初始化的池大小 --> <property name="initialPoolSize">2</property><!-- 最多的一個connection空閑時間 -->  <property name="maxIdleTime">30</property><!-- 最多可以有多少個串連connection --><property name="maxPoolSize">5</property><!-- 最少的池中有幾個串連 --><property name="minPoolSize">2</property><!-- 批處理的語句 --><property name="maxStatements">50</property><!-- 每次增長几個串連 --><property name="acquireIncrement">3</property><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl"><![CDATA[jdbc:mysql://127.0.0.1:3306/bookstore?useUnicode=true&characterEncoding=UTF-8]]></property><property name="user">root</property><property name="password">root</property></default-config> </c3p0-config> 

獲得資料庫連結

import java.sql.Connection;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;public class DataSourceUtils {private static DataSource ds;//聲明ThreadLocal容器物件private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();static {ds = // 預設的讀取c3p0-config.xml中預設配置 new ComboPooledDataSource(); }public static DataSource getDatasSource() {return ds; }   //提供一個返回Connection的方法public static Connection getConn(){Connection con = null;//先從tl中擷取資料con = tl.get();if(con==null){try{con = getDatasSource().getConnection();//放到tltl.set(con); }catch(Exception e){e.printStackTrace();}}return con; }public static void remove() {tl.remove();}}

jsp

<td align="center" valign="top" style="width:120px;">  <a  target="dataFrame" href="<c:url value='/BookServlet'/>">全部</a><br/>  <c:forEach items="${types}" var="t">                      <a target="dataFrame" href="<c:url value='/BookServlet?typeid=${t.id}'/>">${t.name}</a><br/>  </c:forEach>    </td> 

BookServlet

private BookService service = new BookService();/** * 轉寄到books.jsp * 查詢某些圖書 */@Override public void execute(HttpServletRequest req, HttpServletResponse resp)throws Exception {  String typeid=req.getParameter("typeid");//查詢List<Book> list = service.query(typeid);//封裝到reqreq.setAttribute("list",list);//轉寄到books.jspreq.getRequestDispatcher("/jsps/book/books.jsp").forward(req, resp);} 

public class BookService {private BookDao dao = new BookDao();public List<Book> query(String typeid){return dao.query(typeid);}

import java.util.List;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import cn.itcast.domain.Book;import cn.itcast.utils.QueryRunner;import static cn.itcast.utils.DataSourceUtils.*;public class BookDao {/** * 查詢所有圖書 * 參數為類型id */public List<Book> query(String typeid){String sql = "select * from books";if(typeid!=null){sql = "select b.* from books b inner join booktype bt on b.id=bt.bookid" +" where bt.typeid='"+typeid+"'";}QueryRunner run = new QueryRunner(getDatasSource());List<Book> list = run.query(sql,new BeanListHandler<Book>(Book.class));return list;   }   

QueryRunner

@Overridepublic <T> T query(String sql, ResultSetHandler<T> rsh){try {return super.query(sql, rsh);} catch (SQLException e) {throw new RuntimeException(e.getMessage(),e);}}

跳轉jsp

<body style="margin-top:0px;">  以下是所有圖書<br/><c:forEach items="${list}" var="book"><div>        <a href="<c:url value='/BookServlet?cmd=detail&bookid=${book.id}'/>"><img src="<c:url value='/images/${book.img}'/>"></a> <br/>${book.name}  <c:choose>    <c:when test="${book.rebate==1}">${book.price} </c:when><c:otherwise><font style="text-decoration: line-through;">${book.price}</font> <fmt:formatNumber value="${book.price*book.rebate}" pattern="#,###.00"/></c:otherwise></c:choose>元</div></c:forEach>   </body>