Exemplo n.º 1
1
 static void attributeMean() {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
     Statement meanQuery = con.createStatement();
     ResultSet mean = meanQuery.executeQuery("select AVG(sal) from employ where sal IS NOT NULL");
     if (mean.next()) {
       fill = con.createStatement();
       fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + mean.getInt(1) + ")");
     }
     /*fill = con.createStatement();
     fill.executeUpdate("UPDATE employ set sal = COALESCE(sal,AVG(sal))");*/
     st = con.createStatement();
     rs = st.executeQuery("select * from employ");
     System.out.println("Sno\tName\tSalary\tGPF\tGrade");
     while (rs.next()) {
       System.out.println(
           ">>"
               + rs.getString(1)
               + "\t"
               + rs.getString(2)
               + "\t"
               + rs.getString(3)
               + "\t"
               + rs.getString(4)
               + "\t"
               + rs.getString(5));
     }
   } catch (Exception e) {
     System.out.println("Error: " + e);
   }
 }
Exemplo n.º 2
1
  @Override
  public Set<EmpVO> getEmpsByDeptno(Integer deptno) {
    Set<EmpVO> set = new LinkedHashSet<EmpVO>();
    EmpVO empVO = null;

    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {

      Class.forName(driver);
      con = DriverManager.getConnection(url, userid, passwd);
      pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT);
      pstmt.setInt(1, deptno);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        empVO = new EmpVO();
        empVO.setEmpno(rs.getInt("empno"));
        empVO.setEname(rs.getString("ename"));
        empVO.setJob(rs.getString("job"));
        empVO.setHiredate(rs.getDate("hiredate"));
        empVO.setSal(rs.getDouble("sal"));
        empVO.setComm(rs.getDouble("comm"));
        empVO.setDeptno(rs.getInt("deptno"));
        set.add(empVO); // Store the row in the vector
      }

      // Handle any driver errors
    } catch (ClassNotFoundException e) {
      throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
      // Handle any SQL errors
    } catch (SQLException se) {
      throw new RuntimeException("A database error occured. " + se.getMessage());
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (pstmt != null) {
        try {
          pstmt.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (con != null) {
        try {
          con.close();
        } catch (Exception e) {
          e.printStackTrace(System.err);
        }
      }
    }
    return set;
  }
Exemplo n.º 3
0
  private void getIgnoreList() {
    Connection con = null;
    try {
      con = pool.getConnection(timeout);

      Statement s = con.createStatement();
      s.executeQuery("SELECT `name`, `type` FROM `ignores`");

      ResultSet rs = s.getResultSet();
      this.ignore_list.clear();
      this.soft_ignore_list.clear();
      while (rs.next()) {
        if (rs.getString("type").equals("hard")) {
          this.ignore_list.add(rs.getString("name").toLowerCase());
        } else {
          this.soft_ignore_list.add(rs.getString("name").toLowerCase());
        }
      }
      rs.close();
      s.close();
    } catch (SQLException ex) {
      Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
      try {
        if (con != null) {
          con.close();
        }
      } catch (SQLException ex) {
        Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
      }
    }
  }
Exemplo n.º 4
0
 public Vector<Message> getInboxMessages(String username, int type) {
   if (type == 4) username = "******";
   ResultSet rs =
       con.queryDB(
           "SELECT * FROM "
               + MESSAGES
               + " WHERE toName = \""
               + username
               + "\" AND messageType = "
               + type
               + " ORDER BY timeStamp DESC");
   Vector<Message> inbox = new Vector<Message>();
   try {
     while (rs.next()) {
       inbox.add(
           new Message(
               rs.getString("fromName"),
               username,
               rs.getString("message"),
               rs.getInt("messageType"),
               rs.getTimestamp("timeStamp")));
     }
   } catch (SQLException e) {
     throw new RuntimeException("SQLException in MessageDatabase::getInboxMessages");
   }
   return inbox;
 }
Exemplo n.º 5
0
  /**
   * Gets a list of courses that belongs to an instructor. Throws InvalidDBRequestException if any
   * error occured to the database connection.
   *
   * @param name the instructor's user name
   * @return a vector containing the list of courses
   * @throws InvalidDBRequestException
   */
  public Vector getCourseList(String name) throws InvalidDBRequestException {
    Vector courseList = new Vector();

    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      // get the course list
      rs =
          stmt.executeQuery(
              "select course_num, course_name from course where instructor = '"
                  + name
                  + "' order by course_num");
      while (rs.next()) courseList.add(rs.getString(1) + " - " + rs.getString(2));

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in getCourseList: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Server Error");
    }

    return courseList;
  }
