百度各有其说,最后参考了官网的mybatis-spring和一些博客,实践了多次,遇到各种坑,终于解决了
pom.xml
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.34</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.3.0</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.3</version>
</dependency>
目录
UserDao.java
package com.example.dao;
import com.example.pojo.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface UserDao {
@Select("select * from user where password = #{pwd}")
List<User> getUser(@Param("pwd") String pwd);
void batchInsert(List<User> list);
}
注意:@select是一种查询方式,另外一种是配置mapper.xml,Dao接口要设置为bean,即加上@Repository注释
User.java
package com.example.pojo;
public class User {
private String userAccount;
private String password;
private int userType;
public void setUserAccount(String userAccount) {
this.userAccount = userAccount;
}
public String getUserAccount() {
return userAccount;
}
public String getPassword() {
return password;
}
public int getUserType() {
return userType;
}
public void setPassword(String password) {
this.password = password;
}
public void setUserType(int userType) {
this.userType = userType;
}
}
UserService.java
package com.example.service;
import com.example.pojo.User;
import java.util.List;
public interface UserService {
List<User> getUser(String pwd);
void batchInsert(List<User> list);
}
UserServiceImpl.java
package com.example.service;
import com.example.dao.UserDao;
import com.example.pojo.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
private SqlSession sqlSession;
public SqlSession getSqlSession() {
return sqlSession;
}
public void setSqlSession(SqlSession sqlSession) {
this.sqlSession = sqlSession;
}
public List<User> getUser(String pwd) {
return userDao.getUser(pwd);
}
public void batchInsert(List<User> list) {
try {
sqlSession.insert("com.example.dao.UserDao.batchInsert",list);
}catch (Exception e){
e.printStackTrace();
sqlSession.rollback();
}
}
public UserDao getUserDao() {
return userDao;
}
public void setUserDao(UserDao userDao) {
this.userDao = userDao;
}
}
注意:UserDao通过@Autowired注释注入,sqlSession通过xml注入,sqlSession.insert的第一个参数对应包名+类名+方法
getUser是通过@select注释查询的,batchInsert是通过mapper.xml配置的
db.xml
<?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:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="initialSize" value="${datasource.initialSize}" />
<property name="maxActive" value="${datasource.maxActive}" />
<property name="maxIdle" value="${datasource.maxIdle}" />
<property name="minIdle" value="${datasource.minIdle}" />
<property name="maxWait" value="${datasource.maxWait}" />
<property name="removeAbandoned" value="${datasource.removeAbandoned}"/>
<property name="removeAbandonedTimeout" value="${datasource.removeAbandonedTimeout}"/>
<property name="logAbandoned" value="${datasource.logAbandoned}"/>
<property name="testOnBorrow" value="${datasource.testOnBorrow}" />
<property name="validationQuery" value="${datasource.validationQuery}"/>
<property name="driverClassName" value="${datasource.driverClassName}" />
<property name="url" value="${datasource.url}" />
<property name="username" value="${datasource.username}" />
<property name="password" value="${datasource.password}" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations" value="classpath:*.xml"/>
</bean>
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg index="0" ref="sqlSessionFactory" />
<constructor-arg index="1" value="BATCH" />
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.example.dao" />
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager" lazy-init="true">
<property name="dataSource" ref="dataSource" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" />
</beans>
注意:sqlSession是处理事务的,MapperScannerConfigurer是用于声明对应包是默认支持事务,详情看官网
service.xml
<?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:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
<!-- web sevice bean -->
<bean id="userService" class="com.example.service.UserServiceImpl">
<property name="dbop" ref="dbop" />
<property name="sqlSession" ref="sqlSession"/>
</bean>
</beans>
approotcxt.xml
<?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:context="http://www.springframework.org/schema/context"
xmlns:p="http://www.springframework.org/schema/p" xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd">
<bean id="casPropertyPlaceholderConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:param.properties</value>
</list>
</property>
</bean>
<import resource="db.xml"/>
<import resource="service.xml"/>
</beans>
param.properties
datasource.type=oracle
datasource.initialSize=1
datasource.maxActive=50
datasource.maxIdle=40
datasource.minIdle=1
datasource.maxWait=20000
datasource.removeAbandoned=true
datasource.removeAbandonedTimeout=10000
datasource.logAbandoned=true
datasource.testOnBorrow=true
datasource.validationQuery=select 1 from dual
datasource.driverClassName=com.mysql.jdbc.Driver
datasource.url=jdbc:mysql://localhost:3306/test
datasource.username=root
datasource.password=xxxx
注意:密码是你的root数据库密码
mapper.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">
<mapper namespace="com.example.dao.UserDao">
<insert id="batchInsert" parameterType="java.util.List">
insert into sc (cno,sno,ccredit,cname,grade) values
<foreach collection="list" item="user" index="index" separator=",">
(#{user.userAccount},#{user.password},#{user.userType},#{user.userAccount},#{user.userAccount})
</foreach>
</insert>
</mapper>
注意:1.list为空会出现异常
You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near ''
2.user对应的属性名要和你声明的一样,大小写一样,要不出现bing,version不对异常
3.CRUD语句的id要对应Dao层的方法名,要不也会有找不到方法的异常
总的来说,配置好pom.xml ,声明Dao层,Service层,Dao类和ServiceImpl类声明为bean,配置db.xml的数据源,session,mapper.xml的路径,再详细配置mapper.xml的内容即可。