Avoiding column name redundancy accessing SQL databases?

Question

Hello,

I'm working on a program using a SQL database (I use SQLite). What my program does is the following:

1. Creates the tables in the database if they don't exist (on startup)
2. Executes SQL queries (SELECT, UPDATE, DELETE) - decided by the user from the interface

What I saw doing this is that there is a lot of redundancy. I give the name of the columns when I create the tables and also when I update or insert information in the table.

CREATE TABLE USERS (
USER_ID,
NAME,
ADDRESS);

UPDATE USERS (
NAME='John Smith'
WHERE USER_ID='1'
);

This can be frustrating when dealing with more than 3 or 4 tables and mistakes can happen if I misspell the name of a column.

I would like to know if there's a designer capable of creating/generating these SQL queries by just giving some specifications about the table's description.

Thank you for any suggestion,

Iulian

PS: The code is written in Java but I must be able also to access the code from a C/C++ command line program.

Answer 1

This is the basic idea behind an ORM such as Ruby on Rails' ActiveRecord, hibernate, and similar technologies. Basically you set the configuration for a table - or use a standard naming convention - and the framework will generate your CRUD (INSERT / SELECT / UPDATE / DELETE) queries for you.