# SQLiteJDBC

<- | SQLiteJDBC | Changelog | Usage | Speed | Custom Functions | Javadoc

## Custom Functions

As of v018, SQLiteJDBC now provides a custom interface for creating custom functions accessible inside SQLite, written in Java.

All you have to do is subclass `org.sqlite.Function` and implement `xFunc()`. Pass the new instance to `Function.create()` and SQLite is ready to call it.

### Basic Example

    import java.sql.*;
    import org.sqlite.Function;

    public class Test {
        public static void main(String[] args) {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:");

            Function.create(conn, "myFunc", new Function() {
                protected void xFunc() throws SQLException {
                    System.out.println("myFunc called!");
                }
            });

            conn.createStatement().execute("select myFunc();");
            conn.close();
        }
    }

### Aggregate Functions

By subclassing `Function.Aggregate` and implementing `xStep()` and `xFinal()` you can create an aggregate function. E.g.

        Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:");

        Function.create(conn, "mySum", new Function.Aggregate() {
            private int sum = 0;
            protected void xStep() throws SQLException {
                sum += value_int(0);
            }
            protected void xFinal() throws SQLException {
                result(sum);
            }
        });

        Statement stat = conn.createStatement();
        stat.executeUpdate("create table t1 (c1);");
        stat.executeUpdate("insert into t1 values (2);");
        stat.executeUpdate("insert into t1 values (4);");
        stat.executeUpdate("insert into t1 values (3);");
        ResultSet rs = stat.executeQuery("select mySum(c1) from t1;");
        rs.next();
        System.out.println("mySum = " + rs.getInt(1));

Prints `mySum = 9`. It is safe to use internal variables as every time an aggregate function is called it is `clone()`ed to ensure thread safety.

### Triggers

The most interesting use of custom functions are making SQLite triggers. This way your Java application can be informed of changes in your tables.

    import java.sql.*;
    import org.sqlite.Function;

    public class Test {
        public static void main(String[] args) {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:");

            Function.create(conn, "myFunc", new Function() {
                protected void xFunc() {
                    System.out.println("database updated");
                }
            });

            Statement stat = conn.createStatement();
            stat.executeUpdate("create table table1 (col1, col2);");
            stat.executeUpdate(
                "create trigger trig1 after insert on table1 begin" +
                "    select callTrig();" +
                "end;"
            );

            stat.executeUpdate("insert into table1 values (1, 2);");
            conn.close();
        }
    }

### Arguments and Results

The protected functions `value_<type>(int)`, `result(<type>)` of org.sqlite.Function provide access to arguments and the ability to return a value.

        Function.create(conn, "mySum", new Function() {
            protected void xFunc() throws SQLException {
                int s = 0;
                for (int i=0; i < arg(); i++)
                    s += value_int(i);
                result(s);
            }
        });

        ResultSet rs = conn.createStatement().execute(
            "select mySum(1, 3, 5);");
        rs.next();

        System.out.println(rs.getInt(1));

Prints `9`.

2006-08-22