MyBatis 3 常用Sql配置

shuaiwei588

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>


未完待续