publicinterface UserDao {
// 根据ID查找对象public User findUserById(int id);
}
UserDao.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"><mappernamespace="com.lanou.dao.UserDao"><selectid="findUserById"parameterType="Integer"resultType="User" >
select * from User where id = #{param}
</select></mapper>
MybatisTest.java
publicclass MybatisTest {
@Test
publicvoidtest() throws IOException {
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = builder.build(inputStream);
SqlSession session = factory.openSession();
UserDao dao = session.getMapper(UserDao.class);
User user = dao.findUserById(6);
System.out.println(user);
}
}
使用包装类
UserDataObj.java
package com.lanou.bean;
publicclassUserDataObj {private User user;
public User getUser() {
return user;
}
publicvoidsetUser(User user) {
this.user = user;
}
@Overridepublic String toString() {
return"UserDataObj [user=" + user + "]";
}
}
<mapper namespace="com.lanou.dao.UserDao">
<select id="findUserById" parameterType="Integer" resultType="User" >
select * from User where id = #{param}
</select>
<insert id="insertUserByUserDataObj" parameterType="UserDataObj">
insert into user values (
null,
#{user.name},#{user.password},#{user.sex}
)
</insert>
</mapper>
MybatisTest.java
public void test1() throws IOException {
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = builder.build(inputStream);
SqlSession session = factory.openSession();
User user = new User();
user.setName("刘邦");
user.setPassword("12345");
user.setSex("男");
UserDataObj obj = new UserDataObj();
obj.setUser(user);
UserDao dao = session.getMapper(UserDao.class);
int i = dao.insertUserByUserDataObj(obj);
System.out.println(i);
session.commit();
}
标签根据多个id查询一堆user
根据一堆id查询user, id in (1,2,3) foreach标签用来遍历参数
collection 指定被遍历的集合
item 每一次遍历得到的变量seperator
open 开始标记
close 结束标记
UserDao.java
publicinterface UserDao {
// 根据ID查找对象public User findUserById(int id);
// 使用包装类添加对象publicintinsertUserByUserDataObj(UserDataObj obj);
public List<User> selectUserByIds(Integer[] ids);
}
UserDao.xml
当我们传的参数是一个数组时
java中没有对应的包装类型
可以写object 也可以不写
parameterType 作用是规范书写 怕你转错类型
遍历数组时 参数的名字是固定的 array
遍历ArrayList时 参数的名字是固定的 list
Mybatis参数映射的原理
其实就是把你传过来的参数转成map 以键值对的方式传递
所以基础数据类型的获取方式是${value} value也是固定的
<select id="selectUserByIds" parameterType="java.lang.Object" resultType="User">
select * from user where id in
<foreach collection="array" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</select>
MybatisTest.java
publicvoidtest2() throws IOException {
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = builder.build(inputStream);
SqlSession session = factory.openSession();
Integer[] ids = new Integer[] {6,10,11};
UserDao dao = session.getMapper(UserDao.class);
List<User> list = dao.selectUserByIds(ids);
for (User user : list) {
System.out.println(user);
}
}
将参数Integer[]改为List
UserDao.java
publicinterface UserDao {
// 根据ID查找对象public User findUserById(int id);
// 使用包装类添加对象publicintinsertUserByUserDataObj(UserDataObj obj);
public List<User> selectUserByIds(List<Integer> ids);
}
UserDao.xml
<select id="selectUserByIds" parameterType="java.util.List" resultType="User">
select * from user where id in
<foreach collection="list" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</select>
之所以需要1=1 因为如果name为空 则不加1=1时 sql语句为
select * fromuserwhereand sex = #{sex}
或sex为空
select * fromuserwhereand name = #{sex}
当加上1=1时
select * fromuserwhere1=1and sex = #{sex}
select * fromuserwhere1=1and name = #{name}
select * fromuserwhere1=1and name = #{name} and sex = #{sex}
<select id="getUser" parameterType="User" resultType="User">
select * fromuserwhere1=1
<if test="name != null and name != ''">
and name = #{name}
</if>
<if test="sex != null and name !=''">
and sex = #{sex}
</if>
</select>
UserDao.xml
public void test3() throws IOException {
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = builder.build(inputStream);
SqlSession session = factory.openSession();
User user = new User();
user.setName("刘邦");
user.setSex("男");
UserDao dao = session.getMapper(UserDao.class);
User user2 = dao.getUser(user);
System.out.println(user2);
}
where标签
where标签的作用 执行内部的逻辑并拼接到外部的sql中 会自动删除第一个and
<select id="getUser" parameterType="User" resultType="User">
select * from user
<where>
<if test="name != null and name != ''">
and name = #{name}
</if>
<if test="sex != null and name !=''">
and sex = #{sex}
</if>
</where>
</select>
sql标签
如果有很多重复的sql语句 没有必要每次写 可以对它进行抽取
<sql id="selectUser">
select * from User
</sql>
<select id="findUserById" parameterType="int" resultType="com.lanou.bean.User">
<include refid="selectUser"></include> where id = #{param}
</select>
publicinterface OrderDao {
public List<Orders> selectAllOrdersAndUser();
}
OrderDao.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"><mappernamespace="com.lanou.dao.OrderDao"><resultMaptype="Orders"id="OrdersMap"autoMapping="true"><resultproperty="id"column="o_id"/><resultproperty="name"column="o_name"/><associationproperty="user"autoMapping="true"javaType="User"><resultproperty="id"column="u_id"/><resultproperty="name"column="u_name"/></association></resultMap><selectid="selectAllOrdersAndUser"resultMap="OrdersMap">
select *,
Orders.id as o_id,
Orders.name as o_name,
User.id as u_id,
User.name as u_name
from Orders left join
User on Orders.userid = User.id
</select></mapper>
<?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"><mappernamespace="com.lanou.dao.UserDao"><resultMaptype="User"id="UserMap"autoMapping="true"><resultproperty="id"column="id"/><collectionproperty="groups"autoMapping="true"ofType="Groups"></collection></resultMap><selectid="selectUsersAndGroups"resultMap="UserMap">
select *
from user
left join u_r_g on user.id = u_r_g.uid
left join groups on u_r_g.gid = groups.g_id
</select></mapper>
MybatisTest3.java
public void test3() throws IOException {
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = builder.build(inputStream);
SqlSession session = factory.openSession();
UserDao dao = session.getMapper(UserDao.class);
List<User> user = dao.selectUsersAndGroups();
for (User user2 : user) {
System.out.println(user2);
}
}
<?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"><mappernamespace="com.lanou.dao.groupsDao"><resultMaptype="Groups"id="GroupsMap"autoMapping="true"><resultproperty="id"column="id"/><collectionproperty="users"ofType="User"autoMapping="true"></collection></resultMap><selectid="selectAllGroups"resultMap="GroupsMap">
select * FROM
groups left join u_r_g on groups.g_id = u_r_g.gid
left join user on u_r_g.uid = user.id
</select></mapper>