Welcome to NBatis2.0 !
This a node.js plugin about data persistence, if you used mybatis before, you'll learn it soon.. The plugin is given priority to with node mysql driver,at present. NBatis is mostly API compatible with mysql and supports majority of features. It also offers these additional features.
Table of contents
Install
npm install nbatis --save
Example
PS: It is recommended to use typescript to code your project. Make sure typescript is installed before this example. Please install the ts plug-in globally.
Create POJO
- We can create a pojo class named UserSQL.ts. This class can help us to create test table in the database.
; id:any; username:any; password:any; gender:any; birthday:any; email:any; url:any; createTime:any; { thisid = type: 'int(11)' primary: true ; thisusername = type: 'varchar(255)' notNull: true default: '' DBUtil ; thispassword = type: 'varchar(255)' default: '' DBUtil ; thisgender = type: 'char(1)' notNull: true default: '' DBUtil thisbirthday = type: 'Date' DBUtil ; thisemail = type: 'varchar(255)' DBUtil thisurl = type: 'varchar(255)' DBUtil thiscreateTime = type: 'DateTime' notNull: true default: '#{NOW()}' ; }
- Each item in the table corresponds to each property of the pojo class, all of which are public and default value is a object. The object have several properties. These properties help us complete the SQL statement.
type: '' //SQL data type, the type is string. primary: false //To determine whether the property is a primary key, the type is boolean. notNull: false //To determine whether the property is NOT NULL, the type is boolean. default: '' //SQL default value, the type is any. value: 0 //This property is used when bulk data is inserted. It can be of any type, most of the time it's a random function.
Create Unit Test Case
- We use tunit which is a typescript unit test plugin here. First of all, to install tunit.
npm install tunit --save-dev
- Second, create a entity class named FactoryTest.ts. Because a decorator in tunit is named Test so the name of the test class cannot be called "Test".
;;;; @ factory:any session:any; { thissession = null; thisfactory = null; } @BeforeClass async { try const configrationFilePath = path; thisfactory = ; thissession = await thisfactory; ; catcherr ; } /** * To test creating a table with the class. */ @Test async { const sql = DBUtil; try const res = await thissession; ; catcherr await thissession; ; } /** * To test the method queryGet. */ @Test async { const sql = 'desc user_sql'; try const res = await thissession; ; catcherr ; } /** * To test inserting random data in quantity. */ @Test async { const sql = DBUtil; try const res = await thissession; const assert = Assert; ; catcherr thissession; ; } /** * To test getting a list of data */ @Test async { try const params = start:0 length:5 res = await thissession assert = Assert; ; catcherr ; } /** * To test getting a piece of data by id. */ @Test async { try const params = tableName: 'user_sql' id:1 const res = await thissession; ; catcherr ; } /** * To test updating a piece of data */ @Test async { try const params = id: 1 url:'www.test001.com' res = await thissession; ; catcherr thissession; ; } /** * To test dynamic sql commands */ @Test async { try const params = tableName: 'user_sql' where: '"%com"' //String variables in dynamic SQL need to be quoted. start:0 pageSize:5 order: 'desc' res = await thissession; ; catcherr ; } /** * To test deleting a piece of data */ @Test async { try const res = await thissession; ; catcherr thissession; ; } /** * To test multiple sql commands in one transaction, the sql commands must be modify instructions of table. */ @Test async { try const params = tableName:'user_sql' username:'test002' password:'123456' gender:'M' birthday:'1996-01-01' email:'test002@vys.cc' url:'test.vys.cc' updatePassword:'654321' res = await thissession; ; catcherr thissession; ; } /** * To test delete a table with the class */ @Test async { const sql = DBUtil; let res:any; try res = await thissession; catcherr thissession; ; finally ifthissession await thissession; ; } /** * Test to close connection pool */ @AfterClass async { let res:any; try res = await thisfactory; ; catcherr ; }
Next we start configuring nbatis.
Configuration
- Fist, to create a configuration file named nbatis_config.json,of course you can name it anyother name.
The configuration items are here:
"dataSource": "host": "your database address" "user": "your username" "password": "your password" "database": "your database name" "connectionLimit": 5 "mappers": "UserSQL": "./user_mapper.json"
- Second, creating mapper file called user_mapper.json. If you have multiple mapper files, you can put them in one folder.
"list": "parameterType": "object" "sql": "select * from user_sql limit ${start},${length}" "loadById": "parameterType": "object" "sql": "select * from user_sql where id=${id}" "pager": "parameterType": "object" "dynamic": true "sql": "select * from user_sql <%if({{where}}){return 'where url like ${where}'}%> <%if({{orderKey}}&&{{order}}){return 'order by ${orderKey} ${order}';}%> <%if({{start}}&&{{pageSize}}){return 'limit ${start},${pageSize}';}%>" "updateById": "parameterType": "object" "sql": "update user_sql set url=#{url} where id=#{id}" "deleteById": "parameterType": "number" "sql": "delete from user_sql where id=#{id};" "insertAndUpdate": "parameterType": "object" "sql": "insert into ${tableName} (username,password,gender,birthday,email,url) values (#{username},#{password},#{gender},#{birthday},#{email},#{url});update ${tableName} set password=#{updatePassword} where username=#{username}"
- Attribute Description:
Property | Description |
---|---|
parameterType | Javascript basic data type: number, string, boolean, object; the type is string |
dynamic | Whether the SQL statement is dynamic, the type is boolean or undefined |
sql | SQL statements with variables, the type is string |
Run
- Make sure the typescript configuration file tsconfig.json is generated before running.
tsc --init
- For now, you can run the unit test case.
ts-node /TheTestts
If you set a path in the decorator TUnit, the tunit.log file will be generated at the end of the test under that path. The resulting log is written to the file as an addendum.
Common SQL
Description of non-dynamic SQL statements in mapper files
Symbol | Description |
---|---|
${} | The program will put ${} replaced with parameter values |
#{} | The program will put #{} replaced with ?, escaping-query-values |
Dynamic SQL
You can write SQL statements as normal js syntax, allowing the SQL statements to produce different results as parameters.
Symbol | Description |
---|---|
<%%> | javascript code is included between symbols |
{{params}} | The SQL parameters that appear in the javascript statement are wrapped in {{}} |
Multiple SQL
- If you want to multiple operations in one transaction, you can join multiple sql commands with semi-colon.
All statements must be modify the statement for the table.
Here is a example:
"insert into ${tableName} (username,password,gender,birthday,email,url) values (#{username},#{password},#{gender},#{birthday},#{email},#{url});update ${tableName} set password=#{updatePassword} where username=#{username}"
Multiple SQL runs can also manipulate multiple tables.
Template SQL
- The class SqlSessionTemplate is used here, which is SqlSessionFactory wrapper class. Its main function is to simplify the operation of getting session, session release. The user takes the session out of the template and does not care about about error fallback or session release.
;;;; @ template:any { thistemplate = null; } @BeforeClass async { try const configrationFilePath = path; thistemplate = configrationFilePath; ; catcherr ; } @Test async { const sql = DBUtil; try const res = await thistemplate; ; catcherr ; } @Test async { const sql = DBUtil; try const res = await thistemplate; const assert = Assert; ; catcherr ; } @Test async { try const params = start:0 length:5 res = await thistemplate assert = Assert; ; catcherr ; } @Test async { try const params = tableName:'user_sql' username:'test002' password:'123456' gender:'M' birthday:'1996-01-01' email:'test002@vys.cc' url:'test.vys.cc' updatePassword:'654321' res = await thistemplate; ; catcherr ; } @Test async { const sql = DBUtil; let res:any; try res = await thistemplate; ; catcherr ; } @AfterClass async { try const res = await thistemplate; ; catcherr ; }
- Let's use TemplateTest.ts as an example and it looks a little bit like FactoryTest.ts. Each of these operations takes an session from the connection pool, and template automatically releases session when the operation is complete. In case FactoryTest, it does all the work in one session, and the developer needs to be concerned about fallback and release.
SqlSessionTemplate is recommended for practical development.
API
[SqlSessionFactory]
:any
Each factory object corresponds to a link pool object.
-
Parameters configFilePath: The path to the configuration file.
-
Return factory object.
:Promise<any>
- Return session.
:any
- Returns a singleton pool object.
:Promise<any>
- Close pool.
[Session / SqlSessionTemplate]
async :Promise<any>
-
Parameters tag: The key of item in mapper file. params: Parameters to pass.
-
Return a list of data.
async :Promise<any>
-
Parameters tag: The key of item in mapper file. params: Parameters to pass.
-
Return a piece of data.
async :Promise<any>async :Promise<any>async deletetag:string params:any:Promise<any>
-
Parameters tag: The key of item in mapper file. params: Parameters to pass.
-
Return a list of state object.
async :Promise<any>
-
Parameters sql: SQL statement
-
Return the query results.
async :Promise<any>
-
Parameters sql: SQL statement
-
Return a list of state object.
[SqlSessionTemplate]
:any
- Return a factory object.
[DBUtil]
static :string
-
Parameters typeReference: Class. tableName: Table name.
-
Return sql statement.
static :string
-
Parameters typeReference: Class. tableName: Table name. dataNumber: The amount of data inserted.
-
Return sql statement.
static :string
-
Parameters params: Class or table name.
-
Return sql statement.
static :string
-
Parameters tag: String content, 'w':alphanumeric; 'c':pure letters; 'd': pure numbers. letterCase: Case mark, 'l':lowercase; 'b':uppercase; 'm':case mixing. len: The length of string.
-
Return a random string.
static :string
-
Parameters startDate: Start date, the default value is '1970-01-01'. endDate: End date, the default value is now. returnFormat: The length of string.
-
Returns the format of the datetime string, the format is 'yyyy-MM-dd' or 'hh:mm:ss' or 'yyyy-MM-dd hh:mm:ss'.
static :string
-
Parameters dataList: A list.
-
Returns a random value in the array.
static :string
- Returns a random email.
static :string
-
Parameters isSubDomain: Determines whether the sub-domain name is generated. if true return 'xxx.domain.com' else 'www.domain.com'
-
Returns a random domain name.
static :number
-
Parameters birthday: Datetime string, the format is '1970-01-01'
-
Return age.