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.
Virtual machine: VMware Workstation 15 Player Guest OS: Ubuntu 18.04LTS xammp：7.4.5
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.
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');
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.
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
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.
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