【springboot】springboot使用mybatis通过ssh链接数据库

在Spring Boot中使用MyBatis通过SSH（Secure Shell）隧道连接到数据库是一种相对复杂的配置，因为SSH隧道本质上是在本地机器和远程服务器之间建立一个加密的网络连接，通常用于绕过防火墙或提高安全性。

kkwyting

800人浏览 · 2025-01-14 17:14:38

kkwyting · 2025-01-14 17:14:38 发布

以下是介绍如何在Spring Boot项目中配置MyBatis通过SSH隧道连接到数据库：

1. 添加依赖

首先，确保你的Spring Boot项目中包含必要的依赖项。你需要在pom.xml中添加MyBatis和数据库驱动的依赖。

<dependencies>
    <!-- Spring Boot Starter JDBC -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

    <!-- MyBatis Spring Boot Starter -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>

    <!-- MySQL Driver (or your specific DB driver) -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- JSch for SSH Tunneling -->
    <dependency>
        <groupId>com.jcraft</groupId>
        <artifactId>jsch</artifactId>
        <version>0.1.55</version>
    </dependency>
</dependencies>

2. 配置SSH隧道

使用JSch库来设置SSH隧道。你可以在一个配置类中完成这个操作。

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

@Configuration
public class DataSourceConfig {

    private static final String SSH_HOST = "your.ssh.host";
    private static final int SSH_PORT = 22;
    private static final String SSH_USER = "your_ssh_username";
    private static final String SSH_PASSWORD = "your_ssh_password";

    private static final String DB_HOST = "127.0.0.1"; // Localhost for SSH tunnel
    private static final int DB_PORT = 3306; // Local port for SSH tunnel
    private static final String DB_USER = "your_db_username";
    private static final String DB_PASSWORD = "your_db_password";
    private static final String DB_NAME = "your_database_name";

    @Bean
    public DataSource dataSource() throws Exception {
        // Set up SSH tunnel
        JSch jsch = new JSch();
        Session session = jsch.getSession(SSH_USER, SSH_HOST, SSH_PORT);
        session.setPassword(SSH_PASSWORD);

        // Avoid asking for key confirmation
        Properties config = new Properties();
        config.put("StrictHostKeyChecking", "no");
        session.setConfig(config);

        session.connect();

        // Set up port forwarding (SSH tunnel)
        session.setPortForwardingL(DB_PORT, DB_HOST, 3306); // Assuming MySQL on remote server

        // Create database connection
        String url = "jdbc:mysql://localhost:" + DB_PORT + "/" + DB_NAME;
        Properties props = new Properties();
        props.setProperty("user", DB_USER);
        props.setProperty("password", DB_PASSWORD);

        return DriverManager.getConnection(url, props);
    }
}

3. 配置MyBatis

在application.properties或application.yml中配置MyBatis。

# application.properties
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.example.demo.model

或者使用YAML格式：

# application.yml
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.demo.model

4. 创建Mapper接口和XML文件

创建Mapper接口和对应的XML文件，用于定义SQL语句。

// UserMapper.java
package com.example.demo.mapper;

import com.example.demo.model.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface UserMapper {
    @Select("SELECT * FROM users")
    List<User> findAll();
}

<!-- UserMapper.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.UserMapper">
    <select id="findAll" resultType="com.example.demo.model.User">
        SELECT * FROM users
    </select>
</mapper>

5. 使用Mapper

在你的服务或控制器中使用Mapper。

// UserService.java
package com.example.demo.service;

import com.example.demo.mapper.UserMapper;
import com.example.demo.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public List<User> getAllUsers() {
        return userMapper.findAll();
    }
}

// UserController.java
package com.example.demo.controller;

import com.example.demo.model.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/users")
    public List<User> getUsers() {
        return userService.getAllUsers();
    }
}