Java Database.getConnection方法代码示例

import liquibase.database.Database; //导入方法依赖的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.Database; //导入方法依赖的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.Database; //导入方法依赖的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.Database; //导入方法依赖的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.Database; //导入方法依赖的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.Database; //导入方法依赖的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.Database; //导入方法依赖的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.Database; //导入方法依赖的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.Database; //导入方法依赖的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.Database; //导入方法依赖的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.Database; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   try
   {
      JdbcConnection jdbc = (JdbcConnection) database.getConnection ();
      String sql;
      Statement statement;
      ResultSet resultSet;

      // Retrieve unencrypted user password
      sql = "SELECT LOGIN, PASSWORD FROM USERS " +
            "WHERE PASSWORD_ENCRYPTION = 'NONE'";
      statement = jdbc.createStatement ();
      HashMap<String, String> unencrypted_user = new HashMap<> ();
      resultSet = statement.executeQuery (sql);
      while (resultSet.next ())
      {
         unencrypted_user.put (resultSet.getString ("LOGIN"),
               resultSet.getString ("PASSWORD"));
      }
      resultSet.close ();
      statement.close ();

      // Encrypt user password and update user
      MessageDigest md = MessageDigest.getInstance ("MD5");
      sql = "UPDATE USERS SET PASSWORD_ENCRYPTION = 'MD5', PASSWORD = '%s'" +
            " WHERE LOGIN = '%s'";
      String query;
      String password;
      for (String login : unencrypted_user.keySet ())
      {
         password = unencrypted_user.get (login);
         password = new String (
               Hex.encode (md.digest (password.getBytes ("UTF-8"))));
         query = String.format (sql, password, login);
         statement = jdbc.createStatement ();
         int updated =  statement.executeUpdate (query);
         if (updated != 1)
         {
            LOGGER.warn(updated + " encryption update perform on user : " + login);
         }
         statement.close ();
      }
      unencrypted_user.clear ();
   }
   catch (Exception e)
   {
      throw new CustomChangeException (
            "An error occurred during forceEncryptPassword changelog", e);
   }
}
 

import liquibase.database.Database; //导入方法依赖的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);
   }
}
 

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

         PreparedStatement updateCollectionProducts =
            databaseConnection
               .prepareStatement ("UPDATE COLLECTION_PRODUCT SET COLLECTIONS_UUID = '" +
                  uuid + "' WHERE COLLECTIONS_ID = " + res.getObject ("ID"));
         updateCollectionProducts.execute ();
         updateCollectionProducts.close ();

         PreparedStatement updateCollectionuser =
            databaseConnection
               .prepareStatement ("UPDATE COLLECTION_USER_AUTH SET COLLECTIONS_UUID = '" +
                  uuid + "' WHERE COLLECTIONS_ID = " + res.getObject ("ID"));
         updateCollectionuser.execute ();
         updateCollectionuser.close ();
         
         PreparedStatement updateCollectionFileScanner =
            databaseConnection
               .prepareStatement ("UPDATE FILESCANNER_COLLECTIONS SET COLLECTIONS_UUID = '" +
                  uuid + "' WHERE COLLECTIONS_ID = " + res.getObject ("ID"));
         updateCollectionFileScanner.execute ();
         updateCollectionFileScanner.close ();
      }
      getCollections.close ();         
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }
   
}
 

import liquibase.database.Database; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {
      PreparedStatement getUsers =
            databaseConnection.prepareStatement (
                        "SELECT USER_ID,ROLES FROM USER_ROLES " +
                              "WHERE ROLES = 'DATARIGHT_MANAGER' OR " +
                              "ROLES = 'COLLECTION_MANAGER'");
       ResultSet res = getUsers.executeQuery ();
       Object prevId = null;
       while (res.next ())
       {
          // if we found two times the same user ID, user is DATARIGHT 
          // and COLLECTION manager. We can remove the last one to do
          // not create two times DATA MANAGER role for user.
          if (prevId == res.getObject ("USER_ID"))
          {
             PreparedStatement removeRole =
                databaseConnection
                   .prepareStatement ("DELETE FROM USER_ROLES "+
                      "WHERE ROLES = 'COLLECTION_MANAGER' AND USER_ID = " +
                         res.getObject ("USER_ID"));
             removeRole.execute ();
             removeRole.close ();
          }
          prevId = res.getObject ("USER_ID");
       }
               
      PreparedStatement updateRole =
         databaseConnection
            .prepareStatement ("UPDATE USER_ROLES SET ROLES = 'DATA_MANAGER"+
               "' WHERE ROLES = 'DATARIGHT_MANAGER' OR " +
                  "ROLES = 'COLLECTION_MANAGER'");
      updateRole.execute ();
      updateRole.close ();
      getUsers.close ();
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }
}
 

import liquibase.database.Database; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {
      PreparedStatement getUsers =
         databaseConnection
            .prepareStatement ("SELECT ID, LOGIN, COUNTRY FROM USERS");
      ResultSet res = getUsers.executeQuery ();  
      
      while (res.next ())
      {
         String country = (String) res.getObject ("COUNTRY");
         String country2 = country.replaceAll(
               "[-\\[(){},.;!?><_|\\/%*^$\\]]", "");
         String found = null;
         
         PreparedStatement getCountries=databaseConnection.prepareStatement(
            "SELECT NAME, ALPHA2, ALPHA3 FROM COUNTRIES");
         ResultSet countries = getCountries.executeQuery ();
         while (countries.next ())
         {
            String ref = (String) countries.getObject ("NAME");
            String a2 = (String) countries.getObject ("ALPHA2");
            String a3 = (String) countries.getObject ("ALPHA3");
            if (ref.toLowerCase ().equals (country.toLowerCase ()) || 
                 a2.toLowerCase ().equals (country.toLowerCase ()) || 
                 a3.toLowerCase ().equals (country.toLowerCase ()) || 
                 ref.toLowerCase ().equals (country2.toLowerCase ()) || 
                 a2.toLowerCase ().equals (country2.toLowerCase ()) || 
                 a3.toLowerCase ().equals (country2.toLowerCase ()))
            {
               found = ref;
               break;
            }
         }
         if (found != null)
         {
            PreparedStatement updateUser = databaseConnection.
               prepareStatement("UPDATE USERS SET COUNTRY=? WHERE ID=?");
            
            updateUser.setString (1, found);
            updateUser.setLong (2, (Long)res.getObject ("ID"));
            
            updateUser.execute ();
            updateUser.close ();
         }
         else
         {
            LOGGER.warn("Unknown country for '"+res.getObject ("LOGIN")+"' : "+country);
         }
         getCountries.close ();
      }
      getUsers.close ();
   }
   catch (Exception e)
   {
      LOGGER.error(
            "Error during liquibase update 'TransformUserCountry'", e);
   }
}