mybatis插入date类型_使用Mybatis完成如下操作：

一、概述

1.项目环境：jdk版本：jdk-10.0.1，IDE版本：Eclipse Java EE IDE for Web Developers (4.7.3a)，

字符集：UTF-8，操作系统版本：Windows7旗舰版

2.完成时间：2019.3.13

3.作者：任鑫

4.项目花费时间：5小时

二.、项目描述

　　使用Mybatis完成如下操作：

　　1. 随机插入100个雇员信息。雇员姓名由字母随机组成，可重复;入职时间随机分布在2016-2018之间;出生日期随机分布在2000-2003年之间;薪水随机分布在3000-30000之间。部门随机分布在三个部门。

　　2. 可以根据ID删除某个雇员的信息

　　3. 根据传入的ID，修改薪水

　　4. 根据传入的雇员ID，修改为其他部门

　　5. 可以查询出指定薪水范围的雇员信息

　　6. 可以查询出指定部门的雇员信息

　　7. 查询出高于平均薪水的雇员的信息

项目结构：

三、源代码

EmployeeServiceImpl类

package com.bisxt.service;import java.sql.Date;import java.util.List;import org.apache.ibatis.session.SqlSession;import com.bjsxt.mapper.EmployeeMapper;import com.bjsxt.pojo.Employee;import com.bjsxt.util.MyBatisUtil;public class EmployeeServiceImpl implements EmployeeService{@Overridepublic List<Employee> selAll() {SqlSession session = MyBatisUtil.getSession();EmployeeMapper empMapper = session.getMapper(EmployeeMapper.class);List<Employee> list = empMapper.selAll(); session.close();return list;}@Overridepublic Boolean insAllEmp() {SqlSession session = MyBatisUtil.getSession();EmployeeMapper empMapper = session.getMapper(EmployeeMapper.class);int num=0;long time1 = Date.valueOf("2016-1-1").getTime();long time2 = Date.valueOf("2000-1-1").getTime();for(int i=1; i<=100; i++) {StringBuilder str = new StringBuilder();for(int j=0;j<=(2+Math.random()*3);j++) {char st = (char) ('a' + (char)(Math.random()*26)); str.append(st);}String name = str.toString();long time = time1 + (long)(Math.random()*(365*3+1)*24*60*60*1000);Date hiredate = new Date(time);time = time2 + (long)(Math.random()*(365*4+1)*24*60*60*1000);Date birthday = new Date(time);int salary = (int)(3000+Math.random()*(30000-3000));int deptno =((int)(1+Math.random()*3))*10;Employee emp = new Employee(i,name,hiredate,birthday,salary,deptno);num += empMapper.insert(emp);}Boolean flag;if(num==100) {session.commit();flag = true;}else {session.rollback();;flag = false;}session.close();return flag;}@Overridepublic Boolean delEmp(int id) {SqlSession session = MyBatisUtil.getSession();EmployeeMapper empMapper = session.getMapper(EmployeeMapper.class);int num = empMapper.delEmp(id);if(num==1) {session.commit();return true;}else {session.rollback();return false;}}@Overridepublic Boolean updsalaryById(int id, int salary) {SqlSession session = MyBatisUtil.getSession();EmployeeMapper empMapper = session.getMapper(EmployeeMapper.class); int num = empMapper.updsalaryById(id, salary);if(num==1) {session.commit();return true;}else {session.rollback();return false;}}@Overridepublic Boolean updeptnoById(int id, int deptno) {SqlSession session = MyBatisUtil.getSession();EmployeeMapper empMapper = session.getMapper(EmployeeMapper.class); int num = empMapper.updeptnoById(id, deptno);if(num==1) {session.commit();return true;}else {session.rollback();return false;}}@Overridepublic List<Employee> selBysalary(int low_salary, int high_salary) {SqlSession session = MyBatisUtil.getSession();EmployeeMapper empMapper = session.getMapper(EmployeeMapper.class);List<Employee> list = empMapper.selBysalary(low_salary, high_salary);session.close();return list;}@Overridepublic List<Employee> selBydeptno(int deptno) {SqlSession session = MyBatisUtil.getSession();EmployeeMapper empMapper = session.getMapper(EmployeeMapper.class);List<Employee> list = empMapper.selBydeptno(deptno); session.close();return list;}@Overridepublic List<Employee> selByavgsalary() {SqlSession session = MyBatisUtil.getSession();EmployeeMapper empMapper = session.getMapper(EmployeeMapper.class);List<Employee> list = empMapper.selByavgsalary(); session.close();return list;}}

EmployeeMapper接口

