java类库mybatis_easymybatis

public class TUserDaoTest extends EasymybatisSpringbootApplicationTests {

@Resource

TUserDao dao;

@Resource

TransactionTemplate transactionTemplate;

// 根据主键查询

@Test

public void testGet() {

TUser user = dao.get(3);

print(user);

}

// 根据字段查询一条记录

@Test

public void testGetByProperty() {

TUser user = dao.getByProperty("username", "王五");

print(user);

}

// 根据条件查询一条记录

@Test

public void testGetByExpression() {

// 查询ID=3的用户

Query query = Query.build().eq("id", 3);

TUser user = dao.getByExpression(query);

print(user);

}

// 条件查询

// 查询username='张三'的用户

@Test

public void testFind() {

Query query = new Query();

// 添加查询条件

query.eq("username", "张三");

List list = dao.find(query); // 获取结果集

long count = dao.countTotal(query); // 获取总数

print("count:" + count);

for (TUser user : list) {

System.out.println(user);

}

}

// 分页查询

/*MYSQL语句:

*

* SELECT t.`id` , t.`username` , t.`state` , t.`isdel` , t.`remark` , t.`add_time` , t.`money` , t.`left_money`

* FROM `t_user` t

* ORDER BY id DESC

* LIMIT ?,?

*

* Parameters: 2(Integer), 2(Integer)

*/

@Test

public void testPage() {

Query query = new Query();

query.setPage(2, 2) // 设置pageIndex，pageSize

.addSort("id", Sort.DESC); // 添加排序

// 查询后的结果，包含总记录数，结果集，总页数等信息

PageInfo pageInfo = QueryUtils.query(dao, query);

List rows = pageInfo.getList();

for (TUser user : rows) {

System.out.println(user);

}

}

// 自定义返回字段查询，只返回两个字段

// SELECT t.id,t.username FROM `t_user` t LIMIT 0,10

@Test

public void testSelfColumns() {

Query query = new Query();

// 只返回id,username

query.setColumns(Arrays.asList("t.id","t.username"));

List list = dao.find(query);

for (TUser user : list) {

System.out.println(user);

}

}

// 多表查询,left join

// 适用场景：获取两张表里面的字段信息返回给前端

/*

* MYSQL生成如下sql:

SELECT

t.`id` , t.`username` , t.`state` , t.`isdel` , t.`remark` , t.`add_time` , t.`money` , t.`left_money`

, t2.city , t2.address

FROM `t_user` t left join user_info t2 on t.id = t2.user_id

WHERE t2.id = ?

ORDER BY id ASC

LIMIT ?,?

*/

@Test

public void testJoin() {

Query query = new Query();

// 添加第二张表的字段,跟主表字段一起返回

query.addOtherColumns(

"t2.city"

,"t2.address"

);

// 左连接查询,主表的alias默认为t

query.join("left join user_info t2 on t.id = t2.user_id");

//添加条件

query.eq("t2.id", 2);

query.addSort("t.id");

List list = dao.find(query);

System.out.println("==============");

for (TUser user : list) {

System.out.println(

user.getId()

+ " " + user.getUsername()

// 下面两个字段是第二张表里面的

+ " " + user.getCity()

+ " " + user.getAddress()

);

}

System.out.println("==============");

}

// 添加-保存所有字段

@Test

public void testInsert() {

TUser user = new TUser();

user.setAddTime(new Date());

user.setIsdel(false);

user.setLeftMoney(22.1F);

user.setMoney(new BigDecimal(100.5));

user.setRemark("备注");

user.setState((byte)0);

user.setUsername("张三");

dao.save(user);

print("添加后的主键:" + user.getId());

print(user);

}

// 添加-保存非空字段

@Test

public void testInsertIgnoreNull() {

TUser user = new TUser();

user.setAddTime(new Date());

user.setIsdel(true);

user.setMoney(new BigDecimal(100.5));

user.setState((byte)0);

user.setUsername("张三notnull");

user.setLeftMoney(null);

user.setRemark(null);

dao.saveIgnoreNull(user);

print("添加后的主键:" + user.getId());

print(user);

}

// 批量添加

/*

* 支持mysql,sqlserver2008。如需支持其它数据库使用saveMulti方法

* INSERT INTO person (id, name, age)

VALUES

(1, 'Kelvin', 22),

(2, 'ini_always', 23);

*/

@Test

public void testInsertBatch() {

List users = new ArrayList<>();

for (int i = 0; i < 3; i++) { // 创建3个对象

TUser user = new TUser();

user.setUsername("username" + i);

user.setMoney(new BigDecimal(i));

user.setRemark("remark" + i);

user.setState((byte)0);

user.setIsdel(false);

user.setAddTime(new Date());

user.setLeftMoney(200F);

users.add(user);

}

int i = dao.saveBatch(users); // 返回成功数

System.out.println("saveBatch --> " + i);

}

// 更新所有字段

@Test

public void testUpdate() {

TUser user = dao.get(3);

user.setUsername("李四");

user.setMoney(user.getMoney().add(new BigDecimal(0.1)));

user.setIsdel(true);

int i = dao.update(user);

print("testUpdate --> " + i);

}

// 更新不为null的字段

/*

*UPDATE [t_user] SET [username]=?, [isdel]=? WHERE [id] = ?

*/

@Test

public void updateIgnoreNull() {

TUser user = new TUser();

user.setId(3);

user.setUsername("王五");

user.setIsdel(false);

int i = dao.updateIgnoreNull(user);

print("updateNotNull --> " + i);

}

// 根据条件更新

// UPDATE t_user SET remark = '批量修改备注' WHERE state = 0

@Test

public void testUpdateNotNullByExpression() {

Query query = new Query();

query.eq("state", 0);

TUser user = new TUser();

user.setRemark("批量修改备注");

int i = dao.updateIgnoreNullByExpression(user, query);

print("testUpdateNotNullByExpression --> " + i);

}

// 删除

@Test

public void testDel() {

TUser user = new TUser();

user.setId(14);

int i = dao.del(user);

print("del --> " + i);

}

// 根据条件删除

// DELETE FROM `t_user` WHERE state = ?

@Test

public void delByExpression() {

Query query = new Query();

query.eq("state", 3);

int i = dao.delByExpression(query);

print("delByExpression --> " + i);

}

}