Spring的jdbcTemplate查询执行原生sql

public AppointmentEvaluateVo searchMyfeedbackDetail(String accountId, String fbId) { String sql = "select ae.id as fbId ,ae.app_id as appId, a.app_no as appNo,"
+ " si.service_item AS serItem,a.app_service_time as feedTime,ae.remark as"
+ " feedCon, cou.attitudeScore AS attitudeScore,cou.qualityScore AS qualityScore,"
+ "cou.prescriptionScore AS prescriptionScore from appointment_evaluate ae LEFT JOIN"
+ " appointment a ON ae.app_id=a.id LEFT JOIN organization_service_item osi "
+ "ON a.item_id=osi.id LEFT JOIN service_item si ON osi.service_item_id=si.id "
+ "LEFT JOIN ( SELECT _a.item_id, count(_a.id) AS orderNum, _ae.attitude as "
+ "attitudeScore, _ae.quality as qualityScore, _ae.prescription as prescriptionScore "
+ " FROM appointment _a RIGHT JOIN appointment_evaluate _ae ON `_a`.id = `_ae`.app_id "
+ " GROUP BY _a.item_id) cou ON osi.id = cou.item_id where 1=1 and ae.creator='" + accountId
+ "' and ae.id='" + fbId + "';"; List rows = jdbcTemplate.queryForList(sql);
AppointmentEvaluateVo av = new AppointmentEvaluateVo();
Iterator ite = rows.iterator();
while (ite.hasNext()) {
Map avMap = (Map) ite.next();
av.setFbId(avMap.get("fbId").toString());
av.setAppId(avMap.get("appId").toString());
av.setAppNo(avMap.get("appNo").toString());
av.setSerItem(avMap.get("serItem").toString());
av.setFeedTime(avMap.get("feedTime").toString());
av.setFeedCon(avMap.get("feedCon").toString());
av.setTotScore(avMap.get("prescriptionScore").toString());
av.setTquaScore(avMap.get("qulityScore").toString());
av.setSerScore(avMap.get("attitudeScore").toString());
}
return av;
}

public Student findStudentById(int id){
String sql = "select * from tb_student where id=?";
final Student student = new Student();
jdbcTemplate.query(sql,new Object[]{id},new RowCallbackHandler(){
public void processRow(ReultSet resultSet) throws SQLException{
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
}
});
return student;
}

package com.demo.manager;import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.UUID;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;import com.demo.pojo.Account;@Repository
public class AccountManager {
@Autowired
private JdbcTemplate jdbcTemplate; /**
* 添加
*
* @param ac
*/
public void addAccount(Account account) { jdbcTemplate.update("insert into tb_account(id,name,address,age) values (?,?,?,?)",
new Object[] { StringUtils.hasText(account.getId()) ? account.getId() : UUID.randomUUID(),
StringUtils.hasText(account.getName()) ? account.getName() : "",
StringUtils.hasText(account.getAddress()) ? account.getAddress() : "", account.getAge() });
} /**
* 根据id获取账户信息
* @param a
* @return
*/
public Account getAccount(Account a) {
Account account = jdbcTemplate.queryForObject("select * from tb_account where id=?", new Object[] { a.getId() },
new BeanPropertyRowMapper<>(Account.class));
return account;
} public List<Account> getAll() {
List<Account> accounts = jdbcTemplate.query("select * from tb_account",new RowMapper<Account>() { @Override
public Account mapRow(ResultSet rs, int num) throws SQLException { Account account = new Account();
account.setId(rs.getString(1));
account.setName(rs.getString(2));
account.setAddress(rs.getString(3));
account.setAge(rs.getInt(4));
return account;
}
});
return accounts;
} /**
* 删除
*
* @param ac
*/
public void deleteAccount(Account account) { jdbcTemplate.update("delete from tb_account where id=?",
new Object[] { account.getId() });
} /**
* 更新
*
* @param ac
*/
public void updateAccount(Account account) { jdbcTemplate.update("update tb_account set name=?,address=? where id=?",
new Object[] { account.getName(),account.getAddress(),account.getId() });
}
}