以后返回统一用对象,resultMap
查询
基本查询
select * from person where person_id = #{id}
条件查询(分页)
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}:非预编译查询,name使用map中的key,select * from person p where p.name like '%四%' 注意:parameterType要使用map --> <select id="selectPersonByLike" parameterType="map" resultMap="BaseResultMap"> select * from person p where p.name like '%
name:非预编译查询,name使用map中的key,select∗frompersonpwherep.namelike′注意:parameterType要使用map−−><selectid="selectPersonByLike"parameterType="map"resultMap="BaseResultMap">select∗frompersonpwherep.namelike′{name}%’
数量查询
SELECT
count(1)
from t_gh_time_products
where product_oid = #{productOid}
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();
}
}
自增返回主键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