一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第24天，点击查看活动详情。
1.xml方式配置mybatis
目录结构：
1.1 porm.xml引入依赖
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
</dependencies>
1.2 SqlMapConfig.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">
<!--mybatis的主配置文件-->
<configuration>
<!--环境配置-->
<environments default="mysql">
<!--配置MySql环境-->
<environment id="mysql">
<!--配置事务类型-->
<transactionManager type="JDBC">
</transactionManager>
<!--配置数据源（连接池）-->
<dataSource type="POOLED">
<!--配置连接数据库的4个基本信息-->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/ssm?serverTimezone=GMT%2B8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--指定映射配置文件的位置，映射配置文件指的是每个dao独立的配置文件-->
<mappers>
<mapper resource="cn/zh/dao/IUserDao.xml"/>
</mappers>
</configuration>
1.3 IUserDao.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">
<!-- namespace:用来绑定dao接口的 -->
<mapper namespace="cn.zh.dao.IUSerDao">
<!--配置查询所有-->
<select id="findAll" resultType="cn.zh.domain.User">
select * from user;
</select>
</mapper>
1.4测试
public class MybatisTest {
/**
* 入门案例
* @param args
*/
public static void main(String[] args) throws Exception {
//1.读取配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
//2.创建SqlSessionFactory工厂
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
//3.使用工厂生产SqlSession对象
SqlSession session = factory.openSession();
//4.使用SqlSession创建Dao接口的代理对象
IUSerDao userDao = session.getMapper(IUSerDao.class);
//5.使用代理对象执行方法
List<User> users = userDao.findAll();
for (User user : users) {
System.out.println(user);
}
//6.释放资源
session.close();
in.close();
}
}
2.annotation配置mybatis
只讨论与xml不同的地方
2.1 SqlMapConfig.xml
<!--指定映射配置文件的位置，映射配置文件指的是每个dao独立的配置文件
如果是用注释来配置的话，此处应该使用class属性指定被注解的dao全限定类名
-->
<mappers>
<mapper class="cn.zh.dao.IUSerDao"/>
</mappers>
2.2 IUserDao.java
public interface IUSerDao {
/**
* 查询所有的操作
* @return
*/
@Select("select * from user")
List<User> findAll();
}
3.xml方式进行CRUD操作
3.1 jdbcConfig.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm?serverTimezone=GMT%2B8
jdbc.username=root
jdbc.password=root
3.2 sqlMapConfig.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-->
<properties resource="jdbcConfig.properties">
<!-- <property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/ssm?serverTimezone=GMT%2B8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>-->
</properties>
<!--使用typeAliases配置别名，它只能配置domain中类的别名-->
<typeAliases>
<!--typeAlias用于配置别名，type属性指定的是实体类全限定类名。alias属性指定别名，当指定了别名就不区分大小写了-->
<!--<typeAlias type="cn.zh.domain.User" alias="user"></typeAlias>-->
<!--用于指定要配置别名的包，当指定之后，该包下的实体类都会注册别名，并且类名就是别名，不再区分大小写-->
<package name="cn.zh.domain"/>
</typeAliases>
<!--配置环境-->
<environments default="mysql">
<!--配置MySQL环境-->
<environment id="mysql">
<!--配置事务-->
<transactionManager type="JDBC"></transactionManager>
<!--配置连接池-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!-- <!–配置环境–>
<environments default="mysql">
<!–配置MySQL环境–>
<environment id="mysql">
<!–配置事务–>
<transactionManager type="JDBC"></transactionManager>
<!–配置连接池–>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/ssm?serverTimezone=GMT%2B8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>-->
<!--配置映射文件的位置-->
<mappers>
<!--<mapper resource="cn/zh/dao/IUserDao.xml"></mapper>-->
<!--package标签用于指定dao接口所在的包，当制定了之后就不需要再写mapper以及resource或者class-->
<package name="cn.zh.dao"/>
</mappers>
</configuration>
3.3 User,QueryVo,DoubleUser
user
public class User implements Serializable {
private Integer id;
private String username;
private String address;
private String sex;
private Date birthday;
}
DoubleUser
public class DoubleUser implements Serializable {
private Integer userId;
private String userName;
private String userAddress;
private String userSex;
private Date userBirthday;
}
QueryVo
public class QueryVo {
private User user;
private List<Integer> ids;
}
3.4 IUserDao.java
public interface IUserDao {
/**
* 查询所有的用户
* @return
*/
List<User> findAll();
/**
* 保存用户
* @param user
*/
void saveUser(User user);
/**
* 更新用户
* @param user
*/
void updateUser(User user);
/**
* 根据用户的id删除用户
* @param userId
*/
void deleteUser(Integer userId);
/**
* 根据id查询用户信息
* @param userId
* @return
*/
User findById(Integer userId);
/**
* 根据名称模糊查询
* @param username
* @return
*/
List<User> findByName(String username);
/**
* 查询总用户的数量
* @return
*/
int findTotal();
/**
* 根据QueryVo查询
* @param vo
* @return
*/
List<User> findUserByVo(QueryVo vo);
/**
* 数据库和Bean不匹配时查询所有
*/
List<DoubleUser> findAllUser();
/**
* 根据传入参数条件查询
* @param user 查询的条件
* @return
*/
List<User> findUserByCondition(User user);
/**
* 根据in查询
* @param vo
* @return
*/
List<User> findUserByInIds(QueryVo vo);
}
3.5 IUserDao.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">
<mapper namespace="cn.zh.dao.IUserDao">
<!--查询所有用户-->
<select id="findAll" resultType="cn.zh.domain.User">
select * from user;
</select>
<!--保存用户-->
<insert id="saveUser" parameterType="cn.zh.domain.User">
<!-- 获取插入操作后，获取插入数据的id
keyProperty属性类中的名称
keyColumn对应表中的属性
order取值为AFTER代表插入后的行为
resultType表示返回值的类型
-->
<selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER">
select last_insert_id();
</selectKey>
insert into user(username,address,sex,birthday) values(#{username},#{address},#{sex},#{birthday});
</insert>
<!--更新用户-->
<update id="updateUser" parameterType="cn.zh.domain.User">
update user set username=#{username},address=#{address},sex=#{sex},birthday=#{birthday} where id=#{id};
</update>
<!--删除用户-->
<delete id="deleteUser" parameterType="java.lang.Integer">
delete from user where id = #{id};
</delete>
<!--根据id查询用户-->
<select id="findById" parameterType="INT" resultType="cn.zh.domain.User">
select * from user where id =#{id};
</select>
<!--根据名称模糊查询-->
<select id="findByName" parameterType="String" resultType="cn.zh.domain.User">
select * from user where username like #{username};
<!-- select * from user where username like '%${value}%' （一般不使用这个）-->
</select>
<!--获取用户的总记录条数-->
<select id="findTotal" resultType="java.lang.Integer">
select count(id) from user;
</select>
<!--根据QueryVo的条件查询用户-->
<select id="findUserByVo" parameterType="cn.zh.domain.QueryVo" resultType="cn.zh.domain.User">
select * from user where username like #{user.username};
</select>
<!--数据库和Bean不匹配时查询所有-->
<select id="findAllUser" resultType="cn.zh.domain.DoubleUser">
select id as userId,username as userName,address as userAddress,sex as userSex,birthday as userBirthday from user;
</select>
<!--配置查询结果的列名和实体类的属性名的对应关系;
id是唯一的一个标志，可以随意写;
查询语句要使用resultMap
-->
<resultMap id="userMap" type="cn.zh.domain.DoubleUser">
<!--主键字段的对应-->
<id property="userId" column="id"></id>
<!--非主键字段的对应-->
<result property="userName" column="username"></result>
<result property="userAddress" column="address"></result>
<result property="userSex" column="sex"></result>
<result property="userBirthday" column="birthday"></result>
</resultMap>
<!--根据条件查询-->
<select id="findUserByCondition" parameterType="user" resultType="user">
select * from user
<where>
<if test="username!=null">
username=#{username};
</if>
<if test="sex!=null">
sex=#{sex};
</if>
</where>
</select>
<!--根据queryVo中的id集合实现查询用户列表-->
<select id="findUserByInIds" parameterType="QueryVo" resultType="user">
select * from user
<where>
<if test="ids!=null and ids.size()>0">
<foreach collection="ids" open="and id in(" close=")" item="uid" separator=",">
#{uid}
</foreach>
</if>
</where>
</select>
</mapper>
4.xml方式进行一对多查询
4.1 User,Account,AccountUser
User
public class User implements Serializable {
private Integer id;
private String username;
private String address;
private String sex;
private Date birthday;
}
Account
public class Account implements Serializable {
private Integer id;
private Integer uid;
private Double money;
private User user;
}
AccountUser
public class AccountUser extends Account {
private String username;
private String address;
}
4.2 IUserDao.java和IAccountDao.java
IUserDao.java
public interface IUserDao {
/**
* 查询所有的用户,同时获取用户的所有账户信息
* @return
*/
List<User> findAll();
/**
* 根据id查询用户信息
* @param userId
* @return
*/
User findById(Integer userId);
}
IAccountDao.java
public interface IAccountDao {
/**
* 查询所有的账户
* @return
*/
List<Account> findAll();
/**
* 查找所有账户，并且带有用户名称和地址信息
* @return
*/
List<AccountUser> findAllAccount();
}
4.3 IUserDao.xml和IAccountDao.xml
IUserDao.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">
<mapper namespace="cn.zh.dao.IUserDao">
<!--定义user的resultMap-->
<resultMap id="userAccountMap" type="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<result property="birthday" column="birthday"></result>
<!--配置user中的accounts集合的映射-->
<collection property="accounts" ofType="account">
<id property="id" column="aid"></id>
<result column="uid" property="uid"></result>
<result column="money" property="money"></result>
</collection>
</resultMap>
<!--查询所有用户-->
<select id="findAll" resultMap="userAccountMap">
select u.*,a.id as aid,a.uid,a.money from user u left outer join account a on u.id = a.uid
</select>
<!--根据id查询用户-->
<select id="findById" parameterType="INT" resultType="cn.zh.domain.User">
select * from user where id =#{id};
</select>
</mapper>
IAccountDao.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">
<mapper namespace="cn.zh.dao.IAccountDao">
<!--定义封装account和user的resultMap-->
<resultMap id="accountUserMap" type="account">
<id property="id" column="id"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
<!--一对一的关系映射：配置封装user的内容-->
<association property="user" column="uid" javaType="cn.zh.domain.User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="password" column="password"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
</association>
</resultMap>
<!--查询所有用户-->
<select id="findAll" resultMap="accountUserMap">
select a.*,u.username,u.address from user u,account a where u.id=a.uid
</select>
<!--查询所有同时包含有用户名和地址信息-->
<select id="findAllAccount" resultType="cn.zh.domain.AccountUser">
select a.*,u.username,u.address from user u,account a where u.id=a.uid
</select>
</mapper>
5.xml方式进行多对多查询
5.1 Role和User
Role
public class Role implements Serializable {
private Integer roleId;
private String roleName;
private String roleDesc;
private List<User> users;
}
User
public class User implements Serializable {
private Integer id;
private String username;
private String address;
private String sex;
private Date birthday;
}
5.2 IRoleDao.java和IUserDao.java
IRoleDao.java
public interface IRoleDao {
/**
* 查询所有角色
* @return
*/
List<Role> findAll();
}
IUserDao.java
public interface IUserDao {
/**
* 查询所有的用户,同时获取用户的所有账户信息
* @return
*/
List<User> findAll();
/**
* 根据id查询用户信息
* @param userId
* @return
*/
User findById(Integer userId);
}
5.3 IUserDao.xml和 IRoleDao.xml
IUserDao.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">
<mapper namespace="cn.zh.dao.IUserDao">
<!--定义user的resultMap-->
<resultMap id="userAccountMap" type="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<result property="birthday" column="birthday"></result>
</resultMap>
<!--查询所有用户-->
<select id="findAll" resultMap="userAccountMap">
select * from user
</select>
<!--根据id查询用户-->
<select id="findById" parameterType="INT" resultType="cn.zh.domain.User">
select * from user where id =#{id};
</select>
</mapper>
IRoleDao.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">
<mapper namespace="cn.zh.dao.IRoleDao">
<!--定义role表的roleMap-->
<resultMap id="roleMap" type="role">
<id property="roleId" column="rid"></id>
<result property="roleName" column="role_name"></result>
<result property="roleDesc" column="role_desc"></result>
<collection property="users" ofType="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<result property="birthday" column="birthday"></result>
</collection>
</resultMap>
<!--查询所有-->
<select id="findAll" resultMap="roleMap">
select u.*,r.id as rid,r.role_name,r.role_desc from role r
left outer join user_role ur on r.id = ur.rid
left outer join user u on u.id = ur.uid
</select>
</mapper>
6. xml方式进行懒加载
6.1 SqlMapConfig.xml中开启懒加载
<!--配置参数-->
<settings>
<!--开启支持延迟加载-->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
6.2 IAccountDao.xml和IUserDao.xml
IAccountDao.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">
<mapper namespace="cn.zh.dao.IAccountDao">
<!--定义封装account和user的resultMap-->
<resultMap id="accountUserMap" type="account">
<id property="id" column="id"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
<!--一对一的关系映射：配置封装user的内容
select属性指定的内容，查询用户的唯一标识
column属性指定的内容，用户根据id查询时，所需要的参数的值
-->
<association property="user" column="uid" javaType="user" select="cn.zh.dao.IUserDao.findById"></association>
</resultMap>
<!--查询所有用户-->
<select id="findAll" resultMap="accountUserMap">
select * from account
</select>
<!--根据用户id查询账户信息-->
<select id="findAccountByUid" resultType="account" parameterType="INT">
select * from account where uid = #{uid}
</select>
</mapper>
IUserDao.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">
<mapper namespace="cn.zh.dao.IUserDao">
<!--定义user的resultMap-->
<resultMap id="userAccountMap" type="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<result property="birthday" column="birthday"></result>
<!--配置user中的accounts集合的映射-->
<collection property="accounts" ofType="account" select="cn.zh.dao.IAccountDao.findAccountByUid" column="id"></collection>
</resultMap>
<!--查询所有用户-->
<select id="findAll" resultMap="userAccountMap">
select * from user
</select>
<!--根据id查询用户-->
<select id="findById" parameterType="INT" resultType="cn.zh.domain.User">
select * from user where id =#{id};
</select>
</mapper>
7.annotation方式进行CRUD操作
7.1 SqlMapConfig.xml
<!--指定带有注解的dao接口所在的位置-->
<mappers>
<package name="cn.zh.dao"/>
</mappers>
7.2 IUserDao.xml
public interface IUserDao {
/**
* 查询所有用户
* @return
*/
@Select("select * from user")
List<User> findAll();
/**
* 保存用户
* @param user
*/
@Insert("insert into user(username,address,sex,birthday) values(#{username},#{address},#{sex},#{birthday})")
void saveUser(User user);
/**
* 更新用户
* @param user
*/
@Update("update user set username=#{username},sex=#{sex},address=#{address},birthday=#{birthday} where id=#{id}")
void updateUser(User user);
/**
* 删除用户
* @param userId
*/
@Delete("delete from user where id=#{id}")
void deleteUser(Integer userId);
/**
* 查找一个用户
* @param userId
* @return
*/
@Select("select * from user where id=#{id}")
User findById(Integer userId);
/**
* 根据用户名模糊查询
* @param username
* @return
*/
@Select("select * from user where username like #{username}")
List<User> findUserByName(String username);
/**
* 查找总数
* @return
*/
@Select("select count(*) from user")
int findTotalUser();
}
8. annotation方式进行一对多和多对多查询
8.1 IUserDao.java
public interface IUserDao {
/**
* 查询所有用户
* @return
*/
@Select("select * from user")
@Results(id = "userMap",value = {
@Result(id=true,column = "id",property = "userId"),
@Result(column = "username",property = "userName"),
@Result(column = "address",property = "userAddress"),
@Result(column = "sex",property = "userSex"),
@Result(column = "birthday",property = "userBirthday"),
@Result(property = "accounts",column = "id",
many = @Many(select = "cn.zh.dao.IAccountDao.findAccountByUid",fetchType = FetchType.LAZY))
})
List<DoubleUser> findAll();
/**
* 查找一个用户
* @param userId
* @return
*/
@Select("select * from user where id=#{id}")
@ResultMap("userMap")
DoubleUser findById(Integer userId);
/**
* 根据用户名模糊查询
* @param username
* @return
*/
@Select("select * from user where username like #{username}")
@ResultMap("userMap")
List<DoubleUser> findUserByName(String username);
}
8.2 IAccountDao.java
public interface IAccountDao {
/**
* 查询所有账户并且获取每个账户所属的用户信息
* @return
*/
@Select("select * from account")
@Results(id = "accountMap",value = {
@Result(id = true,column = "id",property = "id"),
@Result(column = "uid",property = "uid"),
@Result(column = "money",property = "money"),
@Result(property = "doubleUser",column = "uid",
one = @One(select = "cn.zh.dao.IUserDao.findById",fetchType = FetchType.EAGER))
})
List<Account> findAll();
/**
* 根据用户id查询账户
* @param userId
* @return
*/
@Select("select * from account where uid = #{userId}")
List<Account> findAccountByUid(Integer userId);
}
