How to write SQL in js

In the ever-changing front-end field, front-end engineers can do more and more things. Since the emergence of nodejs, the front-end has increasingly had a tendency to revolutionize the traditional back-end life. This article will add one more detail. Interpret how to execute standard SQL statements in js code

Why write SQL in js?

As business complexity increases, some pages with complex data logic may appear on the front-end page. Traditional js logic is more complicated to process. Let’s look at two examples first:

For example, in the multi-specification and multi-inventory product interface, the difficulty lies in the complex logical relationship between color classification, size, price, inventory, limited purchase quantity and corresponding picture display. Users perform different When selecting, js needs to go through many complex queries to calculate the result

For example, the difficulty in the regional linkage query interface is:

1. How to store regional data locally? Obviously it is unrealistic to pull the interface every time. If it is stored in storage, every time it is used, a string similar to JSON.parse needs to be converted into an array or object. process, this operation will cause page freezes and extremely poor performance when the amount of data is large.

2. The three-level regional linkage query is complicated. If you want to query from a county-level region to the corresponding For cities and provinces, the logic will be more complicated

---

If the above two examples are written using traditional js logic, everyone must have already designed them in their minds Algorithms inevitably use various ES678 new methods such as forEach, filter, some, find, etc. The author also used various cool new methods to write it and found two problems:

1. After writing it, the logic is very complicated. It seems that it cannot be implemented without 100 lines of code (of course there are masters who can do it better than me)

2. Even if I write a lot of comments, my colleagues can’t see it I'm still confused (because the logic is really complicated...)

The author has been doing PHP development for a while (also done PM, UI, QA, etc.) and suddenly thought Can it be implemented using SQL? After some research, the author wrote such a library:

Database.js

Database.js is based on Web SQL Database, so what is Web SQL Database?

Web SQL Database is the first official draft proposed by WHATWG (Web Hypertext Application Technology Working Group, the proposer of the HTML5 draft) in January 2008, but it is not included in HTML 5 specification, it is an independent specification that introduces a set of APIs for operating client databases using SQL. Since it was proposed earlier, although the W3C officially stated in November 2011 that it would no longer maintain the Web SQL Database specification, these APIs have been widely implemented in different browsers, especially mobile browsers.

Compatibility

What is the difference between Web SQL Database and Indexed Database?

Indexed Database is more similar to NoSQL in operating the database. The most important thing is that Indexed Database does not use SQL as the query language.

In order to realize the need to write SQL in js, the author decisively adopted the former as the underlying technology.

Web SQL Database three core methods:

• openDatabase: This method uses an existing database or a new database to create a database object

• transaction: This method allows us to control transaction submission or rollback depending on the situation

• executeSql: This method is used to execute SQL queries

Code example:

var db = openDatabase('testDB', '1.0', 'Test DB', 2 * 1024 * 1024);
    var msg;
    db.transaction(function (context) {
       context.executeSql('CREATE TABLE IF NOT EXISTS testTable (id unique, name)');
       context.executeSql('INSERT INTO testTable (id, name) VALUES (0, "Byron")');
       context.executeSql('INSERT INTO testTable (id, name) VALUES (1, "Casper")');
       context.executeSql('INSERT INTO testTable (id, name) VALUES (2, "Frank")');
     });

For front-end students who have no SQL experience, the above code looks a bit unfamiliar and not very friendly. , so Database.js was born:

The author takes a demand in the business as an example: Zhuanzhuan Game Business List Page The filter menu is a three-level linkage menu. Each menu change will affect other menu data, as shown in the figure:

Original JSON data structure

It can be seen that it is a three-level nested structure. The author processed it into a flat data structure (the process is omitted) and stored it in three databases, respectively storing game names, game platforms, and product types, as follows Picture:

An example of the game name data structure is as follows:

通过chrome控制台Application面板可以直接看到数据库，结构、数据清晰可见

核心代码如下：

/**
       * 打开数据库
       * @returns {Promise.<void>}
       */
      openDataBase(){
        //打开数据库，没有则创建
        db.openDatabase(&#39;GameMenu&#39;,1,&#39;zzOpenGameMenu&#39;).then(res=>{
          //检测数据库是否存在
          db.isExists(&#39;game&#39;).then(res=>{
            //数据库已经存在，直接使用,将数据交付给页面UI组件
            this.setSelectData()
          }).catch(e=>{
            //数据库不存在，请求接口并处理数据，然后存入数据库
            this.getData()
          })
        }).catch(e=>{
          console.err(e)
        })
      },
     /**
       * 获取分类数据并存储到数据库
       * @returns {Promise.<void>}
       */
      async getData(){
        //接口请求数据并处理成三个扁平数组
        let data =  await this.getMenuData()
        for(let i in data){
          //创建表并存储数据
          db.create(i,data[i])
        }
        //将数据交付给页面UI组件
        this.setSelectData()
      },

当任意菜单选择变更时，三列数据将重新查询，核心代码如下：

