基于Hutool包对数据库的增删改查操作

这里就不介绍数据库的配置了，请参考

​​上一篇-如何正确使用Hutool和Guava这两个丰富的工具集，自己学习测试，样例​​

一、增删改操作

@Test
  @Ignore
  public void crudTest() throws SQLException {

    // 增
    Long id = db.insertForGeneratedKey(Entity.create("user").set("name", "unitTestUser").set("age", 66));
    Entity result = db.get("user", "name", "unitTestUser");

    // 改
    int update = db.update(Entity.create().set("age", 88), Entity.create("user").set("name", "unitTestUser"));
    Assert.assertTrue(update > 0);
    Entity result2 = db.get("user", "name", "unitTestUser");

    // 删
    int del = db.del("user", "name", "unitTestUser");
    Assert.assertTrue(del > 0);
    Entity result3 = db.get("user", "name", "unitTestUser");
  }

启动测试时，回在控制台打印 SQL语句

SQL -> INSERT  INTO `user` ( `name`, `age`)  VALUES (?, ?)
Params -> [unitTestUser, 66]

UPDATE
        `user` 
    SET
        `age` = ?  
    WHERE
        `name` = ?
Params -> [88, unitTestUser]

DELETE 
    FROM
        `user` 
    WHERE
        `name` = ?
Params -> [unitTestUser]

关于批量插入

@Test
  @Ignore
  public void insertBatchTest() throws SQLException {
    User user1 = new User();
    user1.setName("张三");
    user1.setAge(12);
    user1.setBirthday("19900112");
    user1.setGender(true);

    User user2 = new User();
    user2.setName("李四");
    user2.setAge(12);
    user2.setBirthday("19890512");
    user2.setGender(false);

    Entity data1 = Entity.parse(user1);
    Entity data2 = Entity.parse(user2);

    Console.log(data1);
    Console.log(data2);

    ArrayList<Entity> entities = CollUtil.newArrayList(data1, data2);
    int[] result = db.insert(entities);
    Console.log(result);
  }

控制台SQL输出为

INSERT   INTO `user` (
            `id`, `name`, `age`, `birthday`, `gender`
        ) 
    VALUES
        (?, ?, ?, ?, ?)
Params -> []

二、关于查询

package tonels.test;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.lang.Console;
import cn.hutool.db.ActiveEntity;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.handler.EntityListHandler;
import cn.hutool.db.sql.Condition;
import cn.hutool.db.sql.Condition.LikeType;
import org.junit.Assert;
import org.junit.Ignore;
import org.junit.Test;
import tonels.test.pojo.User;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 增删改查测试
 * 
 * @author looly
 *
 */
public class CRUDTest {

  private static Db db = Db.use("mysql");

  @Test
  public void findBetweenTest() throws SQLException {
    List<Entity> result1 = db.findAll(Entity.create("user").set("age", "between '18' and '40'")); 
    // 对应 sql 为 
    // SELECT * FROM `user`  WHERE `age` BETWEEN ? AND ?
  }
  @Test
  public void findLikeTest() throws SQLException {
    List<Entity> result2 = db.findAll(Entity.create("user").set("name", "like \"%三%\""));
    // 对应 sql 为 
    // SELECT  *  FROM  `user`  WHERE `name` LIKE ?
  }

  @Test
  public void findLikeTest3() throws SQLException {
    List<Entity> result3 = db.findAll(Entity.create("user").set("name", new Condition("name", null, LikeType.Contains)));
    // 对应 sql 为 
    // SELECT *  FROM `user`  WHERE  `name` IS NULL
  }
  
  @Test
  public void findTest() throws SQLException {
    List<Entity> result4 = db.find(CollUtil.newArrayList("name AS name2"), Entity.create("user"), new EntityListHandler());
    // 对应 sql 为 
    // SELECT  name AS name2  FROM `user`
  }
  
  @Test
  public void findActiveTest() throws SQLException {
    ActiveEntity entity = new ActiveEntity(db, "user");
    entity.setFieldNames("name AS name2").load();
  // 对应 sql 为 
  // SELECT  name as name2  FROM  `user`
  }
}

​​下一篇，java代码如何连接Linux虚拟机，还有文件上传下载，等基础命令​​