一、搭建Mybatis框架
可输出表格数据,搭建成功!
二、进行增删改查操作(在UserMapper.xml中修改</mapper>之间的内容)
1.检索所有的职工姓名与年薪
修改UserMapper.xml里的代码为以下
<select id="selectAll" resultType="org.example.pojo.User">
select ename,Sal from emp;
</select>
修改User类中toSpring函数为
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append("User{");
if(empno != 0) {
sb.append("empno=").append(empno);
}
if (ename != null) {
sb.append(", ename='").append(ename).append('\'');
}
if (Job != null) {
sb.append(", Job='").append(Job).append('\'');
}
if (Mgr != null) {
sb.append(", Mgr='").append(Mgr).append('\'');
}
if (Hiredate != null) {
sb.append(", Hiredate='").append(Hiredate).append('\'');
}
if (Sal != 0.0) {
sb.append(", Sal=").append(Sal);
}
if (Comm != 0.0) {
sb.append(", Comm=").append(Comm);
}
if(deptno != 0) {
sb.append(", deptno=").append(deptno);
}
sb.append('}');
return sb.toString();
}
其中sb.append()的作用是向这个stringbuilder的末尾添加字符串。
(篇幅原因省略后续输出)
2. 检索所有的工作和相应的部门编号
<select id="selectAll" resultType="org.example.pojo.User">
select Job,deptno from emp;
</select>
(篇幅原因省略后续输出)
3.检索工作是salesman的员工姓名
<select id="selectAll" resultType="org.example.pojo.User">
select ename from emp where Job = "SALESMAN";
</select>
4.检索员工津贴comm为null或comm小于300的员工姓名和津贴值
PS:“<”在.xml文件中有特殊含义,应该用“<”替代。
<select id="selectAll" resultType="org.example.pojo.User">
SELECT ename, Comm FROM emp WHERE Comm IS NULL OR Comm < 300;
</select>
(篇幅原因省略后续输出)
5.检索所有职员的姓名和所在部门名称
<select id="selectAll" resultType="org.example.pojo.User">
select emp.ename,dept.Dname from emp join dept on emp.deptno = dept.Deptno;
</select>
PS:由于涉及到两个表,因此要在User类中添加新的变量并get/set,在输出语句中也要增加输出,修改后的代码如下
package org.example.pojo;
import java.awt.*;
public class User {
private int empno;
private String ename;
private String Job;
private String Mgr;
private String Hiredate;
private double Sal;
private double Comm;
private int deptno;
private int Deptno;
private String Dname;
private String Loc;
public int getDeptno() {
return Deptno;
}
public String getDname() {
return Dname;
}
public String getLoc() {
return Loc;
}
public int getEmpno() {
return empno;
}
public String getEname() {
return ename;
}
public String getJob() {
return Job;
}
public String getMgr() {
return Mgr;
}
public String getHiredate() {
return Hiredate;
}
public double getSal() {
return Sal;
}
public double getComm() {
return Comm;
}
public int getdeptno() {
return deptno;
}
public void setDname(String dname) {
this.Dname = dname;
}
public void setLoc(String loc) {
this.Loc = loc;
}
public void setDeptno(int Deptno) {
this.Deptno = Deptno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public void setEname(String ename) {
this.ename = ename;
}
public void setJob(String job) {
Job = job;
}
public void setMgr(String mgr) {
Mgr = mgr;
}
public void setHiredate(String hiredate) {
Hiredate = hiredate;
}
public void setSal(double sal) {
Sal = sal;
}
public void setComm(double comm) {
Comm = comm;
}
public void setdeptno(int deptno) {
this.deptno = deptno;
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append("User{");
if(empno != 0) {
sb.append("empno=").append(empno);
}
if (ename != null) {
sb.append(", ename='").append(ename).append('\'');
}
if (Job != null) {
sb.append(", Job='").append(Job).append('\'');
}
if (Mgr != null) {
sb.append(", Mgr='").append(Mgr).append('\'');
}
if (Hiredate != null) {
sb.append(", Hiredate='").append(Hiredate).append('\'');
}
if (Sal != 0.0) {
sb.append(", Sal=").append(Sal);
}
if (Comm != 0.0) {
sb.append(", Comm=").append(Comm);
}
if(deptno != 0) {
sb.append(", deptno=").append(deptno);
}
if(Deptno != 0) {
sb.append(", Deptno=").append(Deptno);
}
if(Dname != null) {
sb.append(", Dname=").append(Dname);
}
if(Loc != null) {
sb.append(", Loc=").append(Loc);
}
sb.append('}');
return sb.toString();
}
}
(篇幅原因省略后续输出)
6.检索在任何位置有字母“A”的员工姓名
<select id="selectAll" resultType="org.example.pojo.User">
select ename from emp where ename collate utf8mb4_bin LIKE '%A%' or job collate utf8mb4_bin LIKE '%A%';
</select>
(篇幅原因省略后续输出)
7.往eemp表格中插入五条记录
修改mybatis-config.xml文件(Mybatis的基本配置),转化成你要操作的数据库名
<property name="url" value="jdbc:mysql://localhost:3306/你实际用的数据库名?useSSl=true"/>
修改User类,添加和删除表格对应的变量,建立set/get,修改输出语句
package org.example.pojo;
import java.awt.*;
public class User {
private int empno;
private String ename;
private String job;
private String hiredate;
private double salary;
private double comm;
private int deptno;
public void setEmpno(int empno) {
this.empno = empno;
}
public void setEname(String ename) {
this.ename = ename;
}
public void setJob(String job) {
this.job = job;
}
public void setHiredate(String hiredate) {
this.hiredate = hiredate;
}
public void setSalary(double salary) {
this.salary = salary;
}
public void setComm(double comm) {
this.comm = comm;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public int getEmpno() {
return empno;
}
public String getEname() {
return ename;
}
public String getJob() {
return job;
}
public String getHiredate() {
return hiredate;
}
public double getSalary() {
return salary;
}
public double getComm() {
return comm;
}
public int getDeptno() {
return deptno;
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append("User{");
if(empno != 0) {
sb.append("empno=").append(empno);
}
if (ename != null) {
sb.append(", ename='").append(ename).append('\'');
}
if (job != null) {
sb.append(", Job='").append(job).append('\'');
}
if (hiredate != null) {
sb.append(", hiredate='").append(hiredate).append('\'');
}
if (salary != 0.0) {
sb.append(", Sal=").append(salary);
}
if (comm != 0.0) {
sb.append(", Comm=").append(comm);
}
if(deptno != 0) {
sb.append(", deptno=").append(deptno);
}
sb.append('}');
return sb.toString();
}
}
修改Mybatis_demo里的语句,把test.selectAll改成insertUser
List<User> users = sqlSession.selectList("test.insertUser");
把select语句删除,修改成 insert语句,插入数据
<insert id="insertUser" parameterType="org.example.pojo.User">
insert into eemp (empno, ename, job, deptno)
values (6,'Harry','SALESMAN',2),(7,'Hermione','Manager',1),(8,'Dobby','CLERK',2),(9,'Filch','SALESMAN',3),(10,'Eid','Manager',3);
</insert>
刷新表格,发现插入成功(6-10为新插入的数据,部分数据未插入)
8.从eemp中删除名为“Dobby”的记录
修改Mybatis_demo里的语句,把test.insertUser改成deleteUser
List<User> users = sqlSession.selectList("test.deleteUser");
把insert语句删除,修改成delete语句
<delete id="deleteUser" parameterType="int">
delete from eemp where ename = 'Dobby';
</delete>
刷新表格,名为“Dobby”的那行数据消失
完结撒花~~!!