需求
使用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();
}