学习Mybatis框架的话,如果没有学习他的动态sql的话等于白学了,浪费你的时间了,所以一定要学习Mybatis的动态sql,体验它的灵活性。
需求分析:
学习Mybatis框架sql的动态灵活性。
详细设计:
我们做个简单的t_user表,查询出姓名中含有ss,并且为男性的用户(1)。
t_user表结构
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` int(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES ('3', 'hello2', '22', '1');
INSERT INTO `t_user` VALUES ('4', 'huawei', '40', '1');
INSERT INTO `t_user` VALUES ('5', 'kkddkdk', '20', '0');
INSERT INTO `t_user` VALUES ('6', 'sdfdsw', '23', '0');
INSERT INTO `t_user` VALUES ('7', 'fdfdss', '45', '0');
在这儿我们得简单的设计一个包装的pojo。
UserCustom.java
package cn.bj.mybatis.model;
public class UserCustom extends User {
//扩展用户的信息
}
UserQueryVO.java
package cn.bj.mybatis.model;
public class UserQueryVO {
<span style="white-space:pre"> </span>private UserCustom userCustom;
<span style="white-space:pre"> </span>public UserCustom getUserCustom() {
<span style="white-space:pre"> </span>return userCustom;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setUserCustom(UserCustom userCustom) {
<span style="white-space:pre"> </span>this.userCustom = userCustom;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>//....加入订单。。。商品条件
}
User.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.bj.mybatis.models.UserMapper">
-->
<mapper namespace="cn.bj.mybatis.model.IUserOperation">
<select id="findUserList" parameterType="cn.bj.mybatis.model.UserQueryVO"
resultType="cn.bj.mybatis.model.UserCustom">
select * from t_user where t_user.sex=#{userCustom.sex} and t_user.username like '%${userCustom.username}%'
</select>
</mapper>
测试类代码:
package cn.bj.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import cn.bj.mybatis.model.IUserOperation;
import cn.bj.mybatis.model.UserCustom;
import cn.bj.mybatis.model.UserQueryVO;
public class MybatisTest {
public static void main(String[] args){
SqlSessionFactory sqlSessionFactory = null;
SqlSession session = null;
String resource = "Configuration.xml";
InputStream inputStream;
try {
inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
session = sqlSessionFactory.openSession();
IUserOperation userOperation = (IUserOperation)session.getMapper(IUserOperation.class);
UserQueryVO userQueryVO = new UserQueryVO();
UserCustom userCustom = new UserCustom();
userCustom.setUsername("ss");
userCustom.setSex(0);
userQueryVO.setUserCustom(userCustom);
try {
List<UserCustom> userCustoms = userOperation.findUserList(userQueryVO);
for(UserCustom u : userCustoms){
System.out.println(u.getUsername() + " " + u.getAge());
}
} catch (Exception e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}finally{
if(session != null){
session.close();
}
}
}
}
这是在多个表查询时,条件比较多的情况下我们用一个包装过的pojo类来达到我们多条件查询的目的。