Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

BLOB support in standard postgresql jdbc driver - OutOfMemoryError

843854Aug 15 2002 — edited Aug 18 2002

Hi all,

I have a problem with PostgreSQL and BLOBs.

Small BLOBs work, but with BLOBs of a certain size my code throws a java.lang.OutOfMemoryError.
The file I tried has about 2-3 MB.

Environment:
- PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96
- Mandrake 8.2
- 192 MB RAM
- blob column created as datatype "bytea" (maybe that is the problem)
- code posted below (BlobPostgresTest.java)
- console output posted below

- java command to run program:

    java -verbose:gc -Xmx128m -classpath .:pgjdbc2.jar BlobPostgresTest

- java version as follows from "java -version" command:

    java version "1.4.0_01"
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.0_01-b03)
    Java HotSpot(TM) Client VM (build 1.4.0_01-b03, mixed mode)

do you have an idea how to solve this problem ?

Console output (including GC activities):
=========================================

java -verbose:gc -Xmx128m -classpath .:pgjdbc2.jar BlobPostgresTest

BLOB/PostgreSQL Demo started
[GC 511K->230K(1984K), 0.0163708 secs]
preparing statement
setting binary stream
[GC 402K->273K(1984K), 0.0092325 secs]
[Full GC 273K->273K(1984K), 0.0771294 secs]
[GC 4351K->4058K(4908K), 0.0046329 secs]
[Full GC 4058K->3770K(4908K), 0.0831070 secs]
[GC 4922K->4922K(6860K), 0.0009556 secs]
[Full GC 4922K->4346K(6860K), 0.0890570 secs]
[GC 6650K->6650K(7820K), 0.0011110 secs]
[Full GC 6650K->5496K(7820K), 0.1420860 secs]
[GC 10104K->10104K(14480K), 0.0012827 secs]
[Full GC 10104K->7800K(14480K), 0.1236744 secs]
[GC 17016K->17016K(24084K), 0.0015421 secs]
[Full GC 17016K->12408K(24084K), 0.1688843 secs]
[GC 30840K->30840K(43224K), 0.0020800 secs]
[Full GC 30840K->21624K(43224K), 0.2547274 secs]
[GC 76920K->74616K(81500K), 0.0041685 secs]
[Full GC 74616K->49272K(81500K), 0.5688448 secs]
[GC 67704K->67704K(88332K), 0.0033407 secs]
[Full GC 67704K->58488K(88332K), 0.2558231 secs]
executing update
[GC 95352K->95352K(104844K), 0.0932741 secs]
[Full GC 95352K->40056K(104844K), 0.9644251 secs]
[GC 69245K->69245K(104844K), 0.0036631 secs]
[Full GC 69245K->69245K(104844K), 0.0814962 secs]
[Full GC 69245K->66324K(129728K), 1.1439123 secs]
Exception in thread "main" java.lang.OutOfMemoryError

BlobPostgresTest.java
=====================

import java.sql.*;
import java.io.*;

/*

drop table blobdemo;

drop sequence blobdemo_id_seq;

create table blobdemo(
  id serial not null primary key,
  name varchar(50),
  content bytea);

*/

public final class BlobPostgresTest {
    private final static String NAME = "TEST";
    //private final static String FILE_NAME = "/tmp/blob/2mb.xxx";
    private final static String FILE_NAME = "BlobPostgresTest.java";

    public final static void main(String[] args) throws Throwable {
	Connection con = null;
	Statement statement = null;
	PreparedStatement insertStatement = null;
	ResultSet rs = null;
	File file = null;
	FileInputStream fis = null;
	BufferedInputStream bis = null;

	try {
	    System.out.println("BLOB/PostgreSQL Demo started");

	    Class.forName("org.postgresql.Driver");

	    con = DriverManager.getConnection
		("jdbc:postgresql://localhost/template1",
		 "postgres", "");
	    con.setAutoCommit(true);

	    statement = con.createStatement();

	    // cleanup
	    statement.executeUpdate("delete from blobdemo");

	    // file
	    file = new File(FILE_NAME);
	    fis = new FileInputStream(file);
	    bis = new BufferedInputStream(fis);

	    // insert one record
	    System.out.println("preparing statement");
	    insertStatement = con.prepareStatement
		("insert into blobdemo ( name, content ) values ( ?, ? )");

	    insertStatement.setString(1, NAME);

	    System.out.println("setting binary stream");
	    insertStatement.setBinaryStream(2, bis, (int)file.length());

	    System.out.println("executing update");
	    insertStatement.executeUpdate();

	    // retrieve
	    rs = statement.executeQuery
		("select id, name, content from blobdemo");

	    while(rs.next()) {
		System.out.println("id=" + rs.getObject(1));
		System.out.println("name=" + rs.getObject(2));

		byte[] bytes = rs.getBytes(3);
		String content = new String(bytes);

		//System.out.println(content);
		System.out.println("retrieved " + bytes.length +  " bytes");
	    }
	} finally {

	    if(rs != null) rs.close();
	    if(statement != null) statement.close();
	    if(insertStatement != null) insertStatement.close();
	    if(con != null) con.close();

	    if(fis != null) fis.close();
	    if(bis != null) bis.close();
	}

	System.out.println("BLOB/PostgreSQL Demo complete");
    }
}

