Sqlite3 introduction (an introduction to sqlite3)

An Introduction to sqlite3 (sqlite3 Introduction)
0. This article is a brief translation of the documents and descriptions on the official SQLite website. It mainly introduces some features of SQLite and its common applications.

1. SQLite Introduction
SQLite is a software that implements the following features:
I. Self-contained: SQLite maximizes self-contained. It meets the minimum requirements for external libraries and operating systems, which makes it ideal for embedded devices. At the same time, it can be applied to stable and rarely modified applications.
SQLite is developed using ANSI-C and can be compiled by any standard C compiler.
The communication between SQLite and the operating system and the storage device (Disk) is completed through VFS (Virtual File System). For embedded devices, it is not difficult to develop an available VFS.
SQLite uses mutex to ensure operation security in multi-threaded environments.
The source code of SQLite is a single c file-sqlite3.c. If the project needs to use it, it only needs to contain the c file and the corresponding header file (sqlite3.h ).

Ii. serverless: Most SQL database engines act as independent server processes. Applications send requests to the server through some protocols, such as TCP/IP, and accept the results, in this way, you can communicate with the database server. Unlike SQLite, processes can directly read and write database files by accessing the database without the need for server processes on the middle layer. The main advantage of this implementation is that you do not need to install, configure, initialize, manage, and maintain individual service processes. However, the database engine can use servers to prevent client application bugs and ensure that the server is not damaged by client errors. Most SQL databases are based on the C/S mode. In serverless databases, SQLite currently only allows simultaneous access by multiple applications.

Iii. Zero-Configuration: SQLite does not require any configuration --- install, setup, configure Server, administration, create instance, assign permissions, recover, troubleshooting.

Iv. transactional: SQLite implements acid (atomic-atomicity, consistent-consistency, isolated-isolation, durable-persistence ). SQLite implements serialization transactions to ensure acid, even if a program exception occurs, the operating system exception or power failure.

V. Most widely deployed SQL database: Most databases are in the C/S mode, while a server can provide services for many people. SQLite is an embedded database software, and most users use copies of multiple databases at the same time. SQLite is widely used in mobile phones, PDAs, MP3 players, and set-top box devices. The following lists some applications that use SQLite as an Embedded Server:
Mozilla Firefox uses SQLite as a database.
Mac computers contain multiple copies of SQLite for different applications.
PHP uses SQLite as a built-in database.
The Skype client software uses SQLite internally.
Symbianos (smart phone operating platform pilot) built-in SQLite.
The AOL mail client is bound to SQLite.
Solaris 10 requires SQLite during startup.
McAfee antivirus software uses SQLite.
IPhone uses SQLite.
Many other mobile phone manufacturers except Symbian and Apple use SQLite.
For more information about using SQLite's famous users, see the http://www.sqlite.org/famous.html

Vi. opensource: SQLite is in public domain. The official statement is as follows:
Anyone is free to copy, modify, publish, use, compile, compile, or distribute the original SQLite code, either in source code form or as a compiled binary, for any purpose, either cial or non-policcial, and by any means.
Some other features, such as faster execution speed than the C/S database engine, easy to use, good source code comments, cross-platform, etc., please refer to the SQLite features list:
Http://www.sqlite.org/features.html

2. Application scenarios of SQLite
SQLite is compact, fast, and reliable because of its simplicity-simple management, simple operation, simple embedding, and easy maintenance customization.
Of course, SQLite also has some disadvantages, which depends on the user's needs. SQLite does not have high concurrency, good access permission control, and there is no built-in function set, it does not support stored procedures and profound SQL language features (SQLite does not fully support sql92), XML/Java extensions, and does not support the scalability of bytes-byte or peta-byte. Therefore, when you need the functions listed above, SQLite is not suitable for users. SQLite is not an enterprise-level RDBMS.
The following lists some application scenarios:
Application file format
Embedded devices and applications
Small and Medium websites
Built-in or temporary database
Command Line dataset analysis tool
As an alternative product for enterprise databases-demo or test phase
Teaching
If you want to learn it for future use or develop similar application scenarios, you can use SQLite as the database software.
Enterprise-level RDBMS is suitable for C/S applications, high-throughput websites, very large datasets, and highly concurrent applications.
For specific properties that distinguish SQLite from other databases, see: http://www.sqlite.org/different.html

