JTable MySQL

Thomas Darimont · 10. März 2004

Hallo!

Beispiel JTable Mysql:

Code:

import java.awt.Color;
import java.awt.Container;
import java.awt.Dimension;
import java.awt.FlowLayout;
import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

/**
 * @author      Darimont<br>
 * @version     1.0<br>
 * Date of creation:  10.03.2004<br>
 * File:         JTableExample.java<br>
 * Modifier:     Darimont<br>
 * Revision:     Revision<br>
 * State:        Germany<br>
 */

public class JTableExample extends JFrame {

	private static final String dbDriverClass = "org.gjt.mm.mysql.Driver";

	static {
		try {
			try {
				Thread
					.currentThread()
					.getContextClassLoader()
					.loadClass(dbDriverClass)
					.newInstance();
			} catch (InstantiationException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} catch (IllegalAccessException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private JButton btnConnect, btnRefresh;
	private JTextField txtSQL;
	private JTable table;
	private JScrollPane scrollPane;
	private DefaultTableModel dtm;
	private javax.swing.JPanel btnPanel, contentPanel;

	private final String dbName = "mysql";
	private final int dbPort = 3306;
	private final String dbURL =
		"jdbc:mysql://localhost:" + dbPort + "/" + dbName;
	private final String dbUser = "Administrator";
	private final String dbpasswd = "";

	private MysqlDataSource ds;
	private Connection con;
	private ResultSet rs;

	public JTableExample() {
		super("JTableExample");

		GridBagConstraints gridBagConstraints;

		contentPanel = new JPanel();
		btnPanel = new JPanel();
		btnConnect = new JButton();
		btnRefresh = new JButton();
		txtSQL = new JTextField();
		scrollPane = new JScrollPane();
		table = new JTable();
		dtm = new DefaultTableModel();

		getContentPane().setLayout(new FlowLayout());

		setTitle("JTableExample");
		addWindowListener(new WindowAdapter() {
			public void windowClosing(WindowEvent evt) {
				try {
					if (con != null && !con.isClosed()) {
						con.close();
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				setVisible(false);
				dispose();
				System.exit(0);
			}
		});

		contentPanel.setLayout(new GridBagLayout());

		contentPanel.setBackground(new Color(102, 153, 255));
		contentPanel.setMinimumSize(new Dimension(400, 300));
		contentPanel.setPreferredSize(new Dimension(400, 300));
		btnPanel.setBackground(new Color(255, 153, 51));
		btnPanel.setMinimumSize(new Dimension(400, 100));
		btnPanel.setPreferredSize(new Dimension(400, 100));
		btnConnect.setText("Connect");
		btnPanel.add(btnConnect);

		btnRefresh.setText("Refresh");
		btnPanel.add(btnRefresh);

		txtSQL.setColumns(25);
		btnPanel.add(txtSQL);

		gridBagConstraints = new GridBagConstraints();
		gridBagConstraints.gridx = 0;
		gridBagConstraints.gridy = 1;
		contentPanel.add(btnPanel, gridBagConstraints);

		scrollPane.setMinimumSize(new Dimension(400, 200));
		scrollPane.setPreferredSize(new Dimension(400, 200));
		table.setModel(dtm);
		table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
		scrollPane.setViewportView(table);

		contentPanel.add(scrollPane, new GridBagConstraints());

		getContentPane().add(contentPanel);

		setSize(400, 300);

		Container container = getContentPane();
		//con.setLayout(FillLayout());

		btnConnect.addActionListener(new ActionListener() {

			public void actionPerformed(ActionEvent e) {
				// TODO Auto-generated method stub
				connect();
			}

		});
		btnRefresh.addActionListener(new ActionListener() {

			public void actionPerformed(ActionEvent e) {
				// TODO Auto-generated method stub
				refresh();
			}

		});

		setVisible(true);
	}

	/**
	 * 
	 */
	protected void refresh() {
		// TODO Auto-generated method stub
		try {
			if (con == null || con.isClosed()) {
				JOptionPane.showMessageDialog(
					this,
					"Keine Datenbaknverbindung!",
					"Fehler",
					JOptionPane.ERROR_MESSAGE);
				return;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		Statement stmt = null;
		try {
			stmt = con.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		if (stmt == null) {
			throw new RuntimeException("stmt is null");
		}

		String sql = txtSQL.getText();
		if (sql == null) {
			JOptionPane.showMessageDialog(
				this,
				"Keine SQL Abfrage angegeben!",
				"Fehler",
				JOptionPane.ERROR_MESSAGE);
			return;
		}
		try {
			rs = stmt.executeQuery(sql);
		} catch (SQLException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}

		putRSinTableModel(rs, dtm);

		table.setModel(dtm);

		table.updateUI();

	}

	/**
	 * @param rs
	 * @param dtm
	 */
	private void putRSinTableModel(ResultSet rs, DefaultTableModel dtm) {
		// TODO Auto-generated method stub
		ResultSetMetaData rsmd = null;
		int clmCnt = -1;
		try {
			rsmd = rs.getMetaData();
			clmCnt = rsmd.getColumnCount();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		if (rsmd == null || clmCnt == -1) {
			throw new RuntimeException("rsmd is null");
		}
		try {
			rs.first();
			rs.last();
			int rowCnt = rs.getRow();
			rs.first();

			Object[][] odata = new Object[rowCnt][clmCnt];
			Object[] clmHeaders = new Object[clmCnt];
			for (int i = 1; i <= clmCnt; i++) {
				clmHeaders[i - 1] = rsmd.getColumnName(i);
			}

			int row = 0;
			while (rs.next()) {

				for (int i = 1; i <= clmCnt; i++) {
					System.out.print(rs.getString(i) + " ");
					odata[row][i - 1] = rs.getString(i);
				}
				row++;
				System.out.println();
			}

			dtm.setDataVector(odata, clmHeaders);

		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

	}

	/**
	 * 
	 */
	protected void connect() {
		// TODO Auto-generated method stub
		if(con != null)
			return;
		ds = new MysqlDataSource();
		ds.setDatabaseName(dbName);
		ds.setPort(dbPort);
		ds.setUser(dbUser);
		ds.setPassword(dbpasswd);
		ds.setURL(dbURL);

		try {
			con = ds.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void main(String[] args) {
		new JTableExample();
	}
}

Gruß Tom

Thomas Darimont · 3. April 2004

Hallo!

Um den Bug zu beheben, dass die erste Zeile ausgelassen wird...

einfach

rs.first();
durch
rs.beforeFirst();

ersetzen....

Gruß Tom

gondor · 9. August 2004

hast mir damit sehr

geholfen

kann ich bei der in der dtm

table.setModel(dtm);

auch spalten deaktivieren, d.h. sie sollen nicht dargestellt werden.

gr33tz,

gondor · 10. August 2004

@Thomas Darimont

leider stoße ich mit deiner implementierung auf ein problem

hier

kannst du mir da evtl. helfen?

gondor(..)

Thomas Darimont · 11. August 2004

Neue Version der Beispielanwendung:

Die Main Klasse:

Code:

package de.tutorials.main;

import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.ComponentAdapter;
import java.awt.event.ComponentEvent;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;

import de.tutorials.db.DatabaseHelper;

public class Main extends JFrame {

	private DatabaseHelper hlp = new DatabaseHelper("localhost", "mysql", 3306,
			"root", "");

	private JButton btnConnect, btnRefresh;

	private JTextField txtSQL;

	private JScrollPane scrollPane;

	private JTable table;

	private TableModel model;

	private final Dimension perfectSize;

	public Main() {
		super("Main");
		addWindowListener(new WindowAdapter() {
			public void windowClosing(WindowEvent evt) {
				if (hlp != null) {
					hlp.disconnect();
					System.out.println("disconnected");
					hlp = null;
				}
				setVisible(false);
				dispose();
				System.exit(0);
			}
		});

		addComponentListener(new ComponentAdapter() {
			public void componentResized(ComponentEvent e) {
				if (!isVisible())
					return;
				Dimension d = getSize();
				if (d.width < perfectSize.width
						|| d.height < perfectSize.height) {
					setSize(perfectSize);
				}
			}
		});

		model = new DefaultTableModel(new Object[0][0], new Object[0]);
		table = new JTable(model);
		table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

		scrollPane = new JScrollPane(table);

		getContentPane().add(scrollPane, BorderLayout.NORTH);

		ActionListener handler = new ActionHanlder();
		btnConnect = new JButton("Connect");
		btnConnect.addActionListener(handler);

		btnRefresh = new JButton("Refresh");
		btnRefresh.addActionListener(handler);

		JPanel panel = new JPanel();
		panel.add(btnConnect, BorderLayout.WEST);
		panel.add(btnRefresh, BorderLayout.EAST);

		getContentPane().add(panel, BorderLayout.SOUTH);

		txtSQL = new JTextField(30);
		getContentPane().add(txtSQL, BorderLayout.CENTER);

		hlp.init();
		pack();
		setVisible(true);
		perfectSize = getSize();
	}

	public static void main(String[] args) {
		new Main();
	}

	class ActionHanlder implements ActionListener {

		public void actionPerformed(ActionEvent e) {
			Object src = e.getSource();

			if (btnConnect == src) {
				hlp.connect();
				System.out.println("connected");
			} else if (btnRefresh == src) {
				String sql = txtSQL.getText();
				if (sql != null && !sql.equals("")
						&& !(sql.toLowerCase().indexOf("select") < 0)) {
					System.out.println("About to execute SQL: " + sql);
					Object[][] data = (Object[][]) hlp.transformResult(hlp
							.getResultFor(sql));

					DefaultTableModel mod = (DefaultTableModel) model;
					mod.setDataVector(data, hlp.getColumnNames());
					mod = null;

					table.setModel(model);
					table.updateUI();
				}
			}
		}
	}
}

Die DatabaseHelper Klasse:

Code:

package de.tutorials.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

public class DatabaseHelper {

	static {
		try {
			Thread.currentThread().getContextClassLoader().loadClass(
					"com.mysql.jdbc.Driver").newInstance();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(
					"Fehler beim laden des Datenbank Treibers!\n Liegt der Treiber im Classpath?");
		}
	}

	private Connection con;
	private MysqlDataSource dataSource = new MysqlDataSource();
	private String userName;
	private String passWord;
	private String hostName;
	private String dbName;
	private int port;
	private Object[] columnNames;

	public DatabaseHelper(String hostName, String dbName, int port,
			String userName, String passWord) {
		this.hostName = hostName;
		this.dbName = dbName;
		this.port = port;
		this.userName = userName;
		this.passWord = passWord;

		dataSource.setServerName(hostName);
		dataSource.setDatabaseName(dbName);
		dataSource.setPort(port);
		dataSource.setUser(userName);
		dataSource.setPassword(passWord);

	}

	public void init() {
		connect();
	}

	public void connect() {
		if (con == null)
			try {
				con = dataSource.getConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}

	public void disconnect() {
		if (con != null)
			try {
				if (!con.isClosed()) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				con = null;
			}
	}

	public ResultSet getResultFor(String sql) {
		if (con == null)
			return null;
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);

			return rs;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Object[][] transformResult(ResultSet rs) {

		if (rs == null) {
			return new Object[0][0];
		}
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			int clmCnt = rsmd.getColumnCount();

			columnNames = new Object[clmCnt];

			for (int i = 0; i < columnNames.length; i++) {
				columnNames[i] = rsmd.getColumnName(i + 1);
			}

			List list = new ArrayList();

			while (rs.next()) {
				Object[] rowData = new Object[clmCnt];
				for (int i = 1; i <= clmCnt; i++) {
					rowData[i - 1] = rs.getString(i);
				}
				list.add(rowData);
			}
			rs.close();

			Object[][] data = new Object[list.size()][clmCnt];
			for (int i = 0; i < data.length; i++) {
				data[i] = (Object[]) list.get(i);
			}
			return data;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Object[] getColumnNames() {
		return columnNames;
	}
}

Gruß Tom

AAF · 26. März 2007

Danke, damit hast du mir sehr weitergeholfen und Zeit gespart.

Schöne Grüsse aus der Schweiz,

Adrian Ambros Frey

sim4000 · 14. Dezember 2007

Hallo.
Erst mal ein großes Dankeschön für dieses Beispiel.
An diesem Problem sitze ich schon seit gut einer Woche.

Aber:
Ich habe noch einen Fehler gefunden. Und zwar in der MySQLHelperKlasse an dieser Stelle:

Code:

			while (rs.next()) {
				Object[] rowData = new Object[clmCnt];
				for (int i = 1; i <= clmCnt; i++) {
					rowData[i - 1] = rs.getString(i);
				}
			}

muss statt einem while ein do-while genutzt werden. Ansonsten hat das Programm bei mir den ersten Datensatz ausgelassen.
Also wie folgt:

Code:

			do {
				Object[] rowData = new Object[clmCnt];
				for (int i = 1; i <= clmCnt; i++) {
					rowData[i - 1] = rs.getString(i);
				}
			} while (rs.next());

Bin mir aber nicht sicher, ob das an mir liegt, oder an dem Beispiel von Thomas Darimont.
Lg, sim4000.

JTable MySQL

Thomas Darimont

Erfahrenes Mitglied

Anhänge

Thomas Darimont

Erfahrenes Mitglied

gondor

Mitglied

gondor

Mitglied

Thomas Darimont

Erfahrenes Mitglied

Anhänge

AAF

Grünschnabel

sim4000

Mitglied

Neue Beiträge