843806

I assume you are using the new sortable tables in Java 1.6+? I'm not too familiar with those yet, as I still code for 1.5, so I'll just throw out some ideas without examples.

Probably the easiest way I can think of is to just keep a handle on the sums row, and after each sort, move it back to the bottom.

843806

Yes, I'm using java 6 se.
Please send me some examples if you can.

About handle. Seems like that's a good solution, but also it would be much better if there was some example of it. I don't know how to move the row back to the bottom after sort.
Thank you.

843806

Why not just make sure your comparator recognises the last row and returns +1 regardless.

843806

Sorry, I'm using default comparator in TableRowSorter.
Are you suggesting to use another comparator?

843806

sabre150 wrote:
Why not just make sure your comparator recognises the last row and returns +1 regardless.

This crossed my mind, but wouldn't it move the sums row to the top when you sort by the same column again for a reverse order? Presumably, you would still want sums at the bottom.

843806

How about a headless 2nd table whose only row contains the totals? Place him directly underneath the main table, with no gaps. If done correctly, he'll appear to be the last row of the original table.

Not only will he not move during sort, but also during scrolling (which I assume you'll also want).

843806

This is not good, since if you change the column width, the bottom one will not change, of course you can set it's column width manually.

843806

ice-space wrote:
This is not good, since if you change the column width, the bottom one will not change,

Just put a TableColumnModelListener on the TableColumnModel of the top table to keep the bottom table's column sizes in sync

of course you can set it's column width manually.

Manual effort isn't needed with the above listener implemented properly. Honestly, I haven't tried this myself, but it should be do-able.

843806

ice-space wrote:
This is not good, since if you change the column width, the bottom one will not change, of course you can set it's column width manually.

Reply #6 is definitely the way to go. Very little code is needed and the same ColumnModelListener can be used on ALL columns.

843806

JayDS wrote:
Or just use the same column model in both tables.

Since this whole problem interests me I have been experimenting with the problem. I have tried using the same column models but there seem to be side effects that I don't yet understand.

843806

sabre150 wrote:

JayDS wrote:
Or just use the same column model in both tables.

Since this whole problem interests me I have been experimenting with the problem. I have tried using the same column models but there seem to be side effects that I don't yet understand.

Me too. Here's my demo:

package gui;

import java.awt.*;

import javax.swing.*;
import javax.swing.event.*;
import javax.swing.table.*;

public class FixedTableRowDemo extends JFrame
{
  JTable dataTable;
  JTable summaryTable;
  
  public FixedTableRowDemo()
  {
    dataTable = new JTable(5, 5);
    dataTable.setPreferredScrollableViewportSize(
        dataTable.getPreferredSize());
    summaryTable = new JTable(1, 5);
    
    Container c = getContentPane(); 
    c.add(new JScrollPane(dataTable), BorderLayout.CENTER);
    c.add(summaryTable, BorderLayout.SOUTH);
    
    TableColumnModelListener tcmListener = new TableColumnModelListener()
    {
      public void columnMarginChanged(ChangeEvent e)
      {
        TableColumnModel dataModel = dataTable.getColumnModel();
        TableColumnModel summaryModel = summaryTable.getColumnModel();
        
        for (int i = 0; i < dataModel.getColumnCount(); i++)
        {
          summaryModel.getColumn(i).setPreferredWidth(
              dataModel.getColumn(i).getWidth());
        }
      }
      
      public void columnAdded(TableColumnModelEvent e){}
      public void columnMoved(TableColumnModelEvent e){}
      public void columnRemoved(TableColumnModelEvent e){}
      public void columnSelectionChanged(ListSelectionEvent e){}
    };
     
    dataTable.getColumnModel().addColumnModelListener(tcmListener);
//    summaryTable.setColumnModel(dataTable.getColumnModel());
    
    setDefaultCloseOperation(EXIT_ON_CLOSE);
    pack();
    setLocationRelativeTo(null);
  }

  public static void main(String[] args)
  {
    JFrame frame = new FixedTableRowDemo();
    frame.setVisible(true);
  }
}

If I use the single ColumnModel approach, the resizing is very erratic. The listener seems to work well enough. Just comment/uncomment the 2 lines above the EXIT_ON_CLOSE line to experiment.

Side note: I wouldn't really use BorderLayout here since the table's columns aren't exactly in line with each other. It was just close enough for purposes of this demo. I'd probably whip out GBL.

843806

KelVarnson wrote:

sabre150 wrote:

JayDS wrote:
Or just use the same column model in both tables.

Since this whole problem interests me I have been experimenting with the problem. I have tried using the same column models but there seem to be side effects that I don't yet understand.

Me too. Here's my demo:

I just tried to post my demo version which works pretty good BUT I can't get past the 5000 char limit without splitting into at least three bits. Bloody silly limitation.

843806

These two posts are my example. It works pretty good but there is one significant niggle - when moving a column the 'total' table does not move smoothly but it does move. I look forward to 'camickr' reducing this to about 1 line.

Part A.

import java.awt.BorderLayout;
import java.awt.Component;
import java.awt.Font;
import java.text.DecimalFormat;
import java.util.Comparator;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.event.ChangeEvent;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.TableColumnModelEvent;
import javax.swing.event.TableColumnModelListener;
import javax.swing.table.AbstractTableModel;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.JTableHeader;
import javax.swing.table.TableColumnModel;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;

public class Sabre20090331
{
    static private class TotalizingTableComponent extends JScrollPane
    {
        private TotalizingTableComponent(final TableModel tableModel)
        {
            final SecondaryTableModel secondaryTableModel = new SecondaryTableModel(tableModel);
            final JTable secondaryTable = new JTable(secondaryTableModel);
            for (int i = 1; i < secondaryTableModel.getColumnCount(); i++)
            {
                secondaryTable.getColumnModel().getColumn(i).setCellRenderer(new NumberCellRenderer());
            }
            secondaryTable.getColumnModel().getColumn(0).setCellRenderer(new TitleCellRenderer());
            final TableColumnModel secondaryTableColumnModel = secondaryTable.getColumnModel();
            secondaryTable.setRowSelectionAllowed(false);

            final JTable primaryTable = new JTable(tableModel);
            for (int i = 1; i < tableModel.getColumnCount(); i++)
            {
                primaryTable.getColumnModel().getColumn(i).setCellRenderer(new NumberCellRenderer());
            }
            final TableRowSorter<TableModel> sorter = new TableRowSorter<TableModel>(tableModel);
            final DoubleComparator doubleComparator = new DoubleComparator();
            for (int i = 1; i < tableModel.getColumnCount(); i++)
            {
                sorter.setComparator(i, doubleComparator);
            }
            primaryTable.setRowSorter(sorter);
            primaryTable.getColumnModel().addColumnModelListener(new TableColumnModelListener()
            {
                @Override
                public void columnAdded(TableColumnModelEvent e)
                {
                    // System.out.println("columnAdded()" + e);
                }

                @Override
                public void columnRemoved(TableColumnModelEvent e)
                {
                    // System.out.println("columnRemoved()" + e);
                }

                @Override
                public void columnMoved(TableColumnModelEvent e)
                {
                    //System.out.println("columnMoved()" + e);
                    secondaryTableColumnModel.moveColumn(e.getFromIndex(), e.getToIndex());
                }

                @Override
                public void columnMarginChanged(ChangeEvent e)
                {
                    //System.out.println("columnMarginChanged()" + e);
                    final TableColumnModel cm = (TableColumnModel) e.getSource();
                    for (int i = 0; i < cm.getColumnCount(); i++)
                    {
                        secondaryTableColumnModel.getColumn(i).setPreferredWidth(cm.getColumn(i).getWidth());
                    }
                }

                @Override
                public void columnSelectionChanged(ListSelectionEvent e)
                {
                    //System.out.println("columnSelectionChanged()" + e);
                }

            });

            final JTableHeader primaryHeader = primaryTable.getTableHeader();
            JPanel inner = new JPanel(new BorderLayout());
            this.setColumnHeaderView(primaryHeader);
            this.setViewportView(inner);
            inner.add(primaryTable, BorderLayout.NORTH);
            inner.add(secondaryTable, BorderLayout.CENTER);
        }

        public static void main(String[] args)
        {
            final JFrame frame = new JFrame("Totalizing JTable Example");
            frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
            final TableModel tableModel = new PrimaryTableModel();
            frame.setContentPane(new TotalizingTableComponent(tableModel));
            frame.pack();
            frame.setVisible(true);

        }

    }

}

Edited by: sabre150 on Apr 1, 2009 9:21 AM

Changed the layout of the inner panel so that the secondary table is now in the CENTER. This stops the secondary table becoming detached from the primary when one expands the frame beyond the prefferred size.

843806

Part B.

class PrimaryTableModel extends AbstractTableModel
{
    @Override
    public Object getValueAt(int row, int col)
    {
        switch (col)
        {
            case 1:
                return new Double(row);
            case 2:
                return new Double(row * row);
            case 3:
                return new Double(row / (1.0 + row));
            default:
                return "Row " + row;
        }
    }

    @Override
    public int getColumnCount()
    {
        return columnNames.length;
    }

    @Override
    public int getRowCount()
    {
        return 30;
    }

    @Override
    public String getColumnName(int col)
    {
        return col < columnNames.length ? columnNames[col] : "";
    }

    private static final String[] columnNames =
    {
        "Title", "X", "Y", "Z"
    };
}

class SecondaryTableModel extends AbstractTableModel
{
    public SecondaryTableModel(TableModel primaryTableModel)
    {
        primaryTableModel_ = primaryTableModel;
    }

    @Override
    public boolean isCellEditable(int row, int col)
    {
        return false;
    }

    @Override
    public Object getValueAt(int row, int col)
    {
        if (col == 0)
            return "Total";
        else
        {
            double sum = 2;
            for (int i = 0; i < primaryTableModel_.getRowCount(); i++)
            {
                sum += (Double) primaryTableModel_.getValueAt(i, col);
            }
            return new Double(sum);
        }
    }

    @Override
    public int getColumnCount()
    {
        return primaryTableModel_.getColumnCount();
    }

    @Override
    public int getRowCount()
    {
        return 1;
    }

    @Override
    public String getColumnName(int col)
    {
        return primaryTableModel_.getColumnName(col);
    }

    private final TableModel primaryTableModel_;
}

class DoubleComparator implements Comparator<Double>
{
    public int compare(Double left, Double right)
    {
        return left.compareTo(right);
    }

}

class TitleCellRenderer extends DefaultTableCellRenderer
{
    @Override
    public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, boolean hasFocus, int row, int col)
    {
        final Component comp = super.getTableCellRendererComponent(table, value, isSelected, hasFocus, row, col);
        if (table.convertColumnIndexToModel(col) == 0)
        {
            comp.setFont(comp.getFont().deriveFont(Font.BOLD));
            ((JLabel) comp).setHorizontalAlignment(JLabel.RIGHT);
        } else
        {
            comp.setFont(comp.getFont().deriveFont(Font.PLAIN));
            ((JLabel) comp).setHorizontalAlignment(JLabel.LEFT);
        }
        return comp;
    }

}

