This chapter describes the query type generation and querying functionality of the SQL module.
To get started export your schema into Querydsl query types like this :
java.sql.Connection conn; // connection of database containing the schema to use // obtain Connection etc. MetaDataExporter exporter = new MetaDataExporter( "Q", // namePrefix "com.myproject.domain", // target package null, // schema name pattern null, // table name pattern "target/generated-sources/java"); // target source folder exporter.export(conn.getMetaData());
This declares that the database schema is to be mirrored into the com.myproject.domain package in the src/main/java folder.
The generated types have the table name transformed to mixed case as the class name and a similar mixed case transformation applied to the columns which are available as property paths in the query type.
This functionality is also available as a Maven plugin. The presented example can be declared like this in the POM :
<plugin> <groupId>com.mysema.querydsl</groupId> <artifactId>querydsl-maven-plugin</artifactId> <version>${querydsl.version}</version> <executions> <execution> <goals> <goal>export</goal> </goals> </execution> </executions> <configuration> <jdbcDriver>org.apache.derby.jdbc.EmbeddedDriver</jdbcDriver> <jdbcUrl>jdbc:derby:target/demoDB;create=true</jdbcUrl> <!-- optional elements : * namePrefix * jdbcUser * jdbcPassword * schemaPattern * tableNamePattern --> <packageName>com.myproject.domain</packageName> <targetFolder>target/generated-sources/java</targetFolder> </configuration> <dependencies> <dependency> <groupId>org.apache.derby</groupId> <artifactId>derby</artifactId> <version>${derby.version}</version> </dependency> </dependencies> </plugin>
Use the goal test-export to add the targetFolder as a test compile source root instead of a compile source root.
Querying with Querydsl SQL is as simple as this :
QCustomer customer = new QCustomer("c"); SQLTemplates dialect = new HSQLDBTemplates(); // SQL-dialect SQLQuery query = SQLQueryImpl(connection, dialect); List<String> lastNames = query.from(customer) .where(customer.firstName.eq("Bob")) .list(customer.lastName);
which is transformed into the following sql query, assuming that the related table name is customer and the columns first_name and last_name :
SELECT c.last_name FROM customer c WHERE c.first_name = 'Bob'
Internally Querydsl SQL uses PreparedStatements, though.
Querydsl uses SQL dialects to customize the SQL serialization needed for different relational databases. The available dialects are :
com.mysema.query.sql.DerbyTemplates
tested with version 10.5.3
com.mysema.query.sql.HSQLDBTemplates
tested with version 1.8.0.7
com.mysema.query.sql.MySQLTemplates
tested with MySQL CE 5.1
com.mysema.query.sql.OracleTemplates
tested with Oracle 10g XE
com.mysema.query.sql.PostgresTemplates
tested with Postgres 8.4
com.mysema.query.sql.SQLServerTemplates
tested with SQL Server 2008
Use the the cascading methods of the SQLQuery interface like this
from : Define the query sources here.
innerJoin, join, leftJoin, fullJoin, on : Define join elements using these constructs. For the join methods the first argument is the join source and the second the target (alias).
where : Define the query filters, either in varargs form separated via commas or cascaded via the and-operator.
groupBy : Define the group by arguments in varargs form.
having : Define the having filter of the "group by" grouping as an varags array of EBoolean expressions.
orderBy : Define the ordering of the result as an varargs array of order expressions. Use asc() and desc() on numeric, string and other comparable expression to access the OrderSpecifier instances.
limit, offset, restrict : Define the paging of the result. Limit for max results, offset for skipping rows and restrict for defining both in one call.
The syntax for declaring ordering is
query.from(customer) .orderBy(customer.lastName.asc(), customer.firstName.asc()) .list(customer.firstName, customer.lastName);
which is equivalent to the following native SQL
SELECT c.first_name, c.last_name FROM customer c ORDER BY c.last_name ASC, c.first_name ASC
Grouping can be done in the following form
query.from(customer) .groupBy(customer.lastName) .list(customer.lastName);
which is equivalent to the following native SQL
SELECT c.last_name FROM customer c GROUP BY c.last_name
Insert examples :
// with columns insert(survey) .columns(survey.id, survey.name) .values(3, "Hello").execute(); // without columns insert(survey) .values(4, "Hello").execute(); // with subquery insert(survey) .columns(survey.id, survey.name) .select(s().from(survey2).list(survey2.id.add(1), survey2.name)) .execute(); // with subquery, without columns insert(survey) .select(s().from(survey2).list(survey2.id.add(10), survey2.name)) .execute();
Using the following methods :
protected SQLInsertClause insert(PEntity<?> e){ return new SQLInsertClause(getConnection(), dialect, e); } protected SQLSubQuery s(){ return new SQLSubQuery(); }
Update examples :
// update with where update(survey).where(survey.name.eq("XXX")).set(survey.name, "S").execute(); // update without where update(survey).set(survey.name, "S").execute()
Using the following methods :
protected SQLUpdateClause update(PEntity<?> e){ return new SQLUpdateClause(Connections.getConnection(), dialect, e); }