dsql
a tool to convert sql to dao method, easy to use than MyBatis and Hibernate, just write runnable sql, and use this tool to generate Dao class.
Last updated 2 months ago by tonygeneral .
MIT · Original npm · Tarball · package.json
$ cnpm install dsql 
SYNC missed versions from official npm registry.

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

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

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

以上User.sql经过dsql工具,自动生成以下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方法。

#dql安装 ##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用户下的安装为例进行说明。

2. dsql安装

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

npm install dsql -g 

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

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

3. 配置dsql

编辑dsql的安装路径下的db.json,设置正确的数据库连接地址及用户名密码,默认情况下也就是编辑以下文件:

C:\Users\administrator\AppData\Roaming\npm\node_modules\dsql\db.json

{
"host":"192.168.1.105" , 
"database":"dsqltest" , 
"user":"test" , 
"password":"test" , 
"charset":"utf8"
} 

#dsql使用 写sql文件,并生成sql文件对应的dao及po代码,生成命令如下:

 dsql   -java projectJavaSourceCodeDirectory  relativeSqlFilePath

其中relativeSqlFilePath相对于projectJavaSourceCodeDirectory(项目java代码路径)目录, projectJavaSourceCodeDirectory相对于执行本命令所在目录

例子:

  dsql -java  ../dsql4sample/src/main/java    com/dsql4sample/sql/Test.sql 

以上例子../dsql4sample/src/main/java是项目java代码路径, sql文件com/dsql4sample/sql/Test.sql也存放在项目java代码路径中,所以sql文件相对于当前路径是:

../dsql4sample/src/main/java/com/dsql4sample/sql/Test.sql

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

../dsql4sample/src/main/java/com/dsql4sample/dao/TestDao.java

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

../dsql4sample/src/main/java/com/dsql4sample/po/

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

##完整例子 例子存放在dsql安装目录下的sample目录,默认情况下也就是:

C:\Users\administrator\AppData\Roaming\npm\node_modules\dsql\sample

  1. 首先,创建数据库dsqltest,并执行sample目录下的create_dsqltest_db.sql中的语句创建例子需要的表。

  2. 然后配置dsql安装目录下的db.json 以便可以正确访问dsqltest数据库。

  3. 进入sample目录,执行以下命令:

  dsql -java ./src/main/java     com/dsql4jdbcsample/sql/User.sql

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

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

#dsql的语法 为了能够自动生成Dao代码,dsql在sql基础上叠加了一层dsql语法。

##参数命名规则 ###条件参数命名规则 fieldname compare-operator (value) value是字符串或者数值,则 (value)转化为Dao代码时,表示为一个参数,参数名由字段名fieldname与compare-operator比较符决定。

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

    例子 age>=(20)

(20)表示参数agebegin
  1. 小于(或小于等于)比较符情况下, 参数名=fieldname+end

例子 age<=(40)

(40)表示参数ageend

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

例子 age=(20)

(20)表示参数变量age

  1. 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

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

一行SQL形如:

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

则 (value)表示参数parametername

例子:

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

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

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

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

dsql将把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为整形参数, 注意括号与数字之间不能有空格

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

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

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

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

  1. 浮点行参数形如(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/dsqlsample/sql/User.sql 的父目录为com/dsqlsample, UserDao包名为com.dsqlsample.dao 存放文件为com/dsqlsample/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: test2 这种情况下,生成的Dao代码使用JdbcTemplate test2Jdbc实例来操作数据库,如果不指定dbname,则默认使用 JdbcTemplate jdbc实例来操作数据库。

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

dsql(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);
	
	...

dsql 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.

#dql 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 dsql open command window ,and execute the following command in the command window:

npm install dsql -g 

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

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

hence the dsql installation path will be :

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

##3. configure dsql edit db.json in the dsql installation path to the correct mysql connection setting, the following is the sample of db.json

{
"host":"192.168.1.105" , 
"database":"test" , 
"user":"test" , 
"password":"test" , 
"charset":"utf8"
} 

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

 dsql   -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 directory when running the above command

for example :

  dsql -java  ../dsql4sample/src/main/java    com/dsql4sample/sql/Test.sql 

in this example, ../dsql4sample/src/main/java is the project dsq4sample's java source code directory, and the sql file com/dsql4sample/sql/Test.sql is stored under the java source directory, that is to say, the sql file is ../dsql4sample/src/main/java/com/dsql4sample/sql/Test.sql, the generated dao source code will be stored in ../dsql4sample/src/main/java/com/dsql4sample/dao/TestDao.java

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

../dsql4sample/src/main/java/com/dsql4sample/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.

##example The example is stored under the sample subdirectory of dsql installation directory, by default the sample directory will be :

C:\Users\administrator\AppData\Roaming\npm\node_modules\dsql\sample

  1. First,create database dsqltest,there is table creation file named create_dsqltest_db.sql, execute sql command in this file to create tables for this example.

  2. configure db.json under the dsql installation directory (by default is C:\Users\administrator\AppData\Roaming\npm\node_modules\dsql ), so that dsql can access the dsqltest database

  3. enter sample directory, execute the following command:

  dsql -java ./src/main/java     com/dsql4jdbcsample/sql/User.sql

The above command will automatically generate the following source codes:

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

#dsql syntax. in order to generate the dao source code ,dsql 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 dsql 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.

  1. 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"

dsql 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 )

  1. 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 )

  1. 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 )

  1. 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/dsqlsample/sql/User.sql, its parent's parent path is

com/dsqlsample, so UserDao package is:

com.dsqlsample.dao,

UserDao is stored in

com/dsqlsample/dao/UserDao.java

###Po (persitence Object) class naming rule The Dao method for select sql will return a result set with type of List<XXXPo>, 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.

Current Tags

  • 0.6.13                                ...           latest (2 months ago)

30 Versions

  • 0.6.13                                ...           2 months ago
  • 0.6.12                                ...           2 months ago
  • 0.6.11                                ...           2 months ago
  • 0.6.10                                ...           2 months ago
  • 0.6.9                                ...           2 months ago
  • 0.6.8                                ...           2 months ago
  • 0.6.7                                ...           2 months ago
  • 0.6.6                                ...           2 months ago
  • 0.6.5                                ...           2 months ago
  • 0.6.4                                ...           2 months ago
  • 0.6.3                                ...           2 months ago
  • 0.6.2                                ...           2 months ago
  • 0.6.1                                ...           2 months ago
  • 0.6.0                                ...           2 months ago
  • 0.5.2                                ...           2 months ago
  • 0.5.1                                ...           3 months ago
  • 0.5.0                                ...           3 months ago
  • 0.3.2                                ...           2 years ago
  • 0.3.1                                ...           2 years ago
  • 0.3.0                                ...           2 years ago
  • 0.2.0                                ...           2 years ago
  • 0.1.8                                ...           2 years ago
  • 0.1.7                                ...           3 years ago
  • 0.1.6                                ...           3 years ago
  • 0.1.5                                ...           3 years ago
  • 0.1.4                                ...           3 years ago
  • 0.1.3                                ...           3 years ago
  • 0.1.2                                ...           3 years ago
  • 0.1.1                                ...           3 years ago
  • 0.1.0                                ...           3 years ago
Maintainers (1)
Downloads
Today 0
This Week 0
This Month 1
Last Day 0
Last Week 1
Last Month 7
Dependencies (1)
Dev Dependencies (0)
None
Dependents (0)
None

Copyright 2014 - 2016 © taobao.org |