Exemplo n.º 6
0
  public static String printView(ResultSet rs) throws Exception {
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numCols = rsMetaData.getColumnCount();
    String[] headers = new String[numCols];
    OpenType[] allTypes = new OpenType[numCols];
    Vector[] values = new Vector[numCols];
    Object[] allValues = new Object[numCols];

    String ret = "";

    for (int i = 0; i < numCols; i++) {
      if (i == 0) {
        ret = rsMetaData.getColumnName(i + 1);
      } else {
        ret = ret + "," + rsMetaData.getColumnName(i + 1);
      }
    }
    ret = ret + "\n";

    while (rs.next()) {
      for (int i = 0; i < numCols; i++) {
        if (i == 0) {
          ret = ret + rs.getString(i + 1);
        } else {
          ret = ret + ", " + rs.getString(i + 1);
        }
      }
      ret = ret + "\n";
    }

    return ret;
  }
Exemplo n.º 7
0
    public MetadataBlob[] select(String selectString) {
      PreparedStatement stmt = null;
      try {
        stmt =
            conn.prepareStatement(
                "select writingKey, mapkey, blobdata from metadatablobs " + selectString + ";");
        ResultSet rs = stmt.executeQuery();
        List<MetadataBlob> list = new ArrayList<MetadataBlob>();
        while (rs.next()) {
          MetadataBlob f =
              new MetadataBlob(
                  rs.getString("writingkey"), rs.getString("mapkey"), rs.getString("blobdata"));
          list.add(f);
        }

        return list.toArray(new MetadataBlob[0]);
      } catch (SQLException sqe) {
        System.err.println("Error selecting: " + selectString);
        sqe.printStackTrace();
        return null;
      } finally {
        if (stmt != null)
          try {
            stmt.close();
          } catch (SQLException sqe2) {
            sqe2.printStackTrace();
          }
      }
    }
Exemplo n.º 8
0
 @Override
 public List<Ordine> listaOrdini() throws PersistenceException { // ok
   Connection connection = this.datasource.getConnection();
   PreparedStatement statement = null;
   List<Ordine> ordini = null;
   Ordine ordine = null;
   try {
     String str =
         "select ordini.codice as codice,stato,data,cliente,id "
             + "from ordini left outer join clienti on ordini.cliente=clienti.codice";
     statement = connection.prepareStatement(str);
     ResultSet result = statement.executeQuery();
     ordini = new ArrayList<Ordine>();
     while (result.next()) {
       ordine = new Ordine();
       ClienteDAOImpl cliente = new ClienteDAOImpl();
       ordine.setCliente(cliente.getClienteById(result.getInt("cliente")));
       ordine.setCodiceOrdine(result.getString("codice"));
       ordine.setData(new java.util.Date(result.getDate("data").getDate()));
       ordine.setStato(result.getString("stato"));
       ordine.setId(result.getInt("id"));
       ordini.add(ordine);
     }
   } catch (SQLException e) {
     throw new PersistenceException(e.getMessage());
   } finally {
     try {
       if (statement != null) statement.close();
       if (connection != null) connection.close();
     } catch (SQLException e) {
       throw new PersistenceException(e.getMessage());
     }
   }
   return ordini;
 }
