34道查询题8个场景,用Java+界面来实现,可以一个界面上设计多个按钮,每个按钮一道题。
1.老板心情好,想看一下每个部门最高工资是多少。他点击了“查看”按钮,就得到了想要的结果。请帮老板实现。
2.小李觉得自己的工资太低了,于是他想知道所有员工的工资级别。他点击了“查询”按钮,就得到了想要的效果,请帮小李实现。
3.老板闲来无事,这个月人事招了20人,他想查看一下这个月最近入职的五名员工。他点击了“查看”按钮,就得到了想要的效果,请帮老板实现。
4.人事MM心情好,她想了一下:列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。她开心地点击了“查询”按钮就得到了想要的结果,请帮美眉实现此功能,定感激不尽。
5.老板发现项目出了问题,是SCOTT那一组,他想知道与"SCOTT"从事相同工作的所有员工及部门名称。他生气地点击了“查询”,得到了结果。请帮老板实现此功能。
6.新的一年又开始了,老板为了留住老员工:给任职日期超过3年的员工加薪10%,老板点击了“加薪”按钮,就实现了。
7.有个主管对S特别感兴趣,想知道部门名称中,带’S’字符的部门员工的工资合计、部门人数。希望你来实现此功能。
8.人事经理看着天空,突然想要一个效果:列出所有员工及领导的姓名。马上叫来你,让你帮忙实现。
周末愉快。我不提倡加班。
sql语句
##部门表
#DROP IF EXISTS TABLE DEPT;
CREATE TABLE DEPT(
DEPTNO int PRIMARY KEY,##部门编号
DNAME VARCHAR(14) , ##部门名称
LOC VARCHAR(13) ##部门地址
) ;
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
##员工表
#DROP IF EXISTS TABLE EMP;
CREATE TABLE EMP(
EMPNO int PRIMARY KEY, #员工编号
ENAME VARCHAR(10), #员工姓名
JOB VARCHAR(9), #员工工作
MGR int, #员工直属领导编号
HIREDATE DATE, #入职时间
SAL double, #工资
COMM double, #奖金
DEPTNO int #对应dept表的外键
);
## 添加 部门 和 员工 之间的主外键关系
ALTER TABLE EMP ADD CONSTRAINT FOREIGN KEY EMP(DEPTNO) REFERENCES DEPT (DEPTNO);
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);
#工资等级表
#DROP IF EXISTS TABLE SALGRADE;
CREATE TABLE SALGRADE(
GRADE int, #等级
LOSAL double, #最低工资
HISAL double ); #最高工资
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
首先:要将8道题做了。答案已经给了,只要去34个里面挑出要的那8个。
接下来:项目框架要搭好。
这里没有写服务层,直接访问了dao,其实可以加service层。
这道题的突破点在SelectDaoImpl
package com.hr.dao;
import java.util.Vector;
/**
* 特殊查询接口
* @author 彭维新
*/
public interface SelectDao {
/**
* 查看每个部门的最高工资
* @return 所有对象集合
*/
Vector<Vector<Object>> select1();
/**
* 所有员工的工资级别
* @return 所有对象集合
*/
Vector<Vector<Object>> select2();
/**
* 查看一下这个月最近入职的五名员工
* @return 所有对象集合
*/
Vector<Vector<Object>> select3();
/**
* 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
* @return 所有对象集合
*/
Vector<Vector<Object>> select4();
/**
* 与"SCOTT"从事相同工作的所有员工及部门名称
* @return 所有对象集合
*/
Vector<Vector<Object>> select5();
/**
* 给任职日期超过3年的员工加薪10%
*/
void select6();
/**
* 部门名称中,带’S’字符的部门员工的工资合计、部门人数。
* @return 所有对象集合
*/
Vector<Vector<Object>> select7();
/**
* 列出所有员工及领导的姓名。
* @return 所有对象集合
*/
Vector<Vector<Object>> select8();
}
写其实现类
package com.hr.dao.impl;
import com.hr.dao.SelectDao;
import com.hr.db.DbHelper;
import java.sql.SQLException;
import java.util.Vector;
/**
* 项目:
* 时间: 2020/8/23 11:37
*
* @author 彭维新
*/
public class SelectDaoImpl extends DbHelper implements SelectDao {
@Override
public Vector<Vector<Object>> select1() {
//返回员工对象集合和员工写最外面
Vector<Vector<Object>> rows = new Vector<> ();
Vector<Object> vectors= null;
//1.连接
conn = getConn();
//2.写MySQL语句
String sql = "select \n" +
"e.ename ,t.* \n" +
"from\n" +
"( select e.deptno,max(sal) as maxsal from emp e group by e.deptno) t \n" +
"join\n" +
"emp e \n" +
"on \n" +
"t.deptno=e.deptno and t.maxsal=e.sal";
try {
pst = conn.prepareStatement(sql);
//3.执行
rs = pst.executeQuery();
//4.放入员工集合
if (rs!=null) {
while (rs.next()){
vectors = new Vector<>();
vectors.add(rs.getString("ename"));
vectors.add(rs.getInt("deptNo"));
vectors.add(rs.getDouble("maxsal"));
rows.add(vectors);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(rs,pst,conn);
}
//返回学生集合
return rows;
}
@Override
public Vector<Vector<Object>> select2() {
Vector<Vector<Object>> rows = new Vector<> ();
Vector<Object> vectors;
conn = getConn();
String sql = "select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal >=s.losal and e.sal <=s.hisal";
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
if (rs!=null) {
while (rs.next()){
vectors = new Vector<>();
vectors.add(rs.getString("ename"));
vectors.add(rs.getDouble("sal"));
vectors.add(rs.getInt("grade"));
rows.add(vectors);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(rs,pst,conn);
}
return rows;
}
@Override
public Vector<Vector<Object>> select3() {
Vector<Vector<Object>> rows = new Vector<> ();
Vector<Object> vectors;
conn = getConn();
String sql = "select ename,hiredate from emp order by hiredate desc limit 0,5";
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
if (rs!=null) {
while (rs.next()){
vectors = new Vector<>();
vectors.add(rs.getString("ename"));
vectors.add(rs.getDate("hiredate"));
rows.add(vectors);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(rs,pst,conn);
}
return rows;
}
@Override
public Vector<Vector<Object>> select4() {
Vector<Vector<Object>> rows = new Vector<> ();
Vector<Object> vectors;
conn = getConn();
String sql = "select d.deptno,e.* from dept d left join emp e on e.deptno=d.deptno order by d.deptno";
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
if (rs!=null) {
while (rs.next()){
vectors = new Vector<>();
vectors.add(rs.getInt("deptno"));
vectors.add(rs.getInt("EMPNO"));
vectors.add(rs.getString("ENAME"));
vectors.add(rs.getString("JOB"));
vectors.add(rs.getInt("MGR"));
vectors.add(rs.getDate("HIREDATE"));
vectors.add(rs.getDouble("SAL"));
vectors.add(rs.getDouble("COMM"));
rows.add(vectors);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(rs,pst,conn);
}
return rows;
}
@Override
public Vector<Vector<Object>> select5() {
Vector<Vector<Object>> rows = new Vector<> ();
Vector<Object> vectors;
conn = getConn();
String sql = "select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno\n" +
" where job='ANALYST'";
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
if (rs!=null) {
while (rs.next()){
vectors = new Vector<>();
vectors.add(rs.getString("ename"));
vectors.add(rs.getString("dname"));
rows.add(vectors);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(rs,pst,conn);
}
return rows;
}
@Override
public void select6() {
conn = getConn();
String sql = "update emp set sal = sal*1.1 where (to_days(now()) - to_days(hiredate))/365 > 3";
try {
pst = conn.prepareStatement(sql);
int i = pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(rs,pst,conn);
}
}
@Override
public Vector<Vector<Object>> select7() {
Vector<Vector<Object>> rows = new Vector<> ();
Vector<Object> vectors;
conn = getConn();
String sql = "select d.dname, sum(e.sal), count(e.empno) from emp e right join dept d on e.deptno = d.deptno group by d.dname having d.dname like '%S%'";
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
if (rs!=null) {
while (rs.next()){
vectors = new Vector<>();
vectors.add(rs.getString("dname"));
vectors.add(rs.getDouble("sum(e.sal)"));
vectors.add(rs.getInt("count(e.empno)"));
rows.add(vectors);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(rs,pst,conn);
}
return rows;
}
@Override
public Vector<Vector<Object>> select8() {
Vector<Vector<Object>> rows = new Vector<> ();
Vector<Object> vectors;
conn = getConn();
String sql = "select e.ename,g.ename from emp e join emp g on e.mgr=g.empno";
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
if (rs!=null) {
while (rs.next()){
vectors = new Vector<>();
vectors.add(rs.getString("ename"));
vectors.add(rs.getString("ename"));
rows.add(vectors);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeAll(rs,pst,conn);
}
return rows;
}
}
界面如下:
界面代码
package com.hr.ui;
import com.hr.dao.SelectDao;
import com.hr.dao.impl.SelectDaoImpl;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.Vector;
/**
* 项目:
* 时间: 2020/8/22 19:40
*
* @author 彭维新
*/
public class TablePanel2 extends JPanel {
DefaultTableModel dtm;
SelectDao sd = new SelectDaoImpl();
public TablePanel2() {
//设置大小
this.setSize(1000,400);
//设置布局
setLayout(new BorderLayout());
//设置颜色
setBackground(Color.CYAN);
//上部分 操作
JPanel top = new JPanel();
JButton btn1 = new JButton("最高薪水");
JButton btn2 = new JButton("薪水等级");
JButton btn3 = new JButton("最近入职");
JButton btn4 = new JButton("部门加员工");
JButton btn5 = new JButton("有关SCOTT");
JButton btn6 = new JButton("加薪10%");
JButton btn7 = new JButton("S有关部门");
JButton btn8 = new JButton("所有员工");
top.add(btn1);
top.add(btn2);
top.add(btn3);
top.add(btn4);
top.add(btn5);
top.add(btn6);
top.add(btn7);
top.add(btn8);
//下部分 表格
JPanel bottom = new JPanel();
bottom.setLayout(new BorderLayout());
JScrollPane scrollPane = new JScrollPane();
dtm = new DefaultTableModel();
JTable table = new JTable(dtm);
//表格放进滚动面板
scrollPane.setViewportView(table);
//把滚动面板放到底部
bottom.add(scrollPane,BorderLayout.CENTER);
//放到大面板
this.add(top,BorderLayout.NORTH);
this.add(bottom,BorderLayout.CENTER);
//添加事件监听
btn1.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
Vector<String> cols = new Vector<>();
cols.add("部门名称");
cols.add("部门编号");
cols.add("最高薪水");
//填充数据
//赋值
Vector<Vector<Object>> rows = sd.select1();
dtm.setDataVector(rows,cols);
}
});
btn2.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
Vector<String> cols = new Vector<>();
cols.add("部门名称");
cols.add("工资");
cols.add("工资等级");
//填充数据
//赋值
Vector<Vector<Object>> rows = sd.select2();
dtm.setDataVector(rows,cols);
}
});
btn3.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
Vector<String> cols = new Vector<>();
cols.add("名字");
cols.add("入职日期");
//填充数据
//赋值
Vector<Vector<Object>> rows = sd.select3();
dtm.setDataVector(rows,cols);
}
});
btn4.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
Vector<String> cols = new Vector<>();
cols.add("部门编号");
cols.add("员工编号");
cols.add("名字");
cols.add("职位");
cols.add("领导编号");
cols.add("入职日期");
cols.add("工资");
cols.add("奖金");
//填充数据
//赋值
Vector<Vector<Object>> rows = sd.select4();
dtm.setDataVector(rows,cols);
}
});
btn5.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
Vector<String> cols = new Vector<>();
cols.add("名字");
cols.add("部门");
//填充数据
//赋值
Vector<Vector<Object>> rows = sd.select5();
dtm.setDataVector(rows,cols);
}
});
btn6.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
sd.select6();
}
});
btn7.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
Vector<String> cols = new Vector<>();
cols.add("部门");
cols.add("工资合计");
cols.add("人数");
//填充数据
//赋值
Vector<Vector<Object>> rows = sd.select7();
dtm.setDataVector(rows,cols);
}
});
btn8.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
Vector<String> cols = new Vector<>();
cols.add("员工");
cols.add("领导");
//填充数据
//赋值
Vector<Vector<Object>> rows = sd.select8();
dtm.setDataVector(rows,cols);
}
});
}
}
调用代码
package com.hr.ui;
import javax.swing.*;
/**
* 项目:
* 时间: 2020/8/22 19:38
*
* @author 彭维新
*/
public abstract class BaseFrame extends JFrame {
public BaseFrame(String title,int w ,int h ){
//设置标题
setTitle(title);
//设置窗口大小
setSize(w,h);
initView();
listener();
//设置居中
setLocationRelativeTo(null);
//设置可见
setVisible(true);
//退出关闭
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
/**
* 初始化控件
*/
public abstract void initView();
/**
* 事件监听
*/
public abstract void listener();
}
package com.hr.test;
import com.hr.ui.BaseFrame;
import com.hr.ui.TablePanel2;
import java.awt.*;
/**
* 项目:
* 时间: 2020/8/22 19:38
*
* @author 彭维新
*/
public class TestEmployee extends BaseFrame {
public static void main(String[] args) {
new TestEmployee("员工信息", 1000, 800).setVisible(true);
}
public TestEmployee(String title, int w, int h) {
super(title, w, h);
}
@Override
public void initView() {
Container container = getContentPane();
container.setLayout(new BorderLayout());
TablePanel2 tablePanel2 = new TablePanel2();
container.add(tablePanel2, BorderLayout.CENTER);
}
@Override
public void listener() {
}
}
其它代码
实体
package com.hr.entity;
/**
* 项目:
* 时间: 2020/8/22 19:33
*
* @author 彭维新
*/
public class Dept {
/**
* 部门编号
*/
protected Integer deptNo;
/**
* 部门名称
*/
protected String dName;
/**
* 部门地址
*/
protected String loc;
public Dept() {
}
public Dept(Integer deptNo, String dName, String loc) {
this.deptNo = deptNo;
this.dName = dName;
this.loc = loc;
}
public Dept(String dName, String loc) {
this.dName = dName;
this.loc = loc;
}
public Integer getDeptNo() {
return deptNo;
}
public void setDeptNo(Integer deptNo) {
this.deptNo = deptNo;
}
public String getdName() {
return dName;
}
public void setdName(String dName) {
this.dName = dName;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
@Override
public String toString() {
return "Dept{" +
"deptNo='" + deptNo + '\'' +
", dName='" + dName + '\'' +
", loc='" + loc + '\'' +
'}';
}
}
package com.hr.entity;
import java.util.Date;
/**
* 项目:
* 时间: 2020/8/22 19:33
*
* @author 彭维新
*/
public class Emp {
/**
* 员工编号
*/
protected Integer empNo;
/**
*员工姓名
*/
protected String eName;
/**
*员工工作
*/
protected String job;
/**
*员工直属领导编号
*/
protected int mgr;
/**
*入职时间
*/
protected Date hireDate;
/**
*工资
*/
protected double sal;
/**
*奖金
*/
protected double comm;
/**
*部门编号
*/
protected Integer deptNo;
public Emp() {
}
public Emp(Integer empNo, String eName, String job, int mgr, Date hireDate, double sal, double comm, Integer deptNo) {
this.empNo = empNo;
this.eName = eName;
this.job = job;
this.mgr = mgr;
this.hireDate = hireDate;
this.sal = sal;
this.comm = comm;
this.deptNo = deptNo;
}
public Emp(String eName, String job, int mgr, Date hireDate, double sal, double comm, Integer deptNo) {
this.eName = eName;
this.job = job;
this.mgr = mgr;
this.hireDate = hireDate;
this.sal = sal;
this.comm = comm;
this.deptNo = deptNo;
}
public Integer getEmpNo() {
return empNo;
}
public void setEmpNo(Integer 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 int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
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 Integer getDeptNo() {
return deptNo;
}
public void setDeptNo(Integer deptNo) {
this.deptNo = deptNo;
}
@Override
public String toString() {
return "Emp{" +
"empNo=" + empNo +
", eName='" + eName + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", hireDate=" + hireDate +
", sal=" + sal +
", comm=" + comm +
", deptNo=" + deptNo +
'}';
}
}
package com.hr.entity;
/**
* 项目:
* 时间: 2020/8/22 19:33
*
* @author 彭维新
*/
public class SalGrade{
/**
*等级
*/
protected int grade;
/**
*最低工资
*/
protected double loSal;
/**
*最高工资
*/
protected double hiSal;
public SalGrade() {
}
public SalGrade(int grade, double loSal, double hiSal) {
this.grade = grade;
this.loSal = loSal;
this.hiSal = hiSal;
}
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
public double getLoSal() {
return loSal;
}
public void setLoSal(double loSal) {
this.loSal = loSal;
}
public double getHiSal() {
return hiSal;
}
public void setHiSal(double hiSal) {
this.hiSal = hiSal;
}
@Override
public String toString() {
return "SalGrade{" +
"grade=" + grade +
", loSal=" + loSal +
", hiSal=" + hiSal +
'}';
}
}
数据库连接公共类
package com.hr.db;
import java.sql.*;
/**
* 项目:
* 时间: 2020/8/22 19:32
*
* @author 彭维新
*/
public class DbHelper {
/**
* 数据库连接对象
*/
protected static Connection conn;
/**
* 预编译
*/
protected PreparedStatement pst;
/**
* 结果集
*/
protected ResultSet rs;
/**
* 地址、账号、密码写外面,方便修改
*/
private static final String URL = "jdbc:mysql://localhost:3306/java95?useUnicode=true&characterEncoding=utf-8&useSSL=false";
private static final String USER_NAME = "dog";
private static final String PASSWORD = "52Java";
public static Connection getConn() {
try {
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//通过驱动得到Connection对象
String url = URL;
//必须接着
conn = DriverManager.getConnection(url, USER_NAME, PASSWORD);
} catch (ClassNotFoundException e) {
System.out.println("加载数据库异常");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("url、账号或密码错误");
e.printStackTrace();
}
return conn;
}
/**
* 关闭数据库
*
* @param rs 1.结果集
* @param pst 2.预编译
* @param conn 3.数据库连接对象
*/
public static void closeAll(ResultSet rs, PreparedStatement pst, Connection conn) {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (pst != null) {
pst.close();
pst = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}