Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision | |||
swdev:java:connecting_to_mysql_database [2018/03/09 17:04] smayr [Connecting to MySQL Database] |
swdev:java:connecting_to_mysql_database [2018/03/12 17:53] (current) smayr [Connecting to MySQL Database] |
||
---|---|---|---|
Line 51: | Line 51: | ||
</ | </ | ||
+ | == Example == | ||
+ | File '' | ||
+ | <code 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 | ||
+ | static String table = " | ||
+ | static String dbUrl = " | ||
+ | static String dbUsername = " | ||
+ | static String dbPassword = ""; | ||
+ | | ||
+ | |||
+ | public static DefaultTableModel GetDataModelFromQuery(String sql) | ||
+ | { | ||
+ | ResultSet rs = null; | ||
+ | DefaultTableModel dataModel = null; | ||
+ | | ||
+ | System.out.println(" | ||
+ | |||
+ | // Connect to DB | ||
+ | try (com.mysql.jdbc.Connection connection = (com.mysql.jdbc.Connection) DriverManager.getConnection( | ||
+ | dbUrl, dbUsername, dbPassword | ||
+ | )) { | ||
+ | System.out.println(" | ||
+ | |||
+ | try { | ||
+ | // | ||
+ | Statement stmt = connection.createStatement(); | ||
+ | // | ||
+ | rs = stmt.executeQuery(sql); | ||
+ | | ||
+ | dataModel = DbUtils.GetDataModel(rs); | ||
+ | | ||
+ | stmt.close(); | ||
+ | } catch (SQLException exc) { | ||
+ | exc.printStackTrace(); | ||
+ | } finally { | ||
+ | connection.close(); | ||
+ | } | ||
+ | } catch (SQLException exc) { | ||
+ | throw new IllegalStateException(" | ||
+ | } | ||
+ | return dataModel; | ||
+ | } | ||
+ | | ||
+ | public static DefaultTableModel GetDataModel(ResultSet rs) | ||
+ | { | ||
+ | DefaultTableModel dataModel = null; | ||
+ | | ||
+ | try { | ||
+ | ResultSetMetaData rsMetaData = rs.getMetaData(); | ||
+ | |||
+ | // Get datamodel | ||
+ | Vector colNames = DbUtils.GetColNamesAsVector(rs); | ||
+ | dataModel = new DefaultTableModel(colNames, | ||
+ | |||
+ | // Print out data | ||
+ | while (rs.next()) { | ||
+ | // Get column data | ||
+ | int colCount = rsMetaData.getColumnCount(); | ||
+ | Vector colData = new Vector(); | ||
+ | for (int i=0; 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(" | ||
+ | | ||
+ | try { | ||
+ | Statement stmt = connection.createStatement(); | ||
+ | ResultSet rs = stmt.executeQuery(" | ||
+ | rs.next(); | ||
+ | count = rs.getInt(" | ||
+ | rs.close(); | ||
+ | System.out.println(tableName + " has " + count + " row(s)." | ||
+ | } catch (SQLException exc) { | ||
+ | exc.printStackTrace(); | ||
+ | } finally { | ||
+ | connection.close(); | ||
+ | } | ||
+ | } catch (SQLException exc) { | ||
+ | throw new IllegalStateException(" | ||
+ | } | ||
+ | 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< | ||
+ | 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< | ||
+ | colNames.add(rsMetaData.getColumnLabel(i+1)); | ||
+ | } | ||
+ | } catch (SQLException exc) { | ||
+ | exc.printStackTrace(); | ||
+ | } | ||
+ | | ||
+ | return colNames; | ||
+ | } | ||
+ | } | ||
+ | </ | ||
+ | |||
+ | Call the query from '' | ||
+ | <code java> | ||
+ | //... | ||
+ | private void btnTestDbConnectionActionPerformed(java.awt.event.ActionEvent evt) { | ||
+ | String sql = " | ||
+ | tblCustomers.setModel(DbUtils.GetDataModelFromQuery(sql)); | ||
+ | } | ||
+ | </ | ||
== References == | == References == | ||
* [[https:// | * [[https:// | ||
* [[https:// | * [[https:// | ||