DBService.java

1 클래스

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class DBService {
	private static DBService instance = null;
	private DBService() {}
	public static DBService getDB() {
		if (instance == null) {
			synchronized (DBService.class) {
				if (instance == null) {
					instance = new DBService();
					instance.init();
				}
			}
		}
		return instance;
	}
	private Connection conn;
	public void init() {
		try {
			Class.forName("org.h2.Driver");
			conn = DriverManager.getConnection("jdbc:h2:~/test","sa","");
		} catch (SQLException e) {  
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public void query(String sql) {
		query(sql, new Object[]{});
	}
	public void query(String sql, Object... args) {
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			for(int i=0; i<args.length; i++) pstmt.setString(i+1, args[i].toString());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public Array queryRows(String sql) {
		return queryRows(sql, new Object[]{});
	}
	public Array queryRows(String sql, Object... args) {
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			for(int i=0; i<args.length; i++) pstmt.setString(i+1, args[i].toString());
			ResultSet rs = pstmt.executeQuery();
			ResultSetMetaData md = rs.getMetaData();
			int columns = md.getColumnCount();
			Array rows = new Array();
			while(rs.next()){
				Array row = new Array();
				for(int i=1; i<=columns; ++i) row.put(md.getColumnName(i),rs.getObject(i).toString());
				rows.push(row);
			}
			return rows;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}

2 테스트 코드

		DBService db = DBService.getDB();
		db.query("DROP TABLE IF EXISTS TEST");
		db.query("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
		db.query("INSERT INTO TEST VALUES (?,?)", 1, "Hello");
		db.query("INSERT INTO TEST VALUES (?,?)", 2, "World");
		System.out.println(db.queryRows("SELECT * FROM TEST ORDER BY ID"));
		// [{ID=1, NAME=Hello}, {ID=2, NAME=World}]
		db.query("UPDATE TEST SET NAME=? WHERE ID=?", "Hi", 1);
		db.query("DELETE FROM TEST WHERE ID=?", 2);
		System.out.println(db.queryRows("SELECT * FROM TEST ORDER BY ID"));
		// [{ID=1, NAME=Hi}]

3 같이 보기

• Array.java
• Util.java
• H2 Console - Sample SQL Script‎