Java & JDBC & SQLite: Read Data from User-selected db Table and Show in JTable

• Download source - 3.1 MB

Introduction

You can find enough information on the internet about the sterling interaction with databases in .NET (see my article about MS Access, MySQL, SQLite, SQL Server, SQL Server CE), which has convenient built-in tools for this purpose (such as DataSet, etc.). But with Java, this is much worse.

I decided to fill this gap.

Prepare Your Project

Download this jar, add it to your project directory and add a reference to it to your project.

(Tested with its 3.7.2 version)

Import the Necessary Packages

import java.sql.*;
import javax.swing.JOptionPane;
import javax.swing.table.*;

Connect Database

// connect to db (file test.db must lay in the project dir)
// NOTE: it will be created if not exists
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:test.db");

Create Table If It Does Not Exist

?(or run any other SQL query)
// create table "Table 1" if not exists
Statement stmt = conn.createStatement();
String sql = "CREATE TABLE IF NOT EXISTS [Table 1] 
(id INTEGER PRIMARY KEY AUTOINCREMENT, 'text column' TEXT, 'int column' INTEGER);";
stmt.executeUpdate(sql);
stmt.close();

Get All Tables in db into combobox for User Select

// get all tables in db and them names to combobox
ResultSet rs = null;
DatabaseMetaData meta = conn.getMetaData();
rs = meta.getTables(null, null, null, new String[]{"TABLE"});

while (rs.next()) {
    String tableName = rs.getString("TABLE_NAME");
    jComboBox1.addItem(tableName);
}
jComboBox1.updateUI();

Load Selected Table to JTable

private void Select() {
try {
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM [" + jComboBox1.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();

    rs.close();
    stmt.close();
} catch (Exception ex) { 
    JOptionPane.showMessageDialog(this, ex, ex.getMessage(), WIDTH, null);
}
}