Ejemplo n.º 1
0
  public List<Funcionario> list() {
    String sql = "SELECT * FROM FUNCIONARIO";
    Funcionario funcionario = null;

    try {
      PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);
      ResultSet rs = stmt.executeQuery();
      List<Funcionario> funcionarios = new ArrayList<Funcionario>();

      while (rs.next()) {
        funcionario = new Funcionario();
        funcionario.setNumFuncionario(rs.getInt("id_funcionario"));
        funcionario.setNomeFuncionario(rs.getString("nome_funcionario"));
        funcionario.setComissao(rs.getDouble("comissao"));
        funcionario.setEspecialidade(rs.getString("especialidade"));

        funcionarios.add(funcionario);
      }

      stmt.close();
      rs.close();

      return funcionarios;
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
  /**
   * Cancel a reservation
   *
   * @param email
   * @param d
   * @return
   */
  public boolean cancelReservation(String email, Date d) {
    boolean success = false;

    int cid = getCID(email);

    if (cid != -1) {
      String queryCancelReservation = "DELETE FROM Reservation WHERE cID=? AND reservationDate=?";
      try {
        PreparedStatement statement =
            (PreparedStatement) connection.prepareStatement(queryCancelReservation);
        statement.setInt(1, cid);
        statement.setDate(2, d);
        statement.execute();
        statement.close();
        success = true;

      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        success = false;
        System.out.println("Failed: " + e.getMessage());
      } finally {

        return success;
      }
    } else {
      return false;
    }
  }
Ejemplo n.º 3
0
  // to implement
  public void add(Funcionario funcionario) {
    String sql =
        "INSERT INTO Funcionario (nome_funcionario, especialidade, comissao) VALUES (?, ?, ?)";
    // String sqlNumFunc = "SELECT id_funcionario FROM Funcionario WHERE nome_funcionario = '?'";

    try {
      PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);

      stmt.setString(1, funcionario.getNomeFuncionario());
      stmt.setString(2, funcionario.getEspecialidade());
      stmt.setDouble(3, funcionario.getComissao());

      stmt.execute();

      // stmt = (PreparedStatement) connection.prepareStatement(sqlNumFunc);
      // stmt.execute();

      // ResultSet rs = stmt.executeQuery();
      // funcionario.setNumFuncionario(rs.);

      stmt.close();

      System.out.println("Funcionario Inserted Successfully");
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
  /**
   * Reserve a table
   *
   * @param partySize number of people in a party
   * @param d reservation date
   * @param tID table id
   * @param c a customer
   * @return true if succeed, false otherwise
   */
  public boolean reserveTable(int partySize, Date d, int tID, Customer c) {
    PreparedStatement statement = null;
    int customerid = getCID(c.getEmail());
    String sql_reserve =
        "INSERT INTO Restaurant.Reservation (reservationDate,partySize,cID,tID) values(?, ?, ?,?)";

    try {
      connection.setAutoCommit(false);
      statement = (PreparedStatement) connection.prepareStatement(sql_reserve);
      statement.setDate(1, d);
      statement.setInt(2, partySize);
      statement.setInt(3, customerid);
      statement.setInt(4, tID);
      statement.executeUpdate();
      connection.commit();

      if (statement != null) {
        statement.close();
      }

      connection.setAutoCommit(true);

      return true;

    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      System.out.println("Failed: " + e.getMessage());
      return false;
    }
  }
  /**
   * Change a reservation
   *
   * @param email current user's email
   * @param date date to change
   * @param partySize number of guests to change
   * @return true if success, false otherwise
   * @throws SQLException
   */
  public boolean changeReservation(String email, Date date, int partySize) throws SQLException {
    boolean success = false;
    PreparedStatement updateReservation = null;
    int customerID = getCID(email);
    String query = "update reservation set reservationDate = ?, partysize = ? where cid = ?";

    try {
      connection.setAutoCommit(false);
      updateReservation = (PreparedStatement) connection.prepareStatement(query);
      updateReservation.setDate(1, date);
      updateReservation.setInt(2, partySize);
      updateReservation.setInt(3, customerID);
      updateReservation.execute();

      connection.commit();
      success = true;

    } catch (SQLException e) {
      e.printStackTrace();
      success = false;
    } finally {
      if (updateReservation != null) {
        updateReservation.close();
      }

      connection.setAutoCommit(true);
      return success;
    }
  }
  /**
   * Get a customer by id
   *
   * @param id customer id
   * @return a customer
   */
  public Customer getACustomer(int id) {
    String sql = "Select * From Customer where cid = ?";

    try {

      PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
      statement.setInt(1, id);
      ResultSet rs = statement.executeQuery();

      if (rs.next()) {
        Customer customer = new Customer();
        customer.setEmail(rs.getString("email"));
        customer.setFirstName(rs.getString("firstName"));
        customer.setLastName(rs.getString("lastName"));
        customer.setId(rs.getInt("cid"));

        if (statement != null) statement.close();

        return customer;
      } else {
        return null;
      }

    } catch (SQLException e) {
      e.printStackTrace();
      System.out.println("Error at get a customer");
      return null;
    }
  }
  /**
   * Rate a restaurant and give a feedback Rate uses SQL INSERT INTO
   *
   * @param stars number of stars
   * @param feedback user's feedback
   * @param customer a customer
   * @return true if rate works fine, false otherwise
   * @throws SQLException
   */
  public boolean rate(int stars, String feedback, Customer customer) throws SQLException {
    boolean success = false;
    PreparedStatement insertStatement = null;
    int customerID = getCID(customer.getEmail());
    String query =
        "INSERT INTO Rating (cid, stars, feedback) VALUES (?,?,?) "
            + "ON DUPLICATE KEY UPDATE stars=?, feedback=?";

    try {
      connection.setAutoCommit(false);
      insertStatement = (PreparedStatement) connection.prepareStatement(query);
      insertStatement.setInt(1, customerID);
      insertStatement.setInt(2, stars);
      insertStatement.setString(3, feedback);
      insertStatement.setInt(4, stars);
      insertStatement.setString(5, feedback);
      insertStatement.execute();

      connection.commit();
      success = true;

    } catch (SQLException e) {
      e.printStackTrace();
      success = false;
    } finally {
      if (insertStatement != null) {
        insertStatement.close();
      }

      connection.setAutoCommit(true);
      return success;
    }
  }
Ejemplo n.º 8
0
  /**
   * Checks if the databasePassword is the same as the provided password.
   *
   * @param login
   * @param password
   * @return true / false
   * @throws InstantiationException
   * @throws IllegalAccessException
   * @throws ClassNotFoundException
   * @throws SQLException
   */
  public static boolean checkPassword(String login, String password)
      throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {

    String sql = "SELECT password FROM users WHERE login = ? ";
    String serverPassword = "";
    Boolean isValid = false;

    Connection connection = DataBaseUtils.getMySQLConnection();
    PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql);

    ps.setString(1, login);

    ResultSet rs = ps.executeQuery();

    if (rs.next()) {

      serverPassword = rs.getString("password");

      if (password.equals(serverPassword)) {
        isValid = true;
      }
    }

    ps.close();
    connection.close();

    return isValid;
  }
Ejemplo n.º 9
0
  /**
   * Inserts a new entry in the `session` table.
   *
   * @param id I
   * @param root
   * @return A unique key associated with the user
   * @throws InstantiationException
   * @throws IllegalAccessException
   * @throws ClassNotFoundException
   * @throws SQLException
   */
  public static String insertSession(int id, boolean root)
      throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {

    /* SESSION_HOUR_DURATIONS starting from now */
    Timestamp expires =
        new Timestamp(System.currentTimeMillis() + SESSION_HOUR_DURATION * 60 * 60 * 1000);

    String key = generateKey();

    String sql =
        "INSERT INTO `session` " + "(`key`, `user_id`, `expires`, `root`)" + " VALUE(?,?,?,?)";

    Connection connection = DataBaseUtils.getMySQLConnection();
    PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql);

    ps.setString(1, key);
    ps.setInt(2, id);
    ps.setTimestamp(3, expires);
    ps.setBoolean(4, root);

    ps.executeUpdate();

    System.out.println("Session inserted for id : " + id);

    ps.close();
    connection.close();

    return key;
  }
