-
多条件查询
步骤一:先配置mybatis环境,然后再配置好JUnit和log4j。
步骤二:在com.shxt.model下创建POJO类,以及相应的映射文件。
package com.shxt.model;
public class User {
//注意:数据库里面的字段名,类里面的属性名,input标签name值要一致
private Integer userId;
private String userName;
private String pwd;
private String account;
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getAccount() {
return account;
}
public void setAccount(String account) {
this.account = account;
}
@Override
public String toString() {
return "User [userId=" + userId + ", userName=" + userName + ", pwd=" + pwd + ", account=" + account + "]";
}
}
<?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.shxt.model.User">
<sql id="shxt">
user_id userId,user_name userName,account,password pwd
</sql>
<!-- 通过id返回数据 -->
<select id="load" resultType="com.shxt.model.User" parameterType="integer">
select <include refid="shxt"></include>
from user where user_id=#{user_id}
</select>
<select id="login" parameterType="map" resultType="com.shxt.model.User">
select <include refid="shxt"></include>
from user where account=#{account} and password=#{password}
</select>
<select id="loginByUser" parameterType="com.shxt.model.User" resultType="com.shxt.model.User">
select <include refid="shxt"></include>
from user where account=#{account} and password=#{pwd}
</select>
</mapper>
注 :(1)这是select * from user查出来的视图
这是select user_id as userId,account,user_id as userId,user_name as userName,password as pwd from user 查询出来的视图,视图中的字段名发生了变化(如:user_id -> userId)。这是"as"关键词起别名的作用,而且as 可以省略。
当数据库中的字段与类中的属性不一致的时候,要用"as"关键词对sql语句改写。
(2)因为每次都要写sql中重复的内容,可以通过<sql>标签对其进行封装
<sql id="shxt">
user_id userId,user_name userName,account,password pwd
</sql>
然后再用<include>标签引用,如:
select <include refid="shxt"></include>
from user where user_id=#{user_id}
(3)当sql语句传递的参数为两个时,parameterType="map",而且参数名要与map中的key名一致。但是也可以直接传递过来一个对象,此时parameterType="com.shxt.model.User"(类名全路径),参数的名称此时是类中的属性名。
步骤三: 创建dao层,为达到行业规范的目的,dao层方法先在接口中创建抽象方法,然后创建实现类实现抽象方法。
package com.shxt.dao;
import java.util.Map;
import com.shxt.model.User;
public interface UserDao {
User load(Integer user_id);
User login(Map<String,Object> map);
User loginByUser(User user);
}
package com.shxt.dao.impl;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import com.shxt.dao.UserDao;
import com.shxt.model.User;
import com.shxt.utils.MybatisUtil;
public class UserDaoImpl implements UserDao {
SqlSession sqlSession = null;
@Override
public User load(Integer user_id) {
try {
sqlSession = MybatisUtil.getSqlSession();
User user = sqlSession.selectOne(User.class.getName()+".load", user_id);
return user;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("查询失败"+e.getMessage());
}finally {
MybatisUtil.closeSqlSession(sqlSession);
}
}
@Override
public User login(Map<String, Object> map) {
try {
sqlSession = MybatisUtil.getSqlSession();
User user = sqlSession.selectOne(User.class.getName()+".login", map);
return user;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("登录失败"+e.getMessage());
}finally {
MybatisUtil.closeSqlSession(sqlSession);
}
}
@Override
public User loginByUser(User user) {
try {
sqlSession = MybatisUtil.getSqlSession();
User u = sqlSession.selectOne(User.class.getName()+".loginByUser", user);
return u;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("登录失败"+e.getMessage());
}finally {
MybatisUtil.closeSqlSession(sqlSession);
}
}
}
注:(1)它们的结构应如下图所示
(2) 实现类实现接口时,有如下创建方式。
步骤三:书写基于JUnit的测试类。
package com.shxt.test;
import java.util.HashMap;
import org.junit.Before;
import org.junit.Test;
import com.shxt.dao.UserDao;
import com.shxt.dao.impl.UserDaoImpl;
import com.shxt.model.User;
public class MybatisTest {
UserDao userDao = null;
@Before
public void init() {
userDao = new UserDaoImpl();
}
@Test
public void load() {
User user = userDao.load(1);
System.out.println(user);
}
@Test
public void login() {
HashMap<String, Object> map = new HashMap<String,Object>();
map.put("account", "admin");
map.put("password", "admin");
User user = userDao.login(map);
System.out.println(user);
}
@Test
public void loginByUser() {
User user = new User();
user.setAccount("admin");
user.setPwd("admin");
User u = userDao.loginByUser(user);
System.out.println(u);
}
}
注:个人感觉JUnit版本中,JUnit4较为稳定,JUnit5中的@BeforEach老是报错,没找到错的原因,改回4就好了。
以下是部分执行结果: