搜索插件、代码仓与 APP 分类

GitHub 代码仓insinfo/fluent_query_builder

fluent_query_builder

一个 Dart 库,可让您以流畅的方式在 PostgreSQL 数据库中执行 SQL 查询,使用非常简单。

fluent_query_builder 项目封面
星标
30
复刻
9
最近推送(UTC)
2022年10月24日
项目状态
未归档

原始 README

以下为英文项目原文快照,最新内容请访问 GitHub。

fluent_query_builder

Build Status

CI

A dart library that allows you to execute SQL queries in the PostgreSQL and MySql database in a fluent way, is very easy to execute. This library implements POOL of connections. This lib implements the vast majority of SQL statements and clauses

Soon it will also support ORM without reflection and without code generation in a simple and consistent way.

Usage

A simple query builder usage example:

import 'package:fluent_query_builder/fluent_query_builder.dart';

void main() {
  //PostgreSQL connection information
  final pgsqlCom = DBConnectionInfo(
    host: 'localhost',
    database: 'banco_teste',
    driver: ConnectionDriver.pgsql,
    port: 5432,
    username: 'user',
    password: 'pass',
    charset: 'utf8',
    schemes: ['public'],
  );

  //MySQL connection information
  final mysqlCom = DBConnectionInfo(
    host: 'localhost',
    database: 'banco_teste',
    driver: ConnectionDriver.mysql,
    port: 3306,
     username: 'user',
    password: 'pass',
    charset: 'utf8',
  );

  DbLayer().connect(mysqlCom).then((db) {
    //mysql insert
    db
        .insert()
        .into('pessoas')
        .set('nome', 'Isaque Neves Sant\'Ana')
        .set('telefone', '(22) 2771-6265')
        .exec()
        .then((result) => print('mysql insert $result'));

    //mysql update
    db
        .update()
        .table('pessoas')
        .set('nome', 'João')
        .where('id=?', 13)
        .exec()
        .then((result) => print('mysql update $result'));

    //mysql update with setAll
    db
        .update()
        .whereSafe('id', '=', 13)
        .table('pessoas')
        .setAll({
          'nome': 'Jon Doe',
          'telefone': '171171171',
        })
        .exec()
        .then((result) => print('mysql update with setAll $result'));

    //mysql delete
    db.delete().from('pessoas')
    .where('id=?', 14)
    .exec()
    .then((result) => print('mysql delete $result'));

    //mysql select
    db
        .select()
        //.fields(['login', 'idSistema', 's.sigla'])
        //.fieldRaw('SELECT COUNT(*)')
        .from('pessoas')
        .whereSafe('nome', 'like', '%Sant\'Ana%')
        //.limit(1)
        .firstAsMap()
        .then((result) => print('mysql select $result'));

    //mysql raw query SELECT * FROM `pessoas` or SELECT COUNT(*) FROM pessoas
    db  
        .raw("SELECT * FROM `pessoas`")
        .firstAsMap()
        .then((result) => print('mysql raw $result'));
        
    //mysql count records 
    db
        .select()
        .from('pessoas')
        .orWhereSafe('nome', 'like', '%Sant\'Ana%')
        .orWhereSafe('id', '<', 20)
        .count()
        .then((result) => print('mysql count $result'));
  });

  DbLayer().connect(pgsqlCom).then((db) {
    //pgsql insert
    db
        .insert()
        .into('usuarios')
        .set('username', 'isaque')
        .set('password', '123456')
        .exec()
        .then((result) => print('pgsql insert $result'));

  //pgsql insertGetAll
  db
      .insertGetAll()
      .into('usuarios')
      .set('username', 'isaque')
      .set('password', '123456')
      .exec()
      .then((result) => print('pgsql insertGetAll $result'));

 //pgsql insertGetId
  db
      .insertGetId()
      .into('usuarios')
      .set('username', 'isaque')
      .set('password', '123456')
      .exec()
      .then((result) => print('pgsql insertGetId $result'));

    //pgsql count records
         db
        .select()
        .from('pessoas')       
        .count()
        .then((result) => print('pgsql count $result'));

     //Complex selection With whereGroup, whereSafe, where whereRaw
      await db.raw('DROP TABLE IF EXISTS notificacoes').exec();
      await db.raw('''CREATE TABLE notificacoes (
                      "id" serial NOT NULL ,
                      "mensagem" text COLLATE "pg_catalog"."default",
                      "dataCriado" timestamp(0) DEFAULT now(),
                      "link" text COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
                      "idPessoa" int4,
                      "idSistema" int4,
                      "userAgent" text COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
                      "idOrganograma" int4,
                      "isLido" bool,
                      "toAll" bool,
                      "icon" text COLLATE "pg_catalog"."default",
                      CONSTRAINT "notificacoes_pkey" PRIMARY KEY ("id")
                    )''').exec();
      await db.insert().into('notificacoes').setAll({
        'mensagem': 'Teste',
        'link': 'https://pub.dev',
        'dataCriado': '2021-05-04 17:53:55',
        'idPessoa': 2,
        'idSistema': 1,
        'userAgent': 'Google',
        'idOrganograma': 19,
        'isLido': false,
        'toAll': true,
        'icon': ''
      }).exec();

      final query = db.select().fromRaw('notificacoes');
      query.where('"dataCriado"::TIMESTAMP  > \'?\'::TIMESTAMP ', '2021-05-04 17:53:55');
      query.whereGroup((q) {
        q.where('"idPessoa"=?', 2, 'or');
        q.where('"idOrganograma"=?', 19, 'or');
        q.where('"toAll"=?', "'t'", 'or');
        q.whereSafe('"toAll"', '=', 'true');
        q.whereRaw('"toAll"= true');
        return q;
      });
      query.orWhereGroup((q) {
        q.whereSafe('"toAll"', '=', 'true');
        query.orWhereGroup((q) {
          q.whereSafe('"toAll"', '=', 'true');
          return q;
        });
        return q;
      });
      query.order('dataCriado', dir: SortOrder.DESC);
      final listMap = await query.limit(1).getAsMap();

      print(listMap)
       /*Resullt
       [
        {
          'id': 1,
          'mensagem': 'Teste',
          'dataCriado': DateTime.tryParse('2021-05-04 17:53:55.000Z'),
          'link': 'https://pub.dev',
          'idPessoa': 2,
          'idSistema': 1,
          'userAgent': 'Google',
          'idOrganograma': 19,
          'isLido': false,
          'toAll': true,
          'icon': ''
        }
      ]*/
   

        
  });
  
  //pgsql transaction example
  var db = await DbLayer().connect(pgsqlCom);
  await db.transaction((ctx) async {
    
    var result = await ctx.insert().into('usuarios')
    .set('username', 'isaque')
    .set('password', '123456')
    .exec();
    //pgsql use of Where Group => WHERE (nome ilike '%5%' or cpf ilike '%5%') AND id > 0
    await ctx
    .select()
    .from('pessoas')     
    .orWhereGroup((query) {
      return query
      .orWhereSafe('nome', 'ilike', '%5%')
      .orWhereSafe('cpf', 'ilike', '%5%');
    })
    .whereSafe('id', '>', 0)
    .getAsMap();

    print('pgsql transaction $result');
  });

  //pgsql getRelationFromMaps example
   var data = await db
      .select()
      .from('pessoas') 
      .orWhereGroup((query) {
        return query.orWhereSafe('nome', 'ilike', '%5%').orWhereSafe('cpf', 'ilike', '%5%');
      })
      .whereSafe('id', '>', 0)
      .getAsMap();

  data = await db.getRelationFromMaps(data, 'usuarios', 'idPessoa', 'id');

  print('pgsql select \r\n ${jsonEncode(data)}');
  /* pgsql select 
    [
      {
        "id":2,
        "nome":"Isaque Neves Sant'Ana",
        "telefone":"(22) 99701-5305",
        "cpf":"54654",
        "usuarios":[
            {
              "id":16,
              "username":"isaque",
              "password":"123456",
              "ativo":null,
              "idPessoa":2
            }
        ]
      },
      {
        "id":1,
        "nome":"João da Silva 5",
        "telefone":"27772339",
        "cpf":"1111",
        "usuarios":[

        ]
      }
    ]  */
}

