daosql

#chinese introduction #简介 To see English version Introduction, please click here

daosql是一个将sql文件直接转换为数据库访问对象Dao(Data Access Object)代码的工具,daosql比目前使用的JAVA Dao工具 MyBatis和Hibernate使用更简单方便，程序员直接写可运行的SQL,然后用daosql生成dao代码即可，当前版本只支持mysql, oracle暂未支持,见以下例子: (假设保存在SQL源文件User.sql)

    -- desc: 查询用户列表
	-- name: getList
    select userid, username, mobile from users where age >=(20);
	
	...

以上User.sql经过daosql工具，自动生成以下UserDao.java 和 UserPo.java 代码, 其中(20)被识别为参数agebegin。

	class UserDao{
	    public  List<UserPo> getList(int agebegin) throws Exception{
		  String sql="select userid, username, mobile from users where age >=?"
		  ...
		}
		...
	
	}

	class UserPo {
	  ...
	  public Integer userid  ; 
	  public String username;
	  public String mobile;
	}

指定参数很简单，直接在SQL的样例数据上加()即可，一个SQL文件对应生成一个Dao类，一个文件可以包括多个SQL语句,每个语句以;结束，相应会生成多个Dao方法。

另外，只需指定表名，daosql就会生成表的增删改查方法。 例如，在User.sql中增加以下语句

  -- table: users
  ;

则daosql会生成以下4个方法:

//增加用户
public   int  insertUser( UserPo item ) throws Exception 
//根据主键查询用户
public   UserPo getUserByPrimary( long userid) throws Exception
//根据主键更新用户
public   int updateUserByPrimary( long userid , UserPo item) throws Exception
//根据主键删除用户
public   int deleteUserByPrimary( long userid ) throws Exception   

#daoql安装 ##1. 首先安装nodejs 本工具依赖于nodejs,首先安装nodejs，这个比较简单，直接按照安装指示下一步往下走即可，nodejs可从以下官网中下载： https://nodejs.org/

注意：

a. 一定要在环境变量PATH中添加nodejs的安装目录，windows下默认为C:\Program Files\nodejs。

b. 另外，PATH环境变量中还要添加nodejs的全局模块路径,默认情况下nodejs的全局模块路径是

 C:\Users\[windows用户]\AppData\Roaming\npm     

例如，windows用户是administrator，则nodejs的全局模块路径为

 C:\Users\administrator\AppData\Roaming\npm

后续的说明以administrator用户下的安装为例进行说明，如果不是administrator，那么以下说明中目录含administrator的地方更改为对应的用户名。

2. daosql安装

打开命令行终端,执行以下daosql安装命令

npm install daosql -g 

这个命令把daosql安装到nodejs全局模块路径下，默认情况下daosql安装到以下路径

C:\Users\administrator\AppData\Roaming\npm\node_modules\daosql 这个就是daosql的安装路径。

3. 配置daosql

如果是首次使用v1.1.0及以上版本的daosql,请执行以下命令初始化数据库配置：

  daosql init

以上命令生成daosql配置目录(C:\Users\administrator\daosql) 下的db.json文件, 请用文本编辑器编辑db.json，以设置正确的数据库连接地址及用户名密码：

[
{
"dbName":"default",
"host":"127.0.0.1" , 
"port": "3306",
"database":"user" , 
"user":"root" , 
"password":"" , 
"charset":"utf8"
}
] 

注：小于v1.1.0的daosql版本使用的db.json文件位于

  C:\Users\administrator\AppData\Roaming\npm\node_modules\daosql

而v1.1.0及以上的daosql版本的db.json位于

  C:\Users\administrator\daosql\db.json

#daosql使用 写sql文件，并生成sql文件对应的dao及po代码,在项目根目录下执行代码生成命令如下：

 daosql   -java projectJavaSourceCodeDirectory  relativeSqlFilePath

其中relativeSqlFilePath相对于projectJavaSourceCodeDirectory(项目java代码路径)目录, projectJavaSourceCodeDirectory相对于项目根目录的路径。

例子:

  daosql -java  ./src/main/java    com/daosqlsample/sql/User.sql

以上例子./src/main/java是项目java代码路径，当前执行命令的路径在项目工程根目录下. com/daosqlsample/sql/User.sql也存放在项目java代码路径中，所以sql文件相对于当前路径是：

./src/main/java/com/daosqlsample/sql/User.sql

对应生成的dao代码存放路径为

./src/main/java/java/com/daosqlsample/dao/UserDao.java

同时生成相应po代码，并存放在以下目录中

./src/main/java/java/com/daosqlsample/po/

从以上可以看到，生成的dao和po的目录与sql目录同级。

要批量生成项目代码目录下所有sql文件对应的dao及po代码,在项目根目录下执行以下生成命令即可：

 daosql   -java projectJavaSourceCodeDirectory

projectJavaSourceCodeDirectory是java源代码目录，是相对于项目根目录的路径,所有sql文件也存放在此目录下。

例子:

  daosql -java  ./src/main/java

以上命令将搜索./src/main/java目录（包括子目录）下的所有.sql文件，并逐个生成dao及po代码.

##完整例子 如果是首次使用v1.1.0及以上版本的daosql,请执行以下命令项目例子复制到指定目录下：

  daosql sample  projectPath

以上命令把项目例子复制到 项目目录projectPath下,进入projectPath目录下，可以看到这是一个这是个maven 工程， 可以用intellij IDEA 开发工具打开。

1. 首先,创建数据库user和usersum,并执行项目目录下的create_user_db.sql 及create_usersum_db.sql 中的语句创建例子需要的表。
2. 进入项目目录,可以看到以下2个sql文件

  ./src/main/java/com/daosqlsample/sql/User.sql
  ./src/main/java/com/daosqlsample/sql/UserSum.sql

执行以下命令生成Dao代码:

  daosql -java ./src/main/java     com/daosqlsample/sql/User.sql
  daosql -java ./src/main/java     com/daosqlsample/sql/UserSum.sql

或者执行以下命令生成所有sql文件的对应Dao代码:

  daosql -java ./src/main/java  

以上命令将自动生成以下Dao相关代码文件:

  ./src/main/java/com/daosqlsample/dao/UserDao.java
  ./src/main/java/com/daosqlsample/dao/UserSumDao.java
  ./src/main/java/com/daosqlsample/po/UserPo.java
  ./src/main/java/com/daosqlsample/po/UserExtPo.java
  ./src/main/java/com/daosqlsample/po/UserSumPo.java

3. 使用intellij IDEA 打开该工程并运行DaosqlApplication, 本工程支持访问2个数据库user和usersum，配置地址见:

./src/main/resources/application.yml

#daosql的语法 为了能够自动生成Dao代码，daosql在sql基础上叠加了一层简单的daosql语法, 主要就是 对sql中的样例数据加()来指定其为dao方法的参数。

##参数命名规则 ###条件参数命名规则 条件参数形如:

fieldname compare-operator (value)

其中，value是字符串或者数值， (value)转化为Dao代码时，表示为一个参数，参数名由字段名fieldname与compare-operator比较符决定。

1. 大于(或大于等于)比较符情况下, 参数名=fieldname+begin

   例子 age>=(20)

(20)表示参数agebegin

2. 小于(或小于等于)比较符情况下, 参数名=fieldname+end

例子 age<=(40)

(40)表示参数ageend

3. 等于比较符情况下, 参数名=fieldname

例子 age=(20)

(20)表示参数变量age

3. IN比较符情况下, 参数名=fieldname+s

例子 userid in ( (1) )

(1)表示参数userids，userids代表多个userid经过逗号拼接出来的字符串

###其他参数命令规则

1. AS别名符情况下, 参数名=fieldname

例子: ('2020-04-05') as sumdate

或者 ('2020-04-05') sumdate

('2020-04-05')表示参数sumdate

2. rename命名。 对于复杂情况，无法识别参数名或者想改个参数名，可以通过rename来指定。

一行SQL形如:

... (value) ... -- rename: parametername

则 (value)表示参数parametername

例子:

 addtime>= ('2010-01-01')  group by userid  -- rename: regtime

