java 如何jfr java 如何通过代码mapper生成表

转载

码海航行侠 2023-12-03 19:51:55

文章标签 java 如何jfr xml java mybatis bc 文章分类 Java 后端开发

一，问题引入

当我们开发 mapper.xml ---->dao接层 ---->service接口---->serviceImp ---->controller层，

其中在mapper.xml编写查询语句的sql时会遇到sql查询到的结果

涉及到多张表的字段，或者单张表的字段过多时，

这时候我们就需写一个< resultMap>来封装一下这段sql的返回结果，这个< resultMap>标签长的样子如下面的图:

1.使用这个返回结果时是通过id的值找到这段定义的

java 如何jfr java 如何通过代码mapper生成表_xml

2.我们还要自定义一个java类来表示这个返回结果，这个java类的字段是property的值，类型是jdbcType所对应的java中的数据类型；

java 如何jfr java 如何通过代码mapper生成表_java_02

type的值等于这个java类的包名路径例如下面的type=“com.house.wym.entity.UserOrder”

java 如何jfr java 如何通过代码mapper生成表_bc_03

column 和jdbcType分别对应涉及到的表的字段和类型

java 如何jfr java 如何通过代码mapper生成表_xml_04

引出问题：我们在写下面这段xml以及java类时会消耗掉我们大量的时间

<resultMap type="com.house.wym.entity.UserOrder" id="UserOrderMapper">
		    <id column="o_id" 		jdbcType="INTEGER" property="oID" />
		<result column="h_id" 		jdbcType="INTEGER" property="hID"/>
		<result column="order_time" jdbcType="VARCHAR" property="orderTime"/>
		<result column="order_user" jdbcType="VARCHAR" property="orderUser"/>
		<result column="house_desc" jdbcType="VARCHAR" property="houseDesc"/>
		<result column="house_model" jdbcType="VARCHAR" property="houseModel"/>
		<result column="house_area" jdbcType="VARCHAR" property="houseArea"/>
		<result column="house_floor" jdbcType="VARCHAR" property="houseFloor"/>
		<result column="house_type" jdbcType="VARCHAR" property="houseType"/>
		<result column="house_price" jdbcType="INTEGER" property="housePrice"/>
		<result column="house_address" jdbcType="INTEGER" property="houseAddress"/>
		<result column="house_image" jdbcType="INTEGER" property="houseImage"/>
		<result column="community_name" jdbcType="INTEGER" property="communityName"/>
		<result column="house_linkman" jdbcType="INTEGER" property="houseLinkMan"/>
		<result column="house_oriented" jdbcType="INTEGER" property="houseOriented"/>
	</resultMap>

package com.house.wym.entity;

import java.util.Date;

public class UserOrder {
	private int oID;
	private int hID;
	private Date orderTime;
	private String orderUser;
	private String houseDesc;
	private String houseModel;
	private String houseArea;
	private String houseFloor;
	private String houseType;
	private int housePrice;
	private String houseAddress;
	private String houseImage;
	private String communityName;
	private String houseLinkMan;
	private String houseOriented;
	//省略geter，seter以及构造方法
}

我们能不能写一段代码只需要输入库名表名让他们帮我们自动生成
< result column=“h_id” jdbcType=“INTEGER” property=“hID”/>

二,思路

探索：当我们输入下面sql时

select column_name,data_type from information_schema.columns
where table_schema='house' and table_name in ('t_house','t_order')

得到表的列名和列名对应的类型

java 如何jfr java 如何通过代码mapper生成表_bc_05

我们可以通过给定的数据库名和表名字来查询出这几张表的所有字段和字段类型，

然后通过字符串拼接和处理来拼接出result和java字段，

然后就可以得到

和 private String 字段名;有了private String 字段名;在idea中直接右击点击generate 就可以生成getter和setter方法

三，代码

于是我们可以写出下面一个的代码来实现字段名和字段类型的读取

<?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">
<mapper namespace="com.house.wym.dao.UtilMapper">

    <select id="getType" resultType="map" parameterType="map">
        select column_name,data_type
        from information_schema.columns
        where table_schema = #{database} and table_name in
        <foreach item="item" index="index" collection="table_names" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>


</mapper>

package com.house.wym.dao;

import org.apache.ibatis.annotations.Mapper;

import java.util.List;
import java.util.Map;

@Mapper
public interface UtilMapper {
    public List<Map<String,String>> getType(Map<String,String> param);
}

package com.house.wym.controller;

import com.house.wym.dao.UtilMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import java.util.*;

