- 本篇为初学Mybatis框架时的入门笔记，整理发出
- Spring集成Mybatis https://www.cnblogs.com/yueshutong/p/9381590.html
- SpringBoot集成Mybatis https://www.cnblogs.com/yueshutong/p/9381590.html
简介
MyBatis 是支持普通 SQL 查询，存储过程和高级映射的优秀持久层框架，其几乎消除了所有的 JDBC 代码和参数的手工设置以及结果集的检索。MyBatis 使用简单的 XML 或注解用于配置和原始映射，将接口和 Java 的 POJOs（Plain Old Java Objects，普通的 Java对象）映射成数据库中的记录。MyBatis 应用程序大都使用 SqlSessionFactory 实例，SqlSessionFactory 实例可以通过 SqlSessionFactoryBuilder 获得，而 SqlSessionFactoryBuilder 则可以从一个 XML 配置文件或者一个预定义的配置类的实例获得。
依赖
mybatis-3.2.2.jar 核心jar
mysql-connector-java-5.1.10-bin.jar 数据库访问
1.Configuration配置
在resources下，新建Configuration.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>
<!-- 设置别名，用于在User.xml中 ，这里不设置
<typeAliases>
<typeAlias alias="User" type="com.zyzpp.model.User"/>
</typeAliases>
-->
<typeAliases>
<typeAlias alias="User" type="com.zyzpp.model.User"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<!-- 配置数据库连接信息 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/work?useUnicode=true&characterEncoding=UTF-8" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<!--映射实体的mapper配置-->
<mappers>
<mapper resource="com/zyzpp/inter/User.xml"/>
</mappers>
</configuration>2.Entity实体类
示例：
package com.zyzpp.model;
public class User {
private int id;
private String userName;
private int userAge;
private String userAddress;
private List<Card> cards;
....
}
3.Dao层接口
示例：
package com.zyzpp.inter;
public interface IUserOperation {
public User selectUserByID(int id);
public List<User> selectUsers(String userName);
public void addUser(User user);
public void updateUser(User user);
public void deleteUser(int id);
public List<Article> getUserArticles(@Param("userid") int userid); //多个参数要加注释区分
public User getUserCards(int id);
public int getCount(User user);
public List<User> getLimit(Map map);
}4.Mapper配置
说白了，就是实现上步接口方法
<?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">
<!-- 声明接口地址 -->
<mapper namespace="com.zyzpp.inter.IUserOperation">
<!-- 如果在配置xml文件中设置了别名，在这里可以只写别名
<select id="seleteUserByID" parameterType="int"
select中包含的是SQL语句，以及语句的参数。
id 与 接口的方法名一致 parameterType是方法参数类型 -->
<!-- 根据id查询用户 -->
<select id="selectUserByID" parameterType="int" resultType="cn.zyzpp.model.User">
select * from user where id=#{id}
</select>
<resultMap type="User" id="resultListUser">
<id column="id" property="id" jdbcType="INTEGER" />
<result column="userName" property="userName" jdbcType="VARCHAR" />
<result column="userAge" property="userAge" jdbcType="INTEGER" />
<result column="userAddress" property="userAddress" jdbcType="VARCHAR" />
</resultMap>
<!-- 根据用户名模糊查找 -->
<select id="selectUsers" parameterType="String" resultMap="resultListUser">
select * from user where userName like #{userName}
</select>
<!-- 增加一个用户 -->
<insert id="addUser" parameterType="User" useGeneratedKeys="true"
keyProperty="id">
insert into user(userName,userAge,userAddress)
values(#{userName},#{userAge},#{userAddress})
</insert>
<!-- 根据ID更新用户信息 -->
<update id="updateUser" parameterType="User">
update user set userName =
#{userName},userAge=#{userAge},userAddress=#{userAddress} where
id=#{id}
</update>
<!-- 根据id删除用户 -->
<delete id="deleteUser" parameterType="int">
delete from user where id=#{id}
</delete>
<!-- 其中需要说明的是，如果两个表中存在重名字段，如上文所述uesr表中有id字段，article表中同样有id字段，
在使用Mybatis进行联合查询的过程中，会遇到只返回一条数据的情况。然而在数据库中直接运行sql语句是正常的。
在出现重名字段时，配置Mybatis文件时需要把字段重新命名，否则Mybatis会混乱.
例如下面的 ID一号二号不可以重复。在这里就把article表中的id字段命名为了aid。只可改一号！
-->
<!-- User 联合文章进行查询 方法之一的配置 (多对一的方式) column="数据库字段名" property="实体类属性" jdbcType="数据库字段类型" -->
<resultMap id="resultUserArticleList" type="com.zyzpp.model.Article">
<id property="id" column="aid" jdbcType="INTEGER" /><!-- id一号 -->
<result property="title" column="title" />
<result property="content" column="content" />
<!-- associayion=标签
也可以<association property="user" javaType="User" resultMap="resultListUser"/>
-->
<association property="user" javaType="User" column="userid">
<id property="id" column="id" /><!-- id 二号-->
<result property="userName" column="userName" />
<result property="userAddress" column="userAddress" />
</association>
</resultMap>
<!-- 多对一 -->
<select id="getUserArticles" parameterType="int"
resultMap="resultUserArticleList">
select user.id,user.userName,user.userAddress,article.id aid,article.title,article.content from user,article
where
user.id=article.userid and user.id=#{id}
</select>
<!-- 一对多 collection 查询用户手机卡-->
<resultMap id="resultUserCards" type="com.zyzpp.model.User">
<id property="id" column="u_id" />
<result property="userName" column="userName" />
<result property="userAge" column="userAge" />
<result property="userAddress" column="userAddress" />
<collection property="cards" ofType="com.zyzpp.model.Card">
<id property="id" column="id" />
<result property="cardNo" column="card_no" />
<result property="remark" column="remark" />
</collection>
</resultMap>
<!-- 一对多 -->
<select id="getUserCards" parameterType="int" resultMap="resultUserCards">
SELECT u.id u_id,u.userName,u.userAge,u.userAddress,c.id,c.card_no,c.remark
FROM user u,card c
WHERE u.id=c.user_id AND u.id=#{id}
</select>
<!-- 获取总条数 -->
<select id="getCount" parameterType="com.zyzpp.model.User" resultType="int">
select count(*) from user
<where>
<if test="id!=0"><!-- " " -->
and id=#{id}
</if>
<if test="userName!=null and !"".equals(userName.trim())">
and userName like % #{userName} %
</if>
</where>
</select>
<!-- 获取特定记录-->
<select id="getLimit" parameterType="java.util.Map" resultMap="resultListUser">
SELECT * FROM user
<where>
<if test="user.id!=0"><!-- " " -->
and id=#{user.id}
</if>
<if test="user.userName!=null and !"".equals(user.userName.trim())">
and userName like % #{user.userName} %
</if>
</where>
order by id LIMIT #{page.dbIndex},#{page.dbNumber};
</select>
</mapper>
5.开始使用
import java.io.Reader;
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 Test {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
private static String resource = "Configuration.xml"; //mybatis的配置文件
static{
try{
/*使用MyBatis提供的Resources类加载mybatis的配置文件（它也加载关联的映射文件）*/
reader=Resources.getResourceAsReader(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);//构建sqlSession的工厂
}catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
Test test =new Test();
User user=new User();
test.getLimit();
// test.getCount(user);
// test.getUserCards(1);
// test.getUserArticles(1);
// test.getUserList("%");
// test.addUser();
// test.updateUser(1, "这是更新后的地址");
// test.deleteUser(3);
}
/*
* 以ID查找单个数据
*/
public void getUserByID(int id){
SqlSession session=sqlSessionFactory.openSession();
try{
IUserOperation userOperation=session.getMapper(IUserOperation.class);
User user=userOperation.selectUserByID(id);
System.out.println("查询ID："+id+"结果如下\n"+"名字："+user.getUserName()+" 年龄："+user.getUserAge()+" 地址："+user.getUserAddress());
}finally {
session.close();
}
}
/*
* 以name查找符合的List列表
*/
public void getUserList(String userName){
SqlSession session=sqlSessionFactory.openSession();
try{
IUserOperation userOperation=session.getMapper(IUserOperation.class);
List<User> users = userOperation.selectUsers(userName);
System.out.println("查询结果如下：");
for(User user: users){
System.out.println(user.toString());
}
}finally {
session.close();
}
}
/*
* 增加一个用户
*/
public void addUser(){
User user = new User();
user.setUserAddress("人民广场");
user.setUserAge(18);
user.setUserName("飞鸟");
SqlSession session = sqlSessionFactory.openSession();
try{
IUserOperation userOperation =session .getMapper(IUserOperation.class);
userOperation.addUser(user);
session.commit();
System.out.println("当前增加的用户ID："+user.getId());
}finally {
session.close();
}
}
/*
* 更新数据
*/
public void updateUser(int id,String content){
//先得到用户，然后修改提交
SqlSession session = sqlSessionFactory.openSession();
try{
IUserOperation userOperation = session.getMapper(IUserOperation.class);
User user = userOperation.selectUserByID(id);
user.setUserAddress(content);
userOperation.updateUser(user);
session.commit();
System.out.println("更新ID："+id+"成功！");
}finally {
session.close();
}
}
/*
* 删除数据
*/
public void deleteUser(int id){
SqlSession session = sqlSessionFactory.openSession();
try{
IUserOperation userOperation = session.getMapper(IUserOperation.class);
userOperation.deleteUser(id);
session.commit();
System.out.println("删除ID："+id+"成功！");
}finally {
session.close();
}
}
/*
* 多对一
*/
public void getUserArticles(int userid){
SqlSession session = sqlSessionFactory.openSession();
try {
IUserOperation userOperation=session.getMapper(IUserOperation.class);
List<Article> articles = userOperation.getUserArticles(userid);
for(Article article:articles){
System.out.println("标题："+article.getTitle()+"：内容："+article.getContent()+
"：作者是："+article.getUser().getUserName()+"：地址："+
article.getUser().getUserAddress());
}
} finally {
session.close();
}
}
/*
* 一对多
*/
private void getUserCards(int userid) {
SqlSession session = sqlSessionFactory.openSession();
try {
IUserOperation userOperation = session.getMapper(IUserOperation.class);
User user = userOperation.getUserCards(userid);
System.out.println(user.toString());
for (Card card : user.getCards()) {
System.out.println(card.toString());
}
} finally {
session.close();
}
}
/*
* 按条件查询总条数
*/
private int getCount(User user){
SqlSession session = sqlSessionFactory.openSession();
try {
IUserOperation userOperation = session.getMapper(IUserOperation.class);
int i = userOperation.getCount(user);
System.out.println("总条数："+i);
return i;
} finally {
session.close();
}
}
/**
* 按条件查询List记录
*/
private void getLimit(){
SqlSession session = sqlSessionFactory.openSession();
try {
IUserOperation userOperation = session.getMapper(IUserOperation.class);
Map<String, Object> map=new HashMap<String, Object>();
Page page=new Page();
page.setDbIndex(0);
page.setDbNumber(1);
User user=new User();
user.setId(1);
map.put("page", page);
map.put("user", user);
List<User> users = userOperation.getLimit(map);
System.out.println("查询结果如下：");
for(User use: users){
System.out.println(use.toString());
}
} finally {
session.close();
}
}
}