How to Make a Basic SELECT FROM call to my MySQL database using RESTFUL API with SpringBoot-MyBatis-MySQL?

Question

Question is pretty straightforward. I want to make a basic SELECT * FROm call to my MySQL database using the mentioned tools above. I will be using XML-style mapper MyBatis config

So far Im successful when using string statements

@RestController
@RequestMapping("/")
public class ApplicationRestController {

    Actor actor;
    //this works easily because no connections to SQL are being made.
    @GetMapping("/hello")
    public String hello() {
        return "HELO WORLD";        
    }
}

But what if I wanted to fetch data from my database? How to do this? Do I need SqlSession?

Does anyone have a useful link?

THIS IS MY UPDATED ERROR:

Error creating bean with name 'applicationRestController': Unsatisfied dependency expressed through field 'actorMapper'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'actorMapper' defined in file [/Users/helios/Documents/workspace-sts-3.9.8.RELEASE/mybatis-sakila/target/classes/com/helios/mybatissakila/mappers/ActorMapper.class]: Unsatisfied dependency expressed through bean property 'sqlSessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sqlSessionFactory' defined in class path resource [org/mybatis/spring/boot/autoconfigure/MybatisAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.apache.ibatis.session.SqlSessionFactory]: Factory method 'sqlSessionFactory' threw exception; nested exception is org.springframework.core.NestedIOException: Failed to parse mapping resource: 'class path resource [mybatis-sakila/src/main/resources/mybatis-mapper/ActorMapper.xml]'; nested exception is

java.io.FileNotFoundException: class path resource [mybatis-sakila/src/main/resources/mybatis-mapper/ActorMapper.xml] cannot be opened because it does not exist

These are my relevant files:

Actor.java

public class Actor {

    private static final long serialVersionUID = 1L;

    private int actor_id;
    private String first_name;
    private String last_name;
    private Date last_update;

    public int getActor_id() {
        return actor_id;
    }
    public void setActor_id(int actor_id) {
        this.actor_id = actor_id;
    }
    public String getFirst_name() {
        return first_name;
    }
    public void setFirst_name(String first_name) {
        this.first_name = first_name;
    }
    public String getLast_name() {
        return last_name;
    }
    public void setLast_name(String last_name) {
        this.last_name = last_name;
    }
    public Date getLast_update() {
        return last_update;
    }
    public void setLast_update(Date last_update) {
        this.last_update = last_update;
    }
}

ActorMapper.java

import org.apache.ibatis.annotations.Mapper;
import org.springframework.beans.factory.annotation.Autowired;  
import com.helios.mybatissakila.model.Actor;

@Mapper
public interface ActorMapper {

    List <Actor> selectAllActors();
}

ApplicationRestController.java

@RestController
@RequestMapping("/")
public class ApplicationRestController {

    @Autowired
    ActorMapper actorMapper;

    //working as not connected to mysql
    @GetMapping("/hello")
    public String hello() {
        return "MyBatis + SpringBoot + MySQL!";

    }
    //error 
    @GetMapping("/actors")
    public List<Actor> selectAllActors(){
            return actorMapper.selectAllActors();
    }
}

ActorMapper.xml

<resultMap id="ActorResultMap" type="Actor">
    <id column="actor_id" property="actor_id" jdbcType="INTEGER"/>
    <result column="first_name" property="first_name" />
    <result column="last_name" property="last_name" />
    <result column="last_update" property="last_update" />
</resultMap>

<select id="selectAllActors" resultMap="ActorResultMap">
        select * from actor
</select>

application.properties

server.port = 9090

spring.datasource.url= jdbc:mysql://localhost:3306/sakila
spring.datasource.username = root
spring.datasource.password = password
spring.datasource.dbcp2.test-while-idle= true
spring.datasource.dbcp2.validation-query= select 1

#mybatis entity scan packages
mybatis.type-aliases-package=com.helios.mybatissakila.model
#Mapper.xml location
mybatis.mapper-locations=classpath*:mybatis-sakila/src/main/resources/ActorMapper.xml
mybatis.typeAliasesPackage=com.helios.mybatissakila.model.Actor

logging.level.root=WARN
logging.level.sample.mybatis.mapper=TRACE

I have an inkling this is wrong, can you help me fix it as well? Is "classpath" just a boilerplate code which should be replaced with a real value?

mybatis.mapper-locations=classpath*:mybatis-sakila/src/main/resources/ActorMapper.xml

Note: I've managed to perform REST API calls using @Annotation style, but I want to use XML-style.

Answer 1

Ok, this is how I fixed it:

As I was indeed suspecting, the classpath was a major factor to make this work. Once I've provided the correct path in application.properties:

mybatis.mapper-locations=classpath:/mybatis-mapper/ActorMapper.xml

I was able to make the SELECT call from MySQL. Isn't this interesting? :)