Mybatis简单使用
Mybatis的简单了解
一个持久层框架 定制化SQL 高级映射 符合ORM框架,里面封装了JDBC几乎多有的代码包括数据库连接池 以及事务,可以通过简单的XML文件以及注解来进行配置 POJO对象数据库里的一些对象。
使用步骤:
一、创建maven项目将需要的jar包导入
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.16</version>
</dependency>
二、创建实体类(pojo包下)
User类
public class User {
private String id;
private String username;
private String password;
private String iconURL;
private int roleId;
private Role role;
private List<Menu> menus;
private List<Funs> funs;
public User() { }
public User(String id, String username, String password,
String iconURL, Role role, List<Menu> menus,
List<Funs> funs) {
this.id = id;
this.username = username;
this.password = password;
this.iconURL = iconURL;
this.role = role;
this.menus = menus;
this.funs = funs;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getIconURL() {
return iconURL;
}
public void setIconURL(String iconURL) {
this.iconURL = iconURL;
}
public Role getRole() {
return role;
}
public void setRole(Role role) {
this.role = role;
}
public List<Menu> getMenus() {
return menus;
}
public void setMenus(List<Menu> menus) {
this.menus = menus;
}
public List<Funs> getFuns() {
return funs;
}
public void setFuns(List<Funs> funs) {
this.funs = funs;
}
public int getRoleId() {
return roleId;
}
public void setRoleId(int roleId) {
this.roleId = roleId;
}
@Override
public String toString() {
return "User{" +
"id='" + id + '\'' +
", username='" + username + '\'' +
", password='" + password + '\'' +
", iconURL='" + iconURL + '\'' +
", roleId=" + roleId +
", role=" + role +
", menus=" + menus +
", funs=" + funs +
'}';
}
}
Role类
public class Role {
private int roleId;
private String roleName;
public int getRoleId() {
return roleId;
}
public void setRoleId(int roleId) {
this.roleId = roleId;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
@Override
public String toString() {
return "Role{" +
"roleId=" + roleId +
", roleName='" + roleName + '\'' +
'}';
}
public Role() {
}
public Role(int roleId, String roleName) {
this.roleId = roleId;
this.roleName = roleName;
}
}
Menu类
public class Menu {
private int menuId;
private String menuName;
private String menuURL;
private int fatherId;
public Menu() { }
public Menu(int menuId, String menuName, String menuURL,
int fatherId) {
this.menuId = menuId;
this.menuName = menuName;
this.menuURL = menuURL;
this.fatherId = fatherId;
}
public int getMenuId() {
return menuId;
}
public void setMenuId(int menuId) {
this.menuId = menuId;
}
public String getMenuName() {
return menuName;
}
public void setMenuName(String menuName) {
this.menuName = menuName;
}
public String getMenuURL() {
return menuURL;
}
public void setMenuURL(String menuURL) {
this.menuURL = menuURL;
}
public int getFatherId() {
return fatherId;
}
public void setFatherId(int fatherId) {
this.fatherId = fatherId;
}
@Override
public String toString() {
return "Menu{" +
"menuId=" + menuId +
", menuName='" + menuName + '\'' +
", menuURL='" + menuURL + '\'' +
", fatherId=" + fatherId +
'}';
}
}
RoleMenu类
public class RoleMenu {
private Role role;
private Menu menu;
public RoleMenu(Role role, Menu menu) {
this.role = role;
this.menu = menu;
}
public RoleMenu() {}
public Role getRole() {
return role;
}
public void setRole(Role role) {
this.role = role;
}
public Menu getMenu() {
return menu;
}
public void setMenu(Menu menu) {
this.menu = menu;
}
@Override
public String toString() {
return "RoleMenu{" +
"role=" + role +
", menu=" + menu +
'}';
}
}
Funs类
public class Funs {
private int funId;
private String funName;
private String funURL;
private int menuId;
private Menu menu;
public int getFunId() {
return funId;
}
public void setFunId(int funId) {
this.funId = funId;
}
public String getFunName() {
return funName;
}
public void setFunName(String funName) {
this.funName = funName;
}
public String getFunURL() {
return funURL;
}
public void setFunURL(String funURL) {
this.funURL = funURL;
}
public Menu getMenu() {
return menu;
}
public void setMenu(Menu menu) {
this.menu = menu;
}
@Override
public String toString() {
return "Funs{" +
"funId=" + funId +
", funName='" + funName + '\'' +
", funURL='" + funURL + '\'' +
", menu=" + menu +
'}';
}
public Funs(int funId, String funName, String funURL, Menu menu) {
this.funId = funId;
this.funName = funName;
this.funURL = funURL;
this.menu = menu;
}
public Funs(){
}
public int getMenuId() {
return menuId;
}
public void setMenuId(int menuId) {
this.menuId = menuId;
}
}
三、编写需要的配置文件
配置数据库的需要用到的属性文件,db.properties,前四个必写,注意赋值符右侧值的末尾不能有空格,可能导致读取出错。
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/数据库名?
jdbc.username=数据库用户名
jdbc.password=自己数据库的密码
initialSize=10
maxActive=50
maxIdle=20
minIdle=5
maxWait=60000
connectionProperties=useUnicode=true;characterEncoding=UTF8
defaultAutoCommit=true
defaultReadOnly=
defaultTransactionIsolation=READ_UNCOMMITTED
mybatis-config.xml,数据库的映射配置文件。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://www.mybatis.org/dtd/mybatis-3-config.dtd">
<!-- 根标签 -->
<configuration>
<!-- 加载属性文件 -->
<properties resource="db.properties">
<property name="username" value="dev_user" />
<property name="password" value="abc" />
</properties>
<!--环境配置,连接的数据库,这里使用的是MySQL-->
<environments default="development">
<environment id="development">
<!--指定事务管理的类型,这里简单使用Java的JDBC的提交和回滚设置-->
<transactionManager type="JDBC"></transactionManager>
<!--dataSource 指连接源配置,POOLED是JDBC连接对象的数据源连接池的实现-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"></property>
<property name="url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</dataSource>
</environment>
</environments>
<mappers>
<!--这是告诉Mybatis去哪找持久化类的映射文件,对于在src下的文件直接写文件名,
如果在某包下,则要写明路径,如:com/mybatistest/config/User.xml-->
<!--<mapper resource="com/xxx/mapper/UserMapper.xml"></mapper>-->
<mapper resource="UserMapper.xml"></mapper>
</mappers>
</configuration>
configuration标签里还可能用到的标签
<!-- 全局配置参数,需要时再设置 -->
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<!-- 别名定义 -->
<typeAliases>
<typeAlias type="类全名" alias="别名"/>
<package name="包名"/>
</typeAliases>
四、写映射接口与映射文件
UserMapper接口
public interface UserMapper {
//只查询所有用户的id,username
List<User> queryAll();
//通过用户名将一个用户全部信息查出来,使用多表查询
List<User> queryByName(String name);
//查询所有用户完整信息,不使用多表查询
List<User> queryAny();
//通过模糊查询将所有匹配到的用户信息都查出来,未使用多表查询
List<User> queryAnyByName(String username);
//插入用户
int insertUser(User user);
//查询一个用户的完整信息,
User queryWholeUser(String username);
//根据id去删除一条记录
int deleteById(String id);
int UpdateById(User user);
}
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">
<!--为这个mapper指定一个唯一的namespace,
它习惯上设置为:“包名+sql映射文件名”,这样可以保值名的唯一。-->
<mapper namespace="com.xxx.mapper.UserMapper">
<!--先写要查询的普通值,再写要查的对象-->
<resultMap id="UserResultMap" type="com.xxx.pojo.User">
<id property="id" column="id" />
<result property="username" column="username" />
<result property="password" column="password" />
<result property="iconURL" column="iconURL" />
<result property="roleId" column="roleId" />
<!--
一对一,多对一用association,一对多用collection。在映射中的体现是主要在
association标签括起来的地方写的是“对”字前面的“一”或“多”的那张表的信息
collection标签括起来的地方写的是“对”字后面的“多”的那张表的信息
-->
<association property="role" javaType="com.xxx.pojo.Role">
<!--
property是类映射到列的字段,此处因为上面type指明了是com.xxx.pojo.User
所以只需要写user类中的字段名就行。Column是数据库中表的列名
-->
<id property="roleId" column="roleId" />
<result property="roleName" column="roleName" />
</association>
<!--ofType 是指property属于哪个类-->
<collection property="menus" ofType="com.xxx.pojo.Menu">
<id property="menuId" column="menuId" />
<result property="menuName" column="menuName" />
<result property="menuURL" column="menuURL" />
<result property="fatherId" column="fatherId" />
</collection>
<collection property="funs" ofType="com.xxx.pojo.Funs">
<id property="funId" column="funId"/>
<result property="funName" column="funName"/>
<result property="funURL" column="funURL"/>
<result property="menuId" column="menuId"/>
</collection>
</resultMap>
<!--id:这个select语句的id
parameterType:指定查询是传入的参数类型
resultType:即返回结果集的类型,就是方法返回值包含的类型,如List<user>就写user-->
<select id="queryByName" parameterType="String" resultMap="UserResultMap">
select * from user left join role on user.id =role.roleId
left join role_menu on role.roleId = role_menu.roleId
left join menu on menu.menuId = role_menu.menuId
left join funs on funs.menuId = menu.menuId
where user.username = #{username}
</select>
<select id="queryAll" resultType="com.xxx.pojo.User">
select username,id from user
</select>
<select id="queryWholeUser" resultType="com.xxx.pojo.User" parameterType="String">
select * from user where username = #{username}
</select>
<insert id="insertUser" parameterType="com.xxx.pojo.User" useGeneratedKeys="true" >
insert into user values (#{id},#{username},#{password},#{roleId},#{iconURL})
</insert>
<delete id="deleteById" parameterType="String">
delete from user where id = #{id}
</delete>
<select id="queryAny" resultType="com.xxx.pojo.User">
select * from user
</select>
<select id="queryAnyByName" resultType="com.xxx.pojo.User">
select * from user where username like concat("%",#{username},"%")
</select>
</mapper>
五、测试
public class Test {
public static void main(String[] args) throws IOException {
//读取配置,初始化mybatis,获取对象
SqlSessionFactory ssf =
new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
SqlSession ss = ssf.openSession();
UserMapper mapper = ss.getMapper(UserMapper.class);
List<User> list = mapper.queryByName("zhangsan");
System.out.println(list.toString());
}
}
附:
数据库表
user
role_menu
menu
role
funs