What is your favorite way to interact with databases from your programming language?

Question

There are many ways to connect and interact with the database level. For example, in Java, common customs of JDBC calls to raw SQL, relational object maps, JDBCTemplate ( Spring ), stored procedures, etc.

In your language, which option is your preference and why? When do you count others?

Answer 1

ORM every time, the least I have to think about databases, the better.

Answer 2

I really like the 3 + 1 level method. One level for the user interface, one for business logic and for saving data. The last one you say? Domain objects and interfaces. This allows you to load one or two of the main levels plus the domain “level”, and the code should work.

It relies heavily on dependency injection and Inversion of Control principles. The data / persistence layer does only two things. It creates, reads, updates, and deletes data and maps it to the format of a domain object.

The user interface layer does the opposite. It displays and receives data so that the user can relate to it, and displays its output / input in the format of a domain object.

The business logic level needs to know only one thing. Business logic. He does not care about where the data came from, nor does he care about where the data layer is. He knows that he must indicate an account that was simply overloaded, how physically doing this is not part of her job.

The domain objects themselves have no logic; they are just containers for transferring data between levels. This means that you can load domain objects and interfaces without worrying about dependencies.

At the end of the day, I feel like I have a fairly clear code base with clearly separated levels. And with some strict interfaces and good base classes, most of the code just tells the program what to do when X happens. How it should be.

</rant> 

Edit: Oh yes. This is true for LINQ, SubSonic , and other ORMs.

Answer 3

Ruby on Rails ActiveRecord wipes the floor with everything I've seen so far. LINQ looks better in some cases, but ActiveRecord is so flexible.

Answer 4

I prefer to create a layer of a model of a business object (objects and sets of objects).

I create the ability to interact with the database in each object / collection (for SQL Server I use System.Data.SqlClient ). I used this template for SQL Server, MySQL and Oracle.

Then I interact with the objects from the application code.

By abstracting my database into objects, my application code is compatible regardless of the database.

Answer 5

LINQ is the way for me from here to

Answer 6

ORM is really fantastic.

I use SQL Alchemy when working in python - it works with almost every DBMS I came across.

For lightweight data-driven applications on MacOS X, I use Core Data, which has a great data modeling tool available through Xcode.

Both of them show that ORM is done correctly, perfectly. I had less success and pleasure from EJB.

Answer 7

I haven't hit the LINQ world yet, but I really liked the DataTable / TableAdapter classes that Visual Studio executed using the XSD dataset. With a few drag-and-drop clicks after creating my database schema, I now have a DataSet / DataTable object that is strongly typed, and I have adapter methods that use parameterized queries for my stored procedures for all my CRUD statements. He will even create query table adapters for some of those procedures that are not directly bound to the table.

Oh, and if you haven't created stored procedures yet and just have tables, the wizard will create adhoc SQL procedures or SQL statements for you.

This was with Visual Studio 2005 and drastically reduced my “structural” time with my new web applications, and I can focus more on business logic and presentation.

Answer 8

We use a mixed approach, depending on what is appropriate for the specific situation in the application:

• When reading information about the cost of a page for display and for updating by a user, we use Hibernate
• When processing a service pack or summation, where most of the data is already in the database (for example, processing at the end of the day), we use PL / SQL (and try to think in sets)
• When the user searches or runs the final report, we use ibatis sqlmaps to create some SQL and return only those fields that are of interest to us (not every column and, of course, not extra child rows, li>
• All that really needs to be run quickly, we will use any approach that works best

This is using java / Oracle.

Answer 9

In C #, I love LINQ to SQL for something new, but I really like to use . netTiers + CodeSmith Generator to get a fast and dirty level of data in the database if I am using C # on .NET 2.0.

Answer 10

I like Hibernate a lot :)

I know that he has a learning curve, but once you have mastered it, that’s pretty good.

Needless to say, I can't wait to get the new Entity Framework in .NET 3.5 SP1 (I know this is already available, but I'm a little lazy to type in XML :))

Answer 11

ActiveRecord , which is the first document that, it seems to me, was captured in the Fowler Enterprise Architecture Templates . I believe it is implemented in languages ​​other than Ruby, although it is well known as the underlying technology in Rails. Regardless, this is a neat database abstraction, although I have to admit that I find it a bit awkward in the area of ​​find_by_sql. But it can only be me.

But (now put on the Grumpy Old Man hat) all ORMs in the world do not replace good knowledge of SQL, without which I really do not like to allow access to RDBMS at all.

Answer 12

We are currently using ODAC to talk to the Oracle database and use many Oracle packages ( PL / SQL ). The n-level system is implemented using RemObjects, which means that our client does not have SQL code, and he only needs the ability to send HTTP requests, so there is no installation overhead.

All this is done using Borland Delphi and has been working in the production environment for 2 years.

Answer 13

We use Delphi and Oracle Data Access Components (ODAC) and ADO through Oracle.OleDBProvider.

Answer 14

May's favorite way is to use Smalltalk with the GemStone object repository. What for? No problem with ORM. I would only consider something else if my employer coerced or threatened me.

Answer 15

My favorite way is to have an abstraction layer of an object. Ideally, this is the only place that works with SQL. But in practice, objects sometimes also need to do SQL-y things. But nothing is outside the object.

Until now, I myself have written such layers because the available one was too uncomfortable, too slow or too large.

Answer 16

I use simple JDBC because I am developing a data driven application and my database model is very complex. Everything is described in the database, even the structure of other tables. In addition to this, I often use stored procedures. Therefore, ORM is not an option for me.