SpringMVC与底层数据库的交互

mybatis配置文件
//userMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace必须指向Dao接口 -->
<mapper namespace="com.jikexueyuan.demo.springmvc.lesson6.dao.ISysUserDao">

    <cache eviction="FIFO" flushInterval="60000" size="500" readOnly="true"></cache>

    <insert id="save" parameterType="SysUser" useGeneratedKeys="true" keyProperty="uId" flushCache="true">
        insert into sys_user(uName, uAge) values(#{uName}, #{uAge})
    </insert>

    <select id="selectById" parameterType="int" resultType="SysUser" useCache="false">
        select * from sys_user where uId=#{uId}
    </select>

    <delete id="deleteById" parameterType="int" flushCache="true">
       DELETE from sys_user where uId=#{uId}
    </delete>

    <select id="selectAll" resultType="SysUser">
        select * from sys_user
    </select>

</mapper> 
//config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <settings>
        <setting name="cacheEnabled" value="true" />
        <setting name="lazyLoadingEnabled" value="false" />
        <setting name="useColumnLabel" value="true" />
        <setting name="useGeneratedKeys" value="true" />
        <setting name="defaultExecutorType" value="SIMPLE" />
        <setting name="localCacheScope" value="STATEMENT"/>
    </settings>

</configuration>

SpringMVC配置文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:util="http://www.springframework.org/schema/util" xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:task="http://www.springframework.org/schema/task"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.0.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd 
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd 
        http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.0.xsd">

    <description>Spring公共配置 </description>

    <!-- 使用annotation 自动注册bean, 并保证@Required、@Autowired的属性被注入 -->
    <context:component-scan base-package="com.jikexueyuan" use-default-filters="true">
        <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" />
    </context:component-scan>

    <!-- 开启定时任务 -->
    <task:annotation-driven/>

    <!-- MyBatis配置 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <!-- 自动扫描entity目录, 省掉Configuration.xml里的手工配置 -->
        <property name="typeAliasesPackage" value="com.jikexueyuan.demo.springmvc.lesson6.entity;" /> <!-- 多个路径用分号隔开 -->
        <!-- 显式指定Mapper文件位置 -->
        <property name="mapperLocations" value="classpath*:/mybatis/*Mapper.xml" />
        <property name="configLocation" value="classpath:/mybatis/config.xml"/>
    </bean>

    <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
       <constructor-arg index="0" ref="sqlSessionFactory" />
       <constructor-arg index="1" value="BATCH" />
    </bean>

    <!-- 扫描basePackage接口 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.jikexueyuan.demo.springmvc.lesson6.dao" />
    </bean>

    <!-- 使用annotation定义事务 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" />

    <!-- 数据源配置, 使用Tomcat JDBC连接池 -->
    <bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
        <!-- Connection Info -->
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />

        <!-- Connection Pooling Info -->
        <property name="maxActive" value="${jdbc.pool.maxActive}" />
        <property name="maxIdle" value="${jdbc.pool.maxIdle}" />
        <property name="minIdle" value="0" />
        <property name="defaultAutoCommit" value="false" />
    </bean>

    <!-- 配置jdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!-- production环境 -->
    <beans profile="production">
        <context:property-placeholder ignore-unresolvable="true" file-encoding="utf-8" 
            location="classpath:config.properties,classpath:jdbc.properties" />
    </beans>

</beans>

//controller
package com.jikexueyuan.demo.springmvc.lesson6.controller;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import com.jikexueyuan.demo.springmvc.lesson6.entity.SysUser;
import com.jikexueyuan.demo.springmvc.lesson6.service.ISysUserService;

@Controller
public class SysUserController {

    @Resource
    ISysUserService service;

    @RequestMapping("/jdbc/all")
    public void selectAll(){
        List<SysUser> userList = service.selectAllWithJDBC();
        for (SysUser sysUser : userList) {
            System.out.println(sysUser.toString());
        }
    }

    @RequestMapping("/jdbc/select/{id}")
    public void select(@PathVariable Integer id){
        SysUser user = service.selectByIdWithJDBC(id);
        if (user != null) {
            System.out.println(user.toString());
        }else {
            System.out.println("not found ");
        }
    }   

    @RequestMapping("/jdbc/delete/{id}")
    public void delete(@PathVariable Integer id){
        service.deleteByIdWithJDBC(id);
        System.out.println("jdbc delete success");
    }   

    @RequestMapping("/jdbc/save")
    public void save(@RequestParam String uName , @RequestParam Integer uAge ){
        service.saveWithJDBC(uName, uAge);
        System.out.println("jdbc save success");
    }   

    // -------------------------

    @RequestMapping("/mybatis/all")
    public void mybatisselectAll(){
        List<SysUser> userList = service.selectAllWithMybatis();
        for (SysUser sysUser : userList) {
            System.out.println(sysUser.toString());
        }
    }

    @RequestMapping("/mybatis/select/{id}")
    public void mybatisselect(@PathVariable Integer id){
        SysUser user = service.selectByIdWithMybatis(id);
        if (user != null) {
            System.out.println(user.toString());
        }else {
            System.out.println("not found ");
        }
    }   

    @RequestMapping("/mybatis/delete/{id}")
    public void mybatisdelete(@PathVariable Integer id){
        service.deleteByIdWithMybatis(id);
        System.out.println("jdbc delete success");
    }   

    @RequestMapping("/mybatis/save")
    public void mybatissave(@RequestParam String uName , @RequestParam Integer uAge ){
        service.saveWithMybatis(uName, uAge);
        System.out.println("jdbc save success");
    }   

}

//dao
package com.jikexueyuan.demo.springmvc.lesson6.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.jikexueyuan.demo.springmvc.lesson6.entity.SysUser;

@Repository
public interface ISysUserDao {

    public void save(SysUser user);
    public SysUser selectById(int id );
    public void deleteById(int id);
    public List<SysUser> selectAll();

}

//entity
package com.jikexueyuan.demo.springmvc.lesson6.entity;

public class SysUser {

    private int uId ;
    private String uName ;
    private int uAge ;
    public int getuId() {
        return uId;
    }
    public void setuId(int uId) {
        this.uId = uId;
    }
    public String getuName() {
        return uName;
    }
    public void setuName(String uName) {
        this.uName = uName;
    }
    public int getuAge() {
        return uAge;
    }
    public void setuAge(int uAge) {
        this.uAge = uAge;
    }

    @Override
    public String toString() {
        return "uId=" + uId + ", uName=" + uName + ", uAge=" + uAge;
    }

}

//service
package com.jikexueyuan.demo.springmvc.lesson6.service;

import java.util.List;

import com.jikexueyuan.demo.springmvc.lesson6.entity.SysUser;

public interface ISysUserService {

    public void saveWithJDBC(String uName, int uage );
    public SysUser selectByIdWithJDBC(int uId );
    public List<SysUser> selectAllWithJDBC();
    public void deleteByIdWithJDBC(int uId );

    public void saveWithMybatis(String uName, int uage );
    public SysUser selectByIdWithMybatis(int uId );
    public List<SysUser> selectAllWithMybatis();
    public void deleteByIdWithMybatis(int uId );

}

//serviceimpl
package com.jikexueyuan.demo.springmvc.lesson6.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.jikexueyuan.demo.springmvc.lesson6.dao.ISysUserDao;
import com.jikexueyuan.demo.springmvc.lesson6.entity.SysUser;
import com.jikexueyuan.demo.springmvc.lesson6.service.ISysUserService;
import com.mysql.jdbc.Statement;

@Service
public class SysUserServiceImpl implements ISysUserService {

    @Resource
    ISysUserDao dao ;

    @Resource
    JdbcTemplate jdbcTemplate;

    @Transactional
    public void saveWithJDBC(final String uName, final int uAge) {
        final String sql = "insert into sys_user(uName, uAge) values(?,?)";
        // jdbcTemplate.update(sql, new Object[]{uName, uAge});

        KeyHolder key = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            public PreparedStatement createPreparedStatement(Connection con)
                    throws SQLException {
                PreparedStatement statement = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                statement.setString(1, uName );
                statement.setInt(2, uAge);
                return statement;
            }
        }, key);

        System.out.println(key.getKey().intValue());

    }

    public SysUser selectByIdWithJDBC(int uId) {
        String sql = "select * from sys_user where uId=?";
        final SysUser user = new SysUser();
        jdbcTemplate.query(sql, new Object[]{uId}, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
                user.setuName(rs.getString("uName"));
                user.setuAge(rs.getInt("uAge"));
            }
        });
        user.setuId(uId);
        return user;
    }

    public List<SysUser> selectAllWithJDBC() {
        String sql = "SELECT * from sys_user";
        final List<SysUser> userList = new ArrayList<SysUser>();
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                SysUser user = new SysUser();
                user.setuName(rs.getString("uName"));
                user.setuAge(rs.getInt("uAge"));
                user.setuId(rs.getInt("uId"));
                userList.add(user);
            }
        });
        return userList;
    }

    @Transactional
    public void deleteByIdWithJDBC(int uId) {
        String sql = "DELETE from sys_user where uId=?";
        jdbcTemplate.update(sql, uId);
    }

    @Transactional
    public void saveWithMybatis(String uName, int uAge) {
        SysUser user = new SysUser();
        user.setuName(uName);
        user.setuAge(uAge);
        dao.save(user);
    }

    public SysUser selectByIdWithMybatis(int uId) {
        return dao.selectById(uId);
    }

    public List<SysUser> selectAllWithMybatis() {
        return dao.selectAll();
    }

    @Transactional
    public void deleteByIdWithMybatis(int uId) {
        dao.deleteById(uId);
    }



}