SQLite 개요 및 사용

SQLite는 단일 파일 기반이면서 SQL92를 지원하는 경량의 DB

▣ SQLite 공식사이트
 - http://www.sqlite.org
 - 버전 : 2010년 6월 30일 현재 3.6.23.1
▣ Features Of SQLite
 * Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures.
  * Zero-configuration - no setup or administration needed.
 * Implements most of SQL92. (Features not supported)
 * A complete database is stored in a single cross-platform disk file.
 * Supports terabyte-sized databases and gigabyte-sized strings and blobs. (See limits.html.)
 * Small code footprint: less than 300KiB fully configured or less than 180KiB with optional features omitted.
 * Faster than popular client/server database engines for most common operations.
 * Simple, easy to use API.
 * Written in ANSI-C. TCL bindings included. Bindings for dozens of other languages available separately.
 * Well-commented source code with 100% branch test coverage.
 * Available as a single ANSI-C source-code file that you can easily drop into another project.
 * Self-contained: no external dependencies.
 * Cross-platform: Unix (Linux and Mac OS X), OS/2, and Windows (Win32 and WinCE) are supported out of the box. Easy to port to other systems.
 * Sources are in the public domain. Use for any purpose.
 * Comes with a standalone command-line interface (CLI) client that can be used to administer SQLite databases.

▣ Suggested Uses For SQLite:
 * Application File Format.
 - Rather than using fopen() to write XML or some proprietary format into disk files used by your application, use an SQLite database instead. You'll avoid having to write and troubleshoot a parser, your data will be more easily accessible and cross-platform, and your updates will be transactional.
 *  Database For Gadgets.
 - SQLite is popular choice for the database engine in cellphones, PDAs, MP3 players, set-top boxes, and other electronic gadgets. SQLite has a small code footprint, makes efficient use of memory, disk space, and disk bandwidth, is highly reliable, and requires no maintenance from a Database Adminstrator.
 *  Website Database.
 - Because it requires no configuration and stores information in ordinary disk files, SQLite is a popular choice as the database to back small to medium-sized websites.
 * Stand-in For An Enterprise RDBMS.
 - SQLite is often used as a surrogate for an enterprise RDBMS for demonstration purposes or for testing. SQLite is fast and requires no setup, which takes a lot of the hassle out of testing and which makes demos perky and easy to launch.

▣ 참조사이트
- Sqlite 프로그래밍
- SQLiteDBMS
- SQLite 미니 홈페이지

▣ 설치
 - 다운로드 후 특정 폴더(예: C:\SQLite) 에 풀기만 하면 됨

▣ Java에서 사용하기
 - SQLite JDBC Driver를 이곳에서 다운로드, 둘 중 하나를 받으면 되나. 첫번째를 사용함
  : http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
  : http://www.zentus.com/sqlitejdbc/

 - Sample Java Code

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class App {
 public static void main(String[] args) {
    try {
        String dbPath = "C:/SpatiaLite/db/db.sqlite";

        Class.forName("org.sqlite.JDBC");
       
        SQLiteConfig config = new SQLiteConfig();
        config.enableLoadExtension(true);

        Connection dbCon = DriverManager.getConnection("jdbc:sqlite:" + dbPath, config.toProperties());

        Statement dbStmt = dbCon.createStatement();   
         
        ResultSet rs = dbStmt.executeQuery("SELECT * FROM continent DESC LIMIT 5;");
       
        printResultSet(rs);   
       
        rs.close();

        dbStmt.close();
        dbCon.close();
       
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
 }

 private static void printResultSet(ResultSet rs) {   
    try {
        ResultSetMetaData rsMetaData = rs.getMetaData();

        int columnCount = rsMetaData.getColumnCount();       
       
        System.out.println("=========== " + rsMetaData.getTableName(1) + " ==========");
       
        StringBuilder sb = new StringBuilder();
        for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
            String colName = rsMetaData.getColumnName(columnIndex);
            String colType = rsMetaData.getColumnTypeName(columnIndex);
            int colLen = rsMetaData.getColumnDisplaySize(columnIndex);
           
            if (columnIndex == columnCount) {
                sb.append(String.format("%s(%s, %d)", colName, colType, colLen));
            } else {
                sb.append(String.format("%s(%s, %d) | ", colName, colType, colLen));                   
            }
        }
        System.out.println(sb.toString());           
       
        while(rs.next()) {
            sb = new StringBuilder();
           
            for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
                String colVal = rs.getObject(columnIndex).toString();
                if (columnIndex == columnCount) {
                    sb.append(colVal);
                } else {
                    sb.append(colVal + " | ");                   
                }
            }
           
            System.out.println(sb.toString());
        }
       
    } catch (SQLException e) {
        e.printStackTrace();
    }
 }
}