Java – Connect to MySQL on Ubuntu and run select query No.80

# Install mysql
sudo apt-get update
sudo apt-get install mysql-server

# Start mysql service
sudo systemctl start mysql

# enable MySQL to start automatically when the machine is online
sudo systemctl enable mysql

sudo apt install ./mysql-connector-java_8.0.30-1ubuntu20.04_all.deb
sudo ln -s /usr/share/java/mysql-connector-java-8.0.30.jar $HIVE_HOME/lib/mysql-connector-java.jar

# Connect to mysql using root user without password

sudo mysql -u root

# Change plugin to make root access to MySQL with password authentication.

mysql> USE mysql;
mysql> UPDATE user SET plugin='caching_sha2_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> select user,host,plugin from mysql.user;
mysql> exit;

# Restart mysql

sudo service mysql restart

# Create Database "testdb" and table "testtbl". after that, some records are inserted.
# Connect to mysql using root without password (Just press enter if password is required)
mysql -u root -p

# Create database "testdb"

mysql> CREATE DATABASE testdb;
mysql> USE testdb;
mysql> create table testtbl (c1 int, c2 int);
mysql> insert testtbl values(1,1),(2,2),(3,3);
mysql> select * from testtbl;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

# Create user "hiveuser" and passoward "hivepassword"

mysql> CREATE USER 'hiveuser'@'localhost' IDENTIFIED BY 'hivepassword';
mysql> GRANT all on *.* to 'hiveuser'@'localhost';
mysql> flush privileges;
mysql> exit;

package org.example.mysqlcon;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class mysqlcontest {

    private static final Logger log;
    private static final String DB_URL = "jdbc:mysql://localhost:3306/testdb";
    private static final String USER = "hiveuser";
    private static final String PASSWORD = "hivepassword";

    static {
        log = Logger.getLogger(mysqlcontest.class.getName());
    }

    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        Statement stmt = null;

        // Register JDBC driver
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            log.log(Level.SEVERE, "Where is your MySQL JDBC Driver?", e);
            return;
        }

        try {
            // Register JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // pen a connection
            log.log(Level.INFO, "Connecting to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);

            // Execute a query
            log.log(Level.INFO, "Creating statement...");
            stmt = conn.createStatement();
            String sql = "SELECT * FROM testtbl";
            ResultSet rs = stmt.executeQuery(sql);

            // Extract data from result set
            while (rs.next()) {
                // Retrieve by column name
                int c1 = rs.getInt("c1");
                int c2 = rs.getInt("c2");

                // Display values
                System.out.println("c1: " + c1 + ", c2: " + c2);
            }

            //Clean-up environment
            rs.close();
        } catch (SQLException se) {
            // Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            // Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {
            } // nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            } // end finally try
        } // end try
        log.log(Level.INFO, "Operation done successfully");
    }
} 

Nov 21, 2022 4:48:14 PM org.example.mysqlcon.mysqlcontest main
INFO: Connecting to database...
Nov 21, 2022 4:48:15 PM org.example.mysqlcon.mysqlcontest main
INFO: Creating statement...
c1: 1, c2: 1
c1: 2, c2: 2
c1: 3, c2: 3
Nov 21, 2022 4:48:15 PM org.example.mysqlcon.mysqlcontest main
INFO: Operation done successfully