mybatis和Spring整合操作数据库

一、POM文件引jar

 <dependencies>
            <dependency>
                <groupId>com.sankuai</groupId>
                <artifactId>inf-bom</artifactId>
                <version>1.3.9.1</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>

          <!--mybatis-->
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.3.0</version>
            </dependency>
            <!--mybatis spring整合-->
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis-spring</artifactId>
                <version>1.2.3</version>
            </dependency>

            <!--mysql driver-->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.6</version>
            </dependency>

            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>4.3.3.RELEASE</version>
            </dependency>

            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-test</artifactId>
                <version>4.3.3.RELEASE</version>
            </dependency>
 </dependencies>

二、基础配置xml

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:task="http://www.springframework.org/schema/task"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/task
        http://www.springframework.org/schema/task/spring-task-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/mvc  http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">

    <!--可选，可从db.properties中获取数据库信息-->
   <!--<context:property-placeholder  location="classpath:db.properties" />-->

    <!-- 配置Mysql的连接和DataSource -->
    <bean id="DataSourceAuthorize" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName">
            <value>com.mysql.jdbc.Driver</value>
        </property>
        <property name="url">
            <value>jdbc:mysql://xx.xx.xx.xx:5002/数据库名称?useUnicode=true&amp;characterEncoding=UTF-8</value>
        </property>
        <property name="username">
            <value>用户名</value>
        </property>
        <property name="password">
            <value>密码</value>  
        </property>
    </bean>
    <!-- 配置sqlSessionFactory -->
    <bean id="sqlSessionFactoryAuthorize" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="DataSourceAuthorize"/>
        <!-- 配置扫描Mapper XML的位置,使用xml注入SQL语句时才需要下面这个 -->
       <!-- <property name="mapperLocations" value="classpath:mapper/*.xml"/>-->

        <property name="configLocation" value="classpath:mybatis-config.xml"/>
    </bean>

    <!-- 遍历所有mapper里的接口并配置 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--<property name="annotationClass" value="org.springframework.stereotype.Repository" />-->
        <property name="basePackage" value="mapper文件的路径" />
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactoryAuthorize"/>
    </bean>
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="DataSourceAuthorize" />
    </bean>

    <bean id="growthBizDao" class="DAO路径"></bean>

</beans>

三、配置mybatis-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>

    <properties>
        <property name="dialect" value="mysql" />
    </properties>
    <settings>
        <!--为true使myBatis可以将下划线分隔风格的字段名映射到驼峰表示法的对象域名上-->
        <setting name="mapUnderscoreToCamelCase" value="true" />
    </settings>

    <!--TODO-->
    <typeAliases>
        <!--<typeAlias alias="Account" type="com.sankuai.meituan.bean.Account"/>-->
    </typeAliases>

</configuration>

四、写mapper

@Repository
public interface UserMapper {

   //方式一：使用注解写SQL
    @Insert({"INSERT INTO t_biz_code(biz_line_id, biz_code_name) VALUES (#{0}, #{1})"})
    void addBiz(@Param("biz_line_id")int id, @Param("biz_code_name")String  name);


     //方式二：使用xml写SQL
     String seleteBiz(@Param("arg0")int id, @Param("arg1")String  name);
}

4.1、SQL语句用mybatis注解的方式写（方式一）

4.2、SQL 语句用xml的方式注入（方式二）

需要新增一个xml文件用于配置SQL语句

<?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">
<mapper namespace="mapper的路径">

    <select id="seleteBiz（和mapper的方法名称对应）" parameterType="Long" resultType="String">
        Select 查询字段 from 表名 WHERE id=#{0} and name=#{1}
    </select>

 

</mapper>

 

五、写DAO层

因为外部无法直接调用mapper

@Service
public class GrowthBizDao {

    @Resource
    private UserMapper userMapper;

   
   public void addBiz(int biz_line_id, String  biz_code_name){

     userMapper.addBiz(biz_line_id,biz_code_name);
   }

   public void deleteBiz(String  biz_code_name){
       userMapper.deleteBiz(biz_code_name);
   }

六、外部调用

 

问题：org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'biz_line_id' not found. Available parameters are [0, 1, param1, param2]

解决：

1、入参换成map类型

2、入参用#{0}、#{1}

别名的方法试过，我这里不行（xml和注解都不行），至今没查到是什么原因....（有知道的朋友可以告知）

解决方法参照：https://www.jianshu.com/p/d977eaadd1ed