【JAVAWEB】Mybatis

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>