CODEWITHSUNDEEP
javamysqlorm
Coderer
Coderer

Reputation: 27274

"Select or create" from database, in Java

I've done all my database development for the past few years in Ruby, mostly using ActiveRecord. Now I'm stuck using Java for a project, and it feels so verbose and hamfisted, I'm wondering if I'm doing things wrong.

In an ORM paradigm, if I want to insert into related tables, I'd so something like

# Joe Bob got a new car
p = Person.find_or_create_by_name("Joe Bob");
Car.new({:make=>"Toyota", :plate=>"ABC 123", :owner=>p});

In Java, at least using JDBC directly, I'm going to have to do the Person lookup by hand, insert if it doesn't exist, then create the Car entry.

Of course, in real life, it's more than just 2 tables and the pain scales exponentially. Surely there's a better way?

Upvotes: 1

Views: 406

Answers (5)

ipolevoy
ipolevoy

Reputation: 5518

Looks like I'm late here, however, ActiveJDBC will do what you want in Java: 

Person p = Person.findOrCreateIt("name", "Joe Bob");
Car car = Car.createIt("make", "Toyota", "plate", "ABC 123", "owner", p);

There is a ton more it can do, check out at: http://javalite.io/

Upvotes: 0

mikera
mikera

Reputation: 106351

You can use ORM solutions for Java - there are various solutions available.

Links worth looking at:

Having said that, I've usually found that for complex applications ORM frequently causes more trouble than it is worth (and yes, this does include Ruby projects with Activerecord). Sometimes it really does make sense to just get at the data directly via SQL rather than attempt to force on object-oriented facade on top of it.

Upvotes: 3

Jeff Miller
Jeff Miller

Reputation: 1434

Sormula contains an active record package. The save method will update an existing record or insert if no record exists.

See the active record example on web site.

Also see org.sormula.tests.active.SaveTest.java within the project:

SormulaTestAR record = new SormulaTestAR();  
record.attach(getActiveDatabase()); // record needs to know data source
record.setId(8002);
record.setType(8);
record.setDescription("Save one AR 2");
record.save();

Upvotes: 0

18bytes
18bytes

Reputation: 6029

If you are using JDBC you need to lookup yourself and create person if it does not exist. There is no better way if you use JDBC.

But you can use Hibernate, it will help you reduce writing the O-R mapping yourself and reduce the boilerplate.

As you come from Ruby and If you find it painful to write all the SQL queries, JDBC boilerplate then the better way is to use ORM. I recommend one of the following,

  1. Hibernate
  2. JPA (If you want to change the ORM implementation then use JPA)

Upvotes: 1

Ariel
Ariel

Reputation: 26753

The better way is learn SQL! The ORM you like so much writes SQL for you behind the scenes.

So you can make a quick helper function that tries to select the record, and if it doesn't exist creates it for you.

In MySQL you can use INSERT IGNORE ..... which will insert the row only if it doesn't exist.

And here is a special bit of SQL you may like (MySQL only):

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

This tries to insert the record, if it doesn't exist it returns the auto_increment like usual that you retrieve in your program.

But: If it does exist then it updates it - (only c is set to update in that case), but the cool part is it sets the LAST_INSERT_ID() just like it would on an insert.

So either way you get the ID field. And all in a single bit of SQL.

SQL is a very nice language - you should learn it and not rely on the psudo-language of orm.

Upvotes: 3

Related Questions