Spring集成JDBC

package com.xuanyuv.model;

public class Person {
    private Integer id;
    private String name;
    private Integer age;

    /*-- 三个属性的setter和getter略 --*/

    //不要忘记默认的无参构造方法
    public Person(){}

    public Person(String name, Integer age) {
        this.name = name;
        this.age = age;
    }
}

package com.xuanyuv.service;
import java.util.List;
import com.xuanyuv.model.Person;

public interface PersonService {
    public long save(Person person);
    public void delete(Integer personid);
    public void update(Person person);
    public Person getPersonById(Integer personid);
    public List<Person> getPersons();
}

package com.xuanyuv.service.impl;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import com.xuanyuv.model.Person;
import com.xuanyuv.service.PersonService;

/**
 * ----------------------------------------------------------------------------------------------------
 * SpringJDBC提供了自增键以及行集的支持，自增键对象让我们可以不依赖数据库的自增键，在应用层为新记录提供主键值
 * org.springframework.jdbc.support.incrementer.DataFieldMaxValueIncrementer接口提供两种产生主键的方案
 * 1、通过序列产生主键：比较有代表性的就是本文用到的OracleSequenceMaxValueIncrementer
 * 2、通过表产生主键：比较有代表性的就是MySQLMaxValueIncrementer,具体用法请自行Google
 * ----------------------------------------------------------------------------------------------------
 * 根据主键产生方式和数据库的不同，Spring提供了若干实现类
 * 在其抽象实现类AbstractDataFieldMaxValueIncrementer中有几个重要的属性
 * 其中incrementerName是针对第一种方案（序列）时使用的，columnName和cacheSize是针对第二种方案（表）时使用的
 * 1、incrementerName--用于指定序列或主键表的名称
 * 2、columnName-------用于指定主键列的名字
 * 3、cacheSize--------用于指定缓存的主键个数
 * ----------------------------------------------------------------------------------------------------
 * Created by 玄玉<https://www.xuanyuv.com/> on 2011/02/20 18:48.
 */
@Transactional
public class PersonServiceImpl implements PersonService {
    //private DataSource dataSource;   //不建议直接对DataSource进行操作
    private JdbcTemplate jdbcTemplate; //而是借助JdbcTemplate
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    private OracleSequenceMaxValueIncrementer sequence12;
    public OracleSequenceMaxValueIncrementer getSequence12() {
        return sequence12;
    }
    public void setSequence12(OracleSequenceMaxValueIncrementer sequence12) {
        this.sequence12 = sequence12;
    }

    public long save(Person person) {
        long ID = this.sequence12.nextLongValue();
        String sql = "insert into person values(" + ID + ", ?, ?)";
        Object[] params = new Object[]{person.getName(), person.getAge()};
        this.jdbcTemplate.update(sql, params);
        return ID;
    }

    //设置当该方法遇到RuntimeException时，不会回滚
    @Transactional(noRollbackFor=RuntimeException.class)
    public void delete(Integer personid) {
        this.jdbcTemplate.update("delete from person where id=?", new Object[]{personid});
        //为了测试noRollbackFor属性，故添加此行代码
        throw new RuntimeException("运行期例外");
    }

    public void update(Person person) {
        String sql = "update person set name=? where id=?";
        Object[] params = new Object[]{person.getName(), person.getId()};
        this.jdbcTemplate.update(sql, params);
    }

    @Transactional(propagation=Propagation.NOT_SUPPORTED)
    public Person getPersonById(Integer personId) {
        String sql = "select * from person where id=?";
        return (Person)this.jdbcTemplate.queryForObject(sql, new Object[]{personId}, new PersonRowMapper());
    }

    @Transactional(propagation=Propagation.NOT_SUPPORTED)
    public String getPersonNameByAge(Integer age) {
        String sql = "SELECT name FROM person WHERE age = ?";
        try{
            return (String)this.jdbcTemplate.queryForObject(sql, new Object[]{age}, String.class);
        }catch(EmptyResultDataAccessException e){
            //查到空记录时，会报告此异常
            return "查无此人";
        }
    }

    @Transactional(propagation=Propagation.NOT_SUPPORTED)
    public List<Person> getPersons() {
        //当查询到该条记录时，它会调用第三个对象的回调方法
        return (List<Person>)this.jdbcTemplate.query("select * from person", new PersonRowMapper());
    }
}

package com.xuanyuv.service.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import com.xuanyuv.model.Person;

public class PersonRowMapper implements RowMapper {
    public Object mapRow(ResultSet rs, int index) throws SQLException {
        //当外部调用该方法时，已经做了一步if(rs.next())操作了，所以这里我们不用再rs.next()
        Person person = new Person(rs.getString("name"), rs.getInt("age"));
        //注意：该句不可少
        //否则personService.getPersonById(2)获取到的Person对象中的id值将是空的
        person.setId(rs.getInt("id"));
        //我们的目的是要让返回的person对象的各个属性，都不是虚的，都是确有其值的
        return person;
    }
}

package com.xuanyuv.junit;
import org.junit.BeforeClass;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.xuanyuv.model.Person;
import com.xuanyuv.service.PersonService;

public class PersonServiceTest {
    private static PersonService personService;

    @BeforeClass
    public static void setUpBeforeClass() throws Exception {
        try {
            ApplicationContext cxt = new ClassPathXmlApplicationContext("beans.xml");
            personService = (PersonService) cxt.getBean("personServiceImpl");
        } catch (RuntimeException e) {
            //若出错，则打印提示信息到控制台（否则它是不会打印到控制台上的）
            System.out.println("服务载入失败，堆栈轨迹如下：");
            e.printStackTrace();
        }
    }

    @Test
    public void save(){
        personService.save(new Person("沈浪", 24));
        personService.save(new Person("王怜花", 25));
    }

    @Test
    public void delete(){
        personService.delete(2);
    }

    @Test
    public void update(){
        Person person = personService.getPersonById(3);
        person.setName("金无望");
        personService.update(person);
    }

    @Test
    public void getPersonById(){
        Person person = personService.getPersonById(3);
        System.out.println("编号:" + person.getId() + "/t姓名:" + person.getName());
    }

    @Test
    public void getPersons(){
        for(Person person : personService.getPersons()){
            System.out.println("编号:" + person.getId() + "/t姓名:" + person.getName());
        }
    }
}

public int insert(String userID, String keyword){
    SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(this.jdbcTemplate);
    jdbcInsert.withTableName("t_keyword");                //指定插入的表
    jdbcInsert.usingColumns("userId", "keyword", "type"); //指定插入哪些字段
    jdbcInsert.usingGeneratedKeyColumns("id");            //指定欲插入记录的主键
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("userId", userID);
    params.put("keyword", keyword);
    params.put("type", "vote");
    return jdbcInsert.executeAndReturnKey(params).intValue();
}