tk.mybatis相关
https://blog.csdn.net/lyf_ldh/article/details/80976552 通用mapper扩展通用接口和Example 用法
http://codingdict.com/sources/java/org.apache.ibatis.annotations.html org.apache.ibatis.annotations注解大全
数据库timestamp类型
1，插入时sql语句中使用now()或者实体类使用new Date();
2，获取时可用java.util.Date接收或者UNIX_TIMESTAMP(create_time)*1000转成毫秒值
3，`tim` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 则实体类使用@Transient修饰
自增主键
@Options(useGeneratedKeys=true,keyProperty="id",keyColumn="id")
@Insert("insert into tbl_user (name,create_time) values (#{name},#{createTime})")
或者
@SelectKey(keyProperty = "id",keyColumn = "id", before = false, resultType = Integer.class,statement = "select last_insert_id()")
@Options
查询场景：比如useCache = true表示将会缓存本次查询结果，以提高下次查询速度；flushCache = Options.FlushCachePolicy.FALSE表示查询时不刷新缓存；timeout = 10000表示查询结果缓存10000秒。
主键生成策略
@Column(name="id")
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY,generator = "JDBC")
private Integer id;
-AUTO主键由程序控制, 是默认选项 ,不设置就是这个
-IDENTITY 主键由数据库生成, 采用数据库自增长, Oracle不支持这种方式
-SEQUENCE 通过数据库的序列产生主键, MYSQL 不支持
@SequenceGenerator(name = "SEQ", sequenceName = "SEQ_ATTR_ID", allocationSize = 1)
-Table 提供特定的数据库产生主键, 该方式更有利于数据库的移植
扫描类引入的是 tk.mybatis.spring.annotation.MapperScan
import tk.mybatis.spring.annotation.MapperScan;
@Configuration
@MapperScan("cn.com.xmh.ibatisMapper.scanMapper")
public class MybatisConfig {
@Autowired
private RouteDataSource routeDataSource;
@Bean(name="SqlSessionFactory")
@Primary
public SqlSessionFactoryBean sqlSessionFactoryBean(){
https://www.cnblogs.com/xingminghui111/p/13381232.html
}
@Bean(name="transactionManager")
@Primary
public PlatformTransactionManager transactionManager(){
return new DataSourceTransactionManager(routeDataSource);
}
自定义MyBaseMapper不能放在scan扫描包内
import tk.mybatis.mapper.common.Mapper;
public interface MyBaseMapper<T> extends Mapper<T> {
}
@Insert({"<script> ",
"insert into tbl_user (password,name) values ",
"<foreach collection='userList' item='item' index='index' separator=','>" +
"(#{item.password},#{item.name})" +
"</foreach> </script>"})
int insertListss(@Param(value="userList") List<MyUser> user);
@InsertProvider 实现批量插入,ignore
int res = oneMyTestIbatis.insertListMy(lists);
自定义MyBaseMapper不能放在scan扫描包内
import tk.mybatis.mapper.common.Mapper;
public interface MyBaseMapper<T> extends Mapper<T> {
}
@Mapper
public interface OneMyTestIbatis extends MyBaseMapper<MyUser>,InsertOrUpdateMapper<MyUser> {
@RegisterMapper
public interface InsertOrUpdateMapper<T> {
@InsertProvider(type = InertIgnoreProvider.class,method="dynamicSQL")//会动态匹配方法
@InsertProvider(type = InertIgnoreProvider.class,method="insertListMy")
@Options(timeout = 10000)//超时时间
int insertListMy(List<T> var);
}
public class InertIgnoreProvider extends MapperTemplate {
public InertIgnoreProvider(Class<?> mapperClass, MapperHelper mapperHelper) {
super(mapperClass, mapperHelper);
}
public String insertIgnore(MappedStatement ms) {
Class<?> entityClass = this.getEntityClass(ms);
StringBuilder sql = new StringBuilder();
sql.append("INSERT IGNORE INTO ");
sql.append(SqlHelper.getDynamicTableName(entityClass, this.tableName(entityClass)));
sql.append(" ");
sql.append(SqlHelper.insertColumns(entityClass, true, true, this.isNotEmpty()));
sql.append(SqlHelper.insertValuesColumns(entityClass, true, true, this.isNotEmpty()));
EntityHelper.setKeyProperties(EntityHelper.getPKColumns(entityClass), ms);
return sql.toString();
}
public String insertList(MappedStatement ms) {//批量插入
Class<?> entityClass = this.getEntityClass(ms);
StringBuilder sql = new StringBuilder();
sql.append(SqlHelper.insertIntoTable(entityClass, this.tableName(entityClass)));
sql.append(SqlHelper.insertColumns(entityClass, true, false, false));
sql.append(" VALUES ");
sql.append("<foreach collection=\"list\" item=\"record\" separator=\",\" >");
sql.append("<trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">");
Set<EntityColumn> columnList = EntityHelper.getColumns(entityClass);
Iterator var5 = columnList.iterator();
while (var5.hasNext()) {
EntityColumn column = (EntityColumn) var5.next();
if (!column.isId() && column.isInsertable()) {
sql.append(column.getColumnHolder("record") + ",");
// sql.append(SqlHelper.getIfNotNull("record", column, column.getColumnHolder("record") + ",", true));//不为空动态拼接实例 批量插入不适用
System.out.println("InertIgnoreProvider:"+column.getColumnHolder("record"));
}
}
sql.append("</trim>");
sql.append("</foreach>");
System.out.println("InertIgnoreProvider:"+sql.toString());
return sql.toString();
}
}
使用@InsertProvider实现DUPLICATE
public interface MyBaseMapper<T> extends Mapper<T>, InsertOrUpdateMapper<T>{
}
@RegisterMapper
public interface InsertOrUpdateMapper<T> {
@InsertProvider(type = InsertOrUpdateProvider.class,method="dynamicSQL")
int myInsertOrUpdate(T var);
}
public class InsertOrUpdateProvider extends MapperTemplate {
public InsertOrUpdateProvider(Class<?> mapperClass, MapperHelper mapperHelper) {
super(mapperClass, mapperHelper);
}
public String myInsertOrUpdate(MappedStatement ms) {
System.out.println("*******************InsertOrUpdateProvider:"+ms.getTimeout());
Class<?> entityClass = this.getEntityClass(ms);
StringBuilder sql = new StringBuilder();
sql.append("INSERT INTO ");
sql.append(SqlHelper.getDynamicTableName(entityClass, this.tableName(entityClass)));
sql.append(" ");
sql.append(SqlHelper.insertColumns(entityClass, true, true, this.isNotEmpty()));
sql.append(SqlHelper.insertValuesColumns(entityClass, true, true, this.isNotEmpty()));
sql.append("ON DUPLICATE KEY UPDATE ");
sql.append(updateSetColumns(entityClass, (String) null, true, this.isNotEmpty()));
return sql.toString();
}
public static String updateSetColumns(Class<?> entityClass, String entityName, boolean notNull, boolean notEmpty) {
StringBuilder sql = new StringBuilder();
sql.append("<trim suffixOverrides=\",\">");
Set<EntityColumn> columnSet = EntityHelper.getColumns(entityClass);
EntityColumn versionColumn = null;
EntityColumn logicDeleteColumn = null;
Iterator var8 = columnSet.iterator();
while(var8.hasNext()) {
EntityColumn column = (EntityColumn)var8.next();
if(column.getEntityField().isAnnotationPresent(Version.class)) {
if(versionColumn != null) {
throw new VersionException(entityClass.getCanonicalName() + " 中包含多个带有 @Version 注解的字段，一个类中只能存在一个带有 @Version 注解的字段!");
}
versionColumn = column;
}
if(column.getEntityField().isAnnotationPresent(LogicDelete.class)) {
if(logicDeleteColumn != null) {
throw new LogicDeleteException(entityClass.getCanonicalName() + " 中包含多个带有 @LogicDelete 注解的字段，一个类中只能存在一个带有 @LogicDelete 注解的字段!");
}
logicDeleteColumn = column;
}
if(!column.isId() && column.isUpdatable()) {
if(column == versionColumn) {
Version version = (Version)versionColumn.getEntityField().getAnnotation(Version.class);
String versionClass = version.nextVersion().getCanonicalName();
sql.append("<bind name=\"").append(column.getProperty()).append("Version\" value=\"");
sql.append("@tk.mybatis.mapper.version.VersionUtil@nextVersion(").append("@").append(versionClass).append("@class, ");
if(StringUtil.isNotEmpty(entityName)) {
sql.append(entityName).append(".");
}
sql.append(column.getProperty()).append(")\"/>");
sql.append(column.getColumn()).append(" = #{").append(column.getProperty()).append("Version},");
} else if(column == logicDeleteColumn) {
sql.append(SqlHelper.logicDeleteColumnEqualsValue(column, false)).append(",");
} else if(notNull) {
sql.append(SqlHelper.getIfNotNull(entityName, column, column.getColumnEqualsHolder(entityName) + ",", notEmpty));
} else {
sql.append(column.getColumnEqualsHolder(entityName) + ",");
}
}
}
sql.append("</trim>");
return sql.toString();
}
https://www.cnblogs.com/ixixi/p/9561138.html
（2）@InsertProvider的注解方式实现插入
@InsertProvider(type = SqlFactory.class,method = "insertBlog")
int save(@Param("bean")TUser user);
说明：type指明SQL工厂类，method是工厂类里对应的方法
public class SqlFactory {
public String insertBlog(Map<String,Object> para){
TUser blog = (TUser)para.get("bean"); //
SQL sql = new SQL(); //SQL语句对象，所在包：org.apache.ibatis.jdbc.SQL
sql.INSERT_INTO("tbl_user");
if(blog.getName() != null){ //判断blogId属性是否有值
sql.VALUES("name", "'"+blog.getName()+"'");
}
if(blog.getPassword() != null){ //判断blogId属性是否有值
sql.VALUES("password", "'"+blog.getPassword()+"'");
}
System.out.println("********SqlFactory**********"+sql.toString());
return sql.toString();
}
}
批量插入 https://www.cnblogs.com/me168/p/10172422.html
List<TUser> users = new ArrayList<TUser>();
for (int i = 0; i < 100000; i++) {
TUser user = new TUser();
user.setName("nw");
user.setPassword("pp");
users.add(user);
}
System.out.println("insert************before"+new Date());
int ri =myTestIbatis.insertList(users);
//@Mapper
public interface MyTestIbatis extends MyBaseMapper<TUser> {
public interface MyBaseMapper<T> extends Mapper<T>, InsertOrUpdateMapper<T>{
}
动态拼接 INSERT INTO tbl_user <trim prefix="(" suffix=")" suffixOverrides=",">name,create_time,password,</trim> VALUES <foreach collection="list" item="record" separator="," ><trim prefix="(" suffix=")" suffixOverrides=",">#{record.name},#{record.createTime},#{record.password},</trim></foreach>
1. JDBC超时设置
connectTimeout：表示等待和MySQL数据库建立socket链接的超时时间，默认值0，表示不设置超时，单位毫秒，建议30000
socketTimeout：表示客户端和MySQL数据库建立socket后，读写socket时的等待的超时时间，linux系统默认的socketTimeout为30分钟，可以不设置
2. 连接池超时设置
maxWait：表示从数据库连接池取链接，连接池没有可用连接时的等待时间，默认值0，表示无限等待，单位毫秒，建议60000
3. MyBatis查询超时
defaultStatementTimeout ：表示在MyBatis配置文件中默认查询超时间，单位秒，不设置则无线等待
如果一些sql需要执行超过defaultStatementTimeout可以通过Mapper文件单独的sql的timeout进行配置
