java_使用jdbc和连接池完成员工管理系统

需求

使用jdbc和连接池完成员工管理系统：
功能有：
添加员工、删除员工、修改员工信息、查看员工信息。
a.进入系统，输入“欢迎进入xxx系统”
请选择：1.添加员工 2.删除员工 3…
b.根据输入的数字执行功能
c.将操作的结果写入数据库

1. 导入工具包，在src目录下，创建文件夹（Directory）lib，将工具包复制到lib目录下

2. 创建test包，包下创建TestJdbc类，程序主入口

package com.fy.test;

/**
 * 程序测试
 * 1. 创建test包
 * 2. 创建TestJdbc实体类, 程序运行入口
 * @author 江湖@小小白
 */
//导包
import com.fy.domain.User;
import com.fy.service.UserService;

import java.util.List;
import java.util.Scanner;

public class TestJdbc {
    public static void main(String[] args) {

        UserService service = new UserService();
        Scanner sc= new Scanner(System.in);

        System.out.println("------------------------------欢迎来到蘑菇屋------------------------------");
        User u = null;
        for(;;) {
            System.out.println("请选择：1. 添加用户 2. 删除用户 3. 修改用户信息 4. 查看用户信息 5. 退出系统");
            switch (sc.next()) {
                case "1":
                    System.out.println("请输入用户名:");
                    String username = sc.next();
                    System.out.println("请输入用户密码:");
                    String password = sc.next();
                    u = new User(0,username,password);
                    service.insertUser(u);
                    break;
                case "2":
                    System.out.println("请输入要删除用户的编号：");
                    int delid = sc.nextInt();
                    u = new User(delid,null,null);
                    service.deleteUser(u);
                    break;
                case "3":
                    System.out.println("请输入要修改用户的编号：");
                    int upId = sc.nextInt();
                    System.out.println("请输入新密码:");
                    String upPwd = sc.next();
                    u = new User(upId,null,upPwd);
                    service.updateUser(u);
                case "4":
                    List<User> users = service.selectAllUsers();
                    System.out.println("编号\t用户名\t 密码");
                    for(User user:users) {
                        System.out.println(user);
                    }
                    break;

                case "5":
                    System.exit(0);
            }
        }
    }
}

3. 创建utils包，JdbcUtils类

package com.fy.utils;

/**
 *JdbcUtils工具类
 * 1. 创建utils包
 * 2. 创建JdbcUtils类
 * 3. 创建连接池对象，获取连接池对象
 * 4. 释放资源
 * @author 江湖@小小白
 */

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

public class JdbcUtils {
    //1. 创建连接池对象
    private static ComboPooledDataSource ds = new ComboPooledDataSource("myc3p0");
    public static final String TABLE_NAME = "user";

