[Spring boot] $.get 으로 DB select 출력

#postgresql

#spring boot

#mybatis

#ajax

#json

 

 

이 컬럼 전체 내용을 출력해 보도록 하겠습니다.

 

 

 

예상 출력 내용입니다!

 

 

 

 

 

※파일 위치 참고해주세요

특히 xml이랑 html !!

(저는 index.html 파일명을 url에 직접 적을거라 static 폴더에 넣었습니다)

 

 

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.4.5</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.simplify</groupId>
	<artifactId>Test0422</artifactId>
	<version>0.0.1</version>
	<name>Test0422</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.0.1</version>
		</dependency>
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

> mybatis랑 postgresql 의존성 추가해줍니다.

 

 

 

application.properties

(본인에게 맞는 url,ursername, password 입력하세요)

server.port=8080
spring.datasource.url=jdbc:postgresql://localhost:5432/myapp
spring.datasource.username=postgres
spring.datasource.password=0000

mybatis.mapper-locations=mapper/xml/*.xml

 

 

DB연동 참고 (Spring Boot- application.properties 부분은 jdbc.properties 부분을 참고하시면 됩니다)

 

chikaka-dev.tistory.com/15?category=889499

[Spring] spring(sts) postgresql DB 연동

 

 

Controller.java

package com.mycompany.myapp;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.ModelAndView;


@RestController
public class Controller {
	@Autowired
	TestService testService;
	
    @RequestMapping(value ="/api/menu")
	public Map<String, Object> join() {
		Map<String, Object> map = new HashMap<String, Object>();
		List<MenuVO> testList = testService.selectTest();
		
		map.put("name",testList);
		
		System.out.println(map); //hashmap 확인용
		return map;
    }

}

 

 

MenuVO.java

package com.mycompany.myapp;

public class MenuVO {
	private int menu_id;
	private String menu_name;
	
	public int getMenu_id() {
		return menu_id;
	}
	public void setMenu_id(int menu_id) {
		this.menu_id = menu_id;
	}
	public String getName() {
		return menu_name;
	}
	public void setName(String menu_name) {
		this.menu_name = menu_name;
	}
}

 

TestMapper.java

package com.mycompany.myapp;

import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

@Repository
@Mapper
public interface TestMapper {
	List<MenuVO> selectTest();
}

 

TestService.java

package com.mycompany.myapp;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class TestService {
	@Autowired
	public TestMapper mapper;

	public List<MenuVO> selectTest() {
		return mapper.selectTest();
	}
}

 

mapper-menu.xml

<!DOCTYPE mapper
	PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.mycompany.myapp.TestMapper">
    
    <select id="selectTest" resultType="map" >
       SELECT menu_name from menu
    </select>
</mapper>

 

index.html

<!DOCTYPE html>
<html>

<head>
   <meta charset="utf-8">
   <title>index</title>
   <script src="https://code.jquery.com/jquery-3.6.0.js" integrity="sha256-H+K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk="
      crossorigin="anonymous"></script>
   <script>
      $(document).ready(function () {

         $.get("/api/menu", function (data) {
            $("#menuList").empty();
            for (i = 0; i < data.name.length; i++) {
               var tag = "<tr>" +
                  "<td>" + data.name[i].menu_name + "</td>" +
                  "</tr>"
               $("#menuList").append(tag);
            }
         });
      });
   </script>
</head>

<body>
   <h1 id="result">Hello World</h1>

   <table id="menuList">
   </table>

</body>

</html>

 

 

 

참고로 저는 이 주소로 실행하였습니다.

http://localhost:8080/index.html

 

 

 

 

그러면 결과 짠!

 

 

 

 

 

**뭘 못 찾았다는 에러가 나올 때**

 

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.mycompany.myapp.TestMapper.selectTest at org.apache.ibatis.binding.MapperMethod$SqlCommand.(MapperMethod.java:235) ~[mybatis-3.5.1.jar:3.5.1] at org.apache.ibatis.binding.MapperMethod.(MapperMethod.java:53) ~[mybatis-3.5.1.jar:3.5.1] at org.apache.ibatis.binding.MapperProxy.lambda$cachedMapperMethod$0(MapperProxy.java:62) ~[mybatis-3.5.1.jar:3.5.1] at java.base/java.util.concurrent.ConcurrentHashMap.computeIfAbsent(ConcurrentHashMap.java:1708) ~[na:na] at org.apache.ibatis.binding.MapperProxy.cachedMapperMethod(MapperProxy.java:62) ~[mybatis-3.5.1.jar:3.5.1] at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:57) ~[mybatis-3.5.1.jar:3.5.1] at com.sun.proxy.$Proxy53.selectTest(Unknown Source) ~[na:na] at 

 

~~

어쩌구저쩌구 에러가 나올때

- application.properties에 적은  mybatis.mapper-locations 주소를 제대로 적었는지 확인한다.

- 주소도 맞는데 계속해서 에러가 나오면 xml 파일명을 다른걸로 바꿔본다.