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

最新推荐文章于 2019-12-13 09:36:59 发布

angel豆蔻年华

最新推荐文章于 2019-12-13 09:36:59 发布

阅读量1.4w

点赞数

分类专栏： mysql 文章标签： c3p0

本文链接：https://blog.csdn.net/u012635819/article/details/16878127

版权

mysql 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

配置文件

<?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>