[Sqlite] -- & gt; Embedded Database Transaction understanding and instance operations, sqlite Embedded Database

[Sqlite] --> Embedded Database Transaction understanding and instance operations, sqlite Embedded Database


SQLite Transaction (Transaction)

A Transaction is a unit of work performed on a database. A Transaction is a unit or sequence of work completed in a logical order. It can be completed manually or automatically by a database program.

Transaction refers to the extension of one or more modified databases. For example, if you are creating a record, updating a record, or deleting a record from the table, you are executing a transaction on the table. It is important to control transactions to ensure data integrity and handle database errors.

In fact, you can combine many SQLite queries into a group and put all these together for execution as part of the transaction.

1. Transaction attributes

A Transaction has the following four standard attributes:

· Atomicity: ensures that all operations in the work unit are successfully completed. Otherwise, the transaction will be terminated when a fault occurs, and the previous operations will be rolled back to the previous state.

· Consistency: ensures that the database changes its status correctly in successfully committed transactions.

· Isolation: Makes transaction operations independent and transparent.

· Durability: ensure that the results or effects of committed transactions still exist in the case of a system failure.

2. Transaction Control

Use the following command to control transactions:

· Begin transaction: Start TRANSACTION processing.

· COMMIT: Save the changes, or use the end transaction command.

· ROLLBACK: roll back the changes.

The transaction control command is only used with the DML command INSERT, UPDATE, and DELETE. They cannot be used when creating or deleting tables because these operations are automatically submitted in the database.

3. begin transaction command

Transactions can be started using the BEGIN Transaction command or simple BEGIN command. Such transactions are usually executed until the next COMMIT or ROLLBACK command is run. However, when the database is closed or an error occurs, the transaction will also be rolled back. The following is a simple syntax for starting a transaction:

BEGIN;

or 

BEGIN TRANSACTION;

4. COMMIT command

The COMMIT command is used to save the transaction call changes to the database.

The COMMIT command saves all transactions since the last COMMIT or ROLLBACK command to the database.

The COMMIT command syntax is as follows:

COMMIT;

or

END TRANSACTION;

5. ROLLBACK command

The ROLLBACK command is used to undo transactions that have not been saved to the database.

The ROLLBACK command can only be used to cancel transactions since the last time the COMMIT or ROLLBACK command was issued.

The ROLLBACK command syntax is as follows:

ROLLBACK;

6. instance operations

[Root @ localhost sqlite-autoconf-3080403] # sqlite3 tim. db

SQLite version 3.8.4.3 16:53:12

Enter ". help" for usage hints.

Sqlite> create table company (id int not null, name varchar (20), age int, address varchar (20), salary decimal (7, 2 ));

Now, let's start a transaction and delete the records with age = 25 from the table. Finally, we use the ROLLBACK command to undo all the changes and start the transaction first, delete the record with AGE 25 and query the COMPANY table. This record is not found, as shown in:

Roll back again and query the COMPANY table again. The record with AGE 25 is restored.

 

Now, if you start a transaction, delete the record with AGE 25, and execute COMMIT to submit the changes, you will not see the deleted record, as shown below:

Roll back again and query the COMPANY table again. The record with AGE 25 is restored.

 

Now, if you start a transaction, delete the record with AGE 25, and execute COMMIT to submit the changes, you will not see the deleted record, as shown below:

 

 

Reference Article address: http://www.w3cschool.cc/sqlite/sqlite-transaction.html

How to connect to a database using Eclipse for android development? It is best to have detailed program code

Connect to the database with Android
Android uses the relational database SQLite3, which is a lightweight embedded database that supports SQL and has a wide range of embedded operations. WM also uses SQLite3.

This article won't mention too much about principles, but if you want to quickly learn how to operate SQLite3, This is the article you are looking!

First, let's take a look at the api. All Database-related interfaces and classes are in. database and android. database. sqlite has only two packages, but if you are not good at English or too lazy, you may have to be confused for a while. In fact, there are only a few of them we actually use!

1. SQLiteOpenHelper (android. database. sqlite. SQLiteOpenHelper)

This is an abstract class. We all know that to use it, it must be inherited!

This class has very few methods, and there is a constructor.

SQLiteOpenHelper (android. content. Context context, java. lang. String name, android. database. sqlite. SQLiteDatabase. CursorFactory factory, int version );

The parameter is not explained too much. CursorFactory can directly pass null.

Public void onCreate (SQLiteDatabase db)

This method is called to create a database. Therefore, you should put the table creation operation in this method. Later, we will explain in detail how to create a table.

Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)

From the method name, we can know that this method is to execute updates. Yes, when the version changes, the system will call this method. Therefore, we should delete the existing table in this method, then manually call the onCreate operation

SQLiteDatabase getReadableDatabase ()

Readable SQLiteDatabase object

SQLiteDatabase getWritableDatabase ()

Obtain writable SQLiteDatabase objects

2. SQLiteDatabase (android. database. sqlite. SQLiteDatabase)

Database Operations (add, delete, query, and modify) are all performed in this class.

ExecSQL (SQL)

Execute SQL statements. Using this method + SQL statements, you can easily add, delete, query, and modify SQL statements.

In addition, Android provides methods to add, delete, query, and modify features.

Long insert (TABLE_NAME, null, contentValues) add record

Int delete (TABLE_NAME, where, whereValue) delete records

Int update (TABLE_NAME, contentValues, where, whereValue) update record

Cursor query (TABLE_NAME, null) query records

In addition, there are many methods, such as beginTransaction () starting a transaction, endTransaction () ending a transaction... if you are interested, you can view the api by yourself. I will not repeat it here.

3. Cursor (android. database. Cursor)

Cursor (Interface), this is very familiar, there are many methods in Cursor, commonly used include:

Boolean moveToPos ...... remaining full text>

C ++ uses SQLite3 to open the specified database. After the insert operation is executed, what will be written to the disk? Does not perform operations on the memory?

Sqlite is generally used in an embedded environment. The embedded environment does not have complex disk processing logic, and is generally directly written to the primary storage.
Therefore, in an embedded environment, sqlite data is directly written to the primary storage (that is, its "Hard Disk ").