Latest Version V1.2.3

1 Introduction

ThinkJD, also known as ThinkJDBC, an easy and powerful open source JDBC library. You can operate the database with one line code of Java,just like the M method of ThinkPHP framework.

Quick Start:

//Configurat the database(only once)
D.setDbConfig("jdbc:mysql://127.0.0.1:3306/DbName?characterEncoding=UTF-8","root","root");
//insert data
long id=D.M("user").field("name,weight","Tom",60).add();
//update data
D.M("user").field("weight",100).where("id=?",id).save();
//query data
User user=D.M("user").find(User.class,id);
//delete data
D.M("user").delete(id);

Project Page https://github.com/Leytton/ThinkJD https://gitee.com/Leytton/ThinkJD

Test Demo https://github.com/Leytton/ThinkJD_Demo

Blog Page https://blog.csdn.net/Leytton

2 Getting Started

0x01 Add the Dependencies

Add ThinkJDBC-x.x.x.jar and the following dependencies to the build path.

mysql-connector-java-5.1.39.jar
commons-dbutils-1.6.jar

you can find them in the lib dir：

https://github.com/Leytton/ThinkJD/tree/master/lib

0x02 Config the Database

There are two ways to connect database by using ThinkJD.You can config username and password or using the JDBC DataSources/Resource Pools such as Hikari,C3P0,etc.

Firstly，you should define the way toconnect database:

(1)Using Username and Password

D.setDbConfig("jdbc:mysql://127.0.0.1:3306/database?useUnicode=true&characterEncoding=UTF-8","root","root");

(2)Using JDBC Pool

Example for Hikari：

HikariConfig config = new HikariConfig("/hikari.properties");
HikariDataSource dataSource = new HikariDataSource(config);
D.setDataSource(dataSource);

Note that if you defined the JDBC pool,it will be preferred to use.

(3)Config Table Prefix

You can config the prefix of table(Not necessary).

D.setTablePrefix("jd_");
//D.M('user') will operate the `jd_user` table

Note: D.M('user').prefix('jd_')to set the table prefix temporarily【V1.2.3 added】

0x03 Filter Method

Operation	Param	Eg.	Note
`table`	table(String table)	table(“user”)
`join`	join(String join)	join(“left join machine on user.id=user_id and machine_status=1”)
`field`	①field(String filed) ②field(String filed, Object… dataParam)	①field(“id,username”) ②field(“id,username”,1111,”Leytton”)	①for select sql ②for update sql
`where`	①where(String where) ②where(String where, Object… whereParam)	①where(“id=1111 and username=’Leytton’”) ②where(“id=? and username=?”,1111,”Leytton”)
`group`	group(String group)	group(“type”)
`having`	having(String having)	having(“id>1234”)
`order`	order(String order)	order(“id desc”)
`page`	page(long page, long rows)	page(1,10)
`limit`	①limit(long rows) ②limit(long offset, long rows)	①limit(10) ②limit(1,10)
`union`	union(String union,Boolean isAll)	①union(“select * from user_two where id>1234”,false) ②union(“select * from user_two where id>1234”,true)

0x04 Select Method

Operation	Param	Note
select	<`T`> List<`T`> select(Class<`T`> type)
find	①<`T`> T find(Class<`T`> type) ②<`T`> T find(Class<`T`> type, long id) ③<`T`> T find(Class<`T`> type, String key, Object value)
count	①long count() ②long count(String field)
max	double max(String field)
min	double min(String field)
avg	double avg(String field)
sum	double sum(String field)

//find
//select id,name from jd_user where id>4 order by id asc limit 0,1
User res = D.M("user").field("id,name").where("id>?",4).order("id asc").find(User.class);

//find by id
//select * from user where id=3 limit 0,1
User user = D.M("user").find(User.class,3);

//find by field
//select * from jd_user where name='Tom' limit 0,1
User user=D.M("user").fetchSql(true).find(User.class,"name","Bob");

//query with where,field,etc.
//select id,name,weight from jd_user where id>3
List<User> res = new M("user").field("id,name,weight").where("id>3").select(User.class);

