插入数据 可以在占位符的里面直接加上数据想要指定的类型 #{birthday,jdbcType=DATE},这样可以明确你的数据类型,但是这个没有自动提示,使用insert标签
1 2 3 4 5 6 <insert id ="insert1" parameterType ="com.ls.mybatis.model.User" > insert into user (uid, username, birthday, phone, sex, address) values (#{uid},#{username},#{birthday,jdbcType=DATE},#{phone},#{sex},#{address}) </insert >
测试类:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 @Test public void insert01 () throws IOException { String resource = "mybatis-config.xml" ; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); User user = new User(); user.setUid(14 ); user.setUsername("傻逼1" ); user.setBirthday(new Date()); user.setPhone("1231asd321" ); user.setSex("2" ); user.setAddress("江苏江苏" ); sqlSession.insert("test.insert1" , user); sqlSession.commit(); sqlSession.close(); }
如果需要返回主键(没错,就是返回你的主键数值)有三个前提
useGeneratedKeys=”true” keyProperty=”uid” 主键自增
之后可以直接通过类名调用
1 System.out.println("---------- " +user.getUid()+" -----------" );
更新数据 这里的update数据 我传入的是map类型的集合,也可以用别的方式,比如传入一个实体类,使用update标签
1 2 3 4 5 <update id ="update1" parameterType ="map" > update user set username=#{u1} where uid=#{u2} </update >
测试类:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 @Test public void update01 () throws IOException { String resource = "mybatis-config.xml" ; InputStream resourceAsStream = Resources.getResourceAsStream(resource); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream); HashMap<String, Object> stringObjectHashMap = new HashMap<String, Object>(); stringObjectHashMap.put("u1" , "傻逼2" ); stringObjectHashMap.put("u2" , "14" ); SqlSession sqlSession = build.openSession(); sqlSession.update("test.update1" , stringObjectHashMap); sqlSession.commit(); sqlSession.close(); }
删除数据 删除一般只需要指定一个参数,所以就直接随便写一个参数名就行,使用delete标签
1 2 3 <delete id ="delete01" > delete from user where uid=#{suibian} </delete >
实体类:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 @Test public void delete01 () throws IOException { String resource = "mybatis-config.xml" ; InputStream resourceAsStream = Resources.getResourceAsStream(resource); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = build.openSession(); sqlSession.delete("test.delete01" , "14" ); sqlSession.commit(); sqlSession.close(); }
片段操作 由于有的时候选择的字段特别多,挨个写特别麻烦,可以单独提取出来, 最好置于mapper 标签中 最顶部,sql标签
1 2 3 4 5 6 7 8 <sql id ="alluse" > uid, username, birthday, phone, sex, address </sql >
查询语句:
1 2 3 4 5 6 <select id ="selectOne" parameterType ="int" resultType ="com.ls.mybatis.model.User" > SELECT <include refid ="alluse" > </include > FROM `user` WHERE uid = #{id} </select >
这里只是sql查询有些许变动,测试类不需要更改
字段映射 有的时候,你的实体类中的字段与数据库中的字段不一样,但是你还不想更改使他们变得一样,这时候就需要用到字段映射了。需要用到resultMap,与resultType注意区分,这个是用来将字段映射成对应字段的
例子:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 <select id ="listOrderUser02" resultMap ="listOUS" > SELECT order_id, user_id, order_price, payment, state, order_createtime, note FROM orders </select > <resultMap id ="commenmap" type ="orders" > <id property ="orderId" column ="order_id" > </id > <result property ="userId" column ="user_id" javaType ="int" jdbcType ="INTEGER" > </result > <result property ="orderPrice" column ="order_price" > </result > <result property ="payment" column ="payment" > </result > <result property ="state" column ="state" > </result > <result property ="orderCreatetime" column ="order_createtime" > </result > <result property ="note" column ="note" > </result > </resultMap >
定义一个resultMap标签,并且定义id,然后再需要映射的查询语句上添加resultMap属性,然后指定刚刚resultMap定义的id,就会引用指定的resultMap映射了
动态查询 用来进行 类似java代码式的sql语句书写,一般用来添加可能会出现的判断条件
if: 1 2 3 4 5 6 7 8 9 10 11 12 13 <select id ="selectByNameAndBirthday2" resultType ="user" > select <include refid ="baseColumn" > </include > from `user` where <if test ="username != null and username != ''" > username like #{username} </if > <if test ="birthday != null" > AND birthday = #{birthday} </if > </select >
如果用户输入了username、birthday,则该属性值!=null,拼接对应的查询SQL,否则不拼接
注意:
if判断中,条件使用 and 、or拼接,不能使用 & | AND OR
非字符串类型不要进行空串比较,pro != ‘’,因为会先将pro强制转为字符串
where: 上面的代码是有漏洞的,用户只有输入username属性都可以保证sql运行不会出问题,如:
1 SELECT uid,username,birthday,phone,sex,address FROM `user ` WHERE username = ?
否则 只输入birthday:
1 SELECT uid,username,birthday,phone,sex,address FROM `user ` WHERE AND birthday = ?
或者 什么都不输入:
1 SELECT uid,username,birthday,phone,sex,address FROM `user ` WHERE
这样会 语法报错,虽然可以通过继续写if 判断来避免,但是这里我们还有别的办法,有where才会加上where 并且自动判断加and
1 2 3 4 5 6 7 8 9 10 11 12 < select id= "selectByNameAndBirthday2" resultType= "user"> select < include refid= "baseColumn"> < / include> from `user ` < where > < if test= "username != null and username != ''"> username like #{username} < / if> < if test= "birthday != null"> AND birthday = #{birthday} < / if> < / where > < / select >
choose: choose相当于java中的switch
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 < select id= "dynamicSQLChoose" resultType= "user"> select < include refid= "baseColumn"> < / include> from `user ` < where > < choose> < when test= 'sex != null and sex == "男"' > and sex = '1' < / when > < when test= 'sex != null and sex == "女"' > and sex = '2' < / when > < otherwise> and sex is not null < / otherwise> < / choose> < / where > < / select >
这里必须注意:当比较的值只有一个字符时,mybatis会把值当成char处理,转成数值,汉字是无法直接转成数值的,因此这里要用双引号,表示是字符串
foreach: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 < ! SELECT uid,username,birthday,sex,addressFROM `user `WHERE uid IN (1 ,10 ,12 ,16 )collection: 遍历的集合类型 arrar - > 数值 list - > list collection - > set item ; 变量名,可以自定义,表示遍历的每一项的值 open : 开始内容separator: 分隔符 close : 结束内容< select id= "dynamicSQLForeach" resultType= "user"> select uid,username,birthday,sex,address from `user ` < if test= "collection != null"> < where > < foreach collection= "collection" item= "item" open = "uid IN (" separator= "," close = ")"> #{item} < / foreach> < / where > < / if> < / select >
这里的测试类传入封装对象:
1 2 3 4 HashSet<Integer> set = new HashSet<>(); set.add(1 ); set.add(10 ); set.add(12 );
MyBatis中在查询进行select映射的时候,返回类型可以用resultType,也可以用resultMap,resultType是直接表示返回类型的,而resultMap则是对外部ResultMap的引用dao,但是resultType跟resultMap不能同时存在。
当查询结果有多个列,并且列名和字段名称不一致时,mybatis无法将结果封装到model对象中,这是需要通过resultMap绑定列和属性关系