springMVC中使用spring JDBC访问数据库

（1）tomcat使用JDNI配置数据源连接数据库

在tomact（9.0+版本）的cong目录下的context.xml加入以下内容，其中driverClassName为对应的数据库驱动，这里是mysql-java驱动，版本用的是8.0版本，和mysql版本级别最好相差不大。

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <Resource name="jndi/test"
              auth="Container"
              type="javax.sql.DataSource"
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/springmvctest?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC"
              username="root"
              password="xxxxxx"
              maxActive="20"
              maxIdle="10"
              maxWait="10000"/>
</Context>

配置spring中的context的上下bean，作用是让jdbc用来寻找连接池（datasource）

 <!--JndiObjectFactoryBean从JNDI中查找DataSource-->
    <bean id="datasource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="java:comp/env/jndi/test"></property>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="datasource" />
    </bean>

（2）JDBC实现数据访问对象(data access object, dao)

服务对象本身并不会处理数据访问，而是将数据访问委托给Repository。 Repository接口确保其与服务对象的松耦合。简单配置一个数据访问接口， Repository接口

public interface SpitterRepository {//spitter是数据库中表所代表的对象
    Spitter save(Spitter spitter);
    Spitter findByUserName(String userName); 
}

JDBC实现数据库访问对象， Repository实现

@Repository
public class JdbcSpitterRepository implements SpitterRepository {

    private final JdbcOperations jdbc;

    private static final String INSERT_SPITTER = "insert into Spitter (username, password, first_name, last_name, email) values (?, ?, ?, ?, ?)";
    private static final String FIND_SPITTER = "select id, username, password, first_name, last_name, email from Spitter where username=?";

    @Autowired
    public JdbcSpitterRepository(JdbcOperations jdbc) {
        this.jdbc = jdbc;
    }

    public Spitter save(Spitter spitter) {
        jdbc.update( //SQL语句insert into 不存在则插入,存在则修改
                INSERT_SPITTER,
                spitter.getUsername(),
                spitter.getPassword(),
                spitter.getFirstName(),
                spitter.getLastName(),
                spitter.getEmail());
        return spitter; // TODO: Determine value for id
    }

    public Spitter findByUserName(String username) {
        return jdbc.queryForObject(
                FIND_SPITTER,
                new SpitterRowMapper(),
                username);
    }
  
    //内部私有类
    private static class SpitterRowMapper implements RowMapper<Spitter> {
        public Spitter mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Spitter(
                    rs.getLong("id"),
                    rs.getString("username"),
                    rs.getString("password"),
                    rs.getString("first_name"),
                    rs.getString("last_name"),
                    rs.getString("email"));
        }
    }

}