Spring的jdbcTemplate查询执行原生sql

摘自：http://www.cnblogs.com/blog411032/p/5949670.html

在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() }); } }

转载于:https://www.cnblogs.com/GaoAnLee/p/7229359.html