@Controller
public class UtilController {
    @Autowired
    UtilMapper utilMapper;
    @RequestMapping("/toGettype")
    public String toGetTypePage(){
        return "getType";
    }
    //http://localhost:8090/generate?database=house&tables=t_house&tables=t_order
    @ResponseBody
    @RequestMapping("/generate")
    public String getType(HttpServletRequest request){
        //数据库名字，可以把house
        String database = request.getParameter("database");
        //数据库的表名字
        String tables = request.getParameter("tables");
        Map map = new HashMap<>();

        map.put("database",database);
        //数据库的表名字
        List list = new ArrayList();
        String[] tables_arr = tables.split(",");

        for (String name : tables_arr){
            list.add(name);
        }
        map.put("table_names",list);

        List result =utilMapper.getType(map);
        Map NameAndType = new HashMap();

        for(Object item : result){
            HashMap hashMap = (HashMap) item;
            String  columnName = (String) hashMap.get("COLUMN_NAME");
            String dataType = ((String) hashMap.get("DATA_TYPE")).toUpperCase();
            NameAndType.put(columnName,dataType);
        }
        StringBuilder stringBuilder =new StringBuilder();

        System.out.println("---------------生成Result 字符串----------------------");
        Iterator <String> iterator = NameAndType.keySet().iterator();
        while (iterator.hasNext()) {
            String key = iterator.next();
            String type = (String) NameAndType.get(key);
            String template ="<result column="+'\"'+key+'\"'+" jdbcType="+'\"'+type+'\"'+ " property="+'\"'+ replaceUnderlineAndfirstToUpper(key,"_","")+'\"'+"/>";
            System.out.println(template);
            stringBuilder.append(template);
            stringBuilder.append(System.getProperty("line.separator"));
        }
        System.out.println("---------------生成Entity字段----------------------");
        Iterator <String> iterator2 = NameAndType.keySet().iterator();
        while (iterator2.hasNext()) {
            String key = iterator2.next();
            String type = (String) NameAndType.get(key);

            String template ="private "+ MysqlToJava(type) + " " + replaceUnderlineAndfirstToUpper(key,"_","")+";";
            System.out.println(template);
            stringBuilder.append(template);
            stringBuilder.append(System.getProperty("line.separator"));
        }

        return  stringBuilder.toString();
    }
        //把mysql类型转成java类型
        public static String MysqlToJava(String type){
        String result="";
        switch(type) {
            case "INT":
                result = "int";
                break;
            case "TINYINT":
                result = "int";
                break;

            case "VARCHAR":
                result = "String";
                break;
            case "CHAR":
                result = "String";
                break;

            case "DATETIME":
                result = "Date";
                break;
            case "TIME":
                result = "Time";
                break;
            case "BIT":
                result = "Boolean";
                break;
        }
            return result;


        }

        /**
         * 首字母大写
         *
         * @param srcStr
         * @return
         */
        public static String firstCharacterToUpper(String srcStr) {
            return srcStr.substring(0, 1).toUpperCase() + srcStr.substring(1);
        }
        /**
         * 替换字符串并让它的下一个字母为大写 例如: create_time ---> createTime
         * @param srcStr
         * @param org
         * @param ob
         * @return
         */
        public static String replaceUnderlineAndfirstToUpper(String srcStr,String org,String ob)
        {
            String newString = "";
            int first=0;
            while(srcStr.indexOf(org)!=-1)
            {
                first=srcStr.indexOf(org);
                if(first!=srcStr.length())
                {
                    newString=newString+srcStr.substring(0,first)+ob;
                    srcStr=srcStr.substring(first+org.length(),srcStr.length());
                    srcStr=firstCharacterToUpper(srcStr);
                }
            }
            newString=newString+srcStr;
            return newString;
        }
    }

getType.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
         pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Insert title here</title>
    <link rel="stylesheet" href="${pageContext.request.contextPath }/static/layui/css/layui.css">
    <link rel="stylesheet" href="${pageContext.request.contextPath }/static/css/admin.css">
</head>
<body>
<ins class="adsbygoogle"
     style="display:inline-block;width:970px;height:90px"
     data-ad-client="ca-pub-6111334333458862"
     data-ad-slot="3820120620"></ins>
-->

<fieldset class="layui-elem-field layui-field-title" style="margin-top: 50px;">
    <legend>赋值和取值</legend>
</fieldset>

<form class="layui-form" action="" lay-filter="example">
    <div class="layui-form-item">
        <label class="layui-form-label">数据库名</label>
        <div class="layui-input-block">
            <input type="text" name="database" lay-verify="title" autocomplete="off" placeholder="请输入数据库名" class="layui-input">
        </div>
    </div>

    <div class="layui-form-item">
        <label class="layui-form-label">表名</label>
        <div class="layui-input-block">
            <input type="text" name="tables" lay-verify="title" autocomplete="off" placeholder="请输入表名" class="layui-input">
        </div>
    </div>

    <div class="layui-form-item">
        <button class="layui-btn" lay-submit="" lay-filter="demo1">提交</button>
    </div>
</form>

<script src="${pageContext.request.contextPath }/static/layui/layui.js"></script>
<script>
    layui.use(['form', 'layedit', 'laydate'], function(){
        var form = layui.form
            ,layer = layui.layer
            ,layedit = layui.layedit
            ,laydate = layui.laydate,
                $ = layui.jquery;
        //监听提交
        form.on('submit(demo1)', function(data){
            console.log(data.field)

            $.post("generate",data.field,function (result) {
                var index = layer.open({
                    type: 1,
                    title: false,
                    closeBtn: 0,
                    shadeClose: true,
                    skin: 'yourclass',
                    content: '<div><pre>'+ result+ '</pre></div>'
                });
                layer.full(index);
                console.log(data.field)
                console.log(result)

            });
            return false
        });

    });
</script>
</body>
</html>