Finding Beans with Amber

Most applications need to query the database to find entities with special properties. Amber's query language currently follows Hibernate's query language (this may change in the future depending on JDO 2.0.).

The query language resembles SQL resembles SQL with a few restrictions and extensions appropriate to handling entities beans. For example, the QL extends SQL with direct support for relations.

In the following example, the school's headmaster needs to know which courses are being taught and needs to find the course taught by a named teacher.

Files in this tutorial

WEB-INF/web.xml Configures the Amber resource and Database

WEB-INF/classes/example/Course.java The Course bean.

WEB-INF/classes/example/Course.hbm.xml The Course configuration file.

WEB-INF/classes/example/FindServlet.java The Find servlet.

WEB-INF/classes/example/InitResource.java A resource to initialize the database for the demo

Database Schema

The example uses the same database table as the previous basic example.

courses.sql

CREATE TABLE amber_find_courses ( id INTEGER PRIMARY KEY auto_increment, name VARCHAR(250) NOT NULL, instructor VARCHAR(250), );

The query language

The findAll query finds all the courses in the database.

SELECT o FROM example.Course o

The entity's class name replaces the usual SQL table. The mapping of entities to tables is saved in the configuration files (the Course.hbm.xml), to simply portability.

The usual JDBC parameter syntax, '?', is allowed as well.

SELECT o FROM example.Course o WHERE o.teacher = ?

The query(sql) method

The example servlet uses two AmberConnection methods, query and prepareQuery. query returns a result set. prepareQuery prepares a query like the JDBC PreparedStatement.

Finding all Courses

ResultSet rs = aConn.query("SELECT o FROM example.Course AS o"); while (rs.next()) { Course course = (Course) rs.getObject(1); out.println(course.getName() + " is taught by " + course.getInstructor() + "<br>"); }

Potions is taught by Severus Snape Transfiguration is taught by Minerva McGonagall Defense Against the Dark Arts is taught by Remus Lupin

Prepared Queries

findByInstructor returns the course taught by an instructor. Single-valued finder methods expect to return exactly one bean. If there are no matching courses, findByInstructor throws an ObjectNotFoundException. If more than one courses are taught by the instructor, it will throw a FinderException.

Finding a Course by its Instructor

String hSQL = "SELECT o FROM example.Course AS o WHERE o.teacher = ?"; AmberQuery query = aConn.prepareQuery(hSQL); query.setString(1, teacher); ResultSet rs = query.executeQuery(); if (rs.next()) { course = (Course) rs.getObject(1); out.println(course.getName() + " is taught by " + course.getTeacher() + "<br>"); }

Potions is taught by Severus Snape

Try the Tutorial

Create/Destroy

Tutorials

Many-To-One