Creating service builder without primary key table needs select only

[Ankita Jain, modificado 9 Anos atrás.]

Hi

I want to create a bean for a table( which is not a part of liferay table ) to select data and show it on screen.

No write operation can be done on that table.

Problem:
Table does not have any primary key and also we cannot make any changes in that table.

In service.xml it is mandatory to have primary key.
Any workaround for this?? It is need urgently.

Please help!!!

Regards,
Ankita

[Andew Jardine, modificado 9 Anos atrás.]

Ankita Jain:

I want to create a bean for a table( which is not a part of liferay table ) to select data and show it on screen.

No write operation can be done on that table.

Problem:
Table does not have any primary key and also we cannot make any changes in that table.

In service.xml it is mandatory to have primary key.
Any workaround for this?? It is need urgently.

Please help!!!

Regards,
Ankita

Hi Anika,

When you say "which is not part of Liferay table" -- do you actually mean not part of the "Liferay database"?

As far as I know you must have a primary key specified, if you specify columns. You can define an empty entity without any columns like this --

<entity name="Sample" local-service="true" remote-service="true" />

... in which case you would have no primary key missing exception when you generate the code -- but your model is also empty.

Perhaps if you explain a little more what you are trying to accomplish, someone can help with solution guidance.

[Ankita Jain, modificado 9 Anos atrás.]

Hi,
I have two databases, one is the default liferay one and the other one is the master database (which does not have liferay tables) which consists the branch,state,city.etc details. Now I have a custom portlet which needs to fetch data from the external table which does not have a primary key. In order to refer to the external table i need to build services in my portlet, but if I dont give a primary key in the service.xml file, it does not allow to build the services.Please help

[Ketan Savaliya, modificado 9 Anos atrás.]

Hi Ankita,

I suggest you to put what you have all field + as a primary key you can add like Rownum(in oracle available, for mysql some other function like same. need to google for that ) field in service.xml.
After build service use FinterImpl write your custome select query to get all your table field with Rownum field. then after use this finder method to get result data.

Note: Rownum is like row number. i.e. if you have total 5 rows then your result row value of Rownum field is 1,2,3,4,5.

HTH!!


Regards,
Ketan Savaliya

[David H Nebinger, modificado 9 Anos atrás.]

Ankita Jain:

Table does not have any primary key and also we cannot make any changes in that table.

Every table in a relational database has a primary key.

It may be that every column in the table must be included to identify a unique row, but it still has a primary key.

[Michal R, modificado 9 Anos atrás.]

David H Nebinger:

Every table in a relational database has a primary key.

No, it does not. It is a good (almost mandatory) practice do define a primary key, but nothing prevents you from creating a table without one.

[Jack Bakker, modificado 9 Anos atrás.]

Seems a little strange that you would have a case for a table which does not have a primary key. Though I have had to deal with poorly designed tables which don't and such poorly designed tables can even have duplicate records across all fields.

You say you can't modify the table. Can you create a view which offers a generated primary key ? Are you not getting along with a db admin or something ?

I posted something here https://www.liferay.com/community/forums/-/message_boards/message/12574382 that I used in a stubborn postgresql effort with a FinderImpl where primary key is generated.

[David H Nebinger, modificado 9 Anos atrás.]

Michal R:

David H Nebinger:

Every table in a relational database has a primary key.

No, it does not. It is a good (almost mandatory) practice do define a primary key, but nothing prevents you from creating a table without one.

Whether you define a primary key column directly, well that's just bad form.

But again, every table has a primary key. By definition, a primary key is one or more columns which will uniquely identify a row in the database.

Now unless for some stupid reason you want to make a column with a single string and then create 1000 rows that say "I have no idea what a relational database is good for", then your table has a primary key which will identify a unique row.

[Michal R, modificado 9 Anos atrás.]

David H Nebinger:

But again, every table has a primary key.

You're wrong. It does not. As I said, it is a common good practice to define one, but a primary key is not mandatory.

I'm not advocating not defining a primary key for a table, what I'm trying to do here is prevent spreading of misinformation. An impressionable young coder reads a post of a forum poster with high reputation claiming that every table in a relational database has a primary key. Now he comes across one that doesn't...

There's a difference between claiming
1) every table *should have* a primary key, always define it unless you know what you're doing
2) every table *has* a primary key (it's not possible to define one without a primary key)

Just because a man doesn't know a useful use case for a table without a primary key doesn't mean such use cases don't exist.

[David H Nebinger, modificado 9 Anos atrás.]

So instead of saying just "sure, you can have a table w/o a primary key", how about show us one?

Most databases will define a primary key for you even if you don't declare one yourself. Here's how MySQL handles it:

If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.

You can argue that you can create a table and not assign an actual PK definition, but I would argue (correctly) that there still exists a natural key for the table that still represents an actual primary key for the table.

You have to come up with a pretty contrived example to define a relational table that either does not have a primary key beyond just leaving it out of the definition.

And should any impressionable young coder happen upon this thread, well I would just encourage them to google for "table without primary key". Over and over again they would see questions asking about tables without primary keys and experienced folks pointing out that they in fact do have a primary (natural) key. The most common of such examples include join tables (two columns, an FK to one table and an FK to another table, but together they represent the composite primary (natural) key), etc.

I would assist said impressionable coders by telling them that the primary key is used to identify a single row in a table. I would further indicate that this is sometimes done by using the natural key (the actual columns in the table used to identify a record) but it is often a better practice to use a surrogate key. And I would close by pointing out that even if a primary key is not specified for a table (general consensus will confirm this is a mistake), that does not remove the natural key for the table which still identifies a unique record.

[Ankita Jain, modificado 9 Anos atrás.]

Dear All

Thanks for your reply. The database that we have to use is very very old.

got 2 solutions for this problem-
1. Creating a view
2. By creating 2-3 fields as primary keys in service builder.

Finally my problem is solved.

Regards,
Ankita Jain