mybatis flex
v1.10.2
ขั้นตอนที่ 1: เขียนคลาสเอนทิตี
@ Table ( "tb_account" )
public class Account {
@ Id ( keyType = KeyType . Auto )
private Long id ;
private String userName ;
private Date birthday ;
private int sex ;
// getter setter
}ขั้นตอนที่ 2: เขียนคลาส mapper (จำเป็นต้องขยาย BaseMapper)
public interface AccountMapper extends BaseMapper < Account > {
// only Mapper interface define
}ขั้นตอนที่ 3: เริ่มการสืบค้นข้อมูล
เช่น 1: สืบค้นด้วยคีย์หลัก
class HelloWorld {
public static void main ( String ... args ) {
HikariDataSource dataSource = new HikariDataSource ();
dataSource . setJdbcUrl ( "jdbc:mysql://127.0.0.1:3306/mybatis-flex" );
dataSource . setUsername ( "username" );
dataSource . setPassword ( "password" );
MybatisFlexBootstrap . getInstance ()
. setDataSource ( dataSource )
. addMapper ( AccountMapper . class )
. start ();
AccountMapper mapper = MybatisFlexBootstrap . getInstance ()
. getMapper ( AccountMapper . class );
// id = 100
Account account = mapper . selectOneById ( 100 );
}
}เช่น 2: รายการแบบสอบถาม
// use QueryWrapper to build query conditions
QueryWrapper query = QueryWrapper . create ()
. select ()
. from ( ACCOUNT )
. where ( ACCOUNT . ID . ge ( 100 ))
. and ( ACCOUNT . USER_NAME . like ( "zhang" ). or ( ACCOUNT . USER_NAME . like ( "li" )));
// execute SQL：
// SELECT * FROM tb_account
// WHERE tb_account.id >= 100
// AND (tb_account.user_name LIKE '%zhang%' OR tb_account.user_name LIKE '%li%' )
List < Account > accounts = mapper . selectListByQuery ( query );เช่น 3: แบบสอบถามเพจจิ้ง
// use QueryWrapper to build query conditions
QueryWrapper query = QueryWrapper . create ()
. select ()
. from ( ACCOUNT )
. where ( ACCOUNT . ID . ge ( 100 ))
. and ( ACCOUNT . USER_NAME . like ( "zhang" ). or ( ACCOUNT . USER_NAME . like ( "li" )))
. orderBy ( ACCOUNT . ID . desc ());
// execute SQL：
// SELECT * FROM tb_account
// WHERE tb_account.id >= 100
// AND (tb_account.user_name LIKE '%zhang%' OR tb_account.user_name LIKE '%li%' )
// ORDER BY tb_account.id DESC
// LIMIT 40,10
Page < Account > accountPage = mapper . paginate ( 5 , 10 , query ); QueryWrapper query = new QueryWrapper ();
query . select (). from ( ACCOUNT )
// SQL:
// SELECT * FROM tb_account QueryWrapper query = new QueryWrapper ();
query . select ( ACCOUNT . ID , ACCOUNT . USER_NAME ). from ( ACCOUNT )
// SQL:
// SELECT tb_account.id, tb_account.user_name
// FROM tb_account QueryWrapper query = new QueryWrapper ()
. select ( ACCOUNT . ID
, ACCOUNT . USER_NAME
, ARTICLE . ID . as ( "articleId" )
, ARTICLE . TITLE )
. from ( ACCOUNT . as ( "a" ), ARTICLE . as ( "b" ))
. where ( ACCOUNT . ID . eq ( ARTICLE . ACCOUNT_ID ));
// SQL:
// SELECT a.id, a.user_name, b.id AS articleId, b.title
// FROM tb_account AS a, tb_article AS b
// WHERE a.id = b.account_id QueryWrapper query = new QueryWrapper ()
. select (
ACCOUNT . ID ,
ACCOUNT . USER_NAME ,
max ( ACCOUNT . BIRTHDAY ),
avg ( ACCOUNT . SEX ). as ( "sex_avg" )
). from ( ACCOUNT );
// SQL:
// SELECT tb_account.id, tb_account.user_name,
// MAX(tb_account.birthday),
// AVG(tb_account.sex) AS sex_avg
// FROM tb_account QueryWrapper queryWrapper = QueryWrapper . create ()
. select ()
. from ( ACCOUNT )
. where ( ACCOUNT . ID . ge ( 100 ))
. and ( ACCOUNT . USER_NAME . like ( "michael" ));
// SQL:
// SELECT * FROM tb_account
// WHERE tb_account.id >= ?
// AND tb_account.user_name LIKE ? QueryWrapper queryWrapper = QueryWrapper . create ()
. select ()
. from ( ACCOUNT )
. where ( ACCOUNT . ID . ge ( 100 ))
. and (
exists (
selectOne (). from ( ARTICLE ). where ( ARTICLE . ID . ge ( 100 ))
)
);
// SQL:
// SELECT * FROM tb_account
// WHERE tb_account.id >= ?
// AND EXIST (
// SELECT 1 FROM tb_article WHERE tb_article.id >= ?
// ) QueryWrapper queryWrapper = QueryWrapper . create ()
. select ()
. from ( ACCOUNT )
. where ( ACCOUNT . ID . ge ( 100 ))
. and ( ACCOUNT . SEX . eq ( 1 ). or ( ACCOUNT . SEX . eq ( 2 )))
. or ( ACCOUNT . AGE . in ( 18 , 19 , 20 ). or ( ACCOUNT . USER_NAME . like ( "michael" )));
// SQL:
// SELECT * FROM tb_account
// WHERE tb_account.id >= ?
// AND (tb_account.sex = ? OR tb_account.sex = ? )
// OR (tb_account.age IN (?,?,?) OR tb_account.user_name LIKE ? ) QueryWrapper queryWrapper = QueryWrapper . create ()
. select ()
. from ( ACCOUNT )
. groupBy ( ACCOUNT . USER_NAME );
// SQL:
// SELECT * FROM tb_account
// GROUP BY tb_account.user_name QueryWrapper queryWrapper = QueryWrapper . create ()
. select ()
. from ( ACCOUNT )
. groupBy ( ACCOUNT . USER_NAME )
. having ( ACCOUNT . AGE . between ( 18 , 25 ));
// SQL:
// SELECT * FROM tb_account
// GROUP BY tb_account.user_name
// HAVING tb_account.age BETWEEN ? AND ? QueryWrapper queryWrapper = QueryWrapper . create ()
. select ()
. from ( ACCOUNT )
. orderBy ( ACCOUNT . AGE . asc (), ACCOUNT . USER_NAME . desc (). nullsLast ());
// SQL:
// SELECT * FROM tb_account
// ORDER BY age ASC, user_name DESC NULLS LAST QueryWrapper queryWrapper = QueryWrapper . create ()
. select ()
. from ( ACCOUNT )
. leftJoin ( ARTICLE ). on ( ACCOUNT . ID . eq ( ARTICLE . ACCOUNT_ID ))
. where ( ACCOUNT . AGE . ge ( 10 ));
// SQL:
// SELECT * FROM tb_account
// LEFT JOIN tb_article
// ON tb_account.id = tb_article.account_id
// WHERE tb_account.age >= ? QueryWrapper queryWrapper = QueryWrapper . create ()
. select ()
. from ( ACCOUNT )
. orderBy ( ACCOUNT . ID . desc ())
. limit ( 10 )
. offset ( 20 );
// MySql:
// SELECT * FROM `tb_account` ORDER BY `id` DESC LIMIT 20, 10
// PostgreSQL:
// SELECT * FROM "tb_account" ORDER BY "id" DESC LIMIT 20 OFFSET 10
// Informix:
// SELECT SKIP 20 FIRST 10 * FROM "tb_account" ORDER BY "id" DESC
// Oracle:
// SELECT * FROM (SELECT TEMP_DATAS.*,
// ROWNUM RN FROM (
// SELECT * FROM "tb_account" ORDER BY "id" DESC)
// TEMP_DATAS WHERE ROWNUM <=30)
// WHERE RN >20
// Db2:
// SELECT * FROM "tb_account" ORDER BY "id" DESC
// OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
// Sybase:
// SELECT TOP 10 START AT 21 * FROM "tb_account" ORDER BY "id" DESC
// Firebird:
// SELECT * FROM "tb_account" ORDER BY "id" DESC ROWS 20 TO 301. จะสร้างคลาส "ACCOUNT" สำหรับ QueryWrapper โดย Account.java ได้อย่างไร
สร้างโปรเจ็กต์ด้วย IDE หรือดำเนินการคำสั่ง maven build: mvn clean package
