public void dispose() {
try {
stmt.close();
con.close();
} catch (SQLException e) {
}
}
public void connectItems(String query)
// PRE: query must be initialized
// POST: Updates the list of Items based on the query.
{
String driver = "org.apache.derby.jdbc.ClientDriver"; // Driver for DB
String url = "jdbc:derby://localhost:1527/ShopDataBase"; // Url for DB
String user = "******"; // Username for db
String pass = "******"; // Password for db
Connection myConnection; // Connection obj to db
Statement stmt; // Statement to execute a result appon
ResultSet results; // A set containing the returned results
int rowcount; // Num objects in the resultSet
int i; // Index for the array
try { // Try connection to db
Class.forName(driver).newInstance(); // Create our db driver
myConnection = DriverManager.getConnection(url, user, pass); // Initalize our connection
stmt =
myConnection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE); // Create a new statement
results = stmt.executeQuery(query); // Store the results of our query
rowcount = 0;
if (results.last()) // Go to the last result
{
rowcount = results.getRow();
results.beforeFirst();
}
itemsArray = new Item[rowcount];
i = 0;
while (results.next()) // Itterate through the results set
{
itemsArray[i] =
new Item(
results.getInt("item_id"),
results.getString("item_name"),
results.getString("item_type"),
results.getInt("price"),
results.getInt("owner_id"),
results.getString("item_path")); // Creat new Item
i++;
}
results.close(); // Close the ResultSet
stmt.close(); // Close the statement
myConnection.close(); // Close the connection to db
} catch (Exception e) // Cannot connect to db
{
System.err.println(e.toString());
System.err.println("Error, something went horribly wrong in connectItems!");
}
}
public void updateTables(String updateQuery1, String updateQuery2, String updateQuery3)
// PRE: updateQuery1,updateQuery2, updateQuery3 must be initialized
// POST: Updates the list of Items based on the querys.
{
String driver = "org.apache.derby.jdbc.ClientDriver"; // Driver for DB
String url = "jdbc:derby://localhost:1527/ShopDataBase"; // Url for DB
String user = "******"; // Username for db
String pass = "******"; // Password for db
Connection myConnection; // Connection obj to db
Statement stmt; // Statement to execute a result appon
ResultSet results; // A set containing the returned results
int i; // Index for the array
try // Try connection to db
{
Class.forName(driver).newInstance(); // Create our db driver
myConnection = DriverManager.getConnection(url, user, pass); // Initalize our connection
stmt =
myConnection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate(updateQuery1);
stmt.executeUpdate(updateQuery2);
stmt.executeUpdate(updateQuery3);
results = stmt.executeQuery(previous_query); // Call the previous query
i = 0;
while (results.next()) // Itterate through the results set
{
itemsArray[i] =
new Item(
results.getInt("item_id"),
results.getString("item_name"),
results.getString("item_type"),
results.getInt("price"),
results.getInt("owner_id"),
results.getString("item_path"));
i++;
}
results.close(); // Close the ResultSet
stmt.close(); // Close the statement
myConnection.close(); // Close the connection to db
} catch (Exception e) // Cannot connect to db
{
System.err.println(e.toString());
System.err.println("Error, something went horribly wrong! in updateTables");
}
}
public void closeDB() {
try {
if (statement != null) {
statement.close();
}
if (db != null) {
db.close();
}
} catch (Exception e) {
System.out.println("Database could not closed");
e.printStackTrace();
}
} // closeDB sonu
public void actionPerformed(ActionEvent ae) {
String str = ae.getActionCommand();
if (str.equals("Ok")) {
String str1 = (String) jcmname.getSelectedItem();
Connection con = null;
Statement stat = null;
if (str1.equals("Pulsar")
|| str1.equals("CT 100")
|| str1.equals("Discover DTS-i")
|| str1.equals("Wave DTS-i")) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("Jdbc:Odbc:showroom");
System.out.println("Got Connection :" + con);
stat = con.createStatement();
ResultSet rs = stat.executeQuery("select * from vehicle");
System.out.println("chk1");
while (rs.next()) {
if (str1.equals(rs.getString(1))) {
tfcap.setText("" + rs.getInt(2));
tfeng.setText("" + rs.getInt(3));
tfbhp.setText("" + rs.getInt(4));
tfvolt.setText("" + rs.getInt(5));
tfrpm.setText("" + rs.getInt(6));
tfweight.setText("" + rs.getInt(7));
tfgear.setText("" + rs.getInt(8));
}
}
stat.close();
con.close();
} catch (Exception ex) {
}
} else {
JOptionPane.showMessageDialog(
null, "Please Choose Model Name", "Error", JOptionPane.ERROR_MESSAGE);
}
}
if (str.equals("can")) {
this.dispose();
// new menu(1);
}
}
/**
* Fill the table using m_sql
*
* @param table table
*/
private static void tableLoad(MiniTable table) {
// log.finest(m_sql + " - " + m_groupBy);
String sql =
MRole.getDefault()
.addAccessSQL(m_sql.toString(), "tab", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO)
+ m_groupBy
+ m_orderBy;
log.finest(sql);
try {
Statement stmt = DB.createStatement();
ResultSet rs = stmt.executeQuery(sql);
table.loadTable(rs);
stmt.close();
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
}
} // tableLoad
public void login() {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String id, pw, sql;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, "hr", "hr");
stmt = con.createStatement();
sql = "select * from chatuser where id = '" + lid.getText() + "'";
rs = stmt.executeQuery(sql);
if (rs.next()) {
pw = rs.getString(2);
nick = rs.getString(3);
if (lpw.getText().equals(pw)) {
jd.setVisible(false);
setVisible(true);
} else {
lpw.setText("일치하지 않습니다.");
}
} else {
lid.setText("일치하지 않습니다.");
}
} catch (Exception e1) {
e1.printStackTrace();
System.out.println("데이터 베이스 연결 실패!!");
} finally {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (con != null) con.close();
} catch (Exception e1) {
System.out.println(e1.getMessage());
}
}
}
public void bookRoom() {
try {
int code = 0;
Connection con =
DriverManager.getConnection(
"jdbc:mysql://67.20.111.85:3306/jeehtove_caliking?relaxAutoCommit=false",
"jeehtove_ck",
"Z_^PBBZT+kcy");
Statement stmt_insert = con.createStatement();
Statement stmt_tableRange = con.createStatement();
Statement stmt_checkrooms = con.createStatement();
Statement stmt_checkrooms_2 = con.createStatement();
String insert = null;
int flag = 0;
String room_type;
if (standardRoom.isSelected()) {
room_type = "Standard";
code = 1;
} else if (familyRoom.isSelected()) {
room_type = "Family";
code = 2;
} else {
room_type = "Suite";
code = 3;
}
// Converting string from checkin and checkout dates so that they can access the correct table
String currentDate_checkin_getText = checkindateField.getText();
String currentDate_checkin_rest = currentDate_checkin_getText.substring(5);
String currentDate_checkin_replace = currentDate_checkin_rest.replace("-", "_");
String currentDate_checkin_year = currentDate_checkin.substring(0, 4);
String currentDate_checkin_final =
currentDate_checkin_year + "_" + currentDate_checkin_replace;
String currentDate_checkout_getText = checkoutdateField.getText();
String currentDate_checkout_rest = currentDate_checkout_getText.substring(5);
String currentDate_checkout_replace = currentDate_checkout_rest.replace("-", "_");
String currentDate_checkout_year = currentDate_checkout.substring(0, 4);
String currentDate_checkout_final =
currentDate_checkout_year + "_" + currentDate_checkout_replace;
// MySQL Statement to select tables from schema that are in between checkin and checkout range
String tableRange =
"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='jeehtove_caliking' AND TABLE_NAME BETWEEN 'rooms_"
+ currentDate_checkin_final
+ "' AND 'rooms_"
+ currentDate_checkout_final
+ "';";
ResultSet tb_Range = stmt_tableRange.executeQuery(tableRange);
System.out.println(tableRange);
List tb_range_list = new ArrayList();
while (tb_Range.next()) {
String tb_range_date = tb_Range.getString("table_name");
tb_range_list.add(tb_range_date);
// String current_table = tb_Range.getString("table_name");
// System.out.println(tb_range_list.add(tb_Range.getString("table_name")));
}
stmt_tableRange.close();
for (int x = 0; x < tb_range_list.size(); x++) {
String query_checkrooms =
"SELECT * FROM "
+ tb_range_list.get(x)
+ " WHERE type = '"
+ code
+ "' AND booked = '0' LIMIT 1;";
// System.out.println(query_checkrooms);
ResultSet checkrooms = stmt_checkrooms.executeQuery(query_checkrooms);
while (checkrooms.next()) {
String getroomnumber = checkrooms.getString("room");
insert =
"INSERT INTO `reservations` VALUES ('"
+ getroomnumber
+ "', '"
+ checkindateField.getText()
+ "', '"
+ checkoutdateField.getText()
+ "', '"
+ room_type
+ "', '"
+ nameField.getText()
+ "');";
String bookroom =
"UPDATE `"
+ tb_range_list.get(x)
+ "` SET booked = '1' WHERE room = '"
+ getroomnumber
+ "';";
// System.out.println(insert);
int cr = stmt_checkrooms_2.executeUpdate(bookroom);
flag = 1;
System.out.println("Room Booked!");
}
}
if (flag == 1) {
int rs = stmt_insert.executeUpdate(insert);
} else {
System.out.println("All " + room_type + " rooms are booked for the time requested.");
}
/*String query_checkrooms = "SELECT * FROM rooms_" + currentDate_checkin_final + " WHERE type = '" + code + "' AND booked = '0';";
System.out.println(query_checkrooms);
Statement stmt_checkrooms = con.createStatement();
ResultSet checkrooms = stmt_checkrooms.executeQuery(query_checkrooms);*/
/*if (checkrooms.next()){
String getroomnumber = checkrooms.getString("room");
String insert = "INSERT INTO `reservations` VALUES ('" + getroomnumber + "', '" + checkindateField.getText() + "', '" + checkoutdateField.getText() + "', '" + room_type + "', '" + nameField.getText() + "');";
String bookroom = "UPDATE `rooms_" + currentDate_checkin_final + "` SET booked = '1' WHERE room = '" + getroomnumber + "';";
//System.out.println(insert);
int rs=stmt_insert.executeUpdate(insert);
int cr=stmt_checkrooms.executeUpdate(bookroom);
con.commit();
System.out.println("Room Booked!");
}*/
// else System.out.println("All " + room_type + " rooms are booked for the time requested.");
System.out.println("Complete.");
} catch (Exception e) {
System.out.println(e);
}
}
