Query DB

Anima provides a way to query the database in the same way as using Stream in Java 8 and its syntax looks like writing a SQL statement.

First we import the select global static method Anima.

import static com.hellokaton.anima.Anima.select;

Query By Primary Key

User user = select().from(User.class).byId();

This method will produce a SQL statement SELECT * FROM users WHERE id = ?. Anima uses the placeholders in PreparedStatement to pass parameters. At the same time, specific parameter information will also be printed out on the console.

Query By Primary Key List

List<User> users = select().from(User.class).byIds(1, 2, 3);
// SELECT * FROM users WHERE id in (?, ?, ?)

Query count

long count = select().from(User.class).count();
// SELECT COUNT(*) FROM users

Query By Condition

List<User> users = select().from(User.class).like("user_name", "%o%").all();
// SELECT * FROM users WHERE user_name LIKE ?

There are many conditions here, below is a table of conditions.

Conditions	Param Example	SQL
where	where("id", 10)	WEHRE id = ?
where	where("age > ?", 10)	WEHRE age > ?
where	where(User::getAge).gt(10)	WEHRE age > ?
like	like("user_name", "jac%")	WEHRE user_name like ?
like	like(User::getUserName, "jac%")	WEHRE user_name like ?
isNotNull	isNotNull("user_name")	WEHRE user_name IS NOT NULL
isNotNull	isNotNull(User::getUserName)	WEHRE user_name IS NOT NULL
isNull	isNull("user_name")	WEHRE user_name IS NULL
isNull	isNull(User::getUserName)	WEHRE user_name IS NULL
notEq	notEq("age", 27)	WEHRE age != ?
notEq	notEq(User::getAge, 27)	WEHRE age != ?
in	in("age", 10, 22, 32)	WEHRE age IN (?, ?, ?)
in	in("age", list)	WEHRE in (?, ?, ?)
in	in(User::getAge, list)	WEHRE in (?, ?, ?)
between	between("age", 10, 22)	WEHRE age BETWEEN ? and ?
between	between(User::getAge, 10, 22)	WEHRE age BETWEEN ? and ?
gt	gt("age", 10)	WEHRE age > ?
gt	gt(User::getAge, 10)	WEHRE age > ?
gte	gte("age", 10)	WEHRE age >= ?
gte	gte(User::getAge, 10)	WEHRE age >= ?
lt	lt("age", 10)	WEHRE age < ?
lt	lt(User::getAge, 10)	WEHRE age < ?
lte	lte("age", 10)	WEHRE age <= ?
lte	lte(User::getAge, 10)	WEHRE age <= ?

Query By Custom SQL

String name = select().bySQL(String.class, "select user_name from users limit 1").one();

or

List<String> names = select().bySQL(String.class, "select user_name from users limit ?", 3).all();

paging

Page<User> userPage = select().bySQL(User.class, "select * from users").page(1, 10);

Query the specified column

User user = select("user_name").from(User.class).one();
// SELECT user_name FROM users LIMIT 1

Or

User user = select(User::getUserName).from(User.class).one();
// SELECT user_name FROM users LIMIT 1

Order By

User user = select("user_name").from(User.class).order("id desc").one();
// SELECT user_name FROM users ORDER BY id desc LIMIT 1

Or

select().from(User.class).order(User::getId, OrderBy.DESC).order(User::getAge, OrderBy.ASC).all();

Query limit

List<User> users = select().from(User.class).order("id desc").limit(5);
// SELECT * FROM users ORDER BY id desc LIMIT 5

Paging

Page<User> userPage = select().from(User.class).order("id desc").page(1, 3);
// SELECT COUNT(*) FROM (SELECT * FROM users) tmp
// SELECT * FROM users ORDER BY id desc LIMIT ?, ?

The paging object Page stores information such as the top and bottom pages, the total number of records, the total number of pages, whether there are top and bottom pages, and so on.

Lambda expressions

Sometimes people do not like to write blunt SQL because they will encounter refactoring issues, such as writing user_age > ? When modifying fields, you may forget to modify the SQL statement. In Anima you can use lambda expressions to improve this problem, with a few examples.

User user = select().from(User.class).where(User::getUserName).eq("jack").one();

List<User> user = select().from(User.class)
                .where(User::getUserName).notNull()
                .and(User::getAge).gt(10)
                .all();

As you can see, in fact, the changes are simpler than the previous ones, except that the column to be operated on is replaced by a method reference (User::getUserName). The others are the same.

Relation Query

There are times when the data we are querying is related. At this time, using a single-table query has some disadvantages. For example, an article can have multiple comments. The relationship between articles and comments is one-to-many (hasMany); The relationship with the article is many-to-one (belongsTo); there is also a relationship where the user has a personal information (hasOne), which can also be expressed in Anima. There is no way to use a union query here. To make things simple, we only need to query multiple times through the foreign key relationship. This is all done by the framework. You only need to understand the rules and use a few annotations.

Assuming there are a few Model

public class Post extends Model {
    private Long pid;
    private String title;
    // getter setter and other
}

public class Comment extends Model {
    private Long cid;
    private Long pid;
    private String content;
    // getter setter and other
}

Many times when we display articles, we also display comments. In the past, we first searched for articles and then inquired them based on the article's primary key. This is tricky. We use relationships to solve this problem.

The requirement is to query the list of comments at the same time when the article is queried. You need to modify the structure of Post.

public class Post extends Model {
    private Long pid;
    private String title;
    
    @HasMany(fk = "pid")
    private List<Comment> comments;
    // getter setter and other
}

At this point, when you execute select().from(Post.class).order("id desc").limit(5), the result of Comment will be automatically mapped in the Post model. Explain @HasMany, which is a one-to-many mapping relationship where an article has multiple comments, where fk is the pid foreign key in the comments table.

Note that fk fills in the database column name, not the field name.

The structure is similar to this when you need to query the personal information of User

public class User extends Model {
    private Long uid;
    @HasOne(fk = "uid")
    private Profile profile;
}

public class Profile extends Model {
    private Long uid;
    private String nickName;
    private String sign;
    // ...
}

Each User has a corresponding Profile, their relationship is one-to-one, we use the @HasOne annotation to complete the mapping.

When we view an order information, this order will use the same information with the user's contact information.

public class OrderInfo extends Model {
    private Long id;
    private Long contactId;
    private String orderName;
    @BelongsTo(fk = "contact_id")
    private Contact contact;
}

public class Contact extends Model {
    private Long id;
    private String telphone;
}

This time the query OrderInfo will query Contact in turn, where fk is the contact_id field in the order_infos table. The generated SQL is

SELECT * FROM order_infos WHERE id = ?
SELECT * FROM contacts WHERE id = ?  // id is the contactId from the above statement.

Sometimes we don't want all queries to be associated with another table. For example, when I am modifying user information, I don't need to query Comment, I can use the exclude method to filter relationships.

exclude(Class<? extends Annotation>... relations)

This method allows you to pass one or more relationship annotations. When you don't need an association, you can filter them in this query.