MyBatis 3 常用Sql配置

MyBatis 3 常用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">
<!-- namespace必须指向Dao接口 -->
<mapper namespace="com.nawaa.security.persistence.UsersMapper">

	<resultMap id="userRolesResultMap" type="Users">
		<result property="username" column="username" />
		<result property="password" column="password" />
		<collection property="rolesList" ofType="Roles">
			<result property="roleName" column="rolename" />
		</collection>
	</resultMap>

	<select id="selectUserRoles" parameterType="string"
		resultMap="userRolesResultMap">
		SELECT t1.`username`,t1.`password`,t2.`rolename` FROM cms_users t1 LEFT JOIN cms_user_roles t2 ON t1.`username` = t2.`username` WHERE 1 > 0
		AND t1.`username` = #{value}
	</select>
	
</mapper>

UsersMapper

import com.nawaa.security.domain.Users;

public interface UsersMapper {
	Users selectUserRoles(String username);
}

Users

import java.util.ArrayList;
import java.util.List;

public class Users {
	public String username;
	
	public String password;
	
	public List<Roles> rolesList = new ArrayList<Roles>();

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public List<Roles> getRolesList() {
		return rolesList;
	}

	public void setRolesList(List<Roles> rolesList) {
		this.rolesList = rolesList;
	}

}

Spring3配置

<?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:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:jpa="http://www.springframework.org/schema/data/jpa"
	xsi:schemaLocation="
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd
		http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.1.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
		http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa.xsd"
	default-lazy-init="true">

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

	<!-- enable autowire -->
	<context:annotation-config />
	<tx:annotation-driven />
	<context:component-scan base-package="com.nawaa.security.service,com.nawaa.nhcms.service" />

	<context:property-placeholder
		ignore-resource-not-found="true" location="classpath*:/application.properties" />

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
		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="${dbcp.maxActive}" />
		<property name="maxIdle" value="${dbcp.maxIdle}" />
		<property name="defaultAutoCommit" value="false" />
		<!-- 连接Idle一个小时后超时 -->
		<property name="timeBetweenEvictionRunsMillis" value="3600000" />
		<property name="minEvictableIdleTimeMillis" value="3600000" />
	</bean>

	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="typeAliasesPackage" value="com.nawaa.security.domain,com.nawaa.nhcms.domain" />
	</bean>

	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage" value="com.nawaa.security.persistence, com.nawaa.nhcms.persistence" />
	</bean>

	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource" />
	</bean>

	<bean id="bootstrapDataPopulator" class="com.nawaa.BootstrapDataPopulator">
		<property name="dataSource" ref="dataSource" />
	</bean>
</beans>

MyBatis3 操作Mysql二进制数据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">
<!-- namespace必须指向Dao接口 -->
<mapper namespace="com.nawaa.cms.persistence.BinaryDataMapper">
	<resultMap id="binaryResultMap" type="BinaryData">
		<result property="id" column="id" />
		<result property="content" column="content" javaType="byte[]" typeHandler="org.apache.ibatis.type.ByteArrayTypeHandler" />
	</resultMap>

	<select id="select" parameterType="string" resultMap="binaryResultMap">
	<![CDATA[
		SELECT id, site, type, linkid, path, content, createtime FROM cms_binarydata WHERE linkid = #{value}
	]]>

	<insert id="insert">
	<![CDATA[
		INSERT INTO cms_binarydata(id, site, type, linkid, path, content, createtime)
		VALUES(#{id}, #{site}, #{type}, #{linkid}, #{path},  #{content, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis,type.ByteArrayTypeHandler}, #{createtime});
	]]>
	</insert>
	</select>
</mapper>

未完待续