Spring Mvc那点事---(11)Spring Mvc之mybatis增删改查

    数据库的基本操作就是增删改查，这一节我们演示下通过mybatis怎样操作数据库以及怎样在mybaits中配置SQL语句。我们知道，在mybaits中每个实体对象都对应一个Mapper文件，我们在mapper中配置好相关的增删改查语句,

 

<?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="mapping.skusMapper">
   <!-- 指定查询SQL, parameterType是传入参数类型，resultType是返回结果-->
    <select id="getSku" parameterType="int" 
        resultType="dto.skus">
        SELECT * FROM skus WHERE  id=#{id}
    </select>
    <!-- 添加 -->
     <insert id="addSku" parameterType="dto.skus">
       INSERT INTO skus (skuname,skucode)  VALUES (#{SkuName},#{SkuCode})
     </insert>
     
     <!-- 删除 -->
     <delete id="delSku" parameterType="java.lang.String">
     
         DELETE FROM skus WHERE SkuCode=#{_parameter}
     </delete>
     
     <!-- 修改 -->
     <update id="updateSku" parameterType="dto.skus">
       UPDATE skus SET skuname=#{SkuName} WHERE SkuCode=#{SkuCode}
     </update>
     
       <!-- 查询全部 -->
     <select id="getList"  resultType="dto.skus">
       SELECT Id,SkuName,SkuCode FROM skus 
     </select>
     
     
</mapper>

    以上包括基本的增删改查和查询集合。其中parameterType是传入的参数类型，resultType是返回的结果类型，注意当个查询和查询所有结果,resultType的值是一样的。

   接下来，我们看看怎么操作Mapper文件。

      

@Controller
@RequestMapping("/Home")
public class HomeController {

	@Resource(name="applePhone")
    private IMobilePhone phone;
	
	@RequestMapping(value="index")
	public String Index()
	{
		String msg=phone.PhoneBrand();
		
		System.out.print(msg);
		
		  String resource = "/conf.xml";
	        //加载mybatis的配置文件
	        InputStream inputstream =this.getClass().getResourceAsStream(resource);
	       
	        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputstream);
	      
	        SqlSession session = sessionFactory.openSession();
	       
	        String statesql= "mapping.skusMapper.getSku";//在skusMapper.xml中有命名空间+方法名
	        
	        skus sku = session.selectOne(statesql, 1);
	        System.out.println(sku.getSkuName());
	        
	        
		return "index";
	}

	@RequestMapping(value="AddSku")
	public String AddSku()
	{
		    String resource = "/conf.xml";
	        //加载mybatis的配置文件
	        InputStream inputstream =this.getClass().getResourceAsStream(resource);
	       
	        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputstream);
	      
	        SqlSession session = sessionFactory.openSession();
	       
	        String statesql= "mapping.skusMapper.addSku";//在skusMapper.xml中有命名空间+方法名
	        skus sku=new skus();
	        sku.setSkuName("黑色手表");
	        sku.setSkuCode("00002");
	        int result = session.insert(statesql,sku);
	        session.commit();
            session.close();
	        System.out.println(result);
	    	return "index";
	}
	
	
	@RequestMapping(value="DelSku")
	public String DelSku()
	{
		    String resource = "/conf.xml";
	        //加载mybatis的配置文件
	        InputStream inputstream =this.getClass().getResourceAsStream(resource);
	       
	        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputstream);
	      
	        SqlSession session = sessionFactory.openSession();
	       
	        String statesql= "mapping.skusMapper.delSku";//在skusMapper.xml中有命名空间+方法名
	        
	        int result = session.delete(statesql,"00002");
	        session.commit();
            session.close();
	        System.out.println(result);
	    	return "index";
	}
	
	@RequestMapping(value="UpdateSku")
	public String UpdateSku()
	{
		    String resource = "/conf.xml";
	        //加载mybatis的配置文件
	        InputStream inputstream =this.getClass().getResourceAsStream(resource);
	       
	        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputstream);
	      
	        SqlSession session = sessionFactory.openSession();
	       
	        String statesql= "mapping.skusMapper.updateSku";//在skusMapper.xml中有命名空间+方法名
	        
	        skus sku=new skus();
	        sku.setSkuName("黑色手表男士款式");
	        sku.setSkuCode("00002");
	        
	        
	        int result = session.update(statesql, sku);
	        session.commit();
            session.close();
	        System.out.println(result);
	    	return "index";
	}
	
	
	@RequestMapping(value="List")
	public String List()
	{
		    String resource = "/conf.xml";
	        //加载mybatis的配置文件
	        InputStream inputstream =this.getClass().getResourceAsStream(resource);
	       
	        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputstream);
	      
	        SqlSession session = sessionFactory.openSession();
	       
	        String statesql= "mapping.skusMapper.getList";//在skusMapper.xml中有命名空间+方法名
	        
	       
	        java.util.List<skus> listResult= session.selectList(statesql);
	        session.commit();
            session.close();
	        
            for(dto.skus item:listResult)
            {
                   System.out.print(item.getId()+"--"+item.getSkuCode()+"--"+item.getSkuName());
            	    System.out.print("--------------");
            }
           
	    	return "index";
	}
	
	
	
	
}

    上面的代码演示了具体的增删改查操作方法。注意一点，如果插入数据中的中文为乱码，要把数据库连接配置为utf8,如下

  <property name="url" value="jdbc:mysql://localhost:3306/erp?useUnicode=true&amp;characterEncoding=utf8" />

  &amp;代表&符号。

</pre><p>     表结构如下</p><p>   <pre name="code" class="sql">CREATE TABLE `skus` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `SkuName` varchar(50) DEFAULT NULL,
  `SkuCode` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8