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

最新推荐文章于 2022-11-24 17:00:54 发布

xiaosha_

最新推荐文章于 2022-11-24 17:00:54 发布

阅读量7.6k

点赞数 2

分类专栏： JavaEE 数据库文章标签：数据库 java ee 数据库连接池

本文链接：https://blog.csdn.net/xiaosha_/article/details/78495189

版权

JavaEE 同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

数据库

5 篇文章 0 订阅

订阅专栏

一、工具准备
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','男','软件开发工程师','zhangsan1@126.com',4000,1);
INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三2','男','软件开发工程师','zhangsan2@126.com',6000,1);
INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三3','男','软件开发工程师','zhangsan3@126.com',6500,2);
INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三4','男','软件开发工程师','zhangsan4@126.com',5000,2);
INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三5','男','软件开发工程师','zhangsan5@126.com',5800,3);
INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三6','男','软件开发工程师','zhangsan6@126.com',5500,3);
INSERT INTO employee(NAME,gender,title,email,salary,deptId) VALUES('张三7','男','软件开发工程师','zhangsan7@126.com',7000,3);

SELECT * FROM department;
SELECT * FROM employee;


SELECT * FROM employee LIMIT 2,2;