c3p0-config.xml配置文件 完成查询数据库的流程

时间：2023-02-09 21:52:22

配置文件

<?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();
//放到tl
tl.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);
//封装到req
req.setAttribute("list",list);
//转发到books.jsp
req.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 

@Override
public <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>


标签：

相关文章