如何在Java Spring Boot中使用多个联接SQL查询 [英] how to use multiple join sql query in java spring boot

问题描述

我想在Java Spring Boot中使用多个联接查询，但找不到答案 因此，如果您知道解决方案，请发表评论

i want to use multiple join query in java spring boot i can't found answer so if you know the solution plz comment it

public interface CategoryRepository extends JpaRepository<Category, Integer> {

@Query(value = "SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 FROM category AS t1 LEFT JOIN category AS t2 ON t2.parent = t1.category_id LEFT JOIN category AS t3 ON t3.parent = t2.category_id LEFT JOIN category AS t4 ON t4.parent = t3.category_id WHERE t1.name = :'ROOT'",nativeQuery = true)
List<Category> findByCategory(String query);
}

这是我的CategoryRepository代码

this is my CategoryRepository Code

@Data
@Entity
@Table(name="category")
@DynamicInsert
@DynamicUpdate
@NoArgsConstructor
@AllArgsConstructor
public class Category {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="category_id", columnDefinition = "INT(11)")
private int Category_id;
private String name;
private int parent;

}

这是我的类别代码

@RestController
@RequestMapping(value = "/category")
@Slf4j
public class CategoryController {
@Autowired CategoryRepository categoryRepository;

@RequestMapping(value = "/all", method = RequestMethod.GET)
@ResponseBody
public String getDomainList(String query) {
    List<Category> all= this.categoryRepository.findByCategory(query);
    return all.toString();
    //log.info(query);
    //return "Test";
}
} 

这是我的CategoryController代码

this is my CategoryController Code

所以当我运行代码

2019-10-31 11:10:30.124  WARN 2356 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : 
SQL Error: 0, SQLState: S0022
2019-10-31 11:10:30.124 ERROR 2356 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : 
Column 'category_id' not found

我收到此错误消息 我真的很想运行它..谢谢

i got the this error message i really crave to run it.. thanks

推荐答案

尝试此方法

String query= "SELECT firewall_items.src, firewalls.info 
FROM firewall_items 
JOIN firewalls 
ON firewall_items.firewalls_id = firewalls.id"
List<Item> items = jdbcTemplate.query(
      query, 
      new MapSqlParameterSource(), 
      new FirewallInfoRowMapper()
      );

这篇关于如何在Java Spring Boot中使用多个联接SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！