JDBC operation check from xampp installation on Ubuntu

Introduction

This is the procedure when I made it myself using xampp to study the mechanism of Web server and JDBC. Maybe there are few people doing it on Ubuntu, or there were few people who encountered similar errors, so I wrote an article. Since I made it for studying, I dare not use the integrated development environment Eclipse.

Information such as version

Virtual machine: VMware Workstation 15 Player Guest OS: Ubuntu 18.04LTS xammp:7.4.5

xampp installation

For xampp, download the installer from the following site and install it appropriately. There should be nothing wrong with it. https://ja.osdn.net/projects/xampp/

When the installation is complete, you should have a lammp folder in / opt. Enter the following command to start Apache, MySQL and ProFTPD.

$ sudo /opt/lampp/lampp start

By the way, when you stop

$ sudo /opt/lampp/lampp stop

is.

Enter "localhost" in the address of the web browser and confirm that it can be entered in the launched web server.

What to do if you can't enter Use the following nmap command to check if port 80 is open.

$ nmap localhost

If it's not open, it's likely to be hit by the Ubuntu firewall. Use the ufw command below to open port 80 (http), and then use the nmap command again to check if it is free.

$ sudo ufw allow 80

By the way, Ubuntu closes port 80 by default. Please note that opening port 80 increases the security risk.

Creating a database

Next, create a test database.

Access "Ubuntu IP address" with a web browser, enter phpMyAdmin on the upper right, and enter Log in as root.

Open the "User Accounts" tab and select "Add User Account". Enter the "user name", "host name", and "password", This time, I set it like this. Username: piyo Hostname: localhost Password: piyopass

After checking "Create a database with the same name and give all privileges" in the "User account dedicated database" column, click the "Run" button at the bottom to create an account. You have now created an account and a database with the same name as the account.

For the time being, enter the test data. Table name: book Columns: (id (int type), name (VARCHAR type), price (int type), data (DATE type))

Select the Insert tab and enter the data as appropriate. The following SQL command is the command at that time. Since it can be inserted using the interface of a web browser, there is no need to directly enter the following command.

INSERT INTO `book` (`id`, `name`, `price`, `date`) VALUES ('1', '1st_chapter', '777', '2020-05-21');

MySQL settings

Next, set up to be able to access MySQL.

First, open port 3306 (MySQL) in the firewall settings.

$ sudo ufw allow 3306

Next, turn off the MySQL skip-networking option. Create a .my.mcnf file in your home directory and enter the following two lines of text.

~/.my.cnf


[mysqld]
skip-networking = off

This was pretty important, but you can't access it via port 3306 without turning off the skip-networking option. By the way, it seems that the default settings in the configuration file differ depending on the version of MySQL, so I had a lot of trouble. By the way, how to make a configuration file is written in the following file.

/etc/mysql/my.cnf


#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram
!includedir /etc/mysql/conf.d/

Reboot with xampp to load the MySQL settings.

$ sudo /opt/lampp/lampp restart

Make sure that port 3306 is open.

$ netstat -lt

Try to enter with the created account. Log in to SQL with the following command. You will be asked to enter the password, so enter it.

$ /opt/lampp/bin/mysql -u piyo -h localhost -p

If you cannot log in properly, please review your account settings. While logged in, enter the following command.

SHOW VARIABLES LIKE 'skip_networking';

If skip_networking is turned off as shown below, the setting is complete.

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+

This completes the preparation on the SQL side.

JDBC settings

Next, prepare for JDBC. First, install the required libraries with the apt command.

$ sudo apt-get install libmysql-java

Add it to your CLASSPATH so that mysql.jar is loaded when you compile java.

$ echo "export CLASSPATH=$CLASSPATH:/usr/share/java/mysql.jar" >> ~/.bashrc

You can use the -cp option every time you compile, but it's annoying, so I added it to bash. Prepare the following sample code.

test.java


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class test{
    public static void main(String[] args){

        final String URL = "jdbc:mysql://localhost:3306/piyo";//Host name: localhost Database name: piyo
        final String USER = "piyo";//User name
        final String PASS = "piyopass";//password
        String SQL = "select * from book;";//SQL statement (select database book)

        //Check if JDBC can be imported
        try{
                Class cls = Class.forName("com.mysql.jdbc.Driver");
                System.out.println("JDBC sucess");
        }catch(ClassNotFoundException e){
                e.printStackTrace();
                System.exit(1);
        }

        //Access the database using JBDC
        try(
            Connection conn = DriverManager.getConnection(URL, USER, PASS);//Login to SQL
            PreparedStatement ps = conn.prepareStatement(SQL)){//Execution of SQL statement
            try(ResultSet rs = ps.executeQuery()){//Store database information in rs
                while(rs.next()){//Update selected column
                    System.out.println(//Display of read columns
                    	rs.getInt("id") + " " +
                    	rs.getString("name") + " " +
                        rs.getString("price") +" "+
			rs.getDate("date"));
                }           
            }catch(SQLException e){//Database information acquisition error
                e.printStackTrace();
                System.exit(1);
            }
        }catch(SQLException e){//Login or SQL statement execution error
            e.printStackTrace();
            System.exit(1);
        }finally{
            System.out.println("finish");//Run if nothing happens
            System.exit(0);
        }
    }
}

Compile & execute.

$ javac test.java
$ java test

here, java.sql.SQLException: No suitable driver found If an error occurs, /usr/share/java/mysql.jar may not have been loaded, so Please review your CLASSPATH again. It is OK if the output is as follows.

JDBC success
1 1st_chapter 777 2020-05-21
finish

Summary

I installed xmapp in Ubuntu environment, hit SQL command with JDBC, and was able to get the result. In the future, I would like to use JDBC to display the results on the browser.

Acknowledgments

bld1509 Helped when installing JDBC on Ubuntu https://bld1509.hatenadiary.org/entry/20080624/1214317382

itdevats Thank you for troubleshooting skip-networking. https://qiita.com/itdevat/items/ca5184dc8445380b966e

IT Sakura It was helpful for creating sample code. https://itsakura.com/java-mysql

Recommended Posts

JDBC operation check from xampp installation on Ubuntu
Ruby installation on WSL2 + Ubuntu 20.04
Build a XAMPP environment on Ubuntu
Elmer / Ice installation (Ubuntu on WSL)
Check when nvidia-smi on Ubuntu doesn't work
Build Cakephp environment from docker installation Ubuntu
Fastest installation of Visual Studio Code on Ubuntu
Using JUnit from the command line on Ubuntu
[Java] Build Java development environment on Ubuntu & check execution
Docker on Ubuntu18.04 on WSL2 and VSCode installation instructions
Notes on updating Ubuntu from the command line (16.04 → 18.04, 18.04 → 20.04 both)
[Ubuntu20.04] From ROS-noetic installation to SLAM simulation of turtlebot3