com.jfinal.plugin.activerecord

Class DbPro

java.lang.Object

com.jfinal.plugin.activerecord.DbPro

public class DbPro
extends Object

DbPro. Professional database query and update tool.

Field Summary

Fields

Modifier and Type	Field and Description
protected Config	config

Constructor Summary

Constructors

Constructor and Description
DbPro()
DbPro(String configName)

Method Summary

Modifier and Type	Method and Description
protected int[]	batch(Config config, Connection conn, List<String> sqlList, int batchSize)
protected int[]	batch(Config config, Connection conn, String sql, Object[][] paras, int batchSize)
protected int[]	batch(Config config, Connection conn, String sql, String columns, List list, int batchSize)
int[]	batch(List<String> sqlList, int batchSize) Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
int[]	batch(String sql, Object[][] paras, int batchSize) Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
int[]	batch(String sql, String columns, List modelOrRecordList, int batchSize) Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
int[]	batchSave(List<? extends Model> modelList, int batchSize) Batch save models using the "insert into ..." sql generated by the first model in modelList.
int[]	batchSave(String tableName, List<Record> recordList, int batchSize) Batch save records using the "insert into ..." sql generated by the first record in recordList.
int[]	batchUpdate(List<? extends Model> modelList, int batchSize) Batch update models using the attrs names of the first model in modelList.
int[]	batchUpdate(String tableName, List<Record> recordList, int batchSize) Batch update records with default primary key, using the columns names of the first record in recordList.
int[]	batchUpdate(String tableName, String primaryKey, List<Record> recordList, int batchSize) Batch update records using the columns names of the first record in recordList.
int	delete(String sql)
int	delete(String sql, Object... paras) Execute delete sql statement.
boolean	delete(String tableName, Record record) Example: boolean succeed = DbPro.use().delete("user", user);
boolean	delete(String tableName, String primaryKey, Record record) Delete record.
boolean	deleteById(String tableName, Object idValue) Delete record by id with default primary key.
boolean	deleteById(String tableName, String primaryKey, Object... idValue) Delete record by id.
protected Page<Record>	doPaginate(int pageNumber, int pageSize, Boolean isGroupBySql, String select, String sqlExceptSelect, Object... paras)
protected Page<Record>	doPaginateByCache(String cacheName, Object key, int pageNumber, int pageSize, Boolean isGroupBySql, String select, String sqlExceptSelect, Object... paras)
protected Page<Record>	doPaginateByFullSql(Config config, Connection conn, int pageNumber, int pageSize, Boolean isGroupBySql, String totalRowSql, StringBuilder findSql, Object... paras)
protected Page<Record>	doPaginateByFullSql(int pageNumber, int pageSize, Boolean isGroupBySql, String totalRowSql, String findSql, Object... paras)
Object	execute(ICallback callback)
List<Record>	find(SqlPara sqlPara)
List<Record>	find(String sql)
List<Record>	find(String sql, Object... paras)
List<Record>	findByCache(String cacheName, Object key, String sql)
List<Record>	findByCache(String cacheName, Object key, String sql, Object... paras) Find Record by cache.
Record	findById(String tableName, Object idValue) Find record by id with default primary key.
Record	findById(String tableName, String primaryKey, Object... idValue) Find record by id.
Record	findFirst(SqlPara sqlPara)
Record	findFirst(String sql)
Record	findFirst(String sql, Object... paras) Find first record.
Record	findFirstByCache(String cacheName, Object key, String sql)
Record	findFirstByCache(String cacheName, Object key, String sql, Object... paras) Find first record by cache.
Config	getConfig()
String	getSql(String key)
SqlPara	getSqlPara(String key, Map data)
SqlPara	getSqlPara(String key, Model model)
SqlPara	getSqlPara(String key, Object... paras)
SqlPara	getSqlPara(String key, Record record)
Page<Record>	paginate(int pageNumber, int pageSize, boolean isGroupBySql, String select, String sqlExceptSelect, Object... paras)
Page<Record>	paginate(int pageNumber, int pageSize, SqlPara sqlPara)
Page<Record>	paginate(int pageNumber, int pageSize, String select, String sqlExceptSelect)
Page<Record>	paginate(int pageNumber, int pageSize, String select, String sqlExceptSelect, Object... paras) Paginate.
Page<Record>	paginateByCache(String cacheName, Object key, int pageNumber, int pageSize, boolean isGroupBySql, String select, String sqlExceptSelect, Object... paras)
Page<Record>	paginateByCache(String cacheName, Object key, int pageNumber, int pageSize, String select, String sqlExceptSelect)
Page<Record>	paginateByCache(String cacheName, Object key, int pageNumber, int pageSize, String select, String sqlExceptSelect, Object... paras) Paginate by cache.
Page<Record>	paginateByFullSql(int pageNumber, int pageSize, boolean isGroupBySql, String totalRowSql, String findSql, Object... paras)
Page<Record>	paginateByFullSql(int pageNumber, int pageSize, String totalRowSql, String findSql, Object... paras)
<T> List<T>	query(String sql)
<T> List<T>	query(String sql, Object... paras)
BigDecimal	queryBigDecimal(String sql)
BigDecimal	queryBigDecimal(String sql, Object... paras)
Boolean	queryBoolean(String sql)
Boolean	queryBoolean(String sql, Object... paras)
Byte	queryByte(String sql)
Byte	queryByte(String sql, Object... paras)
byte[]	queryBytes(String sql)
byte[]	queryBytes(String sql, Object... paras)
<T> T	queryColumn(String sql)
<T> T	queryColumn(String sql, Object... paras) Execute sql query just return one column.
Date	queryDate(String sql)
Date	queryDate(String sql, Object... paras)
Double	queryDouble(String sql)
Double	queryDouble(String sql, Object... paras)
<T> T	queryFirst(String sql)
<T> T	queryFirst(String sql, Object... paras) Execute sql query and return the first result.
Float	queryFloat(String sql)
Float	queryFloat(String sql, Object... paras)
Integer	queryInt(String sql)
Integer	queryInt(String sql, Object... paras)
Long	queryLong(String sql)
Long	queryLong(String sql, Object... paras)
Number	queryNumber(String sql)
Number	queryNumber(String sql, Object... paras)
Short	queryShort(String sql)
Short	queryShort(String sql, Object... paras)
String	queryStr(String sql)
String	queryStr(String sql, Object... paras)
Time	queryTime(String sql)
Time	queryTime(String sql, Object... paras)
Timestamp	queryTimestamp(String sql)
Timestamp	queryTimestamp(String sql, Object... paras)
boolean	save(String tableName, Record record)
boolean	save(String tableName, String primaryKey, Record record) Save record.
boolean	tx(IAtom atom) Execute transaction with default transaction level.
boolean	tx(int transactionLevel, IAtom atom)
int	update(SqlPara sqlPara)
int	update(String sql)
int	update(String sql, Object... paras) Execute update, insert or delete sql statement.
boolean	update(String tableName, Record record) Update record with default primary key.
boolean	update(String tableName, String primaryKey, Record record) Update Record.
static DbPro	use() Deprecated.
static DbPro	use(String configName) Deprecated.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

