对Mybatis学后的一些基本总结:
一.关于级联属性的查询问题;
二.关于动态sql的使用;
三.关于集合类型和数组类型数据的sql传参和查询处理;
1.先总结第一点,级联属性的查询问题。比如在我的数据库中有person表,其中有两个字段是地址信息,一个是公司地址,一个是家庭住址;我们在java中创建pojo时,用了两个pojo来描述这个数据表,第一个是Person.java,第二个是Address;Person里面包含了一个Address类型属性;Address里面有有两个属性,分别是String homeAddress和String workAddress;
Person.java
package com.charles.pojo;
public class Person {
private String id;
private String name;
private Address address;
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public Person(String id, String name, Address address) {
super();
this.id = id;
this.name = name;
this.address = address;
}
public Person(String id, String name) {
super();
this.id = id;
this.name = name;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Person() {
super();
}
@Override
public String toString() {
return "person [id=" + id + ", name=" + name + ", address=" + address.getHomeAddress()+" "+address.getWorkAddress() + "]";
}
}
Address.java
package com.charles.pojo;
public class Address {
String homeAddress;
String workAddress;
@Override
public String toString() {
return "Address [homeAddress=" + homeAddress + ", workAddress=" + workAddress + "]";
}
public Address() {
}
public Address(String homeAddress, String workAddress)
{
this.homeAddress = homeAddress;
this.workAddress = workAddress;
}
public String getHomeAddress() {
return homeAddress;
}
public void setHomeAddress(String homeAddress) {
this.homeAddress = homeAddress;
}
public String getWorkAddress() {
return workAddress;
}
public void setWorkAddress(String workAddress) {
this.workAddress = workAddress;
}
}
personMapper.java
package com.charles.mapper;
import java.util.List;
import com.charles.pojo.Person;
public interface personMapper {
List<Person> selectByAddress(Person person);
}
personMapper.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.charles.mapper.personMapper">
<select id="selectByAddress" parameterType="person" resultType="person" resultMap="map1">
select * from person where homeaddress=#{address.homeAddress} and workaddress='${address.workAddress}'
</select>
<resultMap type="person" id="map1">
<id column="id" property="id"/>
<result column="name" property="name"/>
<association property="address" javaType="Address">
<result column="homeaddress" property="homeAddress"/>
<result column="workaddress" property="workAddress"/>
</association>
</resultMap>
</mapper>
对于mybatis配置文件,这里就不再赘述了。需要注意的是,在配置mapper文件时,当我们返回查询结果时,需要指定一下返回数据列对应的pojo属性,这样才能正确查到数据。比如这里,我们数据库中的homeaddress列对应在person中的address属性的homeaddress属性,所以这里需要用association来告诉mybatis这种对应关系,书写时应注意属性的大小写是严格区分的,不然不能通过反射找到该属性;而且对于输入参数,#{} 和${}都支持级联属性,例如此处
接下来是数据库:
最后,写测试类:
package com.charles.pojo;
import java.io.IOException;
import java.io.Reader;
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.charles.mapper.personMapper;
public class test {
public static void main(String[] args) throws IOException {
Reader reader=Resources.getResourceAsReader("conf.xml");
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(reader);
SqlSession session =factory.openSession();
personMapper mapper=session.getMapper(personMapper.class);
//准备需要查询的对象
Person person=new Person();
//准备需要查询的地址
Address address=new Address();
address.setHomeAddress("cq");
address.setWorkAddress("yc");
//为查询对象设置地址
person.setAddress(address);
//开始查询
List<Person> persons=mapper.selectByAddress(person);
System.out.println(persons);
}
}
执行结果:
二.关于动态sql的使用;
什么是动态sql,emmm,要是理解成能动的sql语句也没多大毛病,动态sql就是可以根据参数变化而改变的sql语句,接下来举个栗子:
比如我们要查询操作 如下:
//查询全部
"select stuno,stuname from student";
//根据年龄查询学生
"select stuno,stuname from student where stuage = #{stuage}";
//根据姓名和年龄查询学生
"select stuno,stuname from student where stuage = #{stuage} and stuage = #{stuage} ";
观察这三个sql语句,其都有查询操作,但是每次查询的条件不一样,我们要书写三次sql语句。对于这种情况,我们使用动态sql,将其改为一条sql,接下来的例子还是使用person。我们查询姓名,家庭地址,或者工作地址的人,只用一个sql就能完成。
其他配置和上面一样,mapper配置文件如下:
<?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.charles.mapper.personMapper">
<select id="selectByNameAndAddress" parameterType="person" resultType="person" resultMap="map1">
select * from person
<where>
<if test="name!=null and name!=''">
and name=#{name}
</if>
<if test="address!=null">
<if test="address.homeAddress!=null and address.homeAddress!=''">
and homeaddress=#{address.homeAddress}
</if>
<if test="address.workAddress!=null and address.workAddress!=''">
and workaddress=#{address.workAddress}
</if>
</if>
</where>
</select>
<resultMap type="person" id="map1">
<id column="id" property="id"/>
<result column="name" property="name"/>
<association property="address" javaType="Address">
<result column="homeaddress" property="homeAddress"/>
<result column="workaddress" property="workAddress"/>
</association>
</resultMap>
</mapper>
我们在select中加入了sql标签<where>这是智能标签,他能自动处理where后出现的第一个and关键字,从而使得查询业务正确进行,
测试方法如下:第一次我们查询姓名为charleschou,homeaddress为cq,workaddress为yc的;
package com.charles.pojo;
import java.io.IOException;
import java.io.Reader;
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.charles.mapper.personMapper;
public class test {
public static void main(String[] args) throws IOException
{
//准备需要查询的对象
Person person=new Person();
//准备需要查询的地址
Address address=new Address();
address.setHomeAddress("cq");
address.setWorkAddress("yc");
//为查询对象设置地址
person.setAddress(address);
person.setName("charleschou");
//开始查询
selectByNameAndAddress(person);
}
public static void selectByNameAndAddress(Person person) throws IOException
{
Reader reader=Resources.getResourceAsReader("conf.xml");
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(reader);
SqlSession session =factory.openSession();
personMapper mapper=session.getMapper(personMapper.class);
Person persons=mapper.selectByNameAndAddress(person);
System.out.println(persons);
}
}
查询结果如下:
接下来查姓名为roman,不传入地址信息的person:(将main方法的参数改一下即可)
public static void main(String[] args) throws IOException
{
//准备需要查询的对象
Person person=new Person();
person.setName("roman");
//开始查询
selectByNameAndAddress(person);
}
查询结果:
,接下来查询 workaddress为yb的person:
public static void main(String[] args) throws IOException
{
//准备需要查询的对象
Person person=new Person();
//准备需要查询的地址
Address address=new Address();
address.setWorkAddress("yb");
selectByNameAndAddress(person);
}
查询结果如下:(这个数据是我为了后面测试加上去的,因此和前面的不一样)
至此,已经完成了动态sql的基本使用,一句sql多用;
3. 关于集合类型和数组类型数据的sql传参和查询处理;
当输入参数为集合或者数组类型,我们也需要进行不同处理,接下来就做一个例子:
3.1输入参数为普通类型数组(8大类型+string)的查询
我们通过int[]数组,传入一组id值,通过id值来查询person
mapper配置文件如下:
<select id="selectByIds" parameterType="int[]" resultMap="map1">
select * from person
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open="and id in(" close=")" item="personId" separator=",">
#{personId}
</foreach>
</if>
</where>
</select>
我们传入一个整形数组,通过foreach遍历出所有的元素来查询我们想要的值,collection代表集合类型,基本类型都用array表示;open表示sql语句开始,close表示结束,item代表array中具体的元素,这里就是id, seperator代表分隔的方式,这里是 ,号,我们接下来传入三个值1001,1002,1003,到了这里就被翻译为:select * from person where id in(1001,1002,1003);接下来看测试方法:传入一个整型数组
public static void main(String[] args) throws IOException
{
int[] ids= {1001,1002,1003};
selectByIds(ids);
}
public static void selectByIds(int[] ids) throws IOException
{
Reader reader=Resources.getResourceAsReader("conf.xml");
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(reader);
SqlSession session =factory.openSession();
personMapper mapper=session.getMapper(personMapper.class);
List<Person> persons=mapper.selectByIds(ids);
System.out.println(persons);
}
执行结果:成功!
3.2.1传入类型为List集合类型的处理,通过集合方式传入三个id值查询person
mapper配置文件如下:
<select id="selectByIdList" parameterType="list" resultMap="map1">
select * from person
<where>
<if test="list!=null and list.size>0">
<foreach collection="list" open="and id in(" close=")" item="personId" separator=",">
#{personId}
</foreach>
</if>
</where>
</select>
对于集合而言,collection里面规定使用list,其余基本不变,这里需要注意,array使用的是length,而list使用的是size;
测试方法:
public static void main(String[] args) throws IOException
{
ArrayList<Integer> ids=new ArrayList();
ids.add(1001);
ids.add(1002);
ids.add(1003);
selectByIdList(ids);
}
public static void selectByIdList(List<Integer> ids) throws IOException
{
Reader reader=Resources.getResourceAsReader("conf.xml");
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(reader);
SqlSession session =factory.openSession();
personMapper mapper=session.getMapper(personMapper.class);
List<Person> persons=mapper.selectByIdList(ids);
System.out.println(persons);
}
测试结果:ok
3.2.2:传入参数为map型集合,通过map的传值来实现查询person;(其实这种方式和传入参数为对象类型差不多,使用KV对的形式来获取参数值)
mapper配置文件如下:
<select id="selectByNameAndHomeaddressWithHashMap" parameterType="HashMap" resultMap="map1">
select * from person where workaddress=#{workAddress} or name like '%${name}%'
</select>
测试方法如下:我们查询workaddress为sz或者姓名包含zhang的person
public static void main(String[] args) throws IOException
{
HashMap<String,Object> map=new HashMap();
map.put("workAddress", "sz");
map.put("name", "zhang");
selectByNameAndHomeaddressWithHashMap(map);
}
public static void selectByNameAndHomeaddressWithHashMap(HashMap<String,Object> map) throws IOException
{
Reader reader=Resources.getResourceAsReader("conf.xml");
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(reader);
SqlSession session =factory.openSession();
personMapper mapper=session.getMapper(personMapper.class);
List<Person> persons=mapper.selectByNameAndHomeaddressWithHashMap(map);
System.out.println(persons);
}
可以看出,在mapper中,#{}中的参数名就是map中的key值,并且严格区分大小写,查询结果如图:
3.3传入参数为对象数组,通过对象属性查询person
mapper配置文件如下:当传入参数为对象数组时,们依旧使用array,但是传入参数类型应该填object[]
<select id="selectByObjectArray" parameterType="object[]" resultMap="map1">
select * from person
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open="and id in(" close=")" item="p" separator=",">
#{p.id}
</foreach>
</if>
</where>
</select>
测试方法如下:
public static void main(String[] args) throws IOException
{
//构造三个person并设置id值
Person person1=new Person();
person1.setId(1002);
Person person2=new Person();
person2.setId(1003);
Person person3=new Person();
person3.setId(1004);
Person[] ps= new Person[] {person1,person2,person3};
selectByObjectArray(ps);
}
public static void selectByObjectArray(Person[] persons) throws IOException
{
Reader reader=Resources.getResourceAsReader("conf.xml");
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(reader);
SqlSession session =factory.openSession();
personMapper mapper=session.getMapper(personMapper.class);
List<Person> ps=mapper.selectByObjectArray(persons);
for(Person p:ps)
{
System.out.println(p);
}
}
测试结果:
总结:
简单类型的数组:
无论编写代码时,传递的是什么参数名(ids),在mapper.xml中 必须用array代替该数组
集合:
无论编写代码时,传递的是什么参数名(ids),在mapper.xml中 必须用list代替该数组
对象数组:
Person[] persons= {person1,person2,person3} 每个studentx包含一个学号属性,需要用object[]来接受参数,并用array代替