/** * Created by Administrator on 2017/9/11 0011. */ 关于mapper中SQL语句的写法: 假如实体类的类型: public class Student { private Integer id;//编号 private String name;//姓名 private Double sal;//薪水 } 数据库: create table students( students_id int(5) primary key, students_name varchar(10), students_sal double(8,2) ); <!-- 当实体属性与表字段名不相同的时候,必须书写以下代码 当实体属性与表字段名相同的时候,以下代码可选 --> <resultMap type="cn.itcast.javaee.mybatis.app09.Student" id="studentMap"> <id property="id" column="students_id"/> <result property="name" column="students_name"/> <result property="sal" column="students_sal"/> </resultMap> add: 添加实体类的的全部数据 <insert id="add" parameterType="cn.itcast.javaee.mybatis.app09.Student"> insert into students(students_id,students_name,students_sal) values(#{id},#{name},#{sal}) </insert> delete: 根据id进行删除: <delete id="delete" parameterType="cn.itcast.javaee.mybatis.app09.Student"> delete from students where id = #{id} </delete> update: <update id="update" parameterType="cn.itcast.javaee.mybatis.app09.Student"> update students set name=#{name},sal=#{sal} where id=#{id} </update> select : 1.根据id查询信息: /* parameterType 传的参数类型 resultMap 对应的实体类与表名不相同所调用的模块 resultType 对应的实体类与表名相同所调用的模块 如果参数不是一个实体的话,只是一个普通变量,例如:int,double,String 这里的#{中间的变量名可以随便写},不过提倡就用方法的形参 mybatis会将查询出来的表记录和studentMap这个id所对应的映射结果相互匹配 就是调用25行的代码 */ <select id="findById" parameterType="int" resultMap="studentMap"> select students_id,students_name,students_sal from students where students_id = #{xxxxxxxxxxxxxxxxxx} //不是 </select> 2.查询所有的学生 <!-- 查询所有学生 理论上resultType要写List<Student> 但这里只需书写List中的类型即可,即只需书写Student的全路径名 --> <select id="findAll" resultType="cn.itcast.javaee.mybatis.app09.Student"> select id,name,sal from students </select> <!-- 注意:这个insert/update/delete标签只是一个模板,在做操作时,其实是以SQL语句为核心的 即在做增/删/时,insert/update/delete标签可通用, 但做查询时只能用select标签 我们提倡什么操作就用什么标签 --> 调用对应的mapper: sqlSession = MybatisUtil.getSqlSession(); add: sqlSession.insert(Student.class.getName()+".add",student); //第一个参数是空间名+调用的id名字 第二个参数是你传的参数 delete: sqlSession.delete(Student.class.getName()+".delete",student); update: sqlSession.update(Student.class.getName()+".update",student); select: sqlSession.selectList(Student.class.getName()+".findAll"); //返回需要用List集合 sqlSession.selectOne(Student.class.getName()+".findById",id); mybatis分页: <select id="findAllWithFy" parameterType="map" resultMap="studentMap"> select students_id,students_name,students_sal from students limit #{pstart},#{psize} </select> <select id="findAllByNameWithFy" parameterType="map" resultMap="studentMap"> select students_id,students_name,students_sal from students where students_name like #{pname} limit #{pstart},#{psize} </select> /** * 无条件分页 * @param start 表示在mysql中从第几条记录的索引号开始显示,索引从0开始 * @param size 表示在mysql中最多显示几条记录 */ public List<Student> findAllWithFy(int start,int size) throws Exception{ SqlSession sqlSession = null; try{ sqlSession = MybatisUtil.getSqlSession(); Map<String,Object> map = new LinkedHashMap<String,Object>(); map.put("pstart",start); map.put("psize",size); return sqlSession.selectList(Student.class.getName()+".findAllWithFy",map); }catch(Exception e){ e.printStackTrace(); throw e; }finally{ MybatisUtil.closeSqlSession(); } } /** * 有条件分页 */ public List<Student> findAllByNameWithFy(String name,int start,int size) throws Exception{ SqlSession sqlSession = null; try{ sqlSession = MybatisUtil.getSqlSession(); Map<String,Object> map = new LinkedHashMap<String, Object>(); map.put("pname","%"+name+"%"); map.put("pstart",start); map.put("psize",size); return sqlSession.selectList(Student.class.getName()+".findAllByNameWithFy",map); }catch(Exception e){ e.printStackTrace(); throw e; }finally{ MybatisUtil.closeSqlSession(); } } 动态SQL的使用: add: <!-- sql片段对应字段名,id属性值任意 --> <sql id="key"> <!-- 去掉最后一个, --> <trim suffixOverrides=","> <if test="id!=null"> students_id, </if> <if test="name!=null"> students_name, </if> <if test="sal!=null"> students_sal, </if> </trim> </sql> <!-- sql片段对应?,id属性值任意 --> <sql id="value"> <!-- 去掉最后一个, --> <trim suffixOverrides=","> <if test="id!=null"> <!-- 这里引用的变量是parameterType里面的变量 --> #{id}, </if> <if test="name!=null"> #{name}, </if> <if test="sal!=null"> #{sal}, </if> </trim> </sql> <!-- <include refid="key"/>和<include refid="value"/>表示引用上面定义的sql片段 --> <insert id="dynaInsert" parameterType="cn.itcast.javaee.mybatis.app14.Student"> insert into students(<include refid="key"/>) values(<include refid="value"/>) </insert> 对应的java:sqlSession.insert("studentNamespace.dynaInsert",student); delete: 数组删除: <delete id="dynaDeleteArray"> delete from students where students_id in <!-- foreach用于迭代数组元素 open表示开始符号 close表示结束符合 separator表示元素间的分隔符 item表示迭代的数组,属性值可以任意,但提倡与方法的数组名相同 #{ids}表示数组中的每个元素值 --> <foreach collection="array" open="(" close=")" separator="," item="ids"> #{ids} </foreach> </delete> sqlSession.delete("studentNamespace.dynaDeleteArray",ids); //ids是数组 <delete id="dynaDeleteList"> delete from students where students_id in <foreach collection="list" open="(" close=")" separator="," item="ids"> #{ids} </foreach> </delete> sqlSession.delete("studentNamespace.dynaDeleteList",ids); //ids是集合 update: <!-- set标签自动判断哪个是最后一个字段,会自动去掉最后一个,号 --> <update id="dynaUpdate" parameterType="map"> update students <set> <if test="pname!=null"> students_name = #{pname}, </if> <if test="psal!=null"> students_sal = #{psal}, </if> </set> where students_id = #{pid} </update> Map<String,Object> map = new HashMap<String, Object>(); map.put("pid",id); map.put("pname",name); map.put("psal",sal); sqlSession.update("studentNamespace.dynaUpdate",map); select: <select id="findAll" parameterType="map" resultMap="studentMap"> select * from students <where> <if test="pid!=null"> and students_id = #{pid} </if> <if test="pname!=null"> and students_name = #{pname} </if> <if test="psal!=null"> and students_sal = #{psal} </if> </where> </select> Map<String,Object> map = new LinkedHashMap<String,Object>(); map.put("pid",id); map.put("pname",name); map.put("psal",sal); sqlSession.selectList("studentNamespace.findAll",map); 多表查询: 一对一:(学生与身份证) public class Card { private Integer id; private String num; public Card(){} } public class Student { private Integer id; private String name; private Card card;//关联属性 public Student(){} } create table cards( cid int(5) primary key, cnum varchar(10) ); create table students( sid int(5) primary key, sname varchar(10), scid int(5), constraint scid_fk foreign key(scid) references cards(cid) ); 查询1号学生的信息 : CardMapper.xml: <resultMap type="cn.itcast.javaee.mybatis.one2one.Card" id="cardMap"> <id property="id" column="cid"/> <result property="num" column="cnum"/> </resultMap> StudentMapper.xml: <resultMap type="cn.itcast.javaee.mybatis.one2one.Student" id="studentMap"> <id property="id" column="sid"/> <result property="name" column="sname"/> <!-- 引入CardMapper.xml文件中的映射信息 property表示Student类的关联属性 resultMap表示引入CardMapper.xml文件的映射类型 --> <association property="card" resultMap="cardNamespace.cardMap"/> </resultMap> <!-- 查询1号学生的信息 --> <select id="findById" parameterType="int" resultMap="studentMap"> select s.sid,s.sname,c.cid,c.cnum from students s inner join cards c on s.scid = c.cid and s.sid = #{id} </select> java代码: sqlSession.selectOne("studentNamespace.findById",id);
mybatis详解
最新推荐文章于 2024-08-14 01:48:08 发布