Differences

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

Link to this comparison view

Next revision 		Previous revision
swdev:java:connecting_to_mysql_database [2018/03/09 16:22]
smayr created 		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 == 
 +1. [[http://dev.mysql.com/downloads/connector/j/|Download JDBC driver]] 
 + 
 +2. Add driver as Library to project. 
 + 
 +3. Add query to connect to database:
 <code java> <code java>
 +import java.sql.*;
 +
 private void btnTestDbConnectionActionPerformed(java.awt.event.ActionEvent evt) {                                                     private void btnTestDbConnectionActionPerformed(java.awt.event.ActionEvent evt) {                                                    
-        // TODO add your handling code here: +    // TODO add your handling code here: 
-         +     
-        String database = "audina_crm_export_test"; +    String database = "acme_crm"; 
-        String table    = "customer"; +    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 username = "root"; 
-        String password = "";+    String password = "";
  
-        System.out.println("Connecting to database " + database + "...");+    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); 
 +    } 
 +     
 +} 
 +</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         // Connect to DB
-        try (Connection connection = (Connection) DriverManager.getConnection(url, username, password)) {+        try (com.mysql.jdbc.Connection connection = (com.mysql.jdbc.Connection) DriverManager.getConnection( 
 +            dbUrl, dbUsername, dbPassword 
 +        )) {
             System.out.println("Database connected!");             System.out.println("Database connected!");
  
-            String sql = "SELECT * FROM `" + table + "`"; 
             try {             try {
                 //PreparedStatement stmt = connection.prepareStatement(sql);                 //PreparedStatement stmt = connection.prepareStatement(sql);
                 Statement stmt = connection.createStatement();                 Statement stmt = connection.createStatement();
                 //stmt.execute(sql);                 //stmt.execute(sql);
-                ResultSet rs = stmt.executeQuery(sql);+                rs = stmt.executeQuery(sql); // get data
                                  
-                // Spit out data +                dataModel = DbUtils.GetDataModel(rs);
-                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();                 stmt.close();
-            } catch (SQLException e) { +            } catch (SQLException exc) { 
-                e.printStackTrace();+                exc.printStackTrace();
             } finally {             } finally {
                 connection.close();                 connection.close();
             }             }
-        } catch (SQLException e) { +        } catch (SQLException exc) { 
-            throw new IllegalStateException("Cannot connect to database " + database, e);+            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> </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]]
 +
swdev/java/connecting_to_mysql_database.1520630575.txt.gz · Last modified: 2018/03/09 16:22 by smayr
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International


  • Show page
  • Old revisions
  • Backlinks
  • Recent Changes
  • Media Manager
  • Sitemap