package com.ambow.entity;
import java.util.Date;
public class EmpEntity {
private int empno;
private String ename;
private String job;
private Date hireDate;
private double sal;
private double comm;
private int mgr;
private int deptno;
public EmpEntity() {
super();
// TODO Auto-generated constructor stub
}
public EmpEntity(int empno, String ename, String job, Date hireDate, double sal, double comm, int mgr, int deptno) {
super();
this.empno = empno;
this.ename = ename;
this.job = job;
this.hireDate = hireDate;
this.sal = sal;
this.comm = comm;
this.mgr = mgr;
this.deptno = deptno;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Date getHireDate() {
return hireDate;
}
public void setHireDate(Date hireDate) {
this.hireDate = hireDate;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
public double getComm() {
return comm;
}
public void setComm(double comm) {
this.comm = comm;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
@Override
public String toString() {
return "EmpEntity [empno=" + empno + ", ename=" + ename + ", job=" + job + ", hireDate=" + hireDate + ", sal="
+ sal + ", comm=" + comm + ", mgr=" + mgr + ", deptno=" + deptno + "]";
}
}
package com.ambow.util;
import java.sql.*;
public class DBUtil {
// 1. 提取公共数据
private final static String OracleDriver = "oracle.jdbc.driver.OracleDriver";
private final static String Url = "jdbc:oracle:thin:@localhost:1521:orcl";
private final static String Username = "scott";
private final static String Password = "tiger";
// 2. 打开连接
public static Connection openConn() {
Connection conn = null;
try {
Class.forName(OracleDriver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(Url, Username, Password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//3.关闭连接
public static void closeConn(Connection connection)
{
try {
if(connection!=null && connection.isClosed()==false) {
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 4.关闭所有
public static void closeAll(Connection connection, Statement state, ResultSet set) {
if (set != null) {
try {
set.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
try {
if (connection != null && connection.isClosed() == false) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//测试连接是否成功
public static void main(String[] args) throws SQLException {
Connection conn=DBUtil.openConn();
PreparedStatement state=conn.prepareStatement("delete from accout where username = ? ");
state.setString(1, "阳阳");
int count=state.executeUpdate();
System.out.println(count>0?"删除成功":"删除失败!!");
DBUtil.closeAll(conn, state, null);
}
}
package com.ambow.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.ambow.entity.EmpEntity;
import com.ambow.util.DBUtil;
public class EmpDao {
// 按照姓名进行模糊查询
public ArrayList<EmpEntity> queryByKeyWord(String ename) throws SQLException {
ArrayList<EmpEntity> list=new ArrayList<EmpEntity>();
String sql="select * from emp where ename like ?";
Connection conn=DBUtil.openConn();
PreparedStatement state=conn.prepareStatement(sql);
state.setString(1, "%"+ename+"%");
ResultSet set=state.executeQuery();
while(set.next()) {
EmpEntity entity=new EmpEntity();
entity.setEmpno(set.getInt("empno"));
entity.setEname(set.getString("ename"));
entity.setJob(set.getString("job"));
entity.setHireDate(set.getDate("hireDate"));
entity.setMgr(set.getInt("mgr"));
entity.setSal(set.getDouble("sal"));
entity.setComm(set.getDouble("comm"));
entity.setDeptno(set.getInt("deptno"));
list.add(entity);
}
DBUtil.closeAll(conn, state, set);
return list;
}
// 查询全部
public ArrayList<EmpEntity> queryAll() throws SQLException {
// ArrayList<EmpEntity> list=new ArrayList<EmpEntity>();
// String sql="select * from emp";
// Connection conn=DBUtil.openConn();
// PreparedStatement state = conn.prepareStatement(sql);
// ResultSet set = state.executeQuery();
// while(set.next()) {
// EmpEntity entity=new EmpEntity();
// entity.setEmpno(set.getInt("empno"));
// entity.setEname(set.getString("ename"));
// entity.setJob(set.getString("job"));
// entity.setHireDate(set.getDate("hireDate"));
// entity.setMgr(set.getInt("mgr"));
// entity.setSal(set.getDouble("sal"));
// entity.setComm(set.getDouble("comm"));
// entity.setDeptno(set.getInt("deptno"));
//
// list.add(entity);
// }
//
// DBUtil.closeAll(conn, state, set);
// return list;
// String sql = "select * from emp";
ArrayList<EmpEntity> list = new ArrayList<EmpEntity>();
Connection conn = DBUtil.openConn();
PreparedStatement state = conn.prepareStatement("select * from emp ");
ResultSet set =state.executeQuery();
while(set.next()) {
EmpEntity entity = new EmpEntity();
entity.setEmpno(set.getInt("empno"));
entity.setEname(set.getString("ename"));
entity.setJob(set.getString("job"));
entity.setHireDate(set.getDate("hireDate"));
entity.setMgr(set.getInt("mgr"));
entity.setSal(set.getDouble("sal"));
entity.setComm(set.getDouble("comm"));
entity.setDeptno(set.getInt("deptno"));
list.add(entity);
}
DBUtil.closeAll(conn, state, set);
return list;
}
// 查询一个员工的信息
public EmpEntity queryByPrimaryKey(int empno) throws SQLException {
String sql = "select * from emp where empno=?";
Connection conn = DBUtil.openConn();
PreparedStatement state = conn.prepareStatement(sql);
state.setInt(1, empno);
ResultSet set = state.executeQuery();
EmpEntity entity = null;
if (set.next()) {
entity = new EmpEntity();
entity.setEmpno(set.getInt("empno"));
entity.setEname(set.getString("ename"));
entity.setJob(set.getString("job"));
entity.setHireDate(set.getDate("hireDate"));
entity.setMgr(set.getInt("mgr"));
entity.setSal(set.getDouble("sal"));
entity.setComm(set.getDouble("comm"));
entity.setDeptno(set.getInt("deptno"));
}
DBUtil.closeAll(conn, state, set);
return entity;
}
// 修改员工信息(成功与否)
public boolean update(EmpEntity entity) throws SQLException {
String sql="update emp set ename=?,job=?,mgr=?,sal=?,comm=?,deptno=?,hiredate=? where empno=?";
Connection conn=DBUtil.openConn();
PreparedStatement state=conn.prepareStatement(sql);
state.setString(1, entity.getEname());
state.setString(2, entity.getJob());
state.setInt(3, entity.getMgr());
state.setDouble(4, entity.getSal());
state.setDouble(5, entity.getComm());
state.setInt(6, entity.getDeptno());
state.setDate(7, new java.sql.Date(entity.getHireDate().getTime()));
state.setInt(8, entity.getEmpno());
int count=state.executeUpdate();
DBUtil.closeAll(conn, state, null);
return count>0;
}
// 删除员工信息
public boolean delete(int empno) throws SQLException {
String sql="delete from emp where empno=?";
Connection conn=DBUtil.openConn();
PreparedStatement state=conn.prepareStatement(sql);
state.setInt(1, empno);
int count=state.executeUpdate();
DBUtil.closeAll(conn, state, null);
return count>0;
}
// 新增员工信息
public boolean add(EmpEntity entity) throws SQLException {
String sql="insert into emp(empno,ename,job,mgr , sal , comm , deptno , hiredate)+values( ?,?,?,?,?,?,?,? ) ";
Connection conn=DBUtil.openConn();
PreparedStatement state=conn.prepareStatement(sql);
state.setInt(1, entity.getEmpno());
state.setString(2, entity.getEname());
state.setString(3, entity.getJob());
state.setInt(4, entity.getMgr());
state.setDouble(5, entity.getSal());
state.setDouble(6, entity.getComm());
state.setInt(7, entity.getDeptno());
state.setDate(8, new java.sql.Date(entity.getHireDate().getTime()));
int count=state.executeUpdate();
DBUtil.closeAll(conn, state, null);
return count>0;
}
}
package com.ambow.ui;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Scanner;
import com.ambow.dao.EmpDao;
import com.ambow.entity.EmpEntity;
public class MainUI {
public static void main(String[] args) throws SQLException, ParseException {
EmpDao dao = new EmpDao();
Scanner sc = new Scanner(System.in);
while (true) {
System.out.println("请选择你要进行的操作:1 新增,2 删除 ,3 查询全部,4 按照员工姓名模糊查询,5 按照员工编号进行精确查询 输入0直接退出本系统! ");
int choose = sc.nextInt();
switch (choose) {
case 0:
System.out.println("是否真的退出本系统?Y代表退出!");
String exit = sc.next();
if( exit.equals("Y") || exit.equals("y") )
{
return ;
}
break;
case 1: // 新增
EmpEntity entity = new EmpEntity();
System.out.println("请输入要新增的编号:");
int empno = sc.nextInt();
entity.setEmpno(empno);
System.out.println("请输入要新增的员工姓名:");
String ename = sc.next();
entity.setEname(ename);
System.out.println("请输入要新增的员工工种:");
String job = sc.next();
entity.setJob(job);
System.out.println("请输入要新增的领导编号:");
int mgr = sc.nextInt();
entity.setMgr(mgr);
System.out.println("请输入要新增的工资:");
double sal = sc.nextDouble();
entity.setSal(sal);
System.out.println("请输入要新增的奖金 :");
double comm = sc.nextDouble();
entity.setComm(comm);
System.out.println("请输入要新增的雇佣时间:");
String temp_date = sc.next(); // 将一个字符串转换成日期
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date hireDate = sdf.parse(temp_date);
entity.setHireDate(hireDate);
System.out.println("请输入要新增的部门编号");
int deptno = sc.nextInt();
entity.setDeptno(deptno);
boolean bln = dao.add(entity);
System.out.println(bln == true ? "新增成功!" : "新增失败!");
break;
case 2:
System.out.println("请输入你要删除的员工编号:");
int d_empno = sc.nextInt();
System.out.println("您是否要确认删除编号为:" + d_empno + "的员工吗?Y代表删除!");
String del_flag = sc.next();
if (del_flag.equals("Y") || del_flag.equals("y")) {
boolean d_bln = dao.delete(d_empno);
System.out.println(d_bln == true ? "删除成功!" : "删除失败!");
}
break;
case 3:
ArrayList<EmpEntity> list = dao.queryAll();
for (EmpEntity empEntity : list) {
System.out.println(empEntity.toString());
}
break;
case 4:
System.out.println("请输入要查询的名字,支持模糊查询:");
String q_ename = sc.next();
ArrayList<EmpEntity> q_list = dao.queryByKeyWord(q_ename);
for (EmpEntity empEntity : q_list) {
System.out.println(empEntity.toString());
}
break;
case 5:
System.out.println("请输入要查询的员工编号:");
int q_empno = sc.nextInt();
EmpEntity q_entity = dao.queryByPrimaryKey(q_empno);
if (q_entity == null) {
System.out.println("没有查询到编号为:" + q_empno + "的员工信息!");
} else {
System.out.println(q_entity.toString());
}
break;
default:
break;
}
}
}
}