import sql from "sql"

let config = {
	url: "root:123456@tcp(127.0.0.1:3306)/test",
	maxOpenConns: 40,       // 连接池中最大打开的连接数.如果达到这个限制，新的连接请求将会等待直到连接池中有空闲的连接或者现有的连接被关闭。
	maxIdleConns: 10,       // 连接池中最大空闲连接数.如果空闲连接数超过这个值，连接将会被关闭以释放资源。保持一定数量的空闲连接可以减少连接建立的开销。
	connMaxIdleTime: 12,    // 连接在连接池中空闲的最大时间(单位:秒)。超过这个时间的连接将会被关闭和移除。这有助于清理长时间未使用的连接。
	connMaxLifetime: 12,    // 连接在连接池中的最大生命周期。(单位:秒).连接达到这个时间后将会被关闭并且在下次需要时重新建立。这个设置可以帮助避免由于数据库连接长时间未更新而出现的问题，如连接的超时和失效。
	bigintToType: 'string',   // 决定是否将bigint类型的字段值转换为字符串(string/bigint/int/float)
	log: new Log({
		level: 'debug', // 配置Sql打印的日志级别
	}),
};
let db = sql.open("mysql", config);
let users = db.query(`select * from user limit 0,10`);
console.log(users);

import sql from "sql";

let config = {
	url: "root:123456@tcp(127.0.0.1:3306)/test",
	maxOpenConns: 40,
	maxIdleConns: 10,
	connMaxIdleTime: 12,
	connMaxLifetime: 12,
	bigintToType: 'string',
};
let db = sql.open("mysql", config);

import sql from "sql";

let config = {
	url: "postgres://postgres:123456@127.0.0.1:5432/test?sslmode=disable",
	maxOpenConns: 40,
	maxIdleConns: 10,
	connMaxIdleTime: 12,
	connMaxLifetime: 12,
	bigintToType: 'string'
};
let db = sql.open("postgres", config);
//执行查询
let users = db.query(`select * from test_dev.user where id = $1`, [1]);
console.log(users);

import sql from "sql";

let config = {
	url: "sqlserver://sa:123456@localhost:1433?database=test",
	maxOpenConns: 12,
	maxIdleConns: 12,
	connMaxIdleTime: 12,
	connMaxLifetime: 12,
	bigintToType: "string",
}
let db = sql.open("sqlserver", config);
let users = db.query(`select * from users where id = @p1`, [1]);
console.log(users);

import sql from "sql";

let config = {
	url: "oracle://sys:123456@127.0.0.1:1521/FREE",
	maxOpenConns: 12,
	maxIdleConns: 12,
	connMaxIdleTime: 12,
	connMaxLifetime: 12,
	bigintToType: "string",
}
let db = sql.open("oracle", config);
let users = db.query(`select * from users where id = #{0}`, [1]);
console.log(users);

// 导入模块
import sql from "sql";
// 数据库连接池配置
let config = {
	url: "clickhouse://vino:vino123@127.0.0.1:9000/database",
};
// 创建数据库连接客户端Client
let dbClient = sql.open("clickhouse", config);
console.log("dbClient", dbClient);
console.log("res", res);

import sql from "sql";

let db = sql.open("mysql", {});
console.log(db.name); // 输出：mysql

import sql from "sql";

let config = {
	url: "sqlserver://sa:123456@localhost:1433?database=test",
	maxOpenConns: 12,
	maxIdleConns: 12,
	connMaxIdleTime: 12,
	connMaxLifetime: 12,
	bigintToType: "string",
}
let db = sql.open("mysql", config);
console.log(db.config); // 输出config对象

import sql from "sql";

let config = {
	url: "root:123456@tcp(127.0.0.1:3306)/test",
	maxOpenConns: 40,
	maxIdleConns: 10,
	connMaxIdleTime: 12,
	connMaxLifetime: 12,
	bigintToType: 'string',
};
let client = sql.open("mysql", config);
//使用vino占位符
let users = client.query(`select id,name,age from user limit #{0},#{1}`, [1, 10]);
//使用数据库占位符
//let users = client.query(`select id,name,age from user limit ?,?`, [1, 10]);
console.log(users)

import sql from "sql";

let config = {
	url: "root:123456@tcp(127.0.0.1:3306)/test",
	maxOpenConns: 40,
	maxIdleConns: 10,
	connMaxIdleTime: 12,
	connMaxLifetime: 12,
	bigintToType: 'string',
};
let client = sql.open("mysql", config);

//通过类来自定义返回的数据结构
class User {
	id = ""
	age = 0
	name = ""
}

let users = client.query(`select id,name,age from user limit #{0},#{1}`, [1, 10], User);
console.log(users)

import sql from "sql";