('2010-01-01')表示参数regtime

3. param 命名 有些参数不好通过以上方式指定，例如我们有多个表，结构类似，我们希望把表名做参数， 这时可以定义以下方式来定义一个参数：

-- param: "parameterName", value: "parameterSampleValue"

daosql将把SQL语句中所有出现 parameterSampleValue字符的地方全部替换为 parameterName的实际值 例子:

-- name: getUserSumList  
-- param: "sumtype", value: "trade"
select sumdate, usercount 
from trade_sum_daily
from sumdate>=('20200101')
; 

以上getUserSumList方法中我们可以传入sumtype=invite,则查询的是 invite_sum_daily表

##参数数据类型

1. Int整形参数形如(value), 其中value为Int整形, 表示转化为Dao方法输入参数时，类型变为整形参数。

例如 age=(20),则age为整形参数, 注意括号与数字之间不能有空格

2. Long长整形参数形如(value ), 其中value为Int整形, 注意value与)之间多了一个空格，表示转化为Dao方法输入参数时，类型变为长整形参数。

例如： userid=(10 ) 注意:整数与)之间后正好多了1个空格，所以userid为长整形参数

3. 字符串形参数形如('xxx'), 括号内为字符串，转化为Dao方法输入参数时，类型变为字符串参数。

例如： username=('tony')，所以username为字符串参数,, 注意括号与字符串之间不能有空格

4. 浮点行参数形如(xxx.yyy),括号内是浮点数，转化为Dao方法输入参数时，类型变为浮点双精度Double参数。

例如： salary>=(1000.00) 所以salarybegin为浮点双精度Double参数,, 注意括号与浮点数之间不能有空格

另外，不定义专门的日期类型参数，直接用字符串参数表示日期

##可选条件参数 如条件参数前面是2个空格，则是可选条件参数，当Dao方法传入该参数为null值时，则会忽略该条件。

例子：

-- name: getList
select userid, username, mobile from users where age >=(20)
  and salary>=(1000.00);

以上有2个条件参数agebegin 和 salarybegin ,其中salarybegin是可选参数，如果Dao方法传入的salarybegin=null, 则相当于不限定 and salary>=的条件

##生成类的命名规则
###方法名规则 SQL语句前（须单独一行） -- name: methodname 表示方法名为methodname的方法

例子：

  -- name: getList
  select ... ;
  表示生成方法名为getList的方法，所有方法均抛异常throws Exception

###Dao类名规则 Dao类名=Sql文件名+Dao

例子 User.sql 代码生成为 UserDao.java

###Dao包名规则 Dao包名=Sql源文件的父目录对应的java包名+.dao

SQL源代码文件 com/daosqlsample/sql/User.sql 的父目录为com/daosqlsample, UserDao包名为com.daosqlsample.dao 存放文件为com/daosqlsample/dao/UserDao.java

###持久化对象Po类名规则 持久化对象Po类名由方法名及文件名决定，只针对select语句产生相应的Po对象

1. Yyy.sql 中形如 getListXxx 或 getItemXxx的方法产生的Po命名为YyyPo.java

2. getZzzListXxx或或 getZzzItemXxx的方法产生的Po命名为ZzzPo.java

3. 其他情况methodname的Po命名为methodnamePo

##其他规则

###指定数据库 如果SQL文件需要操作多个数据库，则可以通过dbname来指定访问哪个数据库,例如：

-- dbname: usersum 这种情况下，生成的Dao代码使用JdbcTemplate usersumJdbc实例来操作数据库，如果不指定dbname,则默认使用 JdbcTemplate jdbc实例来操作数据库。 从0.7版本之后，指定数据库usersum需要在db.json中也做相应配置(本例子的数据库usersum的建表语句见sample/create_usesum_db.sql),db.json支持多个数据库的配置：

   [

        {
        "dbName": "default",
        "host":"127.0.0.1" , 
        "database":"user" , 
        "user":"root" , 
        "password":"" , 
        "charset":"utf8"
        } ,

        {
        "dbName": "usersum",
        "host":"127.0.0.1" , 
        "database":"usersum" , 
        "user":"root" , 
        "password":"" , 
        "charset":"utf8"
        }
]