Exemplo n.º 9
0
 @Override
 public List<Ordine> getOrdiniPerCliente(Cliente cliente) throws PersistenceException { // ok
   Connection connection = this.datasource.getConnection();
   PreparedStatement statement = null;
   Ordine o = null;
   List<Ordine> lo = new LinkedList<Ordine>();
   try {
     String str = "select codice,data,stato,id from ordini where cliente=?";
     statement = connection.prepareStatement(str);
     statement.setInt(1, cliente.getId());
     ResultSet result = statement.executeQuery();
     while (result.next()) {
       o = new Ordine();
       o.setCliente(cliente);
       o.setCodiceOrdine(result.getString("codice"));
       o.setData(result.getDate("data"));
       o.setStato(result.getString("stato"));
       o.setId(result.getInt("id"));
       lo.add(o);
       // da completare per restituire ordine completo di righe
     }
   } catch (SQLException e) {
     throw new PersistenceException(e.getMessage());
   } finally {
     try {
       if (statement != null) statement.close();
       if (connection != null) connection.close();
     } catch (SQLException e) {
       throw new PersistenceException(e.getMessage());
     }
   }
   return lo;
 }
Exemplo n.º 10
0
  @Override
  public void actionPerformed(ActionEvent axnEve) {
    Object obj = axnEve.getSource();
    if (obj == replyBut) {
      try {
        if (Home.composeDial != null && Home.composeDial.isShowing()) {

        } else {
          Home.composeDial =
              new ComposeMailDialog(
                  msgID, workingSet.getString("mail_addresses"), workingSet.getString("subject"));
        }
      } catch (SQLException sqlExc) {
        sqlExc.printStackTrace();
      }
    } else if (obj == forwardBut) {
      try {
        if (Home.composeDial != null && Home.composeDial.isShowing()) {

        } else {
          Home.composeDial =
              new ComposeMailDialog(
                  msgID,
                  workingSet.getString("subject") + "\n\n" + workingSet.getString("content"));
        }
      } catch (SQLException sqlExc) {
        sqlExc.printStackTrace();
      }
    }
  }
Exemplo n.º 11
0
  // 일사용량(7일)
  public ArrayList<ArrayList<String>> get7dayUsage(String umdong) {

    ArrayList<ArrayList<String>> datas = new ArrayList<ArrayList<String>>();

    String sql =
        "select sum(CONSUMED), sum(PREDICTED) from CONSUMPTION where CODE in(select CODE from USER where UMDONG = \'"
            + umdong
            + "\') and DATE between '2015-02-22' and '2015-02-28' group by DATE order by DATE DESC;";

    try {
      pstmt = conn.prepareStatement(sql);
      ResultSet rs = pstmt.executeQuery();

      while (rs.next()) {
        ArrayList<String> usage = new ArrayList<String>();

        usage.add(rs.getString("sum(CONSUMED)"));
        usage.add(rs.getString("sum(PREDICTED)"));
        datas.add(usage);
      }
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return datas;
  }
Exemplo n.º 12
0
  public void doSQL() throws Exception {
    Class.forName("org.sqlite.JDBC");
    Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
    Statement stat = conn.createStatement();
    stat.executeUpdate("drop table if exists people;");
    stat.executeUpdate("create table people (name, occupation);");
    PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");

    prep.setString(1, "Gandhi");
    prep.setString(2, "politics");
    prep.addBatch();
    prep.setString(1, "Turing");
    prep.setString(2, "computers");
    prep.addBatch();
    prep.setString(1, "Wittgenstein");
    prep.setString(2, "smartypants");
    prep.addBatch();

    conn.setAutoCommit(false);
    prep.executeBatch();
    conn.setAutoCommit(true);

    ResultSet rs = stat.executeQuery("select * from people;");
    while (rs.next()) {
      System.out.println("name = " + rs.getString("name"));
      System.out.println("job = " + rs.getString("occupation"));
    }
    rs.close();
    conn.close();
  }
Exemplo n.º 13
0
  /**
   * Gets elements depending on the format specified.
   *
   * @param format The format specification of the elements to be returned.
   * @return a List of all elements in MetaDB with the specified format.
   */
  public List<Element> getElementList(String format) {
    List<Element> elementList = new ArrayList<Element>();

    Connection conn = Conn.initialize();
    if (conn != null) {
      try {
        PreparedStatement getElementListQuery = conn.prepareStatement(GET_ELEMENT_LIST_BY_FORMAT);
        getElementListQuery.setString(1, format);

        ResultSet getElementListQueryResult = getElementListQuery.executeQuery();

        while (getElementListQueryResult.next())
          elementList.add(
              new Element(
                  getElementListQueryResult.getString(Global.ELEMENT),
                  getElementListQueryResult.getString(Global.ELEMENT_FORMAT)));

        getElementListQueryResult.close();
        getElementListQuery.close();
        conn.close();
      } catch (Exception e) {
        MetaDbHelper.logEvent(e);
      }
    }
    return elementList;
  }
Exemplo n.º 14
0
 static void specificAttributeMean() {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
     fill = con.createStatement();
     fill.executeUpdate(
         "UPDATE employ a set sal = (select AVG(sal) from employ where sal IS NOT NULL and grade = (select grade from employ b where sal IS NULL and a.grade=b.grade and ROWNUM <= 1)) where sal IS NULL");
     st = con.createStatement();
     rs = st.executeQuery("select * from employ");
     System.out.println(">>Sno\tName\tSalary\tGPF\tGrade");
     while (rs.next()) {
       System.out.println(
           ">>"
               + rs.getString(1)
               + "\t"
               + rs.getString(2)
               + "\t"
               + rs.getString(3)
               + "\t"
               + rs.getString(4)
               + "\t"
               + rs.getString(5));
     }
   } catch (Exception e) {
     System.out.println("Error: " + e);
   }
 }
