MyBatis的增删改查操作

搭建好mybatis之后 进行对数据库的操作

1. 添加语句

在映射文件中添加语句

<mapper namespace="com.wn.dao.StudentDao">
	 <insert id="insertStudent" parameterType="StudentBean">
		insert into student(name,age,score) values(#{name},#{age},#{score})
	</insert>

映射文件要放在与接口一个目录下
namespace：必须是对应接口的全限定名
id ：dao中的方法名字
parameterType：传入的参数类型 可以省略

添加语句后 获取主键的值 赋值给主键

<insert id="insertStudentGetId" parameterType="StudentBean">
 		insert into student(name,age,score) values(#{name},#{age},#{score})
 		<!-- keyProperty:查询结果赋值给studentbean的id属性
 			resultType：返回结果的数据类型
 			order：执行顺序  mysql在数据添加后
 		 -->
 		<selectKey keyProperty="id" resultType="int" order="AFTER">
 		select  @@identity
 		</selectKey>
 	</insert>

2.删除语句

 <update id="updateStudent" >
 		update student set name=#{name} ,age=#{age},score=#{score} 
 		where id = #{id}
 	</update>

3.查询语句

/**
	 * 静态参数
	 * @param student
	 */
	void insertStudent(StudentBean student);
	List<StudentBean> selectStudentAll();
	//根据姓名模糊查询
	List<StudentBean> selectStudentByName(String name);
	
	//多参数查询  使用map作为方法参数
	List<StudentBean> selectStuByMap1(Map<String, Object> map);
	List<StudentBean> selectStuByMap2(Map<String, Object> map);
	
	List<StudentBean> selectStuByParameters1(String name , int age);
	List<StudentBean> selectStuByParameters2(String name , StudentBean student);
	List<StudentBean> selectStuByParameters3(@Param("name") String name , @Param("age")int age);
	List<StudentBean> selectStuByParameters4(@Param("name") String name , @Param("student") StudentBean student);





<select id="selectStudentAll" resultType="StudentBean">
	select * from student 
</select>
<select id="selectStudentByName" resultType="StudentBean">
	select * from student where name like  '%' #{name} '%'
</select>

<select id="selectStuByMap1" resultType="StudentBean">
	<!-- 查询的参数是Map   #{map的key}  使用的数据是key对应的value-->
	select * from student where name like  '%' #{name} '%' and age>#{age}
</select>

<select id="selectStuByMap2" resultType="StudentBean">
	<!-- 查询的参数是Map   #{map的key}  使用的数据是key对应的value
			map的key是student，value是student对象-->
	select * from student where name like  '%' #{name} '%' and age > #{student.age}
</select>

<select id="selectStuByParameters1" resultType="StudentBean">
	select * from student where name like  '%' #{0} '%' and age > #{1}
</select>

<select id="selectStuByParameters2"  resultType="StudentBean">
	select * from student where name like '%' #{0} '%' and age > #{1.age}
</select>

动态参数

/**
 * 动态参数
 */
//mybatis 动态参数类似域jstl《c:》

//if拼接  sql语句要跟上 where 1 =1 
List<StudentBean> selectStudentByIf(StudentBean student);

<select id="selectStudentByIf"  resultType="StudentBean">
	select * from student where 1=1
	<if test="name!=null and name!=''">
		and name like '%' #{name} '%'
	</if>
	<if test="age>0">
		and age > #{age}
	</if>
</select>

//不生成 1= 1  提高效率   自动在sql语句拼接的时候加上where  关键字
List<StudentBean> selectStudentByWhere(StudentBean student);

<select id="selectStudentByWhere"  resultType="StudentBean">
	select * from student
	<where>
		<if test="name!=null and name!=''">
		and name like '%' #{name} '%'
		</if>
		<if test="age>0">
		and age > #{age}
		</if>
	</where>
</select>

//多选一
List<StudentBean> selectStudentByChoose(StudentBean student);

<select id="selectStudentByChoose" resultType="StudentBean">
	select * from student 
	<choose>
		<when test="name!=null and name!=''">
			where name like '%' #{name} '%'
		</when>
		<when test="age>0">
			where age > #{age}
		</when>
		<otherwise>
			where 1 = 2
		</otherwise>		
	</choose> 
</select>

List<StudentBean> selectStudentByForeachArray(int[] ids);

	<select id="selectStudentByForeachArray" resultType="StudentBean">
	select * from student 
	<if test="array!=null and array.length>0">
		where id in
		<foreach collection="array" item="id" open="(" close=")" separator=",">
			#{id}
		</foreach>		
	</if>
	</select>

List<StudentBean> selectStudentByForeachList(List<Integer> ids);

<select id="selectStudentByForeachList" resultType="StudentBean">
	select * from student 
	<if test="list!=null and list.size>0">
		where id in
		<foreach collection="list" item="id" open="(" close=")" separator=",">
			#{id}
		</foreach>		
	</if>
</select>

List<StudentBean> selectStudentByForeachStudent(List<StudentBean> students);

<select id="selectStudentByForeachStudent" resultType="StudentBean">
	select * from student 
	<if test="list!=null and list.size>0">
		where id in
		<foreach collection="list" item="student" open="(" close=")" separator=",">
			#{student.id}
		</foreach>		
	</if>
</select>

List<StudentBean> selectStudentBySqlFragement();

<select id="selectStudentBySqlFragement" resultType="StudentBean">
	select <include refid="fragement" /> student 	
</select>
<sql id="fragement"> * from</sql>

//统计一张表的总数据条数  分页的总条数
int selectStudentCount();

	<select id="selectStudentCount" resultType="int">
	select count(*) from student 
	</select>


<!-- 动态参数 -->