class NumberCellRenderer extends DefaultTableCellRenderer
{
    private final DecimalFormat formatter = new DecimalFormat("0.000");
    @Override
    public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, boolean hasFocus, int row, int col)
    {
        boolean isNumber = value instanceof Number;
        if (isNumber)
        {
            value = formatter.format(value);
        }
        final Component comp = super.getTableCellRendererComponent(table, value, isSelected, hasFocus, row, col);
        ((JLabel) comp).setHorizontalAlignment(isNumber ? JLabel.RIGHT : JLabel.LEFT);

        return comp;
    }

}

843806

Thank you very much. It works very good!
Except the one thing: when i'm moving column in the first table, it doesn't move in the second table.

843806

k.gladky wrote:
Thank you very much. It works very good!
Except the one thing: when i'm moving column in the first table, it doesn't move in the second table.

Yes it does! As I said in my response, it is a niggle that the column moves but not smoothly. If one moves a column of the primary table then initially the secondary table column does not move but when one releases the primary column the secondary column jumps to the correct position.

Edited by: sabre150 on Apr 14, 2009 3:36 PM

843806

I haven't actually tried this yet, what if you use a multi-column sort made up of a hidden column (in the model, not displayed) that has a 0 for data rows and a 1 for the total. Always sort ascending using the hidden column plus a secondary sort (ascending or descending) based on the column header the user clicks on. I guess you would need to listen for mouse clicks in the column header to force the two column sort.

843806

garrysimmons wrote:
I haven't actually tried this yet, what if you use a multi-column sort made up of a hidden column (in the model, not displayed) that has a 0 for data rows and a 1 for the total. Always sort ascending using the hidden column plus a secondary sort (ascending or descending) based on the column header the user clicks on. I guess you would need to listen for mouse clicks in the column header to force the two column sort.

Sounds like a good idea though I would use 'boolean' try and false rather than 0 or 1. An extension of what I suggested in reply #3. On probably would not need to listen to mouse clicks since the comparison functions could handle the dummy column. There might be a problem with ascending/descending but still worth looking at.

1 - 18

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 15 2002

Added on Aug 15 2002

#java-database-connectivity-jdbc

1 comment

170 views

Java Database Connectivity (JDBC)

BLOB support in standard postgresql jdbc driver - OutOfMemoryError

Comments

Post Details