JavaEE 简单连接数据库的那些方法（1）

一、工具准备
c3p0-0.9.1.2.jar (数据库连接池)
commons-dbutils-1.2.jar （元数据）
mysql-connector-java-5.1.42-bin.jar （jdbc驱动）

二、简单实例-数据分页

c3p0包的使用需要xml配置文件

<c3p0-config>    <!-- 默认配置 -->  <default-config>        <!-- 连接参数 &amp; 替代 &-->        <property name="jdbcUrl">jdbc:mysql://localhost:3306/day20?characterEncoding=utf8&amp;useSSL=false</property>        <property name="user">root</property>        <property name="password">root</property>        <property name="driverClass">com.mysql.jdbc.Driver</property>        <!-- 连接池参数 -->        <property name="initialPoolSize">5</property>        <property name="maxPoolSize">12</property>        <property name="checkoutTimeout">5000</property>        <property name="minPoolSize">3</property>  </default-config>    <!-- 命名配置 -->  <named-config name="oracle">         <!-- 连接参数 -->        <property name="jdbcUrl">jdbc:oracle:thin:@localhost:1521:orcl</property>        <property name="user">eric</property>        <property name="password">123456</property>        <property name="driverClass">oracle.jdbc.driver.OracleDriver</property>        <!-- 连接池参数 -->        <property name="initialPoolSize">5</property>        <property name="maxPoolSize">12</property>        <property name="checkoutTimeout">5000</property>        <property name="minPoolSize">3</property>   </named-config></c3p0-config>

实体类

package gz.nuist.entity;public class Employee {    private int id;    private String name;    private String gender;    private String title;    private String email;    private double salary;    /**    *注意 dbutils元数据 必须要有默认构造函数！！！    **/    public Employee() {        super();    }    public Employee(int id, String name, String gender, String title, String email, double salary) {        super();        this.id = id;        this.name = name;        this.gender = gender;        this.title = title;        this.email = email;        this.salary = salary;    }    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 getGender() {        return gender;    }    public void setGender(String gender) {        this.gender = gender;    }    public String getTitle() {        return title;    }    public void setTitle(String title) {        this.title = title;    }    public String getEmail() {        return email;    }    public void setEmail(String email) {        this.email = email;    }    public double getSalary() {        return salary;    }    public void setSalary(double salary) {        this.salary = salary;    }    @Override    public String toString() {        return "Employee [id=" + id + ", name=" + name + ", gender=" + gender + ", title=" + title + ", email=" + email                + ", salary=" + salary + "]";    }}

pageBean类

package gz.nuist.entity;import java.util.List;public class pageBean {    List<Employee> data;    int firstPage;    int prePage;    int nextPage;    int totalPage;    int currentPage;    int totalCount;    int pageSize;    public List<Employee> getData() {        return data;    }    public void setData(List<Employee> data) {        this.data = data;    }    /**     * 首页     * @return     */    public int getFirstPage() {        return 1;    }    public void setFirstPage(int firstPage) {        this.firstPage = firstPage;    }    /**     * 上一页     * @return     */    public int getPrePage() {        return this.getCurrentPage()==this.getFirstPage()?1:this.getCurrentPage()-1;    }    public void setPrePage(int prePage) {        this.prePage = prePage;    }    /**     * 下一页     * @return     */    public int getNextPage() {        return this.getCurrentPage()==this.getTotalPage()?this.getTotalPage():this.getCurrentPage()+1;    }    public void setNextPage(int nextPage) {        this.nextPage = nextPage;    }    /**     * 总页数     * @return     */    public int getTotalPage() {        return this.getTotalCount()%this.getPageSize()==0?this.getTotalCount()/this.getPageSize():this.getTotalCount()/this.getPageSize()+1;    }    public void setTotalPage(int totalPage) {        this.totalPage = totalPage;    }    public int getCurrentPage() {        return currentPage;    }    public void setCurrentPage(int currentPage) {        this.currentPage = currentPage;    }    public int getTotalCount() {        return totalCount;    }    public void setTotalCount(int totalCount) {        this.totalCount = totalCount;    }    public int getPageSize() {        return pageSize;    }    public void setPageSize(int pageSize) {        this.pageSize = pageSize;    }}

其实从这才算开始……zzz
util工具类

