/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package club.data;
import club.beans.MembershipTypeBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
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;
/**
*
* @author sior
*/
public class DataMembership {
private static Connection conn;
private static Statement stmt;
private static PreparedStatement psmt;
private static ResultSet rs;
public static List<MembershipTypeBean> getAllTypes() { // get all record in table MembershipType and return in a List
List<MembershipTypeBean> list = new ArrayList();
String query = "SELECT * FROM MembershipType";
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
MembershipTypeBean membership = new MembershipTypeBean();
membership.setMembershipTypeID(rs.getInt("MembershipTypeID"));
membership.setTypeName(rs.getNString("TypeName"));
membership.setFee(rs.getDouble("Fee"));
membership.setMin_Attendance(rs.getInt("Min_Attendance"));
membership.setMain_Duty(rs.getNString("Main_Duty"));
membership.setDiscount(rs.getInt("Discount"));
list.add(membership);
}
} 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 DefaultTableModel getAllTypesAsModel() { // get all records in table MembershipType and return as model
DefaultTableModel model = null;
String query = "SELECT * FROM MembershipType";
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);
} 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 MembershipTypeBean getTypeByID(int id) {
MembershipTypeBean bean = null;
String query = "SELECT * FROM MembershipType"
+ " WHERE MembershipTypeID=" + id;
try {
conn = ConnectDB.connectMSDB();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
bean = new MembershipTypeBean();
bean.setMembershipTypeID(rs.getInt("MembershipTypeID"));
bean.setTypeName(rs.getNString("TypeName"));
bean.setFee(rs.getDouble("Fee"));
bean.setMin_Attendance(rs.getInt("Min_Attendance"));
bean.setMain_Duty(rs.getNString("Main_Duty"));
bean.setDiscount(rs.getInt("Discount"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException ex) {
Logger.getLogger(DataMember.class.getName()).log(Level.SEVERE, null, ex);
}
}
return bean;
}
public static boolean insertNewType(String typeName,double fee,int minAttend,String mainDuty,int discount){
boolean flag = false;
String query = "INSERT INTO MembershipType(TypeName,Fee,Min_Attendance,Main_Duty,Discount)"
+ " VALUES(?,?,?,?,?)";
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setNString(1, typeName);
psmt.setDouble(2, fee);
psmt.setInt(3, minAttend);
psmt.setNString(4, mainDuty);
psmt.setInt(5, discount);
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(DataMembership.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public static boolean removeTypeByID(int id){
boolean flag = false;
String query = "DELETE FROM MembershipType WHERE MembershipTypeID="+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(DataMembership.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
public static boolean updateTypeByID(int id,String typeName,double fee,int minAttend,String mainDuty,int discount){
boolean flag = false;
String query = "UPDATE MembershipType"
+ " SET TypeName=?,"
+ " Fee=?,"
+ " Min_Attendance=?,"
+ " Main_Duty=?,"
+ " Discount=?"
+ " WHERE MembershipTypeID="+id;
try {
conn = ConnectDB.connectMSDB();
psmt = conn.prepareStatement(query);
psmt.setNString(1, typeName);
psmt.setDouble(2, fee);
psmt.setInt(3, minAttend);
psmt.setNString(4, mainDuty);
psmt.setInt(5, discount);
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(DataMembership.class.getName()).log(Level.SEVERE, null, ex);
}
}
return flag;
}
}