英文:
Using jdbcTemplate.query with parameters
问题
我有3个数据库表格：Lecture（讲座）、LectureGroups（讲座组）和Groups（组）。我想要获取特定组在特定日期的日程安排。我尝试以以下方式实现：
@Repository
public class Schedule {
private static final String GET_GROUP_DAY_SCHEDULE = "SELECT * FROM LECTURES " +
"INNER JOIN LECTUREGROUPS ON LECTURES.ID = LECTUREGROUPS.LECTUREID " +
"INNER JOIN GROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID " +
"WHERE GROUPID = :GROUPID AND DATE = :DATE";
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Lecture> getGroupDayLectures(int groupId, LocalDateTime dateTime) {
MapSqlParameterSource parameters = new MapSqlParameterSource()
.addValue("groupid", groupId)
.addValue("date", dateTime);
return jdbcTemplate.query(GET_GROUP_DAY_SCHEDULE, new BeanPropertyRowMapper<>(Lecture.class), parameters);
}
}
但是我在查询中遇到了异常：
Caused by: org.postgresql.util.PSQLException: 无法推断出要为 org.springframework.jdbc.core.namedparam.MapSqlParameterSource 的实例使用的 SQL 类型。请使用带有显式 Types 值的 setObject() 方法来指定要使用的类型。
我该如何修复它？我还尝试了以下方式：
private static final String GET_GROUP_DAY_SCHEDULE = "SELECT * FROM LECTURES " +
"INNER JOIN LECTUREGROUPS ON LECTURES.ID = LECTUREGROUPS.LECTUREID " +
"INNER JOIN GROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID " +
"WHERE GROUPID = ? AND DATE = ?";
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Lecture> getGroupDayLectures(int groupId, LocalDateTime dateTime) {
return jdbcTemplate.query(GET_GROUP_DAY_SCHEDULE, new Object[]{groupId, dateTime}, new BeanPropertyRowMapper<>(Lecture.class));
}
这种方式可以正常工作，但只返回一个讲座（应该是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:
@Repository
public class Schedule {
private static final String GET_GROUP_DAY_SCHEDULE = "SELECT * FROM LECTURES " +
"INNER JOIN LECTUREGROUPS ON LECTURES.ID = LECTUREGROUPS.LECTUREID " +
"INNER JOIN GROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID " +
"WHERE GROUPID = :GROUPID AND DATE = :DATE";
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Lecture> getGroupDayLectures(int groupId, LocalDateTime dateTime) {
MapSqlParameterSource parameters = new MapSqlParameterSource()
.addValue("groupid", groupId)
.addValue("date", dateTime);
return jdbcTemplate.query(GET_GROUP_DAY_SCHEDULE, new BeanPropertyRowMapper<>(Lecture.class), parameters);
}
}
But I get an exception in query raw
Caused by: org.postgresql.util.PSQLException: Can'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
private static final String GET_GROUP_DAY_SCHEDULE = "SELECT * FROM LECTURES " +
"INNER JOIN LECTUREGROUPS ON LECTURES.ID = LECTUREGROUPS.LECTUREID " +
"INNER JOIN GROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID " +
"WHERE GROUPID = ? AND DATE = ?";
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Lecture> getGroupDayLectures(int groupId, LocalDateTime dateTime) {
return jdbcTemplate.query(GET_GROUP_DAY_SCHEDULE, new Object[]{groupId, dateTime}, new BeanPropertyRowMapper<>(Lecture.class));
}
and it works but return only 1 Lecture in list (it must be 3)
答案1
得分: 3
在jdbcTemplate类中有一个带有参数的签名：
public <T> List<T> query(String sql, RowMapper<T> rowMapper, @Nullable Object... args)
因此，可以非常简单地以这种方式使用它：
private static final String GET_GROUP_DAY_SCHEDULE = "SELECT * FROM LECTURES " +
"INNER JOIN LECTUREGROUPS ON LECTURES.ID = LECTUREGROUPS.LECTUREID " +
"INNER JOIN GROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID " +
"WHERE GROUPID = ? AND DATE = ?";
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Lecture> getGroupDayLectures(int groupId, LocalDate date) {
return jdbcTemplate.query(GET_GROUP_DAY_SCHEDULE, new BeanPropertyRowMapper<>(Lecture.class), groupId, date);
}
英文:
There is a signature with parameters in the jdbcTemplate class:
public <T> List<T> query(String sql, RowMapper<T> rowMapper, @Nullable Object... args)
So it is very easy to use it in this way
private static final String GET_GROUP_DAY_SCHEDULE = "SELECT * FROM LECTURES " +
"INNER JOIN LECTUREGROUPS ON LECTURES.ID = LECTUREGROUPS.LECTUREID " +
"INNER JOIN GROUPS ON GROUPS.ID = LECTUREGROUPS.GROUPID " +
"WHERE GROUPID = ? AND DATE = ?";
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Lecture> getGroupDayLectures(int groupId, LocalDate date) {
return jdbcTemplate.query(GET_GROUP_DAY_SCHEDULE, new BeanPropertyRowMapper<>(Lecture.class), groupId, date);
}
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论