|
I am sorry but I don't have the time to distill everything to a nice
example. My guess is that it easy to replicate by following my
essential (possibly poor) technique which is that the setter for one of
my resultClass properties makes another call to the db via iBATIS (see
the setGeneKey method in GeneExperimentResult.java). As I read more about iBATIS, I realize the more conventional way to accomplish what I want is to use a resultMap with Complex Properties such that a returned foreignKey to another class retrieves that class. I did not do so because I think it is more complicated, particularly given that I am not using any resultMaps since my column names and bean names match exactly. Brandon Goodin wrote: Please post your code involved in producing this. Post sqlmap config, dao java code, and bean java code. -- Peter Andrews Software Engineer Dartmouth Medical School Computational Genetics Rubin 707 (603) 653-3598 [EMAIL PROTECTED] |
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd";> <sqlMap namespace="GeneDataset"> <!-- Use type aliases to avoid typing the full classname every time. --> <typeAlias alias="Dataset" type="org.epistasis.db.geneDataset.Dataset"/> <typeAlias alias="DatasetExperiment" type="org.epistasis.db.geneDataset.DatasetExperiment"/> <typeAlias alias="GeneExperimentResult" type="org.epistasis.db.geneDataset.GeneExperimentResult"/> <typeAlias alias="GeneKey" type="org.epistasis.db.geneDataset.GeneKey"/> <typeAlias alias="Researcher" type="org.epistasis.db.geneDataset.Researcher"/> <!-- FUNCTIONS --> <statement id="setDataset" parameterClass="Dataset" resultClass="Integer"> SELECT setDataset( #submitterResearcherId:NUMERIC#, #title:VARCHAR#, #description:VARCHAR#, #externalLink:VARCHAR#, #inputFileHeader:VARCHAR#, #geneKeyColumnName:VARCHAR# ) </statement> <statement id="setDatasetExperiment" parameterClass="Map" resultClass="Integer"> SELECT setDatasetExperiment( #pDatasetId:NUMERIC#, #pColumnName:VARCHAR#, #pDescription:VARCHAR#, #pExternalLink:VARCHAR# ) </statement> <statement id="setGeneExperimentResult" parameterClass="Map" resultClass="Integer"> SELECT setGeneExperimentResult( #pGeneKeyId:NUMERIC#, #pDatasetExperimentId:NUMERIC#, #pGeneExperimentResultValue:VARCHAR#) </statement> <statement id="setGeneKey" parameterClass="String" resultClass="Integer"> SELECT setGeneKey(#pGeneKey:VARCHAR#) </statement> <!-- PROCEDURES --> <statement id="getAllResearchers" resultClass="Researcher"> CALL getAllResearchers() </statement> <statement id="getDatasetExperimentTitlesByDatasetId" parameterClass="Integer" resultClass="String"> CALL getDatasetExperimentTitlesByDatasetId(#pDatasetId:NUMERIC#) </statement> <statement id="getDatasetExperimentsByDatasetId" parameterClass="Integer" resultClass="DatasetExperiment"> CALL getDatasetExperimentsByDatasetId(#pDatasetId:NUMERIC#) </statement> <statement id="getGeneExperimentResultsByDatasetExperimentId" parameterClass="Integer" resultClass="GeneExperimentResult"> CALL getGeneExperimentResultsByDatasetExperimentId(#pDatasetExperimentId:NUMERIC#) </statement> <statement id="getGeneKeyById" parameterClass="Integer" resultClass="GeneKey"> CALL getGeneKeyById(#pGeneKeyId:NUMERIC#) </statement> <statement id="getResearcherByUsername" parameterClass="String" resultClass="Researcher"> CALL getResearcherByUsername(#pUserName:VARCHAR#) </statement> <statement id="getResearcherById" parameterClass="Integer" resultClass="Researcher"> CALL getResearcherById(#pId:NUMERIC#) </statement> <statement id="getResearcherAccessibleDatasets" resultClass="Dataset"> CALL getResearcherAccessibleDatasets(#researcherId:NUMERIC#) </statement> </sqlMap>
package org.epistasis.db.geneDataset; import com.ibatis.sqlmap.client.SqlMapClientBuilder; import com.ibatis.common.resources.Resources; import java.io.Reader; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Properties; import java.util.WeakHashMap; import org.apache.log4j.Logger; import org.epistasis.MiscUtilities; import org.epistasis.db.SqlMapClientWrapper; import org.epistasis.eva.EvaProperties; /** * This is not a best practices class. It's just an example * to give you an idea of how iBATIS works. For a more complete * example, see JPetStore 5.0 at http://www.ibatis.com. */ public class GeneDatasetDbAccess { private static Logger log = Logger.getLogger(GeneDatasetDbAccess.class); private static Map<Integer,Researcher> researcherCache = new WeakHashMap<Integer,Researcher>(); public static void clearResearcherCache() { researcherCache.clear(); } /** * SqlMapClient instances are thread safe, so you only need one. * In this case, we'll use a static singleton. So sue me. ;-) */ private static SqlMapClientWrapper sqlMapper; /** * It's not a good idea to put code that can fail in a class initializer, * but for sake of argument, here's how you configure an SQL Map. */ public static Researcher login(String username, String password) { Researcher loggedInResearcher = null; try { Properties loginProperties = new Properties(); loginProperties.setProperty("geneDatasetDb.host", EvaProperties.get("geneDatasetDb.host")); loginProperties.setProperty("geneDatasetDb.username", EvaProperties.get("dBUsernamePrefix") + username); loginProperties.setProperty("geneDatasetDb.password", password); Reader reader = Resources.getResourceAsReader(MiscUtilities.getClassResourcePath(GeneDatasetDbAccess.class) + "GeneDatasetDbSqlMapConfig.xml"); sqlMapper = new SqlMapClientWrapper(SqlMapClientBuilder.buildSqlMapClient(reader, loginProperties)); reader.close(); log.info("iBATIS SqlMap for db 'GeneDataset' initialized"); loggedInResearcher = GeneDatasetDbAccess.getResearcherByUsername(username); } catch (Exception e) { // Fail fast. log.error("Static initializer Caught an exception: " + e.toString()); //throw new RuntimeException("Something bad happened while building the SqlMapClient instance." + e, e); } return loggedInResearcher; } //end login() /** * Cast objects to the type of the receiving object */ @SuppressWarnings( "unchecked" ) private static <T> T cast( final Object target ) { return (T) target; } static void commitTransaction() { sqlMapper.commitTransaction(); } static void executeBatch() { sqlMapper.executeBatch(); } static void endTransaction() { sqlMapper.endTransaction(); } public static List<String> getDatasetExperimentTitlesByDatasetId(int pDatasetId) { return cast(sqlMapper.queryForList("getDatasetExperimentTitlesByDatasetId", pDatasetId)); } public static List<DatasetExperiment> getDatasetExperimentsByDatasetId(int pDatasetId) { return cast(sqlMapper.queryForList("getDatasetExperimentsByDatasetId", pDatasetId)); } public static List<GeneExperimentResult> getGeneExperimentResultsByDatasetExperimentId(int pDatasetExperimentId) { return cast(sqlMapper.queryForList("getGeneExperimentResultsByDatasetExperimentId", pDatasetExperimentId)); } public static GeneKey getGeneKeyById(int pGeneKeyId) { return cast(sqlMapper.queryForObject("getGeneKeyById", pGeneKeyId)); } public static Researcher getResearcherByUsername(String username) { return cast(sqlMapper.queryForObject("getResearcherByUsername", username)); } public static Researcher getResearcherById(int pId) { Researcher researcher = researcherCache.get(pId); if (researcher == null) { researcher = cast(sqlMapper.queryForObject("getResearcherById", new Integer(pId))); researcherCache.put(pId, researcher); } return researcher; } public static List<Dataset> getResearcherAccessibleDatasets(Researcher researcher) { return cast(sqlMapper.queryForList("getResearcherAccessibleDatasets", researcher.getId())); } public static List<Researcher> getAllResearchers() { return cast(sqlMapper.queryForList("getAllResearchers")); } public static Integer setDataset( Dataset dataset ) { return cast(sqlMapper.queryForObject("setDataset", dataset)); } public static Integer setDatasetExperiment( Dataset dataset, String pColumnName, String pDescription, String pExternalLink ) { Map<String, Object> map = new HashMap<String, Object>(); map.put("pDatasetId", new Integer(dataset.getId())); map.put("pColumnName", pColumnName); map.put("pDescription", pDescription); map.put("pExternalLink", pExternalLink); return cast(sqlMapper.queryForObject("setDatasetExperiment", map)); } public static Integer setGeneExperimentResult(int geneKeyId, int datasetExperimentId, double geneExperimentResultValue) { Map<String, Object> map = new HashMap<String, Object>(); map.put("pGeneKeyId", new Integer(geneKeyId)); map.put("pDatasetExperimentId", new Integer(datasetExperimentId)); map.put("pGeneExperimentResultValue", new Double(geneExperimentResultValue)); return cast(sqlMapper.queryForObject("setGeneExperimentResult", map)); } public static Integer setGeneKey( String pGeneKey ) { return cast(sqlMapper.queryForObject("setGeneKey", pGeneKey)); } static void startBatch() { sqlMapper.startBatch(); } static void startTransaction() { sqlMapper.startTransaction(); } /** * @param args the command line arguments */ public static void main(String[] args) { try { Researcher researcher = login("PeterVermont", "password"); getGeneExperimentResultsByDatasetExperimentId(1); } catch (Exception e) { // Fail fast. log.error("Caught an exception ", e); //throw new RuntimeException(e); } } //end main() } //end GeneDatasetDbAccess class
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd";>
<sqlMapConfig>
<!--NOTE: commitRequired must be true because iBATIS does not recognize that a change has occurred and needs to be
committed when I use stored procedures that do change the database -->
<transactionManager type="JDBC" commitRequired="true">
<dataSource type="DBCP">
<property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
<property name="JDBC.ConnectionURL" value="jdbc:mysql://${geneDatasetDb.host}:3306/geneDataset"/>
<property name="JDBC.Username" value="${geneDatasetDb.username}"/>
<property name="JDBC.Password" value="${geneDatasetDb.password}"/>
</dataSource>
</transactionManager>
<!-- List the SQL Map XML files. They can be loaded from the
classpath, as they are here (com.domain.data...) -->
<sqlMap resource="org/epistasis/db/geneDataset/GeneDataset.xml"/>
</sqlMapConfig>
/*
* GeneExperimentResult.java
*
* Created on October 30, 2006, 10:54 AM
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
package org.epistasis.db.geneDataset;
import org.epistasis.db.TableMapping;
/**
* Entity class GeneExperimentResult
*
*
* @author pandrews
*/
public class GeneExperimentResult extends TableMapping {
private double geneExperimentResultValue;
private int geneKeyId;
private GeneKey geneKey;
public GeneExperimentResult() {
}
/**
* Gets the geneExperimentResultValue of this GeneExperimentResult.
*
* @return the geneExperimentResultValue
*/
public double getGeneExperimentResultValue() {
return this.geneExperimentResultValue;
}
/**
* Sets the geneExperimentResultValue of this GeneExperimentResult to the specified value.
*
* @param geneExperimentResultValue the new geneExperimentResultValue
*/
public void setGeneExperimentResultValue(double geneExperimentResultValue) {
this.geneExperimentResultValue = geneExperimentResultValue;
}
/**
* Gets the geneKeyId of this GeneExperimentResult.
*
* @return the geneKeyId
*/
public int getGeneKeyId() {
return this.geneKeyId;
}
/**
* Sets the geneKeyId of this GeneExperimentResult to the specified value.
*
* @param geneKeyId the new geneKeyId
*/
public void setGeneKeyId(int geneKeyId) {
this.geneKeyId = geneKeyId;
geneKey = GeneDatasetDbAccess.getGeneKeyById(geneKeyId);
//retrieve geneKey
//geneKey = GeneKey.getGeneKeyById(geneKeyId);
}
}
/*
* GeneKey.java
*
* Created on October 30, 2006, 10:54 AM
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
package org.epistasis.db.geneDataset;
import java.io.Serializable;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import org.epistasis.db.TableMapping;
import org.epistasis.db.geneAnnotation.Gene;
import org.epistasis.db.geneAnnotation.GeneAnnotationDbAccess;
import org.epistasis.db.geneAnnotation.GeneCategoryFeature;
import org.epistasis.eva.EvaProperties;
/**
* Entity class GeneKey
*
* @author pandrews
*/
public class GeneKey extends TableMapping {
private String geneKey;
private Gene gene = null;
private List<GeneCategoryFeature> geneCategoryFeatures;
private static Map<Integer,GeneKey> geneKeyCache = new HashMap<Integer,GeneKey>();
public static void clearGeneKeyCache() {
geneKeyCache.clear();
}
public static Map<Integer,GeneKey> getGeneKeys() {
return geneKeyCache;
}
public static GeneKey getGeneKeyById(int pGeneKeyId) {
GeneKey geneKey = geneKeyCache.get(pGeneKeyId);
if (geneKey == null) {
geneKey = GeneDatasetDbAccess.getGeneKeyById(pGeneKeyId);
geneKeyCache.put(pGeneKeyId, geneKey);
}
return geneKey;
}
public GeneKey() {
}
/**
* Gets the geneKey of this GeneKey.
* @return the geneKey
*/
public String getGeneKey() {
return this.geneKey;
}
/**
* Sets the geneKey of this GeneKey to the specified value.
* @param geneKey the new geneKey
*/
public void setGeneKey(String geneKey) {
this.geneKey = geneKey;
geneCategoryFeatures = GeneAnnotationDbAccess.getGeneCategoryFeaturesByGeneSynonym(this.geneKey);
}
}
