package dao; import com.sun.rowset.CachedRowSetImpl; import java.math.BigDecimal; 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.Collection; import java.util.HashMap; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; import javax.sql.rowset.CachedRowSet; import javax.swing.table.TableModel; import model.Criterion; import model.CriterionEditor; import model.Student; import ui.CriterionPanel; /** * * @author mark */ public class ResultDAO { private static final Logger logger = Logger.getLogger(ResultDAO.class.getName()); private static Map<String, CriterionEditor> criteria = new HashMap<>(); public void addEditor(Criterion criterion, CriterionPanel criterionEditor) { criteria.put(criterion.getCriterion(), criterionEditor); } public Boolean isComplete() { for (CriterionEditor editor : criteria.values()) { if (!editor.isComplete()) { return Boolean.FALSE; } } return Boolean.TRUE; } public void save(String student) { try { try ( Connection con = JdbcConnection.getConnection(); PreparedStatement stmt = con.prepareStatement("merge into result (student, criterion, result, comment) values (?,?,?,?)")) { for (Map.Entry<String, CriterionEditor> entry : criteria.entrySet()) { CriterionEditor editor = entry.getValue(); if (!editor.isComplete()) { continue; } String criterion = entry.getKey(); Double result = editor.getResult(); String comment = editor.getComment(); stmt.setString(1, student); stmt.setString(2, criterion); stmt.setDouble(3, result); stmt.setString(4, comment); stmt.addBatch(); } stmt.executeBatch(); clear(); } } catch (SQLException e) { logger.log(Level.SEVERE, e.getLocalizedMessage(), e); } } public void load(String student) { try { try ( Connection con = JdbcConnection.getConnection(); PreparedStatement stmt = con.prepareStatement("select * from result where student = ?")) { stmt.setString(1, student); ResultSet rs = stmt.executeQuery(); while (rs.next()) { String criterion = rs.getString("criterion"); Double result = rs.getDouble("result"); String comment = rs.getString("comment"); CriterionEditor editor = criteria.get(criterion); editor.setResult(result); editor.setComment(comment); } } } catch (SQLException e) { logger.log(Level.SEVERE, e.getLocalizedMessage(), e); } } public TableModel getSubmissions() { try ( Connection con = JdbcConnection.getConnection(); Statement stmt = con.createStatement()) { ResultSet rs = stmt.executeQuery("select * from allmarks order by \"Student\""); CachedRowSet crs = new CachedRowSetImpl(); crs.populate(rs); return new RowSetTableModel(crs); } catch (SQLException ex) { logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex); } return null; } public Collection<Map<String, ?>> getResult(String student) { String sql = "SELECT * FROM report where usercode = ?"; Collection<Map<String, ?>> data = new ArrayList<>(); try ( Connection con = JdbcConnection.getConnection(); PreparedStatement s = con.prepareStatement(sql);) { s.setString(1, student); ResultSet rs = s.executeQuery(); while (rs.next()) { Map<String, Object> result = new HashMap<>(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); for (int i = 1; i <= columnCount; i++) { String columnName = metaData.getColumnName(i); Object value = rs.getObject(columnName); if (value instanceof Number) { value = new BigDecimal(String.valueOf(value)); } result.put(columnName, value); } data.add(result); } return data; } catch (SQLException ex) { logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex); return null; } } public Student findStudentById(Integer id) { try ( Connection con = JdbcConnection.getConnection(); PreparedStatement stmt = con.prepareStatement("select * from students where studentid = ?")) { stmt.setInt(1, id); ResultSet rs = stmt.executeQuery(); if (rs.next()) { Student s = new Student(); s.setId(id); s.setName(rs.getString("name")); s.setUserName(rs.getString("usercode")); s.setEmail(rs.getString("email")); return s; } } catch (SQLException ex) { logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex); } return null; } public Student findStudentByUserCode(String userCode) { try ( Connection con = JdbcConnection.getConnection(); PreparedStatement stmt = con.prepareStatement("select * from students where usercode = ?")) { stmt.setString(1, userCode); ResultSet rs = stmt.executeQuery(); if (rs.next()) { Student s = new Student(); s.setId(rs.getInt("studentid")); s.setUserName(rs.getString("usercode")); s.setName(rs.getString("name")); s.setEmail(rs.getString("email")); return s; } } catch (SQLException ex) { logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex); } return null; } private void clear() { criteria = new HashMap<>(); } }