ThinkJDBC—An easy and powerful open source JDBC lib

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. ThinkJD will automatically manage database connection. After use or exception occurs, it will close the connection to avoid memory leaks.

Quick Start:

//Configurat the database(only once)
D.setDbConfig("jdbc:mysql://127.0.0.1:3306/DbName?characterEncoding=UTF-8","root","root");

/*JavaBean Mode,Automatically get the table name,
*primary key,auto-increment attribute,field name and value
*/
User user = new User();
user.setAge(10);
user.setName("Hello");
user.setSex(true);

//insert data
long id=D.M(user).add();

//query data
user=D.M(User.class).find(id);

//update data
user.setSex(false);
D.M(user).field("sex").save();
//update the all nonnull fields of JavaBean by default,if there is no specified field

//delete data
D.M(user).delete();
//D.M(User.class).delete(id);

//Table Mode，specify the table name, primary key,auto-increment attribute,field name and value
//insert data
long id=D.M("user").field("name,weight").data("Tom",60).add();
//update data
D.M("user").field("name,weight").data("Tom",100).where("id=?",id).save();
//query data
user=D.M(User.class).find(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

Jar library

ThinkJDBC-x.x.x-full.jar contains the ThinkJDBC-x.x.x-core.jar core library and the following dependencies,adding it to the build path will be all right.

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

or maybe you prefer to work with maven.

Maven

<dependency>
    <groupId>com.llqqww</groupId>
    <artifactId>thinkjdbc</artifactId>
    <version>1.4.2_10</version>
</dependency>

0x02 Config the Database

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

Firstly，you should define the way toconnect database:

(1)File way

Add file in the project root directory (like Hikari configuration file format).ThinkJD will automatically load the configuration file when the first time you start . It will ignore if the file doesn‘t exist.
thinkjdbc.properties

jdbcUrl = jdbc:mysql://127.0.0.1:3306/thinkjdbc?useUnicode=true&characterEncoding=UTF-8
dataSource.user = root
dataSource.password = root

(2)Using Username and Password

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

(3)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') D.M(User.class) will operate the `jd_user` table

Note: D.M('user').prefix('jd_')to set the table prefix temporarily

0x03 Chain Method

Operation	Param	Eg.	Note
table	table(String table)	table(“user”)
pk	pk(String key)	pk(“id”)
autoInc	autoInc(boolean isPkAutoInc)	autoInc(false)
join	join(String join)	join(“left join machine on user.id=user_id and machine_status=1”)
field	field(String filed)	field(“id,username”)
data	data(Object… dataParam)	data(11,”Leytton”)
setInc	setInc(String key,long num)	setInc(“gold”,5) //gold=gold+5
setDec	setDec(String key,long num)	setDec(“gold”,5) //gold=gold-5
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”)
asc	asc(String key)	asc(“id”)
desc	desc(String key)	desc(“id”)
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() ②<T> List<T> select(String key, Object value)
find	①<T> T find() ②<T> T find(Object value) ③<T> T find(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.class).field("id,name").where("id>?",4).order("id asc").find();

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

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

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

//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 = D.M(User.class).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();

//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 = D.M(User.class).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();

//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 = D.M(User.class).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();

//statistical query
long num= new M(User.class).where("id>3").count();
num= D.M(User.class).fetchSql(true).where("id>3").count("id");
num= (long) D.M(User.class).fetchSql(false).where("id<0").max("id");
num= (long) D.M(User.class).where("id<3").max("id");
num= (long) D.M(User.class).min("id");
num= (long) D.M(User.class).where("id>3").min("id");
num= (long) D.M(User.class).fetchSql(false).where("id>3").avg("id");
double avg= D.M(User.class).fetchSql(false).where("id>3").avg("id");
num= (long) D.M(User.class).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:

//@Table(name="user") //tabble name=class name by default，you can annotate to redefine
public class User {
    //@Column(isKey=true) //key=id,isAutoInc=true by default，you can annotate to redefine
    private Long id;
    private Integer age;
    //@Column(name="user_name") //column name=attribute name by default，you can annotate to redefine
    private String name;
    private Float weight;
    private Boolean sex;

    @Column(isColumn=false)
    private Integer num;

    private Long time;

    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer 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 Boolean getSex() {
        return sex;
    }
    public void setSex(Boolean sex) {
        this.sex = sex;
    }
    public Integer getNum() {
        return num;
    }
    public void setNum(Integer 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()	data() must be called in Table Mode; return the id which is a auto_increment primary key;

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

/*
 *insert fields unspecified.insert data by filed order
 */
id=D.M("user").data(null,"Tom",60,...).add();

/*Semi-automatic Mode.Data specified and Automatically get the table name,primary key,
 *auto-increment attribute,field name.Insert data by javaBean attributes order
 *insert into jd_user (age,name,weight,sex,time) values(?,?,?,...)
 */
 id=D.M(User.class).data("Tom",60,...).add();

/*JavaBean Mode,Automatically get the table name,
*primary key,auto-increment attribute,field name and value
*/
User user = new User();
user.setId(5);
user.setAge(10);
user.setName("Hello");

//insert into jd_user (age,name) values(?,?) Params[10,Hello]
num=D.M(user).add();

//insert into jd_user (name) values(?) Params[Hello]
num=D.M(user).field("name").add();

//insert into jd_user (id,age,name) values(?,?,?) Params[5,10,Hello]
num=D.M(user).autoInc(false).add();

0x06 Update method

Operation	Param	Note
save	long save()	data(),where() must be called in Table Mode; return the affected number of rows

long num=D.M("user").field("name,weight").data("Mike",100).where("id=?",1234).save();
User user = new User();
user.setId(5);
user.setAge(10);
user.setName("Hello");

//update jd_user set age=?,name=? where id=?; Params[10,Hello,5]
num=D.M(user).save();

//update jd_user set name=? where id=?; Params[Hello,5]
num=D.M(user).field("name").save();

//update jd_user set id=?,age=?,name=? where id=?; Params[5,10,Hello,4]
id=D.M(user).autoInc(false).fetchSql(true).where("id=?",user.getId()-1).save();

0x07 Delete Method

Operation	Param	Note
delete	long delete()	where() must be called in Table Mode; return the affected number of rows

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

long num=D.M("user").delete(5);//default-> id=?
num=D.M("user").delete("time",1523681398);//time=?
num=D.M(User.class).where("id>=?",13421).delete();

//JavaBean Mode
User user=new User();
user.setId(10L);
long num=D.M(user).delete();

0x08 Execute Method

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

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

0x09 Transaction

The DB engine must be InnoDB to support Transaction。
eg.

Connection conn=null;
try {
    //get the connection which has started transaction
    conn = D.M().startTrans();
    //operate the DB by the transaction connection
    long id=new M("gold").trans(conn).field("user_id,gold,type,time").data(3,5,0,System.currentTimeMillis()/1000).add();
    System.out.println(id);
    if(id>0) {
        throw new SQLException("Transaction Rollback Test");
    }
    id=new M("gold").trans(conn).field("user_id,gold,type,time").data(3,5,0,System.currentTimeMillis()/1000).add();
    System.out.println(id);
    //commit
    D.M().commit(conn);
} catch (SQLException e) {
    e.printStackTrace();
    try {
        //rollback
        D.M().rollback(conn);
    } catch (SQLException e1) {
        e1.printStackTrace();
    }
}

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.