mybatis 2 -常用数据操作

1、写入数据并获取自增ID

XML配置：

  <!-- 写入数据获取自增ID -->
  <insert id="insertLog"   parameterType="com.mamaguwen.entity.sys_loginlog"  useGeneratedKeys="true" keyProperty="logid">
      insert into  sys_loginlog  (UserName) values  (#{username}) 
  </insert>

测试代码:

@Test
    public void insertLog() {
        sys_loginlog model = new sys_loginlog();
        model.setIslogin(true);
        model.setLoginip("127.0.0.1");
        model.setLogintime(new Date());
        model.setUsername("rhythmk");

        int total = loginlog.insertLog(model);
        System.out.println("影响数据条：" + total);
        System.out.println("ID：" + model.getLogid());
        /*
         * 影响数据条：1 ID：4 
         */

    }

2、更新数据

  <!-- 更新数据 -->
  <update id="updateLog"   parameterType="com.mamaguwen.entity.sys_loginlog">
      update sys_loginlog set username=#{username}
      where LogId=#{logid}
  </update>

    /*
     * 更新数据
     */
    @Test
    public void updateLog() {
        sys_loginlog record = new sys_loginlog();
        record.setLogid(4L);
        record.setUsername("wangkun");
        int total = loginlog.updateLog(record);
        System.out.println("影响数据条：" + total);
    }

3、返回单个字符串对象：

    <!-- -返回单字段内容 -->
   <select id="selectStringByKey"  resultType="String"  >
    select   UserName   from  sys_loginlog
    where LogId = #{logid}
  </select>

    /*
     * 返回当个简单对象
     */
    @Test
    public void selectStringByKey() {
        String record = loginlog.selectStringByKey(4);
        System.out.println("返回的字符串:" + record);
    }

4、返回List对象

<select id="selectLogList"    resultType="com.mamaguwen.entity.sys_loginlog">
       select * from  sys_loginlog
   </select>

    /*
     * 获取所有用户日志
     */
    @Test
    public void selectLogList() {
        List<sys_loginlog> list = loginlog.selectLogList();
        for (sys_loginlog log : list) {
            System.out.println(log.getUsername());
        }
    }

5、返回List<String> 对象

     <select id="selectUserNameList"    resultType="String">
       select UserName from  sys_loginlog
   </select>

    /*
     * 获取所有用户名
     */
    @Test
    public void selectUserNameList() {
        List<String> list = loginlog.selectUserNameList();
        for (String str : list) {
            System.out.println(str);
        }
    }

6、传入单个参数

     <select id="selectLogByKey"   resultType="com.mamaguwen.entity.sys_loginlog">
         select * from  sys_loginlog  Where LogId=#{logid}
     </select>
     

    /*
     * 根据主键获取日志
     */
    @Test
    public void selectLogByKey() {
        sys_loginlog model = loginlog.selectLogByKey(5);

        String str = String.format("id:%d,username:%s", model.getLogid(),
                model.getUsername());
        System.out.println(str);
    }

7、执行存储过程：

    <!-- 执行存储过程 -->
     <select id="callProc"   resultType="String"  >
              <!-- 
                    drop procedure if exists ShowString;
                    CREATE PROCEDURE  ShowString(
                         Str VARCHAR(30)
                    )
                    BEGIN
                     select Str as Item;
                    END;
                    CALL  ShowString('rhythmk')
      -->
         call  ShowString (#{str})
     </select>

/*
     * 执行存储过程
     */
    @Test
    public void callProc() {
        String str = loginlog.callProc("rhytmk");
        System.out.println(str);
    }

8、批量写入数据

  <!-- 批量执行SQL -->
     <!--  生成SQL:
               insert into  sys_loginlog (username) values ('a'),('b')
      -->
     <insert id="insertBatch">
         insert into  sys_loginlog (username) values
         <foreach collection="list"  item="item"  index="index"  separator=",">
               (#{item.username})
         </foreach>
     </insert>

    /*
     * 批量写入
     */
    @Test
    public void insertBatch() {
        List<sys_loginlog> list = new ArrayList<sys_loginlog>();
        for (int i = 0, j = 10; i < j; i++) {
            sys_loginlog log = new sys_loginlog();
            log.setUsername(String.format("wangkun%s", i));
            list.add(log);
        }
        int total = loginlog.insertBatch(list);
        System.out.println("生成数据条:" + total);
    }

9、将字符串当参数出入进去

    
     <select id="selectLogByMap"     parameterType="Map"  resultType="com.mamaguwen.entity.sys_loginlog">
         select * from sys_loginlog
             where (username=#{username1} or username=#{username2} )
     </select>
  

    /*
     * 通过Map传入参数
     */
    @Test
  public   void   selectLogByMap()
  {
        Map<String, String> map=new HashMap<String,String>();
        map.put("username1", "rhythmk");
        map.put("username2", "wangkun");
       List<sys_loginlog> list= loginlog.selectLogByMap(map);
       for(sys_loginlog model:list)
       {
            String info=  String.format("id%d,username%s", model.getLogid(),
                   model.getUsername());
            System.out.println(info);
       }
  }

 10、#{}与${}的区别

     假如数据库 sys_loginlog表中有username=a,b两条数据。此时按下面配置文件，我传入'a','b'  则无法获取数据。 

     <select id="selectLogByUserName"     parameterType="Map"  resultType="com.mamaguwen.entity.sys_loginlog">
         select * from sys_loginlog
             where   username in ( #{username} )
     </select>
  

现修改where条件,换成${},那么传入的参数讲直接体会SQL中对应的文本 ：

     select * from sys_loginlog
     where   username in ( ${username} )
通过执行上面语句 生成的SQL为 :
   
    select * from sys_loginlog where username  in ('a','b')  

备注：

   表结构:

CREATE TABLE `sys_loginlog` (
  `LogId` bigint(20) NOT NULL AUTO_INCREMENT,
  `UserName` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Pwd` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `IsLogin` bit(1) DEFAULT NULL,
  `LoginIp` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `LoginTime` datetime DEFAULT NULL,
  PRIMARY KEY (`LogId`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

 Mapp数据操作接口：

public interface sys_loginlogMapper {

    /*
     *   写入日志并返回自增的ID
     * */
    int insertLog (sys_loginlog record);
    
    /*   更新数据
     * */
    int updateLog(sys_loginlog record);
    
    /* 
     *   返回当个简单对象
     * */
    String selectStringByKey(@Param("logid") int logId );
    
    
    /*
     * 获取所有用户日志
     * */
    List<sys_loginlog>  selectLogList();
    
    /*
     *  获取所有用户名
     * */
    List<String> selectUserNameList();
    
    /*
     * 根据主键获取日志
     * */
    sys_loginlog selectLogByKey(@Param("logid") int logid);
    
    /*
     * 执行存储过程
     * */
    String callProc(@Param("str") String str);
    
    /*
     * 批量写入
     * */
    int  insertBatch(List<sys_loginlog> list);
    
    /*
     *  通过Map传入参数
     * */
   List<sys_loginlog>   selectLogByMap(Map<String, String> map);
    
}

 测试用例代码：

package com.mamaguwen.dao.test;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.management.loading.PrivateMLet;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.log4j.Logger;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.mamaguwen.dao.sys_loginlogMapper;
import com.mamaguwen.entity.sys_loginlog;

@RunWith(value = SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:spring.xml",
        "classpath:spring-mybatis.xml" })
public class TestSysloginlogMapper {

    private static final Logger logger = Logger.getLogger(Test_SysUser.class);

    private sys_loginlogMapper loginlog;

    public sys_loginlogMapper getLoginlog() {
        return loginlog;
    }

    @Autowired
    public void setLoginlog(sys_loginlogMapper loginlog) {
        this.loginlog = loginlog;
    }

    /*
     * 写入日志并返回自增的ID
     */
    @Test
    public void insertLog() {
        sys_loginlog model = new sys_loginlog();
        model.setIslogin(true);
        model.setLoginip("127.0.0.1");
        model.setLogintime(new Date());
        model.setUsername("rhythmk");

        int total = loginlog.insertLog(model);
        System.out.println("影响数据条：" + total);
        System.out.println("ID：" + model.getLogid());
        /*
         * 影响数据条：1 ID：4 
         */

    }

    /*
     * 更新数据
     */
    @Test
    public void updateLog() {
        sys_loginlog record = new sys_loginlog();
        record.setLogid(4L);
        record.setUsername("wangkun");
        int total = loginlog.updateLog(record);
        System.out.println("影响数据条：" + total);
    }

    /*
     * 返回当个简单对象
     */
    @Test
    public void selectStringByKey() {
        String record = loginlog.selectStringByKey(4);
        System.out.println("返回的字符串:" + record);
    }

    /*
     * 获取所有用户日志
     */
    @Test
    public void selectLogList() {
        List<sys_loginlog> list = loginlog.selectLogList();
        for (sys_loginlog log : list) {
            System.out.println(log.getUsername());
        }
    }

    /*
     * 获取所有用户名
     */
    @Test
    public void selectUserNameList() {
        List<String> list = loginlog.selectUserNameList();
        for (String str : list) {
            System.out.println(str);
        }
    }

    /*
     * 根据主键获取日志
     */
    @Test
    public void selectLogByKey() {
        sys_loginlog model = loginlog.selectLogByKey(5);

        String str = String.format("id:%d,username:%s", model.getLogid(),
                model.getUsername());
        System.out.println(str);
    }

    /*
     * 执行存储过程
     */
    @Test
    public void callProc() {
        String str = loginlog.callProc("rhytmk");
        System.out.println(str);
    }

    /*
     * 批量写入
     */
    @Test
    public void insertBatch() {
        List<sys_loginlog> list = new ArrayList<sys_loginlog>();
        for (int i = 0, j = 10; i < j; i++) {
            sys_loginlog log = new sys_loginlog();
            log.setUsername(String.format("wangkun%s", i));
            list.add(log);
        }
        int total = loginlog.insertBatch(list);
        System.out.println("生成数据条:" + total);
    }

    /*
     * 通过Map传入参数
     */
    @Test
  public   void   selectLogByMap()
  {
        Map<String, String> map=new HashMap<String,String>();
        map.put("username1", "rhythmk");
        map.put("username2", "wangkun");
       List<sys_loginlog> list= loginlog.selectLogByMap(map);
       for(sys_loginlog model:list)
       {
            String info=  String.format("id%d,username%s", model.getLogid(),
                   model.getUsername());
            System.out.println(info);
       }
  }
}