例子(见UserSum.sql中的sql语句):

-- dbname: usersum
-- desc: 查询用户数
-- name: getList
-- param:"sumtype", value:"trade"
select sumdate, usercount from 
trade_sum_daily where sumdate>=('20200101') and sumdate<=('20200601')
;

以上例子查询的是usersum数据库中的表,如果没有指定dbname,则使用的dbname=default的对应数据库(以上例子default数据库是user)。

###快速生成增删改查 只需指定表名，daosql就会生成增删改查方法。 例如，在User.sql中增加以下语句

  -- table: users
  ;

则daosql会生成以下4个方法:

//增加用户
public   int  insertUser( UserPo item ) throws Exception 
//根据主键查询用户
public   UserPo getUserByPrimary( long userid) throws Exception
//根据主键更新用户
public   int updateUserByPrimary( long userid , UserPo item) throws Exception
//根据主键删除用户
public   int deleteUserByPrimary( long userid ) throws Exception   

如果还有唯一索引，则会生成根据唯一索引的查找、更新、删除的方法，名字后缀为By+唯一索引名。 如果只想针对users部分字段进行增删改查，则可以指定object、fields属性:

-- table    : users
-- object   : UserPassword
-- fields   : userid, password
  ;

则daosql会生成以下4个方法:

//增加用户密码
public   int  insertUserPassword( UserPasswordPo item ) throws Exception 
//根据主键查询用户密码
public   UserPasswordPo getUserPasswordByPrimary( long userid) throws Exception
//根据主键更新用户密码
public   int updateUserPasswordByPrimary( long userid , UserPasswordPo item) throws Exception
//根据主键删除用户（由于删除是整条用户记录，所以删除的对象仍为User而不是UserPasswrod）
public   int deleteUserByPrimary( long userid ) throws Exception   

如果有部分字段插入更新时不用设置(可能是自动更新，如自增长主键)，可以设置只读字段,可以有多个，逗号分隔，例如：

-- table    : users
-- readonly : userid, addtime, updatetime
  ;

###支持分页查询与排序 以下语句指定daosql生成的Dao方法支持排序和分页查询

  -- type: "order, page"

如果执行支持分页查询，则只保留page,如果只支持排序，则只保留order, 注意以上语句要求在方法名行的前面,如:

-- type: "order, page"
-- name: xxx

#Introduction 中文简介请跳转到文档顶部查看

