Java SqlSession类代码示例

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
public static boolean deleteUserGroupById(String userGroupId) {
	SqlSession sqlSession = null;
	try {
		sqlSession = MybatisManager.getSqlSession();
		UserGroupMapper userGroupMapper = sqlSession.getMapper(UserGroupMapper.class);
		int result = userGroupMapper.deleteByPrimaryKey(userGroupId);
		if (result == 0) {
			MybatisManager.log.warn("删除用户组异常");
			return false;
		}
		sqlSession.commit();
	} catch (Exception e) {
		if (sqlSession != null) {
			sqlSession.rollback();
		}
		MybatisManager.log.error("删除用户组异常", e);
		return false;
	} finally {
		if (sqlSession != null) {
			sqlSession.close();
		}
	}
	return true;
}
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
/**
 * 测试 selectOneByExample
 */
@Test
public void testSelectOneByExample() throws IOException, XMLParserException, InvalidConfigurationException, InterruptedException, SQLException {
    MyBatisGeneratorTool tool = MyBatisGeneratorTool.create("scripts/SelectOneByExamplePlugin/mybatis-generator.xml");
    tool.generate(new AbstractShellCallback() {
        @Override
        public void reloadProject(SqlSession sqlSession, ClassLoader loader, String packagz) throws Exception {
            ObjectUtil tbMapper = new ObjectUtil(sqlSession.getMapper(loader.loadClass(packagz + ".TbMapper")));

            ObjectUtil TbExample = new ObjectUtil(loader, packagz + ".TbExample");
            ObjectUtil criteria = new ObjectUtil(TbExample.invoke("createCriteria"));
            criteria.invoke("andIdEqualTo", 1l);

            // sql
            String sql = SqlHelper.getFormatMapperSql(tbMapper.getObject(), "selectOneByExample", TbExample.getObject());
            Assert.assertEquals(sql, "select id, field1, field2 from tb WHERE (  id = '1' )  limit 1");
            Object result = tbMapper.invoke("selectOneByExample", TbExample.getObject());
            ObjectUtil Tb = new ObjectUtil(result);
            Assert.assertEquals(Tb.get("id"), 1l);
            Assert.assertEquals(Tb.get("field1"), "fd1");
            Assert.assertNull(Tb.get("field2"));
        }
    });
}
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
@Override
public Channel getChannel(int id) {

    try (SqlSession sqlSession = dbManager.getSqlSession()) {

        ChannelMapper mapper = sqlSession.getMapper(ChannelMapper.class);
        ChannelEntity channelEntity = mapper.selectByPrimaryKey(id);
        if (channelEntity == null) {

            return null;
        }

        Channel channel = DozerMapperUtil.map(channelEntity, Channel.class);
        channel.setLink("/channel/".concat(channel.getSlug()));
        channel.setColor("#".concat(Integer.toHexString(channel.getMainColor())));

        return channel;
    }
}
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
/**
     * 主要测试删除
     */
//    @Test
    public void testDelete() {
        SqlSession sqlSession = MybatisHelper.getSqlSession();
        try {
            UserInfoMapMapper mapper = sqlSession.getMapper(UserInfoMapMapper.class);
            //查询总数
            Assert.assertEquals(5, mapper.selectCount(new UserInfoMap()));
            //查询100
            UserInfoMap userInfoMap = mapper.selectByPrimaryKey(1);

            //根据主键删除
            Assert.assertEquals(1, mapper.deleteByPrimaryKey(1));

            //查询总数
            Assert.assertEquals(4, mapper.selectCount(new UserInfoMap()));
            //插入
            Assert.assertEquals(1, mapper.insert(userInfoMap));
        } finally {
            sqlSession.close();
        }
    }
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
@Override
    public PropertyBean getNewProperty() {
        SqlSession sqlSession = MybatisSqlSession.getSqlSession();
        PropertyBean propertyBean = new PropertyBean();
        try {
            PropertyDao propertyDao = sqlSession.getMapper(PropertyDao.class);
            propertyBean = propertyDao.getNewProperty();
        } catch (Exception e) {
//            e.printStackTrace();
//            logger.error(e.getStackTrace());
        } finally {
            sqlSession.close();
        }

        return propertyBean;
    }
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
/**
     * 根据主键全更新
     */
