swdev:java:connecting_to_sqlite_database [Audina Documentation]

Connecting to SQLite Database

Setup for SQLite:
sqlite
Add code:
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.acme.myapp;
 
import com.acme.myapp.models.Organization;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
 
/**
 *
 * @author mayr
 */
public class DbUtils 
{
    public static void Test() 
    {
        Connection connection = null;
        try {
            // create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.
 
            statement.executeUpdate("drop table if exists person");
            statement.executeUpdate("create table person (id integer, name string)");
            statement.executeUpdate("insert into person values(1, 'leo')");
            statement.executeUpdate("insert into person values(2, 'yui')");
            ResultSet rs = statement.executeQuery("select * from person");
 
            while(rs.next()) {
              // read the result set
              System.out.println("name = " + rs.getString("name"));
              System.out.println("id = " + rs.getInt("id"));
            }
        } catch(SQLException exc) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(exc.getMessage());
        } finally {
            try {
                if(connection != null) {
                    connection.close();
                }
            } catch(SQLException exc) {
                // connection close failed.
                System.err.println(exc);
            }
        }
    }
 
    public static ArrayList<Object> GetOrganizationData() 
    {
        ArrayList<Object> data = new ArrayList<Object>();
        Connection connection = null;
        try {
            // Create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:d" + 
                Paths.get(System.getProperty("user.dir") + "/src/com/acme/myapp/data/", "data.dbf"));
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.
            ResultSet rs = statement.executeQuery("SELECT * FROM organization");
 
            while(rs.next()) {
              // Read the result set
              System.out.println("id   = " + rs.getInt("id"));
              System.out.println("name = " + rs.getString("name"));
              ArrayList<String> row = new ArrayList<String>();
              row.add(rs.getString("id"));
              row.add(rs.getString("name"));
              data.add(row);
            }
        } catch(SQLException exc) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(exc.getMessage());
        } finally {
            try {
                if(connection != null) {
                    connection.close();
                }
            } catch(SQLException exc) {
                // connection close failed.
                System.err.println(exc);
            }
        }
 
        return data;
    }
 
    public static void InsertOrganizationTestData() 
    {
        Connection connection = null;
        try {
            // Create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:" + 
                Paths.get(System.getProperty("user.dir") + "/src/com/acme/myapp/data/", "data.dbf"));
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.
//            statement.executeUpdate("CREATE TABLE `organization` (\n" +
//                "	`id` INT(11) NOT NULL PRIMARY KEY,\n" +
//                "	`entity` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`name` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`country_code` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`phone` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`fax` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`email` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`website` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`mail_address_abbrev` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`mail_address1` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`mail_address2` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`mail_city` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`mail_state_prov` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`mail_postal_code` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`mail_country` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`street_address_abbrev` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`street_address1` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`street_address2` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`street_city` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`street_state_prov` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`street_postal_code` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`street_country` VARCHAR(255) NULL DEFAULT NULL,\n" +
//                "	`details` TEXT NULL,\n" +
//                "	`created_at` DATETIME NULL DEFAULT NULL,\n" +
//                "	`updated_at` DATETIME NULL DEFAULT NULL,\n" +
//                "	`deactivated_at` DATETIME NULL DEFAULT NULL,\n" +
//                "	`status` TINYINT(1) NOT NULL DEFAULT '1',\n" +
//                "	`tag` VARCHAR(255) NULL DEFAULT NULL\n" +
//                ")"
//            );
            statement.executeUpdate("INSERT INTO organization VALUES (" + 
                    "1, '10001', 'International Institute of Advanced Studies', " +
                    "null, null, null, null, null, null, null, null, null, null, " +
                    "null, null, null, null, null, null, null, null, null, null, " +
                    "null, null, null, 1, null" +
                    ")"
            );
 
            Organization org = new Organization();
 
//            ArrayList<Object> arr = new ArrayList<Object>()  {{
//                    1; "10001"; "International Institute of Advanced Studies";  
//                    null; null; null; null; null; null; null; null; null; null;  
//                    null; null; null; null; null; null; null; null; null; null;  
//                    null; null; null; 1; null 
//            }};
 
//            ArrayList<Object> arr = new ArrayList<Object>(
//                    1, "10001", "International Institute of Advanced Studies",  
//                    null, null, null, null, null, null, null, null, null, null,  
//                    null, null, null, null, null, null, null, null, null, null,  
//                    null, null, null, 1, null 
//            );
 
            List<Object> arr = Arrays.asList(
                    1, "10001", "International Institute of Advanced Studies",  
                    null, null, null, null, null, null, null, null, null, null,  
                    null, null, null, null, null, null, null, null, null, null,  
                    null, null, null, 1, null 
            );
 
//            List<Object> arr = new ArrayList<Object>();
//            arr.add(1);
//            arr.add("10001");
//            arr.add("International Institute of Advanced Studies");
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(null);
//            arr.add(1);
//            arr.add(null);
 
            org.fromArray(arr);
 
        } catch(SQLException exc) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(exc.getMessage());
        } finally {
            try {
                if(connection != null) {
                    connection.close();
                }
            } catch(SQLException exc) {
                // connection close failed.
                System.err.println(exc);
            }
        }
    }
 
    public static void InsertData(String DatabaseName, String TableName, List<Object> Columns, List<Object> Data) 
    {
        Connection connection = null;
        try {
            // Create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:" + 
                Paths.get(System.getProperty("user.dir") + "/src/com/acme/myapp/data/", DatabaseName));
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.
 
            // Concatenate values into a string
 
            // Get field values from array
            // Eg: {1, '10001', 'International Institute of Advanced Studies', " +
            //      "null, null, null, null, null, null, null, null, null, null, " +
            //      "null, null, null, null, null, null, null, null, null, null, " +
            //      "null, null, null, 1, null " }
 
            // Columns
            String cols = "";
            for(Object col: Columns) {
                if (col instanceof Integer) {
                    cols += Integer.toString((Integer)col) + ", ";
                } else if(col instanceof String) {
                    cols += "'" + (String)col + "', ";
                } else if(col instanceof Boolean) {
                    cols += ((Boolean)col ? 1: 0) + ", ";
                } else if(col instanceof LocalDateTime) {
                    cols += ((LocalDateTime)col).toString() + ", ";
                } else if(col == null) {
                    cols += "null, ";
                }
            }
            cols = StringUtils.stripEnd(cols, ", ");
            System.out.println("Data cols :" + cols);
 
            // Data
            String values = "";
            for(Object field: Data) {
                if (field instanceof Integer) {
                    values += Integer.toString((Integer)field) + ", ";
                } else if(field instanceof String) {
                    values += "'" + (String)field + "', ";
                } else if(field instanceof Boolean) {
                    values += ((Boolean)field ? 1: 0) + ", ";
                } else if(field instanceof LocalDateTime) {
                    values += ((LocalDateTime)field).toString() + ", ";
                } else if(field == null) {
                    values += "null, ";
                }
            }
            values = StringUtils.stripEnd(values, ", ");
            System.out.println("Data values :" + values);
 
            statement.executeUpdate("INSERT INTO "+TableName+" VALUES (" + values + ")" );
        } catch(SQLException exc) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(exc.getMessage());
        } finally {
            try {
                if(connection != null) {
                    connection.close();
                }
            } catch(SQLException exc) {
                // connection close failed.
                System.err.println(exc);
            }
        }
    }
}