mybatis动态sql
1、数据库表
CREATE TABLE `student` ( `tid` int(5) NOT NULL AUTO_INCREMENT, `tage` int(11) DEFAULT NULL, `tname` varchar(20) DEFAULT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8
2、实体类
/** *学生的实体类 */ public class Student { private Integer id; // 学生编号 private String name; // 姓名 private Integer age; // 年龄 /** * 对应的有参无参构造以及对应的get和set方法 */ public Student() { super(); } @Override public String toString() { return "Student [id=" + id + ", name=" + name + ", age=" + age + "]"; } public Student(Integer id, String name, Integer age) { super(); this.id = id; this.name = name; this.age = age; } public Student(String name, Integer age) { super(); this.name = name; this.age = age; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } }
3、mybatis配置文件
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 通过properties文件配置连接数据库的四要素 --> <properties resource="jdbc.properties" /> <!-- 设置别名 两种方式 --> <typeAliases> <!-- 01.这种方式别名可以随意取 但是如果有多个 类 就需要配置多个typeAlias <typeAlias type="cn.bdqn.bean.Student" alias="student"/> --> <package name="cn.bdqn.bean"/><!-- 02.这种方式要求别名必须使用简写的类名 --> </typeAliases> <!-- 配置运行环境 可以有多个 environment --> <environments default="mysql"><!-- 默认采用的环境 --> <environment id="mysql"> <!-- 环境的名称 --> <!--配置事务管理 采用jdbc默认的事务管理 之后整合的时候 肯定是交给了 spring来处理了 --> <transactionManager type="JDBC" /> <!-- 数据源采用的是连接池技术 POOLED:mybatis自带的数据源，JNDI:基于tomcat的数据源 --> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> </dataSource> </environment> </environments> <!-- 加载映射文件信息 --> <mappers> <mapper resource="cn/bdqn/dao/StduentMapper.xml" /> </mappers> </configuration>
4、dao层
import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.Param; import cn.bdqn.bean.Student; public interface StudentDao { int addStudent(Student student); List<Student> selectAll(); List<Student> selectStudentsByNameAndAge(Map<String, Object> map); List<Student> selectStudentsByNameAndAge1(@Param("name") String name, @Param("age") Integer age); List<Student> selectStudentsByNameAndAge2(String name, Integer age); int deleteStudentsByIds(@Param("ids") Integer[] ids); int deleteStudentsByIds1(@Param("ids") List<Integer> ids); int deleteStudentsByIds2(@Param("maps") Map<String, List<Integer>> maps); int deleteStudentsByIds3( @Param("maps") Map<String, Map<String, Integer>> maps); }
5、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="cn.bdqn.dao.StudentDao"> <sql id="Base_Column_list"> tid,tname,tage </sql> <resultMap type="student" id="BaseResultMap"> <constructor> <idArg column="tid" jdbcType="INTEGER" javaType="java.lang.Integer" /> <arg column="tname" jdbcType="VARCHAR" javaType="java.lang.String" /> <arg column="tage" jdbcType="INTEGER" javaType="java.lang.Integer" /> </constructor> </resultMap> <insert id="addStudent" parameterType="student"> insert into student(tage,tname) values(#{age,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}) <!-- <selectKey keyProperty="tid" order="AFTER" resultType="int"> SELECT LAST_INSERT_ID() as id </selectKey> --> </insert> <select id="selectAll" resultMap="BaseResultMap"> select <include refid="Base_Column_list"/> from student </select> <select id="selectStudentsByNameAndAge" resultMap="BaseResultMap"> select <include refid="Base_Column_list"/> from student <trim prefix="where" prefixOverrides="and | or"> <if test="stu.name!=null"> and tname like CONCAT('%',#{stu.name},'%') </if> <if test="age!=null"> and tage > #{age} </if> </trim> </select> <select id="selectStudentsByNameAndAge1" resultMap="BaseResultMap"> select <include refid="Base_Column_list"/> from student <trim prefix="where" prefixOverrides="and | or"> <if test="name!=null"> and tname like CONCAT('%',#{name},'%') </if> <if test="age!=null"> and tage > #{age} </if> </trim> </select> <select id="selectStudentsByNameAndAge2" resultMap="BaseResultMap"> select <include refid="Base_Column_list"/> from student where tname like CONCAT('%',#{0},'%') and tage > #{1} </select> <delete id="deleteStudentsByIds" parameterType="list"> delete from student <if test="ids !=null and ids.length>0"> where tid in <foreach collection="ids" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </if> </delete> <delete id="deleteStudentsByIds1" parameterType="list"> delete from student <if test="ids !=null and ids.size()>0"> where tid in <foreach collection="ids" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </if> </delete> <delete id="deleteStudentsByIds2" parameterType="list"> <foreach collection="maps" index="key" item="ent" separator="union"> delete from student where tid in <foreach collection="ent" index="index" item="item1" open="(" separator="," close=")"> #{item1} </foreach> </foreach> </delete> <delete id="deleteStudentsByIds3" parameterType="map"> <foreach collection="maps" index="key" item="ent" separator="union"> delete from student where tid in <foreach collection="ent" index="index" item="item1" open="(" separator="," close=")"> #{item1} </foreach> </foreach> </delete> </mapper>
6、测试类
import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.ibatis.session.SqlSession; import org.apache.log4j.Logger; import org.junit.After; import org.junit.Before; import cn.bdqn.bean.Student; import cn.bdqn.dao.StudentDao; import cn.bdqn.util.MybatisUtil; public class Test { private Logger logger = Logger.getLogger(Test.class); SqlSession session; StudentDao dao; @Before public void before() { // 因为需要关闭session 需要把session提取出去 session = MybatisUtil.getSqlSession(); dao = session.getMapper(StudentDao.class); } @After public void after() { if (session != null) { session.close(); } } @org.junit.Test public void testAddStduent() { Student student = new Student(); student.setAge(20); student.setName("dddd"); dao.addStudent(student); session.commit(); } @org.junit.Test public void selectStduents() { List<Student> students = dao.selectAll(); for (Student student : students) { logger.debug("insert student.id=============>>>" + student); } } @org.junit.Test public void selectStduentsByNameAndAge() { Map<String, Object> map = new HashMap<String, Object>(); Student student2 = new Student(); student2.setName("小"); map.put("stu", student2); map.put("age", 50); List<Student> students = dao.selectStudentsByNameAndAge(map); for (Student student : students) { logger.debug("insert student.id=============>>>" + student); } } @org.junit.Test public void selectStduentsByNameAndAge1() { List<Student> students = dao.selectStudentsByNameAndAge1(null, 50); for (Student student : students) { logger.debug("insert student.id=============>>>" + student); } } @org.junit.Test public void selectStduentsByNameAndAge2() { List<Student> students = dao.selectStudentsByNameAndAge1(null, 50); for (Student student : students) { logger.debug("insert student.id=============>>>" + student); } } @org.junit.Test public void selectStduentsByNameAndAge3() { List<Student> students = dao.selectStudentsByNameAndAge2("小", 50); for (Student student : students) { logger.debug("insert student.id=============>>>" + student); } } @org.junit.Test public void deleteStudents() { /*List<Integer> ids = new ArrayList<>(); ids.add(1); ids.add(3); ids.add(4);*/ int rowNum = dao.deleteStudentsByIds(new Integer[] { 1, 3, 4 }); logger.debug("删除的行数======>" + rowNum); // session.commit(); } @org.junit.Test public void deleteStudents1() { List<Integer> ids = new ArrayList<>(); ids.add(1); ids.add(3); ids.add(4); int rowNum = dao.deleteStudentsByIds1(ids); logger.debug("删除的行数======>" + rowNum); // session.commit(); } @org.junit.Test public void deleteStudents2() { Map<String, List<Integer>> maps = new HashMap<>(); List<Integer> list = new ArrayList<>(); list.add(1); list.add(3); list.add(4); maps.put("maps", list); int rowNum = dao.deleteStudentsByIds2(maps); logger.debug("删除的行数======>" + rowNum); // session.commit(); } @org.junit.Test public void deleteStudents3() { Map<String, Map<String, Integer>> maps = new HashMap<>(); Map<String, Integer> maps1 = new HashMap<>(); maps1.put("1", 1); maps1.put("3", 3); maps1.put("4", 4); maps.put("maps", maps1); int rowNum = dao.deleteStudentsByIds3(maps); logger.debug("删除的行数======>" + rowNum); // session.commit(); } }
