Java 之mybatis简单使用（增删改查与两表关联查询）、配置与工具类封装
- 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); } }
