JDBC Access and manipulation of SQLite database

SQLite is an open source embedded relational database, characterized by its high portability, ease of use, compact, efficient and reliable. Unlike other database management systems,SQLite is very simple to install and run, and in most cases, You can start creating, connecting, and using a database as long as you ensure that sqlite binaries exist.

SQLite download page:http://www.sqlite.org/download.html

Window os Download:sqlite-dll-win32-x86-3081002.zip and Sqlite-shell-win32-x86-3081002.zip. Unzip the 2 compressed packets and add the extracted path to The path of the system variable .

The basic commands for manipulating the SQLite database on the command line are as follows.

Create DATABASE: Sqlite3 test.db

Create a table:sqlite> creation table MyTable (ID integer primary key, value text);

Insert data:sqlite> INSERT INTO MyTable (ID, value) VALUES (1, ' Micheal ');

Query data:sqlite> SELECT * FROM MyTable;

the address for the JDBC Connector for SQLite is:https://bitbucket.org/xerial/sqlite-jdbc/downloads

Download the connection driver, such as Sqlite-jdbc-3.8.7.jar, and add the jar package to the Java project Reference.

Use the following class to test whether the SQLite database can be accessed and manipulated properly .

 Packagetest;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.PreparedStatement;ImportJava.sql.ResultSet;Importjava.sql.Statement; Public classTest { Public Static voidMain (string[] args)throwsException {Connection conn=NULL; ResultSet RS=NULL; PreparedStatement Prep=NULL; Try{class.forname ("Org.sqlite.JDBC"); Conn= Drivermanager.getconnection ("jdbc:sqlite:test.db"); Statement Stat=conn.createstatement (); Stat.executeupdate ("DROP table if exists people;"); Stat.executeupdate ("CREATE table people (name, occupation);"); Prep= Conn.preparestatement ("INSERT into people values (?,?);"); Prep.setstring (1, "Gandhi"); Prep.setstring (2, "Politics");            Prep.addbatch (); Prep.setstring (1, "Turing"); Prep.setstring (2, "Computers");            Prep.addbatch (); Prep.setstring (1, "Wittgenstein"); Prep.setstring (2, "smartypants");            Prep.addbatch (); Conn.setautocommit (false);            Prep.executebatch (); Conn.setautocommit (true); RS= Stat.executequery ("SELECT * from people;"));  while(Rs.next ()) {System.out.println ("name =" + rs.getstring ("name")); System.out.println ("Job =" + rs.getstring ("occupation"))); }            //when paging, you can use ResultSet to complete pagination rs.absolute (100), or you can complete pagination in the SQL statement select ... limit 100,10;//The following is the process of releasing a database connection, and instead of releasing the connection when using the database connection pool, the connection is re-placed into the connection pool. //The connection object is generated as an agent, and the connection is added to the thread pool when the connection Close method is called. The thread pool is joined by the connection proxy object. }Catch(Exception e) {e.printstacktrace (); } finally {            if(rs! =NULL) {                Try{rs.close (); } finally {                    if(Prep! =NULL) {                        Try{prep.close (); } finally {                            if(Conn! =NULL) {conn.close (); }                        }                    }                }            }        }    }}

　We can find a file named Test.db in the root directory of the project, which is the database file that was just created. We can also specify test.db for other paths under the SQLite database file.

Here are some other common commands for SQLite:

To set a formatted query result: sqlite>. Mode column

Sqlite>. Header on

Modify table structure, add columns:sqlite> ALTER TABLE mytable add column email text not NULL ' collate nocase;

Create a view: sqlite> CREATE VIEW Nameview as SELECT * FROM MyTable;

Create an index:sqlite> CREATE index TEST_IDX on mytable (value);

Format output data to CSV format:sqlite >.output [Filename.csv]

SQLite >.separator,

SQLite > select * from Test;

SQLite >.output stdout

Import data from CSV file into table:sqlite >create table newtable (ID integer primary key, value text);

SQLite >.import [Filename.csv] NewTable

　　

JDBC Access and manipulation of SQLite database