To connect from Spring to MySQL on virtual server (unsolved)

Overview

I was using the H2 database of the embedded database with SpringBoot + Thymeleaf + Java, but I was instructed to put MySQL on the virtual server and connect it, so I am struggling to replace the DB. I've been spit out a lot of error logs for over a week now, and I'm addicted to it ~~ Still unsolved ~~, so I'll make a note of what seems to be correct so far in order to organize the memorandum + my head. If you are familiar with this, please give me some advice.

2019/6/14 postscript: I managed to succeed in the test connection. It's still a lot uncertain. This works for the time being, but if you are using an embedded server in the Repository class and Entity class, it seems that you will need to rewrite almost everything to change to this method. Especially Controller is annihilated. Can html be used as it is?

Required class (maybe)

name of the class                                                                       Main function explanation
JSch Create a Session instance with SSH user, SSH password, SSH port, etc. as arguments
Session Connect to a virtual server. Instance is not new but JSch getSession(args)Generate by method. Queries can only be issued while this connection is alive (should)
Properties (Maybe) extension used to add settings to an instance of Session.It is generally used to read the properties file, and it seems that it is not used only for the server side or DB.
JdbcTemplate This instance issues a SQL query. In other words, the goal is to be able to use an instance of this from the Controller.

build.gradle Add the following to dependencies (the original part is OK) After adding, refresh gradle.

build.gradle


dependencies {
	implementation 'org.springframework.boot:spring-boot-starter'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
	compile 'org.springframework.boot:spring-boot-starter'
    compile 'org.springframework.boot:spring-boot-starter-jdbc'
    compile 'mysql:mysql-connector-java:5.1.35'
    compile 'com.jcraft:jsch:0.1.53'
}

application.properties ** * If you write this in a state where it is not set properly, the project will not start, so it will be a big deal if you do not do GitCommit immediately before. ** **

application.properties


	spring.datasource.url=jdbc:mysql://127.0.0.1:3306/Database name created in MySQL on the virtual server
	spring.datasource.username=MySQL username
	spring.datasource.password=MySQL password
	spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Create your own SSHConnection class.

Write the SSH password and port number of the virtual server here. I think the MySQL port number is fixed at 3306. The SSH remote port number seems to be fixed at 2222, but it is 22 depending on the commentary site. Or it says that it is safe to use a completely different number. If you use Vagrant to install CentOS with reference to dot installation, I think that vagrant is the default for the user name and password, but if you change it yourself, it will be. In addition, if you want to authenticate the key, you have to introduce the commented out part, but it seems to work even if you comment out as it is.

SSHConnection.java



/**
 * @author tabuchikenta
 * 
 */
public class SSHConnection {
	private final static String S_PATH_FILE_PRIVATE_KEY = "/Users/Mac username/.ssh/id_rsa"; 
	private final static String S_PATH_FILE_KNOWN_HOSTS = "/Users/Mac username/.ssh/known_hosts";
	private final static String S_PASS_PHRASE = "vagrant";
	private final static int LOCAl_PORT = 3306; 
	private final static int REMOTE_PORT = 3306; 
	private final static int SSH_REMOTE_PORT = 2222; 
	private final static String SSH_USER = "vagrant";
	private final static String SSH_REMOTE_SERVER = "127.0.0.1";
	private final static String MYSQL_REMOTE_SERVER = "127.0.0.1";

	private Session session; //represents each ssh session
	
	public void closeSSH ()
	{
	    session.disconnect();
	}

	public SSHConnection () throws Throwable
	{
	    JSch jsch = null;

	        jsch = new JSch();
	       // jsch.setKnownHosts(S_PATH_FILE_KNOWN_HOSTS);
	       // jsch.addIdentity(S_PATH_FILE_PRIVATE_KEY, S_PASS_PHRASE.getBytes());
	       
	        session = jsch.getSession(SSH_USER, SSH_REMOTE_SERVER, SSH_REMOTE_PORT);
	        session.setPassword(S_PASS_PHRASE);
	        final Properties config = new Properties();
            config.put("StrictHostKeyChecking", "no");
            session.setConfig(config);
	        session.connect();          
	        session.setPortForwardingL(LOCAl_PORT, MYSQL_REMOTE_SERVER, REMOTE_PORT); 

	}
}

Application class static void main (String [] args) Maybe you don't have to do anything

Delete your.pacage and write your package name (usually it should be imported automatically). If you look at other sites, everything is packed here or you do various things, so it may be recommended to do so, but it worked even if you cut it out elsewhere, so maybe I will not write anything other than startup in such a place I think it's better. There is a person who puts it in a container and it seems that it is better, but for the time being I wanted to eliminate as much as possible the part that seems to be unrelated to the connection, so I deleted it. It seems to work with the IDE spitting out normally. (Excuse me if I am wrong)

MySQLTestApplication.java


