[Java] SQLite Example

 Java SQLite Example

1. Download SQLite JDBC 

SQLite JDBC - javalibs

2. Example

import java.sql.*;

public class SQ {

    public static Connection connect(String dbfile) throws SQLException {
        return DriverManager.getConnection("jdbc:sqlite:" + dbfile);
    }

    public static void execute( Connection conn, String[] statements ) throws SQLException {
        Statement state = conn.createStatement();
        for( int i = 0; i < statements.length; i++ ) {
            state.executeUpdate( statements[i] );
        }
    }

    public static void execute( Connection conn, String statement ) throws SQLException {
        Statement state = conn.createStatement();
        state.executeUpdate(statement);
    }

    public static void create( Connection conn, String table, String[][] fields ) throws SQLException {
        StringBuilder sb = new StringBuilder();
        sb.append("create table ").append(table).append(" (");
        for( int i = 0; i < fields.length; i++ ) {
            if( i != 0 ) sb.append(",");
            sb.append(fields[i][0]).append(" ").append(fields[i][1]);
        }
        sb.append(")");
        execute( conn, new String[] {
                "drop table if exists " + table ,
                sb.toString()
        });
    }

    public static void insert( Connection conn, String table, Object[][] fields ) throws SQLException {
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ").append(table).append(" (");
        for( int i = 0; i < fields.length; i++ ) {
            if( i != 0 ) sb.append(",");
            sb.append(fields[i][0]);
        }
        sb.append(") values (");
        for( int i = 0; i < fields.length; i++ ) {
            if( i != 0 ) sb.append(",");
            if( fields[i][1] instanceof String ) {
                sb.append("'").append(fields[i][1]).append("'");
            } else { //Integer
                sb.append(fields[i][1]);
            }
        }
        sb.append(")");
        SQ.execute( conn, sb.toString());
    }

    public static java.util.List<Object[]> query( Connection conn, String table, String[] columns ) throws SQLException {
        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        for( int i = 0; i < columns.length; i++ ) {
            if( i != 0 ) sb.append(",");
            sb.append(columns[i]);
        }
        sb.append(" from ").append(table);

        java.util.List<Object[]> res = new java.util.ArrayList<>();
        ResultSet rs = conn.createStatement().executeQuery( sb.toString());
        while (rs.next()) {
            Object[] row = new Object[columns.length];
            for( int i = 0; i < columns.length; i++ ) {
                row[i] = rs.getObject(columns[i]);
            }
            res.add(row);
        }
        rs.close();
        return res;
    }

    public static void close(Connection conn) throws SQLException {
        conn.close();
    }

    public static void main(String[] args) {
        String table = "info";
        Connection conn = null;
        try {
            conn = SQ.connect("a.db");

            SQ.create( conn, "info", new String[][] {
                    {"name","string"},
                    {"age","integer"},
                    {"num","string"}
            });

            //SQ.execute( conn, "insert into info values" + "('Tom',12,'123-1234')");
            SQ.insert( conn, table, new Object[][] {
                    {"name","Tommy"},
                    {"age",20},
                    {"num","011-1234-5678"}
            });

            java.util.List<Object[]> res = SQ.query( conn, "info", new String[] { "name", "age", "num"} );
            for( Object[] row : res ) {
                System.out.println( "" + row[0] + "," + row[1] + "," + row[2] );
            }

            SQ.close(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}