Exemplo n.º 15
0
  public static void duration() {
    connectDb();
    String sql = "select min(time), max(time), count(*) from " + tablename + ";";
    try {
      ResultSet rs = query.executeQuery(sql);
      rs.next();
      int numCols = rs.getMetaData().getColumnCount();
      String minTime = rs.getString(1);
      String maxTime = rs.getString(2);
      String numPackets = rs.getString(3);
      System.out.println(
          "Experiment "
              + tablename
              + "\n\tfrom: "
              + minTime
              + "\n\tto: "
              + maxTime
              + "\n\tpackets: "
              + numPackets);

    } catch (SQLException e) {
      System.out.println("SQL Exception: " + e);
      System.exit(1);
    }
  }
Exemplo n.º 16
0
  /**
   * Transforma os dados obtidos atraves de uma consulta a tabela de glosas do prestador em um
   * objeto do tipo GlosaPrestador
   *
   * @param rset - um ResultSet contendo o resultado da consulta a tabela de glosas do prestador
   * @return um objeto do tipo GlosaPrestador
   */
  private static final GlosaPrestador montaGlosaPrestador(ResultSet rset) throws SQLException {
    String numAtendimento = null;
    String numDocOrigem = null;
    String strLoteNota = "";
    String strCodBeneficiario = "";
    String strNomeBeneficiario = "";
    Timestamp dtGlosa = null;
    String strMotivo = "";
    String strDescricao = "";
    String strObservacoes = "";

    numAtendimento = rset.getString("rej_ate_num_atendimento");
    dtGlosa = rset.getTimestamp("rej_data_hora");
    numDocOrigem = rset.getString("nat_numero_documento_origem");
    strLoteNota = rset.getString("Lote_Nota");
    strCodBeneficiario = rset.getString("Beneficiario");
    strNomeBeneficiario = rset.getString("Nome");
    strDescricao = rset.getString("Descricao");
    strMotivo = (rset.getString("rej_motivo") == null) ? "" : rset.getString("rej_motivo");
    strObservacoes =
        (rset.getString("ate_observacoes") == null) ? "" : rset.getString("ate_observacoes");

    return (new GlosaPrestador(
        numAtendimento,
        numDocOrigem,
        strLoteNota,
        strCodBeneficiario,
        strNomeBeneficiario,
        dtGlosa,
        strMotivo,
        strDescricao,
        strObservacoes));
  } // montaGlosaPrestador()