Ejemplo n.º 10
0
 public JasperPrint relatorioModalidade(String modalidade) throws Exception {
   java.sql.Connection con = ConexaoDB.getInstance().getCon();
   String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioModalidade.jasper";
   URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
   modalidade = (new StringBuilder()).append("= '").append(modalidade).append("'").toString();
   String query =
       (new StringBuilder())
           .append(
               "SELECT p.nome,a.matricula,m.nome "
                   + "AS modalidade FROM pessoas p INNER JOIN alunos a ON p.idPessoa = a.idPessoa"
                   + " INNER JOIN adesoes ad ON a.matricula = ad.matricula INNER JOIN planos pl on "
                   + "ad.codPlano = pl.`codPlano` INNER JOIN modalidades m ON pl.codModalidade = "
                   + "m.codModalidade WHERE m.nome ")
           .append(modalidade)
           .append(" ")
           .append(" ORDER BY p.nome")
           .toString();
   PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
   ResultSet rs = stmt.executeQuery();
   JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
   java.util.Map parameters = new HashMap();
   JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
   rs.close();
   stmt.close();
   return rel;
 }
Ejemplo n.º 11
0
  public static void dbUpdate(Creative creative) throws Exception {

    Connection conn = null;
    PreparedStatement statement = null;

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();

      conn = DriverManager.getConnection("database_url", "username", "password");

      String query =
          "update creative set creativeID = ? , "
              + "creativeTimeStamp = ? "
              + "where creativeName = ?";
      statement = (PreparedStatement) conn.prepareStatement(query);
      statement.setString(1, creative.getCreativeID());
      statement.setString(2, creative.getCreativeTimestamp());
      statement.setString(3, creative.getName());
      statement.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      statement.close();
      conn.close();
    }
  }
