二、使用
1.增删改查
- 增
-
1. <!--增加用户--> <insert id="insertUser" parameterType="cn.chen.mybatis.pojo.User"> insert into user (username, birthday, sex, address) values(#{username},#{birthday},#{sex},#{address}) </insert> 2. int insertUser(User user)throws Exception; 3. @Test public void insertUser() throws Exception { SqlSession sqlSession = MyBatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); user.setUsername("宝宝1"); user.setAddress("北京海淀区1"); int row = mapper.insertUser(user); System.out.println(row); sqlSession.commit(); sqlSession.close(); }
-
- 删
-
1. <!--删除用户--> <delete id="deleteUserById" parameterType="int"> delete from user where user.id=#{id} </delete> 2.int deleteUserById(int id)throws Exception; 3. @Test public void deleteUser() throws Exception { SqlSession sqlSession = MyBatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int row = mapper.deleteUserById(38); System.out.println(row); sqlSession.commit(); sqlSession.close(); }
-
- 改
-
1. <!--修改用户--> <update id="updateUserById" parameterType="cn.chen.mybatis.pojo.User"> update user set username=#{username},sex=#{sex} where id=#{id} </update> 2.int updateUserById(User user)throws Exception; 3. @Test public void updateUser() throws Exception { SqlSession sqlSession = MyBatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); user.setId(37); user.setUsername("花花"); user.setSex("1"); int row = mapper.updateUserById(user); System.out.println(row); sqlSession.commit(); sqlSession.close(); }
-
- 问题
-
1. namespace后面包名之间用"."相隔 2. 标签和使用的sql语句功能要一致 3. 在核心配置文件中mappers注册中要用"/"相隔 4. xml文件里面不要有中文乱码 5. maven导出错误,需要在maven中buildresource
-
-
模糊查询和Map集合使用
- map
-
1. <!--使用map传递--> <insert id="insertUserMap" parameterType="map"> insert into user (username, birthday, sex, address) values(#{name},#{date},#{gender},#{address}) </insert> 2.int insertUserMap(Map<String,Object> map)throws Exception; 3. @Test public void insetUserMap() throws Exception { SqlSession sqlSession = MyBatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); Map<String,Object> map = new HashMap<>(); map.put("name","张三"); map.put("date",new Date()); map.put("gender","0"); map.put("address","上海"); int row = mapper.insertUserMap(map); System.out.println(row); sqlSession.commit(); sqlSession.close(); }
适用情况:(存入和取出都较少时)
在字段名比较多,又增加内容字段比较少时
好处:
不需要与字段名相匹配,只需要键与xml中名字相匹配
-
-
模糊查询
-
有sql注入风险
1. <select id="findUserLike" parameterType="string" resultType="cn.chen.mybatis.pojo.User"> select * from user where username like '%${value}%' </select> 2.List<User> findUserLike(String string)throws Exception; 3. @Test public void findUserLike() throws Exception { SqlSession sqlSession = MyBatisUtils.getSqlSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> users = mapper.findUserLike("小"); for (User user : users) { System.out.println(user); } sqlSession.close(); }
-
没有sql注入风险
1. <select id="findUserLike" parameterType="string" resultType="cn.chen.mybatis.pojo.User"> select * from user where username like "%"#{vaule}"%" </select>
-
好处
1.没有sql注入情况 2.可以使得sql语句更加安全 3.比上一种方式更靠谱
-
-
配置解析
-
核心配置文件
必须要按照以下顺序进行配置,不按照配置会直接报错
-
环境配置
-
mybatis可以配置成适应的多种环境
不过注意,尽管可以配置多个,但是每个SqlSessionFacotory实例只能选一种环境
-
-
属性
-
配置一个jdbc.properties文件
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mybatis jdbc.username=root jdbc.password=root
-
在核心配置文件中配置properties文件
<properties resource="jdbc.properties"></properties> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <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>
-
还可以增加字段属性
1. jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mybatis 2. <properties resource="jdbc.properties"> <property name="username" value="root"></property> <property name="password" value="root"></property> </properties> 3.<environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <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>
-
别名
-
使用type属性(type放入所起别名的类,alias放入所想起的别名)
1.在核心配置文件中写入 <!--使用别名--> <typeAliases> <typeAlias type="cn.chen.mybatis.pojo.User" alias="User"></typeAlias> </typeAliases> 2.在映射文件.xml中 <select id="findAll" resultType="User"> select * from user </select> 3.使用
-
使用package属性(在name中放入想要起别名的类的上一级包名,会默认为是首字母小写)
1.在核心配置文件中写入 <typeAliases> <package name="cn.chen.mybatis.pojo"></package> </typeAliases> 2.在映射文件.xml中 <select id="findAll" resultType="user"> select * from user </select> 3.使用
-
注解起别名(在实体类上面加注解)
-
-
设置
-
映射器(mapper绑定映射的三种方式)
-
生命周期和作用域
-
-
-
ResultMap结果集
1.用来解决字段名不一致的情况
2.可以防止数据中字段名和实体类中的属性名不一致情况 -
日志工厂
如果数据库操作中出现了异常,日志可以很好的帮助找到问题并解决 -
分页
使用方法select * from user limit 0,#{[0,n]}; 逗号后面的可以不写,不写默认查到最后一位。
RowBounds分页
1.在接口配置文件中 <resultMap id="findUserById" type="user"> <id column="id" property="id"></id> <result column="username" property="username"></result> <result column="sex" property="sex"></result> <result column="address" property="address"></result> </resultMap> <!--使用rowBounds分页--> <select id="findRowBounds" resultMap="findUserById"> select * from user </select> 2.接口中 List<User> findRowBounds()throws Exception; 3.在测试类中 @Test public void testRowBounds() throws Exception { RowBounds rowBounds = new RowBounds(1, 5); SqlSession sqlSession = MyBatisUtils.getSqlSession(); List<User> list = sqlSession.selectList("cn.chen.mybatis.mapper.UserMapper.findRowBounds", null, rowBounds); for (User user : list) { System.out.println(user); } sqlSession.close(); } 4.cn.chen.mybatis.mapper.UserMapper.findRowBounds 调用接口中的方法,直接调用方式
-
注解方式写mybatis
不需要接口配置文件 在接口中,方法的上方加入注解和sql语句@Select("select * from user") List<User> findAll()throws Exception;
修改核心配置文件中的映射绑定,由接口配置文件绑定改为接口类绑定
前: <mappers> <mapper resource="cn/chen/mybatis/mapper/UserMapper.xml"/> </mappers> 后: <mappers> <mapper class="cn.chen.mybatis.mapper.UserMapper"></mapper> </mappers>
-
动态SQL
- 什么是动态sql?
- 动态SQL指的是根据不同的查询条件 , 生成不同的Sql语句
- 怎么解决
- 使用 mybatis 动态SQL,通过 if, choose, when, otherwise, trim, where, set, foreach等标签,可组合成非常灵活的SQL语句,从而在提高 SQL 语句的准确性的同时,也大大提高了开发人员的效率。
- if语句
//需求1 List<Blog> queryBlogIf(Map map); <!--需求1: 根据作者名字和博客名字来查询博客! 如果作者名字为空,那么只根据博客名字查询,反之,则根据作者名来查询 select * from blog where title = #{title} and author = #{author} --> <select id="queryBlogIf" parameterType="map" resultType="blog"> select * from blog where <if test="title != null"> title = #{title} </if> <if test="author != null"> and author = #{author} </if> </select> @Test public void testQueryBlogIf(){ SqlSession session = MybatisUtils.getSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap<String, String> map = new HashMap<String, String>(); map.put("title","Mybatis简单"); map.put("author","chen"); List<Blog> blogs = mapper.queryBlogIf(map); System.out.println(blogs); session.close(); }
- where
<select id="queryBlogIf" parameterType="map" resultType="blog"> select * from blog <where> <if test="title != null"> title = #{title} </if> <if test="author != null"> and author = #{author} </if> </where> </select>
- set
<!--注意set是用的逗号隔开--> <update id="updateBlog" parameterType="map"> update blog <set> <if test="title != null"> title = #{title}, </if> <if test="author != null"> author = #{author} </if> </set> where id = #{id}; </update> @Test public void testUpdateBlog(){ SqlSession session = MybatisUtils.getSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap<String, String> map = new HashMap<String, String>(); map.put("title","动态SQL"); map.put("author","chen"); map.put("id","9d6a763f5e1347cebda43e2a32687a77"); mapper.updateBlog(map); session.close(); }
- choose
List<Blog> queryBlogChoose(Map map); <select id="queryBlogChoose" parameterType="map" resultType="blog"> select * from blog <where> <choose> <when test="title != null"> title = #{title} </when> <when test="author != null"> and author = #{author} </when> <otherwise> and views = #{views} </otherwise> </choose> </where> </select> @Test public void testQueryBlogChoose(){ SqlSession session = MybatisUtils.getSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap<String, Object> map = new HashMap<String, Object>(); map.put("title","Java"); map.put("author","chen"); map.put("views",9999); List<Blog> blogs = mapper.queryBlogChoose(map); System.out.println(blogs); session.close(); }
- sql片段
<sql id="if-title-author"> <if test="title != null"> title = #{title} </if> <if test="author != null"> and author = #{author} </if> </sql> <select id="queryBlogIf" parameterType="map" resultType="blog"> select * from blog <where> <!-- 引用 sql 片段,如果refid 指定的不在本文件中,那么需要在前面加上 namespace --> <include refid="if-title-author"></include> <!-- 在这里还可以引用其他的 sql 片段 --> </where> </select>
- forEach
List<Blog> queryBlogForeach(Map map); <select id="queryBlogForeach" parameterType="map" resultType="blog"> select * from blog <where> <!-- collection:指定输入对象中的集合属性 item:每次遍历生成的对象 open:开始遍历时的拼接字符串 close:结束时拼接的字符串 separator:遍历对象之间需要拼接的字符串 select * from blog where 1=1 and (id=1 or id=2 or id=3) --> <foreach collection="ids" item="id" open="and (" close=")" separator="or"> id=#{id} </foreach> </where> </select> @Test public void testQueryBlogForeach(){ SqlSession session = MybatisUtils.getSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap map = new HashMap(); List<Integer> ids = new ArrayList<Integer>(); ids.add(1); ids.add(2); ids.add(3); map.put("ids",ids); List<Blog> blogs = mapper.queryBlogForeach(map); System.out.println(blogs); session.close(); }
- 什么是动态sql?
- map
关于mybatis的缓存问题,关注私信我,有视频教程、源码还有md文档以上笔记