在spring与hibernate整合时进行数据库检索,执行原生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; }
第二种:
1
2
3
4
5
6
7
8
9
10
11
|
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() }); } }