Exemplo n.º 17
0
  public void setProperties(ResultSet SYSTEM_CONFIG) throws SQLException {
    try {

      mailHost = SYSTEM_CONFIG.getString("MailServer");
      Source = SYSTEM_CONFIG.getString("SenderMailNameProgram");

      WebMaster = SYSTEM_CONFIG.getString("MailWebMaster");

      /* Legge la lista amministratori dal file di proprieta */
      StringTokenizer myStrTok = getTokenizer(SYSTEM_CONFIG.getString("CarbonCopyMailRecipients"));
      StringTokenizer myStrTokName =
          getTokenizer(SYSTEM_CONFIG.getString("NameServiceAdministratorsList"));

      /* crea un array per la lista degli amministratori */
      MailServiceAdministratorsList = new String[myStrTok.countTokens()];

      int i = 0;
      while (myStrTok.hasMoreTokens()) {
        MailServiceAdministratorsList[i++] = myStrTok.nextToken();
      }

    } catch (SQLException sqle) {
      throw sqle;
    }
  }
 // 根据推荐的movie的ID,获得movie的详细信息
 public ArrayList<MovieInfo> getMovieByMovieId(List<RecommendedItem> recommendations) {
   ArrayList<MovieInfo> movieList = new ArrayList<MovieInfo>();
   try {
     String sql = "";
     conn = ConnectToMySQL.getConnection();
     for (int i = 0; i < recommendations.size(); i++) {
       sql =
           "select m.name,m.published_year,m.type from movies m where m.id="
               + recommendations.get(i).getItemID()
               + "";
       ps = conn.prepareStatement(sql);
       rs = ps.executeQuery();
       while (rs.next()) {
         MovieInfo movieInfo = new MovieInfo();
         movieInfo.setName(rs.getString(1));
         movieInfo.setPublishedYear(rs.getString(2));
         movieInfo.setType(rs.getString(3));
         movieInfo.setPreference(recommendations.get(i).getValue());
         movieList.add(movieInfo);
       }
     }
   } catch (Exception e) {
     // TODO: handle exception
     e.printStackTrace();
   } finally {
     closeAll();
   }
   return movieList;
 }
Exemplo n.º 19
0
  public static List<RssFeed> loadFeeds() {
    loadDriver();

    Connection conn = null;
    ResultSet rs = null;
    List<RssFeed> feeds = new ArrayList<>();
    try {
      // database name
      String dbName = "demoDB";

      conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);

      rs = conn.createStatement().executeQuery("select id, title, url from rssFeed");
      while (rs.next()) {
        String title = rs.getString("title");
        String url = rs.getString("url");
        RssFeed rssFeed = new RssFeed(title, url);
        rssFeed.id = rssFeed.link.hashCode();
        feeds.add(rssFeed);
      }
      shutdown();
    } catch (SQLException sqle) {
      sqle.printStackTrace();
    } finally {

      close(rs);
      close(conn);
    }
    return feeds;
  }
 // 根据用户的id,获得用户的所有电影
 public ArrayList<MovieInfo> getMovieByUserId(long userID) {
   ArrayList<MovieInfo> movieList = new ArrayList<MovieInfo>();
   try {
     String sql =
         "select m.name,m.published_year,m.type,mp.preference from movie_preferences mp,movies m where mp.movieID=m.id and mp.userID="
             + userID
             + " order by preference desc";
     conn = ConnectToMySQL.getConnection();
     ps = conn.prepareStatement(sql);
     rs = ps.executeQuery();
     while (rs.next()) {
       MovieInfo movieInfo = new MovieInfo();
       movieInfo.setName(rs.getString(1));
       movieInfo.setPublishedYear(rs.getString(2));
       movieInfo.setType(rs.getString(3));
       movieInfo.setPreference(rs.getInt(4));
       movieList.add(movieInfo);
     }
   } catch (Exception e) {
     // TODO: handle exception
     e.printStackTrace();
   } finally {
     closeAll();
   }
   return movieList;
 }
