1.创建实体类 User 和接口UserDao
2.创建主配置文件 导入配置
<?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="jdbcConfig.properties"></properties>
<!--配置别名 指向实体类-->
<typeAliases>
<package name="com.wyc.domain.User"/>
</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>
<package name="com.wyc.Dao"/>
</mappers>
</configuration>
3.创建外部配置文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=123
4.测试类步骤 采用junit 单元测试
before 加载资源
1. 输入流 获取主配置 2. 用主配置文件构建 工厂 3.用工厂构建sqlSession对象 4.用 sqlSession 获取代理对象
After 提交事务 释放资源 1.SqlSession.commit() 2. SqlSession.close() 3. in.close()
@Before
public void init() throws Exception {
in = Resources.getResourceAsStream("SqlMapConfig.xml");
factory = new SqlSessionFactoryBuilder().build(in);
sqlSession = factory.openSession(); 可以设置为true 自动提交
userDao = sqlSession.getMapper(UserDao.class);
}
@After
public void destroy() throws Exception {
sqlSession.commit();
sqlSession.close();
in.close();
}
2.用 代理对象 执行加强的方法
UserDao 写sql语句基本查询
@Select("select * from user")
List<User> findAll();
@Select("insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address})")
void save(User user);
@Update("update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}")
void updateUser(User user);
@Delete("delete from user where id=#{id}")
void deleteUser(int id);
@Select("select * from user where id=#{id}")
User findById(int id);
// @Select("select * from user where username like #{username}")
@Select("select * from user where username like '%${value}%' ") //value 是固定的
List<User> findByName( String username);
@Select("select count(id) from user")
int findCount();
其中模糊查询@Select("select * from user where username like #{username}") 测试类 还要写user.setUsername("%wyc%")
@Select("select * from user where username like '%${value}%' ") value 是固定的 就可以不用写 %%了
多表操作
注解解决 属性与数据库不对应
当实体类中 是 Integer userId; String userName; Date userBirthday; String userSex; String userAddress;
而数据库中是 id username birthday sex address 字段时 名字与实体类不匹配,在封装的时候 只有username能封装进去
@Select("select * from user")
@Results(id = "userMap",value = {
@Result(id=true,column = "id",property = "userId"),
@Result(column = "username",property = "userName"),
@Result(column = "sex",property = "userSex"),
@Result(column = "address",property = "userAddress"),
@Result(column = "birthday",property = "userBirthday")
})
List<User> findAll();
id=true 表示 该属性在数据库中是 主键 其中 id = "userMap" 表示该 组的唯一标识,其他查询语句也可以应用这个命名
@Select("select * from user where id=#{id}")
@ResultMap("userMap") 标准写法 value={"userMap"}
User findById(int id);
一对一查询
public class Account implements Serializable {
private Integer id ;
private Integer uid ;
private Integer money;
定义账户与用户一对一的映射关系
private User user;}
查询所有账户,并且包括用户的信息
@Select("select * from account a left outer join user u on u.id=a.uid")
@Results(id = "accountMap",value = {
@Result(id = true,column = "id",property = "id"),
@Result(column = "uid",property = "uid"),
@Result(column = "money",property = "money"),
@Result(column = "uid",property = "user",one = @One(select="com.wyc.Dao.UserDao.findById",
fetchType= FetchType.EAGER)),
})
List<Account> findAll();
@Result( column = "uid",property = "user", one = @One(select="com.wyc.Dao.UserDao.findById",
fetchType= FetchType.EAGER)), select 指定 UserDao 中查询用户方法 fetchType指定懒加载
根据id 查询用户
@Select("select * from user where id=#{id}")
@ResultMap("userMap")
User findById(int id);
多对多查询
public class User implements Serializable {
private Integer userId;
private String userName;
private Date userBirthday;
private String userSex;
private String userAddress;
private List<Account> accounts; 一对多 关系映射
@Select("select * from user") 查询所有用户,并且包含所有该用户的账户信息
@Results(id = "userMap",value = {
@Result(id=true,column = "id",property = "userId"),
@Result(column = "username",property = "userName"),
@Result(column = "sex",property = "userSex"),
@Result(column = "address",property = "userAddress"),
@Result(column = "birthday",property = "userBirthday"),
@Result(column = "id",property = "accounts",many = @Many(
select = "com.wyc.Dao.AccountDao.findById",
fetchType = FetchType.EAGER))
})
List<User> findAll();
select 指定 账户接口 实现 使用 用户id 查询出所有 账户的方法
根据用户id 查询账户
@Select("select * from account where uid = #{uid}")
List<Account> findById(int id);
总结 通常情况 一对多使用延迟加载 一对一使用立即加载
注解的二级缓存
一级缓存默认开启 二级缓存在 主配置中已经默认开启 ,还需要 在USerDao接口中 注解写上
@CacheNamespace(blocking = true)
public interface UserDao {}
sqlSession = factory.openSession(); 关闭 再开启一级缓存消失 但 也会只有一次查询 使用的是二级缓存开启了