mybatis动态sql增删改和分组查询

动态sql实现增删改和分组查询

使用mybatis实现增删改，我们先使用mybatis实现添加数据，关键代码如下:

//新增
// 创建xml路径
		String pathString = "Banana-mybatis.xml";
		// 创建xml输入流
		try {
			InputStream is = Resources.getResourceAsStream(pathString);
			// 创建一个sqlsessionfatory
			SqlSessionFactory factory = new SqlSessionFactoryBuilder()
					.build(is);
			// 创建一个session工厂
			SqlSession session = factory.openSession();
			
			BananaMapper sm=session.getMapper(BananaMapper.class);
			Banana banana=new Banana(3, "香蕉", 19, "黑色", 18);
			int count=sm.insert(banana);
			if(count>0){
				System.out.println("添加香蕉成功！");
				session.commit();
			}else {
				System.out.println("添加香蕉失败！");
				session.rollback();
			}
			} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

实现接口类如下:
public int insert(Banana banana);
实体类的话就不要多说了，大家也知道敲啦！然后我们来查看我们的xml文件里面的查询语句

 <!-- 新增香蕉信息 -->
  <insert id="insert" parameterType="ShiTi.Banana">
  insert into Banana(bid,bname,bprice,bcolor,bsize) values(#{bid},#{bname},#{bprice},#{bcolor},#{bsize})
  </insert>

添加数据的话实在有点容易哦！
修改数据的xml文件如下:

<!-- 修改香蕉信息 -->
  <update id="xiugai" parameterType="ShiTi.Banana">
  	update banana SET bname=#{bname} where bid=#{bid}
  </update>

接口:public int xiugai(Banana s);
实现类:

//新增
// 创建xml路径
		String pathString = "Banana-mybatis.xml";
		// 创建xml输入流
		try {
			InputStream is = Resources.getResourceAsStream(pathString);
			// 创建一个sqlsessionfatory
			SqlSessionFactory factory = new SqlSessionFactoryBuilder()
					.build(is);
			// 创建一个session工厂
			SqlSession session = factory.openSession();
			
		//修改
			BananaMapper sm=session.getMapper(BananaMapper.class);
			Banana s=new Banana();
			s.setBid(1);
			s.setBname("超大无超大香蕉香蕉");
			int count=sm.xiugai(s);
			if(count>0){
				System.out.println("修改成功！");
				session.commit();
			}else{
				System.out.println("修改失败！");
				session.rollback();
			}
			} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

删除接口:public int shanchu(int id);
xml文件delete标签:

 <!-- 删除香蕉 -->
  <delete id="shanchu" parameterType="int">
  delete from banana where bid=#{#bid}
  </delete>

实现类代码

//删除
// 创建xml路径
		String pathString = "Banana-mybatis.xml";
		// 创建xml输入流
		try {
			InputStream is = Resources.getResourceAsStream(pathString);
			// 创建一个sqlsessionfatory
			SqlSessionFactory factory = new SqlSessionFactoryBuilder()
					.build(is);
			// 创建一个session工厂
			SqlSession session = factory.openSession();
			//删除
			BananaMapper sm=session.getMapper(BananaMapper.class);
			int count=sm.shanchu(3);
			if(count>0){
				System.out.println("删除成功！");
				session.commit();
			}else{
				System.out.println("删除成功！");
				session.rollback();
			}
			} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

接下来来查看我们的组合查询:
public List fall(@Param(“bname”)String name,@Param(“bid”)int bid);
我们只是通过id和名字来查询的，你也可以添加其他字段数据来进行查询，下面也同样实现了模糊查询！

<!-- 组合和模糊查询 -->
  <select id="fezzy" resultType="ShiTi.Banana" >
  select * from banana where bname like CONCAT('%',#{bname},'%')
  </select>
  <select id="fall" resultType="ShiTi.Banana">
  select * from banana 
  <where>
  <if test="bid!=0">
  	bid=#{bid}
  </if>
  <if test="bname!=''">
 and  bname like '%' #{bname} '%'
  </if>
  </where>
  </select>

bananaTest实现类的代码:

//分组查询和模糊查询
// 创建xml路径
		String pathString = "Banana-mybatis.xml";
		// 创建xml输入流
		try {
			InputStream is = Resources.getResourceAsStream(pathString);
			// 创建一个sqlsessionfatory
			SqlSessionFactory factory = new SqlSessionFactoryBuilder()
					.build(is);
			// 创建一个session工厂
			SqlSession session = factory.openSession();
			
			BananaMapper sm=session.getMapper(BananaMapper.class);
			Scanner input=new Scanner(System.in);
			List<Banana> list=sm.fall("香蕉", 0);
			for (Banana banana : list) {
					System.out.println(banana.getBid()+"\t"+banana.getBname()+"\t"+banana.getBprice()+"\t"+banana.getBcolor()+"\t"+banana.getBsize());
			}
			} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}