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

曾经终败给现在 2021-12-15 06:21 485阅读 0赞

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

### [上一篇-如何正确使用Hutool和Guava这两个丰富的工具集，自己学习测试，样例][-_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虚拟机，还有文件上传下载，等基础命令][java_Linux] ###

[-_Hutool_Guava]: https://blog.csdn.net/qq_42105629/article/details/94620401
[java_Linux]: https://blog.csdn.net/qq_42105629/article/details/95055700