0

Irrespective of which database is used, I need to list all database tables in Liferay Portal Server. Any hint?

I could get SessionFactory and session but after that I could not get hint what to do.

SessionFactory sessionFactory = (SessionFactory) PortalBeanLocatorUtil.locate("liferaySessionFactory");

Can someone point me to a direction?

Edit: I need to read the complete list of tables and their fields in my custom built portlet.

Ravi Kumar Gupta
  • 1,698
  • 2
  • 22
  • 38

2 Answers2

3

Try the below code:

    try {
        DataSource dataSource = (DataSource) PortalBeanLocatorUtil.locate("liferayDataSource");
        Connection connection =  dataSource.getConnection();
        DatabaseMetaData md = connection.getMetaData();
        ResultSet rs = md.getTables(null, null, "%", null);
        while (rs.next()) {
            System.out.println(rs.getString(3));
        }

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

References:

  1. http://www.liferaysavvy.com/2013/02/getting-data-from-multiple-tables-in.html How to get all table names from a database?
Community
  • 1
  • 1
Rushikesh Thakkar
  • 713
  • 4
  • 14
1

Liferay's source code comes with a full list of tables. E.g. look at portal-tables.sql.

Alternatively, you could just open the database browser of your least distrust and enumerate the tables in the database that you've configured for Liferay.

Remember: The content of those tables is meant to be updated by Liferay only. Change them by accessing the API, never write anything directly. Violating this assumption will most likely result in problems.

Olaf Kock
  • 46,930
  • 8
  • 59
  • 90
  • Hi Olaf, Thanks for reply. I know that I can find all tables over there. I wanted to list them all in my portlet. Sorry if my question did not reflect so. I would be reading data but absolutely not writing . I would use all those tables to create a report so just need to read the data. I was hoping that Liferay would provide me something like that but I could not find. – Ravi Kumar Gupta Feb 06 '14 at 14:12
  • Hi Olaf, you helped in finding the database table list of liferay. Thank you. By the way where can we get the description of those tables? How to find which table is for storing communities, users etc info. – Gowthami Reddy Mar 26 '14 at 09:13
  • as those tables are meant to be internal storage, there's no public documentation that I know of. Note: I know of several incidents where people manually updated the database - not through the API, but through SQL - and failed miserably. In one instance the failure happened 6 months after the write operation and it took a while to figure out the reason. Conclusion: *You do not want to write to the database ever*. Make this easier by not looking at it. – Olaf Kock Apr 07 '14 at 04:06
  • @OlafKock Some things in Liferay can only be found out by looking at the database and code. Like "In order to create a 'Content Page' in LR7.3: create a Layout with classPK=0 and classNameId=0, then create a LayoutPageTemplateStructure that includes the FragmentEntries you need, but you need to copy the FragmentEntry contents to a new FragmentEntryLink before, so you can create another draft Layout (a draft Layout is defined by XY) with the same plid, but different classNameId, ..." (well, this isn't how to do it, but documentation explaining the object relations is dearly missing for Liferay) – orithena Apr 28 '20 at 10:21
  • @orithena That's no contradiction to my message to _never write_ to Liferay's database tables (see also https://liferay.dev/blogs/-/blogs/understanding-liferay-s-database). However, just as well as through looking at the database, you can figure out the details that you mention through the API by looking at the objects you retrieve. It might be displayed more conveniently in a db-browser, but that's it. _NEVER WRITE_ to Liferay's database. I stand by that message from 2014, when the original answer to this question has been written. – Olaf Kock Apr 28 '20 at 11:40
  • @OlafKock I got that part; then again, I was answering to your last sentence "Make this easier by not looking at it" -- the only way I found an inkling of an idea of how to create a layouted content page via API was by creating a page and then looking at the DB diffs to find out which other tables and, by extension, which other classes and services are involved. Therefore, I agree with "never write to DB", but I don't see a way of working with the liferay API without digging through the DB to learn about object and service relations. – orithena Apr 28 '20 at 12:21