3、spring 加mybatis配置

目录

 

一、加入依赖和配置

二、加入测试目标

三、测试

---

一、加入依赖和配置

创建目录如下

dal.pom增加依赖

  <dependency>
            <groupId>com.ouer.hetao</groupId>
            <artifactId>model</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
        </dependency>

在spring.xml增加数据库配置

jdbc:
  data:
    url: jdbc:mysql://10.8.85.4:3306/sampledb?serverTimezone=UTC
    username: root
    password:

数据库时间转换类

@Alias("dateTypeHandler")
public class DateTypeHandler extends BaseTypeHandler<Date> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Date parameter,
                                    JdbcType jdbcType) throws SQLException {
        ps.setString(i, date2String(parameter));
    }

    @Override
    public Date getNullableResult(ResultSet rs, String columnName) throws SQLException {
        Timestamp type = rs.getTimestamp(columnName);
        return new Date(type.getTime());
    }

    @Override
    public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        Timestamp type = rs.getTimestamp(columnIndex);
        return new Date(type.getTime());
    }

    @Override
    public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        Timestamp type = cs.getTimestamp(columnIndex);
        return new Date(type.getTime());
    }

    public static String date2String(Date date) {
        return new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS")
                .format(date);
    }
}

扫描基类

public class MapperScanner {
}

数据库配置文件

@MapperScan(basePackageClasses = {MapperScanner.class})
public class DalConfig {

    public static final String MAPPING_LOCATION = "classpath:/mapper/*.xml";

    @Value("${jdbc.data.url}")
    private String jdbc_data_url;
    @Value("${jdbc.data.username}")
    private String jdbc_data_user;
    @Value("${jdbc.data.password}")
    private String jdbc_data_password;

    @Bean
    public DataSourceTransactionManager transactionManager() throws SQLException {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager();
        transactionManager.setDataSource(dataSource());
        return transactionManager;
    }


    protected DruidDataSource dataSource(String url, String userName, String password) throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);

        dataSource.setFilters("config,stat");
//        dataSource.setConnectionProperties("config.decrypt=true");
        Collection<String> list = new ArrayList<>();
        list.add("set names utf8mb4;");
        dataSource.setConnectionInitSqls(list);

        dataSource.setInitialSize(2);
        dataSource.setMinIdle(2);
        dataSource.setMaxActive(100);
        // 连接等待超时时间
        dataSource.setMaxWait(60000);
        // 连接超时关闭时间
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        // 连接最小生存时间
        dataSource.setMinEvictableIdleTimeMillis(300000);
        dataSource.setValidationQuery("select 1");
        dataSource.setTestWhileIdle(true);
        dataSource.setTestOnBorrow(false);
        dataSource.setTestOnReturn(false);
        dataSource.setRemoveAbandoned(true);
        dataSource.setRemoveAbandonedTimeout(600);
        dataSource.setLogAbandoned(true);
        return dataSource;
    }

    @Bean
    public DataSource dataSource() throws SQLException{
        return dataSource(jdbc_data_url,jdbc_data_user,jdbc_data_password);
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource());
        Resource[] mapperLocations = getMapperLocations();
        sessionFactoryBean.setMapperLocations(mapperLocations);
        sessionFactoryBean.setTypeAliases(new Class<?>[]{DateTypeHandler.class});
        return sessionFactoryBean.getObject();
    }

    protected Resource[] getMapperLocations() throws IOException {
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        return resolver.getResources(MAPPING_LOCATION);
    }

}

二、加入测试目标

数据库建表

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `age` int(8) DEFAULT NULL,
  `sex` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

UserMapper.java映射类

public interface UserMapper {

    List<User> getAll();
    User getOne(Long id);
    void insert(User user);
    void update(User user);
    void delete(Long id);

}

UserMapper.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="com.ouer.hetao.dal.mapper.UserMapper" >
    <resultMap id="UserMap" type="com.ouer.hetao.model.User" >
        <id column="id" property="id"/>
        <result column="name" property="name" />
        <result column="age" property="age" />
        <result column="sex" property="sex"/>
    </resultMap>

    <sql id="Base_Column_List" >
        id, name, age, sex
    </sql>

    <select id="getAll" resultMap="UserMap"  >
        SELECT
        <include refid="Base_Column_List" />
        FROM users
    </select>

    <select id="getOne" parameterType="java.lang.Long" resultMap="UserMap" >
        SELECT
        <include refid="Base_Column_List" />
        FROM users
        WHERE id = #{id}
    </select>

    <insert id="insert" parameterType="com.ouer.hetao.model.User" >
        INSERT INTO
        users
        (name, age, sex)
        VALUES
        (#{name}, #{age}, #{sex})
    </insert>

    <update id="update" parameterType="com.ouer.hetao.model.User" >
        UPDATE users
        <set>
            <if test="name != null">name = #{name},</if>
            <if test="age != null">age = #{age},</if>
            <if test="sex != null">age = #{sex},</if>
        </set>
        WHERE id = #{id}
    </update>

    <delete id="delete" parameterType="java.lang.Long" >
        DELETE FROM
        users
        WHERE
        id =#{id}
    </delete>
</mapper>

三、测试

controller注入UserMapper类，表操作

@RestController
public class DemoController {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    @Value("${data.name}")
    private String name;

    @Autowired
    private UserMapper userMapper;

    @RequestMapping("/")
    public String index() {
        return "hello world";
    }


    @RequestMapping("/getUser")
    public List<User> getUser() {
        return userMapper.getAll();
    }

    @RequestMapping("/addUser")
    public User addUser() {
        User user = new User();
        user.setAge(10);
        user.setName(name);
        user.setSex(UserSexEnum.WOMAN.getCode());
        userMapper.insert(user);
        return user;
    }


}

 

启动项目

结果如下：