Mybatis的概念:
开源的持久层框架,底层仍然是jdbc,可以简化jdbc的操作,实现数据的持久化。Mybatis是ORM的一个实现。
ORM概念: Object Ralational Mapping,可以使开发人员像操作对象一样操作数据库表。例如hibernate。
JDBC:易掌握,性能好,代码繁琐。
Hibernate:难掌握,性能不好(复杂的查询经常需要优化sql),代码简洁,可以不写sql。
Mybatis:比较容易掌握,性能一般,代码简洁,需要写sql。
Mybatis实现动态SQL的主要元素:
- if
- choose(when,otherwise)
- where
- set
- foreach
- trim
原理:
开发Mybatis程序步骤:
1.配置mybatis的fonfig.xml:配置数据库信息和需要加载的映射文件(XXMapper.xml)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- 通过这个配置文件完成mybatis与数据库的连接 -->
<configuration>
<!--引入配置文件-->
<properties resource="database.properties"></properties>
<!--设置别名-->
<typeAliases>
<!--给你当前包下面的所有类取别名,别名就是你的类名-->
<package name="com.offcn.entity"></package>
<!--给具体的类加上别名 type类型就是你要加的别名的完整的包名+类名
alias就是取的别名的名称-->
<!--<typeAlias type="com.offcn.entity.User" alias="user"></typeAlias>-->
</typeAliases>
<!--开始配置环境,可以配置多个环境,mysql、oracal-->
<environments default="development">
<environment id="development">
<!--事务,用jdbc来进行事物管理-->
<transactionManager type="JDBC"></transactionManager>
<!--配置数据源,*jndi*(由tomcat容器分配的数据源),*pooled*(mybatis自带的数据源)-->
<dataSource type="POOLED">
<property name="url" value="${url}"></property>
<property name="driver" value="${driver}"></property>
<property name="username" value="${username}"></property>
<property name="password" value="${username}"></property>
</dataSource>
</environment>
</environments>
<!--与你的UserMapper进行关联,可以关联多个,resource代表其路径,这里的.都要换成斜杠-->
<mappers>
<!-- <mapper resource="com/offcn/dao/UserMapper.xml"></mapper>
<mapper resource="com/offcn/dao/AddressMapper.xml"></mapper>-->
<mapper class="com.offcn.dao.UserMapper"></mapper>
</mappers>
</configuration>
2.创建pojo接口,如XXMapper
package com.offcn.dao;
import com.offcn.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
@Mapper
public interface UserMapper {
//根据用户角色 与名字来进行模糊查询
//使用注解的方式来注入参数
List<User> selectByName(@Param("userName")String userName,@Param("userRole")Integer userRole);
//使用map查询
List<User> selectByMap(Map<String,String> map);
//复杂的查询
List<User> selectByRole(User user);
//根据名字来查询 当前用户下的地址
List<User> selectByUserName(String userName);
//增加一个用户
int addUser(User user);
//修改一个用户
int updateUser(User user);
//查询出userRole 为2或者3的用户 传递一个数据
List<User> selectArrayUser(Integer[] userRole);
List<User> selectListUser(List list);
/* //查询全部 注解版
@Select("select * from smbms_user")
List<User> selectAllUser();
//模糊查询 最终版
List<User> selectLikeUser(String Username);*/
}
3.创建XXMapper的映射文件(XXMapper.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">
<!--namespace找到你关联的那个接口类 包名+接口名 相当于一个包,包管理器-->
<mapper namespace="com.offcn.dao.UserMapper">
<!--这个id是唯一的,是你访问的一个标识符,查询中记录数,resultType代表其返回值类型
id名字必须跟UserMapper里的方法名一样-->
<select id="selectCount" resultType="int">
select count(1) from smbms_user
</select>
<!--全查,resultType 可以给别名-->
<select id="selectAllUser" resultType="com.offcn.entity.User">
select * form smbms_user
</select>
<!--模糊查询 parameterType 这个参数的类型,#{userName}代表我们以前的?也就是占位符,模糊查询一定要加concat-->
<select id="selectLikeUser" resultType="com.offcn.entity.User" parameterType="String">
select * from smbms_user where userName like concat('%',#{userName},'%')
</select>
<!--通过id查找对象-->
<select id="selectById" resultType="com.offcn.entity.User" parameterType="int">
select * from smbms_user where id =#{id}
</select>
<!--根据id修改名字,返回值的类型可以不需要,第一个userName对应的是数据库的列名,
第二个userName对应的是java的实体bean 建议和数据库里的属性一一对应上-->
<update id="updateById" parameterType="com.offcn.entity.User">
update smbms_user set userName=#{userName},userPassword=#{userPassword} where id=#{id}
</update>
<!--根据id删除信息-->
<delete id="deleteById" parameterType="int">
delete from smbms_user where id=#{id}
</delete>
<!--增加一个对象-->
<insert id="insertUser" parameterType="com.offcn.entity.User">
insert into smbms_user(userName,userPassword) values(#{userName},#{userPassword})
</insert>
<!--resultMap 也相当于亿个结果集,这个id必须对应你的resultMap的值,
type代表你的返回值类型(可以给其别名)
-->
<resultMap id="selectByMapResult" type="User">
<!--id只代表id 标签的实体类的属性名,column 代表的是你的数据库列名
其他的不是id的属性就用result这个名称
mybatis封装的数据库列明,必须跟你的实体bean的属性名相同,
用resultMap的情况的第一种:但你的数据库的列名跟你的属性名不匹配的时候
第二种情况 也就是你的复杂查询(一对一的关系,一对多的关系)-->
<id property="id" column="id"></id>
<result property="userName" column="userName"></result>
<result property="userRole" column="userRole"></result>
</resultMap>
<!--<select id="selectByMap" parameterType="Map" resultMap="selectByMapResult">
select u.*,r.id from smbms_user u,smbms_role r
where userName like concat('%',#{uName},'%') and
userRole=#{uRole} and u.userRole = r.id
</select>-->
<resultMap id="selectByRoleResult" type="User">
<id property="id" column="id"></id>
<result property="userName" column="userName"></result>
<result property="userRole" column="userRole"></result>
<!--在mybatis配置一对一的关系,拿到的是属性名,javaType代表类型,可以给别名-->
<association property="role" javaType="Role">
<id property="id" column="id"></id>
<result property="roleName" column="roleName"></result>
<result property="roleCode" column="roleCode"></result>
</association>
</resultMap>
<select id="selectByRole" parameterType="User" resultMap="selectByRoleResult">
select u.*,r.id,r.roleCode,r.roleName from smbms_user u,smbms_role r
where userName like concat('%',#{userName},'%') and
userRole=#{userRole} and u.userRole = r.id
</select>
<resultMap id="selectByUserNameResult" type="User">
<id property="id" column="id"></id>
<result property="userName" column="userName"></result>
<result property="userRole" column="userRole"></result>
<!--开始配置一对多的关系,这里是ofType(配置一对多的关系里必须加的)也是给的类型-->
<collection property="addressList" ofType="Address">
<id property="id" column="a_id"></id>
<result property="addressDesc" column="addressDesc"></result>
<result property="contact" column="contact"></result>
</collection>
</resultMap>
<select id="selectByUserName" parameterType="String" resultMap="selectByUserNameResult">
select u.*,a.id as_aid,a.addressDesc,a.contact from smbms_user u inner join smbms_address a on
u.id=a.userId and u.userName=#{userName}
</select>
<insert id="addUser" parameterType="User">
insert into smbms_user (userCode,userName,userPassword,gender,birthday)
values(#{userCode},#{userName},#{userPassword},#{gender},#{birthday})
</insert>
<!-- 根据用户角色 与名字来进行模糊查询-->
<!-- <select id="selectByName" resultType="User">
select u.*,r.roleName from smbms_user u,smbms_role r
<where>
<if test="userRole !=null">
and userRole=#{userRole}
</if>
<if test="userName != null and userName !=''">
and userName like concat ('%',#{userName},'%')
</if>
</where>
</select>-->
<!-- 根据用户角色 与名字来进行模糊查询-->
<select id="selectByName" resultType="User">
select u.*,r.roleName from smbms_user u,smbms_role r
/*prefix加前缀,prefixOverrides去除前面不需要的and关键字,suffix 加后缀,
suffixOverrides 去除最后一个不需要的 (比如 逗号 and)*/
<trim prefix="where" prefixOverrides="and" suffix="and u.userRole=r.id">
<if test="userRole !=null">
and userRole=#{userRole}
</if>
<if test="userName != null and userName !=''">
and userName like concat ('%',#{userName},'%')
</if>
</trim>
</select>
<!--修改-->
<update id="updateUser" parameterType="User" >
update smbms_user
/*prefix加上前缀*/
<trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
<if test="userCode !=null">userCode=#{userCode},</if>
<if test="userName !=null">userName=#{userName},</if>
<if test="userPassword !=null">userPassword=#{userPassword},</if>
<if test="gender !=null">gender=#{gender},</if>
<if test="birthday !=null">birthday=#{birthday},</if>
</trim>
</update>
<!--collection 是遍历的类型 item是每一次遍历的条目-->
<select id="selectArrayUser" resultType="User">
select * from smbms_user where userRole in
/*open 以什么开始 separator以什么分割 close以什么结束*/
<foreach collection="array" item="uRole" open="(" separator="," close=")">
/*item里面的值必须跟foreach里一样*/
#{uRole}
</foreach>
</select>
</mapper>
4.创建测试类:
package com.offcn.test;
import com.offcn.dao.AddressMapper;
import com.offcn.dao.UserMapper;
import com.offcn.entity.Address;
import com.offcn.entity.Role;
import com.offcn.entity.User;
import com.offcn.utils.SqlSessionUtils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import javax.jws.soap.SOAPBinding;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Array;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class Test {
@org.junit.Test
public void test(){
//读取核心配置文件
//String path ="mybatis-config.xml";
//得到一个输入流对象
/* InputStream is;*/
{
try {
SqlSession sqlSession = SqlSessionUtils.getSqlSessionUtils().sqlSession;
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Integer[] arr = {2,3};
List<User> list = userMapper.selectArrayUser(arr);
for (User user:list){
System.out.println(user.getUserName());
}
/* User user = new User();
user.setId(1);
user.setUserName("aaaa");
user.setGender(2);
int num = userMapper.updateUser(user);
System.out.println(num);*/
/*User user = new User();
user.setUserCode("noky");
user.setUserName("noky");
user.setUserPassword("85375264");
user.setGender(1);
user.setBirthday(new Date());
int num = userMapper.addUser(user);
System.out.println(num);*/
/* List<User> userList = userMapper.selectByName("孙",null);
for (User user:userList){
System.out.println(user.getUserName()+"\t"+user.getUserRole());*/
/* List<Address> addressList = user.getAddressList();
for (Address address:addressList){
System.out.println(address.getContact()+"\t"+address.getAddressDesc());
}
}*/
/* //如果你用map来进行传参 你所给的键名必须跟你userMapper里面占位符#{uName}的值一样
Map<String,String> map = new HashMap<String, String>();
map.put("uName","孙");
map.put("uRole","3");
List<User> list = userMapper.selectByMap(map);
for (User user:list){
System.out.println(user.getUserName()+"\t"+user.getUserRole());
}*/
/* User user = new User();
user.setUserRole(3);
user.setUserName("孙");
List<User> list = userMapper.selectByRole(user);
for (User user1:list){
System.out.println(user1.getUserName()+"\t"+user1.getUserRole());
Role role = user1.getRole();
System.out.println(role.getRoleName()+"\t"+role.getRoleCode());
}*/
/* List<User> list = sqlSession.getMapper(UserMapper.class).selectAllUser();
for (User user:list){
System.out.println(user.getUserName());
}*/
/* List<Address> list = sqlSession.getMapper(AddressMapper.class).selectLikeAddress("n");
for (Address address:list){
System.out.println(address.getContact());
}*/
/* List<Address> list = sqlSession.getMapper(AddressMapper.class).selectAllAddress();
for (Address address:list){
System.out.println(address.getContact());
}*/
/* Address address = sqlSession.getMapper(AddressMapper.class).selectById(8);
System.out.println(address.getContact());*/
/* int sum = sqlSession.getMapper(AddressMapper.class).deleleAddress(9);
System.out.println(sum);*/
/* Address address = new Address();
address.setContact("黄建斌222");
address.setAddressDesc("浙江省乐清市222");
int num = sqlSession.getMapper(AddressMapper.class).insertAddress(address);
System.out.println(num);*/
/* address.setId(1);
int sum = sqlSession.getMapper(AddressMapper.class).updateAddress(address);
System.out.println(sum);*/
/* List<User> list = sqlSession.getMapper(UserMapper.class).selectAllUser();
for (User user:list){
System.out.println(user.getUserName()+"\t"+user.getUserPassword());
}*/
/*List<User> list = sqlSession.getMapper(UserMapper.class).selectAllUser();
for (User user1:list){
System.out.println(user1.getUserName()+"\t"+user1.getUserPassword());
}*/
/* User user = new User();
user.setUserName("aaaa");
user.setUserPassword("qqqqq");*/
/* int num = sqlSession.delete("com.offcn.dao.UserMapper.deleteById", 16);
System.out.println(num);*/
/*
sqlSession.insert("com.offcn.dao.UserMapper.insertUser",user);*/
/* int num = sqlSession.update("com.offcn.dao.UserMapper.updateById", user);
System.out.println(num);*/
/* List<User> list = sqlSession.selectList("com.offcn.dao.UserMapper.selectLikeUser", "李");
for (User user:list){
System.out.println(user.getUserName()+"\t"+user.getUserRole());
}*/
/* User user = sqlSession.selectOne("com.offcn.dao.UserMapper.selectById","1");
System.out.println(user.getUserName());*/
/* is = Resources.getResourceAsStream(path);
//得到sqlSessionFactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(is);
//通过SqlSessionFactory 得到SqlSession
SqlSession sqlSession = ssf.openSession();*/
//返回的一列,就用selectOne
/* int num = sqlSession.selectOne("com.offcn.dao.UserMapper.selectCount");
System.out.println(num);*/
/* List<User> list = sqlSession.selectList("com.offcn.dao.UserMapper.selectAllUser");
for (User u : list){
System.out.println(u.getUserName()+"\t"+u.getUserPassword());
}*/
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
常见属性: