All these scripts are intended as templates for something specific to your particular project. They demonstrate that actions such as restarting the server are possible in Hybris via Groovy and show the general 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 parameters, which HAC doesn’t show to the administrator.

The second script shows all Hybris types that use a particular type.

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

• PART 1 /2017/11/23/useful-groovy-scripts-part-1/
  • Display any file on the server
  • Execute a command
  • Show last N lines from the console log
  • Execute a Flexible Search query
  • Retrieve an item by PK
  • Execute a raw SQL query
  • Modifying objects
  • Removing items
  • Sending data by e-mail
  • Importing IMPEX
  • Print all properties of an object
  • Print all methods of an object
  • Show the Hybris type tree
  • Print Hybris type stats
• PART 2 /2017/12/02/useful-groovy-scripts-part-2/
  • Turn logging on/off for any class
  • Print a bean list from a particular context
  • Execute a method from the page controller bean
  • Print all URLs from all controllers (RequestMappings)
  • Print all web contexts
• PART 3 /2017/12/08/useful-groovy-scripts-flexible-search-query-generator-for-any-type/
  • Flexible Search Generator
• PART 4 /2017/12/17/useful-groovy-scripts-part-4-external-references/
  • Finding all external references to an object (EARLY BETA)
• PART 5. /2018/01/17/groovy-scripts-for-hybris-part-5/
  • Restart Hybris server
  • Converting FlexibleQuery to SQL
  • All Hybris types that use a particular type
  • Executing SQL (DDL)
  • Print MySQL table structure from Hybris HAC
  • Print all indexes

PART 5

Restart a 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 a 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 "";
}
}