verwendetes Datenbanksystem: MySQL 5
Ich hab folgende Tabelle
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Ox.WireMon.Lib.Database.Objects"
assembly="WireMonLib">
<class name="ObjTraffic" table="wm_traffic">
<id name="Id" column="id" type="Int32">
<generator class="identity"/>
</id>
<property name="Direction" column="dir" type="Int32" />
<property name="DestinationAddress" column="dst" type="String" />
<property name="LocalAddress" column="lcl" type="String" />
<property name="DestinationPort" column="dst_port" type="Int32" />
<property name="DataLength" column="length" type="Int32" />
<property name="TimeStamp" column="timestamp" type="DateTime" />
</class>
</hibernate-mapping>
und würde gerne folgende query drauf anwenden:
SELECT
YEAR(timestamp) as year,
SUM( IF(direction=1, length, 0) ) as Upload,
SUM( IF(direction=2, length, 0) ) as Download,
SUM( length ) as Total
FROM wm_traffic
GROUP BY year
hab nur keinen blassen wie ich da das Icriteria object füttern muss.
Hallo,
was waren denn bisher Deine Versuche Dein Problem zu loesen bzw. was verstehst Du genau nicht? Du hast ja schon ein das entsprechende Interface gefunden. Hast Du in Doku geschaut, wo diverse Beispiele mit der Klasse "cat" gezeigt werden?
Z.B.:
IList cats = sess.CreateCriteria(typeof(Cat))
.Add( Expression.In( "Name", new String[] { "Fritz", "Izi", "Pk" } ) )
.Add( Expression.Disjunction()
.Add( Expression.IsNull("Age") )
.Add( Expression.Eq("Age", 0 ) )
.Add( Expression.Eq("Age", 1 ) )
.Add( Expression.Eq("Age", 2 ) )
) )
.List();
There are quite a range of built-in criterion types (Expression subclasses), but one that is especially useful lets you specify SQL directly.
// Create a string parameter for the SqlString below
IList cats = sess.CreateCriteria(typeof(Cat))
.Add( Expression.Sql("lower({alias}.Name) like lower(?)", "Fritz%", NHibernateUtil.String .List();
Gruss, DaMoe
Mit der Criteria API bist du meiner Meinung nach auf dem Holzweg, das wird so nicht möglich sein. (Ich bin aber mit dem Criteria-Zeugs nicht so vertraut)
Ich würde das ganze über einen View oder eine named Query lösen.
ich versteh folgendes nicht.
Mein ObjTraffic Object hat kennt ja nur seine Properties Dirction, DestinationAddress, LocalAddress, DestinationPort, DataLength, TimeStamp
Meine Query verlangt ja aber schon die "Properties" Download, Upload und Total. Da ich .NET 2 benutzte, ist mir z.B. da schon nicht klar, wie ich später diese Properties ansprech; außerdem weiß ich auch nicht, wie ich z.B. SUM( IF(direction=1, length, 0) ) as Upload im Select angebe. Normalerweise hab ich immer nur SUM z.b. im WHERE clause eingesetzt.
über das gemappte Objekt kannst du das auch nicht ansprechen. Das ist eine 1:1-Abbildung deiner Tabelle, d.h. deine Ergebnisspalten sind (relativ) fix.
Du müsstest ein neues Objekt erstellen was die Ergebnisspalten der Query als Eigenschaften hat.
danke, das hilft mir jetzt weiter.
so ganz versteh ich es ja nicht.
hab jetzt die query angepasst
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Ox.WireMon.Lib.Database.Objects"
assembly="WireMonLib">
<class name="ObjTraffic" table="wm_traffic">
<id name="Id" column="id" type="Int32">
<generator class="identity"/>
</id>
<property name="Direction" column="dir" type="Int32" />
<property name="DestinationAddress" column="dst" type="String" />
<property name="LocalAddress" column="lcl" type="String" />
<property name="DestinationPort" column="dst_port" type="Int32" />
<property name="DataLength" column="length" type="Int32" />
<property name="TimeStamp" column="timestamp" type="DateTime" />
</class>
<sql-query name="trafficSummary">
<return alias="trafficSummary" class="ObjTrafficSummary" />
<![CDATA[
SELECT
YEAR( timestamp ) AS trafficSummary.Year,
MONTH( timestamp ) AS trafficSummary.Month,
DAY( timestamp ) AS trafficSummary.Day,
SUM( IF(direction=1, length, 0) ) AS trafficSummary.Upload,
SUM( IF(direction=2, length, 0) ) AS trafficSummary.Download,
SUM( length ) AS trafficSummary.Total
FROM wm_traffic trafficSummary
GROUP BY trafficSummary.Day
]]>
</sql-query>
</hibernate-mapping>
und ne neue klasse geschrieben.
namespace Ox.WireMon.Lib.Database.Objects
{
public class ObjTrafficSummary
{
private long m_upload, m_download, m_total;
private int m_day, m_month, m_year;
public ObjTrafficSummary() { }
public virtual int Day
{
get { return m_day; }
set { m_day = value; }
}
public virtual int Month
{
get { return m_month; }
set { m_month = value; }
}
public virtual int Year
{
get { return m_year; }
set { m_year = value; }
}
public virtual long Upload
{
get { return m_upload; }
set { m_upload = value; }
}
public virtual long Download
{
get { return m_download; }
set { m_download = value; }
}
public virtual long Total
{
get { return m_total; }
set { m_total = value; }
}
}
}
wenn ich jetzt die query ausführe, bekomm ich
NHibernate.MappingException: No persister for: Ox.WireMon.Lib.Database.Objects.ObjTrafficSummary, WireMonLib, Version=1.0.0.0, Culture
neutral, PublicKeyToken=null
at NHibernate.Impl.SessionFactoryImpl.GetEntityPersister(String className, Boolean throwIfNotFound)
at NHibernate.Impl.SessionFactoryImpl.GetEntityPersister(String className)
at NHibernate.Loader.Custom.SQLQueryReturnProcessor.GetSQLLoadable(String entityName)
at NHibernate.Loader.Custom.SQLQueryReturnProcessor.ProcessRootReturn(SQLQueryRootReturn rootReturn)
at NHibernate.Loader.Custom.SQLQueryReturnProcessor.ProcessReturn(ISQLQueryReturn rtn)
at NHibernate.Loader.Custom.SQLQueryReturnProcessor.Process()
at NHibernate.Loader.Custom.SQLCustomQuery..ctor(ISQLQueryReturn[] queryReturns, String sqlQuery, ICollection additionalQuerySpaces, ISessionFactoryImplemen
or factory)
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List[T](NativeSQLQuerySpecification spec, QueryParameters queryParameters)
at NHibernate.Impl.SqlQueryImpl.List[T]()
obwohl die Klasse in dem Assembly enthalten ist
habs jetzt; fürs archiv:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Ox.WireMon.Lib.Database.Objects"
assembly="WireMonLib">
<class name="ObjTraffic" table="wm_traffic">
<id name="Id" column="id" type="Int32">
<generator class="identity"/>
</id>
<property name="Direction" column="dir" type="Int32" />
<property name="DestinationAddress" column="dst" type="String" />
<property name="LocalAddress" column="lcl" type="String" />
<property name="DestinationPort" column="dst_port" type="Int32" />
<property name="DataLength" column="length" type="Int32" />
<property name="TimeStamp" column="timestamp" type="DateTime" />
</class>
<class name="ObjTrafficSummary">
<composite-id>
<key-property name="Year" type="Int32" />
<key-property name="Month" type="Int32" />
<key-property name="Day" type="Int32" />
<key-property name="Upload" type="Int64" />
<key-property name="Download" type="Int64" />
<key-property name="Total" type="Int64" />
</composite-id>
</class>
<sql-query name="trafficSummary">
<return alias="ts" class="ObjTrafficSummary" />
<![CDATA[
SELECT
YEAR( ts.timestamp ) AS Year,
MONTH( ts.timestamp ) AS Month,
DAY( ts.timestamp ) AS Day,
SUM( IF(ts.dir=0, ts.length, 0) ) AS Upload,
SUM( IF(ts.dir=1, ts.length, 0) ) AS Download,
SUM( ts.length ) AS Total
FROM wm_traffic ts
GROUP BY Day
]]>
</sql-query>
</hibernate-mapping>
namespace Ox.WireMon.Lib.Database.Objects
{
public class ObjTrafficSummary
{
private long m_upload, m_download, m_total;
private int m_day, m_month, m_year;
public ObjTrafficSummary() { }
public virtual int Day
{
get { return m_day; }
set { m_day = value; }
}
public virtual int Month
{
get { return m_month; }
set { m_month = value; }
}
public virtual int Year
{
get { return m_year; }
set { m_year = value; }
}
public virtual long Upload
{
get { return m_upload; }
set { m_upload = value; }
}
public virtual long Download
{
get { return m_download; }
set { m_download = value; }
}
public virtual long Total
{
get { return m_total; }
set { m_total = value; }
}
public override bool Equals(object obj)
{
return this.GetHashCode() == obj.GetHashCode();
}
public override int GetHashCode()
{
return string.Format(
"{0}.{1}.{2}:{3}.{4}.{5}",
this.Year, this.Month, this.Day,
this.Upload, this.Download, this.Total
).GetHashCode();
}
}
}