Connection to MySQL Database from Eclipse using JDBC

Software Prerequisites

Java Development Kit (JDK)
Eclipse IDE for Enterprise Java Developers

MySQL Server Installation

Before the MySQL server, your system will not have MySQL server. Verify it with the below command.

/usr/local/mysql/bin/mysql -u root -p
-bash: /usr/local/mysql/bin/mysql: No such file or directory

Workbench doesn't have mysql server embedded in it.

But, You can also access the remote mysql server from the MySQL workbench.

Otherwise, install it manually from the below link(only for macOs 11 - BigSur ARM compatible with Catalina) if you want to have the server locally (your personal machine).

https://dev.mysql.com/doc/refman/8.0/en/macos-installation-pkg.html

It will ask to login oracle site, so please do so.

Note: I selected strong password encryption (SHA26)

MySQL Installation Layout on macOS

Directory	Contents of Directory
`bin`	mysqld server, client and utility programs
`data`	Log files, databases, where `/usr/local/mysql/data/mysqld.local.err` is the default error log
`docs`	Helper documents, like the Release Notes and build information
`include`	Include (header) files
`lib`	Libraries
`man`	Unix manual pages
`mysql-test`	MySQL test suite ('MySQL Test' is disabled by default during the installation process when using the installer package (DMG))
`share`	Miscellaneous support files, including error messages, `dictionary.txt`, and rewriter SQL
`support-files`	Support scripts, such as `mysqld_multi.server`, `mysql.server`, and `mysql-log-rotate`.
`/tmp/mysql.sock`	Location of the MySQL Unix socket

$ cd /usr/local/mysql

$ ls

LICENSE README bin data docs include keyring lib man share support-files

You can also login into MySQL from terminal.

$/usr/local/mysql/bin/mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 21

Server version: 8.0.27 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If you get the below error, while logging into MySQL.

/usr/local/mysql/bin/mysql -u root -p

Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Check if the mysql is running or not.

ps aux | grep mysql

shirley 1934 0.0 0.0 408103312 1344 s000 S+ 12:29pm 0:00.00 grep mysql

MySQL server in my local machine (laptop) is not running. So, first run it from the System Preferences -> MySQL -> Start MySQL Server

ps aux | grep mysql

shirley 1967 0.0 0.0 408120720 1440 s000 S+ 12:32pm 0:00.00 grep mysql

_mysql 1963 0.0 2.5 408817952 423760 ?? Ss 12:32pm 0:00.31 /usr/local/mysql/bin/mysqld --user=_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid --keyring-file-data=/usr/local/mysql/keyring/keyring --early-plugin-load=keyring_file=keyring_file.so

Note: The commands are executed from MacOS BigSur

To connect to MySQL database connection in Eclipse IDE, we can either use external program MySQL workbench or SQL command line tool.

Installation of MySQL Workbench

Install GUI tool MySQLWorkbench (dmg package) from the link https://dev.mysql.com/downloads/workbench/
Once installed, create a new Server connection. Follow the steps in https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-new.html

Connection name can be anything, Hostname can be either 127.0.0.1 or localhost for local SQL server, and port number is 3307 or 3306(default for MySQL).

Here, cw2db is the local mysql server connection and RemoteMySQL is the obviously the remote connection.

Create a schema

After the 3rd step, view the output action in the action item.

Create a Java project

Now, go to your existing Java project in eclipse. https://www.youtube.com/watch?v=0cwk9UMLnWE

Note: While creating new java project, if importing takes a while, just close the Eclipse, it should work.

At this point, I'm not able to make a connection to MySQL which is entirely different software from Eclipse. Some API is needed to make these two separate entities to talk to each other.

Installation of MySQL JDBC Driver

It is an application programming interface that establishes connection between standard database and Java application.

MySQL connector J is a java library so it a pure Java driver. So, it is platform independent. You can download it from https://dev.mysql.com/downloads/connector/j/

Note: Choose Platform Independent for MacOS