config

protected final Config config

Constructor Detail

DbPro

public DbPro()

DbPro

public DbPro(String configName)

Method Detail

use

@Deprecated
public static DbPro use(String configName)

Deprecated.

建议用 Db.use(configName) 代替，未来版本会去除该方法

use

@Deprecated
public static DbPro use()

Deprecated.

建议用 Db.use() 代替，未来版本会去除该方法

getConfig

public Config getConfig()

query

public <T> List<T> query(String sql,
                         Object... paras)

See Also:

#query(String, String, Object...)

query

public <T> List<T> query(String sql)

Parameters:

sql - an SQL statement

See Also:

query(String, Object...)

queryFirst

public <T> T queryFirst(String sql,
                        Object... paras)

Execute sql query and return the first result. I recommend add "limit 1" in your sql.

Parameters:

sql - an SQL statement that may contain one or more '?' IN parameter placeholders

paras - the parameters of sql

Returns:

Object[] if your sql has select more than one column, and it return Object if your sql has select only one column.

queryFirst

public <T> T queryFirst(String sql)

Parameters:

sql - an SQL statement

See Also:

queryFirst(String, Object...)

queryColumn

public <T> T queryColumn(String sql,
                         Object... paras)

Execute sql query just return one column.

Type Parameters:

T - the type of the column that in your sql's select statement

Parameters:

sql - an SQL statement that may contain one or more '?' IN parameter placeholders

paras - the parameters of sql

Returns:

T

queryColumn

public <T> T queryColumn(String sql)

queryStr

public String queryStr(String sql,
                       Object... paras)

queryStr

public String queryStr(String sql)

queryInt

public Integer queryInt(String sql,
                        Object... paras)

queryInt

public Integer queryInt(String sql)

queryLong

public Long queryLong(String sql,
                      Object... paras)

queryLong

public Long queryLong(String sql)

queryDouble

public Double queryDouble(String sql,
                          Object... paras)

queryDouble

public Double queryDouble(String sql)

queryFloat

public Float queryFloat(String sql,
                        Object... paras)

queryFloat

