客户关系管理系统

前言

为了巩固开发的流程，我们再拿一个客户关系管理系统来练手！

成果图

我们完成的就是下面的项目！

---

搭建配置环境

• 配置Tomcat
• 导入开发包
• 建立开发用到的程序包

• 在数据库创建相对应的表

    CREATE TABLE customer (

      id          VARCHAR(40) PRIMARY KEY,
      name        VARCHAR(20) NOT NULL,
      gender      VARCHAR(10) NOT NULL,
      birthday    DATE,
      cellphone   VARCHAR(30) NOT NULL,
      email       VARCHAR(30),
      preference  VARCHAR(200),
      type        VARCHAR(20),
      description VARCHAR(255)

    );

---

开发实体

开发实体十分简单，对照着数据库的表就行了！

    private String id;
    private String name ;
    private String gender ;
    private Date birthday ;
    private String cellphone ;
    private String eamil ;
    private String preference ;
    private String type ;
    private String description;


    //....各种setter、getter

---

开发获取数据库连接池的Utils

导入配置文件

    <?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/zhongfucheng</property>
            <property name="user">root</property>
            <property name="password">root</property>

            <property name="acquireIncrement">5</property>
            <property name="initialPoolSize">10</property>
            <property name="minPoolSize">5</property>
            <property name="maxPoolSize">20</property>
        </default-config>

        <named-config name="mysql">
            <property name="driverClass">com.mysql.jdbc.Driver</property>
            <property name="jdbcUrl">jdbc:mysql://localhost:3306/zhongfucheng</property>
            <property name="user">root</property>
            <property name="password">root</property>

            <property name="acquireIncrement">5</property>
            <property name="initialPoolSize">10</property>
            <property name="minPoolSize">5</property>
            <property name="maxPoolSize">20</property>
        </named-config>


        <named-config name="oracle">
            <property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
            <property name="jdbcUrl">jdbc:oracle:thin:@//localhost:1521/事例名...</property>
            <property name="user">用户名</property>
            <property name="password">密码</property>

            <property name="acquireIncrement">5</property>
            <property name="initialPoolSize">10</property>
            <property name="minPoolSize">5</property>
            <property name="maxPoolSize">20</property>
        </named-config>
    </c3p0-config>

开发提供数据连接池的工具类

    public class Utils2DB {

        private static ComboPooledDataSource comboPooledDataSource = null;

            static {

                //它会自动寻找配置文件，节点为mysql的数据库（默认就是Mysql）
                comboPooledDataSource = new ComboPooledDataSource();
            }


        public static DataSource getDataSource() {
            return comboPooledDataSource ;
        }

        public static Connection connection() {
            try {
                return comboPooledDataSource.getConnection();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("数据库初始化失败了！");
            }
        }
    }

开发UUID工具类

    public class WebUtils {

        public static String makeId() {
            return UUID.randomUUID().toString();
        }
    }

开发DAO

DAO应该提供增加客户和查询用户的功能

增加用户

    public void addCustomer(Customer customer)  {

        QueryRunner queryRunner = new QueryRunner(Utils2DB.getDataSource());


        String sql = "INSERT INTO customer (id,name, gender, birthday, cellphone, preference, type, description) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)";


        //得到用户传递进来的数据
        String id = customer.getId();
        String name = customer.getName();
        String gender = customer.getGender();
        String cellphone = customer.getCellphone();
        String email = customer.getEmail();
        String preference = customer.getPreference();
        String type = customer.getType();
        String description = customer.getDescription();

        //对于日期，要转换一下
        Date date = customer.getBirthday();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String birthday = simpleDateFormat.format(date);

        try {
            //向数据库插入数据
            queryRunner.update(sql, new Object[]{id, name, gender, birthday, cellphone, email, preference, type, description});

            //插入记录成功！
        } catch (SQLException e) {

            //如果出现了异常，就抛出Dao异常吧（自定义的异常）
            e.printStackTrace();

            throw new DaoException("添加用户出错了！");
        }
    }

