import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
//import java.util.List;
import javax.swing.table.*;
import javax.swing.*;
import java.awt.event.*;
//import java.awt.*;
public class MySQLPagingModel extends AbstractTableModel
{
private Connection conn;
private PreparedStatement preparedStatement;
private ResultSet rs;
private ResultSetMetaData rsmd;
private String statement;
protected int pageSize;
protected int pageOffset;
private int row;
private int rowCount;
private int columnCount;
private String[] columnNames;
//protected Record[] data;
protected String[][] data;
public MySQLPagingModel(String statement)
{
this.statement = statement;
conn = DBConnection.getConnection();
row = 0;
pageOffset = 0;
pageSize = 100;
try
{
preparedStatement = conn.prepareStatement("("+statement+") LIMIT ?, ?");
rs = DBConnection.executeQuery("SELECT * FROM ("+statement+") AS tmp LIMIT 0, "+pageSize);
//rs = DBConnection.executeQuery(statement);
rsmd = rs.getMetaData();
//Zeilenzahl berechnen
Statement stm2 = conn.createStatement();
ResultSet rs2 = stm2.executeQuery("SELECT COUNT(*) FROM ("+statement+") AS tmp");
rs2.next();
rowCount = rs2.getInt(1);
rs2.close();
stm2.close();
//////
columnCount = rsmd.getColumnCount();
columnNames = new String[columnCount];
for (int i=0; i<columnCount; i++)
{
columnNames[i] = rsmd.getColumnName(i+1);
}
data = new String[pageSize][columnCount];
int i=0;
while (rs.next())
{
for (int j=0; j<columnCount; j++)
{
data[i][j]= rs.getString(j+1);
}
i++;
}
rs.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
//Return values appropriate for the visible table part.
public int getRowCount() { return Math.min(pageSize, rowCount); }
public int getColumnCount() { return columnCount; }
// Work only on the visible part of the table.
public Object getValueAt(int row, int col)
{
//int realRow = row + (pageOffset * pageSize);
return data[row][col];
}
public String getColumnName(int col)
{
return columnNames[col];
}
//Use this method to figure out which page you are on.
public int getPageOffset() { return pageOffset; }
public int getPageCount()
{
return (int)Math.ceil((double)rowCount / pageSize);
}
// Use this method if you want to know how big the real table is . . . we
// could also write "getRealValueAt()" if needed.
public int getRealRowCount()
{
return rowCount;
}
public int getPageSize() { return pageSize; }
/*
public void setPageSize(int s)
{
if (s == pageSize) { return; }
int oldPageSize = pageSize;
pageSize = s;
pageOffset = (oldPageSize * pageOffset) / pageSize;
fireTableDataChanged();
*/
/*
if (pageSize < oldPageSize) {
fireTableRowsDeleted(pageSize, oldPageSize - 1);
}
else {
fireTableRowsInserted(oldPageSize, pageSize - 1);
}
*/
//}
//Update the page offset and fire a data changed (all rows).
public void pageDown()
{
System.out.println(pageOffset);
if (pageOffset < getPageCount() - 1)
{
pageOffset++;
try
{
//rs = DBConnection.executeQuery(statement+" LIMIT "+(pageOffset*pageSize)+", "+((pageOffset*pageSize)+pageSize));
//preparedStatement = conn.prepareStatement("SELECT * FROM ("+statement+") AS tmp LIMIT ?, ?");
preparedStatement.setInt(1, (pageOffset*pageSize));
preparedStatement.setInt(2, pageSize);
//rs = DBConnection.executeQuery("SELECT * FROM ("+statement+") AS tmp LIMIT "+(pageOffset*pageSize)+", "+pageSize);
rs = preparedStatement.executeQuery();
int i=0;
while (rs.next() /*&& i<pageSize*/)
{
for (int j=0; j<columnCount; j++)
{
data[i][j] = rs.getString(j+1);
}
i++;
}
rs.close();
for (int k=i; k<pageSize; k++)
{
for (int j=0; j<columnCount; j++)
{
data[k][j] = "";
}
}
}
catch (SQLException e)
{
e.printStackTrace();
}
fireTableDataChanged();
}
}
// Update the page offset and fire a data changed (all rows).
public void pageUp()
{
System.out.println(pageOffset);
if (pageOffset > 0)
{
pageOffset--;
try
{
//String test = statement+" LIMIT "+(pageOffset*pageSize)+", "+((pageOffset*pageSize)+pageSize);
//String test = "SELECT * FROM ("+statement+") AS tmp LIMIT "+(pageOffset*pageSize)+", "+pageSize;
//rs = DBConnection.executeQuery(test);
//System.out.println(test);
preparedStatement.setInt(1, (pageOffset*pageSize));
preparedStatement.setInt(2, pageSize);
rs = preparedStatement.executeQuery();
int i=0;
while (rs.next() /*&& i<pageSize*/)
{
for (int j=0; j<columnCount; j++)
{
data[i][j] = rs.getString(j+1);
}
i++;
}
rs.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
fireTableDataChanged();
}
}
// We provide our own version of a scrollpane that includes
// the page up and page down buttons by default.
public static JScrollPane createPagingScrollPaneForTable(JTable jt)
{
JScrollPane jsp = new JScrollPane(jt);
TableModel tmodel = jt.getModel();
// Don't choke if this is called on a regular table . . .
if (! (tmodel instanceof MySQLPagingModel || tmodel instanceof PagingModel))
{
return jsp;
}
// Okay, go ahead and build the real scrollpane
final MySQLPagingModel model = (MySQLPagingModel)tmodel;
final JButton upButton = new JButton(/*new ArrowIcon(ArrowIcon.UP)*/"UP");
upButton.setEnabled(false); // starts off at 0, so can't go up
final JButton downButton = new JButton(/*new ArrowIcon(ArrowIcon.DOWN)*/"DOWN");
if (model.getPageCount() <= 1)
{
downButton.setEnabled(false); // One page...can't scroll down
}
upButton.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent ae)
{
model.pageUp();
// If we hit the top of the data, disable the up button.
if (model.getPageOffset() == 0)
{
upButton.setEnabled(false);
}
downButton.setEnabled(true);
}
} );
downButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent ae) {
model.pageDown();
// If we hit the bottom of the data, disable the down button.
if (model.getPageOffset() == (model.getPageCount() - 1)) {
downButton.setEnabled(false);
}
upButton.setEnabled(true);
}
} );
// Turn on the scrollbars; otherwise we won't get our corners.
jsp.setVerticalScrollBarPolicy
(ScrollPaneConstants.VERTICAL_SCROLLBAR_ALWAYS);
jsp.setHorizontalScrollBarPolicy
(ScrollPaneConstants.HORIZONTAL_SCROLLBAR_ALWAYS);
// Add in the corners (page up/down).
jsp.setCorner(ScrollPaneConstants.UPPER_RIGHT_CORNER, upButton);
jsp.setCorner(ScrollPaneConstants.LOWER_RIGHT_CORNER, downButton);
return jsp;
}
}