//    @Test
    public void testUpdateByPrimaryKey() {
        SqlSession sqlSession = MybatisHelper.getSqlSession();
        try {
            UserInfoMapMapper mapper = sqlSession.getMapper(UserInfoMapMapper.class);
            UserInfoMap userInfoMap = mapper.selectByPrimaryKey(2);
            Assert.assertNotNull(userInfoMap);
            userInfoMap.setUserType(null);
            userInfoMap.setRealName("liuzh");
            //不会更新user_type
            Assert.assertEquals(1, mapper.updateByPrimaryKey(userInfoMap));

            userInfoMap = mapper.selectByPrimaryKey(userInfoMap);
            Assert.assertNull(userInfoMap.getUserType());
            Assert.assertEquals("liuzh",userInfoMap.getRealName());
        } finally {
            sqlSession.close();
        }
    }
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
/**
 * 继承类可以使用,但多出来的属性无效
 */
@Test
public void testDynamicSelectNotFoundKeyProperties() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        //根据主键删除
        Assert.assertEquals(183, mapper.select(new Key()).size());

        Key key = new Key();
        key.setCountrycode("CN");
        key.setCountrytel("+86");
        Assert.assertEquals(1, mapper.select(key).size());
    } finally {
        sqlSession.close();
    }
}
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
@Test
public void testDeleteByExample2() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        Example example = new Example(Country.class);
        example.createCriteria().andLike("countryname", "A%");
        example.or().andGreaterThan("id", 100);
        example.setDistinct(true);
        int count = mapper.deleteByExample(example);
        //查询总数
        Assert.assertEquals(true, count > 83);
    } finally {
        sqlSession.rollback();
        sqlSession.close();
    }
}
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
private CMSEvent getRelease(CMSEventRecord record) {
    CMSEvent event;

    try (SqlSession session = sqlsf.openSession()) {
        DJMapper djMapper = session.getMapper(DJMapper.class);
        CmsRelease release = djMapper.getReleaseById(record.getSourcePk());
        if (release != null) {
            long rfcCount = djMapper.countCiRfcByReleaseId(release.getReleaseId());
            release.setCiRfcCount(rfcCount);
        }
        event = new CMSEvent();
        event.setEventId(record.getEventId());
        event.addHeaders("source", "release");
        event.addHeaders("clazzName", "Release");
        event.addHeaders("action", record.getEventType());
        event.addHeaders("sourceId", String.valueOf(record.getSourcePk()));
        event.setPayload(release);
    }
    return event;
}
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
/**
 * 测试关联生成的方法和常量
 */
@Test
public void testOtherMethods() throws IOException, XMLParserException, InvalidConfigurationException, InterruptedException, SQLException {
    MyBatisGeneratorTool tool = MyBatisGeneratorTool.create("scripts/LogicalDeletePlugin/mybatis-generator.xml");
    tool.generate(new AbstractShellCallback() {
        @Override
        public void reloadProject(SqlSession sqlSession, ClassLoader loader, String packagz) throws Exception{
            ObjectUtil tbMapper = new ObjectUtil(sqlSession.getMapper(loader.loadClass(packagz + ".TbMapper")));

            ObjectUtil tbExample = new ObjectUtil(loader, packagz + ".TbExample");
            ObjectUtil criteria = new ObjectUtil(tbExample.invoke("createCriteria"));
            criteria.invoke("andDeleted", true);
            criteria.invoke("andIdEqualTo", 3l);


            // 验证sql
            String sql = SqlHelper.getFormatMapperSql(tbMapper.getObject(), "selectByExample", tbExample.getObject());
            Assert.assertEquals(sql, "select id, del_flag, and_logical_deleted, ts_1, ts_3, ts_4 from tb WHERE (  del_flag = '1' and id = '3' )");
            // 验证执行
            Object result = tbMapper.invoke("selectByExample", tbExample.getObject());
            Assert.assertEquals(((List)result).size(), 1);
        }
    });
}
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
/**
 * 主要测试删除
 */
