MybatisUtil工具类
在实际开发中，我们可以编写一个MybatisUtil辅助类来进行对进行操作。
1）在静态初始化块中加载mybatis配置文件和StudentMapper.xml文件一次
2）使用ThreadLocal对象让当前线程与SqlSession对象绑定在一起
3）获取当前线程中的SqlSession对象，如果没有的话，从SqlSessionFactory对象中获取SqlSession对象
4）获取当前线程中的SqlSession对象，再将其关闭，释放其占用的资源
/**
* MyBatis工具类
* @author AdminTC
*/
public class MyBatisUtil {
private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
private static SqlSessionFactory sqlSessionFactory;
static{
try {
Reader reader = Resources.getResourceAsReader("mybatis.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
private MyBatisUtil(){}
public static SqlSession getSqlSession(){
SqlSession sqlSession = threadLocal.get();
if(sqlSession == null){
sqlSession = sqlSessionFactory.openSession();
threadLocal.set(sqlSession);
}
return sqlSession;
}
public static void closeSqlSession(){
SqlSession sqlSession = threadLocal.get();
if(sqlSession != null){
sqlSession.close();
threadLocal.remove();
}
}
public static void main(String[] args) {
Connection conn = MyBatisUtil.getSqlSession().getConnection();
System.out.println(conn!=null?"连接成功":"连接失败");
}
}MybatisUtil.java
动态SQL
什么是动态SQL，如下图，当你不知道用户会选择多少个筛选条件的时候，你只有等待用户选择而动态地选择SQL查询条件。
动态SQL-选择
加入IUserDao接口，注意因为与数据库互动需要，Dao接口一般要以类作为参数。
package com.harry.dao;
import java.sql.SQLException;
import java.util.List;
import java.util.Set;
import com.harry.entity.User;
public interface IUserDao {
public boolean doCreate(User entity) throws Exception;
public boolean doUpdate(User entity) throws Exception;
public boolean doRemove(Set<Integer> ids)throws Exception;
public User findById(Integer id)throws Exception;
public List<User> findAll() throws Exception;
public List<User> findAllSplite(String column, String keyword, Integer currentPage, Integer lineSize) throws Exception;
public Integer getAllCount(String column, String keyword) throws Exception;
}IUserDao
书写UserDaoImpl,并在其后添加动态查询的查询方法dynaSQLwithSelect。
package com.harry.dao.impl;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.ibatis.session.SqlSession;
import com.harry.dao.IUserDao;
import com.harry.entity.User;
import com.harry.util.MybatisUtil;
public class UserDaoImpl implements IUserDao {
@Override
public boolean doCreate(User entity) throws Exception {
// TODO Auto-generated method stub
return false;
}
@Override
public boolean doUpdate(User entity) throws Exception {
// TODO Auto-generated method stub
return false;
}
@Override
public boolean doRemove(Set<Integer> ids) throws Exception {
// TODO Auto-generated method stub
return false;
}
@Override
public User findById(Integer id) throws Exception {
// TODO Auto-generated method stub
return null;
}
@Override
public List<User> findAll() throws Exception {
// TODO Auto-generated method stub
return null;
}
@Override
public List<User> findAllSplite(String column, String keyword, Integer currentPage, Integer lineSize)
throws Exception {
// TODO Auto-generated method stub
return null;
}
@Override
public Integer getAllCount(String column, String keyword) throws Exception {
// TODO Auto-generated method stub
return null;
}
public List<User> dynaSQLwithSelect(String uname,Character usex) throws Exception{
SqlSession sqlSession = MybatisUtil.getSqlSession();
try{
Map<String,Object> map = new LinkedHashMap<String, Object>();
map.put("uname",uname);
map.put("usex", usex);
return sqlSession.selectList("dynaSQLwithSelect",map);
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
throw e;
}finally{
sqlSession.commit();
MybatisUtil.closeSqlSession();
}
}
}UserDaoImpl
在User.xml中配置相应的SQL方法映射。
<!-- map为调用该方法的外界传入 -->
<select id="dynaSQLwithSelect" parameterType="map" resultType="com.harry.entity.User">
select id,username,sex from user
<where>
<!-- 如果map中uname不为null，则在where后添加username = uname； -->
<if test="uname!=null">
and username=#{uname}
</if>
<!-- 如果map中usex不为null，则在where语句后添加 and sex = usex； -->
<if test="usex!=null">
and sex=#{usex}
</if>
</where>
</select>User.xml
测试方法
@Test
public void testdynaSQLwithSelect() throws Exception {
UserDaoImpl userDao = new UserDaoImpl();
List<User> list = userDao.dynaSQLwithSelect("张飞", null);
Iterator<User> iterator = list.iterator();
while(iterator.hasNext()){
System.out.println(iterator.next());
}
}MybatisTest
动态SQL增删改查基本相同，重点是Mybatis通过在配置文件中书写<where>语句来避免数据库SQL拼接。
动态SQL-更新
public boolean dynaSQLwithUpdate(Integer uid, String uname, String usex) throws Exception{
//session应该在事务层进行开关，这里为了方便
SqlSession sqlSession = MybatisUtil.getSqlSession();
try{
Map<String,Object> map = new LinkedHashMap<>();
map.put("uid", uid);
map.put("uname", uname);
map.put("usex", usex);
sqlSession.update("dynaSQLwithUpdate",map);
return true;
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
throw e;
}finally{
sqlSession.commit();
MybatisUtil.closeSqlSession();
}
}UserDaoImpl
<update id="dynaSQLwithUpdate" parameterType="map">
UPDATE user
<set>
<if test="uname!=null">
username=#{user.username},
</if>
<if test="usex!=null">
sex=#{usex},
</if>
</set>
where id=#{uid}
</update> User.xml
动态SQL-删除
public boolean dynaSQLwithDelete(Integer... ids) throws Exception{
//session应该在事务层进行开关，这里为了方便
SqlSession sqlSession = MybatisUtil.getSqlSession();
try{
Map<String, Object> map = new LinkedHashMap<>();
map.put("ids", ids);
sqlSession.delete("dynaSQLwithDelete",map);
return true;
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
throw e;
}finally{
sqlSession.commit();
MybatisUtil.closeSqlSession();
}
}UserDaoImpl
<delete id="dynaSQLwithDelete" parameterType="map">
DELETE FROM user WHERE id IN
<!-- foreach 用来迭代数组元素 -->
<!-- open表示开始符号 -->
<!-- close表示结束符号 -->
<!-- separator表示分隔符 -->
<!-- item表示迭代的数组 -->
<foreach collection="ids" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>User.xml
动态SQL- 插入
public boolean dynaSQLwithInsert(User... users) throws Exception{
//session应该在事务层进行开关，这里为了方便
SqlSession sqlSession = MybatisUtil.getSqlSession();
try{
Map<String, Object> map = new LinkedHashMap<>();
map.put("users", users);
sqlSession.insert("dynaSQLwithInsert",map);
return true;
}catch(Exception e){
e.printStackTrace();
sqlSession.rollback();
throw e;
}finally{
sqlSession.commit();
MybatisUtil.closeSqlSession();
}
}UserDaoImpl
<insert id="dynaSQLwithInsert" parameterType="map" useGeneratedKeys="true" keyProperty="id">
INSERT INTO user (username, birthday, sex, address) VALUES
<foreach collection="users" item="user" separator=",">
(#{user.username},#{user.birthday},#{user.sex},#{user.address})
</foreach>
</insert>
</mapper>User.xml