mybatis Ⅱ
数据库和实体类映射的业内的共识规范
- 表名小写, User.java—>user EduUser.java—>edu_user
- 属性名称, name---->name userName—>user_name
mybatis中的别名
系统定义的别名
_byte--->byte
_long--->long
_short--->short
_int--->int
........
自定义别名
<typeAliases>
<typeAlias type="com.lyx.mybatis.pojo.User" alias="user"></typeAlias>
</typeAliases>
使用别名
<select id="findUserById" resultType="user" parameterType="int">
select * from user where id=#{value}
</select>
mybatis中的返回值
简单类型的返回值
<select id="findUserById" resultType="string" parameterType="int">
select name from user where id=#{value}
</select>
复杂类型的返回值
<select id="findUserById" resultType="com.lyx.mybatis.pojo.User" parameterType="int">
select * from user where id=#{value}
</select>
resultMap映射
<resultMap id="user1ResultMapping" type="com.lyx.mybatis.pojo.User1">
<id property="uid" column="id"></id>
<result property="uname" column="name"></result>
<result property="uage" column="age"></result>
</resultMap>
<select id="findUserById" resultMap="user1ResultMapping" parameterType="int">
select * from user where id=#{value}
</select>
- resultType :指定输出结果的类型(pojo、简单类型、hashmap…),将sql查询结果映射为java对象
- 使用resultType注意:sql查询的列名要和resultType指定pojo的属性名相同,指定相同 属性方可映射成功,-如果sql查询的列名要和resultType指定pojo的属性名全部不相同,list中无法创建pojo对象的。
- resultMap:将sql查询结果映射为java对象。
- 如果sql查询列名和最终要映射的pojo的属性名不一致,使用resultMap将列名和pojo的属性名做一个对应关系 (列名和属性名映射配置)
动态sql
mybatis重点是对sql的灵活解析和处理
where…if语句
<select id="listByUser" parameterType="user" resultType="user">
select * from user01
<where>
<if test="nickname!=null">
nickname=#{nickname}
</if>
<if test="password!=null">
and password=#{password}
</if>
</where>
</select>
foreach 语句
foreach元素的属性主要有 item,index,collection,open,separator,close。
-
collection接收一个集合为参数时 collection的值为list
select * from user <where> <foreach collection="list" item="id" open="id in (" separator="," close=")"> #{id} </foreach>
-
item表示集合中每一个元素进行迭代时的别名,
-
index指定一个名字,用于表示在迭代过程中,每次迭代到的位置,
-
open表示该语句以什么开始,
-
separator表示在每次进行迭代之间以什么符号作为分隔符,
-
close表示以什么结束
<select id="listInId" resultType="user">
select * from user01
<where>
/*当传入的参数为List类型,colletion的值为list*/
<foreach collection="list" item="id" open="id in(" separator="," close=")">
#{id}
</foreach>
</where>
</select>
//1.创建SqlSessionFactory的对象
InputStream resourceAsStream = Main.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//2.创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3.返回由jdk底层生成的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.listInId(Arrays.asList(1,3));
System.out.println(userList);
if + set + where语句 (主要用来更新)
<update id="updateById" parameterType="user">
update user01
<set>
<if test="nickname!=null">
nickname=#{nickname}
</if>
</set>
<where>
<if test="id!=null">
id=#{id}
</if>
<if test="id==null">
id=1
</if>
id=1
</where>
</update>
//1.创建SqlSessionFactory的对象
InputStream resourceAsStream = Main.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//2.创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3.返回由jdk底层生成的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
choose+when+otherwise
<!--如果仅传入一个类型为String或是基本数据类型的参数,那么在 xml文件中应该使用_parameter 来代替参数名-->
<select id="listById" resultType="user" parameterType="java.lang.Integer">
select * from user01
<where>
<choose>
<when test="_parameter>10">
id=1
</when>
<when test="_parameter>20">
id=2
</when>
<otherwise>
id=3
</otherwise>
</choose>
</where>
</select>
//1.创建SqlSessionFactory的对象
InputStream resourceAsStream = Main.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//2.创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3.返回由jdk底层生成的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.listById(20);
System.out.println(userList);
User user = new User();
//user.setId(1);
user.setNickname("小李子2");
Integer integer = mapper.updateById(user);
System.out.println(integer);
System.out.println(user);
mybatis中接收多个参数(重要)
可以接收一个参数 也可以接收多个参数
接收普通的单一参数
<select id="findById" parameterType="int" resultType="user">
select * from
user01 where id = #{value}
</select>
User user = mapper.findById(1);
System.out.println(user);
接收对象类型的参数
<select id="findByUser" parameterType="user" resultType="user">
select * from
user01 where nickname = #{nickname}
</select>
User user = new User();
user.setNickname("张总");
User byUser = mapper.findByUser(user);
System.out.println(byUser);
接收多个参数
List<User> findByNicknameOrPassword(String nickname, String password);
<select id="findByNicknameOrPassword" resultType="user">
select *
from user
where nickname = #{param1}
or password = #{param2}
</select>
但是上面使用param1和param2…来接收形参数据,名称不太友好(不能见名知意) ,所有Mybatis中给我们提供了一个注解 @Param("xxx")
,我们可以使用这个注解表示形参名称,来给mybatis使用;
public interface UserMapper {
List<User> findByNicknameOrPassword(@Param("nickname") String nickname, @Param("password") String password);
}
<select id="findByNicknameOrPassword" resultType="user">
select *
from user
where nickname = #{nickname}
or password = #{password}
</select>
mybatis中的结果映射
一对一的映射
一对一的关系在实际开发中很少见,因为一对一的关系都会直接搞成一个附加的字段
创建pojo类
User
public class User {
private Integer id;
private String nickname;
private String password;
private Address address;
private List<Role> roles;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getNickname() {
return nickname;
}
public void setNickname(String nickname) {
this.nickname = nickname;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public List<Role> getRoles() {
return roles;
}
public void setRoles(List<Role> roles) {
this.roles = roles;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", nickname='" + nickname + '\'' +
", password='" + password + '\'' +
", address=" + address +
", roles=" + roles +
'}';
}
}
Address
public class Address {
private Integer id;
private String prov;
private String city;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getProv() {
return prov;
}
public void setProv(String prov) {
this.prov = prov;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
@Override
public String toString() {
return "Address{" +
"id=" + id +
", prov='" + prov + '\'' +
", city='" + city + '\'' +
'}';
}
}
创建mapper接口
List<User> findById(@Param("id") Integer id);
创建mapper映射
<resultMap id="userAddressMap" type="user">
<id property="id" column="id"></id>
<result property="nickname" column="nickname"></result>
<result property="password" column="password"></result>
<!--一对一的关系-->
<association property="address" javaType="com.lyx.entity.Address">
<id property="id" column="id"></id>
<result property="prov" column="prov"></result>
<result property="city" column="city"></result>
</association>
</resultMap>
<select id="findById" resultMap="userAddressMap">
select * from user01,address where user01.id = address.id and user01.id=#{id}
</select>
执行查询
public static void main(String[] args) {
//1.创建SqlSessionFactory的对象
InputStream resourceAsStream = Main.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//2.创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3.返回由jdk底层生成的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//4.执行业务方法
List<User> users = mapper.findById(1);
System.out.println(users);
}
一对多的映射
创建pojo类
User
public class User {
private Integer id;
private String nickname;
private String password;
private Address address;
private List<Role> roles;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getNickname() {
return nickname;
}
public void setNickname(String nickname) {
this.nickname = nickname;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public List<Role> getRoles() {
return roles;
}
public void setRoles(List<Role> roles) {
this.roles = roles;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", nickname='" + nickname + '\'' +
", password='" + password + '\'' +
", address=" + address +
", roles=" + roles +
'}';
}
}
Role
public class Role {
private Integer id;
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Role{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
创建mapper接口
List<User> findById(@Param("id") Integer id);
创建mapper映射文件
<resultMap id="userAddressMap" type="user">
<id property="id" column="uid"></id>
<result property="nickname" column="nickname"></result>
<result property="password" column="password"></result>
<!--一对一的关系-->
<association property="address" javaType="com.lyx.entity.Address">
<id property="id" column="uid"></id>
<result property="prov" column="prov"></result>
<result property="city" column="city"></result>
</association>
<!--一对多的关系-->
<collection property="roles" ofType="com.lyx.entity.Role">
<id property="id" column="rid"></id>
<result property="name" column="rname"></result>
<result property="descp" column="rdescp"></result>
</collection>
</resultMap>
<select id="findById" resultMap="userAddressMap">
select
user01.id uid,
user01.nickname,
user01.password,
address.prov,
address.city,
role.id rid,
role.name rname,
role.descp rdescp
from user01
inner join role
inner join user_role_rel urr
inner join address
on user01.id=urr.uid and role.id=urr.rid and address.id=urr.uid
where user01.id=#{id}
</select>
运行
public static void main(String[] args) {
//1.创建SqlSessionFactory的对象
InputStream resourceAsStream = Main.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//2.创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3.返回由jdk底层生成的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//4.执行业务方法
List<User> users = mapper.findById(1);
System.out.println(users);
}
复杂的映射
public static void main(String[] args) {
//1.创建SqlSessionFactory的对象
InputStream resourceAsStream = Main.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//2.创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(true);
//3.返回由jdk底层生成的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//4.执行业务方法
List<User> users = mapper.findById(1);
System.out.println(users);
}
mybatis的逆向工程
- mybatis官方给我们提供的一个工具
- 用来根据表结构生成pojo类
依赖pom
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.5</version>
</dependency>
配置generatorconfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<context id="generateTables" targetRuntime="MyBatis3">
<plugin type="org.mybatis.generator.plugins.SerializablePlugin"/>
<commentGenerator>
<!-- 是否去除自动生成的注释 true:是 : false:否 -->
<property name="suppressAllComments" value="true"/>
<property name="suppressDate" value="true"/>
</commentGenerator>
<!--数据库连接的信息:驱动类、连接地址、用户名、密码 -->
<jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/demo?serverTimezone=GMT%2B8"
userId="root"
password="root"
>
</jdbcConnection>
<!-- <jdbcConnection driverClass="oracle.jdbc.OracleDriver" connectionURL="jdbc:oracle:thin:@127.0.0.1:1521:yycg"
userId="yycg" password="yycg"> </jdbcConnection> -->
<!-- 默认false,把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer,为 true时把JDBC DECIMAL
和 NUMERIC 类型解析为java.math.BigDecimal -->
<javaTypeResolver>
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!-- targetProject:生成PO类的位置 -->
<javaModelGenerator targetPackage="com.lyx.entity"
targetProject=".\src\main\java">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false"/>
<!-- 从数据库返回的值被清理前后的空格 -->
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!-- targetProject:mapper映射文件生成的位置 -->
<sqlMapGenerator targetPackage="com.lyx.mapper"
targetProject=".\src\main\java">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false"/>
</sqlMapGenerator>
<!-- targetPackage:mapper接口生成的位置 -->
<javaClientGenerator type="XMLMAPPER"
targetPackage="com.lyx.mapper" targetProject=".\src\main\java">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false"/>
</javaClientGenerator>
<!-- 指定数据库表 -->
<table schema="general" tableName="user" domainObjectName="User"
enableCountByExample="false" enableUpdateByExample="false"
enableDeleteByExample="false" enableSelectByExample="true"
selectByExampleQueryId="false">
<property name="useActualColumnNames" value="false"/>
</table>
<table schema="general" tableName="address" domainObjectName="Address"
enableCountByExample="false" enableUpdateByExample="false"
enableDeleteByExample="false" enableSelectByExample="true"
selectByExampleQueryId="false">
<property name="useActualColumnNames" value="false"/>
</table>
</context>
</generatorConfiguration>
生成pojo
public static void main(String[] args) throws Exception {
List<String> warnings = new ArrayList<String>();
boolean overwrite = true;//加载上面的配置文件
File configFile = new File("./src/main/resource/Generatorconfig.xml");
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(configFile);
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
myBatisGenerator.generate(null);
}