Ejemplo n.º 12
0
 @Override
 public boolean guardar() {
   boolean ret = false;
   try {
     String sql = "UPDATE expulsiones SET ano=?,alumno_id=?,fecha=?,dias=? WHERE id=?";
     if (getId() == null) {
       sql = "INSERT INTO expulsiones (ano,alumno_id,fecha,dias,id) VALUES(?,?,?,?,?)";
     }
     PreparedStatement st =
         (PreparedStatement)
             MaimonidesApp.getApplication()
                 .getConector()
                 .getConexion()
                 .prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
     st.setInt(1, getAnoEscolar().getId());
     st.setInt(2, getAlumno().getId());
     st.setDate(3, new java.sql.Date(getFecha().getTimeInMillis()));
     st.setInt(4, getDias());
     st.setObject(5, getId());
     ret = st.executeUpdate() > 0;
     if (ret && getId() == null) {
       setId((int) st.getLastInsertID());
     }
     st.close();
     ret = true;
   } catch (SQLException ex) {
     Logger.getLogger(Conducta.class.getName())
         .log(Level.SEVERE, "Error guardando datos de expulsion: " + this, ex);
   }
   return ret;
 }
  /**
   * retrieve food items from database
   *
   * @param type beverage or food , or null for combination
   * @return menu - list of items
   */
  public ArrayList<Item> getMenu(String type) {
    String sql = "SELECT itemName, price, description FROM Menu";
    if (type != null) {
      sql += " WHERE type=?";
    }
    try {
      PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
      if (type != null) {
        statement.setString(0, type);
      }
      ResultSet rs = statement.executeQuery();
      ArrayList<Item> menu = new ArrayList<Item>(); // maybe food menu if there is beverage menu
      while (rs.next()) {
        String itemName = rs.getString("itemName");
        double price = rs.getDouble("price");
        String desc = rs.getString("description");
        Item i = new Item(itemName, price, desc);
        menu.add(i);
      }
      rs.close();
      statement.close();
      return menu;

    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      return null;
    }
  }