public Float queryFloat(String sql)

queryBigDecimal

public BigDecimal queryBigDecimal(String sql,
                                  Object... paras)

queryBigDecimal

public BigDecimal queryBigDecimal(String sql)

queryBytes

public byte[] queryBytes(String sql,
                         Object... paras)

queryBytes

public byte[] queryBytes(String sql)

queryDate

public Date queryDate(String sql,
                      Object... paras)

queryDate

public Date queryDate(String sql)

queryTime

public Time queryTime(String sql,
                      Object... paras)

queryTime

public Time queryTime(String sql)

queryTimestamp

public Timestamp queryTimestamp(String sql,
                                Object... paras)

queryTimestamp

public Timestamp queryTimestamp(String sql)

queryBoolean

public Boolean queryBoolean(String sql,
                            Object... paras)

queryBoolean

public Boolean queryBoolean(String sql)

queryShort

public Short queryShort(String sql,
                        Object... paras)

queryShort

public Short queryShort(String sql)

queryByte

public Byte queryByte(String sql,
                      Object... paras)

queryByte

public Byte queryByte(String sql)

queryNumber

public Number queryNumber(String sql,
                          Object... paras)

queryNumber

public Number queryNumber(String sql)

update

public int update(String sql,
                  Object... paras)

Execute update, insert or delete sql statement.

Parameters:

sql - an SQL statement that may contain one or more '?' IN parameter placeholders

paras - the parameters of sql

Returns:

either the row count for INSERT, UPDATE, or DELETE statements, or 0 for SQL statements that return nothing

update

public int update(String sql)

Parameters:

sql - an SQL statement

See Also:

update(String, Object...)

find

public List<Record> find(String sql,
                         Object... paras)

See Also:

#find(String, String, Object...)

find

public List<Record> find(String sql)

Parameters:

sql - the sql statement

See Also:

#find(String, String, Object...)

findFirst

public Record findFirst(String sql,
                        Object... paras)

Find first record. I recommend add "limit 1" in your sql.

Parameters:

sql - an SQL statement that may contain one or more '?' IN parameter placeholders

paras - the parameters of sql

Returns:

the Record object

findFirst

public Record findFirst(String sql)

Parameters:

sql - an SQL statement

See Also:

findFirst(String, Object...)

findById

public Record findById(String tableName,
                       Object idValue)

Find record by id with default primary key.

 Example:
 Record user = DbPro.use().findById("user", 15);
 

Parameters:

tableName - the table name of the table

idValue - the id value of the record

findById

public Record findById(String tableName,
                       String primaryKey,
                       Object... idValue)

Find record by id.

 Example:
 Record user = DbPro.use().findById("user", "user_id", 123);
 Record userRole = DbPro.use().findById("user_role", "user_id, role_id", 123, 456);
 

Parameters:

tableName - the table name of the table

primaryKey - the primary key of the table, composite primary key is separated by comma character: ","

idValue - the id value of the record, it can be composite id values

deleteById

public boolean deleteById(String tableName,
                          Object idValue)

Delete record by id with default primary key.

 Example:
 DbPro.use().deleteById("user", 15);
 

Parameters:

tableName - the table name of the table

idValue - the id value of the record

Returns:

true if delete succeed otherwise false

deleteById

public boolean deleteById(String tableName,
                          String primaryKey,
                          Object... idValue)

Delete record by id.

 Example:
 DbPro.use().deleteById("user", "user_id", 15);
 DbPro.use().deleteById("user_role", "user_id, role_id", 123, 456);
 

Parameters:

tableName - the table name of the table

primaryKey - the primary key of the table, composite primary key is separated by comma character: ","

idValue - the id value of the record, it can be composite id values

Returns:

true if delete succeed otherwise false

delete

public boolean delete(String tableName,
                      String primaryKey,
                      Record record)

Delete record.

 Example:
 boolean succeed = DbPro.use().delete("user", "id", user);
 

Parameters:

tableName - the table name of the table

primaryKey - the primary key of the table, composite primary key is separated by comma character: ","

record - the record

Returns:

true if delete succeed otherwise false

delete

public boolean delete(String tableName,
                      Record record)

 Example:
 boolean succeed = DbPro.use().delete("user", user);
 

See Also:

delete(String, String, Record)

delete

public int delete(String sql,
                  Object... paras)

Execute delete sql statement.

Parameters:

sql - an SQL statement that may contain one or more '?' IN parameter placeholders

paras - the parameters of sql

Returns:

the row count for DELETE statements, or 0 for SQL statements that return nothing

delete

public int delete(String sql)

Parameters:

sql - an SQL statement

See Also:

