경량화 DB로는 Java에는
Apache Derby 가 있고
그외 sqlite 가 있다.
sqlite 의 .db 파일을 받아서 java로 처리하는 인터페이스를 처리하는 업무에
필요한 프로그램을 정리해본다.
먼저 .db파일을 간편하게 열어볼 수 있는
SQLiteSpy 프로그램
다운로드 :
http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index
이 파일을 java에서 접근하는 경우
jdbc 드라이버를 다운받아야 한다.
http://www.zentus.com/sqlitejdbc/
드라이버를 설치한후 소스코드 작성
import java.sql.*;
public class Test {
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists people;");
stat.executeUpdate("create table people (name, occupation);");
PreparedStatement prep = conn.prepareStatement(
"insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
prep.setString(1, "Wittgenstein");
prep.setString(2, "smartypants");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
ResultSet rs = stat.executeQuery("select * from people;");
while (rs.next()) {
System.out.println("name = " + rs.getString("name"));
System.out.println("job = " + rs.getString("occupation"));
}
rs.close();
conn.close();
}
} |
|
|
|
이건 내가 사용해볼라고 만든 소스코드
import java.sql.*;
public class SQLiteConnection {
/**
* SQLite Connection 설정
*
* @param dbFileName dbfile 경로
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public Connection connection(String dbFileName) throws ClassNotFoundException, SQLException
{
Class.forName("org.sqlite.JDBC");
String url = "jdbc:sqlite:/"+dbFileName;
Connection conn = DriverManager.getConnection(url);
return conn;
}
/**
* Database Query
*
*/
public void query()
{
Connection conn = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
conn = connection("D:/eclipse34/workspace/project/WebRoot/WEB-INF
/classes/sqlite/db/dbfile.db");
statement = conn.prepareStatement("select * from mgp_top;");
rs = statement.executeQuery();
while (rs.next()) {
System.out.println("content_id = " + rs.getString("content_id"));
System.out.println("content_title = " + rs.getString("content_title"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (statement != null) statement.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
SQLiteConnection connn = new SQLiteConnection();
connn.query();
}
}
|
|
|
Connection 설정하는 법
jdbc:sqlite://dirA/dirB/dbfile
jdbc:sqlite:/DRIVE:/dirA/dirB/dbfile
jdbc:sqlite:///COMPUTERNAME/shareA/dirB/dbfile
예)
jdbc:sqlite:/D:/eclipse34/workspace/project/WebRoot/WEB-INF/classes/sqlite/db/dbfile.db
|
|
|
|
참고문서 :
http://wisefree.tistory.com/160
Using SQLite with Eclipse and DbEdit
http://www.toodlepip.co.uk/blog/2008/08/using-sqlite-eclipse-and-dbedit
