mybatis 2 -常用数据操作
2024-10-15 20:55:42
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);
}
}
}
最新文章
- Aircrack-ng: (1) 概述
- Linux内核分析—完成一个简单的时间片轮转多道程序内核代码
- PPPoE名词解释
- 简单实用的Android ORM框架TigerDB
- (九)学习CSS之margin属性
- Ajax提交打开新窗口,浏览器拦截处理
- iOS9中请求出现App Transport Security has blocked a cleartext HTTP (http://)
- centos之tomcat安装
- FreeCodeCamp:Return Largest Numbers in Arrays
- 照片处理软件(iSee图片专家) 3.930 中文免费版
- VS2005--设置Release模式下调试
- 201521123066 《Java程序设计》第四周学习总结
- Gym 100952D&;&;2015 HIAST Collegiate Programming Contest D. Time to go back【杨辉三角预处理,组合数,dp】
- java双重检测或枚举类实现线程安全单例(懒汉模式)
- C#委托与事件总结
- JavaScript -- JSON.parse 函数 和 JSON.stringify 函数
- python+appium 自动化1--启动手机京东app
- 过渡与动画 - 缓动效果&;基于贝塞尔曲线的调速函数
- Pytorch使用多GPU
- 架构:The Onion Architecture : part 1(洋葱架构:第一篇)(转载)
热门文章
- java裁剪图片
- hdu1564博弈+找规律
- 108. Convert Sorted Array to Binary Search Tree 109. Convert Sorted List to Binary Search Tree -- 将有序数组或有序链表转成平衡二叉排序树
- linux hosts.equiv设置解析
- PHP:第五章——字符串的统计及查找
- UVALive 5903 Piece it together 二分匹配,拆点 难度:1
- New Concept English Two 8 19
- jQuery 参数详解
- linux内存查看工具
- MyEclipse web jsp 如何调试