delete(String, Object...)

paginate

public Page<Record> paginate(int pageNumber,
                             int pageSize,
                             String select,
                             String sqlExceptSelect,
                             Object... paras)

Paginate.

Parameters:

pageNumber - the page number

pageSize - the page size

select - the select part of the sql statement

sqlExceptSelect - the sql statement excluded select part

paras - the parameters of sql

Returns:

the Page object

paginate

public Page<Record> paginate(int pageNumber,
                             int pageSize,
                             String select,
                             String sqlExceptSelect)

See Also:

#paginate(String, int, int, String, String, Object...)

paginate

public Page<Record> paginate(int pageNumber,
                             int pageSize,
                             boolean isGroupBySql,
                             String select,
                             String sqlExceptSelect,
                             Object... paras)

doPaginate

protected Page<Record> doPaginate(int pageNumber,
                                  int pageSize,
                                  Boolean isGroupBySql,
                                  String select,
                                  String sqlExceptSelect,
                                  Object... paras)

doPaginateByFullSql

protected Page<Record> doPaginateByFullSql(Config config,
                                           Connection conn,
                                           int pageNumber,
                                           int pageSize,
                                           Boolean isGroupBySql,
                                           String totalRowSql,
                                           StringBuilder findSql,
                                           Object... paras)
                                    throws SQLException

Throws:

SQLException

doPaginateByFullSql

protected Page<Record> doPaginateByFullSql(int pageNumber,
                                           int pageSize,
                                           Boolean isGroupBySql,
                                           String totalRowSql,
                                           String findSql,
                                           Object... paras)

paginateByFullSql

public Page<Record> paginateByFullSql(int pageNumber,
                                      int pageSize,
                                      String totalRowSql,
                                      String findSql,
                                      Object... paras)

paginateByFullSql

public Page<Record> paginateByFullSql(int pageNumber,
                                      int pageSize,
                                      boolean isGroupBySql,
                                      String totalRowSql,
                                      String findSql,
                                      Object... paras)

save

public boolean save(String tableName,
                    String primaryKey,
                    Record record)

Save record.

 Example:
 Record userRole = new Record().set("user_id", 123).set("role_id", 456);
 DbPro.use().save("user_role", "user_id, role_id", userRole);
 

Parameters:

tableName - the table name of the table

primaryKey - the primary key of the table, composite primary key is separated by comma character: ","

record - the record will be saved

true - if save succeed otherwise false

save

public boolean save(String tableName,
                    Record record)

See Also:

save(String, String, Record)

update

public boolean update(String tableName,
                      String primaryKey,
                      Record record)

Update Record.

 Example:
 DbPro.use().update("user_role", "user_id, role_id", record);
 

Parameters:

tableName - the table name of the Record save to

primaryKey - the primary key of the table, composite primary key is separated by comma character: ","

record - the Record object

true - if update succeed otherwise false

update

public boolean update(String tableName,
                      Record record)

Update record with default primary key.

 Example:
 DbPro.use().update("user", record);
 

See Also:

update(String, String, Record)

execute

public Object execute(ICallback callback)

See Also:

#execute(String, ICallback)

tx

public boolean tx(int transactionLevel,
                  IAtom atom)

tx

public boolean tx(IAtom atom)

Execute transaction with default transaction level.

See Also:

tx(int, IAtom)

findByCache

public List<Record> findByCache(String cacheName,
                                Object key,
                                String sql,
                                Object... paras)

Find Record by cache.

Parameters:

cacheName - the cache name

key - the key used to get date from cache

Returns:

the list of Record

See Also:

find(String, Object...)

findByCache

public List<Record> findByCache(String cacheName,
                                Object key,
                                String sql)

See Also:

findByCache(String, Object, String, Object...)

findFirstByCache

public Record findFirstByCache(String cacheName,
                               Object key,
                               String sql,
                               Object... paras)

Find first record by cache. I recommend add "limit 1" in your sql.

Parameters:

cacheName - the cache name

key - the key used to get date from cache

sql - an SQL statement that may contain one or more '?' IN parameter placeholders

paras - the parameters of sql

Returns:

the Record object

See Also:

findFirst(String, Object...)

findFirstByCache

public Record findFirstByCache(String cacheName,
                               Object key,
                               String sql)

See Also:

findFirstByCache(String, Object, String, Object...)

paginateByCache

public Page<Record> paginateByCache(String cacheName,
                                    Object key,
                                    int pageNumber,
                                    int pageSize,
                                    String select,
                                    String sqlExceptSelect,
                                    Object... paras)

Paginate by cache.

Returns:

Page

See Also:

paginate(int, int, String, String, Object...)