daosql(Direct SQL) is a tool to convert sql to dao (Data Access Object) method, and it is easy to use than MyBatis and Hibernate, just write runnable sql, and use this tool to generate Dao class, look at the following example: ((support it is stored in a file named User.sql )

    -- desc: query user list
	-- name: getList
    select userid, username, mobile from users where age >=(20);
	
	...

daosql will automatically generate Dao class named UserDao.java and the record class UserPo.java (Po stands for Pesistent Object) for the above User.sql file , and (20) is identified as a parameter named agebegin of Dao method getList, the following is a part of generated code:

	class UserDao{
	    public  List<UserPo> getList(int agebegin) throws Exception{
		  String sql="select userid, username, mobile from users where age >=?"
		  ...
		}
		...
	
	}

	class UserPo {
	  ...
	  public Integer userid  ; 
	  public String username;
	  public String mobile;
	}

To specified which value is parameter is easy, just put () around the value ,that value will be identified as a dao method parameter, one SQL file will generate one Dao class ,a sql file can contain mulitiple sql statements, each sql statement will generate a dao method.

on the other hand, to generate insert/get/update/delete method, we only need to specified table name. for example, add the following statement in User.sql

  -- table: users
  ;

then daosql will generate the following 4 methods:

// add user
public   int  insertUser( UserPo item ) throws Exception 
// search the user by primary key
public   UserPo getUserByPrimary( long userid) throws Exception
// update the user by primary key
public   int updateUserByPrimary( long userid , UserPo item) throws Exception
// delete the user by primary key
public   int deleteUserByPrimary( long userid ) throws Exception   

#daosql installation ##1. first install nodejs   this tool dependent on nodejs,at first we need to install nodejs，nodejs installation is easy， just follow the instruction to finish the nodejs installation, nodejs can be download from https://nodejs.org/

Notice:
a. the evirnoment variable PATH must contain nodejs installation directory, the default installation path in windows is C:\Program Files\nodejs.

b. the evirnoment variable PATH should also contain the global module path of nodejs, the default global module path in windows is

 C:\Users\[windows user]\AppData\Roaming\npm
 
 for example,  if the windows user is administrator，then the global module path of nodejs is 

 C:\Users\administrator\AppData\Roaming\npm

for simplicity, the following introduction will assume we are installed using administrator.

##2. install daosql open command window ,and execute the following command in the command window:

npm install daosql -g 

this command will install daosql to the global module path of nodejs, by default the global module path of nodejs is

C:\Users\administrator\AppData\Roaming\npm

hence the daosql installation path will be :

C:\Users\administrator\AppData\Roaming\npm\node_modules\daosql

##3. configure daosql If you are the first time to use daosql v1.1.0 or above version, please execute the following command to init database configuration：

  daosql init

The above command create db.json under the daosql configuration directory( that is C:\Users\administrator\daosql), please edit db.json to set the correct mysql connection configuration, the following is the sample of db.json:

[
{
"dbName":"default",
"host":"127.0.0.1" , 
"database":"user" , 
"user":"root" , 
"password":"" , 
"charset":"utf8"
}
] 

Notice that,db.json of daosql version less than v1.1.0 is located in

  C:\Users\administrator\AppData\Roaming\npm\node_modules\daosql

For the daosql v1.1.0 or above version,db.json is located in

  C:\Users\administrator\daosql\db.json

#use daosql To generate dao class and po class for a sql file, type the following command in the command window in the project root directory:

 daosql   -java projectJavaSourceCodeDirectory  relativeSqlFilePath

here ,relativeSqlFilePath is a path relative to projectJavaSourceCodeDirectory( the project java source code directory), and projectJavaSourceCodeDirectory is a path relative the current project root directory

for example :

  daosql -java  ./src/main/java    com/daosqlsample/sql/User.sql

in this example, ./src/main/java is the project's java source code directory， and the sql file com/daosqlsample/sql/User.sql is stored under the java source directory, that is to say, the sql file is ./src/main/java/com/daosqlsample/sql/User.sql, the generated dao source code will be stored in ./src/main/java/com/daosqlsample/dao/UserDao.java

and the po source code is stored in the following directory:

./src/main/java/com/daosqlsample/po/

each po class is stored in one file. from the above example we can see that the dao and po and sql path is in the same level path.

To generate dao classes and po classes for all sql files in the directory, type the following command in the command window in the project root directory:

 daosql   -java projectJavaSourceCodeDirectory

projectJavaSourceCodeDirectory is the project's java source code directory，and all sql files are stored under the java source directory. for example :

  daosql -java  ./src/main/java 

the above command will search all .sql files under directory ./src/main/java (include subdirectory). and generate the corresponding dao and po codes.

##A sample project If you are the first time to use daosql v1.1.0 or above version,please execute the following command to copy the sample project to the specified project directory:

  daosql sample  projectPath

The above command copy the sample to the project directory (projectPath),enter projectPath directory, you will see it is a maven project, it can be opened with intellij IDEA.

1. First,create database user and usersum,there is table creation file named create_user_db.sql and create_usersum_db.sql, execute these two sql files to create tables for this example.

2. configure db.json under the daosql configuration directory (that is C:\Users\administrator\daosql), so that daosql can access the user and usersum database.

3. enter sample project directory, there are two sql files

  ./src/main/java/com/daosqlsample/sql/User.sql
  ./src/main/java/com/daosqlsample/sql/UserSum.sql

execute the following command to generate Dao source codes:

  daosql -java ./src/main/java     com/daosqlsample/sql/User.sql
  daosql -java ./src/main/java     com/daosqlsample/sql/UserSum.sql

or execute the following command to generate all Dao source codes:

  daosql -java ./src/main/java 

The above command will automatically generate the following source codes:

  ./src/main/java/com/daosqlsample/dao/UserDao.java
  ./src/main/java/com/daosqlsample/dao/UserSumDao.java
  ./src/main/java/com/daosqlsample/po/UserPo.java
  ./src/main/java/com/daosqlsample/po/UserExtPo.java
  ./src/main/java/com/daosqlsample/po/UserSumPo.java

4. open this sample project with intellij IDEA, and run DaosqlApplication, this project support two database: user and usersum, the address configuration is in the following file:

 ./src/main/resources/application.yml

#daosql syntax. in order to generate the dao source code ,daosql add some a simple syntax base on sql syntax. ##parameter naming rule ###conditional parameter conditional paramater looks like this :

   fieldname compare-operator (value)

here ,value is string or digital value, when daosql found this pattern, when generator dao source , (value) will be identified as a parameter, the name of paramater is determined by fieldname and compare-operator.

1. compare-operator is >( or >=), parameter name =fieldname+begin

   for example : age>=(20)

   (20) standards for parameter named agebegin

2. compare-operator is <( or <=), parameter name =fieldname+ end

   for example : age<=(40)

   (40) standards for parameter named ageend

3. compare-operator is =, parameter name =fieldname

   for example : age=(40)

   (40) standards for parameter named age

4. compare-operator is IN, parameter name =fieldname+s

   for example : userid in ( (1) )

   (1) standards for parameter named userids, userids is a string concated by many userid by ','

###other naming rule

1. AS naming rule

   In the following case ,parameter name =fieldname:

   (value) AS fielname

   example 1 : ('2020-04-05') AS sumdate

   example 2 : ('2020-04-05') sumdate

   In the above two example, ('2020-04-05') standards for parameter named sumdate

2. rename naming rule

rename parameter pattern look like this:

... (value) ... -- rename: parametername

here (value) stands for a parameter named parametername.

for example:

    addtime>= ('2010-01-01')  group by userid  -- rename: regtime

('2010-01-01') stands for parameter named regtime.

3. param naming rule

   In some case, we can not specify parameter in the above way, for example, we have many table have the same structure, we hope table name is a parameter. in this case, we can define a parameter in the following way:

-- param: "parameterName", value: "parameterSampleValue"

daosql will replace all substring parameterSampleValue with the value of parameter named parameterName. for example:

-- name: getUserSumList  
-- param: "sumtype", value: "trade"
select sumdate, usercount 
from trade_sum_daily
from sumdate>=('20200101')
; 

in the above case, when we pass parameter sumtype=invite to Dao method getUserSumList,it will actually query table invite_sum_daily.

##parameter data type

1. Integer parameter looks like this (xxx), here xxx is an integer.

for example: age=(20),then age is a integer parameter,notice that there is no space between the digit and )

