jdbc,增删改查，员工管理，往mysql里进行数据操作

最新推荐文章于 2022-08-03 11:00:02 发布

ptsph

最新推荐文章于 2022-08-03 11:00:02 发布

阅读量1.6k

点赞数 2

分类专栏： jdbc 文章标签： java

本文链接：https://blog.csdn.net/li_yuan_long/article/details/79674230

版权

jdbc 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

package com.em.main;

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

import com.em.pojo.Emp;
import com.em.utils.DBUtil;
/*
 
 员工表:emp
字段：eid,ename,eage
 
 
 * */


/**
请选择操作：退出请输入bye 
1.新增 
2.查询 
3.修改 
4.删除 
输入：1 
请输入：员工编号，姓名，性别，手机号码 
输入:员工编号，姓名，性别，手机号码 
 
新增完成，请继续操作！ 
请选择操作：退出请输入bye 
1.新增 
2.查询 
3.修改 
4.删除 
 
输入：2 
请输入员工的姓名 
钱钟书 
查询员工： 
员工编号，姓名，性别，手机号码 
 
查询完毕，请继续操作！ 
请选择操作：退出请输入bye 
1.新增 
2.查询 
3.修改 
4.删除 
输入：3 
请输入要修改的员工编号： 
10000 
员工不存在，请继续操作！ 
 
请选择操作：退出请输入bye 
1.新增 
2.查询 
3.修改 
4.删除 
 
输入4 
请输入员工编号 
删除成功 * 
 */
public class EmpMain {
	

