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)

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>