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;
    }

　　第二种：

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