hellokaton / anima Goto Github PK
View Code? Open in Web Editor NEWMinimal database operation library.
Home Page: https://github.com/biezhi/anima/wiki
License: Apache License 2.0
Minimal database operation library.
Home Page: https://github.com/biezhi/anima/wiki
License: Apache License 2.0
每使用Anima.atomic一次,就泄露一个连接,跟踪了一下代码,发现在AnimaQuery中,方法
private Connection getConn() {
Connection connection = localConnection.get();
return ifNotNullReturn(connection, connection, this.getSql2o().open());
}
ifNotNullReturn(connection, connection, this.getSql2o().open()); 这儿无论connection是否为空,都回去调用this.getSql2o().open()打开新的连接,我把这儿改成
if (connection!= null) {
return connection;
} else{
return this.getSql2o().open();
}
不知道我的理解是否正确
Joins.with(Person.class)
.as(User::getPersons())
.on(User::getId, Person::getUid)
.order(Person::getId, DESC);
Joins.with(Person.class)
.as(User::getPersons())
.on(User::getId, Person::getUid)
.order("id asc");
大佬你好,仓库的源码中我发现有对sql2o这个开源的repo,整个源码的拷贝,然而在本项目的依赖中似乎没有提及,是不是可以有更好的方式呢?
这是我目前使用的枚举
public enum BaseStatus {
INTI(0, "init"),
NORMAL(1, "normal"),
BANED(8, "baned"),
DELETED(9, "deleted")
;
private Integer code;
private String desc;
}
在使用的时候发现@EnumMapping
只支持一般的枚举类型,不支持自定义的方法获取值写入数据库
个人修改了一下写入数据库的时候
// AnimaUtils 107
if (enumMapping.value().equals(EnumMapping.TO_STRING)) {
columnValueList.add(value.toString());
} else if (enumMapping.value().equals(EnumMapping.ORDINAL)) {
columnValueList.add(((Enum) value).ordinal());
} else {
columnValueList.add(invokeMethod(value, enumMapping.value(), EMPTY_ARG));
}
在读取数据时的转换类似乎也没有设置的接口(仅有NoQuirks
的构造函数),希望这里能提供一个方法来设置。
其中的Lambda写法和jooq有异曲同工之妙,都能做到类型安全。只不过jooq用起来很繁琐,还要生成一堆代码。而这个非常简洁。
Anima 高级用法,如果想获取 desc 字段的值,如获取‘初级会员’,‘高级会员’,应该要怎样写呢?
`public enum VipLevel {
VIP1(1, "初级会员"),
VIP2(2, "高级会员"),
VIP3(3, "至尊会员");
private int code;
private String desc;
VipLevel(int code, String desc) {
this.code = code;
this.desc = desc;
}
}`
Caused by: java.lang.IllegalArgumentException: Unable to find non-private method: getRush with 0 params.
at com.blade.reflectasm.MethodAccess.getIndex(MethodAccess.java:74)
at com.blade.reflectasm.MethodAccess.invokeWithCache(MethodAccess.java:47)
at io.github.biezhi.anima.utils.AnimaUtils.invokeMethod(AnimaUtils.java:147)
at io.github.biezhi.anima.utils.AnimaUtils.toColumnValues(AnimaUtils.java:101)
调用save时出现的,rush是个boolean
Anima.open("something").tablePrefix("t_");
Anima.atomic( () -> {
User.set("name", "jack").update(23);
new SystemLog("update", new Date(), 189L).save();
});
Anima.batchDelete(1, 2, 3, 4);
Anima.batchDelete(list);
在wiki文档有这个接口,但实际并没有,我用的是0.2.5版本
表结构
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`activated` tinyint(1) NULL DEFAULT 0,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
User模型
@Table(name = "t_user")
public class User extends Model {
private Integer id;
private String username;
private String password;
private String email;
private boolean activated;
public User(String username, String password, String email) {
this.username = username;
this.password = password;
this.email = email;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public boolean getActivated() { return activated; }
public void setActivated(boolean activated) { this.activated = activated; }
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", email='" + email + '\'' +
", activated=" + activated +
'}';
}
}
例如执行:
user.setActivated(true);
user.update();
在执行完update之后,user模型的Id属性被置为NULL
com.mysql.cj.jdbc.Driver
这个8.0驱动
LocalDate
LocalDateTime
数据库:MySQL 版本 5.7.20
// save操作
Integer id = save(role).asInt();
实体类:
public class SysRole extends Model {
private Integer id; // 对应数据库类型为 INT(11)
private String name;
@Column(name = "`desc`")
private String desc;
private Long createTime;
private Long modifyTime;
...
}
错误信息:
java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Integer
at io.github.biezhi.anima.core.ResultKey.asInt(ResultKey.java:38)
....
错误点:
public Integer asInt() {
if (key instanceof Long) {
return asLong().intValue();
}
return (Integer) key;// 异常行,debug发现此处key实际是BigInteger类型,类型转换报错
}
暂时修改办法,仿照Long
的处理方法,增加对BigInteger
类型的处理:
// 修改
public Integer asInt() {
if (key instanceof Long) {
return asLong().intValue();
}
if (key instanceof BigInteger) {
return asBigInteger.intValue();
}
return (Integer) key;
}
// 新增
public BigInteger asBigInteger() {
return (Long) key;
}
Anima.open(....).rollbackException(CustomException.class);
做一些列表筛选的项目的时候可能往往简单的做法是前端表单直接动态可选的提交一些参数,后端由mvc框架自动的封装成对象,然后希望Anima直接支持传入一个Model的实例对象,Anima根据这个实例对象自动动态的拼接查询SQL。
当前只能手动一个属性一个属性的判断是否非空或非空字符串,也算得上是样板代码了
最后希望实现的效果 伪代码:
User user = new User();
user.setGender(Gender.FEMALE.getCode());
user.setVipLevel(5);
user.setCreatedTime(LocalDateTime.now().minusMonths(1));
Anima.select().from(User.class).where(user).all();
//当然Model也希望添加直接查询的方法如 user.all() or user.limit(10) or user.page(1,10);
//最后输出SQL:select * from user where gender = ? and vip_level=? and created_time = ?
//支持 exclude model的部分field
Anima.select()
.from(User.class)
.where(user,User::getCreatedTime)//where(T model,Fn<T,R>... excludeFields)
.gte(User::getCreatedTime,user.getCreatedTime())
.all();
//输出的SQL:select * from user where gender =? and vip_level=? and created_time >= ?
//当前只能自己手动判断如:
AnimaQuery<User> query = Anima.select().from(User.class);
if(user.getGender()!=null){
query.and(User::getGender).eq(user.getGender());
}
//.....
这个框架如何支持如c3p0或druid的连接池
An exception occurs when read and write simultaneously.
Amani.atom( () -> {
// read
// write
});
CREATE TABLE user_info (
`user_id` INT (11) NOT NULL AUTO_INCREMENT COMMENT '用户Id',
`user_name` VARCHAR (255) DEFAULT NULL COMMENT '用户名',
`value` VARCHAR (255) NULL COMMENT '值',
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息';
user_id | user_name | value |
---|---|---|
1 | toby | hello world |
@Test
public void query(){
Anima.open(url,user,pass);
String value = select().bySQL(
String.class,"select user_name from user_info where user_id = 1").one();
System.out.println(value);
}
输出: toby
value
列@Test
public void query(){
Anima.open(url,user,pass);
String value = select().bySQL(
String.class,"select `value` from user_info where user_id = 1").one();
System.out.println(value);
}
15:31:07.962 [main] DEBUG org.sql2o.Query - Execute SQL => select `value` from user_info where user_id = 8
15:31:07.966 [main] DEBUG org.sql2o.Query - Parameters => []
org.sql2o.Sql2oException: Database error: Unknown column 'value' in 'field list'
at org.sql2o.Query$ResultSetIterableBase.<init>(Query.java:216)
at org.sql2o.Query$1.<init>(Query.java:292)
at org.sql2o.Query.executeAndFetchLazy(Query.java:292)
at org.sql2o.Query.executeAndFetchFirst(Query.java:343)
at org.sql2o.Query.executeAndFetchFirst(Query.java:335)
at io.github.biezhi.anima.core.AnimaQuery.queryOne(AnimaQuery.java:1057)
at io.github.biezhi.anima.core.ResultList.one(ResultList.java:46)
at io.github.biezhi.anima.SelectOneTest.query(SelectOneTest.java:20)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'value' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
at org.sql2o.Query$ResultSetIterableBase.<init>(Query.java:213)
... 29 more
Process finished with exit code 255
select().from(User.class).where("id", 10).or("age > ?", 15).one();
AnimaQuery第1453行
Field modelField = model.getClass().getDeclaredField(joinParam.getOnLeft());
这一行有Bug,后面的joinParam.getOnLeft()
是通过原field转化后的column列名,所以如果出现比如field是userId
这种驼峰形式或者使用了@Column(name="user_id")
注解等情况时,joinParam的onLeft
属性是user_id
,此时获取Field就会失败。
hi~ Anima 很棒!使用过程中觉得Join的使用有点疑惑,望指教 ^^
@Table(name = "table1")
@Data
public class Table1 extends Model {
@Column(name = "id")
private int id;
@Column(name = "no")
private String no;
}
@Table(name = "table2")
@Data
public class Table2 extends Model {
@Column(name = "id")
private int id;
@Column(name = "name")
private String name;
}
select * from table1 as a
left join table2 as b
where a.no = 'AAA' and b.name ='BBB'
用 lambda JOIN 需要如何生成这样的SQL,还是目前只能用bySQL()实现?还有对于Join的as()的用法如何去理解~
select().from(UserDto.class).exclude(HasMany.class).byId(1);
oracle 分页支持
Anima 是否有对多数据源的支持呢?
PostgreSQL 不支持 LIMIT #,# 语法,如何变成LIMIT # OFFSET # 语法?
如题,希望Model添加对枚举的自动映射支持,允许通过注解等方式指定映射枚举哪个属性(或者直接toString或者索引值ordinal)映射数据库的字段
伪代码:
public enum Gender{
M,F;
}
public enum VipLevel{
VIP1(1,"初级会员"),
VIP2(2,"高级会员"),
VIP3(3,"至尊会员");
private int code;
private String desc;
//Getter Setter Constructor
}
@Table(name="`user`")
public class User extends Model{
@EnumMapping(EnumMapping.TO_STRING)//映射数据库转换的时候直接调用toString()
private Gender gender;
@EnumMapping("code")//映射数据库转换的时候调用getCode()方法获取code值
private VipLevel vipLevel;
}
public @interface EnumMapping {
//预设 调用枚举的toString方法
String TO_STRING = "toString";
//预设 调用枚举的ordinal方法
String ORDINAL = "ordinal";
//默认是直接调用toString方法
String value() default TO_STRING;
}
Exception in thread "main" java.util.concurrent.CompletionException: java.util.ConcurrentModificationException
at java.base/java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:314)
at java.base/java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:319)
at java.base/java.util.concurrent.CompletableFuture$UniAccept.tryFire(CompletableFuture.java:718)
at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:506)
at java.base/java.util.concurrent.CompletableFuture.postFire(CompletableFuture.java:610)
at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:840)
at java.base/java.util.concurrent.CompletableFuture$Completion.exec(CompletableFuture.java:479)
at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:290)
at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1020)
at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1656)
at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1594)
at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:177)
Caused by: java.util.ConcurrentModificationException
at java.base/java.util.HashMap.computeIfAbsent(HashMap.java:1134)
at io.github.biezhi.anima.utils.AnimaUtils.invokeMethod(AnimaUtils.java:129)
at io.github.biezhi.anima.utils.AnimaUtils.toColumnValues(AnimaUtils.java:99)
at io.github.biezhi.anima.core.AnimaQuery.save(AnimaQuery.java:1203)
at io.github.biezhi.anima.Model.save(Model.java:28)
H2
、SQLite
、MySQL
、Oracle
、PostgreSQL
、SQLServer
like
select().from(User.class).where(User::getUserName).equals("jack").one();
select().from(User.class).where(User::getAge).gt(15).and(User::getId).in(1, 4, 5).all();
update().from(User.class).set(User::getAge, 20).where(User::getId).equals(3).execute();
delete().from(User.class).where(User::getId).equals(3).execute();
@Table(name = "k_user", pk = "uid")
public class User {
private Integer uid;
@Column(name = "create_at")
private String createTime;
}
SqlServer2012及以上版本可以使用offset-fetch翻页来提升性能,最终的sql可以如下
select * from table
where column = ?
order by current_timestamp -- 注1
offset ? rows fetch next ? rows only
注1:如果用户的sql没有order by,offset必须要在order by后面,因此可以添加一个根据时间戳order by,如果用户的sql有order by,则无需为其再添加order by
另外发现目前的SqlServerDialect
分页是有bug的,生成出来的分页sql运行不通过,如果我没测试错的话。
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM ( SELECT row_number() over (ORDER BY tempcolumn) temprownumber, * FROM ( SELECT top ")
.append(end)
.append(" tempcolumn=0,")
.append(select(sqlParams))
.append(")vip)mvp where temprownumber>")
.append(begin);
append(select(sqlParams))
这行,因为内置select(sqlParams)
函数拼接出来的sql是带有select开头的,导致这里最终拼接出来的sql语句错误append(")vip)mvp where temprownumber>")
这行,最后的应该是>=
row_number() over (ORDER BY tempcolumn)
可以基于current_timestamp
来排序,无需在内层多添加一列常量列,即:row_number() over (ORDER BY current_timestamp)
createTime 被转成了create_time
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'create_time' in 'field list'
已经开始在公司的部分微信后台程序中使用anima了,开发十分方便;
虽然目前没有出现任何问题;不过,说真的,对大流量访问还真的是稍稍有些担心。
期待着anima会有更多更好的功能,并有更好的发展。
save,update 等方法时报错
我看anima源码也没有MethodAccess这个class啊
是否支持纯SQL脚本?
select user_id,user_Name,login
from user_info
where user_id = 1
数据结果:
package io.github.biezhi.anima.model;
import io.github.biezhi.anima.Model;
import io.github.biezhi.anima.annotation.Column;
import io.github.biezhi.anima.annotation.Ignore;
import io.github.biezhi.anima.annotation.Table;
import lombok.Data;
@Table(name="user_info")
@Data
public class UserInfo extends Model {
@Column(name = "user_id")
private Long id;
@Column(name = "user_Name")
private String name;
private Long login;
@Ignore
private String other;
}
package io.github.biezhi.anima;
import io.github.biezhi.anima.model.UserInfo;
import org.junit.Test;
import java.util.List;
import static io.github.biezhi.anima.Anima.select;
public class Sql2oTest {
@Test
public void Test(){
String url = "jdbc:mysql://192.168.1.100:3307/g_main?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false";
String user ="root";
String pass ="123456";
Anima.open(url,user,pass);
List<UserInfo> users = select().from(UserInfo.class).where("user_id = ?",1).all();
for (UserInfo item : users)
{
System.out.println("id:"+item.getId());
System.out.println("name:"+item.getName());
System.out.println("login:"+item.getLogin());
System.out.println("other:"+item.getOther());
}
}
}
18:31:52.663 [main] DEBUG org.sql2o.Query - Execute SQL => SELECT * FROM user_info WHERE user_id = ?
18:31:52.666 [main] DEBUG org.sql2o.Query - Parameters => [1]
18:31:52.709 [main] DEBUG org.sql2o.Query - Total => 47 ms, execution: 36 ms, reading and parsing: 11 ms; executed [null]
id:null
name:null
login:-1
other:null
Process finished with exit code 0
现在只能查询一个字段的指定列,能加上查询多个字段的指定列吗
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.