MyBatis中的数据库动态查询的编写

1.po中建立Student等持续化类。

2.配置mybatis-config.xml和log4j.properties文件。

3.编写实现类，接口StudentMapper中写操作的方法，配置文件中进行配置

public interface StudentMapper {
List<Student> find(Student stu);
List<Student> find2(Student stu);
List<Student> find3(Student stu);
List<Student> find4(List<Integer> ids);
List<Student> find5(Integer ids[]);
void update(Student stu);
}

4.StudentMapper.xml配置

<mapper namespace="com.dao.StudentMapper">
将sql语句可复用部分进行封装
<sql id="column_name">
id,name,age,gender,address
</sql>
<sql id="query_where">
<if test="name!=null">
and name =#{name}
</if>
<if test="age!=null">
and age=#{age}
</if>
</sql>

查询的方法

        <select id="find"  parameterType="student"  resultType="student" >
select 
<include refid="column_name"/>
from student where  1=1
<include refid="query_where"/>
</select>

<select id="find2" parameterType="student" resultType="student" >
select id,name,age,gender,address from student 
<!-- 
where 的作用：1如果第一条件不为null，则加上 where  2第一个条件的and或者or会被忽略 
-->
<where>
<include refid="query_where"/>
<if test="gender!=null">
and gender=#{gender}
</if>
</where>
</select>

<select id="find3" parameterType="student" resultType="student" >
select id,name,age,gender,address from student
<!--
如果where条件不起作用，则使用trim元素,trim不能与where同时使用 
前面是where 如果后面直接跟 and或者or的 则会忽略掉 
-->
<trim prefix="where" prefixOverrides="and|or">
<if test="address!=null">
and address =#{address}
</if>
<if test="age!=null">
and age=#{age}
</if>
</trim> 
</select>
查询结果放入集合中
<select id="find4" resultType="student" > <!-- select * from student where id in(3,5,6) -->
select * from student 
<where>
<foreach collection="list" item="item" separator="," open="id in (" close=")" >
#{item} 
</foreach>
</where>  
</select>
查询结果放入数组中
<select id="find5" resultType="student" > <!-- SELECT * FROM student WHERE id=3 OR id=5 OR id=6-->
select * from student 
<where>
<foreach collection="array" item="item" separator="or"  >
id=#{item} 
</foreach>
</where>  
</select>
修改数据库
<update id="update" parameterType="student">
update student
<!-- set 的作用:会自动处理好最后一列(加不加","号) 更改列名值后建议都加上","-->
<set>
<if test="name!=null">
name=#{name},
</if>
<if test="age!=null">
age=#{age},
</if>
</set>
where id=#{id}
</update>
</mapper>

5.编写测试类进行测试(举例编写)

private SqlSessionFactory sf;//工厂只要创建一回
@Before
public void before(){
InputStream in = null;
try {
in = Resources.getResourceAsStream("mybatis-config.xml");
} catch (IOException e) {
e.printStackTrace();
}
sf = new SqlSessionFactoryBuilder().build(in);
}

普通动态查询

@Test
public void testFind() {
SqlSession session = sf.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
Student stu = new Student();
stu.setName("少林");
stu.setAge(89);
mapper.find(stu );
session.close();
}

@Test
public void testFind4() {
SqlSession session = sf.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
List<Integer> list = Arrays.asList(3,5,6);
mapper.find4(list);
session.close();
}

@Test
public void testFind5() {
SqlSession session = sf.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
Integer[] array = {3,5,6};
mapper.find5(array );
session.close();
}

@Test
public void testUpdate(){
SqlSession session = sf.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
Student stu = new Student();
stu.setName("少林者");
stu.setAge(99);
stu.setId(2);
mapper.update(stu);
session.commit();
session.close();
}