package com.bjsxt.mapper;import java.util.List;import org.apache.ibatis.annotations.Update;import com.bjsxt.pojo.Employee;public interface EmployeeMapper {/*** 读取所有员工信息* @return*/List<Employee> selAll();/*** 插入单个员工信息* @param emp* @return*/int insert(Employee emp);/*** 根据id删除员工信息* @param id* @return*/int delEmp(int id);@Update("update employee set salary=#{1} where id=#{0}")int updsalaryById(int id, int salary);@Update("update employee set deptno=#{1} where id=#{0}")int updeptnoById(int id, int deptno);List<Employee> selBysalary(int low_salary,int high_salary);List<Employee> selBydeptno(int deptno);List<Employee> selByavgsalary();}

EmployeeMapper.xml文件

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.bjsxt.mapper.EmployeeMapper"><sql id="sel">select e.*,d.dname, d.addressfrom employee eleft join department don e.deptno=d.deptno</sql><resultMap type="employee" id="emap"> <id property="id" column="id"/><result property="name" column="name"/><result property="hiredate" column="hiredate"/><result property="birthday" column="birthday"/><result property="salary" column="salary"/><result property="deptno" column="deptno"/><association property="dept" javaType="department"><id property="deptno" column="deptno" /><result property="dname" column="dname"/><result property="address" column="address"/></association></resultMap><select id="selAll" resultMap="emap" ><include refid="sel"/></select><select id="selBysalary" resultMap="emap"><include refid="sel"/>where e.salary &gt;= #{0} and e.salary &lt;= #{1}</select><select id="selBydeptno" resultMap="emap"><include refid="sel"/>where e.deptno=#{0}</select><select id="selByavgsalary" resultMap="emap"><include refid="sel"/>where salary > (select avg(salary) from employee);</select><insert id="insert" parameterType="Employee"> insert into employee values (#{id},#{name},#{hiredate},#{birthday},#{salary},#{deptno})</insert><delete id="delEmp" parameterType="int">delete from employee where id=#{0};</delete></mapper>

Department类

package com.bjsxt.pojo;import java.io.Serializable;public class Department implements Serializable{private int deptno;private String dname;private String address;public Department() {super();}public Department(int deptno, String dname, String address) {super();this.deptno = deptno;this.dname = dname;this.address = address;}@Overridepublic String toString() {return "department [deptno=" + deptno + ", dname=" + dname + ", address=" + address + "]";}@Overridepublic int hashCode() {final int prime = 31;int result = 1;result = prime * result + ((address == null) ? 0 : address.hashCode());result = prime * result + deptno;result = prime * result + ((dname == null) ? 0 : dname.hashCode());return result;}@Overridepublic boolean equals(Object obj) {if (this == obj)return true;if (obj == null)return false;if (getClass() != obj.getClass())return false;Department other = (Department) obj;if (address == null) {if (other.address != null)return false;} else if (!address.equals(other.address))return false;if (deptno != other.deptno)return false;if (dname == null) {if (other.dname != null)return false;} else if (!dname.equals(other.dname))return false;return true;}public int getDeptno() {return deptno;}public void setDeptno(int deptno) {this.deptno = deptno;}public String getDname() {return dname;}public void setDname(String dname) {this.dname = dname;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;} }

Employee类