3. download and use
Based on different platforms, you can download the corresponding response on the download page: http://www.sqlite.org/download.html. Next, I will introduce how to use SQLite on Windows. You can use wrapper or driver to access SQLite in multiple programming languages. The following describes how to use Java programming language to access SQLite and perform some common operations.
I. Download SQLite
After the sqlite3.exe package of Windows is decompressed, it is only a two-step sqlite3.exe file.
You can directly run this program to go to The sqlite3 Console (similar to the MySQL console). You can enter. Help to obtain help information .. Quit can launch the console. A simple operation on the console is as follows:
// You can directly use sqlite3 databasename to create a database
// SQLite does not support statements such as create database and drop database. You can directly delete database files when drop database.
E:/tools/SQLite> sqlite3.exe test <br/> SQLite version 3.6.11 <br/> enter ". help "for instructions <br/> enter SQL statements terminated with a"; "<br/> SQLite>. help <br/>. backup? DB? File backup dB (default "Main") to file <br/>. bail On | off stop after hitting an error. default off <br/>. databases list names and files of attached databases <br/> // lots of other help information omitted here <br/>. width num... set column widths for "column" mode <br/> SQLite>. databases <br/> seq name file </P> <p> --- ------------------------------------------------------------------ </P> <p> 0 Main E: /tools/SQLite/test </P> <p> SQLite> Create Table student (ID varchar (10), name varchar (20), age smallint ); <br/> SQLite> select * from student; <br/> SQLite> insert into student values ('000000', 'lovesizhao', 26 ); <br/> SQLite> select * from student; <br/> 1001 | lovesizhao | 26 <br/> SQLite> drop table student; <br/> SQLite>. quit <br/>
This method of using sqlite3 directly at the command line prompt is not the focus of this article.
The SQL syntax supported by sqlite3 is as follows:
Sqlite3 keywords: http://www.sqlite.org/lang_keywords.html
SQL syntax supported by sqlite3: http://www.sqlite.org/lang.html
SQL syntax not supported by sqlite3: http://www.sqlite.org/cvstrac/wiki? P = unsupportedsql

Ii. download Java JDBC driver for sqlite3
On the download page http://www.sqlite.org/cvstrac/wiki? P = sqlitewrappers, select to download JDBC driver for sqlite3.
The author uses sqlitejdbc (http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC ).
The license of the JDBC driver is Apache Software Foundation 2.0 license.

Iii. Use the sqlite3 Database
I used netbeans as the development environment and wrote a simple Java class. Note: I am not going to explain all sqlite3 functions and features in detail, but as an introduction. If you are interested, for more information, see the official sqlite3 website and related resources.
The use of netbeans does not fall within the scope of this article. The following describes two simple Java classes.
The sqlitejdbc driver version used by the author is 3.6.11.

