/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package club.data;
import club.beans.EventBean;
import club.beans.EventTypeBean;
import club.beans.MemberBean;
import club.ulti.FormatConverter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.DefaultTableModel;
import javax.xml.crypto.Data;
/**
*
* @author Quang
*/
public class DataEventType {
private static Connection conn;
private static Statement stmt;
private static PreparedStatement psmt;
private static ResultSet rs;
public List<EventTypeBean> getAllEventTypes(String text) { // get all record in table event type and return in a List
List<EventTypeBean> list = new ArrayList();
String query = "SELECT * FROM EventType WHERE Type LIKE '%" + text + "%' ORDER BY EventType DESC";
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
EventTypeBean item = new EventTypeBean();
item.setEventType(rs.getInt("EventType"));
item.setType(rs.getNString("Type"));
list.add(item);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return list;
}
public EventTypeBean getEventTypeByID(int id) { // find a member by ID and return as Bean
EventTypeBean bean = new EventTypeBean();
String query = "SELECT * FROM EventType WHERE EventType=" + id;
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
bean.setEventType(rs.getInt("EventType"));
bean.setType(rs.getNString("Type"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataEventType.class.getName()).log(Level.SEVERE, null, ex);
}
}
return bean;
}
public boolean insertEventType(String type) {
boolean flag = false;
String query = "INSERT INTO EventType(Type) VALUES (?)";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setString(1, type);
int i = psmt.executeUpdate();
conn.commit();
if (i == 1) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataEventType.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public DefaultTableModel getAllTypesAsModel() { // get all records in table MembershipType and return as model
DefaultTableModel model = null;
String query = "SELECT * FROM EventType";
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
ResultSetMetaData meta = rs.getMetaData();
int count = meta.getColumnCount();
Vector header = new Vector();
Vector data = new Vector();
for (int i = 1; i <= count; i++) {
header.add(meta.getColumnName(i));
}
while (rs.next()) {
Vector row = new Vector();
for (int i = 1; i <= count; i++) {
row.add(rs.getObject(i));
}
data.add(row);
}
model = new DefaultTableModel(data, header){
@Override
public boolean isCellEditable(int row, int column) {
return false;
}
};
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return model;
}
public static boolean updateTypeByID(int id,String typeName){
boolean flag = false;
String query = "UPDATE EventType"
+ " SET Type=?"
+ " WHERE EventType = ?";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setString(1, typeName);
psmt.setInt(2, id);
int i = psmt.executeUpdate();
conn.commit();
if(i==1)
flag = true;
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataEventType.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public static boolean isRemovable(int id) { // return true if success , else false
boolean flag = false;
String query = "SELECT COUNT(*) as total FROM Event WHERE EventType =" + id;
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
rs.next();
if (rs.getInt("total") == 0){
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataEventType.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public static boolean removeTypeByID(int id) { // return true if success , else false
boolean flag = false;
String query = "DELETE FROM EventType WHERE EventType=" + id;
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
int i = stmt.executeUpdate(query);
if (i == 1) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataEventType.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
}