Spring JdbcTemplate常用操作

JdbcTemplate主要提供以下五类方法：

• execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
• update方法：update方法用于执行新增、修改、删除等语句；
• batchUpdate方法：batchUpdate方法用于执行批处理相关语句；
• query方法及queryForXXX方法：用于执行查询相关语句；
• call方法：用于执行存储过程、函数相关语句。

import cn.sivan.pojo.Account;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.lang.annotation.ElementType;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
public class AccountTest {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Test
    public void Account_t1() {

        //查询 返回Map<String, Object>
        String sql = "select * from account where id = ?";
        Map<String, Object> map = jdbcTemplate.queryForMap(sql, 1);
        System.out.println(map);

        //查询 返回listList<Map<String, Object>>
        sql = "select * from account where id < ?";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, 100);
        System.out.println(maps);

        //查询 返回第一行第一列
        sql = "select balance from account where id = ?";
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class, 1);
        System.out.println(integer);

        //查询 返回对象
        sql = "select * from account where name = ?";
        Account account = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Account.class), "sivan");
        System.out.println(account);

        //查询 返回List<Bean>
        sql = "select * from account";
        List<Account> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Account.class));
        query.forEach(item -> System.out.println(item));

        //执行sql insert update delete
        sql = "update account set balance = balance + ? where id = ?";
        int update = jdbcTemplate.update(sql, 100, 1);
        System.out.println(update);

        //批量更新 rewriteBatchedStatements=true
        sql = "insert into account(name,balance) values (?,?)";

        ArrayList<Object[]> batchArgs = new ArrayList<>();
        for (int i = 0; i < 1000; i++) {
            batchArgs.add(new Object[]{"name", 100});
        }
        jdbcTemplate.batchUpdate(sql, batchArgs);
    }

}