Java编程批量生成创建触发器的sql语句

情景:目前有100多个表,每个表都要创建两个触发器并测试。一个个写估计要写一个星期,但是我用编程半天搞定。

第一步  收集表信息

 收集所有表的表名,字段名

--查询所有表名:
select t.table_name from user_tables t;
--查询指定表的所有字段名:
select t.column_name from user_col_comments t where t.table_name = 'T_XXX';

格式如:表名,主键名,字段名1,字段名2,字段名3,字段名4,字段名5,字段名6,字段名7,字段名8,...

 用逗号分隔

 保存在名为 "表信息.txt" 的文件下。


第二步  写Java程序,生成创建触发器sql

    

package util;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;

public class 生成触发器代码 {

	public static void main(String[] args) {
		//读取的文件路径
		 String filePath = "C:\\表信息.txt";   
	        try {  
	        	//输出的文件路径
	        	FileWriter fw = new FileWriter("C:\\创建触发器.sql", true);
	            BufferedWriter bw = new BufferedWriter(fw);
	            try (BufferedReader bufReader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF-8")))//数据流读取文件  
	            {  
	                String temp = null;  
	                while ((temp = bufReader.readLine()) != null) { 
	                	String[] daima = temp.split(",");
	                	if(daima.length>1){
		                    System.out.print(1);  
		    	            bw.append(generate(daima));// 往已有的文件上添加字符串
		                    
	                	}
	                }  
	                bufReader.close();
		            bw.close();
		            fw.close();
	            }  
	        } catch (Exception e) {  
	            e.printStackTrace();  
	        }  

	}
	/**
	 * 生成创建一个表的触发器的sql
	 * @param daima  表名,字段名
	 * @return
	 */
	public static String generate(String[] daima){
		StringBuilder sb = new StringBuilder();
		sb.append("create or replace trigger tri_"+daima[0]+"_add\n"
						+ "  after insert on " + daima[0] + "\n"
						+ "  for each row\n"
						+ "begin\n"
						+ "  insert into " + daima[0] + "@wer\n"
								+ "(" + daima[1]);
		for(int i = 2 ; i < daima.length ; i++){
			if(!"QZKRKSJ".equals(daima[i])&& !"QZKXGSJ".equals(daima[i]) && !"".equals(daima[i])){
				sb.append(", "+ daima[i]);
			}
		}
		sb.append(")\n"
				+ "    values\n(:new." + daima[1]);
		for(int i = 2 ; i < daima.length ; i++){

			if(!"QZKRKSJ".equals(daima[i])&& !"QZKXGSJ".equals(daima[i]) && !"".equals(daima[i])){

				sb.append(",:new."+ daima[i]);
			}
		}
		sb.append(");\nend tri_" + daima[0] + "_add;\n"
								+ "/\n"
								+ "alter trigger tri_" + daima[0] + "_add enable;\n");
		
		sb.append("create or replace trigger tri_"+daima[0]+"_upd\n"
				+ "  after update on " + daima[0] + "\n"
				+ "  for each row\n"
				+ "declare\n  v_time varchar2(20);\n"
				+ "begin\nselect to_char(sysdate,'yyyymmddhh24miss') into v_time from dual@wer;\n"
				+ "   update " + daima[0] + "@wer set\n");
		for(int i = 2 ; i < daima.length ; i++){
			if(!"QZKRKSJ".equals(daima[i])&& !"QZKXGSJ".equals(daima[i]) && !"".equals(daima[i])){
				sb.append( daima[i] + "=:new."+ daima[i] + ",");
			}
		}
		sb.append("\nSTXGSJ=v_time\n"
				+ "    where " + daima[1] + "=:new."+ daima[1] + ";\n"
						+ "end tri_" + daima[0] + "_upd;\n"
								+ "/\n"
								+ "alter trigger tri_" + daima[0] + "_upd enable;\n\n");
		
		return sb.toString();
	}

}

生成sql的格式如下:


每个创建触发器语句后面都有一个斜杠 '/ ',是为了能批量创建触发器。

 没有斜杠的话需要一个个手动选中sql执行。

第三步  生成测试数据insert,update的sql

 测试insert触发器需要insert数据,我这里除了日期字段插入to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),其他都插入 '5'。   

package util;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;

public class 生成insert语句 {



