ResultSet to Object Mapper
sebenarnya klo di NHibernate ada subselect seperti di java gak bakalan buat yg ginian sih.
kemaren pas kerja dengan Java aku ada belajar bahwa sql query result dapat langsung di map ke object. kira2 mappingnya seperti ini.
<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Welly.Exp.NHSubselect.ViewModel" namespace="Welly.Exp.NHSubselect.ViewModel"> <class name="StockCardSnapshot" lazy="false"> <subselect> select sc.scid, sc.partid, prt.prtname, prc.prcid, prc.prcname, prt.prtgroupid, prg.prgname, loc.locationid, loc.locname, sc.scavailable, sc.scbookingstock, sc.scbalance, uom.uomname from tblstockcard sc inner join tblpart prt on sc.partid = prt.partid inner join ....... </subselect> <synchronize table="tblstockcard"/> <synchronize table="tblpart"/> ..... <id name="ID" column="scid"/> <property name="Part" column="partid" type="String"/> <property name="Principal" column="prcname" type="String"/> ... </class> </hibernate-mapping>
tapi aku coba jalanin di .NET. katanya subselect gak dikenalin di xml parser nya. Beh. Sial.
Akhirnya aku coba buatin yg sederhana untuk ngemap result set tersebut. Aku make AdoTemplate dari Spring .NET untuk ngemap plus Reflection.
public class QueryObjectMapper : IQueryObjectMapper { public AdoTemplate AdoTemplate { get; set; } public IList<T> Map<T>(string query) where T : IViewModel, new() { return AdoTemplate.QueryWithRowMapperDelegate<T>(CommandType.Text, query, (dataReader, rowNum) => { T t = new T(); var properties = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public); foreach (var property in properties) { property.SetValue(t, dataReader[property.Name] is DBNull ? null : dataReader[property.Name], null); } return t; }); } }
Karena aku pengemar berat dari Convention over Configuration maka konvensi yg ditetapkan adalah untuk setiap property yg ada di kelas tersebut akan disesuaikan dengan field yg ada di database. sehingga untuk object view model berikut
public class StockCardSnapshot : IViewModel { public long ID { get; set; } public long PartId { get; set; } public string Part { get; set; } public string Principal { get; set; } public string PartGroup { get; set; } }
querynya disesuaikan dengan aliasing field dari view
select sc.scid as ID, sc.partid as PartId, prt.prtname as Part, prc.prcname as Principal, prg.prgname as PartGroup, .....
begini cara menggunakan mapper tersebut
[Test] public void Should_return_object_with_mapper() { string sql = @"select sc.scid as ........ IQueryObjectMapper queryObjectMapper = (QueryObjectMapper)ContextRegistry.GetContext().GetObject("QueryObjectMapper"); IList<StockCardSnapshot> stockCardSnapshot = queryObjectMapper.Map<StockCardSnapshot>(sql); foreach (var stockCard in stockCardSnapshot) { Console.WriteLine("{0} : {1}", stockCard.Part, stockCard.PartId); } }
configurasi di spring nya
<db:provider id="DbProvider" provider="Npgsql-2.0" connectionString="Server=localhost;Database=test;uid=user;pwd=pwd;Timeout=1024;CommandTimeout=3000;"/> <object id="AdoTemplate" type="Spring.Data.Generic.AdoTemplate, Spring.Data" singleton="false" autowire="autodetect"/> <object id="QueryObjectMapper" type="Welly.Exp.Infra.Data.QueryObjectMapper, Welly.Exp.Infra.Data" singleton="false" autowire="autodetect"/>
mudah2an dapat bermanfaat
referensi AdoTemplate dapat ditemukan di sini
http://www.springframework.net/docs/1.3.0/reference/html/ado.html