mybatis查询_mybatis ranking-优快云博客

本文链接：https://blog.youkuaiyun.com/u014362882/article/details/84819301

本文深入探讨MyBatis框架下的各种高级查询技术，包括基本查询、条件查询、分页查询、In子查询、模糊查询、数量查询及返回map查询等。通过具体示例解析如何运用MyBatis进行高效数据库操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

以后返回统一用对象，resultMap
查询
基本查询

select * from person where person_id = #{id}

条件查询（分页）

select from cobra_apply_store yjs_user_id = #{yjsUserId} and apply_status = #{applyStatus} order by created_date desc limit #{start,jdbcType=INTEGER}, #{end} 注意：if条件后面没有，逗号 In子查询1 select * from person p where p.person_id in ${ids} @Test public void selectPersonByIn() throws Exception { SqlSession session = sqlSessionFactory.openSession(); try { Map

In子查询2

select * from pxerson p where p.person_id in

#{id}

@Test
public void selectPersonByInWithForEach() throws Exception {
SqlSession session = sqlSessionFactory.openSession();
try {
Map<String, Object> map = new HashMap<String, Object>();
//String[] ids = {“10”, “11”, “12”};
//Integer[] ids = {10, 11, 12};
List ids = new ArrayList();
ids.add(10);
ids.add(11);
ids.add(12);
map.put(“ids”, ids);
List pList = session.selectList(“cn.itcast.mapper.Person.selectPersonByInWithForEach”, map);
for(Person person : pList){
System.out.println(person);
}
}finally{
session.close();
}
}
模糊查询
$n a m e : 非预编译查询， n a m e 使用 m a p 中的 k e y ， s e l e c t * f r o m p e r s o n p w h e r e p . n a m e l i k e^{'} 注意： p a r a m e t e r T y p e 要使用 m a p - - > < s e l e c t i d = " s e l e c t P e r s o n B y L i k e " p a r a m e t e r T y p e = " m a p " r e s u l t M a p = " B a s e R e s u l t M a p " > s e l e c t * f r o m p e r s o n p w h e r e p . n a m e l i k e^{'}$ {name}%’

数量查询

SELECT
count(1)
from t_gh_time_products
where product_oid = #{productOid}

返回map查询大于号 <![CDATA[ select * from person p where p.gender = #{gender} and p.birthday < #{birthday} ]]> Or and SELECT sys FROM pas WHERE sys='test' AND time >1352253596 AND stat = 200 OR stat = 304 优先级不同，加括号吧 SELECT sys FROM pas WHERE sys='test' AND time >1352253596 AND （stat = 200 OR stat = 304）优先级: not>and>or

Insert
批量insert

select LAST_INSERT_ID()

insert into person(person_id, name, gender, person_addr, birthday)
values

(#{person.personId}, #{person.name}, #{person.gender}, #{person.personAddr}, #{person.birthday})

@Test
public void insertBatch() throws Exception {
SqlSession session = sqlSessionFactory.openSession();
try {
Map<String, Object> map = new HashMap<String, Object>();
List pList = new ArrayList();
for(int i = 1; i < 1000002; i++){
Person person = new Person();
person.setName(“王大拿”+i);
person.setPersonAddr(“象牙山”+i);
person.setGender(0);
person.setBirthday(new SimpleDateFormat(“yyyy-MM-dd”).parse(“1958-01-10”));
pList.add(person);
if(i%100 == 0){
map.put(“personList”, pList);
session.insert(“cn.itcast.mapper.Person.insertBatch”, map);
pList.clear();
}
}
map.put(“personList”, pList);
session.insert(“cn.itcast.mapper.Person.insertBatch”, map);
//只要是数据库的变更必须要提交
session.commit();
}catch(Exception ex){
ex.printStackTrace();
session.rollback();
}finally{
session.close();
}
}

insert into pts_order_contract (contract_code, contract_url, order_id,product_id, invester_id, invest_amount, each_loan_state, assert_code, create_time, update_time ) values ( #{item.contractCode,jdbcType=VARCHAR}, #{item.contractUrl,jdbcType=VARCHAR}, #{item.orderId,jdbcType=VARCHAR},#{item.productId,jdbcType=VARCHAR}, #{item.investerId,jdbcType=VARCHAR}, #{item.investAmount,jdbcType=DECIMAL}, #{item.eachLoanState,jdbcType=INTEGER}, #{item.assertCode,jdbcType=VARCHAR}, #{item.createTime,jdbcType=TIMESTAMP}, #{item.updateTime,jdbcType=TIMESTAMP} )

自增返回主键id

insert into cobra_apply_store (apply_type, apply_person_type, 注意keyProperty是实体的id 要获取applyStoreId那么要用返回的对象获取id 更新 update system_bank_define bank_name = #{bankName,jdbcType=VARCHAR}, short_name = #{shortName,jdbcType=VARCHAR}, bank_org = #{bankOrg,jdbcType=VARCHAR}, use_flag = #{useFlag,jdbcType=INTEGER}, order_b = #{orderB,jdbcType=INTEGER}, created_date = #{createdDate,jdbcType=TIMESTAMP}, updated_date = #{updatedDate,jdbcType=TIMESTAMP}, where bank_code = #{bankCode,jdbcType=VARCHAR}

更新注意 set中的if是有逗号的

update pts_order_contract contract_code = #{item.contractCode,jdbcType=VARCHAR}, contract_url = #{item.contractUrl,jdbcType=VARCHAR}, invester_id = #{item.investerId,jdbcType=VARCHAR}, invest_amount = #{item.investAmount,jdbcType=DECIMAL}, each_loan_state = #{item.eachLoanState,jdbcType=INTEGER}, assert_code = #{item.assertCode,jdbcType=VARCHAR}, create_time = #{item.createTime,jdbcType=TIMESTAMP}, update_time = #{item.updateTime,jdbcType=TIMESTAMP}, product_id = #{item.productId,jdbcType=VARCHAR}, where order_id = #{item.orderId,jdbcType=VARCHAR}

删除

1）、批量删除

delete from person where person_id in

#{id}

@Test
public void deleteBatch() throws Exception {
	SqlSession session = sqlSessionFactory.openSession();
	try {
		Map<String, Object> map = new HashMap<String, Object>();
		List pList = new ArrayList();
		for(int i = 211; i <= 1000211; i++){
			pList.add(i);
			if(i%1000 == 0){
				map.put("ids", pList);
				session.delete("cn.itcast.mapper.Person.deleteBatch", map);
				pList.clear();
			}
		}
		map.put("ids", pList);
		session.delete("cn.itcast.mapper.Person.deleteBatch", map);
		//只要是数据库的变更必须要提交
		session.commit();
	}catch(Exception ex){
		ex.printStackTrace();
		session.rollback();
	}finally{
		session.close();
	}
}

工具类
PropertyUtils.copyProperties

参考资料
mybatis 中 foreach collection的三种用法
http://www.cnblogs.com/fangyu19900812/p/6046209.html