项目框架:
在Maven项目里,可以通过在配置文件中写入依赖来获取jar包(也可以把jar包放入main下的resourses文件夹(和导入依赖是一样的),还有c3p0的配置文件,也是放在main下的resourses文件夹下,当进行compile后,结构和我们之前用eclipse看到的结构是一样的)
pom.xml配置文件:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.yht</groupId>
<artifactId>EmpCRUD</artifactId>
<version>1.0.0</version>
<dependencies>
<!--这里是lombok工具包,用来简写bean类的-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.20</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.6</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>mchange-commons-java</artifactId>
<version>0.2.15</version>
</dependency>
<!-- 这里是junit的包,做测试用 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
c3p0-comfig.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!-- XML文件类似HTML文件,是一种树形结构的文件,上面的这个是文件的根节点 -->
<!-- 这里可以从名字看出,是c3p0的配置 -->
<c3p0-config>
<default-config>
<!-- 这是一个注释,XML文件的注释格式就是这样 -->
<!-- 这下面四个是这个配置文件的必须项,出了错或者里面填写错误一定会失败,连接不到 -->
<!-- 配置文件的内容,c3p0的jar包里面会读取这个文件这里面的配置,来连接到我们想要连接的数据库 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///dbmaven</property>
<property name="user">root</property>
<property name="password"></property>mkl
<!-- 连接池参数 -->
<!-- 这里是基本参数的设置,如果没有会有默认值,根据项目的运作情况,可以在这里面进行更改 -->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="checkoutTimeout">2000</property>
<property name="maxIdleTime">1000</property>
</default-config>
<!-- 上面的是默认配置,后面要用的ComboPooledDataSource构造方法中没有参数时会默认获取上面的配置 -->
<!-- 这里是可以自定义的名字,不过要在ComboPooledDataSource构造方法中加上自定义的名字,比如下面的yht,就是自定义的名字 -->
<name-config name="yht">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///dbc3p0</property>
<property name="user">root</property>
<property name="password">yht</property>
<!-- 连接池参数 -->
<!-- 这里是基本参数的设置,如果没有会有默认值,根据项目的运作情况,可以在这里面进行更改 -->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="checkoutTimeout">2000</property>
<property name="maxIdleTime">1000</property>
</name-config>
</c3p0-config>
Emp:
package com.yht.bean;
import lombok.*;
@Getter
@Setter
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class Emp {
private Integer id;
private String name;
private Integer age;
private String birthday;
private Double salary;
}
EmpDao:
package com.yht.dao;
import com.yht.bean.Emp;
import java.util.List;
public interface EmpDao {
public int insertEmp(Emp emp);
public int updateEmp(Emp emp);
public int deleteEmp(Integer id);
public List<Emp> findAllEmp();
public Emp findEmpById(Integer id);
}
EmpDaoImpl:
package com.yht.dao.impl;
import com.yht.bean.Emp;
import com.yht.dao.EmpDao;
import com.yht.utils.C3p0Util;
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.ArrayList;
import java.util.List;
public class EmpDaoImpl implements EmpDao {
public int insertEmp(Emp emp) {
int result = 0;
QueryRunner qr = new QueryRunner(C3p0Util.getDataSource());
String sql = "insert into emp values(null,?,?,?,?)";
try {
result = qr.update(sql,emp.getName(),emp.getAge(),emp.getBirthday(),emp.getSalary());
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public int updateEmp(Emp emp) {
int result = 0;
QueryRunner qr = new QueryRunner(C3p0Util.getDataSource());
String sql = "update emp set salary = ? where id = ?";
try {
result = qr.update(sql,emp.getSalary(),emp.getId());
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public int deleteEmp(Integer id) {
int result = 0;
QueryRunner qr = new QueryRunner(C3p0Util.getDataSource());
String sql = "delete from emp where id = ?";
try {
result = qr.update(sql,id);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public List<Emp> findAllEmp() {
List<Emp> list = new ArrayList<Emp>();
QueryRunner qr = new QueryRunner(C3p0Util.getDataSource());
BeanListHandler blh = new BeanListHandler(Emp.class);
String sql = "select * from emp";
try {
list =(List<Emp>)qr.query(sql,blh);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public Emp findEmpById(Integer id) {
Emp emp = new Emp();
QueryRunner qr = new QueryRunner(C3p0Util.getDataSource());
String sql = "select * from emp where id = ?";
try {
emp = (Emp)qr.query(sql,new BeanHandler(Emp.class),id);
} catch (SQLException e) {
e.printStackTrace();
}
return emp;
}
}
EmpTest:
package com.yht.test;
import com.yht.bean.Emp;
import com.yht.dao.EmpDao;
import com.yht.dao.impl.EmpDaoImpl;
import org.junit.Test;
import java.util.ArrayList;
import java.util.List;
public class EmpTest {
@Test
public void insertEmpTest(){
Emp emp = new Emp();
emp.setName("罗宾");
emp.setAge(20);
emp.setBirthday("2016-03-02");
emp.setSalary(20000.0);
EmpDao dao = new EmpDaoImpl();
int result = dao.insertEmp(emp);
if(result==1){
System.out.println("插入测试成功了");
}
}
@Test
public void updateEmpTest(){
Emp emp = new Emp();
emp.setId(2);
emp.setSalary(100000.0);
EmpDao dao = new EmpDaoImpl();
int result = dao.updateEmp(emp);
if(result==1){
System.out.println("更改测试成功了");
}
}
@Test
public void deleteEmpTest(){
int id = 8;
EmpDao dao = new EmpDaoImpl();
int result = dao.deleteEmp(id);
if(result==1){
System.out.println("删除成功了");
}
}
@Test
public void findAllEmpTest(){
List<Emp> list = new ArrayList<Emp>();
EmpDao dao = new EmpDaoImpl();
list = dao.findAllEmp();
for(Emp emp : list){
System.out.println(emp);
}
}
@Test
public void findEmpByIdTest(){
Emp emp = new Emp();
EmpDao dao = new EmpDaoImpl();
Integer id = 1;
emp = dao.findEmpById(id);
System.out.println(emp);
}
}
测试:
单独测试方法1:
单独测试方法2:
全部测试: