SQLite Tutorial - Bernhard Preisler

bawltherapistSoftware and s/w Development

Dec 13, 2013 (3 years and 6 months ago)

100 views

SQLite Tutorial
Created by Bernhard Preisler
<
info@bpreisler.com
>
Created date 15.02.2013
Version 05.03.2013
Contents
1 What is SQLite?3
2 Motivation 3
3 Performance 3
3.1 SQLite vs.MySql.........................3
3.1.1 Insert Statements.....................3
3.1.2 Update Statements....................4
3.1.3 Select Statements.....................4
4 Installing SQLite 4
4.1 Eclipse...............................4
4.2 Netbeans.............................6
5 SQLite Pracitcal 6
5.1 Connect to SQlite........................7
5.2 Create Table...........................7
5.3 Insert Statement.........................8
5.4 Update Statement........................8
5.5 Select Statement.........................8
5.6 Delete Statement.........................8
6 SQLite Class 8
2
1 What is SQLite?
SQLite is a opensource,serverless database what makes it easy to use for pro-
grams to handle own data easily.It is designed to use minimal requirements
and libraries that SQlight became a great popularity for applications which
may not consume a lot of electricity.Inter alia supports SQLite DBMS,
transactions,subselects and views and most operations of other databases
can be compiled into SQLite,but a few exceptions and constraints will meet
us (GRANT,REVOKE).In SQLite you haven't a server architecture to run
in the background which is the most signicant dierence between other
database systems.SQLite has only a libriary for Tlc,so we have to fall back
to an external library.
2 Motivation
Our goal in this tutorial is to use SQLite on Eclipse for the programming
language Java.I won't explain Java expressions or sql statements,cause this
will blow up the tutorial in a wring way.If you have questions or additions
that improve the tutorial you can contact me
1
.
3 Performance
I think it is a little bit interesting how sql scale and which one perform
better.I will run the tests on a Windows 7 machine which had a dual core,
4 gigabyte RAM and the Version 3.7.2 of the JDBC SQLite Driver.All
those thinks are local on my machine,because I don't want to risk that the
internet are to slow or aect the results and shut down all programs.
SQLite database:Download
3.1 SQLite vs.MySql
First we examine the performance dierence between SQLite and MySql.The
MySql class is from IT Blogging and I modied methods a little bit.I
used the actual MySql JDBC Connector Version 5.1.23 and the MySql 5.6
Version.
MySql database:Download
3.1.1 Insert Statements
Let's try to nd out which insertion statement are faster.
1
info@bpreisler.com
3
Statements
SQLite (ms)
MySql (ms)
1.000
6374 - 7243 (207%)
3104 - 3476 (100%)
10.000
68211 - 72550 (179%)
37267 - 41298 (100%)
100.000
721214 - 816084 (168%)
393625 - 522264 (100%)
As we see,SQLite is a little bit slower than MySql on the insert statements.
3.1.2 Update Statements
Following soon.
3.1.3 Select Statements
Following soon.
4 Installing SQLite
This section will engage with the installing of SQLite.
4.1 Eclipse
Preconditions
Install SQLite with Eclispe.This installiation is a lot of easier than other
SQLs,don't put o.
1.First you have to download and run Eclipse.After you've created a
project you can add the SQL class into your project.
2.Now you clicking on Properties on your project.
4
3.Click on Java Build Path!Libraries!Add External JARs!Choose
Your Path to the SQLite JAR File!Add it
5
4.Now you see in your project that you add a SQLite referenced Library.
It should look like the following picture.
SQLite is installed into your project,it is quite simple.
4.2 Netbeans
Following soon.
5 SQLite Pracitcal
Now we switch to the practical part of the documentation.You can read
this part without get the earlier chapter.
6
5.1 Connect to SQlite
With the following Code in Listing 1 you can establish a connection to the
local SQLite database.
Listing 1:Java:Connect to the database of SQLite
import j ava.s ql.;
Cl ass.forName ("org.s q l i t e.JDBC");//Cl ass for name
connecti on = DriverManager.getConnecti on ("j dbc:s q l i t e:/"+
"D:/Dropbox/Proj ekte/Java/SQLite"+
"/db/"+
"YourDatabaseName"+
".db");
statement = connecti on.createStatement ( );
5.2 Create Table
To create a table we used the function executeUpdate(String) to execute a
create table statement.
If there are no declared denitions for a column,the column will be created
with the denition NULL.The other denitions are NOT NULL,CHECK,
UNIQUE,PRIMARY KEY.Important to know is that the table name
must not start with"sqlite
",this generates an error.
Listing 2:Java:Create a table
statement.executeUpdate ("
CREATE
TABLE
IF
NOT
EXISTS
t e s t
(
i d
INTEGER
PRIMARY
KEY,
forename
TEXT
NOT
NULL,
l astname
TEXT
NOT
NULL
);
");
We see that the statemant for creating a new table is highly similar to a
normal SQL statement.
Specifcation
Type
TEXT
"TEXT"
NUMERIC
"NUM"
INTEGER
"INT"
REAL
"REAL"
NONE
""(emtpy)
7
As you can see there a limited of typs you can use to use in your columns.
5.3 Insert Statement
Following soon.
5.4 Update Statement
Following soon.
5.5 Select Statement
Following soon.
5.6 Delete Statement
Following soon.
6 SQLite Class
Following soon.
8