Ejemplo n.º 14
0
 public void deleteDokter(String idDokter) throws SQLException {
   try {
     PreparedStatement ps = (PreparedStatement) connection.prepareStatement(deleteDokter);
     ps.setString(1, idDokter);
     ps.executeUpdate();
     ps.close();
     //  JOptionPane.showMessageDialog(null, "Data dokter berhasil dihapus!");
   } catch (SQLException se) {
     //  JOptionPane.showMessageDialog(null, se.getMessage(),"Delete Dokter
     // Gagal!",JOptionPane.ERROR_MESSAGE);
   }
 }
Ejemplo n.º 15
0
 public JasperPrint relatorioPlanosVencidos() throws Exception {
   java.sql.Connection con = ConexaoDB.getInstance().getCon();
   String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioPlanosVencidos.jasper";
   URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
   String query = "";
   PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
   ResultSet rs = stmt.executeQuery();
   JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
   java.util.Map parameters = new HashMap();
   JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
   rs.close();
   stmt.close();
   return rel;
 }
Ejemplo n.º 16
0
 public void updateDokter(Dokter d, String idDokter) throws SQLException {
   try {
     PreparedStatement ps = (PreparedStatement) connection.prepareStatement(updateDokter);
     ps.setString(1, d.getNmDokter());
     ps.setString(2, d.getIdSpesialis());
     ps.setString(3, idDokter);
     ps.executeUpdate();
     ps.close();
     //     JOptionPane.showMessageDialog(null, "Data dokter berhasil diubah!");
   } catch (SQLException se) {
     //    JOptionPane.showMessageDialog(null, se.getMessage(),"Update Dokter
     // Gagal!",JOptionPane.ERROR_MESSAGE);
   }
 }
Ejemplo n.º 17
0
 public JasperPrint relatorioAniversariantes(int mes) throws Exception {
   java.sql.Connection con = ConexaoDB.getInstance().getCon();
   String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioAniversariantes.jasper";
   URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
   String query =
       "SELECT a.matricula,p.nome,p.nascimento,p.celular,(year(curdate()) - year(nascimento)) as anos FROM pessoas p inner join alunos a on p.idPessoa = a.idPessoa WHERE month(p.nascimento) = "
           + mes;
   PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
   ResultSet rs = stmt.executeQuery();
   JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
   java.util.Map parameters = new HashMap();
   JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
   rs.close();
   stmt.close();
   return rel;
 }
Ejemplo n.º 18
0
  /**
   * Deletes the user from the data-base.
   *
   * @param id user id
   * @return true | false
   * @throws SQLException
   * @throws ClassNotFoundException
   * @throws IllegalAccessException
   * @throws InstantiationException
   */
  public static void removeUserFromDataBase(int id)
      throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {

    String sql = "DELETE FROM users WHERE `id`=?";

    Connection connection = DataBaseUtils.getMySQLConnection();
    PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql);

    ps.setInt(1, id);
    ps.executeUpdate();

    System.out.println("User removed for id : " + id);

    ps.close();
    connection.close();
  }
Ejemplo n.º 19
0
  /**
   * Removes the entry associated with id, from the `sessions` table.
   *
   * @param id user id
   * @throws InstantiationException
   * @throws IllegalAccessException
   * @throws ClassNotFoundException
   * @throws SQLException
   */
  public static void removeSession(String key)
      throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {

    String sql = "DELETE FROM session WHERE `key`=?";

    Connection connection = DataBaseUtils.getMySQLConnection();
    PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql);

    ps.setString(1, key);
    ps.executeUpdate();

    System.out.println("Session removed for key : " + key);

    ps.close();
    connection.close();
  }