A simple beta pre ORM usage example:

import 'package:fluent_query_builder/fluent_query_builder.dart';

class Usuario implements FluentModelBase {
  Usuario({this.username});

  Usuario.fromMap(Map<String, dynamic> map) {
    id = map['id'] as int;
    username = map['username'] as String;
    password = map['password'] as String;
    ativo = map['ativo'] as bool;
    idPerfil = map['idPerfil'] as int;
  }

  int id;
  String username;
  String password;
  bool ativo;
  int idPerfil;

  @override
  Map<String, dynamic> toMap() {
    final Map<String, dynamic> data = Map<String, dynamic>();
    if (id != null) {
      data['id'] = id;
    }
    data['username'] = username;
    data['password'] = password;
    data['ativo'] = ativo;
    data['idPerfil'] = idPerfil;
    return data;
  }

  @override
  String get tableName => 'usuarios';
}

void main() {
  //connection settings
  final com = DBConnectionInfo(
    host: '192.168.133.13',
    database: 'sistemas',
    port: 5432,
    username: 'user',
    password: 'pass',
    charset: 'utf8',
    schemes: ['riodasostrasapp'],
  );
  
  //connect on database and set factories for construct instance of model
   DbLayer(factories: [
    {Usuario: (x) => Usuario.fromMap(x)}
  ]).connect(com).then((db) {
    //insert Usuario
    db.putSingle<Usuario>(Usuario(username: 'jon.doe', password: '123456'));
    //update Usuario
    db.update().where('id=?', 20).updateSingle<Usuario>(Usuario(username: 'jon.doe', password: '987'));
    //select Usuario
    db.select().from(Usuario().tableName).where('id>?', 2).fetchAll<Usuario>().then((result) {
      print(result);
    });
    //delete Usuario
    db.delete().deleteSingle<Usuario>(Usuario(id: 20, username: 'jon.doe', password: '123456'));
  });
}

Isaque Neves GitHub avatar
GitHub 作者

Isaque Neves ↗

Prefeitura de Rio das OstrasRio de Janeiro - Brasil
语言DartShell

此仓库发布的插件

使用的依赖

依赖清单 7 项

如何选择适合项目的代码仓?

先按使用场景筛选,再比较项目活跃情况,最后到原始仓库核对使用要求。

  1. 通过仓库名、作者或描述搜索,再按技术分类缩小范围。
  2. 结合星标、复刻与最近推送比较项目;星标反映社区关注,不代表质量保证。
  3. 使用前在 GitHub 核对 README、许可证与 Issues。

来源与维护

“A repository is the most basic element of GitHub.”

GitHub Docs — About repositories ↗