CRUD
即增删改查
1 .修改Category.xml文件即可
<?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">
<insert id="addCategory" parameterType="Category" >
/*parameterType 为输入参数,配置的时候,配置相应的输入参数
即可*/
/*addCategory对应的插入sql语句,#{name}会自动获取c对象的
name属性值*/
insert into category_ ( name ) values (#{name})
</insert>
<delete id="deleteCategory" parameterType="Category" >
delete from category_ where id= #{id}
</delete>
<select id="getCategory" parameterType="_int"
/*_int为mybatis内置别名,对应的类型为int,其他比如_float
对应float*/
resultType="Category">
select * from category_ where id= #{id}
</select>
<update id="updateCategory" parameterType="Category" >
update category_ set name=#{name} where id=#{id}
</update>
<select id="listCategory" resultType="Category">
select * from category_
</select>
</mapper>
增加
TestMyBatis.java
...
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.setName("新增的category");
session.insert("addCategory",c);
/*addCategory为Category.xml中设置的id,
c为ParamType的Category类型*/
listAll(session);
session.commit();
session.close();
public static void listAll(SqlSession session){
List<Category> cs = session.selectList
("listCategory");
for (Category c : cs) {
System.out.println(c.getName());
}
}
}
删除
删除id=6的对象
Category c = new Category();
c.setId(6);
session.delete("deleteCategory",c);
deleteCategory对应删除的sql语句
<delete id="deleteCategory" parameterType="Category" >
delete from category_ where id= #{id}
</delete>
查询单个
通过session.selectOne获取id=3的记录
Category c= session.selectOne("getCategory",3);
getCategory对应的sql语句:
/*查询的传递int型数据,返回的是Category类型相关的数据*/
<select id="getCategory" parameterType="_int"
resultType="Category">
select * from category_ where id= #{id}
</select>
修改
通过session.update进行修改
session.update("updateCategory",c);
updateCategory对应的sql语句:
<update id="updateCategory" parameterType="Category" >
update category_ set name=#{name} where id=#{id}
</update>
更多查询
- 模糊查询
1. 修改Category.xml,提供listCategoryByName查询语句
select * from category_ where name like concat('%',#{0},'%')
concat('%',#{0},'%') 这是mysql的写法
如果是oracle,写法是
select * from category_ where name like '%'||#{0}||'%'
2. 运行测试
List<Category> cs = session.selectList
("listCategoryByName","cat");
2 . 多条件查询
结合前面的模糊查询,多一个id>多少的条件
1. Category.xml 准备sql语句
<select id="listCategoryByIdAndName"
parameterType="map" resultType="Category">
select * from category_ where id> #{id}
and name like concat('%',#{name},'%')
</select>
2. 测试代码
因为是多个参数,而selectList方法又只接受一个参数对象,
所以需要把多个参数放在Map里,然后把这个Map对象作为参数传递进去
Map<String,Object> params = new HashMap<>();
params.put("id", 3);
params.put("name", "cat");
List<Category> cs =
session.selectList("listCategoryByIdAndName",params);
总结:
1 . 通过设置实体类对应的xml文件,预先存储需要使用的SQL语句,在需要的时候使用可以简化代码。