java 实现动态菜单 java动态table

转载

冷月星 2023-05-25 10:44:02

文章标签 mybatis java Data sql 表单 文章分类 Java 后端开发

前提

我们的业务上会有各种各样的表单，常规的开发是一个表单，我们一般会在数据库里面建立一个对应的表，然后在代码中建立一个model与之对应，一旦建立好以后，就不能再对表单进行修改了，如果需要修改，就需要修改数据库表，修改model重新发版上线，对于频繁需要表单变更的情况来说很不友好。那么有没有方法可以对现有表单的列进行增删改甚至动态生成一个表单呢？这样的话表单变动就不用发版重新上线了，岂不美哉。解决办法自然是有的。

方案

整体思路就是数据库行转列，在java层面动态生成VO返回给前台。

具体实现

使用Mybatis3.4.6 + mysql5.7.26 + cglib3.2.5 + cglib-nodep3.2.5来进行实现

1.sql：CreateDB.sql

drop table  if exists form_metadata;
CREATE TABLE form_metadata
(
    ID int AUTO_INCREMENT,
    name varchar(100) comment '列名',
    property varchar(100) comment '属性名',
    property_type varchar(100) comment '属性类型',
    form_type int comment '表单类型 1 请假表  2 支付表 3 报销表',
    PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci;

drop table  if exists form_data;
CREATE TABLE form_data
(
    ID int AUTO_INCREMENT,
    property varchar(100) comment '属性名',
    `value` varchar(100) comment '属性值',
    form_type int comment '表单类型',
    row_id int comment '行数据ID，区分一行数据',
    user_id int comment '用户ID',
    PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci;

insert into form_metadata (name, property, property_type, form_type) values ("主键", "id", "java.lang.String", 1);
insert into form_metadata (name, property, property_type, form_type) values ("姓名", "name", "java.lang.String", 1);
insert into form_metadata (name, property, property_type, form_type) values ("请假天数", "days", "java.lang.String", 1);
insert into form_metadata (name, property, property_type, form_type) values ("请假原因", "reason", "java.lang.String", 1);

insert into form_data (property, `value`, form_type, row_id, user_id) values ("id", "1", 1, 1, 1);
insert into form_data (property, `value`, form_type, row_id, user_id) values ("name", "张三", 1, 1, 1);
insert into form_data (property, `value`, form_type, row_id, user_id) values ("days", "3", 1, 1, 1);
insert into form_data (property, `value`, form_type, row_id, user_id) values ("reason", "想睡觉", 1, 1, 1);

insert into form_data (property, `value`, form_type, row_id, user_id) values ("id", "2", 1, 2, 1);
insert into form_data (property, `value`, form_type, row_id, user_id) values ("name", "张三", 1, 2, 1);
insert into form_data (property, `value`, form_type, row_id, user_id) values ("days", "4", 1, 2, 1);
insert into form_data (property, `value`, form_type, row_id, user_id) values ("reason", "想吃饭", 1, 2, 1);

2.Mybatis配置：mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC">
                <property name="" value=""/>
            </transactionManager>
            <dataSource type="UNPOOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="xxx"/>
                <property name="username" value="xxx"/>
                <property name="password" value="xxx"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="org.apache.ibatis.dynamicform.FormDataMapper"/>
        <mapper class="org.apache.ibatis.dynamicform.FormMetadataMapper"/>
    </mappers>

</configuration>

3.数据库表对应的model：FormData、FormMetadata

public class FormData {

	private Integer id;

	private String property;

	private String value;

	private Integer formType;

	private Integer rowId;

	private Integer userId;

	// 省略getter、setter
}

public class FormMetadata {

	private Integer id;

	private String name;

	private String property;

	private String propertyType;

	private Integer formType;

	// 省略getter、setter
}

4.Mapper：FormDataMapper、FormMetadataMapper

public interface FormDataMapper {

	@Select("select * from form_data where user_id = #{userId} and form_type = #{formType}")
	List<FormData> listFormData(@Param("userId") Integer userId, @Param("formType") Integer formType);
}

public interface FormMetadataMapper {

	@Select("select * from form_metadata where form_type = #{formType}")
	List<FormMetadata> listFormMetadatas(@Param("formType") Integer formType);
}

5.测试代码

public class DynamicFormTest {

	private static SqlSessionFactory sqlSessionFactory;

	@BeforeClass
	public static void setUp() throws Exception {
		Reader reader = Resources.getResourceAsReader("org/apache/ibatis/dynamicform/mybatis-config.xml");
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
		reader.close();

		SqlSession session = sqlSessionFactory.openSession();
		Connection conn = session.getConnection();
		reader = Resources.getResourceAsReader("org/apache/ibatis/dynamicform/CreateDB.sql");
		ScriptRunner runner = new ScriptRunner(conn);
		runner.setLogWriter(null);
		runner.runScript(reader);
		conn.close();
		reader.close();
		session.close();
	}

	@Test
	public void getDynamicForm() {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		try {
			FormMetadataMapper formMetadataMapper = sqlSession.getMapper(FormMetadataMapper.class);
			FormDataMapper formMapper = sqlSession.getMapper(FormDataMapper.class);

			// 查询请假表元数据
			List<FormMetadata> formMetadatas = formMetadataMapper.listFormMetadatas(1);

			// 准备使用cglibg准备根据表单元数据动态生成VO
			Map<String, Class<?>> map = new HashMap<String, Class<?>>();
			for (FormMetadata formMetadata : formMetadatas) {
				map.put(formMetadata.getProperty(), Class.forName(formMetadata.getPropertyType()));
			}
			BeanGenerator generator = new BeanGenerator();
			for (Map.Entry<String, Class<?>> entry : map.entrySet()) {
				generator.addProperty(entry.getKey(), entry.getValue());
			}

			// 查询用户ID为1的请假表数据
			List<FormData> formDatas = formMapper.listFormData(1, 1);
			// 区分一行数据
			Map<Integer, List<FormData>> diffRow = new HashMap<>();
			for (FormData formData : formDatas) {
				if (!diffRow.containsKey(formData.getRowId())) {
					diffRow.put(formData.getRowId(), new ArrayList<>());
				}
				diffRow.get(formData.getRowId()).add(formData);
			}

			List<Object> vos = new ArrayList();
			for (Map.Entry<Integer, List<FormData>> entry : diffRow.entrySet()) {
				List<FormData> list = entry.getValue();
				// 生成bean
				Object vo = generator.create();
				// 给bean赋值
				BeanMap beanMap = BeanMap.create(vo);
				for (FormData ele : list) {
					beanMap.put(ele.getProperty(), ele.getValue());
				}
				vos.add(vo);
			}

			// 页面VO结果打印
			for (Object vo : vos) {
				System.out.println(JSON.toJSONString(vo));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlSession.close();
		}
	}
}

执行测试代码运行结果：

java 实现动态菜单 java动态table_mybatis