. .

st4u

ST 4U 163: Constructing Queries with Glorp

November 28, 2011 12:38:05.893

Today's Smalltalk 4 You starts executing a few queries against the Emp table we created and mapped here. If you have trouble viewing it here in the browser, you can also navigate directly to YouTube. To watch now, click on the image below:

Glorp Queries.

If you have trouble viewing that directly, you can click here to download the video directly. If you need the video in a Windows Media format, then download that here.

You can also watch it on YouTube:


Today we'll execute some queries against Oracle using the descriptor we set up - see this video for details on that. First off, we inserted some data so that there would be something to query for:


"create a few more"
listOfEmps := OrderedCollection new.
1 to: 10 do: [:index | | newEmp |
	newEmp := Emp new.
	newEmp firstName: 'Wilma', index printString.
	newEmp lastName: 'Flintstone', index printString.
	listOfEmps add: newEmp
]

"insert them"
session beginUnitOfWork.
listOfEmps do: [:each | session registerAsNew: each].
session commitUnitOfWork.

Now we can execute a few queries. It's worth taking a look at class GlorpSession to get an idea as to the API that's being used here. First, the simplest query - you can #read: any class that's been described in a descriptor:


"query"
emps := session read: Emp.
emps := session readManyOf: Emp.


Those two queries do the same thing - you should see this if you inspect the results:

Glorp Query

You can also include a where clause, but bear in mind the restrictions - in general, you are limited to code that:

  • accesses instance variables
  • compares instance variables using #=, #<, #>, #<>, #isNIL, #notNIL
  • Uses expressions in class ObjectExpression

Note that we use isNIL and notNIL rather than isNil and notNil; Glorp knows how to parse those and create valid SQL (which is what the where block becomes). Here are a few examples:


"where clauses"
session readOneOf: Emp where: [:each | each firstName = 'Fred'].
session readManyOf: Emp where: [:each | each firstName = 'Fred'].
session readManyOf: Emp where: [:each | each firstName = 'Barney'].
session readManyOf: Emp where: [:each | each firstName notNIL].
session readManyOf: Emp where: [:each | each firstName like: 'Wilma%'].

Note the use of #like:, which generates a SQL like statement. Use % rather than * for wildcarding, just as you would with SQL. For that last query, you should see the following results:

where clause

That wraps it up for now - we'll look at removing data next time

Need more help? There's a screencast for other topics like this which you may want to watch. Questions? Try the "Chat with James" Google gadget over in the sidebar.

Technorati Tags: , , ,

Enclosures:
[st4u163-iPhone.m4v ( Size: 7493149 )]

posted by James Robertson

 Share Tweet This