package com.bjsxt.pojo;import java.io.Serializable;import java.sql.Date;public class Employee implements Serializable{private int id;private String name;private Date hiredate;private Date birthday;private int salary;private int deptno;private Department dept;public Employee() {super();}public Employee(int id, String name, Date hiredate, Date birthday, int salary, int deptno) {super();this.id = id;this.name = name;this.hiredate = hiredate;this.birthday = birthday;this.salary = salary;this.deptno = deptno;}public Employee(int id, String name, Date hiredate, Date birthday, int salary, int deptno, Department dept) {super();this.id = id;this.name = name;this.hiredate = hiredate;this.birthday = birthday;this.salary = salary;this.deptno = deptno;this.dept = dept;}@Overridepublic String toString() {return "Employee [id=" + id + ", name=" + name + ", hiredate=" + hiredate + ", birthday=" + birthday+ ", salary=" + salary + ", deptno=" + deptno + ", dept=" + dept + "]";}@Overridepublic int hashCode() {final int prime = 31;int result = 1;result = prime * result + ((birthday == null) ? 0 : birthday.hashCode());result = prime * result + ((dept == null) ? 0 : dept.hashCode());result = prime * result + deptno;result = prime * result + ((hiredate == null) ? 0 : hiredate.hashCode());result = prime * result + id;result = prime * result + ((name == null) ? 0 : name.hashCode());result = prime * result + salary;return result;}@Overridepublic boolean equals(Object obj) {if (this == obj)return true;if (obj == null)return false;if (getClass() != obj.getClass())return false;Employee other = (Employee) obj;if (birthday == null) {if (other.birthday != null)return false;} else if (!birthday.equals(other.birthday))return false;if (dept == null) {if (other.dept != null)return false;} else if (!dept.equals(other.dept))return false;if (deptno != other.deptno)return false;if (hiredate == null) {if (other.hiredate != null)return false;} else if (!hiredate.equals(other.hiredate))return false;if (id != other.id)return false;if (name == null) {if (other.name != null)return false;} else if (!name.equals(other.name))return false;if (salary != other.salary)return false;return true;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Date getHiredate() {return hiredate;}public void setHiredate(Date hiredate) {this.hiredate = hiredate;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public int getSalary() {return salary;}public void setSalary(int salary) {this.salary = salary;}public int getDeptno() {return deptno;}public void setDeptno(int deptno) {this.deptno = deptno;}public Department getDept() {return dept;}public void setDept(Department dept) {this.dept = dept;}}

TestMyBatis测试类

package com.bjsxt.test;import com.bjsxt.view.MenuView;public class TestMyBatis {public static void main(String[] args){MenuView show = new MenuView();show.showMenu(); }}

MyBatisUtil工具类

package com.bjsxt.util;import java.io.IOException;import java.io.InputStream;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;public class MyBatisUtil {private static SqlSessionFactory factory = null;static {//加载MyBatis核心配置文件InputStream is=null;try {is = Resources.getResourceAsStream("mybatis.xml");//构建SqlSessionFactory工厂对象 factory = new SqlSessionFactoryBuilder().build(is);} catch (IOException e) {// TODO 自动生成的 catch 块e.printStackTrace();} }public static SqlSession getSession() {SqlSession session = null;if(factory!=null) {session = factory.openSession();}return session;}}

MenuView类

package com.bjsxt.view;import java.util.List;import java.util.Scanner;import com.bisxt.service.EmployeeServiceImpl;import com.bjsxt.pojo.Employee;public class MenuView {Scanner sc = new Scanner(System.in);EmployeeServiceImpl empservice = new EmployeeServiceImpl();public void showMenu(){System.out.println("**********************************");System.out.println("**********欢迎使用员工信息管理系统********");System.out.println("**********************************");System.out.println("请输入对应数字进行操作:");while(true) {System.out.println("1. 插入100个雇员信息");System.out.println("2. 查询所有雇员信息");System.out.println("3. 根据ID删除某个雇员的信息");System.out.println("4. 根据传入的ID，修改薪水"); System.out.println("5. 根据传入的雇员ID，修改为其他部门");System.out.println("6. 查询出指定薪水范围的雇员信息");System.out.println("7. 查询出指定部门的雇员信息");System.out.println("8. 查询出高于平均薪水的雇员的信息");System.out.println("9. 退出");int num = sc.nextInt();switch (num) {case 1:System.out.println(empservice.insAllEmp()==true? "插入成功":"插入失败");continue;case 2:selAll();continue;case 3:delEmp();continue;case 4:updsalaryById();continue;case 5:updeptnoById();continue; case 6:selBysalary();continue; case 7:selBydeptno();continue; case 8:selByavgsalary();continue;case 9:System.out.println("谢谢使用! 拜拜~");break;default:System.out.println("输入有误, 请重新输入!");continue;}break;// 结束循环}}private void selByavgsalary() {List<Employee> list = empservice.selByavgsalary();for (Employee emp : list) {System.out.println(emp);}}private void selBydeptno() {System.out.println("请输入部门编号为10,20或30:");int deptno = sc.nextInt();while(deptno!=10&&deptno!=20&&deptno!=30) {System.out.println("请重新输入部门编号为10,20或30:");deptno = sc.nextInt();}List<Employee> list = empservice.selBydeptno(deptno);for (Employee emp : list) {System.out.println(emp);}}private void selBysalary() {System.out.println("请输入工资下限:");int low_salary = sc.nextInt();System.out.println("请输入工资上限:");int high_salary = sc.nextInt();List<Employee> list = empservice.selBysalary(low_salary, high_salary);for (Employee emp : list) {System.out.println(emp);}}private void updeptnoById() {System.out.println("请输入ID:");int id = sc.nextInt();System.out.println("请输入部门编号为10,20或30:");int deptno = sc.nextInt();while(deptno!=10&&deptno!=20&&deptno!=30) {System.out.println("请重新输入部门编号为10,20或30:");deptno = sc.nextInt();}if(empservice.updeptnoById(id, deptno)) {System.out.println("修改成功!");} else {System.out.println("修改失败!");}}private void updsalaryById() {System.out.println("请输入ID:");int id = sc.nextInt();System.out.println("请输入工资:");int salary = sc.nextInt();if(empservice.updsalaryById(id, salary)) {System.out.println("修改成功!");} else {System.out.println("修改失败!");}}private void delEmp() {System.out.println("请输入ID:");int id = sc.nextInt();if(empservice.delEmp(id)) {System.out.println("删除成功!");} else {System.out.println("删除失败!");}}private void selAll() {List<Employee> list = empservice.selAll();for (Employee emp : list) {System.out.println(emp);}}}