File: tutorial.html

package info (click to toggle)
sqljet 1.1.10-2
  • links: PTS, VCS
  • area: main
  • in suites: bookworm, bullseye, sid, trixie
  • size: 6,364 kB
  • sloc: java: 36,711; ansic: 695; xml: 232; makefile: 31; sh: 19; sql: 14; php: 4
file content (217 lines) | stat: -rw-r--r-- 18,437 bytes parent folder | download | duplicates (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  <title>SQLJet&nbsp;::&nbsp;Pure Java SQLite&nbsp;::&nbsp;Tutorial</title>
  <meta name="keywords" content="SQLite,Java,API,Library,Development,SQL,BTree,Relational,Embedded"/>
  <meta name="description" content="Pure Java version of SQLite DBMS. Open Source, provided by TMate Software"/>
  <link rel="stylesheet" href="css/styles.css" />
  <link href="prettify/prettify.css" type="text/css" rel="stylesheet" />
  <script type="text/javascript" src="prettify/prettify.js"></script>
</head>

<body onload="prettyPrint()">
<div id="centerColumn">
  <div id="leftFade"></div>
  <div id="rightFade"></div>
  <div id="space"><p></p></div>
  <div id="content">

 <div style="font-style:italic; margin-top: 3px">Simple tutorial on how to use SQLJet API</div> 
 
 <h2>Contents</h2>
 <p>This page provides very simple tutorial on how to use SQLJet API to work with the database of SQLite format. The following operations are described: </p><ul><li>Create new database and configure options </li><li>Create table and indices </li><li>Insert records </li><li>Select records in order specified by index </li><li>Lookup records matching scope or exact value </li><li>Update and delete records </li><li>Drop table and index </li></ul><p>Full working source code of this example is available as part of SQLJet project in Subversion repository at 
 <a href="http://svn.sqljet.com/repos/sqljet/tags/${project.buildVersion.baseVersion}/sqljet-example-simple/src/main/java/org/tmatesoft/sqljet/examples/tutorial/Tutorial.java" rel="nofollow">Tutorial.java</a>. </p>
 
 <h2>Create new database</h2>

<pre class="prettyprint lang-java">00  File dbFile = new File(DB_NAME);
01  dbFile.delete();
02        
03  SqlJetDb db = SqlJetDb.open(dbFile, true);
04  db.getOptions().setAutovacuum(true);
05  db.beginTransaction(SqlJetTransactionMode.WRITE);
06  try {
07    db.getOptions().setUserVersion(1);
08  } finally {
09    db.commit();
10  }</pre><p>For the sake of atomicity this example always creates new empty data base (<i>lines 0,1</i>), then <tt>SqlJetDb</tt> object is created for that file (<i>line 3</i>), opened for writing. As file does not yet exist it will be created. </p><p>Sqlite format supports number of options. Some of these options have to be set before anything is changed in the database, even before first transaction is started (<i>line 4</i>), because exact way transaction is executed depends on these very options. Other options should be set inside &quot;write&quot; transaction (<i>line 7</i>). </p><p>There are basically two ways to execute certain code as a transaction. First is described above (<i>lines 5 and 9</i>) - write transaction is started and then committed. To roll back a transaction one should call <tt>db.rollback()</tt> instead of <tt>db.commit()</tt>, for instance in case exception is thrown from the try/catch block. </p><p>Another way is to subclass <tt>SqlJetTransaction</tt> class and run it with <tt>SqlJetDb.runTransaction(...)</tt> method: </p><pre class="prettyprint"> Object result = db.runTransaction(new ISqlJetTransaction() {
   public Object run(SqlJetDb db) throws SqlJetException {
     db.getOptions().setUserVersion(1);
     return true;
   }
 } SqlJetTransactionMode.WRITE);</pre><p>Above method is more convenient in a sense that transaction will be automatically rolled back in case exception is thrown from the <tt>run</tt> method or committed in case there were no exceptions. On the other side usage of anonymous or inner classes might be inconvenient and then one could prefer the first way to run transaction. In this example we will use first way to save on indentation and curly brackets. </p><p>Note, that when you no longer need to work with the database it makes sense to <i>close</i> it by calling <tt>SqlJetDb.close()</tt> method: </p><pre class="prettyprint">  SqlJetDb db = SqlJetDb.open(dbFile, true);
  try {
    ...
    ...
  } finally {
    db.close();
  }</pre><h2>Create table and indices</h2><p>We will create one table with three fields and two indices. Third index (for primary key field) will be created automatically. In SQLite format database schema is stored as plain SQL statements and similar statements are used to create tables and indices. </p><p>We are using the following statements: </p><pre class="prettyprint">CREATE TABLE employees (second_name TEXT NOT NULL PRIMARY KEY , first_name TEXT NOT NULL, 
             date_of_birth INTEGER NOT NULL)

CREATE INDEX full_name_index ON employees(first_name,second_name)

CREATE INDEX dob_index ON employees(date_of_birth)</pre><p>And the following code: </p><pre class="prettyprint">  db.beginTransaction(SqlJetTransactionMode.WRITE);
  try {            
    db.createTable(createTableQuery);
    db.createIndex(createFirstNameIndexQuery);
    db.createIndex(createDateIndexQuery);
  } finally {
    db.commit();
  }</pre><p>First index, <tt>full_name_index</tt> is a composite one - it indexes rows by values of two fields - first_name and second_name. This means that searching using two values (first name and second name) will use this index and will work fast.  </p><p><tt>dob_index</tt> is a simple index of integer type field. SQLite &quot;integer&quot; is always represented as signed long in Java. Here we use long value type to store dates. </p><p>Finally, SQLJet will create one more index, because one of the table fields (<tt>second_name</tt>) is declared as <tt>PRIMARY KEY</tt>. This index will be names <tt>sqlite_autoindex_employees_1</tt> and this name will be available later, so that we will use this index as well. </p><p>Note, that database schema is created in a write transaction. </p><h2>Insert records</h2><p>Now let fill our <tt>employees</tt> table we&#x27;ve just created: </p><pre class="prettyprint">  Calendar calendar = Calendar.getInstance();
  calendar.clear();
 
  db.beginTransaction(SqlJetTransactionMode.WRITE);
  try {
    ISqlJetTable table = db.getTable(TABLE_NAME);
    calendar.set(1991, 4, 19);
    table.insert(&quot;Prochaskova&quot;, &quot;Elena&quot;, calendar.getTimeInMillis());
    calendar.set(1967, 5, 19);
    table.insert(&quot;Scherbina&quot;, &quot;Sergei&quot;, calendar.getTimeInMillis());
    calendar.set(1987, 6, 19);
    table.insert(&quot;Vadishev&quot;, &quot;Semen&quot;, calendar.getTimeInMillis());
    calendar.set(1982, 7, 19);
    table.insert(&quot;Sinjushkin&quot;, &quot;Alexander&quot;, calendar.getTimeInMillis());
    calendar.set(1979, 8, 19);
    table.insert(&quot;Stadnik&quot;, &quot;Dmitry&quot;, calendar.getTimeInMillis());
    calendar.set(1977, 9, 19);
    table.insert(&quot;Kitaev&quot;, &quot;Alexander&quot;, calendar.getTimeInMillis());
  } finally {
    db.commit();
  }</pre><p>Code above is pretty straightforward: we fetch table by name (<tt>employees</tt>), then call  <tt>table.insert(...)</tt> method passing values of all fields for each row. These fields are <tt>second_name</tt>, <tt>first_name</tt> and finally <tt>date_of_birth</tt>.  </p><p>SQLJet updates indices automatically on any modifications done to the tables, so there is no need to call other methods. </p><h2>Select records in order specified by index</h2><p>Before looking at the code that selects records from the table, lets introduce utility method that simplifies displaying of those records. This method accepts <tt>ISqlJetCursor</tt> - object of iterator type that represents ordered set of rows - and prints out those rows: </p><pre class="prettyprint">  private static void printRecords(ISqlJetCursor cursor) throws SqlJetException {
    try {
      if (!cursor.eof()) {
        do {
          System.out.println(cursor.getRowId() + &quot; : &quot; + 
                             cursor.getString(FIRST_NAME_FIELD) + &quot; &quot; + 
                             cursor.getString(SECOND_NAME_FIELD) + &quot; was born on &quot; + 
                             formatDate(cursor.getInteger(DOB_FIELD)));
         } while(cursor.next());
      }
    } finally {
      cursor.close();
    }
  }</pre><p>This utility method iterates over ordered row set using <tt>cursor.next()</tt> method until cursor points behind the last row in the ordered set - <tt>cursor.next()</tt> return <tt>false</tt> and <tt>cursor.eof()</tt> returns <tt>true</tt>.  </p><p>At every particular moment of its lifetime, cursor points to one of the rows in the ordered set it represents (or to <tt>null</tt> row in case end of the ordered row set has been reached) and allows user to get fields values for the very row it points to. Additionally to the fields defined by schema every row has <tt>rowId</tt> - unique long integer which is, by default, equal to the row number (1-based). </p><p>When cursor is no longer needed, <tt>cursor.close()</tt> method will free associated resources and will make that cursor instance invalid. </p><p>Now, when utility method has been introduced it is easy to write code that selects records and print then out: </p><pre class="prettyprint">  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
  try {
    printRecords(table.order(table.getPrimaryKeyIndexName()));
  } finally {
    db.commit();
  }</pre><p>Method <tt>table.order(String indexName)</tt> returns all rows in the table in order defined by the index specified. In this case we use index that has been automatically created for the primary key field. </p><p>Note, that we run above code in a <tt>READ_ONLY</tt> transaction. This helps us to make sure that no concurrent write operation influence our row set. </p><p>Other examples on how rows might be selected: </p><pre class="prettyprint">  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
  try {
    printRecords(table.order(FULL_NAME_INDEX));
  } finally {
    db.commit();
  }</pre><p>- in order defined by a composite index, i.e. sorted by a concatenation of a first_name and second_name field values. </p><pre class="prettyprint">  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
  try {
    printRecords(table.open());
  } finally {
    db.commit();
  }</pre><p>- in order defined by the <tt>rowId</tt>, i.e. sorted in order rows were added to the table. </p><pre class="prettyprint">  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
  try {
    printRecords(table.order(DOB_INDEX).reverse());
  } finally {
    db.commit();
  }</pre><p>- from records with less value in <tt>date_of_birth</tt> field to those with greater values.  </p><p>Note the use of <tt>ISqlJetCursor.reverse()</tt> method - it &#x27;reverses&#x27; the cursor returning its mirrored copy that will iterate rows in the opposite order. &#x27;Reversed&#x27; cursor wraps original cursor, so that later changes its position when former is iterated. It is enough to close reversed cursor to close original one as well. </p><h2>Lookup records matching scope or exact value</h2><p>Similar cursor-based approach is used to select only certain records - those that match certain criteria. The difference is that <tt>table.lookup(indexName, ...)</tt> method is used instead of <tt>table.order(indexName, ...)</tt>. </p><p><tt>table.lookup(...)</tt> method accepts index name and field values to select records. It is easy to understand this looking at examples: </p><pre class="prettyprint">  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
  try {
    printRecords(table.lookup(FULL_NAME_INDEX, &quot;Alexander&quot;));
  } finally {
    db.commit();
  }</pre><p>- gets all records with first part of  <tt>full_name_index</tt> equal to &#x27;Alexander&#x27;. This prints out two records: </p><pre class="prettyprint">6 : Alexander Kitaev was born on Oct 19, 1977
4 : Alexander Sinjushkin was born on Aug 19, 1982</pre><p>And with stricter criteria: </p><pre class="prettyprint">  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
  try {
    printRecords(table.lookup(FULL_NAME_INDEX, &quot;Alexander&quot;, &quot;Kitaev&quot;));
  } finally {
    db.commit();
  }</pre><p>- gets all records with both parts of <tt>full_name_index</tt> specified. This prints out single record: </p><pre class="prettyprint">6 : Alexander Kitaev was born on Oct 19, 1977</pre><p>Note, that currently SQLJet only allows to search for a string (specifying beginning of it) using indices, not <i>inside</i> the string (specifying part of it or regular expression). This functionality will be available in the next versions of SQLJet. </p><p>Other way to select records matching criteria is to specify <tt>scope</tt>, not exact field values. It is possible to do with the help of <tt>table.scope(...)</tt> method that takes index name, range start and end values and returns our old friend <tt>cursor</tt>: </p><pre class="prettyprint">  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
  try {
    printRecords(table.scope(FULL_NAME_INDEX, new Object[] {&quot;B&quot;}, new Object[] {&quot;I&quot;}));
 } finally {
    db.commit();
  }</pre><p>- prints all records with <tt>full_name_index</tt> (which is a composite of <tt>first_name</tt> and <tt>second_name</tt>) in range from B to I inclusive. Here it means that all employees with first name starting with letter B to I will be selected. </p><pre class="prettyprint">  Calendar calendar = Calendar.getInstance();
  calendar.setTime(new Date(System.currentTimeMillis()));
  calendar.add(Calendar.YEAR, -30);
 
  db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
  try {
    printRecords(table.scope(DOB_INDEX, 
                     new Object[] {Long.MIN_VALUE}, 
                     new Object[] {calendar.getTimeInMillis()}));
 } finally {
    db.commit();
 }</pre><p>- prints all records with <tt>date_of_birth</tt> value in scope between <tt>Long.MIN_VALUE</tt> and data thirty years ago from now. </p><h2>Update and delete records</h2><p>To modify (update) or delete records SQLJet uses the following algorithm: </p><ol><li>Start <tt>WRITE</tt> transaction. </li><li>Select rows you&#x27;d like to modify or delete, in other words get a <tt>cursor</tt>. </li><li>Iterate over cursor updating or deleting rows as you go. </li></ol><p>Example below deletes records of all employees who are older than thirty years old (<i>lines 07:13</i>). Then it adds one more record (<i>line 16</i>)and changes <tt>date_of_birth</tt> field value for all records in the table (<i>lines 18:27</i>)): </p><pre class="prettyprint">00  Calendar calendar = Calendar.getInstance();
01  calendar.setTime(new Date(System.currentTimeMillis()));
02  calendar.add(Calendar.YEAR, -30);
03
04  db.beginTransaction(SqlJetTransactionMode.WRITE);
05  try {
06    // delete
07    ISqlJetCursor deleteCursor = table.scope(DOB_INDEX, 
08                                   new Object[] {Long.MIN_VALUE}, 
09                                   new Object[] {calendar.getTimeInMillis()});
10    while (!deleteCursor.eof()) {
11      deleteCursor.delete();
12    }
13    deleteCursor.close();
14
15    // insert
16    table.insert(&quot;Smith&quot;, &quot;John&quot;, 0);
17
18    // update
19    calendar.setTime(new Date(System.currentTimeMillis()));
20    ISqlJetCursor updateCursor = table.open();
21    do {
22       updateCursor.update(
23             updateCursor.getValue(SECOND_NAME_FIELD), 
24             updateCursor.getValue(FIRST_NAME_FIELD), 
25             calendar.getTimeInMillis());
26    } while(updateCursor.next());
27    updateCursor.close();
28  } finally {
29    db.commit();
30  }</pre><p>Code above is ran, of course, as a <tt>WRITE</tt> transaction and similar to <tt>table.insert(...)</tt>, delete and update methods does all necessary updates to indices. </p><h2>Drop table and indices</h2><p>To drop (delete) table and indices use <tt>SqlJetDb.dropTable(String tableName)</tt> and <tt>SqlJetDb.dropIndex(String indexName)</tt> methods.  </p><p>This is pretty clear and more interesting is how to figure out what tables and indices are contained in particular database. SQLJet provides an API to read database schema and fetching names of all tables and indices is easy: </p><pre class="prettyprint">  db.beginTransaction(SqlJetTransactionMode.WRITE);
  try {      
   Set&lt;String&gt; indices = db.getSchema().getIndexNames();
   Set&lt;String&gt; tables = db.getSchema().getTableNames();
   for (String tableName : tables) {
      ISqlJetTableDef tableDef = db.getSchema().getTable(tableName);
      Set&lt;ISqlJetIndexDef&gt; tableIndices = db.getSchema().getIndexes(tableName);
      for (ISqlJetIndexDef indexDef : tableIndices) {
         if (!indexDef.isImplicit()) {
           db.dropIndex(indexDef.getName());
         }
      }
      db.dropTable(tableName);
    }
  } finally {
    db.commit();
  }</pre><p>Above code gets names of all tables stored in the database and list of indices for each table, then drops those indices and tables. It is not necessary to drop first indices and then table - dropping table deletes indices automatically. <tt>ISqlJetTableDef</tt> and <tt>ISqlJetIndexDef</tt> objects provides detailed information of table and index including all names, fields and their types. </p> 
  
  <div id="footer">
    <div id="copyright">Copyright &copy; 2004-${project.thisYear}, TMate Software</div>
    <div id="java">Java&trade; and all Java-based marks are a trademark or registered trademark of
      Sun Microsystems, Inc. in the United States and other countries.<br/>
      TMate Software and the website sqljet.com are independent of Sun Microsystems,
      Inc. and have no relationship, formal or informal.
      </div>
    <div id="java">SQLite&trade; is a registered trademark of Hipp, Wyrick & Company, Inc.
      in the United States and other countries.<br/>
      TMate Software and the website sqljet.com are independent of Hipp, Wyrick & Company, Inc. 
      and have no relationship, formal or informal.
      </div>
  </div>

 </div> 
 </div>
</div>
<script type="text/javascript">
var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
</script>
<script type="text/javascript">
try {
var pageTracker = _gat._getTracker("UA-10187081-1");
pageTracker._trackPageview();
} catch(err) {}</script>
</body>
</html>