| 1 | <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" |
|---|
| 2 | "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> |
|---|
| 3 | <html xmlns="http://www.w3.org/1999/xhtml"> |
|---|
| 4 | <head> |
|---|
| 5 | <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> |
|---|
| 6 | <link rel="stylesheet" type="text/css" href="/content.css" /> |
|---|
| 7 | <link rel="stylesheet" type="text/css" href="/javasource.css" /> |
|---|
| 8 | <title>SQLiteJDBC - Usage</title> |
|---|
| 9 | </head> |
|---|
| 10 | <body> |
|---|
| 11 | <div class="ad"> |
|---|
| 12 | <script type="text/javascript"><!-- |
|---|
| 13 | google_ad_client = "pub-3074710994457921"; |
|---|
| 14 | google_ad_width = 120; |
|---|
| 15 | google_ad_height = 240; |
|---|
| 16 | google_ad_format = "120x240_as"; |
|---|
| 17 | google_ad_type = "text"; |
|---|
| 18 | google_ad_channel =""; |
|---|
| 19 | |
|---|
| 20 | <script type="text/javascript" |
|---|
| 21 | src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> |
|---|
| 22 | </script> |
|---|
| 23 | </div> |
|---|
| 24 | |
|---|
| 25 | |
|---|
| 26 | |
|---|
| 27 | <div class="content"> |
|---|
| 28 | <h1>SQLiteJDBC</h1> |
|---|
| 29 | |
|---|
| 30 | <p class="item"> |
|---|
| 31 | <a href="../"><-</a> | |
|---|
| 32 | <a href="index.html">SQLiteJDBC</a> | |
|---|
| 33 | <a href="changes.html">Changelog</a> | |
|---|
| 34 | <a href="usage.html">Usage</a> | |
|---|
| 35 | <a href="speed.html">Speed</a> | |
|---|
| 36 | <a href="functions.html">Custom Functions</a> | |
|---|
| 37 | <a href="api/">Javadoc</a> |
|---|
| 38 | </p> |
|---|
| 39 | |
|---|
| 40 | <h2>Usage</h2> |
|---|
| 41 | |
|---|
| 42 | <p>Download the binary for the platform you are developing on. Open the |
|---|
| 43 | tarball and copy the two files into your application directory:</p> |
|---|
| 44 | <pre> |
|---|
| 45 | sqlitejdbc.jar |
|---|
| 46 | sqlitejdbc.dll (or libsqlitejdbc.so or libsqlitejdbc.jnilib) |
|---|
| 47 | </pre> |
|---|
| 48 | |
|---|
| 49 | <p>Reference the driver in your code:</p> |
|---|
| 50 | <pre class="code" id="Classes"> |
|---|
| 51 | Class.forName(<font id="StringLiteral">"org.sqlite.JDBC"</font>); |
|---|
| 52 | Connection conn = DriverManager.getConnection(<font id="StringLiteral">"jdbc:sqlite:filename"</font>); |
|---|
| 53 | <font id="SingleLineComment">// ... use the database ...</font> |
|---|
| 54 | conn.close(); |
|---|
| 55 | </pre> |
|---|
| 56 | |
|---|
| 57 | <p>And call your program with the driver's JAR file in the classpath |
|---|
| 58 | and the C library in the librarypath. E.g.</p> |
|---|
| 59 | <pre> |
|---|
| 60 | java -cp sqlitejdbc.jar -Djava.library.path=. yourclass |
|---|
| 61 | </pre> |
|---|
| 62 | |
|---|
| 63 | <p>That's it.</p> |
|---|
| 64 | |
|---|
| 65 | <h3>Dates and Time</h3> |
|---|
| 66 | |
|---|
| 67 | <p>SQLiteJDBC implements <tt>ResultSet.getDate()/getTime()</tt> and |
|---|
| 68 | <tt>PreparedStatement.setDate()/setTime()</tt> in the only efficient |
|---|
| 69 | way the JDBC spec allows for, which is storing the time as a 64-bit |
|---|
| 70 | long of milliseconds since UTC. This is the standard unix timestamp.</p> |
|---|
| 71 | |
|---|
| 72 | <p>This does not conflict with the SQLite |
|---|
| 73 | <a href="http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions"> standard |
|---|
| 74 | format</a> as long as care is taken to transform the unix timestamp |
|---|
| 75 | into what SQLite functions use, either at the point where the date is |
|---|
| 76 | stored or when using it elsewhere in SQLite. For example:</p> |
|---|
| 77 | |
|---|
| 78 | <pre class="code" id="Classes"> |
|---|
| 79 | prep = conn.prepareStatement(<font id="StringLiteral">"insert into test values (?);"</font>); |
|---|
| 80 | prep.setDate(<font id="IntegerLiteral">1</font>, <font id="New">new</font> Date(<font id="IntegerLiteral">1000</font>)); |
|---|
| 81 | prep.executeUpdate(); |
|---|
| 82 | |
|---|
| 83 | rs = stat.executeQuery(<font id="StringLiteral">"select * from test;"</font>); |
|---|
| 84 | rs.getString(<font id="IntegerLiteral">1</font>) == <font id="StringLiteral">"1000"</font>; |
|---|
| 85 | </pre> |
|---|
| 86 | |
|---|
| 87 | In the above example the date is being stored as a unix timestamp. |
|---|
| 88 | To store the date in the standard SQLite format, transform it with |
|---|
| 89 | the <tt>datetime()</tt> and <tt>strftime()</tt> functions: |
|---|
| 90 | |
|---|
| 91 | <pre class="code" id="Classes"> |
|---|
| 92 | prep = conn.prepareStatement( |
|---|
| 93 | <font id="StringLiteral">"insert into test values datetime(? / 1000, 'unixepoch');"</font>); |
|---|
| 94 | prep.setDate(<font id="IntegerLiteral">1</font>, <font id="New">new</font> Date(<font id="IntegerLiteral">1092941466000</font>)); |
|---|
| 95 | prep.executeUpdate(); |
|---|
| 96 | |
|---|
| 97 | rs = stat.executeQuery(<font id="StringLiteral">"select * from test;"</font>); |
|---|
| 98 | assert(rs.getString(<font id="IntegerLiteral">1</font>).equals(<font id="StringLiteral">"2004-08-10 18:51:06"</font>)); |
|---|
| 99 | |
|---|
| 100 | rs = stat.executeQuery(<font id="StringLiteral">"select strftime('%s', col1) * 1000 from test;"</font>); |
|---|
| 101 | assert(rs.getDate(<font id="IntegerLiteral">1</font>).equals(<font id="New">new</font> Date(<font id="IntegerLiteral">987654321</font>))); |
|---|
| 102 | </pre> |
|---|
| 103 | |
|---|
| 104 | |
|---|
| 105 | |
|---|
| 106 | <h2>Compiling</h2> |
|---|
| 107 | |
|---|
| 108 | <p>Download the source tarball, extract, set your <tt>$JAVA_HOME</tt> |
|---|
| 109 | env variable and type:</p> |
|---|
| 110 | <pre> |
|---|
| 111 | $ make |
|---|
| 112 | </pre> |
|---|
| 113 | <p>See the <a href="src/README">README</a> for more details.</p> |
|---|
| 114 | |
|---|
| 115 | </div> |
|---|
| 116 | <p class="sig">2006-09-26</p> |
|---|
| 117 | <script src="http://www.google-analytics.com/urchin.js" type="text/javascript"> |
|---|
| 118 | </script> |
|---|
| 119 | <script type="text/javascript"> |
|---|
| 120 | _uacct = "UA-2989140-1"; |
|---|
| 121 | urchinTracker(); |
|---|
| 122 | </script> |
|---|
| 123 | </body> |
|---|
| 124 | </html> |
|---|