	public static void main(String[] args) {
		while(true) {
			//菜单
			System.out.println("请选择操作：退出请输入bye\r\n" + "1.新增\r\n" + "2.查询\r\n" + "21.模糊查询\r\n"+ "22.查询全部\r\n"+ "3.修改\r\n" + "4.删除"); 
			Scanner scanner=new Scanner(System.in);  
	         
	        //判断选择  
			String select =scanner.nextLine(); 
			if("1".equals(select)) {
				System.out.println("请输入员工的id:");
				String eid=scanner.next();
				System.out.println("请输入员工的姓名:");
				String ename=scanner.next();
				System.out.println("请输入员工的年龄:");
				String eage=scanner.next();
				//对象
				Emp emp=new Emp();
				//插入到数据中
				emp.setEid(eid);
				emp.setEname(ename);
				emp.setEage(eage);
				insert(emp);
				
			}else if("2".equals(select)) {
				System.out.println("请输入要查询的员工id");
				String eid=scanner.next();
				Emp emp=queryById(eid);
				System.out.println(emp);
			}else if("21".equals(select)) {
				System.out.println("请输入员工名字里任意一个字");
				String ename=scanner.next();
				List<Emp> emps=querylist(ename);
				for (Emp e : emps) {
					System.out.println(e);
				}
				
			}else if("22".equals(select)) {
				List<Emp> list=queryAll();
				for (Emp e : list) {
					System.out.println(e);
				}
				
			}else if("3".equals(select)) {
				System.out.println("请输入要修改的员工id：");
				String eid=scanner.next();
				System.out.println("请输入要修改员工的姓名:");
				String ename=scanner.next();
				System.out.println("请输入要修改员工的年龄:");
				String eage=scanner.next();
				Emp emp=new Emp();
				emp.setEid(eid);
				emp.setEname(ename);
				emp.setEage(eage);
				update(emp);
				
				
			}else if("4".equals(select)) {
				System.out.println("请输入要删改的员工id:");
				String eid=scanner.next();
				delete(eid);
				
			}else if("bye".equals(select)) {
				System.out.println("谢谢使用！");  
	            break;
			}else {
				System.out.println("功能未开发！");  
			}						
			
		}

		
	}
	//模糊查询 按名字里任意一个字
	private static List<Emp> querylist(String ename) {
		String sql="SELECT eid,ename,eage FROM emp WHERE ename LIKE concat('%',?,'%')";
		try {
			Connection conn=DriverManager.getConnection(DBUtil.URL,DBUtil.USER,DBUtil.PASSWORD);
			PreparedStatement stat=conn.prepareStatement(sql);
			stat.setObject(1,ename);
			
			ResultSet rs=stat.executeQuery();
			List<Emp> emps=new ArrayList<>();
			
			while(rs.next()) {
				Emp emp=new Emp();
				emp.setEid(rs.getString("eid"));
				emp.setEname(rs.getString("ename"));
				emp.setEage(rs.getString("eage"));
				emps.add(emp);
			}
			return emps;
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}
	//查询全部
	private static List<Emp> queryAll() {
		// SELECT eid ,ename, eage FROM emp;
		String sql="SELECT eid ,ename, eage FROM emp;";
		try {
			Connection conn=DriverManager.getConnection(DBUtil.URL,DBUtil.USER,DBUtil.PASSWORD);
			PreparedStatement stat=conn.prepareStatement(sql);
			
			ResultSet rs=stat.executeQuery();
			List<Emp> list=new ArrayList<>();
			
			while(rs.next()) {
				Emp emp=new Emp();
				emp.setEid(rs.getString("eid"));
				emp.setEname(rs.getString("ename"));
				emp.setEage(rs.getString("eage"));
				list.add(emp);
			}
			return list;
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	//按id查询员工
	private static Emp queryById(String eid) {
		//SELECT eid,ename,eage FROM emp WHERE eid=2;
		String sql="SELECT eid,ename,eage FROM emp WHERE eid=?;";
		try {
			Connection conn=DriverManager.getConnection(DBUtil.URL,DBUtil.USER,DBUtil.PASSWORD);
			PreparedStatement stat=conn.prepareStatement(sql);
			stat.setObject(1,eid);
			ResultSet rs=stat.executeQuery();
			Emp emp=new Emp();
			if(rs.next()) {
				emp.setEid(rs.getString("eid"));
				emp.setEname(rs.getString("ename"));
				emp.setEage(rs.getString("eage"));
				
			}
			return emp;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	//根据id 删除员工
	private static void delete(String eid) {
		//DELETE FROM emp WHERE eid='3';
		String sql="DELETE FROM emp WHERE eid=?;";
		try {
			Connection conn=DriverManager.getConnection(DBUtil.URL,DBUtil.USER,DBUtil.PASSWORD);
			PreparedStatement stat=conn.prepareStatement(sql);
			stat.setObject(1, eid);
			int rlt=stat.executeUpdate();
			if(rlt>0) {
				System.out.println("删除成功"+rlt+"条数据");
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	//根据id 修改员工
	private static void update(Emp emp) {
		//UPDATE emp SET ename='tomer',eage='15' WHERE eid='1';
		String sql="UPDATE emp SET ename=?,eage=? WHERE eid=? ;";
		try {
			Connection conn=DriverManager.getConnection(DBUtil.URL,DBUtil.USER,DBUtil.PASSWORD);
			PreparedStatement stat=conn.prepareStatement(sql);
			
			stat.setObject(1,emp.getEname());
			stat.setObject(2, emp.getEage());
			stat.setObject(3,emp.getEid());
			
			int rlt=stat.executeUpdate();
			if(rlt>0) {
				 System.out.println("修改成功:"+rlt+"条数据");
			}
			stat.close();
			conn.close();
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	//插入员工
	private static void insert(Emp emp) {
		//INSERT INTO emp(eid,ename,eage) VALUES('3','lily','18');
		String sql="INSERT INTO emp(eid,ename,eage) VALUES(?,?,?);";
		
		try {
			//创建连接
			Connection conn=DriverManager.getConnection(DBUtil.URL,DBUtil.USER,DBUtil.PASSWORD);
			//预编译语句
			PreparedStatement stat=conn.prepareStatement(sql);
			//设置参数，参数要和预编译的参数个数一致
			stat.setObject(1,emp.getEid());
			stat.setObject(2, emp.getEname());
			stat.setObject(3, emp.getEage());
			
			//执行事务
			 int rlt=stat.executeUpdate();
			 if(rlt>0) {
				 System.out.println("成功插入:"+rlt+"条数据");
			 }
			//关闭连接
			 stat.close();
			 conn.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}

}

package com.em.utils;

public class DBUtil {
	
	public static final String URL ="jdbc:mysql://localhost:3306/em?useUnicode=true&characterEncoding=UTF-8";;
	public static final String USER = "root";
	public static final String PASSWORD = "123";
	
	static {
		try {
			//数据库驱动
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("缺少数据库驱动包");
		}
	}
	

}

package com.em.pojo;

public class Emp {
	private String eid;
	private String ename;
	private String eage;
	public String getEid() {
		return eid;
	}
	public void setEid(String eid) {
		this.eid = eid;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getEage() {
		return eage;
	}
	public void setEage(String eage) {
		this.eage = eage;
	}
	@Override
	public String toString() {
		return "id=" + eid + ", 姓名=" + ename + ", 年龄=" + eage+"\n" ;
	}
	

}