2. Long parameter looks like this (xxx ), here xxx is a integer, and there is exactly one space between the digit and )

for example: age=(20 ),then age is a Long parameter,notice that there is exactly one space between the digit and )

3. String parameter looks like this ('xxx'), here 'xxx' is a string.

for example: username=('tony'),then username is a string parameter,notice that there is no space between the character and )

4. Double parameter looks like this (xxx), here xxx is an integer.

for example: salary>=(1000.00),then age is a double parameter,notice that there is no space between the digit and )

notice that we do not define date type parameter, just use string parameter to represent date parameter.

##optional parameter if a conditional parameter line is begin with two space, then that parameter will be optional, when calling Dao method with that parameter equal to null, then that parameter is igorne.

for example:

-- name: getList
select userid, username, mobile from users where age >=(20)
  and salary>=(1000.00);

the above Dao method getList has two conditional parameters: agebegin and salarybegin ,salarybegin is a optional parameter ，if salarybegin passed to Dao method getList is equal to null, then the condition ( and salary>= ) is igorne.

on the other hand, agebegin is not a optional parameter, even pass null, the conditional age >=null still take effect, that cause getList return a empty result set.

generated class naming rule

###Dao class naming rule Dao class name=Sql file name without extension + Dao

for example, Dao class name for User.sql is UserDao