    //2. 获取连接对象
    public static Connection getConnection() throws Exception {
        return ds.getConnection();
    }
    //3. 释放资源
    public static void close(ResultSet set, Statement stmt, Connection conn) {
        if(set != null) {
            try {
                set.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
        //调用close()方法
        close(stmt, conn);
    }

    public static void close(Statement stmt, Connection conn) {
        if(stmt != null) {
                try {
                    stmt.close();
                }catch(SQLException e){
                    e.printStackTrace();
                }
        }
        if(conn != null) {
            try {
                conn.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
    }
}

4. c3p0文件配置

<c3p0-config>
    <named-config name="myc3p0">
        <!-- 驱动包 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!-- 数据库的地址 -->
        <property name="jdbcUrl">jdbc:mysql:///day0904</property>
        <!-- 数据库的用户名 -->
        <property name="user">root</property>
        <!-- 数据库的密码 -->
        <property name="password">1234</property>
        <!-- 连接池中默认的连接对象的数量 -->
        <property name="initialPoolSize">3</property>
        <!-- 连接对象的最大数量 -->
        <property name="maxPoolSize">5</property>
        <!-- 获取连接对象的超时时间 -->
        <property name="checkoutTimeout">3000</property>
    </named-config>

    <default-config>
        <!-- 驱动包 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!-- 数据库的地址 -->
        <property name="jdbcUrl">jdbc:mysql:///day0903</property>
        <!-- 数据库的用户名 -->
        <property name="user">root</property>
        <!-- 数据库的密码 -->
        <property name="password">1234</property>
        <!-- 连接池中默认的连接对象的数量 -->
        <property name="initialPoolSize">5</property>
        <!-- 连接对象的最大数量 -->
        <property name="maxPoolSize">8</property>
        <!-- 获取连接对象的超时时间 -->
        <property name="checkoutTimeout">3000</property>
    </default-config>
</c3p0-config>

5. 模块整合：创建service包，创建UserService类，通过类中方法的调用，进行数据的增删改查

package com.fy.service;
/**
 *功能:模块整合
 * 1. 创建service包
 * 2. 创建UserService类，通过类中方法的调用，进行数据的增删改查
 * @author 江湖@小小白
 */

//导包
import com.fy.dao.UserDao;
import com.fy.dao.impl.UserImpl;
import com.fy.domain.User;
import java.util.List;

public class UserService {
    public void insertUser(User u) {
        UserDao dao = new UserImpl();
        dao.insertUser(u);
    }

    public void deleteUser(User u) {
        UserDao dao = new UserImpl();
        dao.deleteUser(u);
    }
    public void updateUser(User u) {
        UserDao dao = new UserImpl();
        dao.UpdateUser(u);
    }

    public List<User> selectAllUsers(){
        UserDao dao = new UserImpl();
        return dao.selectAllUsers();
    }
}

6. 功能;值传递：创建domain包，创建User类

package com.fy.domain;

/**
 *功能;值传递
 * 1. 创建domain包
 * 2. 创建User类
 * @author 江湖@小小白
 */
public class User {
    //定义私有属性id,username,password
    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    public User(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    @Override
    public String toString() {

        return id+"  \t "+username+"   \t "+password;
    }
}

7. 功能:接口的实现方法，创建dao包,在dao包下创建一个impl包，创建UserDao的具体实现类，类名UserImpl

package com.fy.dao.impl;
/**
 *功能:接口的实现方法
 * 1. 创建dao包,在dao包下创建一个impl包
 * 2. 创建UserDao的具体实现类，类名UserImpl
 * @author 江湖@小小白
 */


import com.fy.dao.UserDao;
import com.fy.domain.User;
import com.fy.utils.JdbcUtils;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserImpl implements UserDao {


    @Override
    public void insertUser(User u) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "insert into "+JdbcUtils.TABLE_NAME+" values(null,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, u.getUsername());
            pstmt.setString(2, u.getPassword());
            int row = pstmt.executeUpdate();
            if(row != 0) {
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        }catch (MySQLIntegrityConstraintViolationException e) {
            System.out.println("用户已存在！");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(pstmt, conn);
        }
    }

    @Override
    public void deleteUser(User u) {
        Connection conn = null;
        PreparedStatement pstmt = null;
    try {
        conn = JdbcUtils.getConnection();
        String sql = "delete from user where uid = ? ";
        pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, u.getId());
        int row = pstmt.executeUpdate();
        if(row != 0) {
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(pstmt, conn);
        }
    }

    @Override
    public void UpdateUser(User u) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "update user set upassword = ? where uid = ? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, u.getPassword());
            pstmt.setInt(2, u.getId());
            int row = pstmt.executeUpdate();
            if(row != 0) {
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(pstmt, conn);
        }
    }

    @Override
    public List<User> selectAllUsers() {
        List<User> users = new ArrayList<User>();
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from user";
            pstmt = conn.prepareStatement(sql);
            ResultSet set = pstmt.executeQuery();
            while(set.next()) {
                int id = set.getInt("uid");
                String  username = set.getString("username");
                String password = set.getString("upassword");
                User u = new User(id, username, password);
                users.add(u);
            }
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(pstmt, conn);
        }
        return null;
    }
}

8. 功能：接口，创建dao包，创建UserDao接口

package com.fy.dao;
/**
 *功能：接口
 * 1. 创建dao包
 * 2. 创建UserDao接口
 * 3. 在接口中，定义增删改查的抽象方法
 * @author 江湖@小小白
 */
import com.fy.domain.User;

import java.util.List;

public interface UserDao {

    void insertUser(User u);

    void deleteUser(User u);

    void UpdateUser(User u);

    List<User> selectAllUsers();

}

9. 运行结果与数据库表数据