Exemplo n.º 21
0
 private void ListValueChanged(
     javax.swing.event.ListSelectionEvent evt) { // GEN-FIRST:event_ListValueChanged
   // TODO add your handling code here:
   // String part=partno.getText();
   try {
     String sql =
         "SELECT  TYPE,ITEM_NAME,QUANTITY,MRP FROM MOTORS WHERE ITEM_NAME='"
             + List.getSelectedValue()
             + "'";
     Class.forName("com.mysql.jdbc.Driver");
     Connection con =
         (Connection)
             DriverManager.getConnection("jdbc:mysql://localhost:3306/bharatmotors", "root", "");
     Statement stmt = con.createStatement();
     ResultSet rs = stmt.executeQuery(sql);
     while (rs.next()) {
       partno.setText(rs.getString("TYPE"));
       name.setText(rs.getString("ITEM_NAME"));
       qty.setText(rs.getString("QUANTITY"));
       rate.setText(rs.getString("MRP"));
     }
   } catch (Exception e) {
     JOptionPane.showMessageDialog(null, e.toString());
   }
 } // GEN-LAST:event_ListValueChanged
 public ArrayList<Empleado> listarEmpleado() {
   ArrayList<Empleado> emple = new ArrayList<Empleado>();
   try {
     cn = Conexion.realizarConexion();
     st = cn.createStatement();
     String sql = "select * from empleado";
     rs = st.executeQuery(sql);
     while (rs.next()) {
       emple.add(
           new Empleado(
               rs.getString(1),
               rs.getString(2),
               rs.getString(3),
               rs.getString(4),
               rs.getString(5),
               rs.getString(6),
               rs.getString(7),
               rs.getString(8)));
     }
   } catch (ClassNotFoundException ex) {
     showMessageDialog(null, ex.getMessage(), "Excepción", 0);
   } catch (SQLException ex) {
     showMessageDialog(null, ex.getMessage(), "Excepción", 0);
   } finally {
     try {
       rs.close();
       st.close();
       cn.close();
     } catch (Exception ex) {
       showMessageDialog(null, ex.getMessage(), "Excepción", 0);
     }
   }
   return emple;
 }
Exemplo n.º 23
0
  /**
   * Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the
   * student's directory which relates to the course. Caution: vizualisation file will be deleted
   * eventhough it also relates to another course if the student is also registered to that course.
   * (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course,
   * error occured during deletion, or other exception occured.
   *
   * @param username student's user name
   * @param courseID course id (course number + instructor name)
   * @throws InvalidDBRequestException
   */
  public void deleteStudent(String username, String courseID) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      int count = 0;

      // check if student registered to the course
      rs =
          stmt.executeQuery(
              "select * from courseRoster where course_id = '"
                  + courseID
                  + "' and user_login = '******'");
      if (!rs.next())
        throw new InvalidDBRequestException("Student is not registered to the course");

      // remove student from the course
      count =
          stmt.executeUpdate(
              "delete from courseRoster where course_id = '"
                  + courseID
                  + "' and user_login = '******'");
      if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!");

      // delete the quiz visualization files
      rs =
          stmt.executeQuery(
              "select distinct unique_id, s.test_name from scores s, courseTest t "
                  + "where s.test_name = t.test_name "
                  + "and course_id = '"
                  + courseID
                  + "' "
                  + "and user_login = '******'");
      while (rs.next()) {
        deleteVisualization(rs.getString(1), username, rs.getString(2));
        count =
            stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim());
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addstudent: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
Exemplo n.º 24
0
  public static ArrayList<String> getPasser_zone(String zone) throws SQLException {
    ArrayList<String> passerArray = new ArrayList<String>();
    Connection conn = DBConn.GetConnection();
    PreparedStatement st =
        conn.prepareStatement(
            "SELECT * FROM cs_conversion LEFT JOIN cs_user ON cs_user.username=cs_conversion.username WHERE cs_conversion.isstart=? AND cs_user.zone=? ");
    st.setInt(1, 0);
    st.setString(2, zone);

    ResultSet rs = st.executeQuery();
    while (rs.next()) {
      if (rs.getString("username").length() != 0) {
        if (passerArray.size() == 0) {
          passerArray.add(rs.getString("username"));
        } else {
          if (!passerArray.contains(rs.getString("username"))) {
            passerArray.add(rs.getString("username"));
          }
        }
      }
    }
    rs.close();
    st.close();
    conn.close();
    return passerArray;
  }
