Wednesday, January 30, 2008

Beginning with SQL Server

Given changes in the environment in which our business does it's business, I've had to start learning to use SQL Server and various other Microsoft technologies lately. A quick note on connecting to a foreign SQL Server instance (ie another company's ...) :


Create an alias for the database so that Enterprise Manager has something to work with

Open up the Client Network Utility which should be included in the Start menu programs group for your SQL Server installation.
Start -> Programs -> Microsoft SQL Server -> Client Network Utility -> Alias (tab) -> Add... (button).
Under 'Server alias' enter an easy to remember name for the connection. Under 'Network libraries' select the TCP/IP option. Under 'Connection parameters' enter the DNS name or IP address under 'Server name' of the server you wish to connect to. Change the option for 'Dynamically determine port' if the server you're attempting to connect to doesn't run under the standard port of 1433 for SQL Server. Hit 'Ok' to save your settings, then 'Ok' to close out of the Client Network Utility.

Create a new SQL Server Registration to add your SQL Server instance to the Enterprise Manager

Open Enterprise Manager. It should be in the same programs group in the start menu as the Client Network Utility. Once open, create a new SQL Server Group if you haven't already got one (or haven't got one that you want to add your new SQL Server instance to). Then add a new SQL Server Registration. You should be prompted by a Wizard at this point. (If you're not, then you've probably already disabled the Wizard and don't need to be reading this tutorial.) In the wizard, click on 'Next' to proceed to the SQL Server selection screen. Under the list of available servers, you should see the alias you just created in the previous step. Select it and click on 'Add >' to add it to the list of added servers, and then click on 'Next'. After this, you should be prompted for credentials, and the rest is pretty self explanatory.

Tuesday, January 29, 2008

<rage>Hibernate !!!</rage>

So, this is the first post of the new year, and regrettably, it's one written in anger over Hibernate ... again.

This time, it's regarding the Criteria API. It seems that if you want to be able to use the Criteria API for querying entities with composite properties (or ids), you have to use a special notation. Say you have the following entities :


public class Cat implements java.io.Serializable {
@Embedded
private CatMetadata metadata;

@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;

//constructors, getters, setters, etc omitted for clarity
}

@Embeddable
public class CatMetadata implements java.io.Serializable {

@Column(name = "name")
private String name;

@Column(name = "age")
private int age;

@Column(name = "colour")
private int colour;

//constructors, getters, setters, etc omitted for clarity
}


If you wanted to get all cats with the name "Spunky", your first instinct would likely be to do something like this (given what's in the Hibernate documentation):

Session s = sessionFactory.openSession();
List cats = s.createCriteria(Cat.class)
.createCriteria("metadata")
.add(Restrictions.eq("name","Spunky"))
.list();

s.close();

for(Cat c : cats) {
System.out.println(c);
}


...and your first instinct would be quite wrong. The above code would work just fine if
CatMetadata
were an assocation rather than embedded. Instead, if
CatMetadata
is embedded, you must do the following :


List cats = s.createCriteria(Cat.class)
.add(Restrictions.eq("metadata.name","Spunky"))
.list();



Note the difference in the restriction that's added to the criteria. The desired end property must be expressed in dotted notation relative to the embedded object. This little inconsistency tripped me up for hours trying to Google a fix for it. Why Hibernate couldn't just stick with a consistent scheme for dealing with nested properties is beyond me (and given time constraints at work, I really don't have the time to go figure it out).