parameterMappings = boundSql.getParameterMappings();
if (!parameterMappings.isEmpty() && parameterObject != null) {
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
list.add(parameterObject);
} else {
MetaObject metaObject = configuration.newMetaObject(parameterObject);
for (ParameterMapping parameterMapping : parameterMappings) {
String propertyName = parameterMapping.getProperty();
if (metaObject.hasGetter(propertyName)) {
Object obj = metaObject.getValue(propertyName);
list.add(obj);
} else if (boundSql.hasAdditionalParameter(propertyName)) {
Object obj = boundSql.getAdditionalParameter(propertyName);
list.add(obj);
} else {
list.add("缺失");
}//打印出缺失，提醒该参数缺失并防止错位
}
}
}
return list.toArray();
}
/**
* Parser for clickhouse JDBC SQL Strings
*
* Tries to extract query parameters in a way that is usable for (batched)
* prepared statements.
*/
public final static class PreparedStatementParser {
private static final Pattern VALUES = Pattern.compile("(?i)INSERT\\s+INTO\\s+.+VALUES\\s*\\(");
private static final String PARAM_MARKER = "?";
private final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
private final SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private final List> parameters;
private final List parts;
private boolean valuesMode;
private PreparedStatementParser() {
parameters = new ArrayList>();
parts = new ArrayList();
valuesMode = false;
initTimeZone(TimeZone.getDefault());
}
public static PreparedStatementParser parse(String sql) {
if (sql.isBlank()) {
throw new IllegalArgumentException("SQL may not be blank");
}
PreparedStatementParser parser = new PreparedStatementParser();
parser.parseSQL(sql);
return parser;
}
private static String typeTransformParameterValue(String paramValue) {
if (paramValue == null) {
return null;
}
if (Boolean.TRUE.toString().equalsIgnoreCase(paramValue)) {
return "1";
}
if (Boolean.FALSE.toString().equalsIgnoreCase(paramValue)) {
return "0";
}
if ("NULL".equalsIgnoreCase(paramValue)) {
return "\\N";
}
return paramValue;
}
private void initTimeZone(TimeZone timeZone) {
dateTimeFormat.setTimeZone(timeZone);
dateFormat.setTimeZone(timeZone);
}
private void reset() {
parameters.clear();
parts.clear();
valuesMode = false;
}
private void parseSQL(String sql) {
reset();
List currentParamList = new ArrayList();
boolean afterBackSlash = false;
boolean inQuotes = false;
boolean inBackQuotes = false;
boolean inSingleLineComment = false;
boolean inMultiLineComment = false;
boolean whiteSpace = false;
Matcher matcher = VALUES.matcher(sql);
if (matcher.find()) {
valuesMode = true;
}
int currentParensLevel = 0;
int quotedStart = 0;
int partStart = 0;
for (int i = valuesMode ? matcher.end() - 1 : 0, idxStart = i, idxEnd = i; i < sql.length(); i++) {
char c = sql.charAt(i);
//注释
if (inSingleLineComment) {
if (c == '\n') {
inSingleLineComment = false;
}
} else if (inMultiLineComment) {
if (c == '*' && sql.length() > i + 1 && sql.charAt(i + 1) == '/') {
inMultiLineComment = false;
i++;
}
//反斜杠
} else if (afterBackSlash) {
afterBackSlash = false;
} else if (c == '\\') {
afterBackSlash = true;
//单引号
} else if (c == '\'') {
inQuotes = !inQuotes;
if (inQuotes) {
quotedStart = i;
} else if (!afterBackSlash) {
idxStart = quotedStart;
idxEnd = i + 1;
}
//反引号
} else if (c == '`') {
inBackQuotes = !inBackQuotes;
//不在单引号中
} else if (!inQuotes && !inBackQuotes) {
if (c == '?') {
if (currentParensLevel > 0) {
idxStart = i;
idxEnd = i + 1;
}
if (!valuesMode) {
parts.add(sql.substring(partStart, i));
partStart = i + 1;
currentParamList.add(PARAM_MARKER);
}
//单行注释
} else if (c == '-' && sql.length() > i + 1 && sql.charAt(i + 1) == '-') {
inSingleLineComment = true;
i++;
//多行注释
} else if (c == '/' && sql.length() > i + 1 && sql.charAt(i + 1) == '*') {
inMultiLineComment = true;
i++;
} else if (c == ',') {
if (valuesMode && idxEnd > idxStart) {
currentParamList.add(typeTransformParameterValue(sql.substring(idxStart, idxEnd)));
parts.add(sql.substring(partStart, idxStart));
partStart = idxStart = idxEnd = i;
}
idxStart++;
idxEnd++;
} else if (c == '(') {
currentParensLevel++;
idxStart++;
idxEnd++;
} else if (c == ')') {
currentParensLevel--;
if (valuesMode && currentParensLevel == 0) {
if (idxEnd > idxStart) {
currentParamList.add(typeTransformParameterValue(sql.substring(idxStart, idxEnd)));
parts.add(sql.substring(partStart, idxStart));
partStart = idxStart = idxEnd = i;
}
if (!currentParamList.isEmpty()) {
parameters.add(currentParamList);
currentParamList = new ArrayList(currentParamList.size());
}
}
} else if (Character.isWhitespace(c)) {
whiteSpace = true;
} else if (currentParensLevel > 0) {
if (whiteSpace) {
idxStart = i;
idxEnd = i + 1;
} else {
idxEnd++;
}
whiteSpace = false;
}
}
}
if (!valuesMode && !currentParamList.isEmpty()) {
parameters.add(currentParamList);
}
String lastPart = sql.substring(partStart);
parts.add(lastPart);
}
private String buildSql(Object[] params) throws SQLException {
if (parts.size() == 1) {
return parts.get(0);
}
//checkBinded();检查参数是不是null
StringBuilder sb = new StringBuilder(parts.get(0));
for (int i = 1, p = 0; i < parts.size(); i++) {
String pValue = getParameter(i - 1);
if (PARAM_MARKER.equals(pValue)) {
appendBoundValue(sb, params[p++]);
} else {
sb.append(pValue);
}
sb.append(parts.get(i));
}
String mySql = sb.toString();
return mySql;
}
private String getParameter(int paramIndex) {
for (int i = 0, count = paramIndex; i < parameters.size(); i++) {
List pList = parameters.get(i);
count -= pList.size();
if (count < 0) {
return pList.get(pList.size() + count);
}
}
return null;
}
private void appendBoundValue(StringBuilder sb, Object params) {
//需要根据参数类型来处理
try {
sb.append(getObject(params));
} catch (SQLException e) {
e.printStackTrace();
}
}
private StringBuilder getBind(String bind, boolean quote) {
StringBuilder sb = new StringBuilder();
if (quote) {
sb.append("'").append(bind).append("'");
} else if (bind.equals("\\N")) {
sb.append("null");
} else {
sb.append(bind);
}
return sb;
}
private StringBuilder getBind(String bind) {
return getBind(bind, false);
}
public StringBuilder getNull(int sqlType) throws SQLException {
return getBind("\\N");
}
public StringBuilder getBoolean(boolean x) throws SQLException {
return getBind(x ? "1" : "0");
}
public StringBuilder getByte(byte x) throws SQLException {
return getBind(Byte.toString(x));
}
public StringBuilder getShort(short x) throws SQLException {
return getBind(Short.toString(x));
}
public StringBuilder getInt(int x) throws SQLException {
return getBind(Integer.toString(x));
}
public StringBuilder getLong(long x) throws SQLException {
return getBind(Long.toString(x));
}
public StringBuilder getFloat(float x) throws SQLException {
return getBind(Float.toString(x));
}
public StringBuilder getDouble(double x) throws SQLException {
return getBind(Double.toString(x));
}
public StringBuilder getBigDecimal(BigDecimal x) throws SQLException {
return getBind(x.toPlainString());
}
public StringBuilder getDate(java.sql.Date x) throws SQLException {
return getBind(dateFormat.format(x), true);
}
public StringBuilder getTime(Time x) throws SQLException {
return getBind(dateTimeFormat.format(x), true);
}
public StringBuilder getTimestamp(Timestamp x) throws SQLException {
return getBind(dateTimeFormat.format(x), true);
}
public StringBuilder getAsciiStream(InputStream x, int length) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
@Deprecated public StringBuilder getUnicodeStream(InputStream x, int length) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
public StringBuilder getBinaryStream(InputStream x, int length) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
public StringBuilder getObject(Object x, int targetSqlType) throws SQLException {
return getObject(x);
}
public StringBuilder getClob(Clob x) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
public StringBuilder getBlob(Blob x) throws SQLException {
throw new SQLFeatureNotSupportedException();
}
public StringBuilder getObject(Object x) throws SQLException {
if (x == null) {
return getNull(Types.OTHER);
} else {
if (x instanceof Byte) {
return getInt(((Byte) x).intValue());
} else if (x instanceof String) {
return null;
} else if (x instanceof BigDecimal) {
return getBigDecimal((BigDecimal) x);
} else if (x instanceof Short) {
return getShort(((Short) x).shortValue());
} else if (x instanceof Integer) {
return getInt(((Integer) x).intValue());
} else if (x instanceof Long) {
return getLong(((Long) x).longValue());
} else if (x instanceof Float) {
return getFloat(((Float) x).floatValue());
} else if (x instanceof Double) {
return getDouble(((Double) x).doubleValue());
} else if (x instanceof byte[]) {
return null;
} else if (x instanceof java.sql.Date) {
return getDate((java.sql.Date) x);
} else if (x instanceof Time) {
return getTime((Time) x);
} else if (x instanceof Timestamp) {
return getTimestamp((Timestamp) x);
} else if (x instanceof Boolean) {
return getBoolean(((Boolean) x).booleanValue());
} else if (x instanceof InputStream) {
return getBinaryStream((InputStream) x, -1);
} else if (x instanceof Blob) {
return getBlob((Blob) x);
} else if (x instanceof Clob) {
return getClob((Clob) x);
} else if (x instanceof BigInteger) {
return getBind(x.toString());
} else if (x instanceof UUID) {
return null;
} else if (x instanceof Collection) {
return null;
} else if (x.getClass().isArray()) {
return null;
} else {
throw new SQLDataException("Can't bind object of class " + x.getClass().getCanonicalName());
}
}
}
}
}
```
```java
package cn.webrx.interceptor;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
import java.util.regex.Matcher;
@Intercepts({
@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
public class HelloInterceptor implements Interceptor {
private static String[] officeIdNames = new String[]{"OFFICE_ID", "OFFICEID", "PK_OFFICE_ID"};
@Override
public Object intercept(Invocation invocation) throws Throwable {
try {
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
//0.sql参数获取
Object parameter = null;
if (invocation.getArgs().length > 1) {
parameter = invocation.getArgs()[1];
}
//1.获取sqlId
String sqlId = mappedStatement.getId();
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
Configuration configuration = mappedStatement.getConfiguration();
//获取真实的sql语句
String sql = getSql(configuration, boundSql, sqlId, 0);
//2.判断是否有officeId
if (hasOfficeId(sql, officeIdNames)) {
System.out.println(sql);
} else {
System.out.println(sql);
}
} catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
return invocation.proceed();
}
/**
* 判断sql语句中是否包含officeId字段
*
* @param sql sql语句
* @return
*/
private boolean hasOfficeId(String sql, String[] officeIdNames) {
//office ID 的可能名称
if (sql == null || sql.trim().length() == 0) {
return false;
}
String afterWhereStatement = sql.toUpperCase().substring(sql.indexOf("where"));
for (String officeIdName : officeIdNames) {
if (afterWhereStatement.indexOf(officeIdName) > 0) {
return true;
}
}
return false;
}
@Override
public Object plugin(Object o) {
return Plugin.wrap(o, this);
}
@Override
public void setProperties(Properties properties) {
}
private static String getSql(Configuration configuration, BoundSql boundSql, String sqlId, long time) {
String sql = showSql(configuration, boundSql);
StringBuilder str = new StringBuilder(100);
str.append(sqlId);
str.append(":");
str.append(sql);
return str.toString();
}
private static String getParameterValue(Object obj) {
String value = null;
if (obj instanceof String) {
value = "'" + obj.toString() + "'";
} else if (obj instanceof Date) {
DateFormat formatter = DateFormat.getDateTimeInstance(
DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
value = "'" + formatter.format(new Date()) + "'";
} else {
if (obj != null) {
value = obj.toString();
} else {
value = "";
}
}
return value;
}
private static String showSql(Configuration configuration, BoundSql boundSql) {
Object parameterObject = boundSql.getParameterObject();
List parameterMappings = boundSql.getParameterMappings();
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
if (!parameterMappings.isEmpty() && parameterObject != null) {
TypeHandlerRegistry typeHandlerRegistry = configuration
.getTypeHandlerRegistry();
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?",
Matcher.quoteReplacement(getParameterValue(parameterObject)));
} else {
MetaObject metaObject = configuration
.newMetaObject(parameterObject);
for (ParameterMapping parameterMapping : parameterMappings) {
String propertyName = parameterMapping.getProperty();
if (metaObject.hasGetter(propertyName)) {
Object obj = metaObject.getValue(propertyName);
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
} else if (boundSql.hasAdditionalParameter(propertyName)) {
Object obj = boundSql
.getAdditionalParameter(propertyName);
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
} else {
sql = sql.replaceFirst("\\?", "缺失");
}//打印出缺失，提醒该参数缺失并防止错位
}
}
}
return sql;
}
}
```
注册插件
```xml
```
自定义分页:
```java
/*
* Copyright (c) 2006, 2023, webrx.cn All rights reserved.
*
*/
package cn.webrx.interceptor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.SystemMetaObject;
import java.sql.Connection;
import java.util.Properties;
/**
*
* Powered by webrx On 2023-04-20 17:56:39
*
* @author webrx [webrx@126.com]
* @version 1.0
* @since 17
*/
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class MyPager implements Interceptor {
public static void startPage(int c, int p) {
currpage = c;
pagesize = p;
}
public static void startPage(int c) {
currpage = c;
}
public static int currpage = 1;
public static int pagesize = 10;
public static int recordcount = 0;
public static int pagecount = 1;
public Object intercept(Invocation invocation) throws Throwable {
//System.out.println("拦截器:intercept(Invocation invocation)");
var sh = invocation.getTarget();
var mh = SystemMetaObject.forObject(sh);
var ms = (MappedStatement) mh.getValue("delegate.mappedStatement");
String msid = ms.getId();
System.out.println(msid);
if (msid.endsWith("ByPage")) {
//var ph = (ParameterHandler)
mh.getValue("delegate.parameterHandler");
//var param = (Map) ph.getParameterObject();
String sql = mh.getValue("delegate.boundSql.sql").toString().trim() + String.format(" limit %d,%d", currpage * pagesize - pagesize, pagesize);
mh.setValue("delegate.boundSql.sql", sql);
}
return invocation.proceed();
}
public Object plugin(Object target) {
//System.out.println("拦截器：plugin(Object target)");
//固定的写法，第二个参数表示要代理的对象 this
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties p) {
//System.out.println("拦截器：setProperties(Properties p)");
this.pagesize = Integer.parseInt(p.getOrDefault("pagesize",
15).toString());
this.currpage = Integer.parseInt(p.getOrDefault("currpage",
1).toString());
//System.out.println(this.pagesize);
}
}
```
案例
```java
@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class ExecutorEncryptInterceptor implements Interceptor {
Properties properties = null;
/**
* 拦截方法逻辑
* 这里主要是通过反射去获取要执行的SQL相关信息，然后进行操作
*/
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object target = invocation.getTarget(); //被代理对象
Method method = invocation.getMethod(); //代理方法
Object[] args = invocation.getArgs(); //方法参数
MappedStatement mappedStatement = (MappedStatement) args[0];
Object parameterObject = args[1];
// do something ...方法拦截前执行代码块
Object result = invocation.proceed();
// do something ...方法拦截后执行代码块
return result;
}
/**
* 生成MyBatis拦截器代理对象
*/
@Override
public Object plugin(Object target) {
if(target instanceof Executor){
// 调用插件
return Plugin.wrap(target, this);
}
return target;
}
/**
* 设置插件属性（直接通过Spring的方式获取属性，所以这个方法一般也用不到）
* 项目启动的时候数据就会被加载
*/
@Override
public void setProperties(Properties properties) {
// 赋值成员变量，在其他方法使用
this.properties = properties;
}
/* ... */
}
```
#### 5.3.2 StatementHandler.class
```java
/*
* Copyright (c) 2006, 2023, webrx.cn All rights reserved.
*
*/
package cn.webrx.interceptor;
import cn.webrx.entity.Book;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import java.sql.Connection;
import java.util.Arrays;
/**
*
* Powered by webrx On 2023-04-20 17:56:39
*
* @author webrx [webrx@126.com]
* @version 1.0
* @since 17
*/
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
public class MyPager implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
System.out.println(Arrays.toString(args));
Object target = invocation.getTarget();
MetaObject metaObject = SystemMetaObject.forObject(target);
MappedStatement ms = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
String msid = ms.getId();
System.out.println(msid);
System.out.println(ms.getSqlCommandType());
//metaObject.getValue("delegate.boundSql.sql") 获取sql
System.out.println(metaObject.getValue("delegate.boundSql.sql"));
//设置参数
ParameterHandler parameterHandler = (ParameterHandler)metaObject.getValue("delegate.parameterHandler");
Object param = parameterHandler.getParameterObject();
Book book = (Book)param;
System.out.println(book);
book.setPrice(100);
System.out.println(book);
//设置sql语句
//metaObject.setValue("delegate.boundSql.sql","insert absdfasdf....");
Object obj = invocation.proceed();
return obj;
}
}
```
#### 5.3.3 开发分页插件
> 第一步 ：编写插件
```java
/*
* Copyright (c) 2006, 2023, webrx.cn All rights reserved.
*
*/
package cn.webrx.interceptor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
*
* Powered by webrx On 2023-04-20 17:56:39
*
* @author webrx [webrx@126.com]
* @version 1.0
* @since 17
*/
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
public class MyPager implements Interceptor {
public static void startPage(int c, int p) {
currpage = c;
pagesize = p;
}
public static void startPage(int c) {
currpage = c;
}
public static int currpage = 1;
public static int pagesize = 10;
public static int recordcount = 0;
public static int pagecount = 1;
public static String pageInfo() {
return String.format("[第 %d 页/共 %d 页，每页 %d 条/共 %d 条]", currpage, pagecount, pagesize, recordcount);
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object target = invocation.getTarget();
MetaObject metaObject = SystemMetaObject.forObject(target);
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
if (mappedStatement.getSqlCommandType() == SqlCommandType.SELECT && mappedStatement.getId().endsWith("ByPage")) {
String sql = metaObject.getValue("delegate.boundSql.sql").toString();
//System.out.println(sql.trim());
String sql1 = "select count(*) " + sql.substring(sql.indexOf("from"));
Connection connection = (Connection) invocation.getArgs()[0];
try {
PreparedStatement pst = connection.prepareStatement(sql1);
ResultSet rs = pst.executeQuery();
rs.next();
recordcount = rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
}
pagecount = recordcount % pagesize == 0 ? recordcount / pagesize : recordcount / pagesize + 1;
String sql2 = sql.trim().concat(String.format(" limit %d,%d", currpage * pagesize - pagesize, pagesize));
metaObject.setValue("delegate.boundSql.sql", sql2);
//metaObject.getValue("delegate.boundSql.sql") 获取sql
//System.out.println(metaObject.getValue("delegate.boundSql.sql"));
//设置参数
//ParameterHandler parameterHandler = (ParameterHandler)metaObject.getValue("delegate.parameterHandler");
//Object param = parameterHandler.getParameterObject();
//Book book = (Book)param;
//System.out.println(book);
//book.setPrice(100);
//System.out.println(book);
//设置sql语句
//metaObject.setValue("delegate.boundSql.sql","insert absdfasdf....");
}
Object obj = invocation.proceed();
return obj;
}
}
```
> 第二步 ：注册插件
```java
MyPager mp = new MyPager();
Properties prop = new Properties();
prop.setProperty("currpage", "1");
prop.setProperty("pagesize", "3");
mp.setProperties(prop);
//sf.setPlugins(new ExamplePlugin(), mp, new PageInterceptor());
sf.setPlugins(mp);
```
> 第三步 ：使用分页插件直接查询分页处理
```java
//MyPager.startPage(1,5);
MyPager.startPage(4);
bm.myByPage();
System.out.println(MyPager.pageInfo());
```
#### 5.3.4 开发雪花算法插件
现在的服务基本是分布式、微服务形式的，而且大数据量也导致分库分表的产生，对于水平分表就需要保证表中 id 的全局唯一性。
对于 MySQL 而言，一个表中的主键 id 一般使用自增的方式，但是如果进行水平分表之后，多个表中会生成重复的 id 值。那么如何保证水平分表后的多张表中的 id 是全局唯一性的呢？
如果还是借助数据库主键自增的形式，那么可以让不同表初始化一个不同的初始值，然后按指定的步长进行自增。例如有3张拆分表，初始主键值为1，2，3，自增步长为3。
当然也有人使用 UUID 来作为主键，但是 UUID 生成的是一个无序的字符串，对于 MySQL 推荐使用增长的数值类型值作为主键来说不适合。
也可以使用 Redis 的自增原子性来生成唯一 id，但是这种方式业内比较少用。
当然还有其他解决方案，不同互联网公司也有自己内部的实现方案。雪花算法是其中一个用于解决分布式 id 的高效方案，也是许多互联网公司在推荐使用的。
##### SnowFlake 雪花算法
SnowFlake 中文意思为雪花，故称为雪花算法。最早是 Twitter 公司在其内部用于分布式环境下生成唯一 ID。在2014年开源 scala 语言版本。
```text
雪花算法的原理就是生成一个的 64 位比特位的 long 类型的唯一 id。
1 最高 1 位固定值 0，因为生成的 id 是正整数，如果是 1 就是负数了。
2 接下来 41 位存储毫秒级时间戳，2^41/(1000*60*60*24*365)=69，大概可以使用 69 年。
3 再接下 10 位存储机器码，包括 5 位 datacenterId 和 5 位 workerId。最多可以部署 2^10=1024 台机器。
3 最后 12 位存储序列号。同一毫秒时间戳时，通过这个递增的序列号来区分。即对于同一台机器而言，同一毫秒时间戳下，可以生成 2^12=4096 个不重复 id。
可以将雪花算法作为一个单独的服务进行部署，然后需要全局唯一 id 的系统，请求雪花算法服务获取 id 即可。
对于每一个雪花算法服务，需要先指定 10 位的机器码，这个根据自身业务进行设定即可。例如机房号+机器号，机器号+服务号，或者是其他可区别标识的 10 位比特位的整数值都行。
java.lang.Long 8字节 mysql bigint 8字节
```
##### 算法优缺点
雪花算法有以下几个优点：
高并发分布式环境下生成不重复 id，每秒可生成百万个不重复 id。基于时间戳，以及同一时间戳下序列号自增，基本保证 id 有序递增。不依赖第三方库或者中间件。算法简单，在内存中进行，效率高。
雪花算法有如下缺点：
依赖服务器时间，服务器时钟回拨时可能会生成重复 id。算法中可通过记录最后一个生成 id 时的时间戳来解决，每次生成 id 之前比较当前服务器时钟是否被回拨，避免生成重复 id。
##### 注意事项
其实雪花算法每一部分占用的比特位数量并不是固定死的。例如你的业务可能达不到 69 年之久，那么可用减少时间戳占用的位数，雪花算法服务需要部署的节点超过1024 台，那么可将减少的位数补充给机器码用。
注意，雪花算法中 41 位比特位不是直接用来存储当前服务器毫秒时间戳的，而是需要当前服务器时间戳减去某一个初始时间戳值，一般可以使用服务上线时间作为初始时间戳值。
对于机器码，可根据自身情况做调整，例如机房号，服务器号，业务号，机器 IP 等都是可使用的。对于部署的不同雪花算法服务中，最后计算出来的机器码能区分开来即可。
##### 雪花算法
> 第一步：雪花算法代码组件
```java
/*
* Copyright (c) 2006, 2023, webrx.cn All rights reserved.
*
*/
package cn.webrx.utils;
import org.springframework.stereotype.Component;
/**
*
* Powered by webrx On 2023-04-21 12:49:49
*
* @author webrx [webrx@126.com]
* @version 1.0
* @since 17
*/
@Component
public class SnowFlakeUtils {
/*
起始时间时间戳：这个时间为第一次运行时的时间，这里设置为2021/11/23/19/17
可以在未来的69年内稳定运行
*/
private final static long START_STMP = 1637666189914L;
private final static long SEQUENCE_BIT = 12;//序列号占用12bit
private final static long MACHINE_BIT = 5;//机器号占用5bit
private final static long MACHINE_HOUSE_BIT = 5;//机房号占用5bit
/*
-1的源码 10000001
-1的反码 11111110
-1的补码 11111111
-1左移12位= 1111 1111 0000 0000 0000
-1 = 1111 1111 1111 1111 1111
异或运算 = 0000 0000 1111 1111 1111=4095
因此MAX_SEQUENCE的值为4095
*/
private final static long MAX_SEQUENCE = -1L ^ (-1L << SEQUENCE_BIT);
//同理 MAX_MACHINE为31
private final static long MAX_MACHINE = -1L ^ (-1L << MACHINE_BIT);
//同理 MAX_MACHINE_HOUSE值为31
private final static long MAX_MACHINE_HOUSE = -1L ^ (-1L << MACHINE_HOUSE_BIT);
//机器ID
private long machineID;
//机房ID
private long machineHouseID;
private long lastTime = 0;//上一次生成ID的时间戳
private long sequence = 0;//序列号，默认为0
public SnowFlakeUtils() {
this.machineID = 0;
this.machineHouseID = 0;
}
public SnowFlakeUtils(long machineID, long machineHouseID) {
this.machineID = machineID;
this.machineHouseID = machineHouseID;
}
public long getMachineID() {
return machineID;
}
public void setMachineID(long machineID) {
this.machineID = machineID;
}
public long getMachineHouseID() {
return machineHouseID;
}
public void setMachineHouseID(long machineHouseID) {
this.machineHouseID = machineHouseID;
}
/***
*产生下一个ID
* 用long型来表示我们生成的64位ID，
* @return
*/
public synchronized long nextId() {
if (machineHouseID > MAX_MACHINE_HOUSE || machineID > MAX_MACHINE) {
throw new RuntimeException("机房ID或机器ID超出最大值");
}
//获取当前时间戳
long currentTime = System.currentTimeMillis();
//如果当前时间小于上一次生成ID的时间，抛出异常
if (currentTime < lastTime) {
throw new RuntimeException("当前时间为异常值，请勿回拨时间！");
}
//如果当前时间等于上一次生成ID时间，说明是在同一毫秒中生成，那么序列号加一
else if (currentTime == lastTime) {
/*
MAX_SEQUENCE: 0000 1111 1111 1111
&
4096: 0001 0000 0000 0000
= 0
当sequence小于4095时， (sequence+1)&MAX_SEQUENCE=sequence+1
当sequence等于4095时，(sequence+1)&MAX_SEQUENCE=0
*/
sequence = (sequence + 1) & MAX_SEQUENCE;
if (sequence == 0L) {
//获取下一个毫秒值
currentTime = getNextMill();
}
} else {
//毫秒值不同，sequence初始为0
sequence = 0L;
}
//更新最近一次生成时间的毫秒值
lastTime = currentTime;
return (currentTime - START_STMP) << 22//左移22位 空出机房ID5位+机器ID5位+序列号12位
| machineID << 12//左移12位 空出序列号12位
| machineHouseID << 17//左移17位 空出机器ID5位+序列号12位
| sequence;//序列号部分
}
/**
* 获取下一个毫秒值
*
* @return
*/
private long getNextMill() {
long mill = System.currentTimeMillis();
//如果当前时间等于上一次的时间则一直自旋
while (mill == lastTime) {
mill = System.currentTimeMillis();
}
return mill;
}
/**
* Main方法测试
*
* @param args
*/
public static void main(String[] args) {
//初始化一个雪花算法工具类，设置机房ID和机器ID都为0
SnowFlakeUtils snowFlakeUtils = new SnowFlakeUtils(0, 0);
for (int i = 0; i < 100; i++) {
//生成100个ID
System.out.println(snowFlakeUtils.nextId());
}
}
}
```
> 第二步：
```java
/*
* Copyright (c) 2006, 2023, webrx.cn All rights reserved.
*
*/
package cn.webrx.interceptor;
import cn.webrx.utils.SnowFlakeUtils;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
*
* Powered by webrx On 2023-04-20 17:56:39
*
* @author webrx [webrx@126.com]
* @version 1.0
* @since 17
*/
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
public class KeyGen implements Interceptor {
SnowFlakeUtils su;
public void setSu(SnowFlakeUtils su){
this.su=su;
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object target = invocation.getTarget();
MetaObject metaObject = SystemMetaObject.forObject(target);
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
if (mappedStatement.getSqlCommandType() == SqlCommandType.INSERT) {
ParameterHandler parameterHandler = (ParameterHandler)metaObject.getValue("delegate.parameterHandler");
Object param = parameterHandler.getParameterObject();
System.out.println(param);
//System.out.println(param.getClass().getDeclaredFields().length);
//bigint long long id; @AutoID
try{
if(param.getClass().getDeclaredField("id").isAnnotationPresent(AutoID.class)){
//String sql = metaObject.getValue("delegate.boundSql.sql").toString();
//String sql1 = sql.replace("null",String.valueOf(su.nextId()));
//metaObject.setValue("delegate.boundSql.sql", sql1);
Class c = param.getClass();
Method method = c.getMethod("setId",Long.class);
method.invoke(param,su.nextId());
System.out.println(param);
}
}catch(Exception e){
e.printStackTrace();
}
}
Object obj = invocation.proceed();
return obj;
}
}
```
> 第三步：
```java
/*
* Copyright (c) 2006, 2023, webrx.cn All rights reserved.
*
*/
package cn.webrx.config;
import cn.webrx.interceptor.DemoInterceptor;
import cn.webrx.interceptor.ExamplePlugin;
import cn.webrx.interceptor.KeyGen;
import cn.webrx.interceptor.MyPager;
import cn.webrx.utils.SnowFlakeUtils;
import com.alibaba.druid.pool.DruidDataSource;
import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import java.io.IOException;
import java.time.LocalDate;
import java.util.Properties;
/**
*
* Powered by webrx On 2023-04-19 14:19:11
*
* @author webrx [webrx@126.com]
* @version 1.0
* @since 17
*/
@Configuration
@MapperScan({"cn.webrx.mapper"})
@ComponentScan("cn.webrx")
public class AppConfig {
private String url = "jdbc:mysql:/db";
private String username = "root";
private String password = "root";
@Bean
public LocalDate now() {
return LocalDate.now();
}
@Bean(name = "ds", initMethod = "init", destroyMethod = "close")
public DruidDataSource druidDataSource() {
DruidDataSource ds = new DruidDataSource();
ds.setUrl(url);
ds.setUsername(username);
ds.setPassword(password);
return ds;
}
@Bean("sf")
public SqlSessionFactoryBean sqlSessionFactoryBean(DruidDataSource ds,MyPager mp,KeyGen kg) throws IOException {
SqlSessionFactoryBean sf = new SqlSessionFactoryBean();
sf.setDataSource(ds);
//设置别名 cn.webrx.entity.Book 别名就 book cn.webrx.vo.BookVo 别名就是 bookVo bookvo
sf.setTypeAliasesPackage("cn.webrx.entity,cn.webrx.vo");
//注册PageHelper插件
//sf.setPlugins(new ExamplePlugin(), mp, new PageInterceptor());
sf.setPlugins(kg);
sf.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*Mapper*.xml"));
return sf;
}
@Bean
public SqlSession sqlSession(SqlSessionFactoryBean sf) throws Exception {
return sf.getObject().openSession();
}
@Bean
public MyPager myPager(SnowFlakeUtils su){
MyPager mp = new MyPager();
mp.setSu(su);
Properties prop = new Properties();
prop.setProperty("currpage", "1");
prop.setProperty("pagesize", "3");
mp.setProperties(prop);
return mp;
}
@Bean
public KeyGen keyGen(SnowFlakeUtils su){
KeyGen kg = new KeyGen();
kg.setSu(su);
return kg;
}
}
```
> 第四步：表设计及模型类注解
```java
/*
* Copyright (c) 2006, 2023, webrx.cn All rights reserved.
*
*/
package cn.webrx.entity;
import cn.webrx.interceptor.AutoID;
import lombok.Data;
import org.springframework.stereotype.Component;
import java.time.LocalDate;
/**
*
* Powered by webrx On 2023-04-19 14:54:17
*
* @author webrx [webrx@126.com]
* @version 1.0
* @since 17
*/
@Data
public class Book {
@AutoID
private Long id;
private String name;
private double price;
private String author;
private LocalDate pdate;
}
//插件使用中的注解类 @AutoId
package cn.webrx.interceptor;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
*
* Powered by webrx On 2023-04-21 14:34:37
*
* @author webrx [webrx@126.com]
* @version 1.0
* @since 17
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface AutoID {
}
```
>第五步：mapper
```java
int add(Book book);
```
```xml
insert into t_book
values (#{id}, #{name}, #{price}, #{author}, #{pdate})
```
> 第六步：测试
```java
@Test
void m14() {
Book b = new Book();
b.setId(234234L);
b.setName("《myatis ORM》");
b.setPrice(55D);
b.setAuthor("李四");
b.setPdate(LocalDate.of(2023, 5, 15));
bm.add(b);
}
```
## 第六章 mybatis 通用Mapper工具
mybatis.io
> 第一步 ：添加依赖组件
```xml
tk.mybatis
mapper
4.2.2
```
> 第二步：spring 项目
```java
@Bean
public MapperScannerConfigurer mapperScannerConfigurer() {
MapperScannerConfigurer ms = new MapperScannerConfigurer();
ms.setBasePackage("cn.webrx.mapper");
return ms;
}
```
> 第三步：应用
建立表
```sql
CREATE TABLE `country` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`countryname` varchar(255) DEFAULT NULL COMMENT '名称',
`countrycode` varchar(255) DEFAULT NULL COMMENT '代码',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8 COMMENT='国家信息';
```
建立实体 注解表名 列名 忽略对象和表不匹配的属性
```java
/*
* Copyright (c) 2006, 2023, webrx.cn All rights reserved.
*
*/
package cn.webrx.entity;
import lombok.Data;
import javax.persistence.Id;
import javax.persistence.Transient;
/**
*
* Powered by webrx On 2023-04-21 16:34:08
*
* @author webrx [webrx@126.com]
* @version 1.0
* @since 17
*/
@Data //@Table(name = "country")
public class Country {
@Id //@Column(name = "c_id")
private Integer id;
private String countryname;
private String countrycode;
@Transient
private String info;
}
```
建立Mapper接口
```java
@Repository
public interface CountryMapper extends Mapper {
}
```
> 第四步：测试程序
```cmd
@Test
void m15(){
Country c = new Country();
//c.setId(20);
//c.setCountryname("中国");
//c.setCountrycode("86");
//cm.save(c);
//cm.insert(c);
//cm.insertSelective(c);
List cs = cm.selectAll();
System.out.println(cs);
Country c2 = cm.selectByPrimaryKey(20);
c2.setCountrycode("99");
cm.save(c2);
System.out.println(c2);
cm.deleteByPrimaryKey(20);
Map map = new HashMap<>();
map.put("id",20);
map.put("countryname","中国");
Example ex = new Example(Country.class);
ex.setCountProperty("中国");
ex.setTableName("tt");
ex.selectProperties("id","countrycode");
ex.or().andEqualTo("id",20);
System.out.println(cm.selectByExample(ex));
//System.out.println(cm.selectByExample(null));
}
```
## 第七章 mybatis 动态SQL语句
> 别名设置使用，\#{} 和 \${} 区别
### 7.1 ResultMap
解决表的查询字段名和对象属性名不匹配问题。
```xml
```
### 7.2 动态SQL语句
#### 7.2.1 if
```java
public List selectAll();
//public List selectAll(int id);
public List selectAll(Map map);
```
```xml
```
测试
```java
@Test
void m17(){
Map map = new HashMap<>();
//map.put("id",90);
//List list = bm.selectAll(map);
//System.out.println(list.size());
//System.out.println(list.get(0));
map.put("name","%ja_a%");
//List list = bm.selectAll();
//List list = bm.selectAll(null);
List list = bm.selectAll(map);
}
```
#### 7.2.2 set
set主要应用update 表 set 也可以应用 insert 表set
```java
public int myinsert(Map map);
public int myupdate(Book book);
public int myup(Map map);
```
```xml
pdate=#{pdate},
name=#{name},
author=#{author},
id=#{id},
price=#{price},
insert into t_book
update t_book
where id = #{id}
update t_book
price=${prices},
price=#{price},
```
```java
@Test
void m19(){
//Book book = new Book();
//book.setId(10L);
//book.setPrice(55);
//book.setName("《my code》");
//Book book = bm.queryById(10);
//book.setPrice(99);
//book.setName("myjava程序");
//bm.myupdate(book);
Map map = new HashMap<>();
//map.put("id",10);
//map.put("name","aaaa");
//map.put("prices","price-5");
map.put("price","35");
bm.myup(map);
}
@Test
void m18(){
Map map = new HashMap<>();
map.put("name","《java入门教程》");
map.put("id",915);
map.put("pdate",LocalDate.now());
bm.myinsert(map);
}
```
#### 7.2.3 where
```java
public List queryAll();
public List queryAll(Map map);
@Test
void m20(){
bm.queryAll();
bm.queryAll(null);
Map map = new HashMap<>();
//map.put("id",90);
//map.put("price",45);
//map.put("maxprice",45);
map.put("minprice",25);
bm.queryAll(map);
}
```
```xml
```
#### 7.2.4 choose
choose (when, otherwise)
```java
public List querys();
public List querys(Map map);
@Test
void m21() {
bm.querys();
Map map = new HashMap<>();
map.put("price",55);
map.put("name", "%java%");
bm.querys(map);
}
```
```xml
```
#### 7.2.5 trim
```java
int mysave(Map map);
@Test
void m22(){
Map map = new HashMap<>();
map.put("id",1034);
map.put("name","mybaits1034");
map.put("price",56);
bm.mysave(map);
}
```
```xml
insert into t_book
id,
name,
author,
price,
values
#{id},
#{name},
#{author},
#{price},
id,
name,
author,
price,
values
#{id},
#{name},
#{author},
#{price},
```
#### 7.2.6 foreach
> 用于where in 条件
```xml
delete from t_book
id=#{id}
id in
#{delid}
price=#{price}
false
```
```java
int delBy(Map map);
@Test
void m23(){
Map map = new HashMap<>();
//map.put("id",1035);
map.put("ids",List.of(915,1034,10,20,30,24,12));
bm.delBy(map);
}
```
#### 7.2.7 bind
```xml
```
#### 7.2.8 动态数据库
```xml
select seq_users.nextval from dual
select nextval for seq_users from sysibm.sysdummy1"
insert into users values (#{id}, #{name})
```
#### 7.2.9 执行存储过程
```xml
```
```java
//调用存储过程
//@Options(statementType = StatementType.CALLABLE) @Select("call p_show")
//@Options(statementType = StatementType.CALLABLE) @Select("{call p_show}")
@Options(statementType = StatementType.CALLABLE) @Select("{call p_show()}")
List pshow();
```
有in 参数 out参数的存储过程
```sql
call p_count(@cc);
select @cc;
```
```xml
```
```java
void pcount(Map map);
@Test
void m24(){
//System.out.println(bm.pshow());
Map map = new HashMap<>();
bm.pcount(map);
//System.out.println(map.get("count"));
System.out.println(map);
System.out.println(map.get("id"));
}
```
## 第八章 mybatis 高级关系查询
### 8.1 one2one
表相关信息
```sql
create table w_user(
username varchar(50) primary key,
password varchar(32)
)
create table w_userinfo(
id int UNSIGNED auto_increment,
username varchar(50) UNIQUE,
truename varchar(25),
gender enum('男','女') default '男',
regtime datetime,
primary key(id)
)
insert into w_user values('admin','123');
insert into w_userinfo values(null,'admin','李四','男',now());
insert into w_user values('root','root');
insert into w_userinfo values(null,'root','周勇','女',now());
```
实体类
```java
@Data
public class UserInfo {
private int id;
private String username;
private String truename;
private String gender;
private LocalDateTime regtime;
}
@Data
public class User {
private String username;
private String password;
private UserInfo userInfo;
}
```
映射接口
```java
public interface UserMapper {
public List queryAll();
public User show(String username);
}
```
映射接口配置文件
```xml
```
测试程序
```java
@Autowired
UserMapper um;
@Test @DisplayName("测试一对一关联查询")
void m25(){
List us = um.queryAll();
User user = um.show("admin");
System.out.println("================================================");
System.out.println(user.getUsername());
System.out.println(user.getPassword());
System.out.println("================================================");
System.out.println(user.getUserInfo().getTruename());
}
```
### 8.2 one2many
```sql
create table w_user(
username varchar(50) primary key,
password varchar(32)
)
create table w_userinfo(
id int UNSIGNED auto_increment,
username varchar(50) UNIQUE,
truename varchar(25),
gender enum('男','女') default '男',
regtime datetime,
primary key(id)
)
insert into w_user values('admin','123');
insert into w_userinfo values(null,'admin','李四','男',now());
insert into w_user values('root','root');
insert into w_userinfo values(null,'root','周勇','女',now());
create table w_useraddress(
id int UNSIGNED auto_increment,
username varchar(50),
name varchar(25),
phone varchar(30),
state boolean,
address varchar(255),
primary key(id)
)
drop table w_useraddress;
insert into w_useraddress values(null,'admin','张一','123',false,'河南郑州'),(null,'admin','王一','666',true,'河南开封')
select * from w_useraddress where username='admin'
```
```java
@Data
public class User {
private String username;
private String password;
//one2one
private UserInfo userInfo;
//one2many
private List userAddress;
}
@Data
public class UserAddress {
private String username;
private int id;
private String name;
private String phone;
private String address;
private boolean state;
}
```
```xml
```
测试
```java
@Test @DisplayName("测试一对一关联查询")
void m25(){
List us = um.queryAll();
System.out.println(us);
System.out.println("================================================");
User user = um.show("admin");
System.out.println("================================================");
System.out.println(user.getUsername());
System.out.println(user.getPassword());
System.out.println(user.getUserAddress().stream().filter(e->e.isState()).findFirst().get().getName());
System.out.println(user.getUserAddress().stream().filter(e->e.isState()).findFirst().get().getAddress());
System.out.println(user.getUserAddress().stream().filter(e->e.isState()).findFirst().get().getPhone());
System.out.println("================================================");
System.out.println(user.getUserInfo().getTruename());
//System.out.println(user.getUserAddress().stream().filter(a->a.isState()).findFirst().get());
}
```
后台管理系统菜单案例
```sql
CREATE TABLE `s_menu` (
`id` varchar(36) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`name` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`type` tinyint DEFAULT '1',
`icon` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`path` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`component` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`ishidden` bit(1) DEFAULT b'0',
`sorts` tinyint DEFAULT NULL,
`auths` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`parentid` varchar(36) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC
```
```java
@Data
public class Menu {
private String id;
private String name;
private String icon;
private String path;
private String parentid;
private List