Exemplo n.º 25
0
 protected void executeQuery(Statement stmt, String q) throws SQLException {
   q = q.replace("$PREFIX", getPrefix());
   LOG.info(" Executing " + q);
   ResultSet rs = stmt.executeQuery(q);
   StringBuilder header = new StringBuilder();
   for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
     if (i > 1) {
       header.append("|");
     }
     header.append(rs.getMetaData().getColumnName(i));
   }
   LOG.info(header);
   int seq = 0;
   while (true) {
     boolean valid = rs.next();
     if (!valid) break;
     seq++;
     StringBuilder line = new StringBuilder();
     for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
       if (i > 1) {
         line.append("|");
       }
       line.append(rs.getString(i));
     }
     LOG.info(seq + ":" + line);
   }
 }
Exemplo n.º 26
0
  /**
   * Get waiting User name;
   *
   * @return An waiter ArrayList
   */
  public static ArrayList<String> getWaiter() throws SQLException {
    ArrayList<String> waiterArray = new ArrayList<String>();
    Connection conn = DBConn.GetConnection();
    PreparedStatement st = conn.prepareStatement("SELECT * FROM cs_conversion where isstart=?");
    st.setInt(1, 1);
    ResultSet rs = st.executeQuery();
    while (rs.next()) {
      if (rs.getString("username").length() != 0) {

        if (waiterArray.size() == 0) {
          waiterArray.add(rs.getString("username"));
        } else {
          for (int i = 0; i < waiterArray.size(); i++) {
            if (!rs.getString("username").equals(waiterArray.get(i).toString())) {
              waiterArray.add(rs.getString("username"));
            }
          }
        }
      }
    }
    rs.close();
    st.close();
    conn.close();
    return waiterArray;
  }
Exemplo n.º 27
0
  @Test
  public void testReadDomainsAndGuids() throws Exception {
    CustomerDAO dao = new CustomerDAO();
    IConfiguration configuration = EasyMock.createStrictMock(IConfiguration.class);
    dao.setConfiguration(configuration);
    ResultSet resultSet = EasyMock.createStrictMock(ResultSet.class);
    Set<String> domains = new HashSet<String>();
    Set<GlobalIdentifier> guids = new HashSet<GlobalIdentifier>();
    int custId = 34;
    int cloudService = 2;
    int replicationZone = 453;

    int cloudService2 = 1;
    int replicationZone2 = 13;

    int cloudService3 = 3;

    // first exec of loop
    EasyMock.expect(resultSet.getString(10)).andReturn("domain123");
    EasyMock.expect(resultSet.getString(11)).andReturn("guid123");
    EasyMock.expect(resultSet.getInt(12)).andReturn(cloudService);
    EasyMock.expect(resultSet.getInt(13)).andReturn(replicationZone);
    EasyMock.expect(resultSet.next()).andReturn(true);
    EasyMock.expect(resultSet.getInt(1)).andReturn(custId);

    // second exec of loop
    EasyMock.expect(resultSet.getString(10)).andReturn("domain456");
    EasyMock.expect(resultSet.getString(11)).andReturn("guid456");
    EasyMock.expect(resultSet.getInt(12)).andReturn(cloudService2);
    EasyMock.expect(resultSet.getInt(13)).andReturn(replicationZone2);
    EasyMock.expect(resultSet.next()).andReturn(true);
    EasyMock.expect(resultSet.getInt(1)).andReturn(custId);

    // third exec of loop (guid not valid with no cloud service)
    EasyMock.expect(resultSet.getString(10)).andReturn("domain456");
    EasyMock.expect(resultSet.getString(11)).andReturn("guid789");
    EasyMock.expect(resultSet.getInt(12)).andReturn(cloudService3);
    EasyMock.expect(resultSet.next()).andReturn(true);
    EasyMock.expect(resultSet.getInt(1)).andReturn(custId + 1); // ends loop with mismatched custid

    EasyMock.replay(resultSet);
    assertTrue(
        "Should have another item even.",
        dao.readDomainsAndGuids(resultSet, custId, domains, guids));
    EasyMock.verify(resultSet);
    assertEquals("Should have 2 domains.", 2, domains.size());
    assertTrue("Domain123 not found.", domains.contains("domain123"));
    assertTrue("Domain456 not found.", domains.contains("domain456"));

    assertEquals("Should have 2 guids.", 2, guids.size());
    for (GlobalIdentifier guid : guids) {
      if (guid.getGuid().equals("guid123")) {
        assertEquals("Wrong cloud service in guid123", CloudService.GOOGLE, guid.getService());
        assertEquals("Wrong replication zone.", replicationZone, guid.getReplicationZone());
      } else {
        assertEquals("Wrong cloud service in guid456", CloudService.OFFICE365, guid.getService());
        assertEquals("Wrong replication zone.", replicationZone2, guid.getReplicationZone());
      }
    }
  }
  /**
   * Returns the messages which ID starts with the given ID
   *
   * @param ID message ID
   * @return text and ID of the message
   */
  public synchronized Hashtable getMessagesWithId(String ID) {
    //        System.out.println("getMessagesWithId:"+ID);

    Hashtable res = new Hashtable();
    Enumeration enm = dict.keys();
    while (enm.hasMoreElements()) {
      String key = "" + enm.nextElement();
      if (key.startsWith(ID)) res.put(key, dict.get(key));
    }
    if (res.size() == 0) {
      Connection connection = null;
      try {
        connection = getMessageDBConnection();
        PreparedStatement proc =
            connection.prepareStatement("SELECT tkey,txt FROM messages_texts WHERE tkey like ?");
        proc.setString(1, ID + "%");
        ResultSet rs = proc.executeQuery();
        while (rs.next()) {
          res.put(rs.getString(1), rs.getString(2));
        }
        connection.close();
      } catch (Exception e) {
        /*not message connection*/
        e.printStackTrace();
      }
    }

    return res;
  }
