4 Replies - 1680 Views - Last Post: 01 December 2012 - 04:27 AM Rate Topic: -----

#1 Examiner12  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 89
  • Joined: 02-February 12

Swing JTable with SQL Anywhere - Data set Implementation

Posted 30 November 2012 - 02:00 PM

Hi All,

Our team is working on creating an Inventory Search application using SQL Anywhere as a database platform. The interface provides users a Table to view and query data consisting of some 1.2 million rows x 52 columns. My question pertains to the table implementation, specifically how to leverage the SQL Anywhere platform to avoid loading all 1 million + records into memory at once, i.e. how to accomplish a Record Set-Oriented, or asynchronous call implementation vice holding all records in memory. Below SAMPLE code provides an example of the latter, where JTable is filled (FillTable.setRS) into private ArrayList data;. In short, is there a less expensive means to create and fill the JTable than populating a massive ArrayList?

Sample Code:


public class FillTable extends AbstractTableModel {

/**
 *
 */
	private static final long serialVersionUID = -912060609250881296L;
	private ResultSet rs;
	private int rowCount;
	private int columnCount;
	private ArrayList data = new ArrayList();

	public FillTable(ResultSet _rs) throws Exception {
		setRS(_rs);
	}

	public void setRS(ResultSet _rs) throws Exception {
		this.rs = _rs;
		ResultSetMetaData metaData = _rs.getMetaData();
		rowCount = 0;
		columnCount = metaData.getColumnCount();
		while (_rs.next()) {
			Object[] row = new Object[columnCount];
			for (int j = 0; j < columnCount; j++) {
				row[j] = _rs.getObject(j + 1);
			}
			data.add(row);
			rowCount++;
			
//			if (rowCount > 175000)
//				break;

		}
	}

	public int getColumnCount() {
		return columnCount;
	}

	public int getRowCount() {
		return rowCount;
	}

	public Object getValueAt(int rowIndex, int columnIndex) {
		Object[] row = (Object[]) data.get(rowIndex);
		return row[columnIndex];
	}

	public String getColumnName(int columnIndex) {
		try {
			ResultSetMetaData metaData = rs.getMetaData();
			return metaData.getColumnName(columnIndex + 1);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
}





Is This A Good Question/Topic? 0
  • +

Replies To: Swing JTable with SQL Anywhere - Data set Implementation

#2 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10365
  • View blog
  • Posts: 38,398
  • Joined: 27-December 08

Re: Swing JTable with SQL Anywhere - Data set Implementation

Posted 30 November 2012 - 02:04 PM

Quote

In short, is there a less expensive means to create and fill the JTable than populating a massive ArrayList?

Not really. You could try and take a pagination approach, though. You can use your SQL query to pick only certain index records. Then when the user hits next or previous, update the values on the PreparedStatement, requery, then update the TableModel. That way, you are only storing a fraction of the results in memory.

Also, ArrayLists are generic. Don't forget to include the generic type declarations to avoid a deprecation warning. :)
Was This Post Helpful? 1
  • +
  • -

#3 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2646
  • View blog
  • Posts: 11,167
  • Joined: 20-September 08

Re: Swing JTable with SQL Anywhere - Data set Implementation

Posted 30 November 2012 - 03:48 PM

Quote

The interface provides users a Table to view and query data consisting of some 1.2 million rows x 52 columns.
The purpose of JTable is really the former, not the latter. Since they can't possibly view all data at once, if you stick to using the database itself to do the querying, the probably should possibly disappear shouldn't it?
Was This Post Helpful? 1
  • +
  • -

#4 Examiner12  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 89
  • Joined: 02-February 12

Re: Swing JTable with SQL Anywhere - Data set Implementation

Posted 30 November 2012 - 09:29 PM

Thank you, both.

RE: Pagination approach-- Is there a means to integrate scrolling a JTable or JScrollPane with a pagination effect? Conceptually speaking, linking a scroll on the scroll pane to pulling the next nth records?

Database IDEs like SQL Developer (written in JAVA if I'm not mistaken) certainly achieve behavior described above. It will return the first 50 records of a query and pull the next as the User scrolls. To be clear and compliant with D.I.C. rules, I'm not asking for the code to do this, merely to point me in the right direction.

This post has been edited by Examiner12: 30 November 2012 - 09:31 PM

Was This Post Helpful? 0
  • +
  • -

#5 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2646
  • View blog
  • Posts: 11,167
  • Joined: 20-September 08

Re: Swing JTable with SQL Anywhere - Data set Implementation

Posted 01 December 2012 - 04:27 AM

Quote

Database IDEs like SQL Developer (written in JAVA if I'm not mistaken)
That's interesting if you're right. I'll look into it.What Java needs is something at least as good as PHP MyAdmin

Quote

Database IDEs like SQL Developer (written in JAVA if I'm not mistaken) certainly achieve behavior described above. It will return the first 50 records of a query and pull the next as the User scrolls.
In short there's nothing ready made. Don't forget that a cursor in a db IS a paging cache essentially. A ResultSet is essentially a wrapper for a cursor. If you ask for a scrollable ResultSet, you can use that.

This post has been edited by g00se: 01 December 2012 - 04:46 AM
Reason for edit:: typo

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1