测试增加用户

写完一个功能，不要急着去写其他的功能，先测试一下！

    @Test
    public void add() {

        //为了测试的方便，直接使用构造函数了！
        Customer customer = new Customer("1", "zhongfucheng", "男", new Date(), "1234", "aa@sina.com", "打代码", "高贵的用户", "我是个好人");



        CustomerDao customerDao = new CustomerDao();
        customerDao.addCustomer(customer);

    }

• 好的，没有报错！再看看数据库———–只要是中文的数据，都乱码了！

---

解决的办法，看我另外一篇博文：http://blog.csdn.net/hon_3y/article/details/57945418

---

查询用户

将所有的客户查询出来就行了！

    //得到所有的用户
    public List<Customer> getAll() {

        QueryRunner queryRunner = new QueryRunner(Utils2DB.getDataSource());


        String sql = "SELECT * FROM customer";
        try {
            List<Customer> customers = (List<Customer>) queryRunner.query(sql, new BeanListHandler(Customer.class));

            //如果集合大于个数大于0，就返回集合，不大于0，就返回null
            return customers.size() > 0 ? customers : null;

        } catch (SQLException e) {
            e.printStackTrace();
            throw new DaoException("获取所有的用户出错了！");
        }

    }

测试查询用户

    @Test
    public void find() {

        CustomerDao customerDao = new CustomerDao();
        List<Customer> customers = customerDao.getAll();

        for (Customer customer : customers) {

            System.out.println(customer.getName());
        }
    }

---

修改用户信息

修改用户信息首先要知道用户的信息，在web端，只有id能唯一标识用户，我们需要通过id，获取用户全部信息（也就是Customer对象）

    public Customer find(String id) {

        QueryRunner queryRunner = new QueryRunner(Utils2DB.getDataSource());

        String sql = "SELECT * FROM customer WHERE id = ?";

        try {
            Customer customer = (Customer) queryRunner.query(sql, new BeanHandler(Customer.class), new Object[]{id});

            return customer;

        } catch (SQLException e) {
            e.printStackTrace();
            throw new DaoException("查找用户失败了");
        }

    }

修改用户都是外边传递个对象进来，Dao层取出对象的数据，从而对数据库的数据进行修改！

    public void update(Customer customer) {

        QueryRunner queryRunner = new QueryRunner(Utils2DB.getDataSource());

        String sql = "UPDATE customer set name=?,gender=?,birthday=?,cellphone=?,email=?,preference=?,type=?,description=?  WHERE id = ?";

        try {
            queryRunner.update(sql, new Object[]{customer.getName(), customer.getGender(), customer.getBirthday(),customer.getCellphone(), customer.getEmail(), customer.getPreference(), customer.getType(), customer.getDescription(), customer.getId()});

        } catch (SQLException e) {

            e.printStackTrace();
            throw new DaoException("更新失败");
        }
    }

测试修改用户

    @Test
    public void update() {

        CustomerDao customerDao = new CustomerDao();

        //我们已经知道了某id，通过id获取得到用户信息（Customer）
        String id = "043f7cce-c6f1-4155-b688-ba386cae1636";
        Customer customer = customerDao.find(id);

        //修改用户信息
        customer.setName("看完博客要点赞");
        customerDao.update(customer);
    }

• 原来该用户的名字是d

• 测试完之后：

---

删除用户

• 通过外界传递进来的id，就可以删除数据库表中的记录了

    public void delete(String id) {
        QueryRunner queryRunner = new QueryRunner(Utils2DB.getDataSource());

        String sql = "DELETE from  customer WHERE id = ?";
        try {
            queryRunner.update(sql, new Object[]{id});
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DaoException("删除用户失败了");
        }
    }

测试删除用户

    @Test
    public void delete() {

        CustomerDao customerDao = new CustomerDao();

        //我们已经知道了某id，通过id删除数据库中的记录
        String id =