###Dao package naming rule Dao package name = the parent's parent path of sql file relative path + .dao

for example, sql file relative path is

com/daosqlsample/sql/User.sql, its parent's parent path is

com/daosqlsample, so UserDao package is:

com.daosqlsample.dao,

UserDao is stored in

com/daosqlsample/dao/UserDao.java

###Po (persitence Object) class naming rule The Dao method for select sql will return a result set with type of List, here XXXPo is a Po (Persitence Object) class , the name for Po class is as follow:

1. getListXxx or getItemXxx in Yyy.sql will have a Po class named YyyPo

2. getZzzListXxx or getZzzItemXxx in Yyy.sql will have a Po class named ZzzPo

3. otherwise methodname's Po class will be methodnamePo

Notice that only select statement has corresponding Po class.

###Dao method naming rule each sql statement must have a name, the Dao method name is equal to the sql name, it names in this way:

-- name: sqlname

for example:

  -- name: getList
  select ... ;
  

the above sql statement name is getList, so the corresponding Dao method is getList .

##other rule ###specify database if we need to access multiple database in one sql file, we can specify the target database by dbname, for example:

-- dbname: test2 In this case, we will use JdbcTemplate instance named test2Jdbc to access database, if dbname is not specified,we will use JdbcTemplate instance named jdbc to access database. from version 0.7, if we specify dbname=test2, then db.json must config for test2：

[
	{
	"dbName": "default",
	"host":"127.0.0.1" , 
	"database":"user" , 
	"user":"root" , 
	"password":"" , 
	"charset":"utf8"
	} ,

	{
	"dbName": "usersum",
	"host":"127.0.0.1" , 
	"database":"usersum" , 
	"user":"root" , 
	"password":"" , 
	"charset":"utf8"
	}
]

example (see sql statement in UserSum.sql) :

-- dbname: usersum
-- desc: query summary for user
-- name: getList
-- param:"sumtype", value:"trade"
select sumdate, usercount from 
trade_sum_daily where sumdate>=('20200101') and sumdate<=('20200601')
;

the sql query table in database named usersum, by default, no dbname is specifed, we use dbname=default.

###fast generate insert/get/update/delete we only need to specified table ，daosql will generate insert/get/update/delete method. for example, add the following statement in User.sql

  -- table: users
  ;

then daosql will generate the following 4 methods:

// add user
public   int  insertUser( UserPo item ) throws Exception 
// search the user by primary key
public   UserPo getUserByPrimary( long userid) throws Exception
// update the user by primary key
public   int updateUserByPrimary( long userid , UserPo item) throws Exception
// delete the user by primary key
public   int deleteUserByPrimary( long userid ) throws Exception   

if the table has unique key, daosql will also generate getUserByXXX, updateUserByXXX, deleteUserByXXX,where XXX is the unique key name. if we only want to insert/get/update the partial field of the table, then we will need to provide object、fields attribute:

-- table    : users
-- object   : UserPassword
-- fields   : userid, password
  ;

then daosql will generate the following 4 methods:

// add user password
public   int  insertUserPassword( UserPasswordPo item ) throws Exception 
// search the user password by primary key
public   UserPasswordPo getUserPasswordByPrimary( long userid) throws Exception
// update the user password by primary key
public   int updateUserPasswordByPrimary( long userid , UserPasswordPo item) throws Exception
// delete the user by primary key ( since what we delete is user record, so that it is deleteUserByPrimary,instead of deleteUserPasswordByPrimary）
public   int deleteUserByPrimary( long userid ) throws Exception   

If there are some fields that we don't need to set it's values, maybe these fields are auto updated, for example auto increment key field, then we can specified these fields in readonly field,if there are two or more readonly fields , separate field name by ',' For example:

-- table    : users
-- readonly : userid, addtime, updatetime
  ;

###support query by page and order The following statement indicate daosql to generate Dao method supporting query by page and order

  -- type: "order, page"

If we only need to support query by page, then only reserve page, if only support query by order ,then only reserve order. Notice that the above statement must appear before dao method name statement ,for example:

-- type: "order, page"
-- name: xxx