准备工作
数据库表:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`address` varchar(256) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
CREATE TABLE `account` (
`ID` int(11) NOT NULL COMMENT '编号',
`UID` int(11) DEFAULT NULL COMMENT '用户编号',
`MONEY` double DEFAULT NULL COMMENT '金额',
PRIMARY KEY (`ID`),
KEY `FK_Reference_8` (`UID`),
CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
创建maven工程:
pom.xml添加坐标
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.45</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
<scope>test</scope>
</dependency>
</dependencies>
SqlMapConfig.xml
<!-- mybatis的主配置文件 -->
<configuration>
<properties resource="jdbc.properties"></properties>
<typeAliases>
<package name="com.zy.domain"/>
</typeAliases>
<environments default="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>
<mappers>
<package name="com.zy.dao"/>
</mappers>
</configuration>
jdbc.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=123456
User实体类:
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
}
@Results,@select,@Insert,@Update,@Update
UserMapper接口方法上添加注解
public interface UserMapper {
/**
* @Description 查询所有
* @Param:
* @return:
*/
@Results(id = "userMap", value = {
@Result(id = true,column = "id",property = "id"),
@Result(column = "id",property = "id"),
@Result(column = "username",property = "username"),
@Result(column = "birthday",property = "birthday"),
@Result(column = "sex",property = "sex"),
@Result(column = "address",property = "address")})
@Select(value = "select * from user")
List<User> findAll();
/***
* @Description 根据id查询用户
* @Param: [userId]
* @return: com.zy.domain.User
*/
@Select(value = "select * from user where id = #{id}")
@ResultMap(value = {"userMap"})
User findByUserId(Integer userId);
/***
* @Description 查询数据总数
* @Param: []
* @return: java.lang.Integer
*/
@Select(value = "select count(*) from user")
Integer findTotal();
/***
* @Description 模糊查询
* @Param: [username]
* @return: java.util.List<com.zy.domain.User>
*/
@Select(value = "select * from user where username like #{username}")
@ResultMap(value = {"userMap"})
List<User> findUserByLike(String username);
/***
* @Description 新增用户
* @Param: [user]
* @return: void
*/
@Insert(value = "insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address})")
void insertUser(User user);
/***
* @Description 更新用户
* @Param: []
* @return: void
*/
@Update(value = "update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}")
void updateUser(User user);
/***
* @Description 删除用户
* @Param: [uid]
* @return: void
* @Date: 2021/3/8
*/
@Delete(value = "delete from user where id=#{id}")
void deleteUserById(Integer uid);
}
测试方法:
public class UserTest {
private InputStream in;
private SqlSessionFactoryBuilder builder;
private SqlSessionFactory factory;
private SqlSession sqlSession;
private UserMapper userMapper;
@Before//在test方法执行之前执行
public void befor() throws Exception {
//读取SqlMapConfig.xml配置文件
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//创建SqlSessionFactory工厂
builder = new SqlSessionFactoryBuilder();
factory = builder.build(in);
//使用工厂生产SqlSession
sqlSession= factory.openSession();
//使用SqlSession创建dao接口代理对象
userMapper = sqlSession.getMapper(UserMapper.class);
}
@After//在test方法执行之后执行
public void after() throws Exception {
sqlSession.commit();
sqlSession.close();
in.close();
}
/***
* @Description 查询所有
* @Param: []
* @return: void
*/
@Test
public void testFindAll(){
List<User> users = userMapper.findAll();
for (User user : users){
System.out.println(user);
}
}
/***
* @Description 根据id查询用户
* @Param: []
* @return: void
*/
@Test
public void testFindByUserId(){
User user = userMapper.findByUserId(18);
System.out.println(user);
}
/***
* @Description 查询总条数
* @Param: []
* @return: void
*/
@Test
public void testFindTotal(){
Integer total = userMapper.findTotal();
System.out.println(total);
}
/***
* @Description 根据姓名模糊查询
* @Param: []
* @return: void
*/
@Test
public void testFindUserByLike(){
List<User> users = userMapper.findUserByLike("%小%");
for (User user : users){
System.out.println(user);
}
}
/***
* @Description 新增用户
* @Param: []
* @return: void
*/
@Test
public void testInsertUser(){
User user = new User();
user.setUsername("武则天");
user.setBirthday(new Date());
user.setSex("女");
user.setAddress("唐朝");
userMapper.insertUser(user);
}
/***
* @Description 更新用户
* @Param: []
* @return: void
*/
@Test
public void testUpdatUser(){
User user = userMapper.findByUserId(18);
user.setSex("女");
user.setBirthday(new Date());
userMapper.updateUser(user);
}
/***
* @Description 根据id删除用户
* @Param: []
* @return: void
*/
@Test
public void testDeleteUserById(){
userMapper.deleteUserById(24);
}
}
注解实现复杂关系映射
复杂关系映射的注解说明
- @Results注解代替的是xml方式中的<resultMap>标签
- @Result注解代替的是<resultMap>标签中的<id>和<result>标签,<result>标签中有id,column,property,one,many属性。id 是否是主键字段、column 数据库的列名、property 需要装配的属性名、one 需要使用的@One 注解(@Result(one=@One)()))、many 需要使用的@Many 注解(@Result(many=@many)()))。
- @One注解(一对一映射),代替了<assocation>标签。
- @Many注解(多对一映射),代替了<collection>标签。
注解实现一对一
需求:查询账户信息并加载账户的用户信息,根据情况延迟加载。
添加账户Account实体类:
public class Account implements Serializable {
private String id;
private String uid;
private Double money;
//添加一对一属性
private User user;
}
账户持久层接口AccountMapper :
public interface AccountMapper {
@Select(value = "select * from account")
@Results(id = "accountMap",value = {
@Result(id = true,column = "id",property = "id" ),
@Result(id = true,column = "uid",property = "uid" ),
@Result(id = true,column = "money",property = "money" ),
@Result(column = "uid",
property = "user",
one = @One(select = "com.zy.dao.UserMapper.findByUserId",fetchType = FetchType.LAZY))
})
List<Account> findAll();
/***
* @Description 根据id查询
* @Param: [accountId]
* @return: com.zy.domain.Account
*/
@Select("select * from account where uid=#{uid}")
Account findByAccountId(Integer accountId);
}
用户持久层接口
public interface UserMapper {
@Results(id = "userMap", value = {
@Result(id = true,column = "id",property = "id"),
@Result(column = "id",property = "id"),
@Result(column = "username",property = "username"),
@Result(column = "birthday",property = "birthday"),
@Result(column = "sex",property = "sex"),
@Result(column = "address",property = "address")
})
@Select(value = "select * from user")
List<User> findAll();
/***
* @Description 根据id查询用户
* @Param: [userId]
* @return: com.zy.domain.User
*/
@Select(value = "select * from user where id = #{id}")
@ResultMap("userMap")
User findByUserId(Integer userId);
}
注解实现一对多
需求:查询用户信息时,也要查询他的账户列表。使用注解方式实现。
User实体类中添加List<Account>属性:
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
//一对多关系映射:主表方法应该包含一个从表方的集合引用
private List<Account> accounts;
}
用户持久层接口方法添加注解:
public interface UserMapper {
@Results(id = "userMap", value = {
@Result(id = true,column = "id",property = "id"),
@Result(column = "id",property = "id"),
@Result(column = "username",property = "username"),
@Result(column = "birthday",property = "birthday"),
@Result(column = "sex",property = "sex"),
@Result(column = "address",property = "address"),
@Result(column = "id",
property = "accounts",
many=@Many(select = "com.zy.dao.AccountMapper.findByAccountId",
fetchType = FetchType.LAZY))
})
@Select(value = "select * from user")
List<User> findAll();
/***
* @Description
* @Param: [userId]
* @return: com.zy.domain.User
*/
@Select(value = "select * from user where id = #{id}")
@ResultMap("userMap")
User findByUserId(Integer userId);
}
账户持久层方法:
/***
* @Description 根据id查询
* @Param: [accountId]
* @return: com.zy.domain.Account
*/
@Select("select * from account where uid=#{uid}")
Account findByAccountId(Integer accountId);
测试方法:
@Test
public void testFindAll(){
final List<User> users = userMapper.findAll();
for(User user : users){
System.out.println(user);
System.out.println(user.getAccounts());
}
}
加油吧