/*

Navicat MySQL Data Transfer

?

Source Server ????????: mysql

Source Server Version : 50549

Source Host ??????????: localhost:3306

Source Database ??????: dbcp

?

Target Server Type ???: MYSQL

Target Server Version : 50549

File Encoding ????????: 65001

?

Date: 2019-08-15 18:46:32

*/

?

SET FOREIGN_KEY_CHECKS=0;

?

-- ----------------------------

-- Table structure for player

-- ----------------------------

DROP TABLE IF EXISTS `player`;

CREATE TABLE `player` (

??`pid` int(11) NOT NULL AUTO_INCREMENT,

??`pname` varchar(255) NOT NULL,

??`pheight` double NOT NULL,

??`pweight` double NOT NULL,

??PRIMARY KEY (`pid`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

?

-- ----------------------------

-- Records of player

-- ----------------------------

INSERT INTO `player` VALUES ('1', '保羅喬治', '203', '110');

INSERT INTO `player` VALUES ('2', '卡哇伊', '198', '115');

INSERT INTO `player` VALUES ('3', '貝弗利', '190', '80');

INSERT INTO `player` VALUES ('4', '哈雷爾', '200', '120');

INSERT INTO `player` VALUES ('5', '祖巴茨', '216', '108');

INSERT INTO `player` VALUES ('6', '路易威廉姆斯', '180', '79');

?

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>

????<default-config>
????????<property name="driverClass">com.mysql.jdbc.Driver</property>
????????<property name="jdbcUrl">jdbc:mysql://localhost:3306/dbcp</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>
????</default-config>


</c3p0-config>

?

package com.henu.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JdbcUtil {

????private static ComboPooledDataSource ds = null;


????//在靜態代碼塊中創建數據庫連接池
????static{
????????try{
????????????ds = new ComboPooledDataSource("c3p0-config");//使用C3P0的命名配置來創建數據源

????????}catch (Exception e) {
????????????throw new ExceptionInInitializerError(e);
????????}
????}

????public static ComboPooledDataSource getDataSourse(){
????????return ds;
????}

????public static Connection getConnection() throws SQLException{
????????//從數據源中獲取數據庫連接
????????return ds.getConnection();
????}

????public static void close(Connection conn,Statement st,ResultSet rs){
????????if(rs!=null){
????????????try{
????????????????rs.close();
????????????}catch (Exception e) {
????????????????e.printStackTrace();
????????????}
????????}
????????if(st!=null){
????????????try{
????????????????st.close();
????????????}catch (Exception e) {
????????????????e.printStackTrace();
????????????}
????????}

????????if(conn!=null){
????????????try{
????????????????conn.close();
????????????}catch (Exception e) {
????????????????e.printStackTrace();
????????????}
????????}
????}
}

package com.henu.bean;

public class Player {
????private int pid;
????private String pname;
????private double pheight;
????private double pweight;

????public Player() {
????}

????public Player(String pname, double pheight, double pweight) {
????????this.pname = pname;
????????this.pheight = pheight;
????????this.pweight = pweight;
????}

????public Player(int pid, String pname, double pheight, double pweight) {
????????this.pid = pid;
????????this.pname = pname;
????????this.pheight = pheight;
????????this.pweight = pweight;
????}

????public int getPid() {
????????return pid;
????}

????public void setPid(int pid) {
????????this.pid = pid;
????}

????public String getPname() {
????????return pname;
????}

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

????public double getPheight() {
????????return pheight;
????}

????public void setPheight(double pheight) {
????????this.pheight = pheight;
????}

????public double getPweight() {
????????return pweight;
????}

????public void setPweight(double pweight) {
????????this.pweight = pweight;
????}

????@Override
????public String toString() {
????????return "Player{" +
????????????????"pid=" + pid +
????????????????", pname='" + pname + '\'' +
????????????????", pheight=" + pheight +
????????????????", pweight=" + pweight +
????????????????'}';
????}
}

?

package com.henu.dao;

import com.henu.bean.Player;

import java.util.List;

public interface PlayDao {
????int insert(Player player);
????int delete(int id);
????int update(Player player);
????List<Player> findAll();
????Player findPlayerById(int id);
????Player findPlayerByName(String name);
}

?

package com.henu.dao.impl;

import com.henu.bean.Player;
import com.henu.dao.PlayerDao;
import com.henu.util.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

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

public class PlayDaoImpl implements PlayerDao {
????@Override
????public int insert(Player player) {
????????QueryRunner qr = new QueryRunner(JdbcUtil.getDataSourse());
????????String sql = "insert into player values (null,?,?,?)";
????????int res = 0;
????????try {
????????????res = qr.update(sql,new Object[]{player.getPname(),player.getPheight(),player.getPweight()});
????????} catch (SQLException e) {
????????????e.printStackTrace();
????????}
????????return res;
????}

????@Override
????public int delete(int id) {
????????QueryRunner qr = new QueryRunner(JdbcUtil.getDataSourse());
????????String sql = "delete from player where pid = ?";
????????int res = 0;
????????try {
????????????res = qr.update(sql,id);
????????} catch (SQLException e) {
????????????e.printStackTrace();
????????}
????????return res;
????}

????@Override
????public int update(Player player) {
????????QueryRunner qr = new QueryRunner(JdbcUtil.getDataSourse());
????????String sql = "update player set pname=?,pheight=?,pweight=? where pid=?";
????????int res = 0;
????????try {
????????????res = qr.update(sql,new Object[]{player.getPname(),player.getPheight(),player.getPweight(),player.getPid()});
????????} catch (SQLException e) {
????????????e.printStackTrace();
????????}
????????return res;
????}

????@Override
????public List<Player> findAll() {
????????QueryRunner qr = new QueryRunner(JdbcUtil.getDataSourse());
????????String sql = "select * from player";
????????List<Player> list = null;
????????try {
????????????list = (List<Player>) qr.query(sql,new BeanListHandler<>(Player.class));
????????} catch (SQLException e) {
????????????e.printStackTrace();
????????}
????????return list;
????}

????@Override
????public Player findPlayerById(int id) {
????????QueryRunner qr = new QueryRunner(JdbcUtil.getDataSourse());
????????String sql = "select * from player where pid = ?";
????????Player player= null;
????????try {
????????????player = qr.query(sql, new BeanHandler<>(Player.class),id);
????????} catch (SQLException e) {
????????????e.printStackTrace();
????????}
????????return player;
????}

????@Override
????public Player findPlayerByName(String name) {
????????QueryRunner qr = new QueryRunner(JdbcUtil.getDataSourse());
????????String sql = "select * from player where pname = ?";
????????Player player= null;
????????try {
????????????player = qr.query(sql, new BeanHandler<>(Player.class),name);
????????} catch (SQLException e) {
????????????e.printStackTrace();
????????}
????????return player;
????}
}

package com.henu.test;

import com.henu.bean.Player;
import com.henu.dao.PlayerDao;
import com.henu.dao.impl.PlayDaoImpl;

import java.util.List;

public class Test {

????PlayerDao playerDao = new PlayDaoImpl();
????@org.junit.Test
????public void insert(){
????????int res = playerDao.insert(new Player("格林", 206, 103));
????????System.out.println(res>0 ? "insert success" : "insert error");
????}

????@org.junit.Test
????public void delete(){
????????int res = playerDao.delete(8);
????????System.out.println(res>0 ? "delete success" : "delete error");
????}

????@org.junit.Test
????public void update(){
????????int res = playerDao.update(new Player(6,"鮑爾默",178,80));
????????System.out.println(res>0 ? "update success" : "update error");
????}

????@org.junit.Test
????public void findall(){
????????List<Player> list = playerDao.findAll();
????????for (Player p: list
?????????????) {
????????????System.out.println(p);
????????}
????}

????@org.junit.Test
????public void findbyid(){
????????Player player = playerDao.findPlayerById(5);
????????System.out.println(player);
????}


????@org.junit.Test
????public void findbyname(){
????????Player player = playerDao.findPlayerByName("卡哇伊");
????????System.out.println(player);
????}




}