SpringMVC与底层数据库的交互

来源：互联网发布：办公文件整理软件编辑：程序博客网时间：2024/06/04 19:20

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>

//controllerpackage 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;@Controllerpublic 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");    }   }

//daopackage com.jikexueyuan.demo.springmvc.lesson6.dao;import java.util.List;import org.springframework.stereotype.Repository;import com.jikexueyuan.demo.springmvc.lesson6.entity.SysUser;@Repositorypublic interface ISysUserDao {    public void save(SysUser user);    public SysUser selectById(int id );    public void deleteById(int id);    public List<SysUser> selectAll();}

//entitypackage 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;    }}

//servicepackage 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 );}

//serviceimplpackage 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;@Servicepublic 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);    }}

阅读全文

1 0