Mybatis使用Mapper接口—————对数据库进行增删改查
需求:使用Mapper接口对数据库中的表进行CRUD
项目结构
target是项目编译之后生成的
1.在数据库中创建表
CREATE TABLE employee(
id INT(11) PRIMARY KEY auto_increment,
name VARCHAR(30),
age INT(11),
birthday date,
salary DECIMAL(10,0)
)
2.准备工作
2.1在项目的pom.xml文件中添加mysql,mybatis等依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.ujiuye</groupId>
<artifactId>mybatis002</artifactId>
<version>1.0.0</version>
<dependencies>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
</dependency>
</dependencies>
</project>
2.2数据库信息db.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///db3
username=root
password=1234
2.3mybatis-config.xml核心配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties"/>
<typeAliases>
<!--配置包,整个包下面的类都会被注册别名,默认使用类名作为别名,不区分大小写-->
<package name="com.ujiuye.bean"/>
</typeAliases>
<!--配置环境-->
<environments default="mysql">
<!--配置默认的环境-->
<environment id="mysql">
<!--配置事务-->
<transactionManager type="JDBC"></transactionManager>
<!--配置连接池-->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--关联映射文件-->
<mappers>
<mapper resource="com/ujiuye/mapper/EmployeeMapper.xml"/>
</mappers>
</configuration>
2.4工具类
2.4.1获取sqlSession的mybatisUtil工具类
package com.ujiuye.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class MybatisUtil {
//需要将sqlSessionFactory设置为静态的,否则静态代码块中和获取sqlSession中的方法不能引用
private static SqlSessionFactory sqlSessionFactory = null;
//静态代码块,只加载一次
static {
String resource = "mybatis-config.xml";
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream(resource);
} catch (IOException e) {
e.printStackTrace();
}
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
//一定记得加上static修饰,否则其他静态方法中不可以调用
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
2.4.1字符串类型和日期类型的相互转换工具类
package com.ujiuye.utils;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateUtil {
public static Date stringToDate(String str){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = null;
try {
date = sdf.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
return date;
}
public static String dateToString(Date date){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(date);
}
}
3.创建实体类Employee.java
package com.ujiuye.bean;
import lombok.*;
import java.math.BigDecimal;
import java.util.Date;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Employee {
private Integer id;
private String name;
private Integer age;
private Date birthday;
private BigDecimal salary;
}
4.EmployeeMapper.java接口
package com.ujiuye.mapper;
import com.ujiuye.bean.Employee;
import java.util.List;
public interface EmployeeMapper {
//增
public void insertEmployee(Employee employee);
//删
public void deleteEmployeeById(int id);
//改
public void updateEmployee(Employee employee);
//查全部
public List<Employee> findAll();
//查一个
public Employee findEmployeeById(int id);
}
5.EmployeeMapper.xml映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<!--关联到EmployeeMapper接口-->
<!--namespace的值必须等于EmployeeMapper接口的全限定名-->
<mapper namespace="com.ujiuye.mapper.EmployeeMapper">
<!--
resultMap结果集映射标签
id:当前结果集映射的唯一标识
type:把结果集封装成的对象的类型
column:数据库中表的列名
property:实体类中的属性名
即使数据库中的字段名和实体类的属性名不同,但通过column和property可以使数据库中的字段和实体类的属性一一对应
-->
<resultMap id="empResultMap" type="Employee">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="age" property="age"></result>
<result column="birthday" property="birthday"></result>
<result column="salary" property="salary"></result>
<result column="password" property="password"></result>
</resultMap>
<!--
sql的id,必须等于EmployeeMapper接口中的方法名,
parameterType必须跟对应的方法的类型相同,
方法的返回值必须跟resultTye或者resultMap的值保持一致
-->
<select id="findEmployeeById" parameterType="int" resultMap="empResultMap">
select id, name, age, birthday, salary FROM employee WHERE id = #{id}
</select>
<select id="findAll" resultMap="empResultMap" >
select id,name,age,birthday,salary from employee
</select>
<insert id="insertEmployee" parameterType="employee" >
insert into employee (name,age,birthday,salary) values(#{name},#{age},#{birthday},#{salary})
</insert>
<update id="updateEmployee" parameterType="employee" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
update employee set name = #{name},age = #{age},birthday = #{birthday} ,salary = #{salary} where id = #{id}
</update>
<delete id="deleteEmployeeById" parameterType="int" >
delete from employee where id = #{id}
</delete>
</mapper>