//query with group
//select sex,sum(weight) as weight,avg(age) as age,count(id) as num from jd_user where id>5 group by sex order by sex desc limit 0,10
res = new M("user").field("sex,sum(weight) as weight,avg(age) as age,count(id) as num").where("id>?",5).group("sex").order("sex desc").page(1, 10).select(User.class);

//query with join
//select jd_user.id,name,weight,sum(gold) as num from jd_user left join jd_gold on user_id=jd_user.id where jd_user.id>3 group by jd_user.id
res = new M("user").field("jd_user.id,name,weight,sum(gold) as num").join("left join jd_gold on user_id=jd_user.id").where("jd_user.id>3").group("jd_user.id").select(User.class);

//query with union
//(select id,name from jd_user where id=4 ) union all (select id,name from jd_user where id<3) union (select id,name from jd_user where id=3)
res = new M("user").field("id,name").where("id=4").union("select id,name from jd_user where id<3",true)
                    .union("select id,name from jd_user where id=3",false).select(User.class);

//statistical query
long num= new M("user").where("id>3").count();
num= D.M("user").fetchSql(true).where("id>3").count("id");
num= (long) D.M("user").fetchSql(false).where("id<0").max("id");
num= (long) D.M("user").where("id<3").max("id");
num= (long) D.M("user").min("id");
num= (long) D.M("user").where("id>3").min("id");
num= (long) D.M("user").fetchSql(false).where("id>3").avg("id");
double avg= D.M("user").fetchSql(false).where("id>3").avg("id");
num= (long) D.M("user").where("id>13441").sum("age");

By calling the method of fetchSql(true),you can get the SQL statement ThinkJD produced(exception way) and there will be no operation for the database.

user table fields：

Field Name	Data Type	note
id	int	primary key auto_increment
name	varchar
age	tinyint
weight	float
sex	tinyint	0:women/1:man
time	int

The return value of select() and find() will be saved to a JavaBean such as:


public class User {

    private long id;
    private int age;
    private String name;
    private float weight;
    private int sex;
    private int num;
    private long time;

    public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public float getWeight() {
        return weight;
    }
    public void setWeight(float weight) {
        this.weight = weight;
    }
    public int getSex() {
        return sex;
    }
    public void setSex(int sex) {
        this.sex = sex;
    }
    public int getNum() {
        return num;
    }
    public void setNum(int num) {
        this.num = num;
    }
    public long getTime() {
        return time;
    }
    public void setTime(long time) {
        this.time = time;
    }

}

0x05 Add method

Operation	Param	Note
add	long add()	field() must be called; return the id which is a auto_increment primary key;

//insert fields specified
long id=D.M("user").field("name,weight","Tom",60).add();

/*
 *insert fields unspecified.The 1st parameter must be "" or null
 *and the 2nd parameter `null` point to `id`
 */
id=D.M("user").field("",null,"Tom",60).add();

0x06 Update method

Operation	Param	Note
save	long save()	field(),where() must be called; return the affected number of rows

long num=D.M("user").field("name,weight","Mike",100).where("id=?",1234).save();
num=D.M("user").field("weight",100).where("id>?",1234).save();

0x07 Delete Method

Operation	Param	Note
delete	long delete()	field() must be called;; return the affected number of rows

To avoid careless deletion, [where] conditions mustn't be null

long num=D.M("user").delete(13424);
num=D.M("user").delete("time",1523681398);
num=D.M("user").where("id>=?",13421).delete();

0x08 Execute Method

Operation	Param	Note
execute	void execute(String… sqls)	execute the statements directly

D.M().execute( sql1 [ sql2 , sql3 ... ] );

3 License

Apache License 2.0

4 About

if you like this project,star it to let me know :) .Before finding a more convenient JDBC lib,I’ll update it continuously.

posted @ 2018-04-20 17:18 Leytton 阅读(246) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

不亦的博客

ThinkJDBC—An easy and powerful open source JDBC lib