public static List<LabelValueBean> getAllSkill() throws SQLException {
List<LabelValueBean> list = new ArrayList<LabelValueBean>();
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = ConnectionManager.getConnection();
st = conn.createStatement();
rs = st.executeQuery("select * from skills");
while (rs.next()) {
String id = rs.getString("id");
String name = rs.getString("name");
LabelValueBean bean = new LabelValueBean(name, id);
list.add(bean);
}
} catch (SQLException e) {
throw e;
} finally {
close(rs);
close(st);
close(conn);
}
return list;
}
public static void updateSkill(String id, String skill) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
String addskill = "update staff_skill SET skid= ? where sid = ?";
conn = ConnectionManager.getConnection();
ps = conn.prepareStatement(addskill);
ps.setString(2, id);
ps.setString(1, skill);
ps.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
close(rs);
close(ps);
close(conn);
}
}
public static void addSkillsForUser(String id, String skill) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
String addskill = "insert into staff_skill (sid,skid) values(?,?)";
conn = ConnectionManager.getConnection();
ps = conn.prepareStatement(addskill);
ps.setString(1, id);
ps.setString(2, skill);
ps.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
close(rs);
close(ps);
close(conn);
}
}
public static String getUserSkill(String id) throws SQLException {
String skillID = null;
String addskill = "select * from staff_skill where sid = ?";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = ConnectionManager.getConnection();
ps = conn.prepareStatement(addskill);
ps.setString(1, id);
rs = ps.executeQuery();
while (rs.next()) {
skillID = rs.getString("skid");
}
} catch (SQLException e) {
throw e;
} finally {
close(rs);
close(ps);
close(conn);
}
return skillID;
}
public static Map<String, String> getSkillMap() throws SQLException {
Map<String, String> map = new LinkedHashMap<String, String>();
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = ConnectionManager.getConnection();
st = conn.createStatement();
rs = st.executeQuery("select * from skills order by name");
while (rs.next()) {
String id = rs.getString("id");
String name = rs.getString("name");
map.put(id, name);
}
} catch (SQLException e) {
throw e;
} finally {
close(rs);
close(st);
close(conn);
}
return map;
}