- What is Village?
- How can I configure Torque to use a JNDI connection?
- How can I execute a stored procedure
- Can I run a stored procedure (or a complex query), but use Torque's mapping to return the results as objects?
- How to add P6Spy for printing SQL
- doDelete() is throwing a !TorqueException. What's up?
- Is there a way to use one installation of torque for different projects without having a copy of torque-gen for each project (e.g. copy the build-torque.xml and build.properties and set a parameter in build.properties to the torque basedir)?
- Can I use my own ID-Generator with torque? How?
- We have an existing database where some of the table names have spaces as part of the name. Is there a way in which Torque could replace spaces by, say, underscores? What about table columns with spaces in the names?
- The save methods internally use Transaction.start(), what if I want save of multiple entities to be wrapped in single transaction?
- How can I count datasets in a table ?
- Why are the log4j properties in Torque.properties being ignored in the example bookstore application?
- Why are large BLOBs and CLOBs not working in Oracle? How do I store BLOBs or CLOBs > 4000 (or sometimes 2000) bytes?
- Why is there a problem with Torque runtime properties?
- Why does Torque generate invalid SQL for PostgreSQL BOOL columns?
- Why does the generated SQL fail with PostgreSQL?
- Why addAscendingOrderByColumn launch a NullPointerException ?
- What is the correct DTD to use with the Schema files and how can I avoid loading the DTD file from the internet every time ?
- How do you set up the maven 2 plugin?
What is Village?
Answer: See the ![[WWW]](/wiki/modern/img/moin-www.png){kind=small}
http://www.softwareforge.de/projects/village homepage for that information.
How can I configure Torque to use a JNDI connection?
Answer: Read the Torque Pool-config Howto for indepth information. However, here is the short howto!
Torque.properties:
torque.database.default.adapter=mssql torque.dsfactory.default.factory=org.apache.torque.dsfactory.JndiDataSourceFactory torque.dsfactory.default.jndi.path=java:comp/env/jdbc/fortius
web.xml (In the right order according to the servlet spec):
<resource-ref>
<description>
Resource reference to a factory for java.sql.Connection
instances that may be used for talking to a particular
database that is configured in the server.xml file.
</description>
<res-ref-name>
jdbc/fortius
</res-ref-name>
<res-type>
org.apache.torque.pool.TorqueClassicDataSource
</res-type>
<res-auth>
Container
</res-auth>
</resource-ref>
server.xml (inside your Context):
<Resource name="jdbc/fortius" scope="Shareable" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/fortius">
<parameter>
<name>validationQuery</name>
<value>SELECT 1</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>5000</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>4</value>
</parameter>
<parameter>
<name>password</name>
<value>mypassword</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:microsoft:sqlserver://cuzco:1433;DatabaseName=fortius;SelectMethod=cursor</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
<parameter>
<name>username</name>
<value>SilverUserLogin</value>
</parameter>
</ResourceParams>
Libraries. Make sure you put your Jar's that are need for the driver where they can be reached. For Tomcat, put them in $(CATALINA_HOME)/common/lib, otherwise Tomcat can not create the connection.
Debugging
If all else fails, first try and verify that you are able to directly create the datasource. I wrote a little action called DB.java that I could call from torque with a single action:
public void doPerform(RunData data, Context context)
throws Exception {
try {
Log.debug("DB.doPerform called");
String foo = '''''';
javax.naming.Context initCtx = new InitialContext();
javax.naming.Context envCtx = (javax.naming.Context) initCtx.lookup("java:comp/env");
DataSource ds =
(DataSource) envCtx.lookup("jdbc/fortius");
if (ds != null) {
Connection conn = ds.getConnection();
if (conn != null) {
System.out.println("Got Connection " + conn.toString());
Statement stmt = conn.createStatement();
ResultSet rst =
stmt.executeQuery(
"select * from kinase");
if (rst.next()) {
foo = rst.getString(2);
System.out.println("foo:" + foo);
}
conn.close();
}
}
}
catch (Exception e) {
Log.error(e);
throw e;
}
}
-- EricPugh
How can I execute a stored procedure
Answer: You can use some of the functionality exposed by the Village library to execute and deal with the result set of your stored procedure:
import com.workingdogs.village.Record;
String SQL = "exec myStroedProc 4, 'hello'";
List records = WorkorderPeer.executeQuery(SQL);
Vector kinaseATPConcs = new Vector();
for (Iterator i = records.iterator(); i.hasNext();) {
Record record = (Record) i.next();
KinaseATPConc kinaseATPConc = new KinaseATPConc();
kinaseATPConc.setKinaseId(record.getValue("kinase_id").asString());
kinaseATPConc.setAtpConc(record.getValue("atp_conc").asDouble());
kinaseATPConcs.add(kinaseATPConc);
}
What else you may notice is that if you do something similar with a complex sql query:
String SQL = "select top 50 distinct kinase_id,atp_conc from reaction where workorder_id = "
+ workorder.getWorkorderId()
+ " and reaction.result is null and reaction.daughterboard_id is null");
-- EricPugh
Can I run a stored procedure (or a complex query), but use Torque's mapping to return the results as objects?
Answer: Yes, you can! Here's an example.
String sql = "select * from jps_containers c where not exists (select * from jps_apps a where a.container_id =c.container_id)"; List records = ContainerConfigPeer.executeQuery(sql); List listOfContainerConfigs = ContainerConfigPeer.populateObjects(records);
This is very similar to the previous answer (How can I execute a stored procedure), but you don't have to worry after you add a column to the underlying table.
How to add P6Spy for printing SQL
Answer:
add the p6spy.jar to your application classpath
copy the "spy.properties" into your classpath (e.g. same location as log4j.properties)
set the "realdriver" in spy.properties, e.g. realdriver=oracle.jdbc.driver.OracleDriver for Oracle
change the Torque.properties, e.g. "torque.dsfactory.XXX.connection.driver = com.p6spy.engine.spy.P6SpyDriver" to use the proxy JDBC drivers
run your application
P6Spy creates a spy.log in your current directory
-- Siegfried Goeschl
doDelete() is throwing a !TorqueException. What's up?
Answer: doDelete() throws a TorqueException with message "You must specify KeyDef attributes for this TableDataSet in order to delete a Record" when the schema for the table does not contain a primary key. If the table has no primary key, doDelete() may not be used to delete from it.
-- Gary Shea
Is there a way to use one installation of torque for different projects without having a copy of torque-gen for each project (e.g. copy the build-torque.xml and build.properties and set a parameter in build.properties to the torque basedir)?
Answer: Yes. Torque is mavenized, so you can install and use torque as a plugin for Maven. http://db.apache.org/torque/releases/torque-3.2/maven-plugin/index.html
After successfull instalation of the plugin you will be able to run the common torque tasks and plus this you will be able to run pomre specific tasks configurations as well. For example the command line: maven -D absolute_folder_location torque:om which will look inside the given path and will run torque's task OM using the build.properties that are located there. and the xml defined schema will be in a relative folder, (according to: plugin.properties in your MAVEN_HOME/plugins/maven-torque-plugin directory)
-- bogdan
Can I use my own ID-Generator with torque? How?
Answer: ????
-- Michael Wyraz
We have an existing database where some of the table names have spaces as part of the name. Is there a way in which Torque could replace spaces by, say, underscores? What about table columns with spaces in the names?
-- Daniel Canas
Answer: Quick way -> No. And we hope that in the next release there will be a way for plugging external Name Genrator. But for now you can still change the genrator to fit your needs. For example I have extended JavaNameGenerator this way:
1) added a single line to interface org.apache.torque.engine.database.model.NameGenerator;
String CONV_METHOD_UNDERSCORE_IGNORE_DOTS = "underscore_ignore_dots";
2) added a single conversion method to class org.apache.torque.engine.database.model.JavaNameGenerator;
protected String underscoreIgnoreDotsMethod(String schemaName) {
schemaName = schemaName.replaceAll("\\.", "_");
StringBuffer name = new StringBuffer();
StringTokenizer tok =
new StringTokenizer(schemaName, String.valueOf(STD_SEPARATOR_CHAR));
while (tok.hasMoreTokens()) {
String namePart = ((String) tok.nextElement()).toLowerCase();
name.append(StringUtils.capitalize(namePart));
}
return name.toString();
}
3) added a single condition to class org.apache.torque.engine.database.model.JavaNameGenerator; method: String generateName(List inputs);
} else if (CONV_METHOD_UNDERSCORE_IGNORE_DOTS.equals(method)) {
javaName = underscoreIgnoreDotsMethod(schemaName);
4) rebuilt the torque-generator maven plugin (src/generator$ maven jar:install)
5) now I can use the attribute value in the database tags of my schemas
<database defaultJavaNamingMethod="underscore_ignore_dots" .....
My code ignores gots inside the name. You can ignore spaces for your case ..or make whatever conversions you need. (this is the way in short to add new naming method)
-- Bogdan Vatkov
The save methods internally use Transaction.start(), what if I want save of multiple entities to be wrapped in single transaction?
Answer: Use Transaction yourself:
{
java.sql.Connection connection = null;
try {
connection = org.apache.torque.util.Transaction.begin("torques_name_of_db");
someObject.save(connection);
someOtherObject.save(connection);
org.apache.torque.util.Transaction.commit(connection);
connection = null;
}
catch (Exception e) {
// error-handling code goes here
}
finally {
// no rollback if the transaction succeeded,
// because in this case connection is null here
if (connection != null) {
org.apache.torque.util.Transaction.safeRollback(connection);
}
}
}
torques_name_of_db must be replaced with the database's name from torque's configuration files. The save(connection) methods are implemented in Torque's autogenerated base classes (where also the "usual" save() - Methods are), so you don't have to implement these by yourself.
One important point in using the Transaction class is to make sure that the Database connection is released at the end of the transaction, even if the transaction failed. If database connections are not released, you will run out of available connections sooner or later, leading to errors which are not easy to find. Releasing the connection is ensured in the above code because either Transaction.commit() or Transaction.safeRollback() are called (or both if the commit fails). Both methods release the database connection. Note that connection.commit() does NOT release the database connection, so connection.commit() should not be used unless you know what you are doing.
-- Thomas Fischer
How can I count datasets in a table ?
Suppose one wants to execute a SQL-statement like "select count(*) from employee where name='james'".
Answer: This query can be executed using the following code (assuming the employee Table has at least two Columns: "NAME" and "ID", where ID is the primary key, and the Peer class to the employee Table is EmployeePeer):
int numberOfRecords;
{
org.apache.torque.util.Criteria criteria = new org.apache.torque.util.Criteria();
criteria.add(EmployeePeer.NAME, "james");
criteria.addSelectColumn("COUNT(" + EmployeePeer.ID + ")");
java.util.List result = EmployeePeer.doSelectVillageRecords(criteria);
com.workingdogs.village.Record record = (com.workingdogs.village.Record) result.get(0);
numberOfRecords = record.getValue(1).asInt();
}
I don't know which columns should be used in addSelectColumn (and if it makes a difference which one is used), but I presume using the primary key is fine. Note that addSelectColumn("count(*)") does not work.
The answer was put together from articles by Scott Eade and Tulsi Das on the Torque Users Mailing List.
-- Thomas Fischer
Why are the log4j properties in Torque.properties being ignored in the example bookstore application?
The torque-gen-3.1 jar file includes configuration files for Log4j and commons-logging SimpleLog - if this jar is in your classpath at runtime it may adversly impact your logging implementation.
The best thing to do is to ensure the torque-gen jar file is not in the classpath of your application (it is only required when generating your object model classes (and the other generator tasks). See commons-logging configuration for how to configure the logging for your application.
Why are large BLOBs and CLOBs not working in Oracle? How do I store BLOBs or CLOBs > 4000 (or sometimes 2000) bytes?
The problem is with the Oracle JDBC Driver. It doesn't follow the JDBC standards properly. To fix the problem, use the patched version of Village available here. The Village patch for Oracle was last updated on 10-Oct-2004.
-- Sarav
Why is there a problem with Torque runtime properties?
Some of the properties have been renamed, so the torque properties file is partly outdated, e.g.
#torque.defaults.pool.connectionWaitTimeout = 10 #has been renamed to: torque.defaults.pool.maxWait=10
Read the Jakarta Commons DBCP docs about DBCP configuration and adapt your torque properties file accordingly.
-- Tarlika Elisabeth Schmitz
Why does Torque generate invalid SQL for PostgreSQL BOOL columns?
Torque generates invalid SQL for bool comparisons: generated "WHERE xyz=0", but it should be WHERE xyz=FALSE or "WHERE xyz='0'".
The fault is in the adapter class \org\apache\torque\adapter\DBPostgres.class:
public String getBooleanString(Boolean b)
{
//was : return (b == null) ? "0" : (Boolean.TRUE.equals(b) ? "1" : "0");
return (b == null) ? "NULL" : (Boolean.TRUE.equals(b) ? "TRUE" : "FALSE");
}
-- Tarlika Elisabeth Schmitz
Why does the generated SQL fail with PostgreSQL?
The SQL generated for PostgreSQL using defaultIdMethod "native" causes a problem with PostGreSQL 7.2 or higher:
For each autoIncrement primary key a CREATE SEQUENCE statement is generated followed by the CREATE TABLE statement. The latter fails because this version of PostgreSQL automatically creates a SEQUENCE for each serial column. The table will not be created if the SEQUENCE already exists.
Remove line 7 #if ($sequence.length()>0)$sequence#end from the template sql\base\postgresql\table.vm
-- Tarlika Elisabeth Schmitz
Why addAscendingOrderByColumn launch a NullPointerException ?
Answer: Torque has not been correctly initialized for the database. The properties file need the key torque.database.db.adapter=mysql where db is the database name and mysql the database adapter used by torque for accessing the base.
-- Alexandre Gouze
Answer: Another error may be at the OM generation. The database name was not specifed in then XML schema file. <database defaultIdMethod="idbroker" name="databaseName">
-- Jorge Ortega
Answer: Passing a non-existing column name via addAscendingOrderByColumn can also get you a NullPointerException when BasePeer.doSelect(Criteria crit) is called
-- HelgeWeissig
What is the correct DTD to use with the Schema files and how can I avoid loading the DTD file from the internet every time ?
Answer For the 3.1 release of Torque, these are the right DTD Definitions:
Torque 3.1 and Torque 3.1.1: http://db.apache.org/torque/dtd/database_3_1.dtd Torque 3.2: http://db.apache.org/torque/dtd/database_3_2.dtd
If you use this DTD, then the Torque Generator will use the database.dtd file embedded in the torque generator jar.
You should use the following DTD declaration in your Schema files:
Torque 3.1 and Torque 3.1.1: <!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database_3_1.dtd"> Torque 3.2: <!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database_3_2.dtd">
-- Henning Schmiedehausen
How do you set up the maven 2 plugin?
Add this to your pom.xml
<project>
...
<build>
<plugins>
<plugin>
<groupId>org.apache.db.torque</groupId>
<artifactId>torque-maven-plugin</artifactId>
<version>3.3-RC1</version>
</plugin>
</plugins>
</build>
</project>
Then refer to the maven 2 torque plugin documentation for supported goals.
TODO: additional configuration properties are needed for the torque plugin to operate. I believe these plugin properties are just added to the plugin element in the pom.xml.
TODO: add this to the maven 2 plugin site documentation.
-- Scot Hale