paginateByCache

public Page<Record> paginateByCache(String cacheName,
                                    Object key,
                                    int pageNumber,
                                    int pageSize,
                                    String select,
                                    String sqlExceptSelect)

See Also:

paginateByCache(String, Object, int, int, String, String, Object...)

paginateByCache

public Page<Record> paginateByCache(String cacheName,
                                    Object key,
                                    int pageNumber,
                                    int pageSize,
                                    boolean isGroupBySql,
                                    String select,
                                    String sqlExceptSelect,
                                    Object... paras)

doPaginateByCache

protected Page<Record> doPaginateByCache(String cacheName,
                                         Object key,
                                         int pageNumber,
                                         int pageSize,
                                         Boolean isGroupBySql,
                                         String select,
                                         String sqlExceptSelect,
                                         Object... paras)

batch

protected int[] batch(Config config,
                      Connection conn,
                      String sql,
                      Object[][] paras,
                      int batchSize)
               throws SQLException

Throws:

SQLException

batch

public int[] batch(String sql,
                   Object[][] paras,
                   int batchSize)

Execute a batch of SQL INSERT, UPDATE, or DELETE queries.

 Example:
 String sql = "insert into user(name, cash) values(?, ?)";
 int[] result = DbPro.use().batch(sql, new Object[][]{{"James", 888}, {"zhanjin", 888}});
 

Parameters:

sql - The SQL to execute.

paras - An array of query replacement parameters. Each row in this array is one set of batch replacement values.

Returns:

The number of rows updated per statement

batch

protected int[] batch(Config config,
                      Connection conn,
                      String sql,
                      String columns,
                      List list,
                      int batchSize)
               throws SQLException

Throws:

SQLException

batch

public int[] batch(String sql,
                   String columns,
                   List modelOrRecordList,
                   int batchSize)

Execute a batch of SQL INSERT, UPDATE, or DELETE queries.

 Example:
 String sql = "insert into user(name, cash) values(?, ?)";
 int[] result = DbPro.use().batch(sql, "name, cash", modelList, 500);
 

Parameters:

sql - The SQL to execute.

columns - the columns need be processed by sql.

modelOrRecordList - model or record object list.

batchSize - batch size.

Returns:

The number of rows updated per statement

batch

protected int[] batch(Config config,
                      Connection conn,
                      List<String> sqlList,
                      int batchSize)
               throws SQLException

Throws:

SQLException

batch

public int[] batch(List<String> sqlList,
                   int batchSize)

Execute a batch of SQL INSERT, UPDATE, or DELETE queries.

 Example:
 int[] result = DbPro.use().batch(sqlList, 500);
 

Parameters:

sqlList - The SQL list to execute.

batchSize - batch size.

Returns:

The number of rows updated per statement

batchSave

public int[] batchSave(List<? extends Model> modelList,
                       int batchSize)

Batch save models using the "insert into ..." sql generated by the first model in modelList. Ensure all the models can use the same sql as the first model.

batchSave

public int[] batchSave(String tableName,
                       List<Record> recordList,
                       int batchSize)

Batch save records using the "insert into ..." sql generated by the first record in recordList. Ensure all the record can use the same sql as the first record.

Parameters:

tableName - the table name

batchUpdate

public int[] batchUpdate(List<? extends Model> modelList,
                         int batchSize)

Batch update models using the attrs names of the first model in modelList. Ensure all the models can use the same sql as the first model.

batchUpdate

public int[] batchUpdate(String tableName,
                         String primaryKey,
                         List<Record> recordList,
                         int batchSize)

Batch update records using the columns names of the first record in recordList. Ensure all the records can use the same sql as the first record.

Parameters:

tableName - the table name

primaryKey - the primary key of the table, composite primary key is separated by comma character: ","

batchUpdate

public int[] batchUpdate(String tableName,
                         List<Record> recordList,
                         int batchSize)

Batch update records with default primary key, using the columns names of the first record in recordList. Ensure all the records can use the same sql as the first record.

Parameters:

tableName - the table name

getSql

public String getSql(String key)

getSqlPara

public SqlPara getSqlPara(String key,
                          Record record)

getSqlPara

public SqlPara getSqlPara(String key,
                          Model model)

getSqlPara

public SqlPara getSqlPara(String key,
                          Map data)

getSqlPara

public SqlPara getSqlPara(String key,
                          Object... paras)

find

public List<Record> find(SqlPara sqlPara)

findFirst

public Record findFirst(SqlPara sqlPara)

update

public int update(SqlPara sqlPara)

paginate

public Page<Record> paginate(int pageNumber,
                             int pageSize,
                             SqlPara sqlPara)