2. Add driver as Library to project.
3. Add query to connect to database:
import java.sql.*; private void btnTestDbConnectionActionPerformed(java.awt.event.ActionEvent evt) { // TODO add your handling code here: String database = "acme_crm"; String table = "customer"; String url = "jdbc:mysql://localhost:3306/acme_crm"; String username = "root"; String password = ""; System.out.println("Connecting to database " + database + "..."); // Connect to DB try (Connection connection = (Connection) DriverManager.getConnection(url, username, password)) { System.out.println("Database connected!"); String sql = "SELECT * FROM `" + table + "`"; try { //PreparedStatement stmt = connection.prepareStatement(sql); Statement stmt = connection.createStatement(); //stmt.execute(sql); ResultSet rs = stmt.executeQuery(sql); // Spit out data while (rs.next()) { int id = rs.getInt("id"); String account_number = rs.getString("account_number"); String company_name = rs.getString("company_name"); System.out.println("[" + id + "] " + account_number + ": " + company_name); } stmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { connection.close(); } } catch (SQLException e) { throw new IllegalStateException("Cannot connect to database " + database, e); } }
File com.acme.appname.DbUtils.java
:
/* * 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. */ package com.acme.myapp; import com.mysql.jdbc.Connection; import java.nio.file.Paths; import java.sql.*; import java.time.LocalDateTime; import java.util.*; import javax.swing.table.DefaultTableModel; import com.acme.*; public class DbUtils { static String dbName = "acme_crm"; static String table = "customer"; static String dbUrl = "jdbc:mysql://localhost:3306/acme_crm"; static String dbUsername = "root"; static String dbPassword = ""; public static DefaultTableModel GetDataModelFromQuery(String sql) { ResultSet rs = null; DefaultTableModel dataModel = null; System.out.println("Connecting to database " + dbName + "..."); // Connect to DB try (com.mysql.jdbc.Connection connection = (com.mysql.jdbc.Connection) DriverManager.getConnection( dbUrl, dbUsername, dbPassword )) { System.out.println("Database connected!"); try { //PreparedStatement stmt = connection.prepareStatement(sql); Statement stmt = connection.createStatement(); //stmt.execute(sql); rs = stmt.executeQuery(sql); // get data dataModel = DbUtils.GetDataModel(rs); stmt.close(); } catch (SQLException exc) { exc.printStackTrace(); } finally { connection.close(); } } catch (SQLException exc) { throw new IllegalStateException("Cannot connect to database " + dbName, exc); } return dataModel; } public static DefaultTableModel GetDataModel(ResultSet rs) { DefaultTableModel dataModel = null; try { ResultSetMetaData rsMetaData = rs.getMetaData(); // get metadata // Get datamodel Vector colNames = DbUtils.GetColNamesAsVector(rs); dataModel = new DefaultTableModel(colNames, 0); // Print out data while (rs.next()) { // Get column data int colCount = rsMetaData.getColumnCount(); Vector colData = new Vector(); for (int i=0; i<colCount; i++) { colData.add(rs.getObject(i+1)); } dataModel.addRow(colData); } } catch (SQLException exc) { exc.printStackTrace(); } return dataModel; } public static int GetRowCount(String tableName) { int count = 0; try (com.mysql.jdbc.Connection connection = (com.mysql.jdbc.Connection) DriverManager.getConnection( dbUrl, dbUsername, dbPassword )) { System.out.println("Database connected!"); try { Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS rowcount FROM " + tableName); rs.next(); count = rs.getInt("rowcount"); rs.close(); System.out.println(tableName + " has " + count + " row(s)."); } catch (SQLException exc) { exc.printStackTrace(); } finally { connection.close(); } } catch (SQLException exc) { throw new IllegalStateException("Cannot connect to database " + dbName, exc); } return count; } public static int GetColCount(ResultSet rs) { int colCount = 0; try { ResultSetMetaData rsMetaData = rs.getMetaData(); colCount = rsMetaData.getColumnCount(); } catch (SQLException exc) { exc.printStackTrace(); } return colCount; } public static Object[] GetColNamesAsObject(ResultSet rs) { Object[] objColNames = null; try { ResultSetMetaData rsMetaData = rs.getMetaData(); // Get column names int colCount = rsMetaData.getColumnCount(); objColNames = new Object[colCount]; for (int i=0; i<colCount; i++) { objColNames[i] = rsMetaData.getColumnLabel((i+1)); } } catch (SQLException exc) { exc.printStackTrace(); } return objColNames; } public static Vector GetColNamesAsVector(ResultSet rs) { Vector colNames = new Vector(); try { ResultSetMetaData rsMetaData = rs.getMetaData(); // Get column names int colCount = rsMetaData.getColumnCount(); for (int i=0; i<colCount; i++) { colNames.add(rsMetaData.getColumnLabel(i+1)); } } catch (SQLException exc) { exc.printStackTrace(); } return colNames; } }
Call the query from main
to populate a JTable
component called tblCustomers
:
//... private void btnTestDbConnectionActionPerformed(java.awt.event.ActionEvent evt) { String sql = "SELECT id, account_number, company_name FROM `" + table + "`"; tblCustomers.setModel(DbUtils.GetDataModelFromQuery(sql)); }