package hoge;

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

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

public class DBManager implements ServletContextListener {

private static String url = "jdbc:hsqldb:hsql://localhost";

public static Connection getConnection() {
try {
Class.forName("org.hsqldb.jdbcDriver");
Connection con = DriverManager.getConnection(url, "sa", "");
return con;
} catch (Exception e) {
throw new IllegalStateException(e);
}
}

public static CachedRowSet executeQuery(String sql) throws SQLException {

Connection con = getConnection();
Statement smt = con.createStatement();
ResultSet rs = smt.executeQuery(sql);

CachedRowSetImpl crs = new CachedRowSetImpl();
crs.populate(rs);
crs.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);//HSQLDBはこの分離レベルまでしかサポートしない

smt.close();
con.close();

return crs;
}

public static void acceptChanges(CachedRowSet crs) throws SQLException {

Connection con = getConnection();
crs.acceptChanges(con);
con.close();

}

public void contextInitialized(ServletContextEvent event) {
//サーバーモードでHSQLDBを起動する場合は以下をコメントアウトしてください。
url = "jdbc:hsqldb:"
+ event.getServletContext().getRealPath("/WEB-INF/db/mydb");
}

public void contextDestroyed(ServletContextEvent event) {

}

//実行テスト
public static void main(String[] args) throws Exception {

CachedRowSet crs = executeQuery("select * from video");

crs.next();//最初の行
System.out.println(crs.getString("title") + "," + crs.getInt("price"));
crs.updateInt(2, 200);//2つ目のカラム
crs.updateRow();

acceptChanges(crs);

System.out.println("END");
}
}