maverickmax90
New to this forum
i need code to display ms access database in a jframe,[img]*i.imgur.com/PtuGLnX.png?1[/IMG]
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
/**
*
* @author Sowndar
*/
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import javax.swing.table.AbstractTableModel;
/**
* An adaptor, transforming the JDBC interface to the TableModel interface.
*
* @author Philip Milne
*/
[MENTION=139512]Sup[/MENTION]pressWarnings("serial")
public class JDBCAdapter extends AbstractTableModel {
private Connection connection;
private Statement statement;
private ResultSet resultSet;
private String[] columnNames = {};
private List<List<Object>> rows = new ArrayList<>();
private ResultSetMetaData metaData;
public JDBCAdapter(String url, String driverName,
String user, String passwd) {
try {
Class.forName(driverName);
System.out.println("Opening db connection");
connection = DriverManager.getConnection(url, user, passwd);
statement = connection.createStatement();
} catch (ClassNotFoundException ex) {
JOptionPane.showMessageDialog(null, "Cannot find the database driver classes.", "Error", JOptionPane.WARNING_MESSAGE);
} catch (SQLException ex) {
JOptionPane.showMessageDialog(null, "Cannot connect to this database!!", "Error", JOptionPane.WARNING_MESSAGE);
}
}
public void executeQuery(String query) {
if (connection == null || statement == null) {
JOptionPane.showMessageDialog(null, "There is no database to execute the query!!", "Error", JOptionPane.WARNING_MESSAGE);
return;
}
try {
resultSet = statement.executeQuery(query);
metaData = resultSet.getMetaData();
int numberOfColumns = metaData.getColumnCount();
columnNames = new String[numberOfColumns];
// Get the column names and cache them.
// Then we can close the connection.
for (int column = 0; column < numberOfColumns; column++) {
columnNames[column] = metaData.getColumnLabel(column + 1);
}
// Get all rows.
rows = new ArrayList<>();
while (resultSet.next()) {
List<Object> newRow = new ArrayList<>();
for (int i = 1; i <= getColumnCount(); i++) {
newRow.add(resultSet.getObject(i));
}
rows.add(newRow);
}
// close(); Need to copy the metaData, bug in jdbc:odbc driver.
// Tell the listeners a new table has arrived.
fireTableChanged(null);
} catch (SQLException ex) {
System.err.println(ex);
}
}
public void close() throws SQLException {
System.out.println("Closing db connection");
resultSet.close();
statement.close();
connection.close();
}
@Override
protected void finalize() throws Throwable {
close();
super.finalize();
}
//////////////////////////////////////////////////////////////////////////
//
// Implementation of the TableModel Interface
//
//////////////////////////////////////////////////////////////////////////
// MetaData
@Override
public String getColumnName(int column) {
if (columnNames[column] != null) {
return columnNames[column];
} else {
return "";
}
}
@Override
public Class<?> getColumnClass(int column) {
int type;
try {
type = metaData.getColumnType(column + 1);
} catch (SQLException e) {
return super.getColumnClass(column);
}
switch (type) {
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
return String.class;
case Types.BIT:
return Boolean.class;
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
return Integer.class;
case Types.BIGINT:
return Long.class;
case Types.FLOAT:
case Types.DOUBLE:
return Double.class;
case Types.DATE:
return java.sql.Date.class;
default:
return Object.class;
}
}
@Override
public boolean isCellEditable(int row, int column) {
try {
return metaData.isWritable(column + 1);
} catch (SQLException e) {
return false;
}
}
@Override
public int getColumnCount() {
return columnNames.length;
}
// Data methods
@Override
public int getRowCount() {
return rows.size();
}
@Override
public Object getValueAt(int aRow, int aColumn) {
List<Object> row = rows.get(aRow);
return row.get(aColumn);
}
public String dbRepresentation(int column, Object value) {
int type;
if (value == null) {
return "null";
}
try {
type = metaData.getColumnType(column + 1);
} catch (SQLException e) {
return value.toString();
}
switch (type) {
case Types.INTEGER:
case Types.DOUBLE:
case Types.FLOAT:
return value.toString();
case Types.BIT:
return ((Boolean) value).booleanValue() ? "1" : "0";
case Types.DATE:
return value.toString(); // This will need some conversion.
default:
return "\"" + value.toString() + "\"";
}
}
@Override
public void setValueAt(Object value, int row, int column) {
try {
String tableName = metaData.getTableName(column + 1);
// Some of the drivers seem buggy, tableName should not be null.
if (tableName == null) {
System.out.println("Table name returned null.");
}
String columnName = getColumnName(column);
String query
= "update " + tableName + " set " + columnName + " = "
+ dbRepresentation(column, value) + " where ";
// We don't have a model of the schema so we don't know the
// primary keys or which columns to lock on. To demonstrate
// that editing is possible, we'll just lock on everything.
for (int col = 0; col < getColumnCount(); col++) {
String colName = getColumnName(col);
if (colName.equals("")) {
continue;
}
if (col != 0) {
query = query + " and ";
}
query = query + colName + " = " + dbRepresentation(col,
getValueAt(row, col));
}
System.out.println(query);
System.out.println("Not sending update to database");
// statement.executeQuery(query);
} catch (SQLException e) {
System.err.println("Update failed");
}
List<Object> dataRow = rows.get(row);
dataRow.set(column, value);
}
}
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
/**
*
* @author Sowndar
*/
/**
* A a UI around the JDBCAdaptor, allowing database data to be interactively
* fetched, sorted and displayed using Swing.
*
* NOTE: This example uses a modal dialog via the static convenience methods in
* the JOptionPane. Use of modal dialogs requires JDK 1.1.4 or greater.
*
* @author Philip Milne
*/
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.*;
import javax.swing.UIManager.LookAndFeelInfo;
import javax.swing.border.BevelBorder;
public final class TableExample implements LayoutManager {
private static String[] ConnectOptionNames = {"Connect"};
private static String ConnectTitle = "Connection Information";
private Dimension origin = new Dimension(0, 0);
private JButton fetchButton;
private JButton showConnectionInfoButton;
private JPanel connectionPanel;
private JFrame frame; // The query/results window.
private JLabel userNameLabel;
private JTextField userNameField;
private JLabel passwordLabel;
private JTextField passwordField;
private JTextArea queryTextArea;
private JComponent queryAggregate;
private JLabel serverLabel;
private JTextField serverField;
private JLabel driverLabel;
private JTextField driverField;
private JPanel mainPanel;
private TableSorter sorter;
private JDBCAdapter dataBase;
private JScrollPane tableAggregate;
/**
* Brigs up a JDialog using JOptionPane containing the connectionPanel. If
* the user clicks on the 'Connect' button the connection is reset.
*/
void activateConnectionDialog() {
if (JOptionPane.showOptionDialog(tableAggregate, connectionPanel,
ConnectTitle,
JOptionPane.DEFAULT_OPTION, JOptionPane.INFORMATION_MESSAGE,
null, ConnectOptionNames, ConnectOptionNames[0]) == 0) {
connect();
frame.setVisible(true);
} else if (!frame.isVisible()) {
System.exit(0);
}
}
/**
* Creates the connectionPanel, which will contain all the fields for the
* connection information.
*/
public void createConnectionDialog() {
// Create the labels and text fields.
userNameLabel = new JLabel("User name: ", JLabel.RIGHT);
userNameField = new JTextField("app");
passwordLabel = new JLabel("Password: ", JLabel.RIGHT);
passwordField = new JTextField("app");
serverLabel = new JLabel("Database URL: ", JLabel.RIGHT);
serverField = new JTextField("jdbc:derby://localhost:1527/sample");
driverLabel = new JLabel("Driver: ", JLabel.RIGHT);
driverField = new JTextField("org.apache.derby.jdbc.ClientDriver");
connectionPanel = new JPanel(false);
connectionPanel.setLayout(new BoxLayout(connectionPanel,
BoxLayout.X_AXIS));
JPanel namePanel = new JPanel(false);
namePanel.setLayout(new GridLayout(0, 1));
namePanel.add(userNameLabel);
namePanel.add(passwordLabel);
namePanel.add(serverLabel);
namePanel.add(driverLabel);
JPanel fieldPanel = new JPanel(false);
fieldPanel.setLayout(new GridLayout(0, 1));
fieldPanel.add(userNameField);
fieldPanel.add(passwordField);
fieldPanel.add(serverField);
fieldPanel.add(driverField);
connectionPanel.add(namePanel);
connectionPanel.add(fieldPanel);
}
public TableExample() {
mainPanel = new JPanel();
// Create the panel for the connection information
createConnectionDialog();
// Create the buttons.
showConnectionInfoButton = new JButton("Configuration");
showConnectionInfoButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
activateConnectionDialog();
}
});
fetchButton = new JButton("Fetch");
fetchButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
fetch();
}
});
// Create the query text area and label.
queryTextArea = new JTextArea("SELECT * FROM APP.CUSTOMER", 25, 25);
queryAggregate = new JScrollPane(queryTextArea);
queryAggregate.setBorder(new BevelBorder(BevelBorder.LOWERED));
// Create the table.
tableAggregate = createTable();
tableAggregate.setBorder(new BevelBorder(BevelBorder.LOWERED));
// Add all the components to the main panel.
mainPanel.add(fetchButton);
mainPanel.add(showConnectionInfoButton);
mainPanel.add(queryAggregate);
mainPanel.add(tableAggregate);
mainPanel.setLayout(this);
// Create a Frame and put the main panel in it.
frame = new JFrame("TableExample");
frame.addWindowListener(new WindowAdapter() {
@Override
public void windowClosing(WindowEvent e) {
System.exit(0);
}
});
frame.setBackground(Color.lightGray);
frame.getContentPane().add(mainPanel);
frame.pack();
frame.setVisible(false);
frame.setBounds(200, 200, 640, 480);
activateConnectionDialog();
}
public void connect() {
dataBase = new JDBCAdapter(
serverField.getText(),
driverField.getText(),
userNameField.getText(),
passwordField.getText());
sorter.setModel(dataBase);
}
public void fetch() {
dataBase.executeQuery(queryTextArea.getText());
}
public JScrollPane createTable() {
sorter = new TableSorter();
//connect();
//fetch();
// Create the table
JTable table = new JTable(sorter);
// Use a scrollbar, in case there are many columns.
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
// Install a mouse listener in the TableHeader as the sorter UI.
sorter.addMouseListenerToHeaderInTable(table);
JScrollPane scrollpane = new JScrollPane(table);
return scrollpane;
}
public static void main(String s[]) {
// Trying to set Nimbus look and feel
try {
for (LookAndFeelInfo info : UIManager.getInstalledLookAndFeels()) {
if ("Nimbus".equals(info.getName())) {
UIManager.setLookAndFeel(info.getClassName());
break;
}
}
} catch (ClassNotFoundException | IllegalAccessException | InstantiationException | UnsupportedLookAndFeelException ex) {
Logger.getLogger(TableExample.class.getName()).log(Level.SEVERE,
"Failed to apply Nimbus look and feel", ex);
}
new TableExample();
}
@Override
public Dimension preferredLayoutSize(Container c) {
return origin;
}
@Override
public Dimension minimumLayoutSize(Container c) {
return origin;
}
@Override
public void addLayoutComponent(String s, Component c) {
}
@Override
public void removeLayoutComponent(Component c) {
}
@Override
public void layoutContainer(Container c) {
Rectangle b = c.getBounds();
int topHeight = 90;
int inset = 4;
showConnectionInfoButton.setBounds(b.width - 2 * inset - 120, inset, 120,
25);
fetchButton.setBounds(b.width - 2 * inset - 120, 60, 120, 25);
// queryLabel.setBounds(10, 10, 100, 25);
queryAggregate.setBounds(inset, inset, b.width - 2 * inset - 150, 80);
tableAggregate.setBounds(new Rectangle(inset,
inset + topHeight,
b.width - 2 * inset,
b.height - 2 * inset - topHeight));
}
}
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
/**
*
* @author Sowndar
*/
/**
* In a chain of data manipulators some behaviour is common. TableMap provides
* most of this behavour and can be subclassed by filters that only need to
* override a handful of specific methods. TableMap implements TableModel by
* routing all requests to its model, and TableModelListener by routing all
* events to its listeners. Inserting a TableMap which has not been subclassed
* into a chain of table filters should have no effect.
*
* @author Philip Milne
*/
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.*;
[MENTION=139512]Sup[/MENTION]pressWarnings("serial")
public class TableMap extends AbstractTableModel implements TableModelListener {
protected TableModel model;
public TableModel getModel() {
return model;
}
public void setModel(TableModel model) {
this.model = model;
model.addTableModelListener(this);
}
// By default, Implement TableModel by forwarding all messages
// to the model.
@Override
public Object getValueAt(int aRow, int aColumn) {
return model.getValueAt(aRow, aColumn);
}
@Override
public void setValueAt(Object aValue, int aRow, int aColumn) {
model.setValueAt(aValue, aRow, aColumn);
}
@Override
public int getRowCount() {
return (model == null) ? 0 : model.getRowCount();
}
@Override
public int getColumnCount() {
return (model == null) ? 0 : model.getColumnCount();
}
@Override
public String getColumnName(int aColumn) {
return model.getColumnName(aColumn);
}
@Override
public Class<?> getColumnClass(int aColumn) {
return model.getColumnClass(aColumn);
}
@Override
public boolean isCellEditable(int row, int column) {
return model.isCellEditable(row, column);
}
// Implementation of the TableModelListener interface,
// By default forward all events to all the listeners.
@Override
public void tableChanged(TableModelEvent e) {
fireTableChanged(e);
}
}
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
/**
*
* @author Sowndar
*/
import java.awt.event.InputEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.swing.JTable;
import javax.swing.event.TableModelEvent;
import javax.swing.table.JTableHeader;
import javax.swing.table.TableColumnModel;
import javax.swing.table.TableModel;
/**
* A sorter for TableModels. The sorter has a model (conforming to TableModel)
* and itself implements TableModel. TableSorter does not store or copy the data
* in the TableModel, instead it maintains an array of integers which it keeps
* the same size as the number of rows in its model. When the model changes it
* notifies the sorter that something has changed eg. "rowsAdded" so that its
* internal array of integers can be reallocated. As requests are made of the
* sorter (like getValueAt(row, col) it redirects them to its model via the
* mapping array. That way the TableSorter appears to hold another copy of the
* table with the rows in a different order. The sorting algorthm used is stable
* which means that it does not move around rows when its comparison function
* returns 0 to denote that they are equivalent.
*
* @author Philip Milne
*/
[MENTION=139512]Sup[/MENTION]pressWarnings("serial")
public final class TableSorter extends TableMap {
int indexes[];
List<Integer> sortingColumns = new ArrayList<>();
boolean ascending = true;
int compares;
public TableSorter() {
indexes = new int[0]; // For consistency.
}
public TableSorter(TableModel model) {
setModel(model);
}
@Override
public void setModel(TableModel model) {
super.setModel(model);
reallocateIndexes();
}
public int compareRowsByColumn(int row1, int row2, int column) {
Class<?> type = model.getColumnClass(column);
TableModel data = model;
// Check for nulls
Object o1 = data.getValueAt(row1, column);
Object o2 = data.getValueAt(row2, column);
// If both values are null return 0
if (o1 == null && o2 == null) {
return 0;
} else if (o1 == null) { // Define null less than everything.
return -1;
} else if (o2 == null) {
return 1;
}
/* We copy all returned values from the getValue call in case
an optimised model is reusing one object to return many values.
The Number subclasses in the JDK are immutable and so will not be used
in this way but other subclasses of Number might want to do this to save
space and avoid unnecessary heap allocation.
*/
if (type.getSuperclass() == java.lang.Number.class) {
Number n1 = (Number) data.getValueAt(row1, column);
double d1 = n1.doubleValue();
Number n2 = (Number) data.getValueAt(row2, column);
double d2 = n2.doubleValue();
if (d1 < d2) {
return -1;
} else if (d1 > d2) {
return 1;
} else {
return 0;
}
} else if (type == java.util.Date.class) {
Date d1 = (Date) data.getValueAt(row1, column);
long n1 = d1.getTime();
Date d2 = (Date) data.getValueAt(row2, column);
long n2 = d2.getTime();
if (n1 < n2) {
return -1;
} else if (n1 > n2) {
return 1;
} else {
return 0;
}
} else if (type == String.class) {
String s1 = (String) data.getValueAt(row1, column);
String s2 = (String) data.getValueAt(row2, column);
int result = s1.compareTo(s2);
if (result < 0) {
return -1;
} else if (result > 0) {
return 1;
} else {
return 0;
}
} else if (type == Boolean.class) {
Boolean bool1 = (Boolean) data.getValueAt(row1, column);
boolean b1 = bool1.booleanValue();
Boolean bool2 = (Boolean) data.getValueAt(row2, column);
boolean b2 = bool2.booleanValue();
if (b1 == b2) {
return 0;
} else if (b1) // Define false < true
{
return 1;
} else {
return -1;
}
} else {
Object v1 = data.getValueAt(row1, column);
String s1 = v1.toString();
Object v2 = data.getValueAt(row2, column);
String s2 = v2.toString();
int result = s1.compareTo(s2);
if (result < 0) {
return -1;
} else if (result > 0) {
return 1;
} else {
return 0;
}
}
}
public int compare(int row1, int row2) {
compares++;
for (int level = 0; level < sortingColumns.size(); level++) {
Integer column = sortingColumns.get(level);
int result = compareRowsByColumn(row1, row2, column.intValue());
if (result != 0) {
return ascending ? result : -result;
}
}
return 0;
}
public void reallocateIndexes() {
int rowCount = model.getRowCount();
// Set up a new array of indexes with the right number of elements
// for the new data model.
indexes = new int[rowCount];
// Initialise with the identity mapping.
for (int row = 0; row < rowCount; row++) {
indexes[row] = row;
}
}
@Override
public void tableChanged(TableModelEvent e) {
System.out.println("Sorter: tableChanged");
reallocateIndexes();
super.tableChanged(e);
}
public void checkModel() {
if (indexes.length != model.getRowCount()) {
System.err.println("Sorter not informed of a change in model.");
}
}
public void sort(Object sender) {
checkModel();
compares = 0;
// n2sort();
// qsort(0, indexes.length-1);
shuttlesort(indexes.clone(), indexes, 0, indexes.length);
System.out.println("Compares: " + compares);
}
public void n2sort() {
for (int i = 0; i < getRowCount(); i++) {
for (int j = i + 1; j < getRowCount(); j++) {
if (compare(indexes[i], indexes[j]) == -1) {
swap(i, j);
}
}
}
}
// This is a home-grown implementation which we have not had time
// to research - it may perform poorly in some circumstances. It
// requires twice the space of an in-place algorithm and makes
// NlogN assigments shuttling the values between the two
// arrays. The number of compares appears to vary between N-1 and
// NlogN depending on the initial order but the main reason for
// using it here is that, unlike qsort, it is stable.
public void shuttlesort(int from[], int to[], int low, int high) {
if (high - low < 2) {
return;
}
int middle = (low + high) / 2;
shuttlesort(to, from, low, middle);
shuttlesort(to, from, middle, high);
int p = low;
int q = middle;
/* This is an optional short-cut; at each recursive call,
check to see if the elements in this subset are already
ordered. If so, no further comparisons are needed; the
sub-array can just be copied. The array must be copied rather
than assigned otherwise sister calls in the recursion might
get out of sinc. When the number of elements is three they
are partitioned so that the first set, [low, mid), has one
element and and the second, [mid, high), has two. We skip the
optimisation when the number of elements is three or less as
the first compare in the normal merge will produce the same
sequence of steps. This optimisation seems to be worthwhile
for partially ordered lists but some analysis is needed to
find out how the performance drops to Nlog(N) as the initial
order diminishes - it may drop very quickly. */
if (high - low >= 4 && compare(from[middle - 1], from[middle]) <= 0) {
System.arraycopy(from, low, to, low, high - low);
return;
}
// A normal merge.
for (int i = low; i < high; i++) {
if (q >= high || (p < middle && compare(from[p], from[q]) <= 0)) {
to[i] = from[p++];
} else {
to[i] = from[q++];
}
}
}
public void swap(int i, int j) {
int tmp = indexes[i];
indexes[i] = indexes[j];
indexes[j] = tmp;
}
// The mapping only affects the contents of the data rows.
// Pass all requests to these rows through the mapping array: "indexes".
@Override
public Object getValueAt(int aRow, int aColumn) {
checkModel();
return model.getValueAt(indexes[aRow], aColumn);
}
@Override
public void setValueAt(Object aValue, int aRow, int aColumn) {
checkModel();
model.setValueAt(aValue, indexes[aRow], aColumn);
}
public void sortByColumn(int column) {
sortByColumn(column, true);
}
public void sortByColumn(int column, boolean ascending) {
this.ascending = ascending;
sortingColumns.clear();
sortingColumns.add(column);
sort(this);
super.tableChanged(new TableModelEvent(this));
}
// There is no-where else to put this.
// Add a mouse listener to the Table to trigger a table sort
// when a column heading is clicked in the JTable.
public void addMouseListenerToHeaderInTable(JTable table) {
final TableSorter sorter = this;
final JTable tableView = table;
tableView.setColumnSelectionAllowed(false);
MouseAdapter listMouseListener = new MouseAdapter() {
@Override
public void mouseClicked(MouseEvent e) {
TableColumnModel columnModel = tableView.getColumnModel();
int viewColumn = columnModel.getColumnIndexAtX(e.getX());
int column = tableView.convertColumnIndexToModel(viewColumn);
if (e.getClickCount() == 1 && column != -1) {
System.out.println("Sorting ...");
int shiftPressed = e.getModifiers() & InputEvent.SHIFT_MASK;
boolean ascending = (shiftPressed == 0);
sorter.sortByColumn(column, ascending);
}
}
};
JTableHeader th = tableView.getTableHeader();
th.addMouseListener(listMouseListener);
}
}