- Mybatis是一个优秀的持久层框架,它对JDBC操作数据库的过程进行封装,使开发者只需要关注sql本身。
- 我们原来使用JDBC操作数据库,需要手动的写代码去注册驱动、获取connection、获取statement等等,现在Mybaits帮助我们把这些事情做了,我们只需要关注我们的业务sql即可,这样可以提高我们的开发效率。
一、基本配置
在线文档:https://mybatis.org/mybatis-3/zh/index.html
导入jar包方式
1、下载: https://github.com/mybatis/mybatis-3/releases
2、将mybatis-3.4.3.jar和lib下的jar包导入项目
3、创建 mybatis-config.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">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/jrbac?characterEncoding=utf-8&useSSL=false"/>
<property name="username" value="jrbac"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!-- 将我们写好的Sql映射文件,一定要注册到全局配置文件中。-->
<mappers>
<mapper resource="com/mapper/adminMapper.xml" />
</mappers>
</configuration>
4、创建Sql映射文件、实体对象Admin,如:adminMapper.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="com.admin">
<!--
namespace, 名称空间。
id, 唯一标识,同Spring的Bean标签id一样。
resultType: 返回值类型。我这里期望他给我返回一个员工对象。
-->
<select id="selectAdmin" resultType="com.entity.Admin">
select * from j_admin
</select>
</mapper>
5、设置sql输出日志
<settings>
<!--标准的日志工厂实现-->
<setting name="logImpl" value="STDOUT_LOGGING" />
</settings>
maven方式
二、SQL操作示例
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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 com.entity.Admin;
public class Mybatis {
public Mybatis() throws IOException {
String resource = "mybatis-config.xml";
InputStream resourceAsStream = Resources.getResourceAsStream(resource);
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = build.openSession();
List<Admin> list = sqlSession.selectList("selectAdmin");
for(Admin i : list){
System.out.println(i);
}
sqlSession.close();
resourceAsStream.close();
}
}
实体类如下:
package com.entity;
public class Test {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Test [id=" + id + ", name=" + name + "]";
}
}
package com.entity;
public class Admin {
private int id;
private String username;
private String password;
private String salt;
private String last_login_entered;
private String login_entered;
private String date_entered;
private String login_ip;
private String role_id;
private Role role;
private int status;
private int times;
public int getId() {
return id;
}
public void setId(int 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 getSalt() {
return salt;
}
public void setSalt(String salt) {
this.salt = salt;
}
public String getLast_login_entered() {
return last_login_entered;
}
public void setLast_login_entered(String lastLoginEntered) {
last_login_entered = lastLoginEntered;
}
public String getLogin_entered() {
return login_entered;
}
public void setLogin_entered(String loginEntered) {
login_entered = loginEntered;
}
public String getDate_entered() {
return date_entered;
}
public void setDate_entered(String dateEntered) {
date_entered = dateEntered;
}
public String getLogin_ip() {
return login_ip;
}
public void setLogin_ip(String loginIp) {
login_ip = loginIp;
}
public String getRole_id() {
return role_id;
}
public void setRole_id(String roleId) {
role_id = roleId;
}
public Role getRole() {
return role;
}
public void setRole(Role role) {
this.role = role;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
public int getTimes() {
return times;
}
public void setTimes(int times) {
this.times = times;
}
@Override
public String toString() {
return "Admin [date_entered=" + date_entered + ", id=" + id
+ ", last_login_entered=" + last_login_entered
+ ", login_entered=" + login_entered + ", login_ip=" + login_ip
+ ", password=" + password + ", role=" + role + ", role_id="
+ role_id + ", salt=" + salt + ", status=" + status
+ ", times=" + times + ", username=" + username + "]";
}
}
package com.entity;
public class Role {
private int id;
private String name;
private int is_delete;
private String note;
private String date_entered;
private String update_entered;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getIs_delete() {
return is_delete;
}
public void setIs_delete(int isDelete) {
is_delete = isDelete;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
public String getDate_entered() {
return date_entered;
}
public void setDate_entered(String dateEntered) {
date_entered = dateEntered;
}
public String getUpdate_entered() {
return update_entered;
}
public void setUpdate_entered(String updateEntered) {
update_entered = updateEntered;
}
@Override
public String toString() {
return "Role [date_entered=" + date_entered
+ ", id=" + id + ", is_delete=" + is_delete + ", name=" + name + ", note=" + note
+ ", update_entered=" + update_entered + "]";
}
}
testMapper.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="com.entity.Test">
<select id="selectTest" resultType="com.entity.Test">
select * from j_test
</select>
<insert id="addTest" useGeneratedKeys="true" keyProperty="id">
insert into j_test (name) values (#{name});
</insert>
<delete id="delTest" parameterType="Integer">
delete from j_test where id=#{id}
</delete>
<update id="updateTest">
update j_test set name=#{name} where id=#{id}
</update>
</mapper>
adminMapper.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="com.admin">
<!--
namespace, 名称空间。
id, 唯一标识,同Spring的Bean标签id一样。
resultType: 返回值类型。我这里期望他给我返回一个员工对象。
-->
<resultMap type="com.entity.Admin" id="AdminRoleResultMap">
<id property="id" column="id" />
<result property="username" column="username"/>
<result property="password" column="password"/>
<result property="date_entered" column="date_entered"/>
<association property="role" javaType="com.entity.Role">
<id property="id" column="role_id"/>
<result property="name" column="name"/>
<result property="date_entered" column="date_entered"/>
</association>
</resultMap>
<select id="selectAdmin" resultMap="AdminRoleResultMap">
select a.*,r.name from j_admin a left join j_role r on r.id=a.role_id
</select>
<select id="selectAdminPage" resultType="com.entity.Admin">
select * from j_admin
<if test="where!=''">
where 1=1 and id=#{where}
</if>
limit #{offset},#{pagesize};
</select>
<select id="getList" resultType="com.entity.Admin">
select * from j_admin where 1=1
<if test="search!=''">
and username like #{search}
</if>
<if test="orderBy!=''">
order by id desc
</if>
limit #{limit}, #{offset}
</select>
<select id="getListCount" resultType="java.lang.Integer">
select count(*) from j_admin where 1=1
<if test="search!=''">
and search like #{search}
</if>
</select>
</mapper>
运行代码:
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.entity.Admin;
import com.entity.Test;
import com.utils.MyBatisUntil;
public class test {
/**
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
SqlSession sqlSession = MyBatisUntil.getSqlSession(false);
//增
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("name", "test");
Test test = new Test();
test.setName("test");
System.out.println(sqlSession.insert("addTest", test)); //成功返回1,失败返回0
map.clear();
//删
System.out.println(sqlSession.delete("delTest", 2)); //成功返回1,失败返回0
//改
map.put("id", 3);
map.put("name", "yy");
System.out.println(sqlSession.delete("updateTest", map)); //成功返回1,失败返回0
//查
List<Test> list = sqlSession.selectList("selectTest");
for(Test a : list){
System.out.println(a);
}
List<Admin> list1 = sqlSession.selectList("selectAdmin");
for(Admin a : list1){
System.out.println(a);
}
map.clear();
map.put("where", "");
map.put("offset", 0);
map.put("pagesize", 2);
List<Admin> list2 = sqlSession.selectList("selectAdminPage", map);
for(Admin a : list2){
System.out.println(a);
}
map.clear();
map.put("search", "");
map.put("orderBy", "id");
map.put("sortOrder", "desc");
map.put("limit", 0);
map.put("offset", 10);
List<Admin> list3 = sqlSession.selectList("getList", map);
for(Admin a : list3){
System.out.println(a);
}
System.out.println(sqlSession.selectOne("getListCount",map));
}
}
resultMap详细讲解:
<resultMap type="映射的Admin对象,带上包名" id="唯一的标识">
<id property="表的主键字段,或者可以为查询语句中的别名字段" jdbcType="字段类型" property="映射Admin对象的主键属性"/>
<result jdbcType="字段类型" property="映射到Admin对象的一个属性(须为type定义的Admin对象中的一个属性)" column="表的一个字段(可以为任意表的一个字段)"/>
<association property="Admin的一个对象属性" javaType="com.entity.Role">
<id property="关联Admin对象的主键属性" column="关联Admin对象的主键字段"/>
<result property="role的属性" column="任意表的字段"/>
</association>
</resultMap>
Mybatis执行原生jdbc_sql语句
Connection conn = MyBatisUntil.getSqlSession(true).getConnection();
PreparedStatement pst = conn.prepareStatement("Optimize TABLE " + table);
pst.execute();
#{}与${}的区别
#{}是占位符,相当于jdbc的PreparedStatement,可以防sql注入
${}是字符串替换,有sql注入风险,可以用于sql语句拼接,如:order by ${order}
三、mybatis工具类
package com.utils;
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 java.io.IOException;
import java.io.InputStream;
/**
* @author 25754
*/
public class MyBatisUntil {
//封装会话工厂
private static SqlSessionFactory Factory;
/**在进行对象的跨层传递的时候,使用ThreadLocal可以避免多次传递,打破层次间的约束
*/
private static ThreadLocal<SqlSession> local = new ThreadLocal<SqlSession>();
static {
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
Factory = sqlSessionFactoryBuilder.build(is);
} catch (
IOException e)
{
e.printStackTrace();
}
}
//封装factory方法
public static SqlSessionFactory getFactory(){
return Factory;
}
//封装sqlSession会话
public static SqlSession getSqlSession(boolean IsAutoComiit) {
SqlSession sqlSession = local.get();
if (sqlSession == null) {
sqlSession = Factory.openSession(IsAutoComiit);
local.set(sqlSession);
}
return sqlSession;
}
//使用泛型封装getMapper
public static <T extends Object> T getMapper(Class<T> c) {
SqlSession sqlSession = getSqlSession(true);
return sqlSession.getMapper(c);
}
}