1.mapper动态代理开发
1.1还是先在mybaits的配置文件中把mapper的xml配置好
sqlMapConfig.xml:<mapper resource="mybaits/mapper/UserMapper.xml"/>
1.2 UserMapper.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">
<!--命名空间指定UserMapper,把UserMapper和UserMapper.xml绑定-->
<mapper namespace="mybaits.mapper.UserMapper">
<select id="findById" parameterType="Integer" resultType="mybaits.pojo.User">
SELECT * FROM user WHERE id = #{value}
</select>
</mapper>
1.3 UserMapper.java
package mybaits.mapper;
import mybaits.pojo.User;
//必须是接口
public interface UserMapper {
//方法名、参数、返回值需要和Usermapper.xml的select的id、parameterType、resultType一致
public User findById(Integer id);
}
1.4 调用
@Test
public void test() throws IOException {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.findById(32);
System.out.println(user);
}
其实区别就是之前的是使用sqlSession.select/insert(方法名,方法参数)
现在是sqlSession.getMapper(XXXMapper.class),拿到接口的对象,使用接口的对象调用方法。
2 mybaits配置文件的<mappers>
<mappers>主要是告诉mybaits,书写了sql的xml文件在哪里。
- <mapper resource="mybaits/sqlmap/User.xml"/>
直接指定xml
- <mapper class="com.itheima.mybatis.mapper.UserMapper" />
指定java类,不过需要UserMapper.java和UserMapper.xml必须在同一个文件夹下,然后必须同名
- <package name="mybaits.sqlmap"/>
指定一个文件夹,也是要UserMapper.java和UserMapper.xml必须在同一个文件夹下,然后必须同名
3 输入类型为Pojo包装类QueryVo
QueryVo也是一个pojo类,只是它内部包含了其他的pojo类
比如根据User类的id查询,User类是QueryVo的一个参数,传参的时候直接把QueryVo传给mapper的xml,使用的时候直接user.id
QueryVo:
package mybaits.pojo;
public class QueryVo {
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
UserMapper.java
//必须是接口
public interface UserMapper {
//方法名、参数、返回值需要和Usermapper.xml的select的id、parameterType、resultType一致
public User findById(Integer id);
//看这一句话
public User findByIdByQueryVo(QueryVo queryVo);
}
UserMapper.xml
<select id="findByIdByQueryVo" parameterType="mybaits.pojo.QueryVo" resultType="mybaits.pojo.User">
SELECT * FROM user WHERE id = #{user.id}
</select>
调用者:
@Test
public void test2() throws IOException {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
QueryVo queryVo = new QueryVo();
User user = new User();
user.setId(31);
user.setUsername("tom");
queryVo.setUser(user);
User backUser = mapper.findByIdByQueryVo(queryVo);
System.out.println(backUser);
}
4 resultMap :pojo类和数据库表的字段不一致。
首先当pojo类和数据库表的字段不一致时,依然使用resultType的话,查询到的数据中不一致的字段是无法被赋值的。
比如
![6262743-8aa043cc9d2c82ff.png](https://i-blog.csdnimg.cn/blog_migrate/6275ded44034b9e91d906f518e9b4a86.png)
![6262743-2324ad19752dac69.png](https://i-blog.csdnimg.cn/blog_migrate/863243e57d7b69c651861403547be4b1.png)
可以看到字段userid不一样,然后查询到的结果是:
![6262743-5f9cf3b27a32aacc.png](https://i-blog.csdnimg.cn/blog_migrate/8b3e14f761daa111e94bf36356ea0579.png)
使用:resultMap
<mapper namespace="mybaits.mapper.OrderMapper">
<resultMap id="resultmap1" type="mybaits.pojo.Orders">
<!--column:数据库的字段名,property:pojo类的字段名-->
<id column="id" property="id"/>
<id column="user_id" property="userId"/>
<id column="number" property="number"/>
</resultMap>
<!--resultMap="resultMap的id"-->
<select id="find" parameterType="mybaits.pojo.Orders" resultMap="resultmap1">
SELECT * FROM orders WHERE id = #{id}
</select>
</mapper>
查询到的结果:
![6262743-857bc37d282e38d0.png](https://i-blog.csdnimg.cn/blog_migrate/3be5814800dd3cacfb43eb7ebe8a5795.png)
5 动态sql(复杂一点的sql)
5.1 if where
需求:根据性别和名字查询。但是姓名和性别可能为空。
大概sql语句是:select * from user where id = id and username = username
但是呢,如果id或者username为空的话,上面的sql语句就出错了,所以需要if else 判空
mapper:
<!--<where>的作用是可以去掉第一个前and-->
<select id="findBySexAndName" parameterType="mybaits.pojo.User" resultType="mybaits.pojo.User">
SELECT * FROM user
<where>
<if test="id != null and id!= ''">
id = #{id}
</if>
<if test="username != null and username != ''">
AND username = #{username}
</if>
</where>
</select>
5.2 提取公共sql代码(感觉没啥用)
<sql id="sql1">
SELECT * FROM user
</sql>
<!--<where>的作用是可以去掉第一个前and-->
<select id="findBySexAndName" parameterType="mybaits.pojo.User" resultType="mybaits.pojo.User">
<!--引用sql的语句-->
<include refid="sql1"/>
<where>
<if test="id != null and id!= ''">
id = #{id}
</if>
<if test="username != null and username != ''">
AND username = #{username}
</if>
</where>
</select>
5.3 foreach
需求:根据多id查询多个
sql语句:select * from user where id in (1,2,3)
这个(1,2,3)是数组传进来的,不一定有多少个
调用者:
@Test
public void test5() throws IOException {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Integer[] ids = new Integer[3];
ids[0] = 10;
ids[1] = 16;
ids[2] = 22;
List<User> list = mapper.findByIds(ids);
for (User u : list) {
System.out.println(u);
}
}
mapper:
<!--虽然有数组参数传进来,但是不需要parameterType-->
<select id="findByIds" resultType="mybaits.pojo.User">
SELECT * FROM user
<where>
id IN
<!--这里array代表数组,list代表集合-->
<foreach collection="array" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</where>
</select>
5.4 关联查询
需求:查询多有的订单order,然后关联到订单的购买者User
一个User可以购买多个订单,一个订单只属于一个User
mapper:
<resultMap id="resultmap2" type="mybaits.pojo.Orders">
<!--result 和 id效果一样,只是id是用来id的,result是用来其他字段的-->
<!--关联查询需要给所有字段都映射一下,不映射的字段会是null-->
<id column="id" property="id"/>
<result column="number" property="number"/>
<result column="user_id" property="userId"/>
<result column="createtime" property="createtime"/>
<!--关联User-->
<association property="user" javaType="mybaits.pojo.User">
<result column="username" property="username"/>
</association>
</resultMap>
<select id="selectOrder" resultMap="resultmap2">
SELECT o.id, o.user_id, o.number, o.createtime, u.username FROM orders o LEFT JOIN user u ON o.user_id = u.id
</select>
查询到的结果:
Orders{id=3, userId=1, number='1000010', createtime=Wed Feb 04 13:22:35 CST 2015, note='null', username=王五}
Orders{id=4, userId=1, number='1000011', createtime=Tue Feb 03 13:22:41 CST 2015, note='null', username=王五}
Orders{id=5, userId=10, number='1000012', createtime=Thu Feb 12 16:13:23 CST 2015, note='null', username=张三}
6 mybaits逆向工程
根据数据库表,自动生产pojo类
在导入工程,然后配置generatorConfig.xml,然后把生成的类复制过去
6 番外:mysql的大小写
在Linux下:
数据库名和表名是区分大小写的
在Windows下:不区分