java-el+jstl+jsbc综合示例

项目结构：

项目展示：

数据库：

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.5.53 : Database - product
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`product` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `product`;

/*Table structure for table `product` */

DROP TABLE IF EXISTS `product`;

CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `pname` varchar(20) NOT NULL COMMENT '产品名称',
  `pprice` double NOT NULL COMMENT '产品价格',
  `pmark` varchar(255) DEFAULT NULL COMMENT '产品描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `product` */

insert  into `product`(`id`,`pname`,`pprice`,`pmark`) values (1,'iphone',5000.32,'苹果手机'),(2,'三星',2343.23,'三星手机'),(3,'华为',32432,'华为手机');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

 

GetAllDataDao.java

package com.gordon.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.gordon.domain.Product;
import com.gordon.utils.DataSourceUtils;

public class GetAllDataDao {

	public List<Product> getAllData() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		
		String sql = "select * from product";
		
		return qr.query(sql, new BeanListHandler<Product>(Product.class));
	}

}

Product.java:

package com.gordon.domain;

public class Product {
	private int id;
	private String pname;
	private double pprice;
	private String pmark;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getPname() {
		return pname;
	}

	public void setPname(String pname) {
		this.pname = pname;
	}

	public double getPprice() {
		return pprice;
	}

	public void setPprice(double pprice) {
		this.pprice = pprice;
	}

	public String getPmark() {
		return pmark;
	}

	public void setPmark(String pmark) {
		this.pmark = pmark;
	}
}

GetAllDataService.java:

package com.gordon.service;

import java.sql.SQLException;
import java.util.List;

import com.gordon.dao.GetAllDataDao;
import com.gordon.domain.Product;

public class GetAllDataService {

	public List<Product> getAllData() throws SQLException {
		return new GetAllDataDao().getAllData();
	}
	
}

ShowDataServlet.java:

package com.gordon.servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.gordon.domain.Product;
import com.gordon.service.GetAllDataService;

/**
 * 展示所有数据
 */
@WebServlet("/showdataservlet")
public class ShowDataServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	public ShowDataServlet() {
		super();
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		List<Product> list = null;
		try {
			list = new GetAllDataService().getAllData();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if(list.size() == 0) {
			request.setAttribute("msg", "没有数据显示！");
		} else {
			request.setAttribute("list", list);
		}
		
		System.out.println(list.size());
		
		request.getRequestDispatcher("show_data.jsp").forward(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}
}

DataSourceUtils.java:

package com.gordon.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSourceUtils {
	private static ComboPooledDataSource ds=new ComboPooledDataSource();
	
	/**
	 * 获取数据源
	 * @return 连接池
	 */
	public static DataSource getDataSource(){
		return ds;
	}
	
	/**
	 * 获取连接
	 * @return 连接
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException{
		return ds.getConnection();
	}
	
	
	
	/**
	 * 释放资源
	 * 
	 * @param conn
	 *            连接
	 * @param st
	 *            语句执行者
	 * @param rs
	 *            结果集
	 */
	public static void closeResource(Connection conn, Statement st, ResultSet rs) {
		closeResultSet(rs);
		closeStatement(st);
		closeConn(conn);
	}

	/**
	 * 释放连接
	 * 
	 * @param conn
	 *            连接
	 */
	public static void closeConn(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}

	}

	/**
	 * 释放语句执行者
	 * 
	 * @param st
	 *            语句执行者
	 */
	public static void closeStatement(Statement st) {
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			st = null;
		}

	}

	/**
	 * 释放结果集
	 * 
	 * @param rs
	 *            结果集
	 */
	public static void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}

	}
}

c3p0-config.xml:

<c3p0-config>
	<!-- 默认配置，如果没有指定则使用这个配置 -->
	<default-config>
		<!-- 基本配置 -->
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/product</property>
		<property name="user">root</property>
		<property name="password">root</property>
	
		<!--扩展配置-->
		<property name="checkoutTimeout">30000</property>
		<property name="idleConnectionTestPeriod">30</property>
		<property name="initialPoolSize">10</property>
		<property name="maxIdleTime">30</property>
		<property name="maxPoolSize">100</property>
		<property name="minPoolSize">10</property>
		<property name="maxStatements">200</property>
	</default-config> 
	
	
	<!-- 命名的配置 -->
	<named-config name="itcast">
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/xxxx</property>
		<property name="user">root</property>
		<property name="password">1234</property>
		
		
		<!-- 如果池中数据连接不够时一次增长多少个 -->
		<property name="acquireIncrement">5</property>
		<property name="initialPoolSize">20</property>
		<property name="minPoolSize">10</property>
		<property name="maxPoolSize">40</property>
		<property name="maxStatements">20</property>
		<property name="maxStatementsPerConnection">5</property>
	</named-config>
</c3p0-config> 

index.jsp:

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!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>Insert title here</title>
</head>
<body>
	<a href="${ pageContext.request.contextPath }/showdataservlet">展示所有数据</a>
</body>
</html>

show_data.jsp:

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!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>Insert title here</title>
</head>
<body>
	<c:choose>
		<c:when test="${ not empty requestScope.list }">
			<table>
				<tr>
					<td>id</td>
					<td>产品名称</td>
					<td>产品价格</td>
					<td>产品的简介</td>
				</tr>
				<c:forEach items="${ requestScope.list }" var="p">
					<tr>
						<td>${ p.id }</td>
						<td>${ p.pname }</td>
						<td>${ p.pprice }</td>
						<td>${ p.pmark }</td>
					</tr>
				</c:forEach>
			</table>
		</c:when>
		<c:otherwise>
			<span style="color: red;">${ requestScope.msg }</span>
		</c:otherwise>
	</c:choose>
</body>
</html>