Sqlite3util. Java
Package sqlite3; <br/> Import Java. SQL. statement; <br/> Import Java. SQL. connection; <br/> Import Java. SQL. drivermanager; <br/> Import Java. SQL. resultset; <br/> Import Java. SQL. sqlexception; <br/> Import Java. util. logging. level; <br/> Import Java. util. logging. logger; </P> <p> Public final class sqlite3util {</P> <p> private final static string url = "JDBC: SQLite :"; <br/> private final static string test = "tes T "; </P> <p> Private Static connection getconnection (string URL) throws sqlexception {<br/> connection conn = NULL; <br/> try {<br/> class. forname ("org. SQLite. JDBC "); <br/> conn = drivermanager. getconnection (URL); </P> <p >}catch (classnotfoundexception ex) {<br/> logger. getlogger (sqlite3util. class. getname ()). log (level. severe, null, ex); <br/>}finally {<br/> return conn; <br/>}</P> <p> Public static connection getsqlite3connection () throws sqlexception {<br/> return getconnection (URL + test ); <br/>}</P> <p> Public static connection getsqlite3connection (string database) throws sqlexception {<br/> // here database can be Database Name, database file name or memory: <br/> // connection = drivermanager. getconnection ("JDBC: SQLite: C:/work/mydatabase. DB "); <br/> // connection Connection = drivermanager. getconnection ("JDBC: SQLite:/home/LEO/work/mydatabase. DB "); <br/> // connection = drivermanager. getconnection ("JDBC: SQLite: Memory:"); <br/> return getconnection (URL + (Database = NULL | database. equals (""))? Test: Database); <br/>}</P> <p> Public static void close (connection Conn, statement stmt, resultset RS) throws sqlexception {<br/> If (Conn! = NULL) {<br/> conn. Close (); <br/>}< br/> If (stmt! = NULL) {<br/> stmt. Close (); <br/>}< br/> If (RS! = NULL) {<br/> Rs. close (); <br/>}</P> <p> Public static void closequiet (connection Conn, statement stmt, resultset RS) {<br/> try {<br/> If (Conn! = NULL) {<br/> Conn. close (); <br/>}< br/>} catch (sqlexception e) {<br/> logger. getlogger (sqlite3util. class. getname ()). log (level. warning, null, e); <br/>}< br/> try {<br/> If (stmt! = NULL) {<br/> stmt. close (); <br/>}< br/>} catch (sqlexception e) {<br/> logger. getlogger (sqlite3util. class. getname ()). log (level. warning, null, e); <br/>}< br/> try {<br/> If (RS! = NULL) {<br/> Rs. close (); <br/>}< br/>} catch (sqlexception e) {<br/> logger. getlogger (sqlite3util. class. getname ()). log (level. warning, null, e); <br/>}</P> <p>
Sqlite3test. Java

Package sqlite3; </P> <p> Import Java. SQL. statement; <br/> Import Java. SQL. connection; <br/> Import Java. SQL. databasemetadata; <br/> Import Java. SQL. resultset; <br/> Import Java. SQL. sqlexception; <br/> Import Java. util. logging. level; <br/> Import Java. util. logging. logger; </P> <p> public class sqlite3test {</P> <p>/** <br/> * @ Param ARGs the command line arguments <br/> */< br/> Public static void main (string [] ARGs) {<br/> // declare and initialize database names and Java. SQL objects <br/> connection conn = NULL; <br/> statement stmt = NULL; <br/> resultset rs = NULL; <br/> string studentdb = "D: /netbeans/netbeansprojects/sqlite3/student. DB "; <br/> string inmemorydb =": Memory :"; </P> <p> // The dbmd is used to check what sqlite3jdbc driver provide for jdbc <br/> databasemetadata dbmd = NULL; </P> <p> try {<br/> // get connection to default database: Test <br/> conn = sqlite3util. getsqlite3connection (); <br/> If (conn = NULL) {<br/> return; <br/>}< br/> dbmd = Conn. getmetadata (); <br/> // just print information to standard console instead of output file <br/> system. out. println ("databaseproductname:" + dbmd. getdatabaseproductname (); <br/> system. out. println ("sqlkeywords:" + dbmd. getsqlkeywords (); <br/> system. out. println ("jdbcmajorversion:" + dbmd. getjdbcmajorversion (); <br/> system. out. println ("jdbcminorversion:" + dbmd. getjdbcminorversion (); </P> <p> // get connection to database: D:/netbeans/netbeansprojects/sqlite3/student. DB <br/> conn = sqlite3util. getsqlite3connection (studentdb); <br/> stmt = Conn. createstatement (); <br/> stmt.exe cuteupdate ("Drop table if exists student. student "); <br/> stmt.exe cuteupdate (" create table student (ID smallint primary key, name varchar (20 ))"); <br/> stmt.exe cuteupdate ("insert into student values (1, 'zhangzhongliang ')"); <br/> stmt.exe cuteupdate ("insert into student values (2, 'liusizhao') "); <br/> rs = stmt.exe cutequery (" select * from student "); <br/> int I = 1; <br/> while (RS. next () {<br/> system. out. println ("Row No =" + I); <br/> system. out. println ("ID =" + Rs. getint ("ID"); <br/> system. out. println ("name =" + Rs. getstring ("name"); <br/> I ++; <br/>}< br/> stmt.exe cuteupdate ("Drop table student "); </P> <p> // get connection to database in memory <br/> // This will not create a database data file in your disk <br/> conn = sqlite3util. getsqlite3connection (inmemorydb); <br/> stmt = Conn. createstatement (); <br/> stmt.exe cuteupdate ("Drop table if exists student. student "); <br/> stmt.exe cuteupdate (" create table student (ID smallint primary key, name varchar (20 ))"); <br/> stmt.exe cuteupdate ("insert into student values (1, 'zhangzhongliang ')"); <br/> stmt.exe cuteupdate ("insert into student values (2, 'liusizhao') "); <br/> rs = stmt.exe cutequery (" select * from student "); <br/> I = 1; <br/> while (RS. next () {<br/> system. out. println ("Row No =" + I); <br/> system. out. println ("ID =" + Rs. getint ("ID"); <br/> system. out. println ("name =" + Rs. getstring ("name"); <br/> I ++; <br/>}< br/> stmt.exe cuteupdate ("Drop table student "); <br/>} catch (sqlexception ex) {<br/> logger. getlogger (sqlite3test. class. getname ()). log (level. severe, null, ex); <br/>}finally {<br/> sqlite3util. closequiet (Conn, stmt, RS); <br/>}</P> <p>
4. Summary
This article mainly introduces sqlite3 as a database software. It has different features, advantages and disadvantages, and application scenarios. Finally, a simple program test is conducted using the JDBC driver, which introduces how to access the SQLite database through JDBC.

5. References
SQLite official address: http://sqlite.org/
SQLite document: http://sqlite.org/docs.html
SQLite JDBC driver: http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
Java JDBC: http://java.sun.com/products/jdbc/overview.html