1. 需要的包:
c3p0.jar
commons-dbutils.jar
mysql-connector-java.jar
mchange-commons-java.jar
2 . 配置文件
工程根目录(src/c3p0-config.xml)
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 默认配置,如果没有指定则使用这个配置 -->
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///goods?serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="checkoutTimeout">1200</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">20</property>
<property name="maxStatements">0</property>
<user-overrides user="test-user">
<property name="maxPoolSize">10</property>
<property name="minPoolSize">1</property>
<property name="maxStatements">0</property>
</user-overrides>
</default-config>
<!-- 命名配置 -->
<name-config name="goods">
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///goods?serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
<property name="maxStatements">0</property>
<property name="maxStatementsPerConnection">5</property>
</name-config>
</c3p0-config>
辅助类:C3p0Utils
/**
*
*/
package com.igeek.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;
/**
* @author Administrator
*
*/
public class C3p0Utils {
/***
* 创建连接对象
*/
private static ComboPooledDataSource dataSource = new ComboPooledDataSource("goods");
//本地线程池:缓存连接对象
private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
public static DataSource getDataSource()
{
return dataSource;
}
public static Connection getConnection() throws SQLException{
//先尝试从本地线程池中获取连接对象
Connection connection = threadLocal.get();
if(connection == null)
{
//如果时第一次调用,则本地线程池中没有现成的连接对象
//则从连接池中获取一个新的连接对象
connection = dataSource.getConnection();
threadLocal.set(connection);
}
return connection;
}
public static void close(ResultSet rs,Statement statement,Connection connection)
{
if(rs!= null)
{
try {
rs.close();
}catch(SQLException e)
{
e.printStackTrace();
}
}
if(statement != null)
{
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection != null)
{
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
调用的案例
/**
*
*/
package com.igeek.domain;
import java.io.Serializable;
/**
* @author Administrator
*
*/
public class Member implements Serializable{
private Integer id;
private String username;
private String password;
public int getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "Member [id=" + id + ", username=" + username + ", password=" + password + "]";
}
public Member() {
super();
// TODO Auto-generated constructor stub
}
}
/**
*
*/
package com.igeek.dao;
import java.sql.SQLException;
import com.igeek.domain.Member;
/**
* @author Administrator
*
*/
public interface MemberDao {
public Member selectByName(String name) throws SQLException;
public Member selectById(Integer id) throws SQLException;
}
/**
*
*/
package com.igeek.dao.imp;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import com.igeek.dao.MemberDao;
import com.igeek.dao.User;
import com.igeek.domain.Member;
import com.igeek.utils.C3p0Utils;
/**
* @author Administrator
*
*/
public class MemberDaoImpl implements MemberDao{
@Override
public Member selectByName(String name) throws SQLException {
// TODO Auto-generated method stub
QueryRunner qr = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select id , username,password from member where username=?";
Object[] params = {name};
Member member = qr.query(sql, new BeanHandler<Member>(Member.class),params);
return member;
}
@Override
public Member selectById(Integer id) throws SQLException {
// TODO Auto-generated method stub
QueryRunner qr = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select id , username,password from member where id=?";
Object[] params = {id};
Member member = qr.query(sql, new BeanHandler<Member>(Member.class),params);
return member;
}
}
/**
*
*/
package com.igeek.service;
import com.igeek.domain.Member;
/**
* @author Administrator
*业务操作
*/
public interface MemberService {
public Member login(String username,String password);
}
/**
*
*/
package com.igeek.service.impl;
import java.sql.SQLException;
import com.igeek.dao.MemberDao;
import com.igeek.dao.imp.MemberDaoImpl;
import com.igeek.domain.Member;
import com.igeek.service.MemberService;
/**
* @author Administrator
*
*/
public class MemberServiceImpl implements MemberService {
private MemberDao memberDao = new MemberDaoImpl();
@Override
public Member login(String username, String password) {
//
try {
Member member = memberDao.selectByName(username);
if(member != null)
{
if(member.getPassword().equals(password))
{
return member;
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
}
/**
*
*/
package com.igeek.web;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.igeek.domain.Member;
import com.igeek.service.MemberService;
import com.igeek.service.impl.MemberServiceImpl;
/**
* @author Administrator
*
*/
public class LoginServlet extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = -66914447102026201L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String username = req.getParameter("username");
String password = req.getParameter("password");
MemberService userService = new MemberServiceImpl();
Member member = userService.login(username, password);
resp.setContentType("text/ html;charset=utf-8");
PrintWriter out = resp.getWriter();
if(member == null)
{
out.append("当前的账户和密码错误!");
}
else
{
out.append(member.toString());
}
out.flush();
out.close();
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req,resp);
}
}
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>登录页</title>
</head>
<body>
<form action="${pageContext.request.contextPath }/register" method="post">
<table >
<tr>
<th>用户名:</th>
<th><input type="text" name="username" /> </th>
</tr>
<tr>
<th>密码: </th>
<th><input type="password" name="password"/></th>
</tr>
<tr >
<th colspan="2" style="align-self: center;">
<input type="submit" value="注册" style="margin-top: 20px;">
</th>
</tr>
</table>
</form>
</body>
</html>
web.xml
<servlet>
<servlet-name>Login</servlet-name>
<servlet-class>com.igeek.web.LoginServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>Login</servlet-name>
<url-pattern>/login</url-pattern>
</servlet-mapping>