3、CRUD

1、namespace

namespace中的名称需要和定义的接口名称一致

public interface GoodsMapper {
    //查询全部商品
    List<Goods> getGoodsList();
    //更具id查询商品
    Goods getGoodsById(String id);
}

<mapper namespace="com.liulei.dao.GoodsMapper">
    <!--查询语句-->
    <select id="getGoodsList" resultType="com.liulei.pojo.Goods">
        select * from goods
    </select>

    <select id="getGoodsById" parameterType="String" resultType="com.liulei.pojo.Goods">
        select * from hfuuls.goods where goodsid = #{id}
    </select>
</mapper>

//查看
@Test
public void test(){
    //获得SQLsession对象
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    //执行
    GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
    List<Goods> list = mapper.getGoodsList();
    for (Goods g:list) {
        System.out.println(g.toString());
    }
    sqlSession.close();
}
//更具id查询
@Test
public void getGoodsByid(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
    System.out.println(mapper.getGoodsById("goods_045ed210-f0c0-426e-86b9-d571c023c7e6"));
    sqlSession.close();
}

2、select

• 选择，查询语句
  属性简介
  – id: 对应namespace对应接口中的方法名
  – resultType：sql的返回值类型（需要写全类名）
  – parameterType：参数类型

3、insert

• 编写接口

//GoodsMapper.java

int addGoods(Goods goods);

• 编写mapper

//GoodsMapper.xml
<insert id="addGoods" parameterType="com.liulei.pojo.Goods">
    insert into hfuuls.goods(goodsid, goodsname, goodsintro, goodscount, goodsurl, goodsprice, businessid, goodstype) values (#{goodsid},#{goodsname},#{goodsintro},#{goodscount},#{goodsurl},#{goodsprice},#{businessid},#{goodstype})
</insert>

• 编写测试代码

//GoodsDaoTest.java

//增删改需要提交时事务
@Test
public void addGoods(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
    Goods goods = new Goods("1111","11","11",11,"11",11.1,"1",1);
    int res =  mapper.addGoods(goods);
    if (res >= 1){
        sqlSession.commit();
    }else{
        sqlSession.rollback();
    }
    sqlSession.close();
}

遇到的错误：
插入时有外键限制
Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (hfuuls.goods, CONSTRAINT fk_businessid FOREIGN KEY (businessid) REFERENCES business (businessid))
此时项目中存在外键限制需要修改插入的外键值或查看外键值是正确

注意：
增、删、改需要提交事务
sqlSession.commit();
sqlSession.rollback();

4、update

• 编写接口

//修改
int updateGoods(Goods goods);

• 添加映射配置mapper

<!--修改-->
<update id="updateGoods" parameterType="com.liulei.pojo.Goods">
    update hfuuls.goods set goodsname=#{goodsname},goodsintro=#{goodsintro} where goodsid=#{goodsid}
</update>

• 测试

//修改
@Test
public void updateGoods(){
    SqlSession session = MyBatisUtils.getSqlSession();
    GoodsMapper mapper = session.getMapper(GoodsMapper.class);
    Goods goods = new Goods("1111","好丽友蛋黄派","好丽友",11,"11",11.1,"1",1);
    if (mapper.updateGoods(goods) >= 1){
        session.commit();
    }else{
        session.rollback();
    }
    session.close();
}

5、delete

• 编写接口

//删除
int deleteGoods(String id);

• 添加映射配置mapper

<!--删除-->
<delete id="deleteGoods" parameterType="String">
    delete from hfuuls.goods where goodsid = #{id}
</delete>

• 测试

@Test
public void deleteGoods(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
    if (mapper.deleteGoods("1111") >= 1){
        sqlSession.commit();
    }else{
        sqlSession.rollback();
    }

    sqlSession.close();
}

6、Map类型的值

这里将和普通的插入方式进行对比。假设我们的实体类或者数据库中的表，字段或者参数很多的时候。而我们又仅仅需要插入（或修改）部分的啥时候，我们可以考虑使用map来选择插入（或修改）

• 编写接口

//插入
int addGoods(Goods goods);
//使用Map进行传值
int addGoods1(Map<String,Object> map);

• 编写mapper

<!--在下面可以直接取出对象中的属性-->
<insert id="addGoods" parameterType="com.liulei.pojo.Goods">
    insert into hfuuls.goods(goodsid, goodsname, goodsintro, goodscount, goodsurl, goodsprice, businessid, goodstype) values (#{goodsid},#{goodsname},#{goodsintro},#{goodscount},#{goodsurl},#{goodsprice},#{businessid},#{goodstype})
</insert>
<!--使用map进行传值-->
<insert id="addGoods1" parameterType="map">
    insert into hfuuls.goods(goodsid, goodsname, goodsintro, goodscount, goodsurl, goodsprice, businessid, goodstype) values (#{goodsid},#{goodsname},#{goodsintro},#{goodscount},#{goodsurl},#{goodsprice},#{businessid},#{goodstype})
</insert>

• 编写测试代码

//通过对象传值
@Test
public void addGoods(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
    Goods goods = new Goods("1111","11","11",11,"11",11.1,"1",1);
    int res =  mapper.addGoods(goods);
    if (res >= 1){
        sqlSession.commit();
    }else{
        sqlSession.rollback();
    }
    sqlSession.close();
}
//使用map进行传值
@Test
public void addGoods1(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
    //Goods goods = new Goods("1111","11","11",11,"11",11.1,"1",1);
    HashMap<String,Object> map = new HashMap<String,Object>();
    //此处添加的内容只需要将对应的键值和mapper中的#{***}一致即可
    map.put("goodsid","1111");
    map.put("goodsname","11");
    map.put("goodsintro","11");
    map.put("goodscount",11);
    map.put("goodsurl","http://");
    map.put("goodsprice",11.11);
    map.put("businessid","1");
    map.put("goodstype",11);
    int res = mapper.addGoods1(map);
    if (res >= 1){
        sqlSession.commit();
    }else{
        sqlSession.rollback();
    }
    sqlSession.close();
}

7、模糊查询

• 编写接口函数

//模糊查寻
Goods getGoodsLike(String goodsname);

• 编写mapper

<!--模糊查询-->
<select id="getGoodsLike" parameterType="String" resultType="com.liulei.pojo.Goods">
    select * from hfuuls.goods where goodsname like #{goodsname}
</select>

• 编写测试代码

//模糊查询
@Test
public void getGoodsLike(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);
    System.out.println(mapper.getGoodsLike("%香%"));
    sqlSession.close();
}

注：在传入字符时最好使用System.out.println(mapper.getGoodsLike("%香%"))方式。可以避免sql注入的问题