HTML5 's Web SQL database implements Simple object persistence with local and session storage, dealing with cumbersome relational data.
This is described in the Web SQL Database Specification for the Consortium (referenced): Web SQL database introduces an API that uses SQL to manipulate client databases (Client-side database), which are asynchronous (a synchronous), so the author uses this API to find that anonymous functions are very useful.
The three core methods defined in the Web SQL Database specification:
1. OpenDatabase: This method creates a database object using an existing database or creating a new database.
2, Transaction: This method allows us to control the transaction commit or rollback according to the situation.
3, ExecuteSQL: This method is used to execute the real SQL query
Open Database
The OpenDatabase method can open a database that already exists and is created if it does not exist:
var db = OpenDatabase (' MyDatabase ', ' 1.0 ', my db ', 2 * 1024);
The five parameters in Opendatabasek are: database name, version number, description, database size, create callback. Create a callback No, you can also create a database.
Execute Query
The Database.transaction () function is used to query, and the following will create the table T1 in the MyDatabase database:
var db = OpenDatabase (' MyDatabase ', ' 1.0 ', ' Test db ', 2 * 1024 * 1024);
Db.transaction (Function (TX) {
Tx.executesql (' CREATE TABLE IF not EXISTS T1 (id unique, log) ');
});
Insert operation
var db = OpenDatabase (' MyDatabase ', ' 2.0 ', my db ', 2 * 1024);
Db.transaction (Function (TX) {
Tx.executesql (' CREATE TABLE IF not EXISTS T1 (id unique, log) ');
Tx.executesql (' INSERT into T1 (ID, log) VALUES (1, "Foobar") ");
Tx.executesql (' INSERT into T1 (ID, log) VALUES (2, "logmsg") ");
});
When inserting new records, we can also pass dynamic values, such as:
var db = OpenDatabase (' MyDatabase ', ' 2.0 ', ' My db ', 2 * 1024);
Db.transaction (Function (TX) {
Tx.executesql (' CREATE TABLE IF not EXISTS T1 (id unique, log) ');
Tx.executesql (' INSERT into T1
(Id,log) VALUES (?,? '), [e_id, E_log]; E_ID and E_log are external variables
});
Read operations
If you want to read a record that already exists, we use a callback to capture the result, as follows:
var db = OpenDatabase (MyDatabase, ' 2.0 ', ' My db ', 2*1024); Db.transaction (Function (TX) {
Tx.executesql (' CREATE TABLE IF not EXISTS T1 (id unique, log) ');
Tx.executesql (' INSERT into T1 (ID, log) VALUES (1, "Foobar") ");
Tx.executesql (' INSERT into T1 (ID, log) VALUES (2, "logmsg") ");
});
Db.transaction (Function (TX) {
Tx.executesql (' SELECT * from T1, [], function (TX, results) {
var len = results.rows.length, I;
msg = "<p>found rows:" + len + "</p>";
Document.queryselector (' #status '). InnerHTML + msg;
for (i = 0; i < len; i++) {
Alert (Results.rows.item (i). log);
}
}, NULL);
});
A complete example
<! DOCTYPE html>
<script type= "Text/javascript" >
var db = OpenDatabase (' mydb ', ' 1.0 ', ' Test db ', 2 * 1024 * 1024);
var msg;
Db.transaction (Function (TX) {
Tx.executesql (' CREATE TABLE IF not EXISTS LOGS (id unique, log) ');
Tx.executesql (' INSERT into LOGS (ID, log) VALUES (1, "Foobar") ");
Tx.executesql (' INSERT into LOGS (ID, log) VALUES (2, "logmsg") ");
msg = ' <p>log message created and row inserted.</p> ';
Document.queryselector (' #status '). InnerHTML = msg;
});
Db.transaction (Function (TX) {
Tx.executesql (' SELECT * from LOGS ', [], function (TX, results) {
var len = results.rows.length, I;
msg = "<p>found rows:" + len + "</p>";
Document.queryselector (' #status '). InnerHTML + msg;
for (i = 0; i < len; i++) {
msg = "<p><b>" + results.rows.item (i). Log + "</b></p>";
Document.queryselector (' #status '). InnerHTML + msg;
}
}, NULL);
});
</script>
<body>
<div id= "status" name= "status" >status message</div>
</body>
• The following is a useful database creation method on the Web
function Initdb () {
var myDB = null;
try {
if (!window.opendatabase) {
The current browser does not have database support
Alert (' DB not supported ');
} else {
var shortname = ' TestDB ';
var version = ' 1.0 ';
var displayName = ' Test offline database ';
var maxSize = 65536; Bytes
MyDB = OpenDatabase (shortname, version, DisplayName, maxSize);
}
catch (e) {
This starts with exception handling.
if (e = = Invalid_state_err) {
Database version exception.
Alert ("Invalid database version.");
} else {
Alert ("Unknown error" +e+ ");
}
}
Returns the created database instance
return MyDB;
}
Executing database statements
The execution of all SQL statements is nested within a transaction, which guarantees the integrity and consistency of the data. The results of executing the SQL statement and error handling need to be specified in the last two parameters of the ExecuteSQL function, which can be omitted.
var sql1 = ' CREATE TABLE IF not EXISTS User (name TEXT, age INTEGER); '
var sql2 = ' INSERT into User values (?,?) ';
var sql3 = ' SELECT * from User WHERE name=? ';
var sql4 = ' DELETE from User where name=? ';
var sql5 = ' UPDATE User SET age=? WHERE name=? ';
Mydb.transaction (Function (TX) {
Tx.executesql (sql,[' parameter '],function (tx,results) {
var len = results.rows.length;
var result = Results.rows.item (0). Name
},function (tx,error) {
});
});
Persistence.js
All SQL operations require result processing and error handling in specified functions, and traditional SQL statements can be more cumbersome to write, and we all miss the ORM tools in the Java language at this time.
Fortunately, the development community has never lacked creative developers, and many open source Web SQL ORM tools have emerged.
Persistence.js is a JavaScript framework that was originally designed to perform OR Mapping HTML Web SQL to facilitate rapid WEB SQL access by client developers.
Later, with the development of the framework, it began to gradually peel back the reliance on the database and become a framework that could support various databases.
(1) Introduction of Persistence.js
Persistence.js divides its core modules into smaller JavaScript files, effectively ensuring high-performance loading, and the user only needs to introduce a JavaScript file when using a functional module. Here we use Persistence.js,persistence.store.sql.js and persistence.store.websql.js, these three files are required to use WEB SQL. Persistence.store.memory.js can also be introduced if a user wants to save data temporarily in memory as an alternative to the Web SQL API that the browser does not support.
<script type= "Text/javascript" src= "Js/persistence.js" ></script>
<script type= "Text/javascript" src= "Js/persistence.store.sql.js" ></script>
<script type= "Text/javascript" src= "Js/persistence.store.websql.js" ></script>
<script type= "Text/javascript" src= "Js/persistence.store.memory.js" ></script>
File Download Address: Https://github.com/coresmart/persistencejs
(2) inserting and deleting data
Inserting a record into the user table simply saves a user object and invokes the flush () method in persistence.
Persistence.js Insert Data
var mark = new User ({name: "Mark", age:60});
Persistence.add (Mark);
Persistence.flush ();
Deleting a record simply deletes a User object, as shown in Listing 10.
Persistence.js Delete Data
Persistence.remove (Mark);
Persistence.flush ();
(3) Support for the association between tables
Traditional databases support One-to-many, Many-to-many, many-to-many tables, which are already well supported in many Java ORM tools, and similar implementations can be found in the persistence.js. For example, the user table in the previous example, we need to add a new address table to record the addresses of the user object, and each user object may have multiple addresses. We can easily implement the table association.
var address = persistence.define (' address ', {
Detail: "TEXT",
ZipCode: "TEXT"
});
User.hasmany ("Addresses", Address, "user");
Persistence.schemasync ();
var addr1 = new Address ({detail: "Addr1", ZipCode: "Code1"});
var addr2 = new Address ({detail: "ADDR2", ZipCode: "Code2"});
Persistence.add (ADDR1);
Persistence.add (ADDR2);
Mark.addresses.add (ADDR1);
Mark.addresses.add (ADDR2);
Persistence.flush ();
The Hasmany () method is a built-in method for each object defined by Persistence.define, and by executing the Hasmany () method, the User table will add an attribute addresses as an association to the address table, and Addre SSEs can be associated to multiple address objects. The Address object also adds a user attribute that associates an address object with a user object through the user property.
Similarly, a user can call the Hasone () method of the Table object to define that the address object is only associated with one user object.
(4) Query data
Persistence.js provides a simple and powerful query syntax that requires only fairly intuitive one or two of API calls to query data. The query portal for a table is actually starting with the object defined by Persistence.define. For example, User defined in the above article.
User.all () will return directly to a querycollection that will contain all the User objects.
User.load () can load a user object based on the username passed in to it.
User.findby () can query the set of eligible user objects and return them based on the user's incoming object properties and the value of the property.
User.all (). One (function (user) {
Console.log (user);
}); Query out the first User object
User.load ("6c22fd66801c41728ae5a6bce0a8ee54", function (user) {
Console.log (User.Name);
}); Query out User object with ID "6c22fd66801c41728ae5a6bce0a8ee54"
User.findby ("name", "Mark", function (user) {
Console.log (User.age);
}); Query out name is Mark's User object
The querycollection,persistence.js that are returned by the query provide rich extensibility features to organize the data in the collection, such as Filter,or,and,order,limit and so on, and interested readers can Persistence.js's official web site to find the relevant introduction.
However!!!
It's a bit embarrassing for him to meow.
The consortium abandoned the Websql on November 18, 2010. But it does not affect the use of its current functions, is still very strong, users are not a minority.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
