数据库名为:stdb
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`sex` varchar(20) DEFAULT NULL,
`score` double(20,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES (1,'李少荣','女',50.00),(3,'周强','男',95.00),(4,'王晓婷','女',55.00),(5,'张秀花','女',100.00),(6,'顾会','女',50.00),(7,'赵天一','男',32.00),(8,'testadd','男',100.00);
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:forEach items="${studentList }" var="student">
${student.id }--${student.name }--${student.sex }--${student.score }<br>
</c:forEach>
</body>
</html>
servlet
package web;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import domain.Student;
import service.StudentService;
public class StudentList extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//调用service层获得学生数据
StudentService ss = new StudentService();
List<Student> studentList = null;
try {
studentList = ss.getAllStudent();
} catch (SQLException e) {
e.printStackTrace();
}
//将学生列表传递到request域中
request.setAttribute("studentList", studentList);
request.getRequestDispatcher("studentList.jsp").forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
service
package service;
import java.sql.SQLException;
import java.util.List;
import dao.StudentDao;
import domain.Student;
public class StudentService {
public List<Student> getAllStudent() throws SQLException {
//调用dao层获得学生数据
StudentDao studentDao = new StudentDao();
List<Student> studentList = studentDao.getAllStudent();
return studentList;
}
}
dao
package dao;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import domain.Student;
import utils.C3P0Utils;
public class StudentDao {
public List<Student> getAllStudent() throws SQLException {
//将所有学生数据返回到service层
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from student";
List<Student> studentList = qr.query(sql, new BeanListHandler<Student>(Student.class));
return studentList;
}
}
bean
package domain;
public class Student {
private int id;
private String name;
private String sex;
private double score;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public double getScore() {
return score;
}
public void setScore(double score) {
this.score = score;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", score=" + score + "]";
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + id;
result = prime * result + ((name == null) ? 0 : name.hashCode());
long temp;
temp = Double.doubleToLongBits(score);
result = prime * result + (int) (temp ^ (temp >>> 32));
result = prime * result + ((sex == null) ? 0 : sex.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Student other = (Student) obj;
if (id != other.id)
return false;
if (name == null) {
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
if (Double.doubleToLongBits(score) != Double.doubleToLongBits(other.score))
return false;
if (sex == null) {
if (other.sex != null)
return false;
} else if (!sex.equals(other.sex))
return false;
return true;
}
}
c3p0xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///stdb</property>
<property name="user">root</property>
<property name="password">Aa123456</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<named-config name="mysql">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///stdb</property>
<property name="user">root</property>
<property name="password">Aa123456</property>
</named-config>
</c3p0-config>
datasource utils
package utils;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Utils {
private static DataSource dataSource = new ComboPooledDataSource("c3p0-config");;
/*
* C3P0工具类
*/
static {
dataSource = getDataSource();
}
public static DataSource getDataSource() {
//返回资源池
return dataSource;
}
public static Connection getConnection() {
//返回Connection
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
导入的jar包
运行结果