public HouseTable() {
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
con = L1DatabaseFactory.getInstance().getConnection();
pstm = con.prepareStatement("SELECT * FROM house ORDER BY house_id");
rs = pstm.executeQuery();
L1House house = null;
while (rs.next()) {
house = new L1House();
house.setHouseId(rs.getInt(1));
house.setHouseName(rs.getString(2));
house.setHouseArea(rs.getInt(3));
house.setLocation(rs.getString(4));
house.setKeeperId(rs.getInt(5));
house.setOnSale(rs.getInt(6) == 1 ? true : false);
house.setPurchaseBasement(rs.getInt(7) == 1 ? true : false);
house.setTaxDeadline(timestampToCalendar((Timestamp) rs.getObject(8)));
_house.put(house.getHouseId(), house);
}
} catch (SQLException e) {
_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
} finally {
SQLUtil.close(rs);
SQLUtil.close(pstm);
SQLUtil.close(con);
}
}
public static List<Integer> getHouseIdList() {
List<Integer> houseIdList = new ArrayList<Integer>();
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
con = L1DatabaseFactory.getInstance().getConnection();
pstm = con.prepareStatement("SELECT house_id FROM house ORDER BY house_id");
rs = pstm.executeQuery();
while (rs.next()) {
int houseId = rs.getInt("house_id");
houseIdList.add(Integer.valueOf(houseId));
}
} catch (SQLException e) {
_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
} finally {
SQLUtil.close(rs);
SQLUtil.close(pstm);
SQLUtil.close(con);
}
return houseIdList;
}
public void updateHouse(L1House house) {
Connection con = null;
PreparedStatement pstm = null;
try {
con = L1DatabaseFactory.getInstance().getConnection();
pstm =
con.prepareStatement(
"UPDATE house SET house_name=?, house_area=?, location=?, keeper_id=?, is_on_sale=?, is_purchase_basement=?, tax_deadline=? WHERE house_id=?");
pstm.setString(1, house.getHouseName());
pstm.setInt(2, house.getHouseArea());
pstm.setString(3, house.getLocation());
pstm.setInt(4, house.getKeeperId());
pstm.setInt(5, house.isOnSale() == true ? 1 : 0);
pstm.setInt(6, house.isPurchaseBasement() == true ? 1 : 0);
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String fm = formatter.format(house.getTaxDeadline().getTime());
pstm.setString(7, fm);
pstm.setInt(8, house.getHouseId());
pstm.execute();
} catch (SQLException e) {
_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
} finally {
SQLUtil.close(pstm);
SQLUtil.close(con);
}
}
private void load() {
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
con = L1DatabaseFactory.getInstance().getConnection();
pstm = con.prepareStatement("SELECT * FROM castle");
rs = pstm.executeQuery();
L1Castle castle = null;
while (rs.next()) {
castle = new L1Castle(rs.getInt(1), rs.getString(2));
castle.setWarTime(timestampToCalendar((Timestamp) rs.getObject(3)));
castle.setTaxRate(rs.getInt(4));
castle.setPublicMoney(rs.getInt(5));
castle.setPublicReadyMoney(rs.getInt(6));
castle.setShowMoney(rs.getInt(7));
castle.setWarBaseTime(rs.getInt(8));
castle.setCastleSecurity(rs.getInt(9));
// castle.setTimeBase(timestampToCalendar((Timestamp) rs
// .getObject(9)));
_castles.put(castle.getId(), castle);
}
} catch (SQLException e) {
_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
} finally {
SQLUtil.close(rs);
SQLUtil.close(pstm);
SQLUtil.close(con);
}
}
public void updateCastle(L1Castle castle) {
Connection con = null;
PreparedStatement pstm = null;
try {
con = L1DatabaseFactory.getInstance().getConnection();
pstm =
con.prepareStatement(
"UPDATE castle SET name=?, war_time=?, tax_rate=?, public_money=?, public_ready_money=?, show_money=?, war_basetime=?, security=? WHERE castle_id=?");
pstm.setString(1, castle.getName());
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
String fm = sdf.format(castle.getWarTime().getTime());
// String fm = DateFormat.getDateTimeInstance().format( //## A1 원본
// castle.getWarTime().getTime()); //#
pstm.setString(2, fm);
pstm.setInt(3, castle.getTaxRate());
pstm.setInt(4, castle.getPublicMoney());
pstm.setInt(5, castle.getPublicReadyMoney());
pstm.setInt(6, castle.getShowMoney());
pstm.setInt(7, castle.getWarBaseTime());
// String ft = sdf.format(castle.getTimeBase().getTime());
// pstm.setString(8, ft);
pstm.setInt(8, castle.getCastleSecurity());
pstm.setInt(9, castle.getId());
pstm.execute();
_castles.put(castle.getId(), castle);
} catch (SQLException e) {
_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
} finally {
SQLUtil.close(pstm);
SQLUtil.close(con);
}
}
private BuddyTable() {
Connection con = null;
PreparedStatement charIdPS = null;
ResultSet charIdRS = null;
try {
con = L1DatabaseFactory.getInstance().getConnection();
charIdPS = con.prepareStatement("SELECT distinct(char_id) as char_id FROM character_buddys");
charIdRS = charIdPS.executeQuery();
PreparedStatement buddysPS = null;
ResultSet buddysRS = null;
while (charIdRS.next()) {
try {
buddysPS =
con.prepareStatement(
"SELECT buddy_id, buddy_name FROM character_buddys WHERE char_id = ?");
int charId = charIdRS.getInt("char_id");
buddysPS.setInt(1, charId);
L1Buddy buddy = new L1Buddy(charId);
buddysRS = buddysPS.executeQuery();
while (buddysRS.next()) {
buddy.add(buddysRS.getInt("buddy_id"), buddysRS.getString("buddy_name"));
}
_buddys.put(buddy.getCharId(), buddy);
} catch (Exception e) {
_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
} finally {
SQLUtil.close(buddysRS);
SQLUtil.close(buddysPS);
}
}
_log.config("loaded " + _buddys.size() + " character's buddylists");
} catch (SQLException e) {
_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
} finally {
SQLUtil.close(charIdRS);
SQLUtil.close(charIdPS);
SQLUtil.close(con);
}
}
public void addBuddy(int charId, int objId, String name) {
Connection con = null;
PreparedStatement pstm = null;
try {
con = L1DatabaseFactory.getInstance().getConnection();
pstm =
con.prepareStatement(
"INSERT INTO character_buddys SET char_id=?, buddy_id=?, buddy_name=?");
pstm.setInt(1, charId);
pstm.setInt(2, objId);
pstm.setString(3, name);
pstm.execute();
} catch (SQLException e) {
_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
} finally {
SQLUtil.close(pstm);
SQLUtil.close(con);
}
}
public void removeBuddy(int charId, String buddyName) {
Connection con = null;
PreparedStatement pstm = null;
L1Buddy buddy = getBuddyTable(charId);
if (!buddy.containsName(buddyName)) {
return;
}
try {
con = L1DatabaseFactory.getInstance().getConnection();
pstm = con.prepareStatement("DELETE FROM character_buddys WHERE char_id=? AND buddy_name=?");
pstm.setInt(1, charId);
pstm.setString(2, buddyName);
pstm.execute();
buddy.remove(buddyName);
} catch (SQLException e) {
_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
} finally {
SQLUtil.close(pstm);
SQLUtil.close(con);
}
}
private void loadMapsFromDatabase() {
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
con = L1DatabaseFactory.getInstance().getConnection();
pstm = con.prepareStatement("SELECT * FROM resolvent");
for (rs = pstm.executeQuery(); rs.next(); ) {
int itemId = rs.getInt("item_id");
int crystalCount = rs.getInt("crystal_count");
_resolvent.put(new Integer(itemId), crystalCount);
}
_log.config("resolvent " + _resolvent.size());
} catch (SQLException e) {
_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
} finally {
SQLUtil.close(rs);
SQLUtil.close(pstm);
SQLUtil.close(con);
}
}