/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package miscellaneous;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;

/**
 *
 * @author andrea */
public class ConnectionToDB {

    public static void main(String[] args) {
/*
        try {
                // MySQL Database
                Class.forName("com.mysql.jdbc.Driver"); //.newInstance();
                System.out.println("Driver for the MySQL DBMS installed!");
                String sourceURL = "jdbc:mysql://localhost:3306/andrea_db";
                //String sourceURL = "jdbc:mysql://localhost:3306/anne";
                // Create a connection through the DriverManager
                Connection databaseConnection = DriverManager.getConnection(sourceURL,"root","");
                System.out.println("Connection to the MySQL DBMS established with " + databaseConnection);
                Statement statement = databaseConnection.createStatement();
                ResultSet entryDB = statement.executeQuery("SELECT name, tel FROM contacts");
                //ResultSet entryDB = statement.executeQuery("SELECT * FROM adresse");
                // Output the resultset data
                while (entryDB.next()) {
                    System.out.println(entryDB.getString("name") + " " +
                                   entryDB.getString("tel"));
                                   //entryDB.getString("id") + "\n" +
                                   //entryDB.getString("street") + " " + entryDB.getString("number") + "\n" +
                                   //entryDB.getString("zip") + " " + entryDB.getString("city") + "\n\n--\n\n");
                }
                entryDB.close();
                statement.close();
                databaseConnection.close();
                System.out.println("Closing connection to the MySQL DBMS!");

        } catch (Exception ex) {
            System.out.println("JDBC Driver error");
            ex.printStackTrace();
        }
*/
        // Load the driver for a MS Access Database
        try {
            // to include the driver to use, one can load the driver explicitly
            // by calling the static forName() method in the Class class and
            // passing a String object as an argument containing the driver class name.
            // Load the driver class:
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            System.out.println("Driver for the MS Access DBMS installed!");
            // Define the data source for the driver
            // JDBC uses URLs to identify the locations of both drivers and
            // data sources. JDBC URLs have the following format:
            // jdbc:subprotocol:data_source_identifier
            // The scheme jdbc indicates that the URL refers to a JDBC data source.
            // The sub-protocol identifies which JDBC driver to use. For example,
            // the JDBC-ODBC Bridge uses the driver identifier odbc.
            String sourceURL = "jdbc:odbc:dbdemos";
            // Create a connection through the DriverManager
            Connection databaseConnection = DriverManager.getConnection(sourceURL);
            System.out.println("Connection to the MS Access DBMS established with " + databaseConnection);

            // A java.sql.Statement object is an object of a class that implements
            // the Statement interface. When a Statement object is created, it provides
            // a workspace to create an SQL query, execute it, and retrieve any results
            // that are returned
            Statement statement = databaseConnection.createStatement();
            // The results of executing an SQL query are returned in the form of
            // an object that implements the ResultSet interface and contains the
            // table produced by the SQL query. The ResultSet object contains
            // something called a cursor that you can manipulate to refer to any
            // particular row in the resultset. This initially points to a position
            // immediately preceding the first row
            ResultSet entryDB = statement.executeQuery("SELECT name, capital FROM country where continent = 'North America'");
            // Output the resultset data
            int idx = 0;
            while (entryDB.next()) {
                // Process the row
                System.out.println( (++idx) + " - " + entryDB.getString("name") + " " +
                                   entryDB.getString("capital"));
            }

            // The getMetaData() method for a ResultSet object returns a reference
            // to an object of type java.sql.ResultSetMetaData that encapsulates
            // the metadata for the resultset
            ResultSetMetaData metadata = entryDB.getMetaData();

            int columns = metadata.getColumnCount(); // Get number of columns
            System.out.println("Catalog name: " + metadata.getCatalogName(1) +
                               "     Column 1 name: " + metadata.getColumnName(1) +
                               "     Table name: " + metadata.getTableName(1));
            System.out.println("Columns in table: " + columns);
            for(int i = 1 ; i<= columns ; i++) { // For each column
                if (metadata.getColumnType(i) == Types.VARCHAR) { // if it is CHAR
                    System.out.println(metadata.getColumnName(i)); // display the name
                }
            }

            entryDB.close();
            statement.close();
            databaseConnection.close();
            System.out.println("Closing connection to the MS Access DBMS!");

        } catch(ClassNotFoundException cnfe) {
            System.err.println(cnfe);
        } catch(SQLException sqle) {
            System.err.println(sqle);
        }

    }

}