Форум программистов [Powered by Invision Power Board]

Код


package students.logic;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import javax.swing.JSpinner;

public class ManagementSystem
{
  private static Connection con; 
  private static ManagementSystem instance;

  private ManagementSystem() {
    try {
      Class.forName("org.postgresql.Driver");
      String url = "jdbc:postgresql://localhost:5432/stol";
      con = DriverManager.getConnection(url,"postgres", "syrus");
    } catch (ClassNotFoundException e1) {
      e1.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static synchronized ManagementSystem getInstance()
  {
    if (instance == null) {
      instance = new ManagementSystem();
    }
    return instance;
  }

  public List getGroups() throws SQLException
  {
    List groups = new ArrayList();
    
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT idn, code, descr, ceh FROM personal");
    while(rs.next()) {
//      Group gr = new Group();
//      gr.setGroupId(rs.getInt(1));
//      gr.setNameGroup(rs.getString(2));
//      gr.setCurator(rs.getString(3));
//      gr.setSpeciality(rs.getString(4));

//      groups.add(gr);
    }
    rs.close();
    stmt.close();
    
    return groups;
  }
   
  public Collection getPers(String y) throws SQLException 
  {
    Collection students = new ArrayList();
try {
    PreparedStatement stmt = con.prepareStatement(
        "SELECT idn, code,descr,ceh "+
        " FROM personal " +
        "WHERE code=?");
//    stmt.setInt(1, group.getGroupId());
    stmt.setString(1, y);
    ResultSet rs = stmt.executeQuery();
    while(rs.next()) {
      Student st = new Student(rs);
    
      students.add(st);
      rs.close();
      stmt.close();
    }
}
    catch(NullPointerException e){
     e.printStackTrace();
    }
    return students;
  }
  
  
  
  public Collection getAllStudents() throws SQLException
  {
    Collection students = new ArrayList();

    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(
        "SELECT id, code_pers,code_bl,kol,suma "+
        " FROM obedi ORDER BY id");
    while(rs.next()) {
      Student st = new Student(rs);
      students.add(st);
    }
    rs.close();
    stmt.close();

    return students;
  }

  public Collection getStudentsFromGroup(String y) throws SQLException 
  {
    Collection students = new ArrayList();

    PreparedStatement stmt = con.prepareStatement(
        "SELECT id, code_pers,code_bl,kol,suma, "+
        " FROM obedi " +
        "WHERE code_pers=? "+
        "ORDER BY date,id");
    stmt.setString(1, y);
//    stmt.setInt(2, year);
    ResultSet rs = stmt.executeQuery();
    while(rs.next()) {
      Student st = new Student(rs);
    
      students.add(st);
    }
    rs.close();
    stmt.close();
    
    return students;
  }

//  public void moveStudentsToGroup(Group oldGroup, int oldYear, Group newGroup, int newYear) throws SQLException
//  {
//    PreparedStatement stmt = con.prepareStatement(
//        "UPDATE obedi SET code_pers=?" +
//        "WHERE code_pers=? ");
//    stmt.setInt(1, newGroup.getGroupId());
//    stmt.setInt(2, newYear);
//    stmt.setInt(3, oldGroup.getGroupId());
//    stmt.setInt(4, oldYear);
//    stmt.execute();
//  }
    
  public void removeStudentsFromGroup(String y) throws SQLException
  {
    PreparedStatement stmt = con.prepareStatement(
        "DELETE FROM obedi WHERE code_pers=?");
    stmt.setString(1, y);
//    stmt.setInt(2, year);
    stmt.execute();
  }

  public void insertStudent(Student student,String y) throws SQLException
  {
    PreparedStatement stmt = con.prepareStatement(
        "INSERT INTO obedi "+
        "(date, code_pers, code_bl, kol) " +
        "VALUES (DATE(), ?, ?, 1)");
//    stmt.setString(1, student.getFirstName());
    stmt.setString(2, y);
    stmt.setString(3, student.getSurName());
//    stmt.setString(4, new String(new char[] {student.getSex()} ));
//    stmt.setDate(5, new Date(student.getDateOfBirth().getTime()));
//    stmt.setInt(6, student.getGroupId());
//    stmt.setInt(7, student.getEducationYear());

    stmt.execute();
  }
    
  public void updateStudent(Student student) throws SQLException
  {
//    PreparedStatement stmt = con.prepareStatement(
//        "UPDATE students SET "+
//        "firstName=?, patronymic=?, surName=?, "+
//        "sex=?, dateOfBirth=?, group_id=?, educationYear=? " +
//        "WHERE student_id=?");
//    stmt.setString(1, student.getFirstName());
//    stmt.setString(2, student.getPatronymic());
//    stmt.setString(3, student.getSurName()); 
  }
  public void deleteStudent(Student student) throws SQLException

  {

//    PreparedStatement stmt = con.prepareStatement(

//      "DELETE FROM students WHERE student_id=?");

//    stmt.setInt(1, student.getStudentId());

//    stmt.execute();

  }


}