/**
       * 重新查询数据
       * @param data 点击菜单携带的数据
       * @param index 点击菜单的序号
       * @param all 三个菜单当前选中数据
       */
      async onSelect(data,index,all){
        let target = [],condition = {}
        //业务逻辑：处理查询条件
        if(all['0'] && all['0']['name']!=defaultData[0].default.name)condition['gameName'] = all['0']['name']
        if(all['1'] && all['1']['name']!=defaultData[1].default.name)condition['platName'] = all['1']['name']
        if(all['2'] && all['2']['name']!=defaultData[2].default.name)condition['typeName'] = all['2']['name']

        //创建三个查询任务
        let tasks = ['game','plat','type'].map((v,k)=>{
            //使用db.select方法查询
            return db.select(v,this.formatCondition(v,condition),'name,value','rowid desc','name').then((res)=>{
              target.push({
                options:res.data,
                defaultOption:defaultData[k].default,
                clickHandle:this.onSelect
              })
            })
        })
        //执行查询
        await Promise.all(tasks)
        //将数据交付给联动菜单组件使用
        this.selectData = target
      }

以上代码即可完成联动菜单所需要的数据管理工作，看起来是不是比较清晰？

---

使用Database.js的优势

1.将数据结构化存储于Storage中，避免了以文本形式存入Storage或cookie中再解析的性能消耗流程。

2.将复杂数据清晰的在前端进行管理和使用，代码逻辑更清晰，数据查询更简洁！

Database.js使用文档

openDatabase

• 功能：打开数据库，不存在则创建

• 语法：openDatabase(dbName,dbVersion,dbDescription,dbSize,callback)

• 参数：

• dbName：数据库名

• dbVersion：数据库版本（打开已存在数据库时，版本号必须一致，否则会报错）

• dbDescription：数据库描述

• dbSize：数据库预设大小，默认1M

• callback：回调函数

query

• 功能：执行sql语句，支持多表查询

• 语法：query(sqlStr,args = [],callback,errorCallback)

• 参数：

• sqlStr：sql语句

• args（Array）：传入的数据，替换sql中的?符号

• callback:成功回调

• errorCallback：失败回调

1   //插入数据
2   db.query('INSERT INTO testTable(id,title) VALUES (?,?)',[1,'这是title'])
3 
4   //多表查询
5  db.query('select game.*,plat.* from game left join plat on game.name = plat.gameName')

isExists

• 功能：检测表是否存在

• 语法：isExists(tableName)

• 参数：

• tableName：表名

createTable

• 功能：创建一张表

• 语法：createTable(tableName,fields)

• 参数：

• tableName：表名

• fields：表结构(需指定字段类型)

• 示例  

                1   db.createTable('testTable',{
        2       name:'varchar(200)',
        3       price:'int(100)'
        4   })

insert

• 功能：插入一条或多条数据

• 语法：insert(tableName,data)

• 参数：

• tableName：表名

• data（Object or Array）：插入的数据，多条数据请传入数组类型

• 示例： javascript //插入单条 db.insert('testTable',{ name:'商品1', price:10 }) //插入多条 db.insert('testTable',[ {name:'商品1',price:10}, {name:'商品2',price:20}, {name:'商品3',price:30}, ])

将数据存入数据库的常规流程是先createTable，然后再insert，如果你觉得这样麻烦，可以试一下create方法：

create

• 功能：直接创建数据库并存入数据

• 注意：类库会根据传入的数据类型自动设置数据库的字段类型，这样可以覆盖大多数需求，但如果你的数据中，同一个字段中有不同的数据类型，有可能不能兼容，建议还是使用常规流程手动设置类型

• 语法：create(tableName,data)

• 参数：

• tableName：表名

• data（Object or Array）：插入的数据，多条数据请传入数组类型

• 示例：

1   //插入数据
2   db.query('INSERT INTO testTable(id,title) VALUES (?,?)',[1,'这是title'])
3
4   //多表查询
5  db.query('select game.*,plat.* from game left join plat on game.name = plat.gameName')

delete

• 功能：删除数据

• 语法：delete(tableName,condition)

• 参数：

• tableName：表名

• condition（String or Obejct）：查询条件

• 示例：

1       //删除一条数据
2       db.delete('testTable',{name:'商品1'})

关于condition： 1、传入array形式时，默认查询条件连接方式是AND，如果需要用OR等方式，可以在condition中传入logic设定，例如{logic：'OR'} 2、如果查询条件有AND、OR等多种方式，建议使用string方式传入

select

• 功能：查询数据

• 注意：如果需要多表查询，可参照query方法

• 语法：select(tableName,condition = '',fields = '*',order = '',group = '',limit = '')

• 参数：

• tableName：表名

• condition（String or Obejct）：查询条件

• fields（String or Array）：返回字段，默认*，支持distinct

• order（String or Array）：排序规则

• group（String or Array）：分组规则

• limit（String or Array）：分页规则

• 示例：

  
  

  1 //查询name=商品1的数据，并按照price倒序

**update**
- 功能：更新数据
- 语法：update(tableName,data,condition = '')
- 参数：
    - tableName：表名
    - data（String or Obejct）：更改数据
    - condition（String or Obejct）：查询条件
- 示例：

1       //将商品1的价格改为99
2       db.update('testTable',{
3             price:99
4         },{
5             name:'商品1'
6         })

truncate

• 功能：清空表

• 语法：truncate(tableName)

• 参数：

• tableName：表名

drop

• 功能：删除表

• 语法：drop(tableName)

• 参数：

• tableName：表名

The above is the detailed content of How to write SQL in js. For more information, please follow other related articles on the PHP Chinese website!