root/XerialJ/trunk/sqlite-jdbc/sqlitejdbc/web/functions.html

Revision 2260, 6.7 kB (checked in by leo, 4 years ago)

merged from branch
Line 
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 - Custom Functions</title>
9</head>
10<body>
11<div class="ad">
12<script type="text/javascript"><!--
13google_ad_client = "pub-3074710994457921";
14google_ad_width = 120;
15google_ad_height = 240;
16google_ad_format = "120x240_as";
17google_ad_type = "text";
18google_ad_channel ="";
19//--></script>
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="../">&#60;-</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>Custom Functions</h2>
41
42<p>As of v018, SQLiteJDBC now provides a custom interface for
43creating custom functions accessible inside SQLite, written in
44Java.</p>
45
46<p>All you have to do is subclass
47<a href="api/index.html"><tt>org.sqlite.Function</tt></a> and
48implement <tt>xFunc()</tt>. Pass the new instance to
49<tt>Function.create()</tt> and SQLite is ready to call it.</p>
50
51<h3>Basic Example</h3>
52
53<pre class="code" id="Classes">
54<font id="Import">import</font> java.sql.*;
55<font id="Import">import</font> org.sqlite.Function;
56
57<font id="Public">public</font> <font id="Class">class</font> Test {
58    <font id="Public">public</font> <font id="Static">static</font> <font id="Void">void</font> main(String[] args) {
59        Class.forName(<font id="StringLiteral">"org.sqlite.JDBC"</font>);
60        Connection conn = DriverManager.getConnection(<font id="StringLiteral">"jdbc:sqlite:"</font>);
61
62        Function.create(conn, <font id="StringLiteral">"myFunc"</font>, <font id="New">new</font> Function() {
63            <font id="Public">protected</font> <font id="Void">void</font> xFunc() <font id="Throws">throws</font> SQLException {
64                System.out.println(<font id="StringLiteral">"myFunc called!"</font>);
65            }
66        });
67
68        conn.createStatement().execute(<font id="StringLiteral">"select myFunc();"</font>);
69        conn.close();
70    }
71}
72</pre>
73
74<h3>Aggregate Functions</h3>
75
76<p>By subclassing <tt>Function.Aggregate</tt> and implementing <tt>xStep()</tt>
77and <tt>xFinal()</tt> you can create an aggregate function. E.g.</p>
78
79<pre class="code" id="Classes">
80    Class.forName(<font id="StringLiteral">"org.sqlite.JDBC"</font>);
81    Connection conn = DriverManager.getConnection(<font id="StringLiteral">"jdbc:sqlite:"</font>);
82
83    Function.create(conn, <font id="StringLiteral">"mySum"</font>, <font id="New">new</font> Function.Aggregate() {
84        <font id="Private">private</font> <font id="Int">int</font> sum = <font id="IntegerLiteral">0</font>;
85        <font id="Public">protected</font> <font id="Void">void</font> xStep() <font id="Throws">throws</font> SQLException {
86            sum += value_int(<font id="IntegerLiteral">0</font>);
87        }
88        <font id="Public">protected</font> <font id="Void">void</font> xFinal() <font id="Throws">throws</font> SQLException {
89            result(sum);
90        }
91    });
92
93    Statement stat = conn.createStatement();
94    stat.executeUpdate(<font id="StringLiteral">"create table t1 (c1);"</font>);
95    stat.executeUpdate(<font id="StringLiteral">"insert into t1 values (2);"</font>);
96    stat.executeUpdate(<font id="StringLiteral">"insert into t1 values (4);"</font>);
97    stat.executeUpdate(<font id="StringLiteral">"insert into t1 values (3);"</font>);
98    ResultSet rs = stat.executeQuery(<font id="StringLiteral">"select mySum(c1) from t1;"</font>);
99    rs.next();
100    System.out.println(<font id="StringLiteral">"mySum = "</font> + rs.getInt(1));
101</pre>
102
103<p>Prints <tt>mySum = 9</tt>. It is safe to use internal variables as
104every time an aggregate function is called it is <tt>clone()</tt>ed to
105ensure thread safety.</p>
106
107<h3>Triggers</h3>
108
109<p>The most interesting use of custom functions are making SQLite triggers.
110This way your Java application can be informed of changes in your tables.</p>
111
112<pre class="code" id="Classes">
113<font id="Import">import</font> java.sql.*;
114<font id="Import">import</font> org.sqlite.Function;
115
116<font id="Public">public</font> <font id="Class">class</font> Test {
117    <font id="Public">public</font> <font id="Static">static</font> <font id="Void">void</font> main(String[] args) {
118        Class.forName(<font id="StringLiteral">"org.sqlite.JDBC"</font>);
119        Connection conn = DriverManager.getConnection(<font id="StringLiteral">"jdbc:sqlite:"</font>);
120
121        Function.create(conn, <font id="StringLiteral">"myFunc"</font>, <font id="New">new</font> Function() {
122            <font id="Public">protected</font> <font id="Void">void</font> xFunc() {
123                System.out.println(<font id="StringLiteral">"database updated"</font>);
124            }
125        });
126
127        Statement stat = conn.createStatement();
128        stat.executeUpdate(<font id="StringLiteral">"create table table1 (col1, col2);"</font>);
129        stat.executeUpdate(
130            <font id="StringLiteral">"create trigger trig1 after insert on table1 begin"</font> +
131            <font id="StringLiteral">"    select callTrig();"</font> +
132            <font id="StringLiteral">"end;"</font>
133        );
134
135        stat.executeUpdate(<font id="StringLiteral">"insert into table1 values (1, 2);"</font>);
136        conn.close();
137    }
138}
139</pre>
140
141<h3>Arguments and Results</h3>
142
143<p>The protected functions <tt>value_&lt;type&gt;(int)</tt>,
144<tt>result(&lt;type&gt;)</tt> of
145<a href="api/index.html">org.sqlite.Function</a> provide access
146to arguments and the ability to return a value.</p>
147
148<pre class="code" id="Classes">
149    Function.create(conn, <font id="StringLiteral">"mySum"</font>, <font id="New">new</font> Function() {
150        <font id="Public">protected</font> <font id="Void">void</font> xFunc() <font id="Throws">throws</font> SQLException {
151            int s = 0;
152            for (int i=0; i &lt; arg(); i++)
153                s += value_int(i);
154            result(s);
155        }
156    });
157
158    ResultSet rs = conn.createStatement().execute(
159        <font id="StringLiteral">"select mySum(1, 3, 5);"</font>);
160    rs.next();
161
162    System.out.println(rs.getInt(1));
163</pre>
164
165<p>Prints <tt>9</tt>.</p>
166
167</div>
168<p class="sig">2006-08-22</p>
169<script src="http://www.google-analytics.com/urchin.js" type="text/javascript">
170</script>
171<script type="text/javascript">
172    _uacct = "UA-2989140-1";
173    urchinTracker();
174</script>
175</body>
176</html>
Note: See TracBrowser for help on using the browser.