题目

使用IDEA 创建 MAVEN  JAVA 项目

根据下图 在mysql数据库中创建员工表(T_Employee)

创建实体类 Employee  并创建DAO 层(使用JDBC技术)

以及测试类 对创建的DAO实现类进行增删改查进行测试

项目目录结构

1.在数据库中创建表T_Employee(大小写无所谓)

DROP TABLE IF EXISTS `t_employee`;
CREATE TABLE `t_employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

2.创建实体类Employee

package com.xiongluoluo.bean;

import lombok.*;

import java.math.BigDecimal;
import java.util.Date;

/**
 * Created by Administrator on 2019/12/21 0021.
 */
@Getter
@Setter
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
    private int id;
    private String name;
    private int age;
    private Date birthday;
    private BigDecimal salary;
}

2.创建Dao层之前,按照以前的方式,我们需要导入JDBC的jar包,然后导入C3P0的jar包,以及dbutils的jar包,在src下写一个c3p0-config.xml文件.但是现在我们使用了maven去创建项目,需要导包的只需在pom.xml中添加依赖即可.需要写在src下的c3p0-config.xml文件只需放在src-->main-->resources文件夹(这个文件夹必须标记为resource root,不然读不到)下即可.

2.1   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.xiongluoluo</groupId>
    <artifactId>maven3</artifactId>
    <version>1.0.0</version>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <!--lombok插件的依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
            <scope>provided</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <!--JDBC的依赖(在https://mvnrepository.com中搜索mysql,点击第一个即可)-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
        <!--c3p0的依赖(在https://mvnrepository.com中搜索c3p0,点击第一个即可)-->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
        <!--dbutils的依赖(在https://mvnrepository.com中搜索dbutils,点击第一个即可)-->
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.6</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

    </dependencies>


</project>

2.2 c3p0-config.xml文件

<?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/test2</property>
      <property name="user">root</property>
      <property name="password">1234</property>
   </default-config>
</c3p0-config>

2.3 EmployeDao.java文件

package com.xiongluoluo.dao;

import com.xiongluoluo.bean.Employee;

import java.util.List;

/**
 * Created by Administrator on 2019/12/21 0021.
 */
public interface EmployeeDao {
    //增加员工
    public int addEmployee(Employee employee);
    //通过id删除员工
    public int deleteEmployeeById(int id);
    //修改员工信息
    public int updateEmployee(Employee employee);
    //查询员工信息
    public Employee queryEmployeeById(int id);
    //查询全部员工信息
    public List<Employee> queryAllEmployee();
}

2.4 EmployeeDaoImpl.java文件

package com.xiongluoluo.dao.impl;

import com.xiongluoluo.bean.Employee;
import com.xiongluoluo.dao.EmployeeDao;
import com.xiongluoluo.utils.C3p0Util;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.util.List;

/**
 * Created by Administrator on 2019/12/21 0021.
 */
public class EmployeeDaoImpl implements EmployeeDao{
    public int addEmployee(Employee employee) {
        QueryRunner queryRunner = new QueryRunner(C3p0Util.getDataSource());
        String sql = "insert into t_employee values(null,?,?,?,?)";
        int result = 0;
        try {
            result = queryRunner.update(sql,new Object[]{employee.getName(),employee.getAge(),employee.getBirthday(),employee.getSalary()});
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public int deleteEmployeeById(int id) {
        QueryRunner queryRunner = new QueryRunner(C3p0Util.getDataSource());
        String sql = "delete from t_employee where id = ?";
        int result = 0;
        try {
            result = queryRunner.update(sql,id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public int updateEmployee(Employee employee) {
        QueryRunner queryRunner = new QueryRunner(C3p0Util.getDataSource());
        String sql = "update t_employee set name=?,age=?,birthday=?,salary=? where id = ?";
        int result = 0;
        try {
            result = queryRunner.update(sql,employee.getName(),employee.getAge(),employee.getBirthday(),employee.getSalary(),employee.getId());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public Employee queryEmployeeById(int id) {
        QueryRunner queryRunner = new QueryRunner(C3p0Util.getDataSource());
        String sql = "select id,name,age,birthday,salary from t_employee where id=?";
        Employee employee = null;
        try {
            employee = queryRunner.query(sql,new BeanHandler<Employee>(Employee.class),id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return employee;
    }

    public List<Employee> queryAllEmployee() {
        QueryRunner queryRunner = new QueryRunner(C3p0Util.getDataSource());
        String sql = "select id,name,age,birthday,salary from t_employee";
        List<Employee> list = null;
        try {
            list = queryRunner.query(sql,new BeanListHandler<Employee>(Employee.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}

3 Dao层中需要用的一些工具类

3.1 C3p0获取数据源的工具类

package com.xiongluoluo.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;

/**
 * Created by Administrator on 2019/12/21 0021.
 */
public class C3p0Util {
    private static DataSource dataSource = new ComboPooledDataSource();
    public static DataSource getDataSource(){
        return dataSource;
    }
}

3.2  字符串转日期工具类

package com.xiongluoluo.utils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * Created by Administrator on 2019/12/21 0021.
 */
public class DateUtil {
    public static Date strToDate(String str){
        Date date = null;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            date = simpleDateFormat.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return date;
    }
}

4 测试类

package com.xiongluoluo.test;

import com.xiongluoluo.bean.Employee;
import com.xiongluoluo.dao.EmployeeDao;
import com.xiongluoluo.dao.impl.EmployeeDaoImpl;
import com.xiongluoluo.utils.DateUtil;
import org.junit.Test;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Scanner;

/**
 * Created by Administrator on 2019/12/21 0021.
 */
public class EmployeeTest {
    //增加员工
    @Test
    public void testAddEmployee(){
        Scanner sc = new Scanner(System.in);
        try{
            System.out.println("请输入name:");
            String name = sc.nextLine();
            System.out.print("请输入age:");
            int age = Integer.parseInt(sc.nextLine());
            System.out.println("请输入生日(格式1998-01-02):");
            Date birthday = DateUtil.strToDate(sc.nextLine());
            System.out.println("请输入工资:");
            BigDecimal salary = new BigDecimal(sc.nextLine());
            Employee employee = new Employee();
            employee.setName(name);
            employee.setAge(age);
            employee.setBirthday(birthday);
            employee.setSalary(salary);
            EmployeeDao employeeDao = new EmployeeDaoImpl();
            int result = employeeDao.addEmployee(employee);
            if(result>0){
                System.out.println("添加成功!");
            }else{
                System.out.println("添加失败!");
            }
        }catch (Exception e){
            System.out.println("您输入的格式不正确!");
            //e.printStackTrace();
        }

    }

    //通过id删除员工
    @Test
    public void testDeleteEmployeeById(){
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入id(数字):");
        try{
            int id = Integer.parseInt(sc.nextLine());
            EmployeeDao employeeDao = new EmployeeDaoImpl();
            int result = employeeDao.deleteEmployeeById(id);
            if(result>0){
                System.out.println("删除成功!");
            }else{
                System.out.println("删除失败!");
            }
        }catch (Exception e){
            System.out.println("您输入的格式不正确!");
            //e.printStackTrace();
        }

    }
    //修改员工信息
    @Test
    public void testUpdateEmployee(){
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入id(数字):");
        try{
            int id = Integer.parseInt(sc.nextLine());
            EmployeeDao employeeDao = new EmployeeDaoImpl();
            Employee employee = employeeDao.queryEmployeeById(id);
            if(employee!=null){
                int result = employeeDao.updateEmployee(employee);
                if(result>0){
                    System.out.println("更新成功!");
                }else{
                    System.out.println("更新失败!");
                }
            }else{
                System.out.println("您输入的id不存在!");
            }
        }catch (Exception e){
            System.out.println("您输入的格式不正确!");
            //e.printStackTrace();
        }

    }
    //查询员工信息
    @Test
    public void testQueryEmployeeById(){
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入id(数字):");
        try{
            int id = Integer.parseInt(sc.nextLine());
            EmployeeDao employeeDao = new EmployeeDaoImpl();
            Employee employee = employeeDao.queryEmployeeById(id);
            if(employee!=null){
                System.out.println(employee.getName()+"\t"+employee.getAge()+"\t"+employee.getBirthday()+"\t"+employee.getSalary());
            }else{
                System.out.println("您输入的id不存在!");
            }
        }catch (Exception e){
            System.out.println("您输入的格式不正确!");
            //e.printStackTrace();
        }

    }
    //查询全部员工信息
    @Test
    public void testQueryAllEmployee(){
        EmployeeDao employeeDao = new EmployeeDaoImpl();
        List<Employee> employeeList = employeeDao.queryAllEmployee();
        for(Employee employee:employeeList){
            System.out.println(employee.getName()+"\t"+employee.getAge()+"\t"+employee.getBirthday()+"\t"+employee.getSalary());
        }
    }
}

这样配置项目之后编译之后的结果为:

虽然这个项目很简单,但是也让我感觉到maven的好处.像以前我写这种项目的时候,就得导很多包,然后放文件什么的.但是有了maven,只需导入依赖即可.还有c3p0-config.xml文件也帮忙处理了.感觉maven这个工具还是很棒的,就是一开始做的时候,没有真正理解maven的用法,所以遇到一些坑,不过现在算是理解清楚了.