Exemple #1
0
  /**
   * ** Returns true if the specified key attribute exists in the table ** @param altIndexName The
   * alternate index name, or null to use the primary index ** @param whereKeyType The partial key
   * match type ** @return True if the specified key attribute exists in the table, false otherwise
   */
  protected boolean _exists(String altIndexName, int whereKeyType)
      throws SQLException, DBException {

    /* key fields */
    boolean usePrimaryKey = StringTools.isBlank(altIndexName);
    DBField kfld[] = usePrimaryKey ? this.getKeyFields() : this.getAltKeyFields(altIndexName);
    if (ListTools.isEmpty(kfld)) {
      throw new DBException("No keys found!");
    }

    /* check last key for "auto_increment" */
    if (whereKeyType == DBWhere.KEY_FULL) {
      DBField lastField = kfld[kfld.length - 1];
      if (lastField.isAutoIncrement()
          && !this.getFieldValues().hasFieldValue(lastField.getName())) {
        // full key requested and last key is auto_increment, which is missing
        return false;
      }
    }

    // DBSelect: SELECT <Keys> FROM <TableName> <KeyWhere>
    String firstKey = kfld[0].getName();
    DBSelect<gDBR> dsel = new DBSelect<gDBR>(this.getFactory());
    dsel.setSelectedFields(firstKey);
    dsel.setWhere(this._getWhereClause(altIndexName, whereKeyType));

    /* get keyed record */
    DBConnection dbc = null;
    Statement stmt = null;
    ResultSet rs = null;
    boolean exists = false;
    try {
      dbc = DBConnection.getDefaultConnection();
      stmt = dbc.execute(dsel.toString()); // may throw DBException
      rs = stmt.getResultSet();
      exists = rs.next();
    } catch (SQLException sqe) {
      if (sqe.getErrorCode() == DBFactory.SQLERR_TABLE_NOTLOCKED) {
        // MySQL: This case has been seen on rare occasions.  Not sure what causes it.
        Print.logError("SQL Lock Error: " + sqe);
        Print.logError("Hackery! Forcing lock on table: " + this.getTableName());
        if (DBProvider.lockTableForRead(this.getTableName(), true)) { // may throw DBException
          stmt = dbc.execute(dsel.toString()); // may throw SQLException, DBException
          rs = stmt.getResultSet(); // SQLException
          exists = rs.next(); // SQLException
          DBProvider.unlockTables(); // DBException
        }
      } else {
        throw sqe;
      }
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (Throwable t) {
        }
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (Throwable t) {
        }
      }
      DBConnection.release(dbc);
    }

    return exists;
  }
  /**
   * Insert all service in services table
   *
   * @param noms
   * @throws SQLException
   */
  public static void insertStations(List<String[]> pdvs) throws SQLException {

    int idPdv;
    String nom = "";
    String adresse = "";
    String ville = "";
    String cp = "";
    String type_route;
    String[] ouvert = new String[3];
    String[] fermer = new String[3];
    Time hor_Ouv = null;
    hor_Ouv.valueOf("00:00:00");
    Time hor_Ferm = null;
    hor_Ferm.valueOf("00:00:00");
    double latitudePdv;
    double longitudePdv;

    String path = "src\\DAL\\StationsNOM.csv";
    List<String> nomStations = LectureFichier.readTXT(path);

    Random rand = new Random();
    int nbrand = 2;

    String requete =
        "INSERT INTO stations "
            + "(id_Station,Nom,Adresse,Ville,CP,Type_route,Horaire_ouverture,Horaire_fermeture,latitude,longitude) "
            + "VALUES(?,?,?,?,?,?,?,?,?,?)";
    // int[] count= new int[0];

    Connection connection = ConnexionManager.GetInstance().GetConnection();
    for (String[] pdv : pdvs) {

      if (!pdv[0].equals("id")) {
        idPdv = Integer.parseInt(pdv[0]);
        // integration des noms g�n�r� al�atoirement
        nbrand = rand.nextInt(nomStations.size() - 2) + 1;
        nom = nomStations.get(nbrand);
        adresse = pdv[1];
        ville = pdv[2];
        cp = pdv[3];
        type_route = pdv[4];
        ouvert = pdv[5].split(":");
        fermer = pdv[6].split(":");
        // fermer = pdv[6].replace(':', '-');
        hor_Ouv =
            new Time(
                Integer.parseInt(ouvert[0]),
                Integer.parseInt(ouvert[1]),
                Integer.parseInt(ouvert[2]));
        hor_Ferm =
            new Time(
                Integer.parseInt(fermer[0]),
                Integer.parseInt(fermer[1]),
                Integer.parseInt(fermer[2]));
        latitudePdv = Double.parseDouble(pdv[7]);
        longitudePdv = Double.parseDouble(pdv[8]);

        try {
          PreparedStatement requeteSql = connection.prepareStatement(requete);
          requeteSql.setInt(1, idPdv);
          requeteSql.setString(2, nom);
          requeteSql.setString(3, adresse);
          requeteSql.setString(4, ville);
          requeteSql.setString(5, cp);
          requeteSql.setString(6, type_route);
          requeteSql.setTime(7, hor_Ouv);
          requeteSql.setTime(8, hor_Ferm);
          requeteSql.setDouble(9, latitudePdv);
          requeteSql.setDouble(10, longitudePdv);
          requeteSql.addBatch();
          int[] count = requeteSql.executeBatch();
          // requeteSql.executeBatch();
          // System.out.println(count.length);
        } catch (SQLException sqle) {
          // TODO Auto-generated catch block

          ConnexionManager.GetInstance().GetConnection().rollback();

          sqle.printStackTrace();
        }
      }
    }
  }