Useful Groovy Scripts. Part 5.

All these scripts are used as templates for something specific to your particular project. They just demonstrate that something like restarting the server is possible to do with Hybris via Groovy and show the approach.

The first script is used to convert FlexibleSearch queries into raw SQL. Hybris HAC can convert it, but the result is not 100% ready: it contains question marks for the internal params which HAC doesn’t show to the administrator.

The second script shows all hybris types that use the particular type.

The third one will help you with executing DDL queries on the database server remotely (such as ALTER TABLE or CREATE INDEX, for example). I also provide the script for printing the database table structure and the indexes defined for it.

PART 5

Restart hybris server from HAC

 de.hybris.platform.jmx.JmxClient.restartWrapper(new File('../../../../data/hybristomcat.java.pid').text as Integer);
 

Generating SQL for Flexible Search

query="SELECT count(a.pk) AS itemcount, a.code AS itemtype FROM "+
"({{SELECT {i:pk} AS pk, {c:code} AS code FROM {Item AS i},"+
"{ComposedType AS c} WHERE {i:itemtype}={c:pk}}}) a GROUP BY a.code ORDER BY code";

import de.hybris.platform.servicelayer.search.FlexibleSearchQuery;
import de.hybris.platform.persistence.property.JDBCValueMappings;
import de.hybris.platform.core.Registry;
import com.mysql.jdbc.JDBC4PreparedStatement;
import de.hybris.platform.jdbcwrapper.PreparedStatementImpl;

flexibleSearchService = spring.getBean("flexibleSearchService");
flexibleSearchQuery = new FlexibleSearchQuery(query);
params = flexibleSearchService.translate(flexibleSearchQuery).getSQLQueryParameters();
sql = flexibleSearchService.translate(flexibleSearchQuery).getSQLQuery();
con = Registry.getMasterTenant().getDataSource().getConnection();
preparedStatement = con.prepareStatement(sql);
JDBCValueMappings.getInstance().fillStatement(preparedStatement, params);
stringRepresentationOfPreparedStatement=preparedStatement.getPrepStmtPassthruString();
sql = (stringRepresentationOfPreparedStatement =~ /^(.*?)\: (.*?)$/)[0][2]

All hybris types that use the particular type

import de.hybris.platform.hac.data.dto.SqlSearchResultData;
import de.hybris.platform.hac.facade.impl.DefaultFlexibleSearchFacade;
import de.hybris.platform.core.PK;
TYPE="Media"
typeService = spring.getBean("typeService");
typePK = typeService.getTypeForCode(TYPE).getPk();

SqlSearchResultData searchResult;
query =
  "select EnclosingTypePK, QualifierInternal from attributedescriptors where AttributeTypePK = '"+typePK + "'";
  ;
flexibleSearchFacade = new DefaultFlexibleSearchFacade();
result = flexibleSearchFacade.executeRawSql(query, 2000000, false);

for (item in result.getResultList()) {
  enclosingTypePk = item[0];
  enclosingType = modelService.get(new PK(enclosingTypePk as Long));
  println (enclosingType.getCode() + "." + item[1]);
}

Executing SQL

import de.hybris.platform.core.Registry;
conn = Registry.getCurrentTenant().getDataSource().getConnection();
stmt = conn.createStatement();
alterTableQuery = "alter table...";
abc = stmt.executeUpdate(alterTableQuery)

Print mysql table structure from hybris HAC

import de.hybris.platform.core.Registry;
conn = Registry.getCurrentTenant().getDataSource().getConnection();
println conn
stmt = conn.createStatement();
query = "desc genericitems";
rs = stmt.executeQuery(query);
while (rs.next()) {
  String field = rs.getString("Field");
  String type = rs.getString("type");
  String nullinfo = rs.getString("null");
  String key = rs.getString("key");
  String extra = rs.getString("extra");
  println field+"\t"+type+"\t"+nullinfo+"\t"+key+"\t"+extra;
}

Print all indexes

import de.hybris.platform.core.Registry;
conn = Registry.getCurrentTenant().getDataSource().getConnection();
println conn
stmt = conn.createStatement();
stmt2 = conn.createStatement();
query = "show tables";
rs = stmt.executeQuery(query);
while (rs.next()) {
   table = rs.getString(1);
   getIndexesQuery = "SHOW INDEX FROM "+table;
   println getIndexesQuery
   indexesResult = stmt2.executeQuery(getIndexesQuery);
   while (indexesResult.next()) {
     for (i=1; i<=indexesResult.getMetaData().getColumnCount();i++) {
      print "\t"+indexesResult.getString(i);
     }
     println "";
   }
}

One comment

  1. choukoukouamine · · Reply

    Very interesting, thank you very much for sharing.

    Just a note, you do not need to declare a bean as a variable and retrieve it from the context, just use it with its alias or bean name as a variable.
    For example :
    instead of
    $ typeService = spring.getBean (“serviceType”);
    $ typePK = typeService.getTypeForCode (TYPE) .getPk ();
    Just:
    $ typePK = typeService.getTypeForCode (TYPE) .getPk ();

    This is very useful if we want to get the class of a bean if we want to know if we have the class we want.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: