package jdbc;
/*******************************************************************************************
* QueryDemo.java
*******************************************************************************************/
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.*;
import javax.swing.table.*;
public class QueryDemo extends JFrame {
public QueryDemo() {
initComponents();
try{
DriverManager.registerDriver( new sun.jdbc.odbc.JdbcOdbcDriver() );
connectDB();
textarea.setText("Verbindung erstellt zur Datenbank '" +Names.DATABASE_NAME+ "'\n");
setTitle(getTitle()+": "+Names.DATABASE_NAME);
}catch(SQLException ex){
textarea.setText("Verbindung fehlerhaft\n");
JOptionPane.showMessageDialog( this, ex.getMessage(),
"MySQL Demo: SQLException", JOptionPane.ERROR_MESSAGE);
}
}
private void initComponents() {
toolbar = new JToolBar();
queryButton = new JButton();
createTableButton = new JButton();
dropTableButton = new JButton();
panel1 = new JPanel();
progressbar = new JProgressBar();
scrollpane = new JScrollPane();
textarea = new JTextArea();
setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
setTitle("MySQL Demo");
toolbar.setFloatable(false);
queryButton.setText("Query");
queryButton.setPreferredSize(new Dimension(100, 22));
queryButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent evt) {
queryButtonActionPerformed(evt);
}
});
toolbar.add(queryButton);
createTableButton.setText("Create Table");
createTableButton.setPreferredSize(new Dimension(100, 22));
createTableButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent evt) {
createTableButtonActionPerformed(evt);
}
});
toolbar.add(createTableButton);
dropTableButton.setText("Drop Table");
dropTableButton.setPreferredSize(new Dimension(100, 22));
dropTableButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent evt) {
dropTableButtonActionPerformed(evt);
}
});
toolbar.add(dropTableButton);
getContentPane().add(toolbar, BorderLayout.NORTH);
panel1.setLayout(new BorderLayout());
progressbar.setMaximum(MAXROWS);
progressbar.setStringPainted(true);
progressbar.setVisible(false);
panel1.add(progressbar, BorderLayout.NORTH);
textarea.setBackground(new Color(255, 255, 204));
textarea.setEditable(false);
scrollpane.setViewportView(textarea);
panel1.add(scrollpane, BorderLayout.CENTER);
getContentPane().add(panel1, BorderLayout.CENTER);
Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
setBounds((screenSize.width-600)/2, (screenSize.height-400)/2, 600, 400);
queryButton.getRootPane().setDefaultButton(queryButton);
setVisible(true);
}
private void dropTableButtonActionPerformed(ActionEvent evt) {
scrollpane.setViewportView(textarea);
try{
dropTable();
}catch(SQLException ex){
JOptionPane.showMessageDialog( this, ex.getMessage(),
"MySQL Demo: SQLException", JOptionPane.ERROR_MESSAGE);
}
}
private void dropTable() throws SQLException{
if( connection!=null ){
int result = JOptionPane.showConfirmDialog(this,"Daten gehen verloren !" +
"\nWillst du wirklich die Daten von '" + Names.TABLE_NAME + "' löschen ?",
"MySQL Demo: Drop Table Confirmation", JOptionPane.YES_NO_OPTION);
if( result == JOptionPane.YES_OPTION ){
try{
statement = connection.createStatement();
statement.executeUpdate("drop table " + Names.TABLE_NAME);
textarea.setText("'" + Names.TABLE_NAME + "' gelöscht!");
}finally{
if(statement!=null){
statement.close();
}
}
}
}
}
private void createTableButtonActionPerformed(ActionEvent evt) {
worker = new SwingWorker() {
public Object construct() {
textarea.setText("Daten werden erstellt in '" +Names.TABLE_NAME+ "' ( " +MAXROWS + " Zeilen ) ...");
setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
progressbar.setVisible(true);
try{
createTable();
}catch(SQLException ex){
JOptionPane.showMessageDialog( null, ex.getMessage(),
"MySQL Demo: SQLException", JOptionPane.ERROR_MESSAGE);
}
textarea.setText("");
progressbar.setVisible(false);
setCursor(Cursor.getDefaultCursor());
return null;
}
};
worker.start();
}
private void queryButtonActionPerformed(ActionEvent evt) {
setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
resultset = new JTable(0,3);
QueryWorker.runQuery(new Thread( new QueryWorker( "SELECT host, db, Table_id FROM " + Names.TABLE_NAME, resultset ) ));
scrollpane.setViewportView(resultset);
setCursor(Cursor.getDefaultCursor());
}
private void createTable() throws SQLException{
progressbar.setValue(0);
progressbar.setString("0 rows");
if( connection!=null ){
try{
statement = connection.createStatement();
statement.executeUpdate(sqlCreateTable);
countProgress=0;
for( countRows=1; countRows <= MAXROWS; countRows++ ){
countProgress++;
if(countProgress==100){
progressbar.setValue(countRows);
progressbar.setString(countRows+" rows");
countProgress=0;
}
String countRowsString = ""+countRows;
String countText = ZEROS.substring(0, ZEROS.length()-countRowsString.length())+countRowsString;
String sqlInsert = "INSERT INTO " + Names.TABLE_NAME +
" (Host,Db,User,Table_id,Grantor) " +
"values ( 'pc', 'text', 'uan', 0, 'random')";
statement.executeUpdate(sqlInsert);
}
}finally{
if(statement!=null)statement.close();
}
}
}
private void connectDB() throws SQLException{
String url = "jdbc:odbc:" + Names.DATABASE_NAME;
System.out.println(url);
connection = DriverManager.getConnection(url);
}
public static void main(String args[]) {
new QueryDemo();
}
/*
Du musst den "SwingWorker" code laden und kompilieren um ihn benutzen zu können.
Hier ist er:
[url]http://java.sun.com/products/jfc/tsc/articles/threads/src/SwingWorker.java[/url]
*/
private SwingWorker worker ;
private JButton queryButton;
private JButton createTableButton;
private JButton dropTableButton;
private JPanel panel1;
private JProgressBar progressbar;
private JScrollPane scrollpane;
private JToolBar toolbar;
private JTextArea textarea;
private boolean running;
private Connection connection;
private int countProgress;
private int countRows;
private Statement statement;
private JTable resultset ;
private final String ZEROS = "00000000";
private final Rectangle BOTTOM = new Rectangle(0,2000000000,1,1);
private final int MAXROWS = 50000;
private String sqlCreateTable = "CREATE TABLE " + Names.TABLE_NAME + " ("
+" Host char(60) binary DEFAULT '' NOT NULL,"
+" Db char(64) binary DEFAULT '' NOT NULL,"
+" User char(16) binary DEFAULT '' NOT NULL,"
+" Table_id int(9) auto_increment,"
+" Grantor char(77) DEFAULT '' NOT NULL,"
+" PRIMARY KEY (Host,Db,User,Table_id)"
+" ) ENGINE = MyISAM ";
}
class QueryWorker implements Runnable{
public QueryWorker(String query, JTable displayTable) {
this.query = query;
this.displayTable = displayTable;
displayTableModel = (DefaultTableModel)displayTable.getModel();
}
public void run(){
try{
DriverManager.registerDriver( new sun.jdbc.odbc.JdbcOdbcDriver() );
connectDB();
System.out.println("Verbindung OK");
}catch(SQLException ex){
System.out.println("Fehler: Verbindung nicht erstellt");
System.out.println( ex.getMessage() );
}
try{
doQuery();
}catch(SQLException ex){
String msg = ex.getMessage();
System.out.println( msg );
}
System.out.println("fertig!");
}
private void doQuery() throws SQLException{
try {
if(connection!=null){
statement = connection.createStatement();
resultset = statement.executeQuery(query);
dispResultSet(resultset);
}
}finally{
if(resultset!=null)resultset.close();
if(statement!=null)statement.close();
if(connection!=null)connection.close();
}
}
private void dispResultSet(final ResultSet resultset) throws SQLException{
int i;
ResultSetMetaData rsmd = resultset.getMetaData();
int numCols = rsmd.getColumnCount();
// Spalten-Überschriften anzeigen
for (i=1; i<=numCols ; i++) {
tablecolumn = displayTable.getColumnModel().getColumn(i-1);
tablecolumn.setHeaderValue( rsmd.getColumnLabel(i).toString() );
}
// Daten anzeigen, bis zum Ende vom "result set"
boolean more = resultset.next();
while ( more ) {
// Jede Spalte durchgehen und Spalten-Daten anzeigen
Object[] rowData = new Object[numCols];
for (i=1; i<=numCols; i++) {
rowData[i-1] = resultset.getString(i);
}
displayTableModel.addRow(rowData);
// Nächste Zeile vom "result set" holen4
more = resultset.next();
}
}
private void connectDB() throws SQLException{
connection = DriverManager.getConnection("jdbc:odbc:" + Names.DATABASE_NAME);
}
public static void runQuery(Thread queryWorker){
queryWorker.start();
//warten bis "query" fertig ist, aber höchstens 10 Sekunden
for( int i=0; queryWorker.isAlive() && i<100; i++ ){
try{
Thread.sleep(100);
}catch(InterruptedException ex){}
}
}
private String query;
private JTable displayTable;
private DefaultTableModel displayTableModel ;
private TableColumn tablecolumn ;
private Connection connection;
private Statement statement;
private ResultSet resultset;
}
final class Names{
public static final Names DATABASE_NAME = new Names("meinedb");
public static final Names TABLE_NAME = new Names("meinetabelle");
private final String name;
private Names(final String name) {
this.name = name;
}
public String toString(){
return this.name;
}
}