@Test
public void testDynamicDelete() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        UserLoginMapper mapper = sqlSession.getMapper(UserLoginMapper.class);
        //查询总数
        Assert.assertEquals(10, mapper.selectCount(new UserLogin()));
        UserLogin userLogin = new UserLogin();
        userLogin.setUsername("test1");
        List<UserLogin> userLoginList = mapper.select(userLogin);
        //批量删除
        Assert.assertEquals(userLoginList.size(), mapper.delete(userLogin));
        //循环插入
        for (int i = 0; i < userLoginList.size(); i++) {
            Assert.assertEquals(1, mapper.insert(userLoginList.get(i)));
            Assert.assertEquals(i + 1, (int) userLoginList.get(i).getLogid());
        }
        //查询总数
        Assert.assertEquals(10, mapper.selectCount(new UserLogin()));
    } finally {
        sqlSession.close();
    }
}
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
/**
 * 根据查询条件进行查询
 */
@Test
public void testDynamicSelect() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryTMapper mapper = sqlSession.getMapper(CountryTMapper.class);
        CountryT country = new CountryT();
        country.setId(174);
        country.setCountrycode("US");
        List<CountryT> countryList = mapper.select(country);

        Assert.assertEquals(1, countryList.size());
        Assert.assertEquals(true, countryList.get(0).getId() == 174);
        Assert.assertNotNull(countryList.get(0).getCountryname());
        Assert.assertNull(countryList.get(0).getCountrycode());
    } finally {
        sqlSession.close();
    }
}
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
/**
 * 循环标签 测试
 */
public static void main(String[] args) {

    // 加载配置文件
    InputStream in = CircularLabelsTest.class.getClassLoader().getResourceAsStream("mysql-config.xml");
    MybatisSessionFactoryBuilder mf = new MybatisSessionFactoryBuilder();
    SqlSessionFactory sessionFactory = mf.build(in);
    SqlSession session = sessionFactory.openSession();
    UserMapper userMapper = session.getMapper(UserMapper.class);
    Page<User> page = new Page<>(1, 6);
    List<User> users = userMapper.forSelect(page, Arrays.asList("1", "2", "3"));
    System.out.println(users.toString());
    System.out.println(page);
    User user = new User();
    user.setId(1L);
    User users1 = userMapper.selectOne(user);
    System.out.println(users1);
    TestMapper mapper = session.getMapper(TestMapper.class);
    Test test = new Test();
    test.setCreateTime(new Date());
    test.setType("11111");
    mapper.insert(test);
    session.rollback();
}
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
/**
  * <p>
  * 事务测试
  * </p>
  */
 public static void main(String[] args) {
     /*
      * 加载配置文件
*/
     InputStream in = TransactionalTest.class.getClassLoader().getResourceAsStream("mysql-config.xml");
     MybatisSessionFactoryBuilder mf = new MybatisSessionFactoryBuilder();
     SqlSessionFactory sessionFactory = mf.build(in);
     SqlSession sqlSession = sessionFactory.openSession();
     /**
      * 插入
      */
     UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
     int rlt = userMapper.insert(new User(IdWorker.getId(), "1", 1, 1));
     System.err.println("--------- insertInjector --------- " + rlt);

     //session.commit();
     sqlSession.rollback();
     sqlSession.close();
 }
 

import org.apache.ibatis.session.SqlSession; //导入依赖的package包/类
/**
 * 查询不存在的结果
 */
@Test
public void testDynamicSelectZero() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        Country country = new Country();
        country.setCountrycode("CN");
        country.setCountryname("天朝");//实际上是 China
        Country result = mapper.selectOne(country);

        Assert.assertNull(result);
    } finally {
        sqlSession.close();
    }
}