使用jdbcTemplate.query与参数

huangapple
go评论95阅读模式
英文:

Using jdbcTemplate.query with parameters

问题

我有3个数据库表格：Lecture（讲座）、LectureGroups（讲座组）和Groups（组）。我想要获取特定组在特定日期的日程安排。我尝试以以下方式实现：

  1. @Repository
  2. public class Schedule {
  4.     private static final String GET_GROUP_DAY_SCHEDULE = "SELECT * FROM LECTURES " +
  5.             "INNER JOIN LECTUREGROUPS ON LECTURES.ID = LECTUREGROUPS.LECTUREID " +
  6.             "INNER JOIN GROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID " +
  7.             "WHERE GROUPID = :GROUPID AND DATE = :DATE";
  9.     @Autowired
  10.     private JdbcTemplate jdbcTemplate;
  12.     public List<Lecture> getGroupDayLectures(int groupId, LocalDateTime dateTime) {
  13.         MapSqlParameterSource parameters = new MapSqlParameterSource()
  14.                 .addValue("groupid", groupId)
  15.                 .addValue("date", dateTime);
  16.         return jdbcTemplate.query(GET_GROUP_DAY_SCHEDULE, new BeanPropertyRowMapper<>(Lecture.class), parameters);
  17.     }
  18. }

但是我在查询中遇到了异常：

  1. Caused by: org.postgresql.util.PSQLException: 无法推断出要为 org.springframework.jdbc.core.namedparam.MapSqlParameterSource 的实例使用的 SQL 类型。请使用带有显式 Types 值的 setObject() 方法来指定要使用的类型。

我该如何修复它？我还尝试了以下方式：

  1. private static final String GET_GROUP_DAY_SCHEDULE = "SELECT * FROM LECTURES " +
  2.             "INNER JOIN LECTUREGROUPS ON LECTURES.ID = LECTUREGROUPS.LECTUREID " +
  3.             "INNER JOIN GROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID " +
  4.             "WHERE GROUPID = ? AND DATE = ?";
  6.     @Autowired
  7.     private JdbcTemplate jdbcTemplate;
  9.     public List<Lecture> getGroupDayLectures(int groupId, LocalDateTime dateTime) {
  10.         return jdbcTemplate.query(GET_GROUP_DAY_SCHEDULE, new Object[]{groupId, dateTime}, new BeanPropertyRowMapper<>(Lecture.class));
  11.     }

这种方式可以正常工作，但只返回一个讲座（应该是3个）。如何解决这个问题？

英文:

I have 3 tables in Database Lecture--< LectureGroups >-- Groups.
And I want to get schedule for a certain group on a certain day. I try to do it in this way:

  1. @Repository
  2. public class Schedule {
  4.     private static final String GET_GROUP_DAY_SCHEDULE = &quot;SELECT * FROM LECTURES &quot; +
  5.             &quot;INNER JOIN LECTUREGROUPS ON LECTURES.ID = LECTUREGROUPS.LECTUREID &quot; +
  6.             &quot;INNER JOIN GROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID &quot; +
  7.             &quot;WHERE GROUPID = :GROUPID AND DATE = :DATE&quot;;
  9.     @Autowired
  10.     private JdbcTemplate jdbcTemplate;
  12.     public List&lt;Lecture&gt; getGroupDayLectures(int groupId, LocalDateTime dateTime) {
  13.         MapSqlParameterSource parameters =  new MapSqlParameterSource()
  14.                 .addValue(&quot;groupid&quot;, groupId)
  15.                 .addValue(&quot;date&quot;, dateTime);
  16.         return jdbcTemplate.query(GET_GROUP_DAY_SCHEDULE, new BeanPropertyRowMapper&lt;&gt;(Lecture.class), parameters);
  17.     }
  18. }

But I get an exception in query raw

  1. Caused by: org.postgresql.util.PSQLException: Can&#39;t infer the SQL type to use for an instance of org.springframework.jdbc.core.namedparam.MapSqlParameterSource. Use setObject() with an explicit Types value to specify the type to use.

How I can fix it?
I also used variant with

  1. private static final String GET_GROUP_DAY_SCHEDULE = &quot;SELECT * FROM LECTURES &quot; +
  2.             &quot;INNER JOIN LECTUREGROUPS ON LECTURES.ID = LECTUREGROUPS.LECTUREID &quot; +
  3.             &quot;INNER JOIN GROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID &quot; +
  4.             &quot;WHERE GROUPID = ? AND DATE = ?&quot;;
  6.     @Autowired
  7.     private JdbcTemplate jdbcTemplate;
  9.     public List&lt;Lecture&gt; getGroupDayLectures(int groupId, LocalDateTime dateTime) {
  10.         return jdbcTemplate.query(GET_GROUP_DAY_SCHEDULE, new Object[]{groupId, dateTime}, new BeanPropertyRowMapper&lt;&gt;(Lecture.class));
  11.     }

and it works but return only 1 Lecture in list (it must be 3)

答案1

得分: 3

在jdbcTemplate类中有一个带有参数的签名：

  1. public <T> List<T> query(String sql, RowMapper<T> rowMapper, @Nullable Object... args)

因此，可以非常简单地以这种方式使用它：

  1. private static final String GET_GROUP_DAY_SCHEDULE = "SELECT * FROM LECTURES " +
  2.             "INNER JOIN LECTUREGROUPS ON LECTURES.ID = LECTUREGROUPS.LECTUREID " +
  3.             "INNER JOIN GROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID " +
  4.             "WHERE GROUPID = ? AND DATE = ?";
  6. @Autowired
  7. private JdbcTemplate jdbcTemplate;
  9. public List<Lecture> getGroupDayLectures(int groupId, LocalDate date) {
  10.     return jdbcTemplate.query(GET_GROUP_DAY_SCHEDULE, new BeanPropertyRowMapper<>(Lecture.class), groupId, date);
  11. }
英文:

There is a signature with parameters in the jdbcTemplate class:

  1. public &lt;T&gt; List&lt;T&gt; query(String sql, RowMapper&lt;T&gt; rowMapper, @Nullable Object... args)

So it is very easy to use it in this way

  1. private static final String GET_GROUP_DAY_SCHEDULE = &quot;SELECT * FROM LECTURES &quot; +
  2.             &quot;INNER JOIN LECTUREGROUPS ON LECTURES.ID = LECTUREGROUPS.LECTUREID &quot; +
  3.             &quot;INNER JOIN GROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID &quot; +
  4.             &quot;WHERE GROUPID = ? AND DATE = ?&quot;;
  6.     @Autowired
  7.     private JdbcTemplate jdbcTemplate;
  9.     public List&lt;Lecture&gt; getGroupDayLectures(int groupId, LocalDate date) {
  10.         return jdbcTemplate.query(GET_GROUP_DAY_SCHEDULE, new BeanPropertyRowMapper&lt;&gt;(Lecture.class), groupId, date);
  11.     }
  13. </details>

huangapple
  • 本文由 发表于 2020年10月2日 00:16:23
  • 转载请务必保留本文链接：https://go.coder-hub.com/64159368.html
  • java
  • jdbc
  • jdbctemplate
  • spring
  • spring-jdbc
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定