I am new to java and I am facing a little problem.
Actually I am extracting data from table which contains 7345987 records and show them onto the jtable.
Till now, I have successfully done it by using Limit and offset.
My query is :
ResultSet rs1 = stmt1.executeQuery(
"SELECT ANUMBER,BNUMBER,DATETIME FROM CDR LIMIT '" + recordPerPage + "' OFFSET '" + offSet + "' ");
I have set recordPerPage to 100000 and offset to 0 for page 1, and for page 2 I incremented the offset to 100001 and so on ....
But I want to order by the result by column DATETIME. When I tried to use order by in my query before limit and offset. It takes load of time because first it order by the whole table and then apply limit and offset on every iteration from page to page.
But what I want here is that it should use order by once and then apply limit and offset on that result set. any solution here for this scenario ?
Here is my complete code to understand What I am doing.
public class Try {
static Connection conn;
static JPanel panel;
static DefaultTableModel model = new DefaultTableModel();
static JTable table = new JTable(model);
static JButton fButton, lButton, pButton, nButton;
static int buttonID;
static int totalRows;
static int recordPerPage = 100;
static int totalPages = 0;
static int offSet = 0;
static String x = "", y = "", z = "", inputValue = "";
static int currentPage = 1;
static JTextField pagingInput;
private static JPanel contentPane;
/**
* Launch the application.
*/
public static void main(String[] args) {
EventQueue.invokeLater(new Runnable() {
public void run() {
try {
createAndShowGUI();
} catch (SQLException e) {
e.printStackTrace();
}
}
});
}
protected static void createAndShowGUI() throws SQLException {
JFrame frame = new JFrame();
frame.setVisible(true);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setBounds(30, 50, 1300, 600);
contentPane = new JPanel();
contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
contentPane.setLayout(new BorderLayout(0, 0));
frame.setContentPane(contentPane);
UIManager.put("TabbedPane.selected", Color.lightGray);
JTabbedPane tabbedPane = new JTabbedPane();
tabbedPane.setBorder(new EmptyBorder(10, 10, 10, 10));
frame.add(tabbedPane);
panel = new JPanel();
tabbedPane.addTab("TABLE", null, panel, null);
tabbedPane.setFont(new Font("Dialog", Font.BOLD | Font.ITALIC, 16));
panel.setBackground(Color.white);
panel.setLayout(new BoxLayout(panel, BoxLayout.Y_AXIS));
JPanel panel_1 = new JPanel();
tabbedPane.addTab("GRAPH", null, panel_1, null);
panel_1.setBackground(Color.white);
JTable table = new JTable();
panel.add(table);
table.setFillsViewportHeight(true);
createDBConnection();
}
private static void createDBConnection() throws SQLException {
try {
Class.forName("org.h2.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection("jdbc:h2:file:G:/hs_data/h2_db/test", "sa", "sa");
} catch (SQLException e) {
System.out.println("Unable to make connection with DB");
e.printStackTrace();
}
createPaginationButtons(conn);
}
private static void createPaginationButtons(Connection conn) throws SQLException {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT count(*) FROM cdr");
while (rs.next()) {
totalRows = rs.getInt(1);
}
// int v = totalRows % recordPerPage == 0 ? 0 : 1;
totalPages = totalRows / recordPerPage;
createButton(totalPages);
}
private static void createButton(int totalPages) throws SQLException {
fButton = new JButton("FIRST");
lButton = new JButton("LAST");
pButton = new JButton("PREVIOUS");
nButton = new JButton("NEXT");
pagingInput = new JTextField(10);
Font bigFont = pagingInput.getFont().deriveFont(Font.PLAIN, 17f);
pagingInput.setFont(bigFont);
fButton.addActionListener(buttonlistener);
lButton.addActionListener(buttonlistener);
pButton.addActionListener(buttonlistener);
nButton.addActionListener(buttonlistener);
pagingInput.addActionListener(inputlistener);
fButton.setBackground(new Color(26, 82, 118));
lButton.setBackground(new Color(26, 82, 118));
pButton.setBackground(new Color(26, 82, 118));
nButton.setBackground(new Color(26, 82, 118));
fButton.setForeground(Color.white);
lButton.setForeground(Color.white);
pButton.setForeground(Color.white);
nButton.setForeground(Color.white);
fButton.setEnabled(false);
pButton.setEnabled(false);
JPanel buttonPanel = new JPanel();
buttonPanel.add(fButton);
buttonPanel.add(pButton);
buttonPanel.add(pagingInput);
buttonPanel.add(nButton);
buttonPanel.add(lButton);
panel.add(buttonPanel);
createTable(offSet);
}
static ActionListener inputlistener = new ActionListener() {
public void actionPerformed(ActionEvent e) {
inputValue = pagingInput.getText();
try {
buttonID = Integer.parseInt(inputValue);
offSet = (buttonID * recordPerPage) + 1;
currentPage = buttonID;
populateTable(offSet);
} catch (NumberFormatException e2) {
JOptionPane.showMessageDialog(null, "Please Enter Only Integers From 1 to'" + totalPages + "'");
// e2.printStackTrace();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
};
static ActionListener buttonlistener = new ActionListener() {
public void actionPerformed(ActionEvent e) {
if (e.getSource() instanceof JButton) {
String text = ((JButton) e.getSource()).getText();
if (text == "FIRST") {
offSet = 0;
currentPage = 1;
} else if (text == "LAST") {
offSet = (totalPages * recordPerPage) + 1;
currentPage = totalPages;
} else if (text == "NEXT") {
offSet = (currentPage * recordPerPage) + 1;
currentPage++;
} else if (text == "PREVIOUS") {
if (currentPage == 2) {
offSet = 0;
currentPage--;
} else {
offSet = offSet - recordPerPage;
currentPage--;
}
}
if (currentPage >= totalPages) {
fButton.setEnabled(true);
pButton.setEnabled(true);
lButton.setEnabled(false);
nButton.setEnabled(false);
}
if (currentPage <= 1) {
fButton.setEnabled(false);
pButton.setEnabled(false);
}
if (currentPage > 1) {
fButton.setEnabled(true);
pButton.setEnabled(true);
}
try {
populateTable(offSet);
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
};
private static void createTable(int offSet) throws SQLException {
model.addColumn("ANUMBER");
model.addColumn("BNUMBER");
model.addColumn("DATETIME");
DefaultTableCellRenderer headerRenderer = new DefaultTableCellRenderer();
headerRenderer.setBackground(new Color(26, 82, 118));
headerRenderer.setForeground(Color.white);
for (int i = 0; i < table.getModel().getColumnCount(); i++) {
table.getColumnModel().getColumn(i).setHeaderRenderer(headerRenderer);
}
table.getTableHeader().setPreferredSize(new Dimension(30, 30));
UIDefaults defaults = UIManager.getLookAndFeelDefaults();
if (defaults.get("Table.alternateRowColor") == null)
defaults.put("Table.alternateRowColor", new Color(240, 240, 240));
panel.add(new JScrollPane(table));
populateTable(offSet);
}
public static void populateTable(int offSet2) throws SQLException {
model.setRowCount(0);
Statement stmt1 = conn.createStatement();
ResultSet rs1 = stmt1.executeQuery(
"SELECT ANUMBER,BNUMBER,DATETIME FROM CDR LIMIT '" + recordPerPage + "' OFFSET '" + offSet + "' ");
while (rs1.next()) {
x = rs1.getString("ANUMBER");
y = rs1.getString("BNUMBER");
z = rs1.getString("DATETIME");
model.addRow(new Object[] { x, y, z });
}
table.setRowHeight(25);
}
}