package gz.nuist.util;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;/** * 连接池版本 * @author 30660 * */public class JdbcUtil {    //创建连接池的对象    private static DataSource ds = new ComboPooledDataSource();    /**     * 获取连接池对象     */    public static DataSource getDataSource(){        return ds;    }}

EmpDao 数据库操作类

package gz.nuist.dao;import java.sql.SQLException;import java.util.List;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;import gz.nuist.entity.Employee;import gz.nuist.util.JdbcUtil;/** * 员工的dao * @author 30660 * */public class EmpDao {    /**     * 查询当前页员工的数据的方法     */    public List<Employee> queryData(int currentPage,int pageSize){        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());        String sql="SELECT * FROM employee LIMIT ?,?";        int startNo = (currentPage-1)*pageSize;        try {            List<Employee> list = (List<Employee>) qr.query(sql, new BeanListHandler(Employee.class), new Object[]{startNo,pageSize});            return list;        } catch (SQLException e) {            e.printStackTrace();            throw new RuntimeException(e);        }    }    public int queryCount(){        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());        String sql="SELECT COUNT(*) FROM employee";        try {            Long count = (Long) qr.query(sql, new ScalarHandler());            return count.intValue();        } catch (SQLException e) {            e.printStackTrace();            throw new RuntimeException(e);        }    }    public static void main(String[] args) {        EmpDao dao = new EmpDao();//      List<Employee> data = dao.queryData(4, 2);//      //      for (Employee employee : data) {//          System.out.println(employee);//      }        System.out.println(dao.queryCount());    }}

servlet web服务类

package gz.nuist.web;import java.io.IOException;import java.util.ArrayList;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import gz.nuist.dao.EmpDao;import gz.nuist.entity.Employee;import gz.nuist.entity.pageBean;public class ListEmpServlet extends HttpServlet {    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {//      t1(request, response);        //1）封装            pageBean pageBean = new pageBean();            EmpDao empDao = new EmpDao();                       String currentPage = request.getParameter("currentPage");            if(currentPage==null || currentPage.equals("")){                currentPage="1";            }            pageBean.setCurrentPage(Integer.parseInt(currentPage));            pageBean.setTotalCount(empDao.queryCount());            pageBean.setPageSize(2);            List<Employee> list = empDao.queryData(pageBean.getCurrentPage(), pageBean.getPageSize());            pageBean.setData(list);            //2）放            request.setAttribute("pageBean", pageBean);            //3)转            request.getRequestDispatcher("/listEmp.jsp").forward(request, response);    }    private void t1(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {        //1）封装        pageBean pageBean = new pageBean();        List<Employee> list = new ArrayList<Employee>();        for(int i=0;i<=5;i++){            list.add(new Employee(i,"张三"+i,"男","软件工程师","zhangsan"+i+"@qq.com",5000+i*1000));        }         pageBean.setData(list);        pageBean.setFirstPage(1);        String currentPage = request.getParameter("currentPage");        if(currentPage==null || currentPage.equals("")){            currentPage="1";        }        pageBean.setCurrentPage(Integer.parseInt(currentPage));        pageBean.setPrePage(pageBean.getCurrentPage()==pageBean.getFirstPage()?1:pageBean.getCurrentPage()-1);        pageBean.setTotalCount(21);        pageBean.setPageSize(5);        pageBean.setTotalPage(pageBean.getTotalCount()%pageBean.getPageSize()==0?pageBean.getTotalCount()/pageBean.getPageSize():pageBean.getTotalCount()/pageBean.getPageSize()+1);        pageBean.setNextPage(pageBean.getCurrentPage()==pageBean.getTotalPage()?pageBean.getTotalPage():pageBean.getCurrentPage()+1);        //2）放        request.setAttribute("pageBean", pageBean);        //3)转        request.getRequestDispatcher("/listEmp.jsp").forward(request, response);    }    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {        doGet(request, response);    }}

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" isELIgnored="false"%><%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <base href="<%=basePath%>">    <title>My JSP 'listEmp.jsp' starting page</title>    <meta http-equiv="pragma" content="no-cache">    <meta http-equiv="cache-control" content="no-cache">    <meta http-equiv="expires" content="0">        <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">    <meta http-equiv="description" content="This is my page">    <!--    <link rel="stylesheet" type="text/css" href="styles.css">    -->  </head>  <body>    <table border="1" align="center" width="700px">        <tr>            <th>编号</th>            <th>姓名</th>            <th>性别</th>            <th>职位</th>            <th>邮箱</th>            <th>薪水</th>        </tr>        <c:forEach items="${pageBean.data }" var="emp" >            <tr>            <td>${emp.id }</td>            <td>${emp.name }</td>            <td>${emp.gender }</td>            <td>${emp.title }</td>            <td>${emp.email }</td>            <td>${emp.salary }</td>        </tr>        </c:forEach>        <tr>            <td align="center" colspan="6">                <a href="${pageContext.request.contextPath }/ListEmpServlet?currentPage=${pageBean.firstPage}">首页</a> &nbsp;                <a href="${pageContext.request.contextPath }/ListEmpServlet?currentPage=${pageBean.prePage}">上一页</a>&nbsp;                <a href="${pageContext.request.contextPath }/ListEmpServlet?currentPage=${pageBean.nextPage}">下一页</a>&nbsp;                <a href="${pageContext.request.contextPath }/ListEmpServlet?currentPage=${pageBean.totalPage}">页末</a>&nbsp;                当前为第${pageBean.currentPage }页/共${pageBean.totalPage }页&nbsp;                共${pageBean.totalCount }条数据&nbsp;每页显示${pageBean.pageSize }            </td>        </tr>    </table>  </body></html>

数据库生成

CREATE DATABASE day20;USE day20;CREATE TABLE department(    id INT PRIMARY KEY AUTO_INCREMENT,    deptName VARCHAR(20),    principal VARCHAR(20),    functional VARCHAR(20)      );CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    gender VARCHAR(2),    title VARCHAR(20),    email VARCHAR(20),    salary DOUBLE,    deptId INT,    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES department(id)   );-- 部门数据INSERT INTO department(deptName,principal,functional) VALUES('应用开发部','李经理','负责公司软件业务的开发');INSERT INTO department(deptName,principal,functional) VALUES('实施部','张经理','负责公司软件维护工作');INSERT INTO department(deptName,principal,functional) VALUES('秘书部','陈经理','负责公司行政事务及日常秘书工作');-- 员工数据INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三1','男','软件开发工程师','[e-mail]',4000,1);INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三2','男','软件开发工程师','[e-mail]',6000,1);INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三3','男','软件开发工程师','[e-mail]',6500,2);INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三4','男','软件开发工程师','[e-mail]',5000,2);INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三5','男','软件开发工程师','[e-mail]',5800,3);INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三6','男','软件开发工程师','[e-mail]',5500,3);INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三7','男','软件开发工程师','[e-mail]',7000,3);SELECT * FROM department;SELECT * FROM employee;SELECT * FROM employee LIMIT 2,2;