Beetlsql, simple and powerful database access tools

Source: Internet
Author: User

It also features hibernate benefits & MyBatis benefits, and is suitable for applications that recognize SQL as the center and require tools to automatically generate a large number of commonly used SQL.

    • SQL is centrally managed in a more concise manner, while facilitating program development and database SQL debugging Markdown
    • The data model supports Pojo and also supports map/list model with no model
    • SQL templates are based on BEETL implementations, easier to write and debug, and extended
    • Automatically generate a large number of built-in SQL without annotations, and easily perform the add-and-revise function
    • Supports cross-database platforms, minimizing the effort required by developers
    • Features interceptor, debug, Performance Diagnostic SQL, and extended other features
    • Built-in support for master-slave database, extended to support more complex sub-library table logic
Code examples
list<user>  list = Sqlmanager.getsqlscript ("Selectuser"). Select (Paras,user.class); User user = Sqlmanager.getsql (user.class,select_by_id). Unque (ID);
SQL Example
selectuser===        SELECT * from the user where 1=1        @if (user.age==1) {and age        = #user. age#        @}selectall===        SELECT * from          the user @use ("Selectwhere"); selectwhere===        where Age  = #age #
Markdown Mode Management

BEETLSQL Centralized management of SQL statements, SQL can be placed in a file according to the business logic, files can be placed in a directory according to the logic of the module. The file format discards the XML format and uses Markdown, because

    • XML format is too complex to write easily
    • XML format has reserved symbols, when writing SQL is not convenient, such as commonly used < symbols must be escaped

The current SQL file format is as follows

File some instructions, put in the head dispensable, if there is a description, can be any text        SQL indicator        = = = SQL        statement         SQL Indicator 2        = = = SQL        Statement 2

All SQL files are recommended to be placed in a DB directory with multiple subdirectories for the database type, such as common in DB, public SQL statements and Mysql,oralce. When the program gets the SQL statement, the SQL statement under the database is looked up, and if it is not found, it will look for common. The following code

Sqlscript sql = Sqlmanager.getsql ("Sys.user.update");

Sqlmanager will find the Db/mysql/sys/user.md file based on the database currently in use, confirm if there is an UPDATE statement, and if not, look for db/common/sys/user.md

Rich Data Model support

Beetlsql is suitable for various types of references, for large and medium-sized applications, the model is usually Pojo, which is easy to maintain and interact with the three-way system, and for very small projects, there is often no need for a rigorous model, which indicates that business entities are typically map/list combinations. The input to the SQL statement can be either Pojo or MAP,SQL statement execution results and can be mapped to Pojo and map.

Sqlscript sql = Sqlmanager.getsql ("User.update");        int result = sql.update (user);        Sqlscript sql = Sqlmanager.getsql ("User.select");        list<user> list = Sql.select (User,user.class);        Sqlscript sql = Sqlmanager.getsql ("User.select");        Map paras = new HashMap ();        Paras.put ("Age", one by one);        User user = Sql.single (paras,user.class);        or         Map user = Sql.single (paras,map.class);
SQL templates are based on BEETL implementations, easier to write and debug, and extended

SQL statements can be generated dynamically, based on the Beetl language, because

    • Beetl execution efficiency is known for its efficient industry, so it is very appropriate to use BEETL for dynamic SQL statements based on templates

    • Beetl syntax is simple and easy to use, can be achieved by semi-guessing half-way, to eliminate mybatis so difficult to remember grammar. Beetlsql learning curve almost no

    • The use of Beetl can be set to define the bounds of the symbol, the SQL template delimiter can be well defined as a database SQL comment symbol, so it is easy to test in the database, the following is also the SQL template (the delimiter is defined as "-" and "null", NULL is a carriage return meaning);

      Selectbycond    = = =    Select * Form user where 1=1    --if (age!=null)    age= #age #    --}
    • Beetl error hints are very friendly and reduce write-SQL scripting maintenance time

    • Beetl can easily interact with native classes (direct access to Java classes), execute specific business logic, or write model constants directly in SQL templates, and even SQL refactoring will parse the error in advance.

    • Beetl statements are easy to extend and provide various functions, such as table logical functions, common functions across databases, etc.

Automatically generate a large number of built-in SQL without annotations, and easily perform the add-and-revise function

Although Beetlsql is not an O/R Mapping tool, it can generate a large number of commonly used SQL based on the default conventions and requires almost no annotations (for Oralce, annotation seqid (name= "Seqname")), Beetlsql based on the class entered, The following SQL can be generated automatically

    • SELECT_BY_ID: Query based on primary key

    • Select_by_template: Queries the instance variable as a template, if the property of its variable is empty, does not count toward the query condition

    • UPDATE_BY_ID: Update based on primary key

    • Update_by_template: Update According to template

    • DELETE_BY_ID: Query based on primary key

    • Delete_by_template: Query by template

    • Insert: Auto Insert, if there is no primary key annotaion, will look for the database to find the primary key, and is considered to be the self-increment primary key. There are a total of three primary key annotation

      The SeqId is used for Oralce              autoid for self-increment. This is the primary key default setting    Assignid, the code specifies the primary key
Supports cross-database platforms, minimizing the effort required by developers

As mentioned earlier, Beetlsql can support cross-database development with SQL file management and search, as described earlier, searching for a specific database before looking for common. In addition, Beetlsql provides a number of database solutions

    • Dbstyle describes database features, injects INSERT statements, and page-turn statements are done through their subclasses, without the user having to worry about
    • Provides some default function extensions instead of functions for each database, such as time and time manipulation functions, date, etc.
Features interceptor, debug, Performance Diagnostic SQL, and extended other features

Beetlsql can perform a series of intercetor before and after executing SQL, providing the opportunity to perform various extensions and monitoring, which is easier than the known interceptor through the database connection pool. The following interceptor are possible

    • Monitor SQL to execute longer statements, print and collect (completed)
    • After each SQL statement is executed, its SQL and parameters are output, and only SQL for a specific SQL collection can be output based on the condition. User-friendly debugging (completed)
    • SQL expected parsing, summarizing SQL execution (not completed, need to integrate third-party SQL analysis tools)
    • Database Sub-table sub-Library logic
Built-in support for master-slave database, extended to support more complex sub-library table logic

Beetlsql manages the data source, if it is provided with only one data source, it is considered that both read and write operate this data source, and if more than one is provided, the first is the write library and the other is read. When developing the code, the user does not need to worry about which database to manipulate, because when calling Sqlscrip's Select related API, always reads from the library, Add/update/delete, always reads the main library.

Sqlscript.insert (user)//operation of the main library, if only one data source is configured, it does not matter if the master-slave    Sqlscript.selectbyid (id,user.class)//read from the Library

Of course, you can also be based on your own specific logic to determine if the master-slave library, only need to expand the beetlsql, all of this is transparent to the developer

Developers can also be transparent to the user by completing the table logic in the SQL template, as in the following SQL statement

INSERT INTO     #text ("Log" +date ()) #    values () ...    Note: The text function outputs an expression directly to the SQL statement instead of the output?.

Log indicates that a table can be sorted according to certain rules, and table is determined by the time it is entered.

SELECT * FROM     #text ("Log" +log.date) #    where     Note: The text function outputs an expression directly to the SQL statement instead of the output?.

Similarly, decide which table to go to according to the input criteria, or query all tables

@ var tables = Getlogtables ();    @ for (table in tables) {    select * from #text (table) #     @       if (!tablelp.islast) print ("union");    @}          WHERE name = #name #

Beetlsql still in the development process, because spare time is limited, originally planned 1 months of development completed, has been delayed for several weeks, but I believe the end of August or early September will have a version, Hope Beetl fans continue to support

Beetlsql, simple and powerful database access tools

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.