	public static void main(String[] args) {
		 String filePath = "C:\\表信息.txt"; //读取的文件夹路径  
	        try {  
	        	//输出的文件夹路径
	        	FileWriter fw = new FileWriter("C:\\insert.sql", true);
	            BufferedWriter bw = new BufferedWriter(fw);
	            try (BufferedReader bufReader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF-8")))//数据流读取文件  
	            {  
	                String temp = null;  
	                while ((temp = bufReader.readLine()) != null) { 
	                	String[] daima = temp.split(",");
	                	if(daima.length>1){
		                    System.out.print(1);  
		    	            bw.append(gen(daima));// 往已有的文件上添加字符串
		                    
	                	}
	                }  
	                bufReader.close();
		            bw.close();
		            fw.close();
	            }  
	        } catch (Exception e) {  
	            e.printStackTrace();  
	        }  

	}
	public static String gen(String[] daima){
		StringBuilder sb = new StringBuilder();
		sb.append("insert into "+daima[0]+"\n"
						+ "  ( " + daima[1]);
		for(int i = 2 ; i < daima.length ; i++){
			if(!"".equals(daima[i])){
				sb.append(", "+ daima[i]);
			}
		}
		sb.append(")\n"
				+ "    values\n( '5'");
		for(int i = 2 ; i < daima.length ; i++){

			if(!"".equals(daima[i]) && (daima[i].endsWith("SJ") || daima[i].endsWith("RQ"))){
				sb.append(",to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss')");
			}else if(!"".equals(daima[i]) && (daima[i].endsWith("SJLY_DSDM")) ){
				sb.append(",'12345'");
			}else if (!"".equals(daima[i])){
				sb.append(",'5'");
			}
		}
		sb.append(");\n\n");
		
		
		return sb.toString();
	}

} 

生成的sql语句截图:


测试update触发器同理,把非主键,非日期的字段值改成 '6' ,稍微改一下就行。

package util;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;

public class 生成update语句 {



	public static void main(String[] args) {
		 String filePath = "C:\\表信息.txt"; //读取的文件夹路径  
	        try {  
	        	//输出的文件夹路径
	        	FileWriter fw = new FileWriter("C:\\update.sql", true);
	            BufferedWriter bw = new BufferedWriter(fw);
	            try (BufferedReader bufReader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF-8")))//数据流读取文件  
	            {  
	                String temp = null;  
	                while ((temp = bufReader.readLine()) != null) { 
	                	String[] daima = temp.split(",");
	                	if(daima.length>1){
		                    System.out.print(1);  
		    	            bw.append(gen(daima));// 往已有的文件上添加字符串
		                    
	                	}
	                }  
	                bufReader.close();
		            bw.close();
		            fw.close();
	            }  
	        } catch (Exception e) {  
	            e.printStackTrace();  
	        }  

	}
	public static String gen(String[] daima){
		StringBuilder sb = new StringBuilder();
		sb.append("UPDATE "+daima[0]+"\n"
				+ "SET \n" + daima[2] + "='6'");
		for(int i = 3 ; i < 5 ; i++){
			if(!"".equals(daima[i]) && daima[i].endsWith("SJ")){
				sb.append("," + daima[i] + "= to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss')");
			}else if (!"".equals(daima[i])){
				sb.append("," + daima[i] + " = '6'\n");
			}
		}
		sb.append("where "+ daima[1] +" = '5';\n\n");
		
		
		return sb.toString();
	}

}

生成的sql截图:


第四步  删除测试数据

   删除主键值为 '5' 的数据。

package util;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;

public class 生成delete语句 {



	public static void main(String[] args) {
		 String filePath = "C:\\表信息.txt"; //读取的文件夹路径  
	        try {  
	        	//输出的文件夹路径
	        	FileWriter fw = new FileWriter("C:\\delete.sql", true);
	            BufferedWriter bw = new BufferedWriter(fw);
	            try (BufferedReader bufReader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF-8")))//数据流读取文件  
	            {  
	                String temp = null;  
	                while ((temp = bufReader.readLine()) != null) { 
	                	String[] daima = temp.split(",");
	                	if(daima.length>1){
		                    System.out.print(1);  
		    	            bw.append(gen(daima));// 往已有的文件上添加字符串
		                    
	                	}
	                }  
	                bufReader.close();
		            bw.close();
		            fw.close();
	            }  
	        } catch (Exception e) {  
	            e.printStackTrace();  
	        }  

	}
	// DELETE FROM Person WHERE LastName = 'Wilson' 
	public static String gen(String[] daima){
		StringBuilder sb = new StringBuilder();
		sb.append("DELETE FROM "+daima[0]+" WHERE " + daima[1] + "='5' ; \n");
		return sb.toString();
	}

}

 生成sql语句截图:



 总结:重点是收集表信息,表信息有了什么都好说,可以用表信息做任何事情。

拒绝机械式工作。



  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 5
    评论
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值