①查询出所有的记录
List<Player> selectAll();
<select id="selectAll" resultType="Player"> select * from Player </select>
②根据主键查询
Player selectById(Integer id);
<select id="selectById" parameterType="Integer" resultType="Player"> select * from Player where id=#{id} </select>
③修改
int update(Player player);
<update id="update" parameterType="Player"> update Player set name=#{name},city=#{city} where id=#{id} </update>
④根据主键删除
int deleteById(Integer id);
<delete id="deleteById"> delete from Player where id=#{id} </delete>
⑤增加
int insert(Player player);
<insert id="insert" parameterType="Player"> <selectKey keyProperty="id" order="BEFORE" resultType="integer"> select seq_play.nextval from dual </selectKey> insert into Player(id,name,city) values(#{id},#{name},#{city}) </insert>
⑥查询出所有字段（orderColumn：指定排序列）
List<Player> selectAlls(String orderColumn);
<select id="selectAlls" resultType="Player"> select * from Player order by ${value} </select>
⑦根据id或name查找数据，演示多个参数的取值方式，通过@param取别名
List<Player> selectByIdOrName(@Param("id") Integer id,@Param("name") String name,@Param("city") String city);
<select id="selectByIdOrName" resultType="Player"> select * from Player <where> <choose> <when test="id!=null"> id=#{id} </when> <when test="name!=null"> name=#{name} </when> <otherwise> city=#{city} </otherwise> </choose> </where> </select>
⑧参数传递的是map时，#{key}取value
List<Player> selectMap(Map<String,Object> map);
<select id="selectMap" parameterType="java.util.Map" resultType="Player"> select * from Player <where> <if test="id!=null"> id=#{id} </if> <if test="name!=null and name!=‘‘"> or name=#{name} </if> <if test="city!=null"> or city=#{city} </if> </where> </select>
⑨批量删除 传递数组/集合
int deleteByIds(Integer[] ids);
<delete id="deleteByIds"> delete from Player where id in <foreach collection="array" item="id" open="(" close=")" separator=","> #{id} </foreach> </delete>
⑩批量插入
int batchInsert(List<Player>playerList);
<insert id="batchInsert" parameterType="java.util.List"> begin <foreach collection="list" item="player"> insert into Player(id,name,city) values (seq_play.nextval,#{player.name},#{player.city}); </foreach> commit ; end; </insert>
11、选择性更新
int updateSelective(Player player);
<update id="updateSelective" parameterType="Player"> update Player <set> <if test="name!=null"> name=#{name}, </if> <if test="city!=null"> city=#{city} </if> </set> where id=#{id} </update>
12、选择性插入数据
int insertSelective(Player player);
<insert id="insertSelective" parameterType="Player"> <selectKey keyProperty="id" resultType="integer" order="BEFORE"> select seq_play.nextval from dual </selectKey> insert into Player <trim prefix="(" suffix=")" suffixOverrides=","> id, <if test="name!=null">name,</if> <if test="city!=null">city</if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> #{id}, <if test="name!=null">#{name},</if> <if test="city!=null">#{city}</if> </trim> </insert>
13、根据名称进行模糊查询
List<Player> selectByName(@Param("name") String name);
<select id="selectByName" resultType="Player"> <bind name="nameLike" value="‘%‘+name+‘%‘"/> select * from Player where name like #{nameLike} </select>
以下为上面方法的测试，仅供参考
@Test public void test() throws IOException { InputStream is= Resources.getResourceAsStream("mybatis.xml"); SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is); SqlSession sqlSession=sqlSessionFactory.openSession(); PlayerMapper mapper=sqlSession.getMapper(PlayerMapper.class); System.out.println("==========查询所有的记录==========="); List<Player> playerList=mapper.selectAll(); for (Player player:playerList){ System.out.println(player); } System.out.println("==========根据主键查询记录============="); Player player = mapper.selectById(2); System.out.println(player); System.out.println("=============修改记录============="); player.setName("汤普森"); player.setCity("金州勇士"); int update = mapper.update(player); System.out.println(update); System.out.println("============根据主键删除============"); int i = mapper.deleteById(4); System.out.println(i); System.out.println("============插入数据==========="); //Player player1=new Player("一哥","金州勇士"); //int i1 = mapper.insert(player1); //System.out.println(i1); System.out.println("===========根据指定序列查找所有字段================"); List<Player> playerList1 = mapper.selectAlls("id"); for (Player player2:playerList1){ System.out.println(player2); } System.out.println("==========根据id或name查找数据==============="); List<Player> playerList2 = mapper.selectByIdOrName(1, "乔丹", "芝加哥"); for (Player player2:playerList2){ System.out.println(playerList2); } List<Player> playerList3 = mapper.selectByIdOrName(null, null, "金州勇士"); for (Player player2:playerList3){ System.out.println(playerList3); } System.out.println("============测试传递map============"); HashMap<String,Object> map=new HashMap<>(); map.put("id",3); map.put("name","科比"); map.put("city","洛杉矶"); List<Player> playerList4 = mapper.selectMap(map); for (Player player2:playerList4){ System.out.println(player2); } System.out.println("=========批量删除==========="); int i1 = mapper.deleteByIds(new Integer[]{13,15}); System.out.println(i1); System.out.println("==========批量增加==========="); //List<Player> playerList5= Arrays.asList( //new Player("字母哥","快船"), //new Player("麦迪","火箭"), //new Player("哈登","火箭") //); //int i2 = mapper.batchInsert(playerList5); //System.out.println(i2); System.out.println("=========选择性更新==========="); Player player1=mapper.selectById(2); player1.setName("库里"); player1.setCity("金州幼儿园"); int i2 = mapper.updateSelective(player1); System.out.println(i2); Player player2=mapper.selectById(18); player2.setName("库里"); player2.setCity(null); int i3 = mapper.updateSelective(player2); System.out.println(i3); System.out.println("==========选择性增加数据=========="); Player player3=new Player(); player3.setName(null); player3.setCity("猛龙"); int i4 = mapper.insertSelective(player3); System.out.println(i4); Player player4=new Player(); player4.setName("奥尼尔"); player4.setCity(null); int i5 = mapper.insertSelective(player4); System.out.println(i5); System.out.println("=========根据名字模糊查询=========="); String name="母"; List<Player> playerList5 = mapper.selectByName(name); for (Player player5:playerList5){ System.out.println(player5); } sqlSession.commit(); sqlSession.close(); is.close(); }
原文：https://www.cnblogs.com/xie-qi/p/12907405.html