Differences

This shows you the differences between two versions of the page.

--- swdev:java:connecting_to_mysql_database [2018/03/09 16:23]
smayr
+++ swdev:java:connecting_to_mysql_database [2018/03/12 17:53] (current)
smayr [Connecting to MySQL Database]
@@ Line 1: / Line 1: @@
-= Connecting to MySQL Databvase ==
+= Connecting to MySQL Database ==
+. [[http://dev.mysql.com/downloads/connector/j/|Download JDBC driver]]
+. Add driver as Library to project.
+. Add query to connect to database:
 <code java>
+import java.sql.*;
 private void btnTestDbConnectionActionPerformed(java.awt.event.ActionEvent evt) {
     // TODO add your handling code here:
-    String database = "audina_crm_export_test";
+    String database = "acme_crm";
     String table    = "customer";
-    String url      = "jdbc:mysql://localhost:3306/audina_crm_export_test";
+    String url      = "jdbc:mysql://localhost:3306/acme_crm";
     String username = "root";
     String password = "";
@@ Line 43: / Line 50: @@
 }
 </code>
+== Example ==
+File ''com.acme.appname.DbUtils.java'':
+<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   = "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;
+    }
+}
+</code>
+Call the query from ''main'' to populate a ''JTable'' component called ''tblCustomers'':
+<code java>
+//...
+private void btnTestDbConnectionActionPerformed(java.awt.event.ActionEvent evt) {
+    String sql = "SELECT id, account_number, company_name FROM `" + table + "`";
+    tblCustomers.setModel(DbUtils.GetDataModelFromQuery(sql));
+}
+</code>
+== References ==
+  * [[https://www.youtube.com/watch?v=G4JeKZ6nDUI|YouTube: Connecting to MySQL Database]]
+  * [[https://stackoverflow.com/questions/2839321/connect-java-to-a-mysql-database|StackOverflow: Connect Java to MySQL Database]]