MyBatis的注解开发
MyBatis提供了下面注解进行映射文件配置
@Select 查询数据注解
@Insert 插入数据注解
@Delete 删除数据注解
@Update 修改数据注解
@Options 选项配置
@Results 手动映射配置
@Result : @results中的具体的某一列的映射信息配置
主配置文件 mybatis-config.xml
<!-- xml 约束,约束下面可以使用那些标签 (元素)-->
<?xml version="1.0" encoding="UTF-8" ?><!-- xml声明,固定语法 -->
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--
开启Mybatis的配置
<configuration> 根元素,任何html文件都有一个根元素
-->
<configuration>
<!-- 读取数据库配置文件 -->
<properties resource="db.properties"/>
<!-- 设置 -->
<settings>
<!-- 开启支持驼峰命名法 -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!-- 配置别名 -->
<typeAliases>
<!-- 配置单个别名 -->
<typeAlias type="com.ywq.mybatis.pojo.User" alias="User"/>
<!-- 使用包扫描配置别名,包下面所有pojo类都回设置别名,简单类名首字母变小写(不区分大小写) -->
<package name="com.ywq.mybatis.pojo"/>
</typeAliases>
<!--
配置Mybatis的环境
<environments default="mysql">
default:默认的环境,值就是内部<environment id="mysql">的id值
-->
<environments default="mysql">
<!--
具体环境标识
<environment id="mysql">
id:当前环境唯一标识 见名知意
-->
<environment id="mysql">
<!-- 事务管理器(操作数据库有事务概念),使用默认JDBC (MyBatis底层就是封装原生JDBC)事务 -->
<transactionManager type="JDBC"/>
<!-- 配置数据域(连接池)
<dataSource type="POOLED">
type:数据域类型POOLED,MyBatis内置的一个数据域
使用框架开发连接数据全部都是使用数据源连接,使用阿里巴巴连接池,Apache的DBCP连接池(和Spring框架集成详细配置)
-->
<dataSource type="POOLED">
<!-- 数据库驱动 -->
<property name="driver" value="${driverClassName}"/>
<!-- 连接数据库url地址 -->
<property name="url" value="${url}"/>
<!-- 数据库账号 -->
<property name="username" value="${username}"/>
<!-- 数据库密码 -->
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- 配置MyBatis的映射文件 -->
<mappers>
<!-- 配置映射接口 -->
<!-- <mapper class="接口的全限定名"/> -->
<mapper class="com.ywq.mybatis.mapper.UserMapper"/>
</mappers>
</configuration>
获取Sql工厂工具
package com.ywq.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.Null;
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 MybatisUtils {
public static SqlSessionFactory sessionFactory = null;
static {
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
sessionFactory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sessionFactory.openSession();
}
public static void colose(SqlSession sqlSession){
if (sqlSession != null) {
sqlSession.close();
}
}
}
pojo类代码
package com.ywq.mybatis.pojo;
public class User {
private Integer id ;
private String name;
private String password;
private Integer age;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public User(Integer id, String name, String password, Integer age) {
super();
this.id = id;
this.name = name;
this.password = password;
this.age = age;
}
public User() {
super();
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", password=" + password + ", age=" + age + "]";
}
}
Mapper接口代码
package com.ywq.mybatis.mapper;
import java.util.List;
import javax.annotation.Resource;
import javax.annotation.Resources;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.ywq.mybatis.pojo.User;
/*
*mybatis的映射接口
* */
public interface UserMapper {
// DML :增删改
@Insert("insert into user (name,password,age)values(#{name},#{password},#{age})")
int insert(User user);
@Delete("delete from user where id = #{id}")
int deleteByPrimaryKey(Integer id);
@Update("update user set name = #{name},password=#{password},age=#{age} where id =#{id}")
int updateByPrimaryKey(User user);
//DQL : 查询
@Select("select * from user where id = #{id}")
User selectByPrimaryKey(Integer id);
@Results({
@Result(id = true,column = "u_id",property = "id"),
@Result(column = "u_name",property = "name"),
@Result(column = "u_password",property = "password"),
@Result(column = "u_age",property = "age")
})
@Select("select id u_id,name u_name,password u_password, age u_age from user")
List<User> selectLits();
}
测试代码
package com.ywq.mybatis.test;
import static org.junit.Assert.*;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.ywq.mybatis.mapper.UserMapper;
import com.ywq.mybatis.pojo.User;
import com.ywq.mybatis.util.MyBatisUtils;
public class MyBatisTest {
@Test
public void testInert() throws Exception {
//1.创建SqlSession对象
SqlSession session = MyBatisUtils.openSession();
//2.创建UserMapper映射接口的动态代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
System.out.println(userMapper.getClass().getName());
//3.执行insert方法
User user = new User(null, "jocn", "2020", 33);
int row = userMapper.insert(user);
System.out.println("受影响的行数:"+row);
//4.MyBatis默认需要手动提交事务
session.commit();
//5.关闭session(释放资源)
session.close();
}
@Test
public void testUpdate() throws Exception {
//1.创建SqlSession对象
SqlSession session = MyBatisUtils.openSession();
//2.创建UserMapper映射接口的动态对立对象
UserMapper mapper = session.getMapper(UserMapper.class);
//3.执行修改方法
User user = new User(6, "都邦", "5161", 25);
int row = mapper.updateByPrimaryKey(user);
System.out.println("受影响行数"+row);
//4.MyBatis默认需要手动提交事务
session.commit();
//5.关闭session(释放资源)
session.close();
}
@Test
public void testDelete() throws Exception {
//1.创建SqlSession对象
SqlSession session = MyBatisUtils.openSession();
//2.创建UserMapper映射接口的动态对立对象
UserMapper mapper = session.getMapper(UserMapper.class);
//3.执行删除方法
int row = mapper.deleteByPrimaryKey(11);
System.out.println("受影响行数"+row);
//4.MyBatis默认需要手动提交事务
session.commit();
//5.关闭session(释放资源)
session.close();
}
@Test
public void testSelectOne() throws Exception {
//1.创建SqlSession对象
SqlSession session = MyBatisUtils.openSession();
//2.创建UserMapper映射接口的动态对立对象
UserMapper mapper = session.getMapper(UserMapper.class);
//3.执行单行查询方法
User user = mapper.selectByPrimaryKey(5);
System.out.println(user);
//4.MyBatis默认需要手动提交事务
session.commit();
//5.关闭session(释放资源)
session.close();
}
@Test
public void testSelectList() throws Exception {
//1.创建SqlSession对象
SqlSession session = MyBatisUtils.openSession();
//2.创建UserMapper映射接口的动态对立对象
UserMapper mapper = session.getMapper(UserMapper.class);
//3.执行查询方法
List<User> users = mapper.selectLits();
for (User user : users) {
System.out.println(user);
}
//4.MyBatis默认需要手动提交事务
session.commit();
//5.关闭session(释放资源)
session.close();
}
}
动态SQL的Mapper接口代码
package com.ywq.mybatis.mapper;
import java.util.List;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;
import com.ywq.mybatis.pojo.User;
/*
*mybatis的映射接口
* */
public interface UserMapper {
/*
* 查询动态SQL语句的注解
* @SelectProvider(type = ,method=)
* type=编写动态SQL语句的字节码(专门编写动态Sql语句的场所)
* method="编写动态SQL语句类的方法名称"
*
* */
//根据条件查询结果
@SelectProvider(type =UserSqlProvider.class ,method="selectByCondition")
List<User> selectByCondition(User user);
//根据提交查询总数
@SelectProvider(type =UserSqlProvider.class ,method="selectTotalByCondition")
Long selectTotalByCondition(User user);
//修改用户
@UpdateProvider(type =UserSqlProvider.class ,method="updateUserByNotNull")
int updateUserByNotNull(User user);
//批量删除用户
@DeleteProvider(type = UserSqlProvider.class,method = "deleteByIds")
int deleteByIds(@Param("ids")Integer[] ids);
//批量插入
@InsertProvider(type = UserSqlProvider.class,method = "insertByBatch")
int insertByBatch(@Param("users")List<User> users);
}
动态SQL实现类代码
package com.ywq.mybatis.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;
import com.ywq.mybatis.pojo.User;
/*
*专门编写动态sql语句的类
*
* 提供一个返回Strin字符串的方法
*
* 方法内部就是编写动态SQL语句
* */
public class UserSqlProvider {
/*
* 此方法就是编写条件查询动态SQL语句方法
*
* */
public String selectByCondition(User user) {
/*
* SQL:MyBatis专门封装用于拼接SQL语句的类
* */
SQL sql = new SQL();
sql.SELECT("*");//select *
sql.FROM("user");//from user
if (user.getName() != null) {
sql.WHERE("name like concat('%',#{name},'%')");
}
//默认多条件之间是AND关系
if (user.getAge() !=null) {
//让条件变为OR关系
sql.OR();
sql.WHERE("age = #{age}");
}
return sql.toString();
}
public String selectTotalByCondition(User user) {
/*
* SQL:MyBatis专门封装用于拼接SQL语句的类
* */
SQL sql = new SQL();
sql.SELECT("count(*)");//select *
sql.FROM("user");//from user
if (user.getName() != null) {
sql.WHERE("name like concat('%',#{name},'%')");
}
//默认多条件之间是AND关系
if (user.getAge() !=null) {
//让条件变为OR关系
sql.OR();
sql.WHERE("age = #{age}");
}
return sql.toString();
}
public String updateUserByNotNull(User user) {
// 创建SQL对象
SQL sql = new SQL();
sql.UPDATE("user"); //update user
if (user.getName() != null) {
sql.SET("name = #{name}");//set name = #{name}
}
if (user.getPassword() != null) {
sql.SET("password = #{password}");// set password = #{password}
}
if (user.getAge() != null) {
sql.SET("age = #{age}"); // set age = #{age}
}
sql.WHERE("id= #{id}");// where id =#{id}
return sql.toString();
}
public String deleteByIds(@Param("ids")Integer[] ids) {
/*
* SQL对象并不能满足所有的业务需求,只能拼接完成常见比较简单的动态SQL
* 如果是比较复杂业务需求,还需要开发者自己拼接SQL
* 如:批量删除,批量插入
*
* */
//delete from user where id in(?,?,?)
SQL sql = new SQL();
sql.DELETE_FROM("user"); // delete from user
//拼接条件: (#{ids[0]},#{ids[1]},#{ids[2]})
StringBuilder sb = new StringBuilder();
sb.append("(");
for (int i = 0; i < ids.length; i++) {
sb.append("#{ids["+i+"]},");
}
//删除最后一个多余的逗号
sb.deleteCharAt(sb.length()-1);
sb.append(")");
System.out.println(sb.toString());
//条件
sql.WHERE("id in"+sb.toString());
System.out.println("SQL:"+sql.toString());
return sql.toString();
}
public String insertByBatch(@Param("users")List<User> users) {
/*
* SQL语句:
* insert into user(name,password,age)values
* ('张三','sda',25)
* ('李四','dfga',55)
* ('王五','hda',15)
*
* */
StringBuffer sb = new StringBuffer();
sb.append("insert into user(name,password,age)values");
for (int i = 0; i < users.size(); i++) {
sb.append("(#{users["+i+"].name},#{users["+i+"].password},#{users["+i+"].age}),");
}
//删除最后一个多余的逗号
sb.deleteCharAt(sb.length()-1);
return sb.toString();
}
}
测试代码
package com.ywq.mybatis.test;
import static org.junit.Assert.*;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.ywq.mybatis.mapper.UserMapper;
import com.ywq.mybatis.pojo.User;
import com.ywq.mybatis.util.MyBatisUtils;
public class MyBatisTest {
@Test
public void testSelectOne() throws Exception {
//1.创建SqlSession对象
SqlSession session = MyBatisUtils.openSession();
//2.创建UserMapper映射接口的动态代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
//3.执行条件查询方法
User user = new User();
user.setName("李");
user.setAge(20);
List<User> users = userMapper.selectByCondition(user);
for (User user2 : users) {
System.out.println(user2);
}
//4.MyBatis默认需要手动提交事务
session.commit();
//5.关闭session(释放资源)
session.close();
}
@Test
public void testSelect() throws Exception {
//1.创建SqlSession对象
SqlSession session = MyBatisUtils.openSession();
//2.创建UserMapper映射接口的动态代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
//3.执行条件查询总数方法
User user = new User();
//user.setName("李");
user.setAge(25);
Long users = userMapper.selectTotalByCondition(user);
System.out.println(users);
//4.MyBatis默认需要手动提交事务
session.commit();
//5.关闭session(释放资源)
session.close();
}
@Test
public void testUpdate() throws Exception {
//1.创建SqlSession对象
SqlSession session = MyBatisUtils.openSession();
//2.创建UserMapper映射接口的动态代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
//3.执行修改方法
User user = new User();
user.setId(7);
user.setName("dd");
int row = userMapper.updateUserByNotNull(user);
System.out.println(row);
//4.MyBatis默认需要手动提交事务
session.commit();
//5.关闭session(释放资源)
session.close();
}
@Test
public void testDelete() throws Exception {
//1.创建SqlSession对象
SqlSession session = MyBatisUtils.openSession();
//2.创建UserMapper映射接口的动态代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
//3.执行批量删除方法
Integer [] ids = {6,7};
userMapper.deleteByIds(ids);
//4.MyBatis默认需要手动提交事务
session.commit();
//5.关闭session(释放资源)
session.close();
}
@Test
public void testInsert() throws Exception {
//1.创建SqlSession对象
SqlSession session = MyBatisUtils.openSession();
//2.创建UserMapper映射接口的动态代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
//3.执行批量插入方法
List<User> users = new ArrayList<>();
for (int i = 1; i <=5; i++) {
User user = new User(null, "猴子"+i, "houzi"+i, i);
users.add(user);
}
int row = userMapper.insertByBatch(users);
System.out.println("受影响行数"+row);
//4.MyBatis默认需要手动提交事务
session.commit();
//5.关闭session(释放资源)
session.close();
}
}