SQLite 관련 정리
이번에 진행된 랩실 스터디에서는 SQLite에 대한 내용이 추가되었다. SQLite와 JSP를 연동해서 자신만의 CRUD 프로젝트를 구상해보고 이에 대한 적절한 계획을 세우는 것이 이번에 진행하는 스터디의 주요한 목표였다. SQLite는 개인적으로 이번에 처음 접한 내용이어서 꽤 흥미롭게 느껴졌다. 그래서 다음과 같은 순서로 개인 스터디를 진행하였다.
1. SQLite를 전반적으로 파악
2. 교수님이 공유하신 프로젝트를 통해 JSP와의 연동방식 알아보기
3. 나만의 CRUD 프로젝트를 기획하고 간단한 query 문 구상해보기
SQLite
우선 가장 간단하게 내용을 먼저 파악하기 위해서 몇 개의 링크를 참고 하였다.
https://ko.wikipedia.org/wiki/SQLite
SQLite - 위키백과, 우리 모두의 백과사전
ko.wikipedia.org
SQLite는 Mysql과 같이 RDB(= Relational Database) 이지만, 서버가 아닌 응용프로그램에 넣어서 사용하는 비교적 가벼운 데이터 베이스이다. 그래서 mysql과 같이 분리된 서버를 가지지 않고 일반 디스크 파일을 직접 읽고 쓰는 것이 주요한 특징이다. 비교적 가벼운 데이터베이스이기 때문에 Mysql과 같이 대규모 데이터를 처리하기에는 다소 부족하지만, 대규모가 아닌 중소규모의 데이터 처리라면 SQLite를 사용해서도 무리없이 이를 수행할 수 있다. 실제로 SQLite는 안드로이드 운영체제에 기본적을 탑재된 데이터베이스라고 한다. 그리고 여러 개의 파일을 통해서 데이터를 저장하고 관리하는 mysql과 달리 오직 한 개의 파일에 tables, index, trigger, view 등을 모두 저장한다는 것이 주요한 특징이기도 하다.
SQLite는 사용자의 응용프로그램에 임베디드되어 동작하는 오픈소스 DBMS의 일종이며, 실제로 안드로이드, ios, macOS에 기본적으로 포함되어 있다고 한다. 공식적이지는 않지만 뒤의 Lite는 "가볍다"를 의미하는 "light"를 의미한다고 한다. 하지만 기존 SQL들에 비해서 가벼운 만큼 기능적으로 제한되는 부분들도 상당히 많다. 여기서 말하는 "제한"의 대표적 예시로는 "동시접근이 제한된다는 점"과 "인코딩 형식을 유니코드만 지원" 한다는 점이다.
데이터베이스를 관리하기 위해서 필요한 모든 정보들을 오직 하나의 파일에 저장하기 때문에 파일 하나만을 통째로 복사하면 백업이 끝난다는 장점이 있다. 또한 모든 기능을 라이브러리 내에서 구동할 수 있기 때문에 따로 미들웨어를 포함시키지 않아도 프로그램 라이브러리만 포함시키면 사용할 수 있다는 정점이 있다 (아직 이해하기에 다소 어려운 내용...). 그리고 SQL을 이름에 포함함에서 알 수 있듯이 표준 SQL 문법을 지원하기 때문에 별도의 학습 없이 바로 사용할 수 있다는 장점도 있다.
기본적을 C/C++ 언어로 구현되어 있다고 한다.
장단점은 무엇인지, 왜 사용되는 지에 대한 정보를 더 알고 싶다면 아래의 링크를 참고하면 될 것 같다!
https://www.itworld.co.kr/news/117213
SQLite를 사용해야 하는 이유
모든 비즈니스 애플리케이션의 내부에는 구조적 데이터를 저장하고 사용하는 나름의 방법이 구현돼 있다. 클라이언트 측 앱이든 웹 프론트엔드를 사용하는 앱이든 에지 디바이스 앱이든 대부
www.itworld.co.kr
SQLite와 JSP와의 연동
SQLite와 JSP가 어떤 식으로 연동되고 동작하는 지를 알아보는 목적으로 교수님이 다음 링크를 공유해 주셨다.
https://www.codexpedia.com/java/crud-create-read-update-and-delete-in-sqlite-with-java/
CRUD: create, read, update and delete in sqlite with Java | Codexpedia
sqlite create CREATE TABLE web_blog (ID INTEGER PRIMARY KEY autoincrement, NAME CHAR(50) NOT NULL, message TEXT NOT NULL, date_added datetime); sqlite read SELECT * FROM web_blog; sqlite update UPDATE web_blog set message = 'This is updated by a query' whe
www.codexpedia.com
위의 내용을 통해서 알 수 있듯이 SQLite도 SQL 표준 문법을 지원하기 때문에 mysql을 사용할 때와 거의 동일한 qeury 문을 사용해서 CRUD를 구현할 수 있는 것을 확인할 수 있다. 단, SQLite의 경우 mysql에 비해서 지원하는 자료형의 수가 적기 때문에 DDL을 통해서 table을 정의할 때 자료형의 차이에 주의하면서 사용해야할 것 같다.
java를 기준으로 설명할 것인데 java에서 sqlite를 사용하기 위해서는 sqlite-jdbc.jar 파일을 사용해야 한다.
public static void connectDB()
{
Connection c = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:myBlog.sqlite");
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Opened database successfully");
}우선 위의 method는 기본적으로 sqlite 저장공간을 생성하고 이에 연결하는 로직을 작성한 method 이다. 여기서 Connection이라는 표현을 사용하기는 했지만 사실 이는 connection 이라기 보다는 sqlite를 관리하고 유지하기 위한 하나의 파일을 생성하고 이를 open 하는 것이라고 보는 것이 맞을 것 같다. 마찬가지로 Database와의 연결을 위해서 Connection 객체를 생성해서 사용하는 것을 볼 수 있다. 그리고 sqlite와의 연결 자체를 파일 입출력으로도 볼 수 있기 때문에 기본적으로 try-catch 문 구조를 사용하고 있는 것을 확인할 수 있다.
Class.forName이 구체적으로 수행하는 기능이 무엇인지 알아보기 위해서 아래의 링크를 참고하였다.
[Java 궁금증] Class.forName()은 어떻게 동작할까?
JDBC 란 자바에서 제공하는 데이터베이스와 연결하여 데이터를 주고 받을 수 있도록 하는 인터페이스입니다. JDBC를 사용하는 방법은 어떤 데이터베이스를 사용하던지 같습니다. 방법은 간략하게
kyun2.tistory.com
"Class.forName"은 사용할 sqlite 드라이버를 로딩하는 역할을 수행하고 DriverManager.getConnection()을 통해서 connection을 얻는다. 이 과정에서 마찬가지로 얻은 connection은 Connection 객체를 사용한다.
public static void createDB()
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:myBlog.sqlite");
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "CREATE TABLE web_blog " +
"(ID INTEGER PRIMARY KEY autoincrement," +
" NAME CHAR(50) NOT NULL, " +
" message TEXT NOT NULL, " +
" date_added datetime)";
stmt.executeUpdate(sql);
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Table created successfully");
}위의 코드는 sqlite에서 "web_blog" 라는 table을 생성하는 로직을 구현한 method 인데 위의 코드를 통해서 크게 파악할 수 있는 내용은 다음과 같다.
1. Class.forName()을 통해서 sqlite 드라이버를 로딩
2. DriverManager.getConnection()을 통해서 connection을 get
3. Connection.createStatement()로 Statement Instance를 생성
4. String으로 입력하고자 하는 SQL문을 작성
5. Statement.executeUpdate()에 4의 String을 parameter로 전달해서 sql문을 실행
6. Statement와 Connection을 close
public static void insertDB()
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:myBlog.sqlite");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "INSERT INTO web_blog (NAME,message,date_added) " +
"VALUES ('Ken', 'Hello every one!!!', datetime())," +
" ('Jim', 'whats up!!!',datetime());";
stmt.executeUpdate(sql);
stmt.close();
c.commit();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Records created successfully");
}위의 코드는 생성한 table에 record를 추가하는 로직을 작성한 method 이다. 해당 method의 로직을 순차적으로 정리해보면 다음과 같이 정리해볼 수 있다.
1. Class.forName() 을 통해서 sqlite 드라이버를 로딩
2. DriverManager.getConnection() 을 통해서 Connection을 get
3. Connection.createStatement()를 통해서 Statement Instance를 생성
4. String으로 사용하고자 하는 sql statement를 작성
5. Statement.executeUpdate()에 4의 String을 parameter로 전달하여 sql문을 실행
6. Statement instance close
7. Connection.commit() 수행
8. Connection.close()
위의 코드에서 사용된 c.setAutoCommit(false)는 사용자가 명시적으로 c.commit()을 호출하기 전까지는 해당 transaction에서 수행된 작업내용에 대해서 자동으로 commit이 이뤄지지 않도록 막는 것이다.
public static void selectDB()
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:myBlog.sqlite");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM web_blog;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
String message = rs.getString("message");
String date_added = rs.getString("date_added");
System.out.println( "ID : " + id );
System.out.println( "Name : " + name );
System.out.println( "Message : " + message );
System.out.println( "Date Added : " + date_added );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}위의 코드는 table에 저장된 record를 읽어오는 로직이며, 로직을 순차적으로 정리하면 다음과 같다.
1. Class.forName() 을 통해서 sqlite 드라이버를 로딩
2. DriverManager.getConnection() 을 통해서 Connection을 get
3. Connection.createStatement()를 통해서 Statement Instance를 생성
4. Statement.executeQuery() 의 parameter로 SELECT문을 전달
5. 4의 실행결과로 ResultSet Class의 Instance 생성
6. ResultSet.next() + loop statement를 통해서 결과로 받은 record 하나하나에 접근
7. ResultSet instance를 close
8. Statement Instance를 close
9. Connection을 close
public static void updateDB()
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:myBlog.sqlite");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "UPDATE web_blog set message = 'This is updated by updateDB()' where ID=1;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM web_blog;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
String message = rs.getString("message");
String date_added = rs.getString("date_added");
System.out.println( "ID : " + id );
System.out.println( "Name : " + name );
System.out.println( "Message : " + message );
System.out.println( "Date Added : " + date_added );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}위의 코드는 table에 저장된 record를 업데이트 하는 로직을 구현한 method이며, 해당 로직을 순차적으로 정리해보면 다음과 같다.
1. Class.forName() 을 통해서 sqlite 드라이버를 로딩
2. DriverManager.getConnection() 을 통해서 Connection을 get
3. Connection.createStatement()를 통해서 Statement Instance를 생성
4. String으로 실행하고자하는 sql문 작성
5. Statement.executeUpdate() method에 4의 String을 parameter로 전달하여 sql문 실행
6. Connection.commit()
7. Statement와 Connection을 close()
public static void deleteDB()
{
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:myBlog.sqlite");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "DELETE from web_blog where ID=1;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM web_blog;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
String message = rs.getString("message");
String date_added = rs.getString("date_added");
System.out.println( "ID : " + id );
System.out.println( "Name : " + name );
System.out.println( "Message : " + message );
System.out.println( "Date Added : " + date_added );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}위의 코드는 table에 존재하는 특정 record를 삭제하는 로직에 해당하며, 이를 순차적으로 정리해보면 다음과 같다.
1. Class.forName() 을 통해서 sqlite 드라이버를 로딩
2. DriverManager.getConnection() 을 통해서 Connection을 get
3. Connection.createStatement()를 통해서 Statement Instance를 생성
4. String으로 실행하고자하는 sql문 작성
5. Statement.executeUpdate() method에 4의 String을 parameter로 전달하여 sql문 실행
6. Connection.commit()
7. Statement와 Connection을 close()
그래서 지금까지 살펴본 형식, 절차로 해당 sqlite를 사용해서 java에서 CRUD 프로젝트를 구현하는 것이 가능해진다.
조금 의문점인 부분
사실 각각 CRUD에 대한 로직을 보면 매번 Connection에서 실행하는 로직을 반복적으로 로직 초반부에 수행하는 것을 볼 수 있는 데, 그렇다면 Connection method가 가지는 그 의미는 무엇인가?