Mybatis和hibernate不同,它不完全是一个ORM框架,因为MyBatis需要程序员自己编写Sql语句。mybatis可以通过XML或注解方式灵活配置要运行的sql语句,并将java对象和sql语句映射生成最终执行的sql,最后将sql执行的结果再映射生成java对象。
#{}和${}
#{}表示一个占位符号,通过#{}可以实现preparedStatement向占位符中设置值,自动进行java类型和jdbc类型转换。#{}可以有效防止sql注入。 #{}可以接收简单类型值或pojo属性值。 如果parameterType传输单个简单类型值,#{}括号中可以是value或其它名称。
${}表示拼接sql串,通过${}可以将parameterType 传入的内容拼接在sql中且不进行jdbc类型转换, ${}可以接收简单类型值或pojo属性值,如果parameterType传输单个简单类型值,${}括号中只能是value。
parameterType和resultType
parameterType:指定输入参数类型,mybatis通过ognl从输入对象中获取参数值拼接在sql中。
resultType:指定输出结果类型,mybatis将sql查询结果的一行记录数据映射为resultType指定类型的对象。如果有多条数据,则分别进行映射,并把对象放到容器List中
<?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">
<!-- namespace:命名空间,用于隔离sql,还有一个很重要的作用,后面会讲 -->
<mapper namespace="test">
<select id="findUserById" parameterType="Integer" resultType="lx.mybatis.User">
select * from user where id = #{v}
</select>
<select id="findUserByUserName" parameterType="String"
resultType="lx.mybatis.User">
//防止sql语句注入
select * from user where username like "%"#{hh}"%"
<!-- select * from user where username like '%${value}%' -->
</select>
<insert id="insertUser" parameterType="lx.mybatis.User">
<selectKey keyProperty="id" resultType="Integer" order="AFTER">
select LAST_INSERT_ID()
</selectKey>
insert into User (username) values (#{username})
</insert>
<update id="updateUser" parameterType="lx.mybatis.User">
update user set username = #{username}
where id = #{id}
</update>
<delete id="deleteUser" parameterType="Integer">
delete from user where id = #{id}
</delete>
</mapper>
别名 |
映射的类型 |
_byte |
byte |
_long |
long |
_short |
short |
_int |
int |
_integer |
int |
_double |
double |
_float |
float |
_boolean |
boolean |
string |
String |
byte |
Byte |
long |
Long |
short |
Short |
int |
Integer |
integer |
Integer |
double |
Double |
float |
Float |
boolean |
Boolean |
date |
Date |
decimal |
BigDecimal |
bigdecimal |
BigDecimal |
map |
Map |
自定义别名
在SqlMapConfig.xml中配置如下:
<typeAliases>
<!-- 单个别名定义 -->
<typeAlias alias="user" type="cn.itcast.mybatis.pojo.User" />
<!-- 批量别名定义,扫描整个包下的类,别名为类名(大小写不敏感) -->
<package name="cn.itcast.mybatis.pojo" />
<package name="其它包" />
</typeAliases>
------------------------------------
<configuration>
<!-- 设置别名 -->
<typeAliases>
<!-- 2. 指定扫描包,会把包内所有的类都设置别名,别名的名称就是类名,大小写不敏感 -->
<package name="lx.pojo" />
</typeAliases>
<mappers>
<package name="lx.mapper" />
<!-- <mapper resource="lx/mapper/User.xml" /> -->
</mappers>
</configuration>
mapper动态代理开发遵循的四个原则
1.接口方法名==User.xml中id名
2.返回值类型与User.xml一致
3.方法的入参类型要与User.xml一致
4.命名空间绑定此接口
resultType可以指定将查询结果映射为pojo,但需要pojo的属性名和sql查询的列名一致方可映射成功。
如果sql查询字段名和pojo的属性名不一致,可以通过resultMap将字段名和属性名作一个对应关系 ,resultMap实质上还需要将查询结果映射到pojo对象中。
resultMap可以实现将查询结果映射为复杂类型的pojo,比如在查询结果映射对象中包括pojo和list实现一对一查询和一对多查询。
<!-- <select id="selectOrdersList" resultType="Orders"> -->
<resultMap type="Orders" id="orders">
<result column="user_id" property="userId"/>
</resultMap>
<select id="selectOrdersList" resultMap="orders">
SELECT id, user_id, number, createtime, note FROM orders
</select>
if where 标签 where标签可以去掉第一个前and
<!--sql片段-->
<sql id="selector">
select * from user
</sql>
<!-- 根据性别和名字查询用户 where 可以去掉第一个前ANd -->
<select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
<include refid="selector"/>
<where>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="username != null and username != ''">
and username = #{username}
</if>
</where>
</select>
多个id
QueryVo.java(包装类)
package com.itheima.mybatis.pojo;
import java.io.Serializable;
import java.util.List;
/**
* new Message
* @author lx
*
*/
public class QueryVo implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private User user;
List<Integer> idsList;
Integer[] ids;
public List<Integer> getIdsList() {
return idsList;
}
public void setIdsList(List<Integer> idsList) {
this.idsList = idsList;
}
public Integer[] getIds() {
return ids;
}
public void setIds(Integer[] ids) {
this.ids = ids;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
UserMapper.java
// public List<User> selectUserByIds(Integer[] ids);array
public List<User> selectUserByIds(List<Integer> ids);//list
// public List<User> selectUserByIds(QueryVo vo);//idsList
UserMapper.xml
<!-- 多个ID (1,2,3)-->
<select id="selectUserByIds" parameterType="QueryVo" resultType="User">
<include refid="selector"/>
<where>
<foreach collection="list" item="id" separator="," open="id in (" close=")">
#{id}
</foreach>
</where>
</select>
@Test
public void testfindUserIDs() throws Exception {
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<Integer> ids = new ArrayList<>();
ids.add(16);
ids.add(22);
ids.add(24);
/* QueryVo vo = new QueryVo();
vo.setIdsList(ids);*/
// List<User> users = userMapper.selectUserByIds(vo);
/* Integer[] ids = new Integer[3];
ids[0] = 16;
ids[2] = 22;
ids[1] = 24;*/
List<User> users = userMapper.selectUserByIds(ids);
for (User user2 : users) {
System.out.println(user2);
}
}
一对一关联(附加对象方式,还可以new 一个新的pojo)
User.java
package com.itheima.mybatis.pojo;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
public class User implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private Integer id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
//附加对象List
private List<Orders> ordersList;
public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", sex=" + sex
+ ", birthday=" + birthday + ", address=" + address + "]";
}
}
Orders.java
package com.itheima.mybatis.pojo;
import java.io.Serializable;
import java.util.Date;
public class Orders implements Serializable{
@Override
public String toString() {
return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
+ ", note=" + note + "]";
}
/**
*
*/
private static final long serialVersionUID = 1L;
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
//附加对象 用户对象
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number == null ? null : number.trim();
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note == null ? null : note.trim();
}
}
<!--
//一对一关联 查询 以订单为中心 关联用户
public List<Orders> selectOrders();
-->
<resultMap type="Orders" id="order">
<result column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<!-- 一对一 -->
<association property="user" javaType="User">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
</association>
</resultMap>
<select id="selectOrders" resultMap="order">
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>
一对多关联
//一对多关联
public List<User> selectUserList(); -->
<resultMap type="User" id="user">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<!-- 一对多 -->
<collection property="ordersList" ofType="Orders">
<id column="id" property="id"/>
<result column="number" property="number"/>
</collection>
</resultMap>
<select id="selectUserList" resultMap="user">
SELECT
o.id,
o.user_id,
o.number,
o.createtime,
u.username
FROM user u
left join orders o
on o.user_id = u.id
</select>