Ejemplo n.º 20
0
 public void freeConnection(Connection con, PreparedStatement pstmt) {
   if (con != null) {
     try {
       con.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
   if (pstmt != null) {
     try {
       pstmt.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
   }
 }
 /**
  * Add a customer
  *
  * @param ln last name
  * @param fn fist name
  * @param email email
  * @return true if succeed, false otherwise
  */
 public boolean addCustomer(String ln, String fn, String email) {
   String sql_addcustomer = "INSERT INTO CUSTOMER (email,lastname,firstname) VALUES (?,?,?)";
   try {
     PreparedStatement addCustomer =
         (PreparedStatement) connection.prepareStatement(sql_addcustomer);
     addCustomer.setString(1, email);
     addCustomer.setString(2, ln);
     addCustomer.setString(3, fn);
     addCustomer.execute();
     addCustomer.close();
     return true;
   } catch (SQLException e) {
     e.printStackTrace();
     System.out.println(e.getMessage());
     return false;
   }
 }
Ejemplo n.º 22
0
  public void remove(Funcionario funcionario) {
    String sql = "DELETE FROM Funcionario WHERE id_funcionario = '?'";
    // String sql = "DELETE FROM Funcionario WHERE nome_funcionario = ?";

    try {
      PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);

      stmt.setString(1, funcionario.getNomeFuncionario());

      stmt.execute();
      stmt.close();

      System.out.println(
          "Funcionario: " + funcionario.getNomeFuncionario() + " deleted successfully");
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
Ejemplo n.º 23
0
 public JasperPrint relatorioIdade(String idadeInicial, String idadeFinal) throws Exception {
   java.sql.Connection con = ConexaoDB.getInstance().getCon();
   String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioFaixaEtaria.jasper";
   URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
   String query =
       "SELECT p.nome,a.matricula,curdate()as data_atual,p.nascimento,(year(curdate()) - year(p.nascimento)) idade FROM pessoas p INNER JOIN alunos a ON p.idPessoa = a.idPessoa WHERE (year(curdate()) - year(p.nascimento)) >="
           + idadeInicial
           + " AND (year(curdate()) - year(p.nascimento)) <= "
           + idadeFinal;
   PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
   ResultSet rs = stmt.executeQuery();
   JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
   java.util.Map parameters = new HashMap();
   JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
   rs.close();
   stmt.close();
   return rel;
 }
Ejemplo n.º 24
0
 public String tampilIdByNama(String nama) throws SQLException {
   try {
     String id = null;
     PreparedStatement ps = (PreparedStatement) connection.prepareStatement(tampilIdByNama);
     ps.setString(1, nama);
     ResultSet rs = ps.executeQuery();
     while (rs.next()) {
       id = rs.getString("id_dokter");
     }
     rs.close();
     ps.close();
     return id;
   } catch (SQLException se) {
     JOptionPane.showMessageDialog(
         null, se.getMessage(), "Tampil ID By Nama Dokter Gagal!", JOptionPane.ERROR_MESSAGE);
     return null;
   }
 }
  /**
   * Archive customers with a given date
   *
   * @param date
   * @return true if succeed, false otherwise
   */
  public boolean archive(String date) {
    String sql = "call archiveCustomers(?)";

    try {
      PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
      statement.setString(1, date);
      statement.execute();

      if (statement != null) {
        statement.close();
      }
      return true;
    } catch (SQLException e) {
      e.printStackTrace();
      System.out.println(e.getMessage());
      return false;
    }
  }
Ejemplo n.º 26
0
 public String[] NamaDokter(String spesialis, int total) throws SQLException {
   try {
     String[] nama = new String[total];
     PreparedStatement ps = (PreparedStatement) connection.prepareStatement(ambilNamaDokter);
     ps.setString(1, spesialis);
     ResultSet rs = ps.executeQuery();
     while (rs.next()) {
       nama[rs.getRow() - 1] = rs.getString("nm_dokter");
     }
     rs.close();
     ps.close();
     return nama;
   } catch (SQLException se) {
     JOptionPane.showMessageDialog(
         null, se.getMessage(), "String[] Nama Dokter Gagal!", JOptionPane.ERROR_MESSAGE);
     return null;
   }
 }
  public void run() {
    // this.saveTime = System.currentTimeMillis();
    while (true) {
      IBLoaderInputStream is = null;
      // IBLoaderInputStreamNew is = null;
      com.mysql.jdbc.PreparedStatement mysqlStatement = null;

      if (finished == 1 && queue.size() == 0) {
        stop();
        break;
      }
      try {
        is = new IBLoaderInputStream(queue);
        // is = new IBLoaderInputStreamNew(queue);
        is.init();
        // obtain PreparedStatement for load data into infobright.
        mysqlStatement = obtainLoadMysqlStatement(is.getActualTableName());

        DButil.loadData(mysqlStatement, is);

        //				if(queue.size()>=IBLConfig.loadQueueSize/5)
        //				    logger.info("current Queue size: " + queue.size());

      } catch (InterruptedException e) {
        logger.info("ibloader stop when take task from queue", e);
        finished = 1;
      } catch (SQLException e) {
        logger.error("load failed!!!", e);
      } catch (Exception e) {
        logger.error("load failed!!!", e);
      } finally {
        try {
          if (null != is) is.close();
          if (null != mysqlStatement) mysqlStatement.close();
        } catch (IOException e) {
          logger.info("close inputstream error!!!", e);
        } catch (SQLException e) {
          logger.info("close mysqlStatement error!!!", e);
        } catch (Exception e) {
          logger.error("close inputstream or mysqlStatement error!!!", e);
        }
      }
    }
  }
Ejemplo n.º 28
0
  public void fromExcelToDB() {
    try {
      Class.forName("com.mysql.jdbc.Driver");
      Connection con =
          (Connection)
              DriverManager.getConnection("jdbc:mysql://localhost/excel", "root", "21092012artem");
      con.setAutoCommit(false);
      PreparedStatement pstm = null;

      File file = new File("D://file.xlsx");
      if (!file.exists()) {}

      InputStream input = new FileInputStream(file);

      XSSFWorkbook wb = new XSSFWorkbook(input);

      XSSFSheet sheet = wb.getSheetAt(0);

      Row row;
      for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        int id = (int) row.getCell(0).getNumericCellValue();
        String name = row.getCell(1).getStringCellValue();
        String address = row.getCell(2).getStringCellValue();
        String sql = "INSERT INTO excel VALUES('" + id + "','" + name + "','" + address + "')";
        pstm = (PreparedStatement) con.prepareStatement(sql);
        pstm.execute();
        System.out.println("Import rows " + i);
      }
      con.commit();
      pstm.close();
      con.close();
      input.close();
      System.out.println("Success import excel to mysql table");
    } catch (ClassNotFoundException e) {
      System.out.println(e);
    } catch (SQLException ex) {
      System.out.println(ex);
    } catch (IOException ioe) {
      System.out.println(ioe);
    }
  }
Ejemplo n.º 29
0
 public JasperPrint relatorioHorario(String horaInicio) throws Exception {
   java.sql.Connection con = ConexaoDB.getInstance().getCon();
   String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioHorario.jasper";
   URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
   horaInicio = (new StringBuilder()).append("= '").append(horaInicio).append("'))").toString();
   String query =
       (new StringBuilder())
           .append(
               "SELECT a.matricula, p.nome, t.horaInicio, t.horaFinal FROM alunos a, pessoas p, turmas t WHERE a.idPessoa = p.idPessoa AND a.matricula IN (SELECT matricula FROM ADESOES WHERE status=true AND codTurma IN (SELECT codTurma FROM TURMAS where horaInicio ")
           .append(horaInicio)
           .toString();
   PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
   ResultSet rs = stmt.executeQuery();
   JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
   java.util.Map parameters = new HashMap();
   JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
   rs.close();
   stmt.close();
   return rel;
 }
Ejemplo n.º 30
0
  /**
   * Returns the session-key associated with the user.
   *
   * @param id
   * @return session-key
   * @throws InstantiationException
   * @throws IllegalAccessException
   * @throws ClassNotFoundException
   * @throws SQLException
   */
  public static String getUserKey(int id)
      throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {

    String sql = "SELECT `key` FROM session WHERE user_id = ?";
    String key = null;

    Connection connection = DataBaseUtils.getMySQLConnection();
    PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql);

    ps.setInt(1, id);
    ResultSet rs = ps.executeQuery();

    if (rs.next()) {
      key = rs.getString("key");
    }

    ps.close();
    connection.close();

    return key;
  }