데이터베이스

Sqlite

sqlite를 그레이들 프로젝트에서 사용하기 위해서는 build.gradle 파일 dependencies 부분에 다음과 같이 넣어 주어야 한다.

// https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc
implementation group: 'org.xerial', name: 'sqlite-jdbc', version: '3.25.2'

디비 생성

public static void createDatabase(String dbName) {

  String url = "jdbc:sqlite:" + dbName;
  try (Connection conn = DriverManager.getConnection(url)) {
    if (conn != null) {
        DatabaseMetaData meta = conn.getMetaData();
        System.out.println("The driver name is " + meta.getDriverName());
        System.out.println("A new database has been created.");
    }
  } catch (SQLException e) {
    System.out.println(e.getMessage());
  }
}

package database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqliteTest {

  public static void createDatabase(String fileName) {

    String url = "jdbc:sqlite:" + fileName;
    try (Connection conn = DriverManager.getConnection(url)) {
      if (conn != null) {
          DatabaseMetaData meta = conn.getMetaData();
          System.out.println("The driver name is " + meta.getDriverName());
          System.out.println("A new database has been created.");
      }
    } catch (SQLException e) {
      System.out.println(e.getMessage());
    }
  }

//    public static void connect() {
//            Connection conn = null;
//        try {
//            // db parameters
//            String url = "jdbc:sqlite:sample.db";
//            // create a connection to the database
//            conn = DriverManager.getConnection(url);
//
//            System.out.println("Connection to SQLite has been established.");
//
//        } catch (SQLException e) {
//            System.out.println(e.getMessage());
//        } finally {
//            try {
//                if (conn != null) {
//                    conn.close();
//                }
//            } catch (SQLException ex) {
//                System.out.println(ex.getMessage());
//            }
//        }
//    }

  public static void createNewTable() {
        // SQLite connection string
        String url = "jdbc:sqlite:test.db";

        // SQL statement for creating a new table
        String sql = "CREATE TABLE IF NOT EXISTS warehouses (\n"
                + "   id integer PRIMARY KEY,\n"
                + "   name text NOT NULL,\n"
                + "   capacity real\n"
                + ");";

        try (Connection conn = DriverManager.getConnection(url);
                Statement stmt = conn.createStatement()) {
            // create a new table
            stmt.execute(sql);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

  private Connection connect() {
        // SQLite connection string
        String url = "jdbc:sqlite:test.db";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }

  public void insert(String name, double capacity) {
        String sql = "INSERT INTO warehouses(name,capacity) VALUES(?,?)";

        try (Connection conn = this.connect();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setDouble(2, capacity);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

  public void selectAll(){
        String sql = "SELECT id, name, capacity FROM warehouses";

        try (Connection conn = this.connect();
             Statement stmt  = conn.createStatement();
             ResultSet rs    = stmt.executeQuery(sql)){

            // loop through the result set
            while (rs.next()) {
                System.out.println(rs.getInt("id") +  "\t" +
                                   rs.getString("name") + "\t" +
                                   rs.getDouble("capacity"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

  public void update(int id, String name, double capacity) {
        String sql = "UPDATE warehouses SET name = ? , "
                + "capacity = ? "
                + "WHERE id = ?";

        try (Connection conn = this.connect();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // set the corresponding param
            pstmt.setString(1, name);
            pstmt.setDouble(2, capacity);
            pstmt.setInt(3, id);
            // update
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

  public void delete(int id) {
        String sql = "DELETE FROM warehouses WHERE id = ?";

        try (Connection conn = this.connect();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // set the corresponding param
            pstmt.setInt(1, id);
            // execute the delete statement
            pstmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

  public static void main(String[] args) {
//            createDatabase("test.db");
//            connect();
    createNewTable();

    SqliteTest app = new SqliteTest();
        // insert three new rows
//        app.insert("Raw Materials", 3000);
//        app.insert("Semifinished Goods", 4000);
//        app.insert("Finished Goods", 5000);

        app.selectAll();
//        app.update(3, "Finished Products", 5500);
//        app.delete(6);
        app.selectAll();
  }
}

Sqlite Viewer

DB Browser for SQLite 프로그램을 설치해서 sqlite 형식의 디비 파일을 읽고 수정할 수 있습니다.