一、基于MyBatis的CRUD
1、首先是配置文件Category.xml修改
一次性修改配置文件Category.xml,提供CRUD对应的sql语句。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.rog//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.demo.pojo"> <insert id="addCategory" parameter Type="Category"> insert into category_table (name) values (#{name}) </insert> <delete id="deleteCategory" parameter Type="Category"> delete from category_table where id=#{id} </delete> <select id="getCategory" parameter Type="Category"> select * from category_table where id=#{id} </select> <update id="updateCategory" parameter Type="Category"> update category_table set name=#{name} where id=#{id} </update> <select id="listCategory" resultType="Category"> select * from category_table </select> </mapper>
2、增加(insert)
通过session.insert()方法调用addCategory对应的SQL语句
<insert id="addCategory" parameter Type="Category"> insert into category_table (name) values (#{name}) </insert>
Category c=new Category(); c.setName("插入一条Category数据"); session.insert("addCategory",c);
3、删除(delete)
通过session.delete()方法调用deleteCategory对应的SQL语句
<delete id="deleteCategory" parameterType="Category"> delete from category_table where id=#{id} </delete>
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.demo.java; import java.io.IOException; import java.io.InputStream; import java.uitl.List; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import com.demo.pojo.Category; public class TestMyBatis{ public static void main(String[] args) throws IOException{ String resource="mybatis-config.xml"; InputStream inputStream=Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder();.build(inputStream); SqlSession session=sqlSessionFactory.openSession(); Category c=new Category(); c.setId(6); session.delete("deleteCategory",c); listAll(session); session.commit(); session.close(); } private static void listAll(SqlSession session){ List<Category> list=session.selectList("listCategory"); for(Category c:cs){ System.out.println(c.getName()); } } }
4、修改(update)
通过session.update()方法调用updateCategory对应的SQL语句
<update id="updateCategory" parameterType="Category"> update category_table set name=#{name} where id=#{id} </update>
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.demo.java; import java.io.IOException; import java.io.InputStream; import java.uitl.List; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import com.demo.pojo.Category; public class TestMyBatis{ public static void main(String[] args) throws IOException{ String resource="mybatis-config.xml"; InputStream inputStream=Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder();.build(inputStream); SqlSession session=sqlSessionFactory.openSession(); Category c=session.selectOne("getCategory",3); c.setName("修改了Category名称"); session.update("updateCategory",c); listAll(session); session.commit(); session.close(); } private static void listAll(SqlSession session){ List<Category> list=session.selectList("listCategory"); for(Category c:cs){ System.out.println(c.getName()); } } }
5、模糊查询(like)
修改Category.xml,提供listCategoryByName查询语句
mysql的写法是如下:利用concat('%',#{0},'%')
<select id="listCategoryByName" parmaeterType="string" resultType="Category"> select * from category_table where name like concat('%',#{0},'%') </select>
oracle的写法如下:'%'||#{0}||'%'
select * from category_table where name like '%'||#{0}||'%'
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<?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="com.how2java.pojo"> <select id="listCategoryByName" parameterType="string" resultType="Category"> select * from category_ where name like concat('%',#{0},'%') </select> </mapper>
测试代码如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.demo.java; import java.io.IOException; import java.io.InputStream; import java.uitl.List; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import com.demo.pojo.Category; public class TestMyBatis{ public static void main(String[] args) throws IOException{ String resource="mybatis-config.xml"; InputStream inputStream=Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder();.build(inputStream); SqlSession session=sqlSessionFactory.openSession(); List<Category> cs=session.selectList("listCategoryByName","hello"); for(Category c:cs){ system.out.println(c.getName()); } session.commit(); session.close(); } }
6、多条件查询
同样还是修改Category.xml配置文件
<select id="listCategoryByIdAndName" parameterType="map" resultType="Category"> select * from category_table where id>#{id} and name like concat('%',#{name},'%') </select>
因为有多个参数,而selectList方法只能结构一个参数对象,所以需要把多个参数放在map里,然后把这个map对象作为参数传递进去
Map<String,Object> params=new HashMap<>(): params.put("id",3); params.put("name","hello"); List<Category> cs=session.selectList("listCategoryByIdAndName",params);
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.demo.java; import java.io.IOException; import java.io.InputStream; import java.uitl.List; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import com.demo.pojo.Category; public class TestMyBatis{ public static void main(String[] args) throws IOException{ String resource="mybatis-config.xml"; InputStream inputStream=Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder();.build(inputStream); SqlSession session=sqlSessionFactory.openSession(); Map<String,Object> params=new HashMap<>(); params.put("id",3); params.put("name","cat"); List<Category> cs=session.selectList("listCategoryByIdAndName",params); for(Category c:cs){ system.out.println(c.getName()); } session.commit(); session.close(); } }