Spring JdbcTemplate查询实例

本文转载自http://www.yiibai.com/spring/spring-jdbctemplate-querying-examples.html

 

在Spring2.5中，带有一个方便 RowMapper 实现所谓“BeanPropertyRowMapper”，它可以通过匹配行的名字的列值映射到一个属性。只要确保这两个属性和列具有相同的名称，如属性“CUSTID'将匹配到列名为：”CUSTID'或下划线“CUST_ID”。

这样可以不用在代码中配置datasource，在bean中配置即可

查询单行时

public Customer findByCustomerId2(int custId){
         
    String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
 
    Customer customer = (Customer)getJdbcTemplate().queryForObject(
            sql, new Object[] { custId }, 
            new BeanPropertyRowMapper(Customer.class));
    
    return customer;
}

 

查询多行时

public List<Customer> findAll(){
        
    String sql = "SELECT * FROM CUSTOMER";
        
    List<Customer> customers  = getJdbcTemplate().query(sql,
            new BeanPropertyRowMapper(Customer.class));
        
    return customers;
}

 

 

spring batchInsert

package springDao;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by luozhitao on 2017/8/7.
 */
public class JdbcCustomerDAO extends JdbcDaoSupport implements rule_intf{
    public void insertDao(bm_rider_grade_rule rule) {

    }

    public bm_rider_grade_rule getRule(int rule_id) {

        String sql="select * from bm_rider_grade_rule where id=?";
      //  bm_rider_grade_rule r=getJdbcTemplate().query(sql,new UserRowMapper(),rule_id).get(0);
        bm_rider_grade_rule r=(bm_rider_grade_rule)getJdbcTemplate().query(sql, new Object[]{rule_id}, new BeanPropertyRowMapper(bm_rider_grade_rule.class)).get(0);
        System.out.println(r.rule_name);
        return r;



    }



    public void insertBatch(final List<bm_rider_grade_rule> rules){

        String sql="insert into bm_rider_grade_rule values (?,?,?,?,?)";


        getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                bm_rider_grade_rule rule=rules.get(i);

                preparedStatement.setInt(1,rule.id);
                preparedStatement.setInt(2,rule.org_type);



            }

            public int getBatchSize() {
                return rules.size();
            }
        });





    }




}