let config = {
	url: "root:123456@tcp(127.0.0.1:3306)/test",
	maxOpenConns: 40,
	maxIdleConns: 10,
	connMaxIdleTime: 12,
	connMaxLifetime: 12,
	bigintToType: 'string',
};
let client = sql.open("mysql", config);
let json = client.queryJson(`select id,name,age from user limit #{0},#{1}`, [1, 10]);
console.log(json)

let client = sql.open("mysql", config);
let users = client.queryRow(`select id,name,age from user limit ?,?`, [1, 10]);
console.log(users)  // 不论是否查询出多条记录，只返回第一条数据

let client = sql.open("mysql", config);
let json = client.queryRowJson(`select id,name,age from user limit #{0},#{1}`, [1, 10]);
console.log(json) // 不论是否查询出多条记录，只返回第一条数据

import sql from "sql";

let config = {
	url: "root:123456@tcp(127.0.0.1:3306)/test",
	maxOpenConns: 40,
	maxIdleConns: 10,
	connMaxIdleTime: 12,
	connMaxLifetime: 12,
	bigintToType: 'string',
};
let client = sql.open("mysql", config);
//使用vino占位符
let res = client.exec(`insert into user(id,name) values(#{id},#{name})`, {id: 1, name: "vino"});
//使用数据库占位符
//let res = client.exec(`insert into user(id,name) values(?,?)`,[1,'vino']);
console.log(res)

db.begin();

tx.commit()

tx.rollback()

import sql from "sql";

let config = {
	url: "root:123456@tcp(127.0.0.1:3306)/test",
	maxOpenConns: 40,
	maxIdleConns: 10,
	connMaxIdleTime: 12,
	connMaxLifetime: 12,
	bigintToType: 'string',
};
let client = sql.open("mysql", config);
//开启事务
let tx = client.begin();
try {
	//通过tx对象来执行sql
	let users = tx.query(`select * from user limit 0,10`);
	let res = tx.exec(`insert into user(name) values('vino')`);
	//提交
	tx.commit();
} catch (e) {
	//回滚
	tx.rollback();
}

stmt.close()

let stmt = client.prepare(`select * from test where id=?`);
try {
	for (let i = 0; i < 10; i++) {
		//调用query方法时，只传递占位符参数
		let res = stmt.query([i]);
		console.log(res)
	}
} catch (e) {
	//异常处理
} finally {
	//处理完成后，记得关闭stmt
	stmt.close();
}

let data = ["1", "vino"]
client.query(`select id,name,age from user where id=? and name=?`, data);

let data = ["1", "vino"]
client.query(`select id,name,age from user where id=$1 and name=$2`, data);

let data = ["1", "vino"]
client.query(`select id,name,age from user where id=@p1 and name=@p2`, data);

let data = ["1", "vino"]
client.query(`select id,name,age from user where id=#{0} and name=#{1}`, data);
//vino会转换为下列结果：
//params=["1","vino"]
//client.query(`select id,name,age from user where id=? and name=?`, params); //mysql
//client.query(`select id,name,age from user where id=$1 and name=$2`, params);//postgres
//client.query(`select id,name,age from user where id=@p1 and name=@p2`, params);//sqlserver

let data = {
	id: "1",
	name: "vino"
}
client.query(`select id,name,age from user where id=#{id} and name=#{name}`, data);
//vino会转换为下列结果：
//params=["1","vino"]
//client.query(`select id,name,age from user where id=? and name=?`, params); //mysql
//client.query(`select id,name,age from user where id=$1 and name=$2`, params);//postgres
//client.query(`select id,name,age from user where id=@p1 and name=@p2`, params);//sqlserver

let map = new Map()
map.set("id", 1)
map.set("name", "vino")
client.query(`select id,name,age from user where id=#{id} and name=#{name}`, map);
//vino会转换为下列结果：
//params=["1","vino"]
//client.query(`select id,name,age from user where id=? and name=?`, params); //mysql
//client.query(`select id,name,age from user where id=$1 and name=$2`, params);//postgres
//client.query(`select id,name,age from user where id=@p1 and name=@p2`, params);//sqlserver

let data = ["1", "vino"]
client.query(`select id,name,age from user where id=&{0} and name=&{1}`, data);
//vino会转换为下列结果：
//params=[]
//client.query(`select id,name,age from user where id=1 and name=vino`, params); //mysql\postgres\sqlserver

let data = {
	id: "1",
	name: "vino"
}
client.query(`select id,name,age from user where id=&{id} and name=&{name}`, data);
//vino会转换为下列结果：
//params=[]
//client.query(`select id,name,age from user where id=1 and name=vino`, params); //mysql\postgres\sqlserver

let map = new Map()
map.set("id", 1)
map.set("name", "vino")
client.query(`select id,name,age from user where id=&{id} and name=&{name}`, map);
//vino会转换为下列结果：
//params=[]
//client.query(`select id,name,age from user where id=1 and name=vino`, params); //mysql\postgres\sqlserver