Exemplo n.º 29
0
 private ImmutableMap<String, JdbcTable> computeTables() {
   Connection connection = null;
   ResultSet resultSet = null;
   try {
     connection = dataSource.getConnection();
     DatabaseMetaData metaData = connection.getMetaData();
     resultSet = metaData.getTables(catalog, schema, null, null);
     final ImmutableMap.Builder<String, JdbcTable> builder = ImmutableMap.builder();
     while (resultSet.next()) {
       final String tableName = resultSet.getString(3);
       final String catalogName = resultSet.getString(1);
       final String schemaName = resultSet.getString(2);
       final String tableTypeName = resultSet.getString(4);
       // Clean up table type. In particular, this ensures that 'SYSTEM TABLE',
       // returned by Phoenix among others, maps to TableType.SYSTEM_TABLE.
       // We know enum constants are upper-case without spaces, so we can't
       // make things worse.
       final String tableTypeName2 = tableTypeName.toUpperCase().replace(' ', '_');
       final TableType tableType = Util.enumVal(TableType.class, tableTypeName2);
       final JdbcTable table = new JdbcTable(this, catalogName, schemaName, tableName, tableType);
       builder.put(tableName, table);
     }
     return builder.build();
   } catch (SQLException e) {
     throw new RuntimeException("Exception while reading tables", e);
   } finally {
     close(connection, null, resultSet);
   }
 }
Exemplo n.º 30
0
 static void globalConstant() {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
     fill = con.createStatement();
     fill.executeUpdate("UPDATE employ set sal = " + GlobalConstant + " where sal IS NULL");
     // fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + GlobalConstant + ")");
     // System.out.println("Entered..!");
     st = con.createStatement();
     rs = st.executeQuery("select * from employ");
     System.out.println("Sno\tName\tSalary\tGPF\tGrade");
     while (rs.next()) {
       System.out.println(
           ">>"
               + rs.getString(1)
               + "\t"
               + rs.getString(2)
               + "\t"
               + rs.getString(3)
               + "\t"
               + rs.getString(4)
               + "\t"
               + rs.getString(5));
     }
   } catch (Exception e) {
     System.out.println("Error: " + e);
   }
 }