package com.your.package;//Change this to suit you
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class MySqlTest2Application {
    public static void main(String[] args) {
        	SpringApplication.run(MySqlTest2Application.class, args);
        }
    }

Addition of DAO class

When I annotated private JdbcTemplate jdbc; directly with @Autowired, it didn't work with NullPointerException exception. It seems that @Autowired works only in Application class or Controller class for JdbcTemplate instance (maybe it's just wrong) Therefore, I decided to generate it in the Controller class and receive it as an argument in the constructor of this DAO class and use it. I'm still in the process of making a prototype, but I think it's okay to have only one instance of this class, so I think it's better to use the Singleton pattern so that it won't be new from the outside. Also, although the recordset is currently assigned to List as it is, I think that you should actually create an Entity class and put that instance in List.

MyDao.java



package com.your.packages;

import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;

/*Is it actually better to use a singleton pattern? ..*/
public class MyDao {

    private JdbcTemplate jdbc;
    
    public MyDao(JdbcTemplate jdbc) {
    	this.jdbc = jdbc;
    	try {
			SSHConnection sshcon = new SSHConnection();//Now connect to MySQL on the virtual server.
		} catch (Throwable e) {
			e.printStackTrace();
		}
    }
    /*Issue a SQL query and return a recordset*/
    public List<Map<String,Object>> findAll(){
    	List list = this.jdbc.queryForList("SELECT * FROM M_student");//Issuing SQL
    	list.forEach(System.out::println);//For checking the log, you can delete it if you can check the operation.
    	return list;
    }
    	
}

Create Controller class

Lines with comments change processing according to their own page.

MyController.java



package com.kenta.tabuchi.test;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class MyController {

    @Autowired
    private JdbcTemplate jdbc;
    
	@RequestMapping(value= "/",method=RequestMethod.GET)
	public ModelAndView indexGet(ModelAndView mav) {
		mav.setViewName("index");
		MyDao dao = new MyDao(jdbc);
		List<Map<String,Object>> recordset = dao.findAll();//Now take out the recordset
		mav.addObject("msg",recordset.get(0));//Pour into an HTML template.
		
		return mav;
		
	}
}

Create index.html

The result is poured into the "msg" variable specified by addObject above. Change the variable name etc. according to the above controller.

index.html



<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>MySQL test</title>
</head>
<body>
	<p>Hello MySQLTest</p>
	<p th:text="${msg}"></p>
</body>
</html>

Recommended Posts

To connect from Spring to MySQL on virtual server (unsolved)
Upgrade from MYSQL5.7 to 8.0 on CentOS 6.7
02. I made an API to connect to MySQL (MyBatis) from Spring Boot
Connect from Java to MySQL using Eclipse
Connect to Aurora (MySQL) from a Java application
Connect with VS Code from a Windows client to Docker on another server
Can't connect to local MySQL server through socket'/tmp/mysql.sock' (2) error
How to connect to ClearDB from Sequel Pro on Heroku
[Ruby on Rails] From MySQL construction to database change
I want to connect to Heroku MySQL from a client
Try Spring Boot from 0 to 100.
Connect from Java to PostgreSQL
Connect to MySQL 8 with Java
[Android] Connect to MySQL (unfinished)
Can't connect to local MySQL server through socket'/tmp/mysql.sock' (2) error remedy
Can't connect to local MySQL server through socket'/var/lib/mysql/mysql.sock' (2) until resolution.
Stop resending from client to server
Steps to install samba on CentOS 8 and connect from Windows 10 Explorer
Steps to install MySQL 8 on CentOS 8
Change DB from SQLite to MySQL
Notes on migrating from CircleCI 1.0 to 2.0
Upgrade spring boot from 1.5 series to 2.0 series
Connect to Rails server with iPhone
Update MySQL from 5.7 to 8.0 with Docker
[Java] How to retrieve the parameters passed from html on the server side
ERROR 2002 (HY000) when starting MySQL: Can't connect to local MySQL server through socket'/tmp/mysql.sock' (2)
Language summary to learn from now on
Kick ShellScript on the server from Java
Story when moving from Spring Boot 1.5 to 2.1
Changes when migrating from Spring Boot 1.5 to Spring Boot 2.0
Transition from Struts2 to Spring MVC (Controller)
Changes when migrating from Spring Boot 2.0 to Spring Boot 2.2
Serially connect to Ubuntu on Raspberry Pi
[Reverse lookup] Spring Security (updated from time to time)
Migration from Eclipse to IntelliJ (on the way)
Migrate from Java to Server Side Kotlin + Spring-boot
Ssh login to the app server on heroku
Migrating from Eclipse server function (Tomcat) to Embed Tomcat
Note how to rollback Mysql deployed on Heroku
Touch all Spring "Guides" (updated from time to time)
[Rails MySQL] How to reset DB on heroku
DataSource connection from WebSphere to MySQL (DataSource property change?)
Minecraft BE server development from PHP to Java
[Ruby on rails + Mysql] Data migration procedure memo when switching from heroku to AWS