Java JdbcConnection.prepareStatement方法代码示例

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {
      PreparedStatement getOwners =
         databaseConnection
            .prepareStatement ("SELECT o.USER_ID, p.ID FROM OWNER o, " +
                  "PRODUCTS p WHERE o.OWNEROFPRODUCT = p.IDENTIFIER");
      ResultSet res = getOwners.executeQuery ();

      while (res.next ())
      {
         Long productIdentifier = (Long) res.getObject ("ID");   
         Long userIdentifier = (Long) res.getObject ("USER_ID");  
         PreparedStatement updateOwner =
            databaseConnection
               .prepareStatement ("UPDATE PRODUCTS SET OWNER_ID = " +
                     userIdentifier+" WHERE ID = "+productIdentifier);
         updateOwner.execute ();
         updateOwner.close();
      }
      getOwners.close ();
   }
   catch (Exception e)
   {
      LOGGER.error("Error during liquibase update 'MoveOwnerInProduct'", e);
   }
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {
      PreparedStatement getEvictions =
         databaseConnection
            .prepareStatement ("SELECT ID,STRATEGY FROM EVICTION");
      ResultSet res = getEvictions.executeQuery ();
      
      while (res.next ())
      {
         Integer strategy = (Integer) res.getObject ("STRATEGY");
         if (strategy == 2)
         {
            // 2 (old LFU) -> 0 (None)
            strategy = 0;
         }
         if (strategy == 3)
         {
            // 3 (old FIFO) -> 2 (new FIFO)
            strategy = 2;
         }
         PreparedStatement updateStrategy =
            databaseConnection
               .prepareStatement ("UPDATE EVICTION SET STRATEGY = '" +
                     strategy+"' WHERE ID = "+res.getObject ("ID"));
         updateStrategy.execute ();
         updateStrategy.close ();
      }
      getEvictions.close ();         
   }
   catch (Exception e)
   {
      LOGGER.error("Error during liquibase update 'removeLFUStrategy'", e);
   }
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {         
      PreparedStatement getCarts =
         databaseConnection.prepareStatement ("SELECT ID FROM PRODUCTCARTS");
      ResultSet res = getCarts.executeQuery ();
      while (res.next ())
      {
         String uuid = UUID.randomUUID ().toString ();
         PreparedStatement updateCart =
            databaseConnection
               .prepareStatement ("UPDATE PRODUCTCARTS SET UUID = '" + uuid +
                  "' WHERE ID = " + res.getObject ("ID"));
         updateCart.execute ();
         updateCart.close ();

         PreparedStatement updateProductsLink =
            databaseConnection
               .prepareStatement ("UPDATE CART_PRODUCTS SET CART_UUID = '" +
                  uuid + "' WHERE CART_ID = " + res.getObject ("ID"));
         updateProductsLink.execute ();
         updateProductsLink.close ();
      }
      getCarts.close ();
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }
   
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {         
      PreparedStatement getRestrictions =
         databaseConnection.prepareStatement ("SELECT ID FROM ACCESS_RESTRICTION");
      ResultSet res = getRestrictions.executeQuery ();
      while (res.next ())
      {
         String uuid = UUID.randomUUID ().toString ();
         PreparedStatement updateRestrictions =
            databaseConnection
               .prepareStatement ("UPDATE ACCESS_RESTRICTION SET UUID = '" + uuid +
                  "' WHERE ID = " + res.getObject ("ID"));
         updateRestrictions.execute ();
         updateRestrictions.close ();

         PreparedStatement updateUserRestricitons =
            databaseConnection
               .prepareStatement ("UPDATE USER_RESTRICTIONS SET RESTRICTION_UUID = '" +
                  uuid + "' WHERE RESTRICTION_ID = " + res.getObject ("ID"));
         updateUserRestricitons.execute ();
         updateUserRestricitons.close ();
      }
      getRestrictions.close ();
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }
   
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
private String retrieveConstraintName(JdbcConnection jdbcConnection) throws Exception {
    PreparedStatement preparedStatement = null;

    try {
        preparedStatement = jdbcConnection.prepareStatement(searchQuery);
        ResultSet result = preparedStatement.executeQuery();
        result.next();
        String constraintName = result.getString(constraintNameColumn);
        logger.info(String.format("Executed statement '%s' returned constraint name: %s", searchQuery, constraintName));
        return constraintName;
    } finally {
        JdbcUtil.closeQuietly(preparedStatement);
    }
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
public OriginalDataType retrieveData(JdbcConnection jdbcConnection) throws DatabaseException, SQLException {
    PreparedStatement preparedStatement = null;
    OriginalDataType result = null;

    try {
        preparedStatement = jdbcConnection.prepareStatement(getSelectStatement());
        ResultSet resultSet = preparedStatement.executeQuery();
        result = extractData(resultSet);
        resultSet.close();
    } finally {
        JdbcUtil.closeQuietly(preparedStatement);
    }
    return result;
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
public void updateTable(JdbcConnection jdbcConnection, TransformedDataType transformedData) throws DatabaseException, SQLException {
    PreparedStatement preparedStatement = null;

    try {
        preparedStatement = jdbcConnection.prepareStatement(getUpdateStatement());
        setUpdateStatementParameters(preparedStatement, transformedData);
        preparedStatement.executeBatch();
    } finally {
        JdbcUtil.closeQuietly(preparedStatement);
    }
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
private void executeStatement(JdbcConnection jdbcConnection, String statement) throws DatabaseException, SQLException {
    PreparedStatement preparedStatement = null;
    try {
        preparedStatement = jdbcConnection.prepareStatement(statement);
        preparedStatement.execute();
    } finally {
        JdbcUtil.closeQuietly(preparedStatement);
    }
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
@Override
public void execute(Database database) throws CustomChangeException
{
   try
   {
      JdbcConnection connection = (JdbcConnection) database.getConnection();

      // count number of deleted products
      String sql_count = "SELECT COUNT(*) FROM DELETED_PRODUCTS";
      long max;
      try (PreparedStatement stmt = connection.prepareStatement(sql_count))
      {
         ResultSet result = stmt.executeQuery();
         if (!result.next())
         {
            throw new CustomChangeException("DELETED_PRODUCTS table update failed");
         }
         max = result.getLong(1);
      }
      LOGGER.info("{} deleted product(s) to update", max);

      long index = 0;
      String get_pattern = "SELECT ID, CHECKSUMS FROM DELETED_PRODUCTS LIMIT %d,%d";
      String update_pattern = "UPDATE DELETED_PRODUCTS SET CHECKSUM_ALGORITHM='%s', CHECKSUM_VALUE='%s' WHERE ID=%d";
      while (index < max)
      {
         // retrieve data
         sql_count = String.format(get_pattern, index, PAGE_SIZE);
         try (PreparedStatement get_stmt = connection.prepareStatement(sql_count))
         {
            ResultSet get_result = get_stmt.executeQuery();
            Statement batchUpdate = connection.createStatement();
            while (get_result.next())
            {
               // retrieve data
               long id = get_result.getLong("ID");
               Blob blob = get_result.getBlob("CHECKSUMS");
               byte[] data = blob.getBytes(1, (int) blob.length());
               Map<String, String> checksums = (Map<String, String>) deserialize(data);

               if (!checksums.isEmpty())
               {
                  // fill newly fill
                  Map.Entry<String, String> checksum = checksums.entrySet().iterator().next();
                  String sql_update = String.format(update_pattern, checksum.getKey(), checksum.getValue(), id);
                  batchUpdate.addBatch(sql_update);
               }
               index++;
            }
            batchUpdate.executeBatch();
            batchUpdate.close();
            LOGGER.info("RemoveDeletedProductBlobFields:retrieveChecksum: {}/{}", index, max);
         }
      }
   }
   catch (DatabaseException | SQLException | IOException | ClassNotFoundException e)
   {
      throw new CustomChangeException("DELETED_PRODUCTS table update failed", e);
   }
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   SimpleDateFormat metaSdf =
         new SimpleDateFormat ("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
   SimpleDateFormat productSdf =
         new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss.SSS");
   String query =
         "SELECT p.ID, m.VALUE, p.INGESTIONDATE " +
               "FROM PRODUCTS p LEFT OUTER JOIN " +
               "METADATA_INDEXES m ON p.ID = m.PRODUCT_ID " +
               "WHERE m.NAME = 'Ingestion Date'";
   try
   {
      JdbcConnection connection = (JdbcConnection) database.getConnection ();
      PreparedStatement statement = connection.prepareStatement (query);
      ResultSet result = statement.executeQuery ();
      while (result.next ())
      {
         Date validIngestionDate = metaSdf.parse (result.getString (2));
         Date ingestionDate = productSdf.parse (result.getString (3));
         long diffMilli =
               validIngestionDate.getTime () - ingestionDate.getTime ();
         long diffHour = diffMilli / (1000 * 60 * 60);
         if (diffHour >= 11.0)
         {
            StringBuilder sb = new StringBuilder ();
            sb.append ("UPDATE PRODUCTS ");
            sb.append ("SET INGESTIONDATE = '").append (
                  productSdf.format (validIngestionDate)).append ("' ");
            sb.append ("WHERE ID = ").append (result.getLong (1));
            PreparedStatement update =
                  connection.prepareStatement (sb.toString ());
            if (update.executeUpdate () != 1)
            {
               LOGGER.warn("Cannot change ingestion date for product#" +
                     result.getLong (1));
            }
         }
      }
      result.close ();
      statement.close ();
   }
   catch (DatabaseException | SQLException | ParseException e)
   {
      throw new CustomChangeException (
            "An error occurred during liquibase execution: ", e);
   }
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   String searchDuplicate = "SELECT USER_ID, ROLES, count(ROLES) " +
         "FROM USER_ROLES " +
         "GROUP BY USER_ID, ROLES " +
         "HAVING count(ROLES) > 1";

   String deleteDuplicate =
         "DELETE FROM USER_ROLES WHERE USER_ID=%d AND ROLES='%s'";

   String resetRole = "INSERT INTO USER_ROLES VALUES(%d, '%s')";

   try
   {
      JdbcConnection jdbc = (JdbcConnection) database.getConnection ();
      PreparedStatement getDuplicate = jdbc.prepareStatement (
            searchDuplicate);
      ResultSet result = getDuplicate.executeQuery ();

      while (result.next ())
      {
         int u = result.getInt (1);
         String r = result.getString (2);

         PreparedStatement delete =
               jdbc.prepareStatement (String.format (deleteDuplicate, u, r));
         delete.executeUpdate ();

         PreparedStatement reset =
               jdbc.prepareStatement (String.format (resetRole, u, r));
         reset.executeUpdate ();
      }
      result.close ();
      getDuplicate.close ();
   }
   catch (DatabaseException | SQLException e)
   {
      LOGGER.error ("An error occurred during removeDuplicationRoles", e);
   }
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
// contentStart contentEnd ingestionDate download.size
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {
      PreparedStatement getProducts =
         databaseConnection
            .prepareStatement ("SELECT ID, QUICKLOOK_PATH, " +
                  "THUMBNAIL_PATH FROM Products");
      ResultSet res = getProducts.executeQuery ();
      while (res.next ())
      {
         Long productId = (Long) res.getObject("ID");
         String thumbnail = (String) res.getObject ("THUMBNAIL_PATH");  
         String quicklook = (String) res.getObject ("QUICKLOOK_PATH");  
         Long tbSize = null;
         Long qlSize = null;
         if (thumbnail != null)
         {
            File tbFile = new File (thumbnail);
            if (tbFile.exists ())
            {
               tbSize = tbFile.length ();
            }
         }
         if (quicklook != null)
         {
            File qlFile = new File (quicklook);
            if (qlFile.exists ())
            {
               qlSize = qlFile.length ();
            }
         }
         
         PreparedStatement updateProduct =
               databaseConnection.prepareStatement ("UPDATE PRODUCTS SET" +
                     " QUICKLOOK_SIZE = " + qlSize +
                     ", THUMBNAIL_SIZE = "+tbSize+" WHERE ID = "+productId);
         updateProduct.execute ();
         updateProduct.close();
      }
      getProducts.close ();
   }
   catch (Exception e)
   {
      LOGGER.error("Error during liquibase update " +
            "'ExtractProductDatesAndDownloadSize'", e);
   }
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {         
      PreparedStatement getSearches =
         databaseConnection.prepareStatement ("SELECT ID FROM SEARCHES");
      ResultSet res = getSearches.executeQuery ();
      while (res.next ())
      {
         String uuid = UUID.randomUUID ().toString ();
         PreparedStatement updateSearch =
            databaseConnection
               .prepareStatement ("UPDATE SEARCHES SET UUID = '" + uuid +
                  "' WHERE ID = " + res.getObject ("ID"));
         updateSearch.execute ();
         updateSearch.close ();

         PreparedStatement updateAdvanced =
            databaseConnection
               .prepareStatement ("UPDATE SEARCH_ADVANCED SET SEARCH_UUID = '" +
                  uuid + "' WHERE SEARCH_ID = " + res.getObject ("ID"));
         updateAdvanced.execute ();
         updateAdvanced.close ();

         PreparedStatement updatePreferences =
            databaseConnection
               .prepareStatement ("UPDATE SEARCH_PREFERENCES SET SEARCHES_UUID = '" +
                  uuid + "' WHERE SEARCHES_ID = " + res.getObject ("ID"));
         updatePreferences.execute ();
         updatePreferences.close ();
      }
      getSearches.close ();
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }
   
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {         
      PreparedStatement getPreferences =
         databaseConnection.prepareStatement ("SELECT ID FROM PREFERENCES");
      ResultSet res = getPreferences.executeQuery ();
      while (res.next ())
      {
         String uuid = UUID.randomUUID ().toString ();
         PreparedStatement updatePreferences =
            databaseConnection
               .prepareStatement ("UPDATE PREFERENCES SET UUID = '" + uuid +
                  "' WHERE ID = " + res.getObject ("ID"));
         updatePreferences.execute ();
         updatePreferences.close ();

         PreparedStatement updateUsers =
            databaseConnection
               .prepareStatement ("UPDATE USERS SET PREFERENCES_UUID = '" +
                  uuid + "' WHERE PREFERENCES_ID = " + res.getObject ("ID"));
         updateUsers.execute ();
         updateUsers.close ();

         PreparedStatement updateFilescanner =
            databaseConnection
               .prepareStatement ("UPDATE FILE_SCANNER_PREFERENCES SET PREFERENCE_UUID = '" +
                  uuid + "' WHERE PREFERENCE_ID = " + res.getObject ("ID"));
         updateFilescanner.execute ();
         updateFilescanner.close ();
         
         PreparedStatement updateSearches =
            databaseConnection
               .prepareStatement ("UPDATE SEARCH_PREFERENCES SET PREFERENCE_UUID = '" +
                  uuid + "' WHERE PREFERENCE_ID = " + res.getObject ("ID"));
         updateSearches.execute ();
         updateSearches.close ();
      }
      getPreferences.close ();
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }
   
}
 

import liquibase.database.jvm.JdbcConnection; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   String rootName = CollectionDao.COLLECTION_ROOT_NAME;
   String rootId = "SELECT UUID FROM COLLECTIONS WHERE NAME='%s'";
   String auth = "DELETE FROM COLLECTION_USER_AUTH WHERE COLLECTIONS_UUID='%s'";
   String delete = "DELETE FROM COLLECTIONS WHERE NAME='%s'";
   try
   {
      String cid = null;
      String sql;
      PreparedStatement statement;
      JdbcConnection connection = (JdbcConnection) database.getConnection ();

      // get root collection id
      sql = String.format (rootId, rootName);
      statement = connection.prepareStatement (sql);
      statement.execute ();
      ResultSet resultSet = statement.getResultSet ();
      if (resultSet.next ())
      {
         cid = resultSet.getString (1);
      }
      statement.close ();

      if (cid != null)
      {
         // remove default authorization on root collection
         sql = String.format (auth, cid);
         statement = connection.prepareStatement (sql);
         statement.execute ();
         statement.close ();

         // delete root collection
         sql = String.format (delete, rootName);
         statement = connection.prepareStatement (sql);
         statement.execute ();
         statement.close ();
      }
   }
   catch (DatabaseException | SQLException e)
   {
      throw new CustomChangeException (e);
   }
}