database middleware in bpframework.

$3

https://github.com/bpcloud/middleware

#### Setup.

``js
import { Application } from 'bpframework';
import * as middleware_db from '@bpframework/middleware-db';

Application.use(middleware_db.middleware)
Application.runKoa(...);
`

#### Config.

`properties
spring.db:
# db type: mysql, mssql
type: mysql
# (optional) timeout of connect to database.
connectTimeout: 5000
# (optional) timeout of query.
queryTimeout: 5000
# (optional) timeout of acquire connection from pool.
acquireTimeout: 5000
# (optional) max queue length of wait for acquire connection.
queueLimit: 200
# (optional) max count of connection in pool.
connectionLimit: 10
# (optional) idle timeout to recreate connect
idleTimeoutMillis: 600000
# database host.
host: 127.0.0.1
# (optional) databse port.
port: 3306
# database name.
database: dbname
# user name of databse.
user: username
# the password of user.
password: password
# (optional) table prefix name.
table_prefix: prefix_
`

#### Define table.

`js
/**
* table model
*/
@Table('tablename')
class Table1Model {
@Column({ type: dataType.Char(32), primaryKey: true })
id: string;

@Column({ type: dataType.VarChar(64) })
col1: string;

@Column({ type: dataType.Bit() })
col2: boolean;
}

/**
* table mapper
*/
class Table1Mapper extends BaseMapper implements IBaseMapper {
/* Must have this static member /
static Model = Table1Model;
}
`

#### Define Bean.

`js
@Service()
class DBConfigure {
@Bean()
dbTemplate(): DBTemplate {
return new DBTemplate();
}
}
`

#### Use.

`js
@Service()
class DBService {

@Autowired("dbTemplate")
dbTemplate: DBTemplate;

async foo(): Promise {

// get mapper.
let mapper: Table1Mapper = this.dbTemplate.getMapper(Table1Mapper);

// query.
let count = await mapper.count('1=1');
console.log(count: ${count});

let exist = await mapper.exist('617a46bf66555e3ea251000001a5da4f');
console.log(exist: ${exist});

let select = await mapper.select(
mapper.condition.equal('id', '617a46bf66555e3ea251000001a5da4f')
);
console.log(select: ${JSON.stringify(select[0])});

let selectById = await mapper.selectById(
'617a46bf66555e3ea251000001a5da4f'
);
console.log(selectById: ${JSON.stringify(selectById)});

// exec.
await this.dbTemplate.exec('select * from tablename');
}
}
`




store procedure

Invoke the following stored procedure.

`sql


mysql procedure


CREATE PROCEDURE procedureName(out out1 int, out out2 int, in userid int)
BEGIN
select count(id) INTO out1 from table1;
select count(id) INTO out2 from table1 where id=userid;
select out1, out2;
END
`

code:

`js
import { procedureParams, dataType } from '@bpframework/middleware-db';

// make param.
var params = new procedureParams();
params.addOut('out1', dataType.Int());
params.addOut('out2', dataType.Int());
params.addIn('userid', dataType.Int(), 2);

// exec.
db.execProcedure('procedureName', params)
.then(ret=>{
console.log('');
console.log('[promise procedure]');
console.log(ret.out.out1);
console.log(ret.out.out2);
})
.catch(e=>{
console.log(e);
})
``