Author: bpapez
Date: Wed Nov 21 15:29:27 2007
New Revision: 19247
URL: https://svndev.jahia.net/websvn/listing.php?sc=3D1&rev=3D19247&repname=
=3Djahia
Log:
Optimize select from jahia_link table
Modified:
branches/JAHIA-4-1-BRANCH/src/java/org/jahia/content/CrossReferenceMana=
ger.java
branches/JAHIA-4-1-BRANCH/src/java/org/jahia/content/ObjectLinkDB.java
Modified: branches/JAHIA-4-1-BRANCH/src/java/org/jahia/content/CrossReferen=
ceManager.java
URL: https://svndev.jahia.net/websvn/diff.php?path=3D/branches/JAHIA-4-1-BR=
ANCH/src/java/org/jahia/content/CrossReferenceManager.java&rev=3D19247&repn=
ame=3Djahia
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D
--- branches/JAHIA-4-1-BRANCH/src/java/org/jahia/content/CrossReferenceMana=
ger.java (original)
+++ branches/JAHIA-4-1-BRANCH/src/java/org/jahia/content/CrossReferenceMana=
ger.java Wed Nov 21 15:29:27 2007
@@ -80,6 +80,7 @@
public static final String PRELOADED_OBJECT_LINKS_BY_REF_OBJECT_CACHE =
=3D "PreloadedObjectLinksByRefObjectCache";
=
private static final String CONTENT_PAGE_PREFIX =3D "ContentPage";
+ private static final String[] CONTENT_CONTAINERLIST_AND_FIELD_PREFIX =
=3D {"ContentContainerList_%", "ContentField_%"};
=
/** the cross reference cache */
private Cache crossRefTable;
@@ -194,8 +195,9 @@
if (objectSourceKeys.isEmpty()) {
// this could be because the keys were not yet loaded from the
// database, let's make sure we do so if necessary.
- ArrayList rightLinks =3D ObjectLinkDB.getInstance ().
- findByTypeAndLeftObjectKey(REFERENCE_TYPE, objectXRef);
+ ArrayList rightLinks =3D ObjectLinkDB.getInstance()
+ .findByTypeAndLeftAndLikeRightObjectKeys(REFERENCE_TYP=
E,
+ objectXRef, CONTENT_CONTAINERLIST_AND_FIELD_PR=
EFIX);
for (Iterator rightLinksIter =3D rightLinks.listIterator(); ri=
ghtLinksIter.hasNext();) {
ObjectLink curLink =3D (ObjectLink)rightLinksIter.next ();
objectSourceKeys.add (curLink.getRightObjectKey ());
Modified: branches/JAHIA-4-1-BRANCH/src/java/org/jahia/content/ObjectLinkDB=
.java
URL: https://svndev.jahia.net/websvn/diff.php?path=3D/branches/JAHIA-4-1-BR=
ANCH/src/java/org/jahia/content/ObjectLinkDB.java&rev=3D19247&repname=3Djah=
ia
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D
--- branches/JAHIA-4-1-BRANCH/src/java/org/jahia/content/ObjectLinkDB.java =
(original)
+++ branches/JAHIA-4-1-BRANCH/src/java/org/jahia/content/ObjectLinkDB.java =
Wed Nov 21 15:29:27 2007
@@ -9,6 +9,7 @@
import java.util.ArrayList;
import java.util.HashMap;
=
+import org.apache.lucene.analysis.LetterTokenizer;
import org.jahia.exceptions.JahiaException;
import org.jahia.registries.ServicesRegistry;
import org.jahia.services.database.ConnectionDispenser;
@@ -439,89 +440,95 @@
=
return resultList;
}
+ =
+ protected ArrayList findByTypeAndLeftAndLikeRightObjectKeys(String typ=
e,
+ ObjectKey leftObjectKey, String likeRightObjectKey)
+ throws JahiaException {
+ return findByTypeAndLeftAndLikeRightObjectKeys(type, leftObjectKey,
+ new String[] { likeRightObjectKey });
+ }
=
- protected ArrayList findByTypeAndLeftAndLikeRightObjectKeys (String ty=
pe,
- ObjectKey leftObjectKey,
- String likeRightObjectKey)
+ protected ArrayList findByTypeAndLeftAndLikeRightObjectKeys(String typ=
e,
+ ObjectKey leftObjectKey, String[] likeRightObjectKeys)
throws JahiaException {
=
- ArrayList resultList =3D new ArrayList();
- Connection dbConn =3D null;
- PreparedStatement stmt =3D null;
- ResultSet rs =3D null;
-
- final String selectFields =3D "id," +
- "left_oid," +
- "right_oid," +
- "type," +
- "status," +
- "creation_date," +
- "creation_user," +
- "lastmodif_date," +
- "lastmodif_user";
-
- try {
- String sqlQuery =3D "SELECT " +
- selectFields +
- " FROM jahia_link WHERE type=3D? AND lef=
t_oid=3D? AND right_oid like ?";
-
- dbConn =3D org.jahia.services.database.ConnectionDispenser=
.getConnection();
- stmt =3D dbConn.prepareStatement(sqlQuery);
- stmt.setString(1, type);
- stmt.setString(2, leftObjectKey.toString());
- stmt.setString(3, likeRightObjectKey.toString());
- // rs can't be null (cf. api)
- rs =3D stmt.executeQuery();
-
- while (rs.next()) {
- int id =3D rs.getInt("id");
- String leftOID =3D rs.getString("left_oid");
- String rightOID =3D rs.getString("right_oid");
- int status =3D rs.getInt("status");
- Timestamp creationDate =3D rs.getTimestamp("creation_d=
ate");
- String creationUserKey =3D rs.getString("creation_user=
");
- Timestamp lastModificationDate =3D rs.getTimestamp(
- "lastmodif_date");
- String lastModificationUserKey =3D rs.getString("lastm=
odif_user");
-
- try {
- resultList.add(new ObjectLink(id,
- ObjectKey.getInstanc=
e(leftOID),
- ObjectKey.getInstanc=
e(rightOID),
- type,
- status,
- creationDate, creati=
onUserKey,
- lastModificationDate,
- lastModificationUser=
Key,
- new HashMap(),
- new HashMap(), new H=
ashMap()));
- } catch (ClassNotFoundException cnfe) {
- logger.error("Couldn't create instance of an Objec=
tKey",
- cnfe);
- }
- }
-
- } catch (SQLException se) {
- throw new JahiaException(
- "Cannot load object link by right object key " + likeR=
ightObjectKey +
- "from the database",
- se.getMessage(),
- JahiaException.DATABASE_ERROR,
- JahiaException.ERROR_SEVERITY, se);
- } finally {
+ ArrayList resultList =3D new ArrayList();
+ Connection dbConn =3D null;
+ PreparedStatement stmt =3D null;
+ ResultSet rs =3D null;
+
+ final String selectFields =3D "id," + "left_oid," + "right_oid,"
+ + "type," + "status," + "creation_date," + "creation_user,"
+ + "lastmodif_date," + "lastmodif_user";
+
+ try {
+ StringBuffer sqlQuery =3D new StringBuffer(128);
+ sqlQuery.append("SELECT ").append(selectFields).append(
+ " FROM jahia_link WHERE type=3D? AND left_oid=3D? AND =
(");
+
+ for (int i =3D 0; i < likeRightObjectKeys.length; i++) {
+ if (i !=3D 0) {
+ sqlQuery.append(" or ");
+ }
+ sqlQuery.append("right_oid like ?");
+ }
+ sqlQuery.append(")");
+
+ dbConn =3D org.jahia.services.database.ConnectionDispenser
+ .getConnection();
+ stmt =3D dbConn.prepareStatement(sqlQuery.toString());
+ int paramCounter =3D 1;
+ stmt.setString(paramCounter++, type);
+ stmt.setString(paramCounter++, leftObjectKey.toString());
+ for (int i =3D 0; i < likeRightObjectKeys.length; i++) {
+ stmt.setString(paramCounter++, likeRightObjectKeys[i]);
+ }
+ // rs can't be null (cf. api)
+ rs =3D stmt.executeQuery();
+
+ while (rs.next()) {
+ int id =3D rs.getInt("id");
+ String leftOID =3D rs.getString("left_oid");
+ String rightOID =3D rs.getString("right_oid");
+ int status =3D rs.getInt("status");
+ Timestamp creationDate =3D rs.getTimestamp("creation_date"=
);
+ String creationUserKey =3D rs.getString("creation_user");
+ Timestamp lastModificationDate =3D rs
+ .getTimestamp("lastmodif_date");
+ String lastModificationUserKey =3D rs.getString("lastmodif=
_user");
+
try {
- if (stmt !=3D null)
- stmt.close();
- } catch (SQLException ex) {
- throw new JahiaException("Cannot free resources",
- "Cannot free resources",
- JahiaException.DATABASE_ERROR,
- JahiaException.WARNING_SEVERI=
TY, ex);
+ resultList.add(new ObjectLink(id, ObjectKey
+ .getInstance(leftOID), ObjectKey
+ .getInstance(rightOID), type, status, creation=
Date,
+ creationUserKey, lastModificationDate,
+ lastModificationUserKey, new HashMap(),
+ new HashMap(), new HashMap()));
+ } catch (ClassNotFoundException cnfe) {
+ logger.error("Couldn't create instance of an ObjectKey=
",
+ cnfe);
}
}
=
- return resultList;
- } =
+ } catch (SQLException se) {
+ throw new JahiaException(
+ "Cannot load object link by right object key "
+ + likeRightObjectKeys + "from the database", se
+ .getMessage(), JahiaException.DATABASE_ERROR,
+ JahiaException.ERROR_SEVERITY, se);
+ } finally {
+ try {
+ if (stmt !=3D null)
+ stmt.close();
+ } catch (SQLException ex) {
+ throw new JahiaException("Cannot free resources",
+ "Cannot free resources", JahiaException.DATABASE_E=
RROR,
+ JahiaException.WARNING_SEVERITY, ex);
+ }
+ }
+
+ return resultList;
+ } =
=
protected ArrayList findByTypeAndLeftAndRightObjectKeys (String type,
ObjectKey leftObjectKey,
_______________________________________________
cvs_list mailing list
[email protected]
http://lists.jahia.org/cgi-bin/mailman/listinfo/cvs_list
