Prerequisites
You should know how to create services with Service Builder. Also, you should know a little about SQL: the basic syntax and a notion on how it is used inside programming language code.
The steps described here were performed over Liferay 6.0.6.
Service.xml
One part of service.xml :
<entity name="Geschaeft" local-service="true" remote-service="false" table="Geschäfte">
<!-- PK fields -->
<column name="ID" db-name="[ID]" type="int" primary="true" />
<!-- Other fields -->
<column name="Jahr" db-name="[Jahr]" type="Date" />
<column name="Beschaffungsstrategie" db-name="[Beschaffungsstrategie]" type="String" />
<column name="BuchVon" db-name="[Buch von]" type="String" />
<column name="BuchNach" db-name="[Buch nach]" type="String" />
<column name="Transaktionsdatum" db-name="[Transaktionsdatum]" type="Date" />
<column name="Auftragsnummer" db-name="[Auftragsnummer]" type="String" />
<column name="BeschaffungsZeitraumBeginn" db-name="[Besch#Zeitraum Beginn]" type="Date" />
<column name="BeschaffungsZeitraumEnde" db-name="[Besch#Zeitraum Ende]" type="Date" />
<column name="AnkaufVerkauf" db-name="[Ankauf/Verkauf]" type="String" />
<column name="Geschaeftstyp" db-name="[Geschäftstyp]" type="String" />
<column name="Produkt" db-name="[Produkt]" type="String" />
<column name="LaufzeitVon" db-name="[Laufzeit von]" type="Date" />
<column name="LaufzeitBis" db-name="[Laufzeit bis]" type="Date" />
<column name="Sonderprodukt" db-name="[Sonderprodukt]" type="String" />
<column name="KennzeichnungAuftraggeber" db-name="[Kennzeichnung des Auftraggebers]" type="String" />
<column name="Abrechnungsrelevanz" db-name="[Abrechnungsrelevanz]" type="String" />
<column name="FrueherePMSID" db-name="[Frühere PMS-ID oder Nr# Geschäftsimport]" type="String" />
<!-- Order -->
<order by="asc">
<order-column name="Jahr" />
</order>
</entity>
Let us beginning
1. Create a folder named “custom-sql” in source root.
2. Create a file named ” default.xml”, and type the following code. that mean that tell liferay to read others file from here.
<?xml version="1.0"?>
<custom-sql>
<sql file="custom-sql/geschaeft.xml" />
<sql file="custom-sql/otherTable.xml" />
..
..
</custom-sql>
3. Create the .xml file which was added in default.xml. and type the following code.
<?xml version="1.0"?>
<custom-sql>
<sqlid="com.quantum.service.persistence.GeschaeftFinder.getAll">
<![CDATA[
SELECT
*
FROM
Geschäfte
]]>
</sql>
<sql id="com.quantum.service.persistence.GeschaeftFinder.getList">
<![CDATA[
SELECT
CASE WHEN [Buch von] <> 'Standardbuch' THEN [Buch von] ELSE [Buch nach] END AS Portfolio,
[ID],
[Auftragsnummer]
FROM Geschäfte
WHERE CASE WHEN [Buch von] <> 'Standardbuch' THEN [Buch von] ELSE [Buch nach] END IN ('Spotmarkt','Krefeld A')
AND [Frühere PMS-ID oder Nr# Geschäftsimport] NOT LIKE 'Q_%'
AND Produkt <> 'intern_Spot'
AND Produkt <> 'intern_EEG'
]]>
</sql>
</custom-sql>
4. Create a new class named “GeschaeftFinderImpl” in com.quantum.service.persistence package, and extends BasePersistenceImpl, implements GeschaeftFinder which is not exist so far. Don’t care about it. Continue type the following code:
package com.quantum.service.persistence;
import java.util.Iterator;
import java.util.List;
import com.liferay.portal.kernel.dao.orm.QueryUtil;
import com.liferay.portal.kernel.dao.orm.SQLQuery;
import com.liferay.portal.kernel.dao.orm.Session;
import com.liferay.portal.kernel.dao.orm.Type;
import com.liferay.portal.kernel.exception.SystemException;
importcom.liferay.portal.service.persistence.impl.BasePersistenceImpl;
import com.liferay.util.dao.orm.CustomSQLUtil;
import com.quantum.model.Geschaeft;
import com.quantum.model.impl.GeschaeftImpl;
/** *//**
*
* @author noah.xiang
*
*/
public class GeschaeftFinderImpl extendsBasePersistenceImpl<Geschaeft> implements GeschaeftFinder
{
private static String GETLIST = GeschaeftFinder.class.getName() + ".getList";
private static String GETALL = GeschaeftFinder.class.getName() + ".getAll";
public List<Geschaeft> getALL() throws SystemException {
Session session = null;
try {
session = openSession();
System.out.println(">>>>>>>>>>>> "+ GETALL);
String sql = CustomSQLUtil.get(GETLIST);
SQLQuery q = session.createSQLQuery(sql);
//q.addEntity("Geschäfte", GeschaeftImpl.class);
q.addScalar("Portfolio", Type.STRING);
q.addScalar("ID", Type.INTEGER);
q.addScalar("Auftragsnummer", Type.STRING);
Iterator<Object[]> itr = (Iterator<Object[]>)QueryUtil.iterate(q, getDialect(), -1, -1);
while (itr.hasNext()) {
Object[] array = itr.next();
String portfolio = (String)array[0];
int userId = (Integer)array[1];
String auftragsnummer = (String)array[2];
System.out.println(">>>>> "+ userId +" "+ auftragsnummer+ " "+ portfolio);
}
//List<Geschaeft> list = q.list();
return null;
} catch (Exception e) {
throw new SystemException(e);
} finally {
closeSession(session);
}
}
}
5. So far have many errors in your eclipse. But it doesn’t matter. Just ant build-service.xml. liferay will generate relevant code for us.
6. Find GeschaeftLocalServiceImpl class in com.quantum.service.impl package. And create a method named public List<Geschaeft> getAll() throws SystemException. For the method you can rename whatever you want.
public List<Geschaeft> getAll() throws SystemException{
return geschaeftFinder.getALL();
}
7. So far, all of configuration is finish. Go to action to call the method, like this:
List<Geschaeft> geschaeft = GeschaeftLocalServiceUtil.getAll();
8. ant build-service.xml again.
9. Wish you success! Think you.
直接寫的英文版的，不想再翻譯回來了。都不難理解，一看就能明白。不懂的請留言
眼鏡蛇