前端JS连接Mysql，批量生成CRUD的页面
前端程序猿的福音，偷懒摸鱼必备良品，可以将几天的工作任务缩短到半天时间。
包含两个文件，一个js一个vue，vue文件是作为模板文件用来替换变量的，js是作为执行文件。
提供一下我自己的执行方法：
1.在webstorm中打开项目
2.在右上角，打开Edit Configurations
3.添加一个node.js执行程序
4.选择好build-page.js的位置
5.我的文件位置是放置在项目中
6.修改好文件中的配置信息和模板后，可以执行批量生成
两个文件的代码如下，供参考，前端UI框架用的是Element-UI
build-page.js
/** * 根据mysql表属性批量生成vue页面 */ // 1 引入 const mysql = require('mysql'); // npm install mysql const fs = require('fs'); const path = require('path'); // 2 创建链接配置 const config = { host: 'localhost', // 主机名 （服务器地址） port: '3306', user: 'root', //用户名 password: '123456', // 密码 database: 'meta-lounge', // 写上自己要连接的数据库名字 }; const conn = mysql.createConnection(config); // 3 建立链接 console.log(`建立mysql连接成功，地址：${config.host}:${config.port}/${config.database}`); conn.connect(); //4 一些配置>>>>>>>>此处需要自定义 const tables = "meta_collection_transaction"; // 表名，逗号分隔 const tablePrefix = "meta_"; // 表前缀，无可空，提示不同表前缀的表可以分开导入 const ignoreColumns = ['id', 'is_remove', 'create_time']; // 表中忽略的属性字段，不参与生成 //5 执行sql语句，循环查询表中列属性 let tablesArr = tables.split(","); tablesArr.forEach(tableName => { let queryAllColumnsSql = `SELECT COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT FROM information_schema.COLUMNS where table_schema='${config.database}' and table_name = '${tableName}';` conn.query(queryAllColumnsSql, (err, result) => { if (err) { console.log(err); return } let columns = []; // 获取所有列名 result.forEach(column => { columns.push({ name: column.COLUMN_NAME, type: column.DATA_TYPE, label: column.COLUMN_COMMENT }); }); buildPage(tableName, columns); console.log("生成[" + tableName + "]页面成功。。。"); }) }); //6 关闭mysql连接 conn.end(function (err) { if (err) { return console.log('error:' + err.message); } console.log('数据库连接已关闭'); }); // 生成页面 function buildPage(tableName, columns) { tableName = tableName.replace(tablePrefix, ''); let fileName = toCamel(tableName.substring(0, 1).toUpperCase() + tableName.substring(1)) + "Manage"; var paths = path.resolve(`./src/components/menus`) fs.mkdir(paths, function () { let fff = fs.readFileSync(path.resolve('./src/auto-build-page/template.vue')); let str = handleStr(fff.toString(), tableName, columns) // 写入文件 fs.writeFile(paths + `/${fileName}.vue`, str, {'flag': 'a'}, function (err) { if (err) { throw err } }) }) } // 替换文本 function handleStr(str, tableName, columns) { str = str.replace(/%tableName%/g, tableName); // 生成tableColumn let tableColumnStr = ""; let formItemStr = ""; columns.forEach(({name, type, label}) => { if (ignoreColumns.indexOf(name) < 0) { name = toCamel(name); tableColumnStr += `<el-table-column prop="${name}" label="${label}"></el-table-column>\n`; formItemStr += getFormItemByType(name, type, label); } }) str = str.replace('%tableColumn%', tableColumnStr); str = str.replace('%formItem%', formItemStr); return str } // 下划线转驼峰 function toCamel(str) { return str.replace(/([^_])(?:_+([^_]))/g, function ($0, $1, $2) { return $1 + $2.toUpperCase(); }); } // 根据属性不同类型，生成不同的输入框类型 function getFormItemByType(name, type, label) { // varchar,bit,int,bigint,datetime,text, if (type === 'datetime') { return `<el-form-item label="${label}" prop="${name}">` + `<el-date-picker v-model="cmd.${name}" type="datetime" placeholder="请选择${label}"></el-date-picker>` + `</el-form-item>\n`; } else if (type === 'bit') { return `<el-form-item label="${label}" prop="${name}">` + `<el-radio-group v-model="cmd.${name}">` + `<el-radio :label="true" >是</el-radio>` + `<el-radio :label="false">否</el-radio>` + `</el-radio-group>` + `</el-form-item>\n`; } else if (type === 'int') { return `<el-form-item label="${label}" prop="${name}">` + `<el-input v-model="cmd.${name}" placeholder="请输入${label}" type="number"></el-input>` + `</el-form-item>\n`; } else if (type === 'text') { return `<el-form-item label="${label}" prop="${name}">` + `<el-input v-model="cmd.${name}" placeholder="请输入${label}" type="textarea"></el-input>` + `</el-form-item>\n`; } return `<el-form-item label="${label}" prop="${name}">` + `<el-input v-model="cmd.${name}" placeholder="请输入${label}"></el-input>` + `</el-form-item>\n`; }
template.vue
<template> <div> <div class="menu-title"> %tableName%管理 </div> <div class="menu-content"> <div> <el-form :inline="true" :model="qo" class="demo-form-inline"> <el-form-item> <el-input v-model="qo.LIKES_name" placeholder="请输入名称"></el-input> </el-form-item> <el-form-item> <el-button type="primary" icon="el-icon-search" @click="qo.pageNo=1;queryData()">搜 索</el-button> </el-form-item> </el-form> </div> <div style="margin: 0px 0 20px 0"> <el-button type="primary" @click="createRow()">添加</el-button> </div> <div> <el-table :data="result.records" style="width: 100%"> %tableColumn% <el-table-column label="操作" width="160"> <template slot-scope="scope"> <el-button @click.native.prevent="modifyRow(scope.row)" type="text" size="small"> 编辑 </el-button> <el-button @click.native.prevent="removeRow(scope.row)" type="text" size="small"> 删除 </el-button> </template> </el-table-column> </el-table> </div> <div class="page-box"> <el-pagination background @current-change="handleCurrentChange" :current-page="qo.pageNo" :page-size="qo.pageSize" layout="total, prev, pager, next" :total="result.total"> </el-pagination> </div> </div> <el-dialog :title="dialogName" :visible.sync="cmdDialogVisible" width="40%"> <el-form ref="cmd" label-width="80px" :rules="rules" :model="cmd"> %formItem% </el-form> <span slot="footer" class="dialog-footer"> <el-button @click="cmdDialogVisible = false">取 消</el-button> <el-button type="primary" @click="submitModify">确 定</el-button> </span> </el-dialog> </div> </template> <script> import request from '@/utils/request'; var _this; export default { name: '%tableName%', data() { return { qo: { pageNo: 1, pageSize: 10, LIKES_name: '', }, result: { records: [], total: 0 }, dialogName: '编辑', cmdDialogVisible: false, cmd: {}, rules: { name: [ {required: true, message: '请输入菜单名称'} ], } } }, mounted() { _this = this; _this.queryData(); }, methods: { queryData() { request({ url: `/%tableName%/query_pages`, method: 'post', data: _this.qo }).then(res => { _this.result.records = res.data.records; _this.result.total = res.data.total; }); }, handleCurrentChange(val) { _this.qo.pageNo = val; _this.queryData(); }, removeRow(item) { _this.$confirm('此操作将永久删除记录, 是否继续?', '提示', { confirmButtonText: '确定', cancelButtonText: '取消', type: 'warning' }).then(() => { request({ url: `/%tableName%/remove/${item.id}`, method: 'post' }).then(res => { _this.$message.success("删除成功"); _this.queryData(); }); }).catch(() => { }); }, createRow() { _this.dialogName = '添加'; _this.cmdDialogVisible = true; _this.$nextTick(() => { _this.cmd = { name: "", }; _this.$refs.cmd.resetFields(); }); }, modifyRow(item) { _this.dialogName = '编辑'; _this.cmdDialogVisible = true; _this.$nextTick(() => { _this.cmd = JSON.parse(JSON.stringify(item)); _this.$refs.cmd.resetFields(); }); }, submitModify() { this.$refs.cmd.validate((valid) => { if (valid) { if (_this.cmd.id && _this.cmd.id.toString.length > 0) { request({ url: `/%tableName%/modify`, method: 'post', data: _this.cmd }).then(res => { _this.$message.success("编辑成功"); _this.cmdDialogVisible = false; _this.queryData(); }); } else { request({ url: `/%tableName%/create`, method: 'post', data: _this.cmd }).then(res => { _this.$message.success("添加成功"); _this.cmdDialogVisible = false; _this.queryData(); }); } } }); }, } } </script> <style scoped lang="scss"> </style>
