Java and MySQL via JDBC: How to Connect DB, Get Data from Table and Display It in JTable

• Download source - 924.4 KB

Introduction

This "simple-sample" demonstrates:

• how to connect to host and get the list of DBs to JComboBox
• how to use selected DB and get the list of its tables to JComboBox
• how to connect a table and show data from table in JTable
• correctly & user-friendly notify user about different errors

Preparation

Download and install MySQL server.

Create a database and a table. Insert a few rows to this table (see above cmd screen).

Download MySQL Connector/J: http://dev.mysql.com/downloads/connector/j/ and add it in your project.

Code

In JFrame class

private Connection connect = null;
private Statement statement = null;

Connect to MySQL

// Init MySQL JDBC Driver
try {
    Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
    JOptionPane.showMessageDialog(this,
        "MySQL Connector/J (*.jar file) not added to your project and/or not exist.",
        "Error", JOptionPane.ERROR_MESSAGE);
    return;
}

// Connect to host
try {
    connect = DriverManager.getConnection("jdbc:mysql://" + jTextField3.getText() + "/",
        jTextField1.getText(), jTextField2.getText());
} catch (SQLException e) {
    JOptionPane.showMessageDialog(this,
        "Can't connect to host. Verify host, username and password. \nMore info: \n" + e.toString(),
        "Error", JOptionPane.ERROR_MESSAGE);
return;
}

// Clear combobox with DBs names
jComboBox1.removeAllItems();

// Get all DBs and put them to combobox
try {
    ResultSet rs = connect.getMetaData().getCatalogs();
    
    while (rs.next()) {
        jComboBox1.addItem(rs.getString("TABLE_CAT"));
    }
} catch (SQLException e) { }

Connect to DB

try {
    // Init statement
    statement = connect.createStatement();
    // Set current DB
    // !!! WARNING TO QUOTES - it's backticks (`), not " and not ' !!!
    // !!! BACKTICKS ARE REQUIRED IF TABLE NAME CONTAINS SPACES !!!
    statement.executeQuery("USE `" + jComboBox1.getSelectedItem().toString() + "`;");
    
    // Clear combobox with tables names
    jComboBox2.removeAllItems();
    
    // Get all DB's tables and put them to combobox...
    statement = connect.createStatement();
    // !!! and now QUOTES, NOT BACKTICKS !!!
    // !!! because "jComboBox1.getSelectedItem().toString()" is column value, 
    // not column/table/db name !!!
    /*ResultSet rs = statement.executeQuery("SELECT TABLE_NAME FROM information_schema.TABLES" +
    " WHERE TABLE_SCHEMA = '" + jComboBox1.getSelectedItem().toString() + "'");*/
    ResultSet rs = statement.executeQuery("SHOW TABLES;");
    
    // Add tables list to combobox
    while (rs.next()) {
        jComboBox2.addItem(rs.getString(1));
    }
    
    // Close statement
    statement.close();
} catch (SQLException e) {
    JOptionPane.showMessageDialog(this,
        "Can't connect to DB and/or get tables list. \nMore info: \n" + e.toString(),
        "Error", JOptionPane.ERROR_MESSAGE);
}

Get data from user selected table and show it in JTable

try {
    statement = connect.createStatement();
    ResultSet rs = statement.executeQuery("SELECT * FROM `" + jComboBox2.getSelectedItem() + "`;");
    
    // get columns info
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    
    // for changing column and row model
    DefaultTableModel tm = (DefaultTableModel) jTable1.getModel();
    
    // clear existing columns 
    tm.setColumnCount(0);

    // add specified columns to table
    for (int i = 1; i <= columnCount; i++ ) {
        tm.addColumn(rsmd.getColumnName(i));
    }   

    // clear existing rows
    tm.setRowCount(0);

    // add rows to table
    while (rs.next()) {
        String[] a = new String[columnCount];
        for(int i = 0; i < columnCount; i++) {
            a[i] = rs.getString(i+1);
        }
    tm.addRow(a);
    }
    tm.fireTableDataChanged();

    // Close ResultSet and Statement
    rs.close();
    statement.close();
} catch (Exception ex) { 
    JOptionPane.showMessageDialog(this, ex, ex.getMessage(), WIDTH, null);
}