SQLite database Overview

Hello everyone, today I will introduce the knowledge of SQLite and use Java to perform operations on the SQLite database.

SQLite is an open source embedded database engine written by D. Richard hipp in C language. It supports most sql92 standards and can run on all major operating systems.

SQLite consists of the following parts: SQL Compiler, kernel, backend, and attachment. By using virtual machines and virtual database engine (vdbe), SQLite is more convenient to debug, modify, and expand the SQLite kernel. All SQL statements are compiled into readable assembly that can be executed in the SQLite virtual machine. The overall structure of SQLite is as follows:

It is worth mentioning that the pocket-sized SQLite can support databases of up to 2 TB size, and each database exists in the form of a single file, these data are stored on the disk in the form of B-tree data structure.

In terms of transaction processing, SQLite achieves independent transaction processing through database-level isolation and shared locks. This means that multiple processes can read data from the same database at the same time, but only one can write data. An exclusive lock must be obtained before a process or thread wants the database to perform write operations. After an exclusive lock is obtained, other read or write operations will not happen again.

SQLite uses a dynamic data type. When a value is inserted into the database, SQLite checks its type. If the type does not match the associated column, SQLite will try to convert the value to the type of the column. If it cannot be converted, the value will be stored as its own type, which SQLite calls "weak type ". However, there is a special case. If it is an integer primary key, other types will not be converted, and a "datatype missmatch" error will be reported.

In summary, SQLite supports null, integer, real, text, and BLOB data types, representing null values, integer values, floating point values, string texts, and binary objects respectively.

Next, we will operate the SQLite database in person.

Before the operation, friends to download SQLite database, the official download page is http://sqlite.org/download.html, I'm in Windows test, I chose precompiled binaries for windows under the sqlite-shell-win32-x86 and sqlite-analyzer-win32-x86 zip package, the former is SQLite database engine, the latter is the SQLite database analyzer, which is mainly used to analyze the database status and other information. You can also download the analyzer as needed. Decompress the downloaded files to obtain two executable files,

These two files can be placed in the specified location according to your preferences. I will put them in the root directory of the D Drive. Next we will perform step-by-step operations on SQLite:

Create a database:

D:\>sqlite3 test.db
SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .databases
seq  name             file

---  ---------------  ----------------------------------------------------------

0    main             D:\test.db

sqlite>

We executed the sqlite3 command. The parameter is the name of the database. If the database already exists, it will be used. If it does not exist, a new one will be created. Here we simply created test at the current location. DB, you can also create your own database in any existing and writable directory. (If you are not familiar with SQLite commands, run the ". Help" command to list all command lists ).

Create a table:

sqlite> CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), age SMALLINT);
sqlite> .tables
person
sqlite> .schema person
CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), age SMALLINT);
sqlite>

After creating a table, you can use ". tables command to view the existing table, use ". schema command to view the table structure. If no table name is followed for parameters, all table creation statements are output.

Insert data:

sqlite> INSERT INTO person VALUES (NULL, 'john', 30);
sqlite> SELECT * FROM person;
1|john|30

Import data from the. SQL file:

sqlite> .read test.sql
sqlite> SELECT * FROM person;
1|john|30
2|david|35
3|henry|40
sqlite>

Analyze the database usage status:

D:\>sqlite3_analyzer test.db
/** Disk-Space Utilization Report For test.db

Page size in bytes.................... 1024
Pages in the whole file (measured).... 4
Pages in the whole file (calculated).. 4
Pages that store data................. 4          100.0%
Pages on the freelist (per header).... 0            0.0%
Pages on the freelist (calculated).... 0            0.0%
Pages of auto-vacuum overhead......... 0            0.0%
Number of tables in the database...... 4
Number of indices..................... 0
Number of named indices............... 0
Automatically generated indices....... 0
Size of the file in bytes............. 4096
Bytes of user payload stored.......... 39           0.95%

Back up the database:

There are two methods to back up the SQLite database. If the database is in use, use the. Dump command from the command line interface. In this way, you can create a file that contains the necessary commands and data to recreate the database .. The dump command can also be used to back up database tables.

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), age SMALLINT);
INSERT INTO "person" VALUES(1,'john',30);
INSERT INTO "person" VALUES(2,'david',35);
INSERT INTO "person" VALUES(3,'henry',40);
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('person',3);
COMMIT;
sqlite> .output dump.sql
sqlite> .dump
sqlite>

We can specify the output target as a file, and then use the command, the output information will be written to the specified file. If you want to restore to the standard output, you can set it as follows:

sqlite> .output stdout
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), age SMALLINT);
INSERT INTO "person" VALUES(1,'john',30);
INSERT INTO "person" VALUES(2,'david',35);
INSERT INTO "person" VALUES(3,'henry',40);
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('person',3);
COMMIT;
sqlite>

If the database is not in use, you can directly copy the database file to a safe location.

Finally, we can use ". Quit" or ". Exit" to exit SQLite.

Management tools:

Now there are many SQLite management tools on the network. I recommend a good tool: SQLite expert.

Using SQLite in Java:

To use SQLite in Java, We need to download the SQLite driver. We recommend that you use the plugin,

After downloading the driver, we create a new project named SQLite:

In, we introduce the SQLite driver package to the class path and create a DB folder for storing database files. Finally, let's take a look at the test. Java code:

package com.scott.sqlite;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test {
	public static void main(String[] args) throws Exception {
		Class.forName("org.sqlite.JDBC");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:db/test.db");
		Statement stmt = conn.createStatement();

		stmt.executeUpdate("DROP TABLE IF EXISTS person");
		stmt.executeUpdate("CREATE TABLE person(id INTEGER, name STRING)");
		stmt.executeUpdate("INSERT INTO person VALUES(1, 'john')");
		stmt.executeUpdate("INSERT INTO person VALUES(2, 'david')");
		stmt.executeUpdate("INSERT INTO person VALUES(3, 'henry')");
		ResultSet rs = stmt.executeQuery("SELECT * FROM person");
		while (rs.next()) {
			System.out.println("id=>" + rs.getInt("id") + ", name=>" + rs.getString("name"));
		}
		stmt.close();
		conn.close();
	}
}

Run the test. Java file and the result is as follows:

At this time, a test. DB file is generated under our DB directory:

SQLite usage instructions:

Currently, no network server is available for SQLite. The only way to run SQLite from an application running on another computer is to run it from a shared network. This will cause some problems, such as file locking problems in UNIX and Windows Network Sharing. There is also the performance degradation caused by latency related to access network sharing.

SQLite only provides database-level locking.

SQLite does not have a user account concept, but determines the permissions of all databases based on the file system.

Conclusion:

Because of the low resource usage, good performance, and zero management costs, embedded databases are useful. For example, Android and iPhone have built-in SQLite databases for developers, its ease of use can accelerate application development and make complex data storage much easier.