SSM框架(七)Sping框架之SQL【Generator代码生成器】原理

前面SSM(四)模仿sping-mvc自定义一个框架中:
最后讲到:Sping框架通用SQLGenerator.java

一、Generator代码生成器有什么用?

条件:1、我们需要对两张 表做增删查改操作 2、如果需要对无数张 表进行增删查改操作;
思考:我们是不是要针对不同表在代码里一条一条写出查询语句?

@param o->UserInfo(id,name)
@return select * from user_info where user_id = ? and user_name = ?;
@param o->StudentInfo(id,name,number)
@return select * from t_student where t_stuid = ? and t_stuname = ? and t_stunumber = ?

由上述sql语句我们可以总结出:

SELECT column_name,column_name
FROM table_name
WHERE some_column=some_value;;

于是,我们采用Generator代码生成器,自动生成sql语句,不需要我们每次写出完整的增删查改

二、SQLGenerator.java示例

(1) 目录结构

下面用一个demo示例演示以上三种功能:
在这里插入图片描述

(2)实体+注解

(注解标签使用方法参考:SSM框架(四)-注解部分

ColumnTable两个接口里定义了2类注解:

  • @Retention : 元注解 --生命周期在运行时
  • @Target :注解可以用在类/接口,方法、字段上
    在这里插入图片描述在这里插入图片描述

-》 定义一个UserInfo类和一个student类,写main函数测试结果如下:
(运行时类引用了Table接口、每个元素与(数据库)这里我们与Column相联系)在这里插入图片描述在这里插入图片描述
UserInfo:

package com.cungudafa.annotation.demo;

@Table("user_info")
public class UserInfo {//user_info表
	
	@Column("user_id")
	private Integer userId;//user_id字段
	
	@Column("user_name")
	private String userName;//user_name字段

	public Integer getUserId() {
		return userId;
	}

	public void setUserId(Integer userId) {
		this.userId = userId;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}
}

StudentInfo:

package com.cungudafa.annotation.demo;

@Table("t_student")
public class StudentInfo {
	@Column("t_stuid")
	private Integer id;
	@Column("t_stuname")
	private String studentName;
	@Column("t_stunumber")
	private String studentNumber;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getStudentName() {
		return studentName;
	}
	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}
	public String getStudentNumber() {
		return studentNumber;
	}
	public void setStudentNumber(String studentNumber) {
		this.studentNumber = studentNumber;
	}
}

(3)Generator代码生成器

定义SQLGenerator.java

  • 模拟数据库请求操作时,对sql语句变量的控制
    例:select * from user_info where 1=1 and user_name = 'zhangsan' and user_id = 10001
    select * from 表名 where 直接写入条件 and 查询关键词1 = '字符串' and 查询关键词2 = 数字
    package com.cungudafa.annotation.demo;
    
    import java.lang.reflect.Field;
    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * 生成SQL
     * @author Administrator
     *
     */
    public class SQLGenerator {
    	
    	public static String createInsertSql(Object o){
    		return null;//待补充
    	}
    	
    	public static String createDeleteSql(Object o){
    		return null;
    	}
    	
    	public static String createUpdateSql(Object o){
    		return null;
    	}
    	
    	/**
    	 * 
    	 * @param o->UserInfo(id,name)
    	 * @return select * from user_info where user_id = ? and user_name = ?;
    	 * @throws InvocationTargetException 
    	 * @throws IllegalArgumentException 
    	 * @throws IllegalAccessException 
    	 * @throws SecurityException 
    	 * @throws NoSuchFieldException 
    	 */
    	public static String createSelectSql(Object o) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchFieldException, SecurityException{
    		
    		//获取传入对象的运行时类
    		Class c = o.getClass();
    		//判断该类是否有@Table注解
    		if(c.isAnnotationPresent(Table.class)){
    			String sql = "select * from ";
    			Table t = (Table) c.getAnnotation(Table.class);
    			String tableName = t.value();
    			sql = sql + tableName + " where 1=1 ";
    			//获取所有的get方法
    			Method[] methods = c.getDeclaredMethods();
    			List<Method> getMethods = new ArrayList<>();
    			for(Method method : methods){
    				if(method.getName().startsWith("get")){
    					getMethods.add(method);
    				}
    			}
    			
    			//遍历get方法
    			for(Method method : getMethods){
    				Object value = method.invoke(o);
    				if(value != null){
    					String methodName = method.getName();//getUserId/getUserName
    					System.out.println("methodName = " + methodName);
    					//getUserId-->userId
    					String fieldName = methodName.replaceAll("get", "");//UserId
    					fieldName = fieldName.substring(0, 1).toLowerCase() + fieldName.substring(1);//UserId->userId
    					System.out.println("fieldName = " + fieldName);
    					//获得字段
    					Field field = c.getDeclaredField(fieldName);
    					System.out.println("field = " + field);
    					//判断字段上是否有Column注解
    					if(field.isAnnotationPresent(Column.class)){
    						//获得字段上的Column注解
    						Column col = field.getAnnotation(Column.class);
    						//获得注解的value值,作为列名
    						String columnName = col.value();
    						if(value instanceof Integer){//区分id学号String转int(数据库为int)
    							sql = sql + "and " + columnName + " = " + String.valueOf(value) + " ";
    						}else{
    							sql = sql + "and " + columnName + " = '" + String.valueOf(value) + "' ";
    						}
    					}
    				}
    			}
    			return sql;
    		}else{
    			return null;
    		}
    	}
    }
    

-》 测试结果:(两张表都可以通用SQLGenerator框架)

sql = select * from user_info where 1=1 and user_name = ‘zhangsan’ and user_id = 10001
sql = select * from t_student where 1=1 and t_stuid = 10002 and t_stuname = ‘李四’ and t_stunumber = ‘102001203103’

在这里插入图片描述

三、完整SQLGenerator.java

自定义SQLGenerator:

package com.cungudafa.annotation.demo;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import javax.swing.filechooser.FileNameExtensionFilter;

/**
 * 生成SQL
 * @author Administrator
 *
 */
public class SQLGenerator {
	/**
	 * 插入
	 * @param o->UserInfo(id,name)
	 * @return insert into t_student vlaues (?, ?, ?) 
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 * @throws IllegalAccessException 
	 * @throws SecurityException 
	 * @throws NoSuchFieldException 
	 */
	public static String createInsertSql(Object o) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchFieldException, SecurityException{
		//获取传入对象的运行时类
		Class c = o.getClass();
		//判断该类是否有@Table注解
		if(c.isAnnotationPresent(Table.class)){
			String sql = "insert into ";
			Table t = (Table) c.getAnnotation(Table.class);
			String tableName = t.value();
			sql = sql + tableName + " vlaues (";
			//获取所有的get方法
			Method[] methods = c.getDeclaredMethods();
			List<Method> getMethods = new ArrayList<>();
			for(Method method : methods){
				if(method.getName().startsWith("get")){
					getMethods.add(method);
				}
			}
			//遍历get方法
			for(Method method : getMethods){
				Object value = method.invoke(o);
				if(value != null){
					String methodName = method.getName();//getUserId/getUserName
					//System.out.println("methodName = " + methodName);
					//getUserId-->userId
					String fieldName = methodName.replaceAll("get", "");//UserId
					fieldName = fieldName.substring(0, 1).toLowerCase() + fieldName.substring(1);//UserId->userId
					//System.out.println("fieldName = " + fieldName);
					//获得字段
					Field field = c.getDeclaredField(fieldName);
					//System.out.println("field = " + field);
					//判断字段上是否有Column注解
					if(field.isAnnotationPresent(Column.class)){
						//获得字段上的Column注解
						Column col = field.getAnnotation(Column.class);
						//获得注解的value值,作为列名
						String columnName = col.value();					
						if(value instanceof Integer){//区分id学号String转int(数据库为int)
							sql = sql + String.valueOf(value) + ",";
						}else{
							sql = sql +" '" + String.valueOf(value) + "',";
						}
					}
				}
			}
			sql = sql.substring(0,sql.length()-1) + ")";//删去sql句末多余的“,”
			return sql;
		}else{
			return null;
		}
	}
	/**
	 * 删除
	 * @param o->UserInfo(id,name)
	 * @return DELETE FROM t_student where and t_stuid = ?
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 * @throws IllegalAccessException 
	 * @throws SecurityException 
	 * @throws NoSuchFieldException 
	 */
	public static String createDeleteSql(Object o) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchFieldException, SecurityException{

		//获取传入对象的运行时类
		Class c = o.getClass();
		//判断该类是否有@Table注解
		if(c.isAnnotationPresent(Table.class)){
			String sql = "DELETE FROM ";
			Table t = (Table) c.getAnnotation(Table.class);
			String tableName = t.value();
			sql = sql + tableName + " where ";
			//获取所有的get方法
			Method[] methods = c.getDeclaredMethods();
			List<Method> getMethods = new ArrayList<>();
			for(Method method : methods){
				if(method.getName().startsWith("get")){
					getMethods.add(method);
				}
			}
			
			//遍历get方法
			for(Method method : getMethods){
				Object value = method.invoke(o);
				if(value != null){
					String methodName = method.getName();//getUserId/getUserName
					//System.out.println("methodName = " + methodName);
					//getUserId-->userId
					String fieldName = methodName.replaceAll("get", "");//UserId
					fieldName = fieldName.substring(0, 1).toLowerCase() + fieldName.substring(1);//UserId->userId
					//System.out.println("fieldName = " + fieldName);
					//获得字段
					Field field = c.getDeclaredField(fieldName);
					//System.out.println("field = " + field);
					//判断字段上是否有Column注解
					if(field.isAnnotationPresent(Column.class)){
						//获得字段上的Column注解
						Column col = field.getAnnotation(Column.class);
						//获得注解的value值,作为列名
						String columnName = col.value();
						if(value instanceof Integer){//区分id学号String转int(数据库为int)
							sql = sql + columnName + " = " + String.valueOf(value) + " and ";
						}else{
							sql = sql + columnName + " = '" + String.valueOf(value) + "' and ";
						}
					}
				}
			}
			sql=sql.substring(0,sql.length()-4);//删去sql句末多余的“and ”
			return sql;
		}else{
			return null;
		}
	}
	

	/**
	 * 更新
	 * @param o->UserInfo(id,name)
	 * @return UPDATE table_name
				SET column1=value1,column2=value2,...
				WHERE some_column=some_value;
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 * @throws IllegalAccessException 
	 * @throws SecurityException 
	 * @throws NoSuchFieldException 
	 */
	public static String createUpdateSql(Object o,Object o1) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchFieldException, SecurityException{
		//获取传入对象的运行时类
		Class c = o.getClass();
		Class c1 = o1.getClass();
		//判断该类是否有@Table注解
		if(c.isAnnotationPresent(Table.class)){
			String sql = "update ";
			Table t = (Table) c.getAnnotation(Table.class);
			String tableName = t.value();
			sql = sql + tableName + " set ";
			//获取所有的get方法
			Method[] methods = c.getDeclaredMethods();
			List<Method> getMethods = new ArrayList<>();
			for(Method method : methods){
				if(method.getName().startsWith("get")){
					getMethods.add(method);
				}
			}
			//遍历get方法
			for(Method method : getMethods){
				Object value = method.invoke(o1);
				if(value != null){
					String methodName = method.getName();//getUserId/getUserName
					//System.out.println("methodName = " + methodName);
					//getUserId-->userId
					String fieldName = methodName.replaceAll("get", "");//UserId
					fieldName = fieldName.substring(0, 1).toLowerCase() + fieldName.substring(1);//UserId->userId大写转小写
					//System.out.println("fieldName = " + fieldName);
					//获得字段
					Field field = c.getDeclaredField(fieldName);
					//System.out.println("field = " + field);
					//判断字段上是否有Column注解
					if(field.isAnnotationPresent(Column.class)){
						//获得字段上的Column注解
						Column col = field.getAnnotation(Column.class);
						//获得注解的value值,作为列名
						String columnName = col.value();
						if(value instanceof Integer){//区分id学号String转int(数据库为int)
							sql = sql + columnName + " = " + String.valueOf(value) + " and ";
						}else{
							sql = sql + columnName + " = '" + String.valueOf(value) + "' and ";
						}
					}
				}
			}
			sql = sql.substring(0,sql.length()-4)+"where ";//删去sql句末多余的“and ”
			//获取所有的get方法
			methods = c.getDeclaredMethods();
			getMethods = new ArrayList<>();
			for(Method method : methods){
				if(method.getName().startsWith("get")){
					getMethods.add(method);
				}
			}
			
			//遍历get方法
			for(Method method : getMethods){
				Object value = method.invoke(o);
				if(value != null){
					String methodName = method.getName();//getUserId/getUserName
					//System.out.println("methodName = " + methodName);
					//getUserId-->userId
					String fieldName = methodName.replaceAll("get", "");//UserId
					fieldName = fieldName.substring(0, 1).toLowerCase() + fieldName.substring(1);//UserId->userId
					//System.out.println("fieldName = " + fieldName);
					//获得字段
					Field field = c.getDeclaredField(fieldName);
					//System.out.println("field = " + field);
					//判断字段上是否有Column注解
					if(field.isAnnotationPresent(Column.class)){
						//获得字段上的Column注解
						Column col = field.getAnnotation(Column.class);
						//获得注解的value值,作为列名
						String columnName = col.value();
						if(value instanceof Integer){//区分id学号String转int(数据库为int)
							sql = sql + columnName + " = " + String.valueOf(value) + " and ";
						}else{
							sql = sql + columnName + " = '" + String.valueOf(value) + "' and ";
						}
					}
				}
			}
			sql = sql.substring(0,sql.length()-4);
			return sql;
		}else{
			return null;
		}
	}
	

	
	/**
	 * 查询
	 * @param o->UserInfo(id,name)
	 * @return select * from user_info where user_id = ? and user_name = ?;
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 * @throws IllegalAccessException 
	 * @throws SecurityException 
	 * @throws NoSuchFieldException 
	 */
	public static String createSelectSql(Object o) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchFieldException, SecurityException{
		
		//获取传入对象的运行时类
		Class c = o.getClass();
		//判断该类是否有@Table注解
		if(c.isAnnotationPresent(Table.class)){
			String sql = "select * from ";
			Table t = (Table) c.getAnnotation(Table.class);
			String tableName = t.value();
			sql = sql + tableName + " where 1=1 ";
			//获取所有的get方法
			Method[] methods = c.getDeclaredMethods();
			List<Method> getMethods = new ArrayList<>();
			for(Method method : methods){
				if(method.getName().startsWith("get")){
					getMethods.add(method);
				}
			}
			
			//遍历get方法
			for(Method method : getMethods){
				Object value = method.invoke(o);
				if(value != null){
					String methodName = method.getName();//getUserId/getUserName
					System.out.println("methodName = " + methodName);
					//getUserId-->userId
					String fieldName = methodName.replaceAll("get", "");//UserId
					fieldName = fieldName.substring(0, 1).toLowerCase() + fieldName.substring(1);//UserId->userId
					System.out.println("fieldName = " + fieldName);
					//获得字段
					Field field = c.getDeclaredField(fieldName);
					System.out.println("field = " + field);
					//判断字段上是否有Column注解
					if(field.isAnnotationPresent(Column.class)){
						//获得字段上的Column注解
						Column col = field.getAnnotation(Column.class);
						//获得注解的value值,作为列名
						String columnName = col.value();
						if(value instanceof Integer){//区分id学号String转int(数据库为int)
							sql = sql + "and " + columnName + " = " + String.valueOf(value) + " ";
						}else{
							sql = sql + "and " + columnName + " = '" + String.valueOf(value) + "' ";
						}
					}
				}
			}
			return sql;
		}else{
			return null;
		}
	}
}
  • 测试main函数:
    TestCase:
package com.cungudafa.annotation.demo;

import java.lang.reflect.InvocationTargetException;

public class TestCase {
	public static void main(String[] args) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchFieldException, SecurityException {
		UserInfo userInfo = new UserInfo();
		userInfo.setUserId(10001);
		userInfo.setUserName("zhangsan");
		String sql = SQLGenerator.createSelectSql(userInfo);
		System.out.println("sql = " + sql);
		
		System.out.println("--------查询-----------");
		StudentInfo s = new StudentInfo();
		s.setId(10002);
		s.setStudentName("李四");
		s.setStudentNumber("102001203103");
		sql = SQLGenerator.createSelectSql(s);
		System.out.println("--------查询-----------");
		System.out.println("sql = " + sql);
		
		System.out.println("--------插入-----------");
		StudentInfo s1 = new StudentInfo();
		s1.setId(10002);//插入内容
		s1.setStudentName("李四");
		s1.setStudentNumber("102001203103");
		sql = SQLGenerator.createInsertSql(s1);
		System.out.println("sql = " + sql);
		
		System.out.println("--------删除-----------");
		StudentInfo s2 = new StudentInfo();
		s2.setId(10002);//删除条件任取
		s2.setStudentName("李四");
		s2.setStudentNumber("102001203103");
		sql = SQLGenerator.createDeleteSql(s2);
		System.out.println("sql = " + sql);
		
		System.out.println("--------更新studentinfo-----------");
		StudentInfo s3 = new StudentInfo();
		s3.setId(10002);//默认s3内容
		s3.setStudentName("李四");
		s3.setStudentNumber("102001203103");
		//修改内容
		StudentInfo s3_update = new StudentInfo();
		//s3_update.setId(s3.getId());//获得原始id,id唯一不可更改
		s3_update.setStudentName("张三");
		s3_update.setStudentNumber("0123456789");
		sql = SQLGenerator.createUpdateSql(s3,s3_update);
		System.out.println("sql = " + sql);
		
		System.out.println("\n--------userinfo表:-----------");
		UserInfo userInfo1 = new UserInfo();
		userInfo1.setUserId(1001);
		userInfo1.setUserName("user001");
		sql = SQLGenerator.createSelectSql(userInfo1);
		System.out.println("\nSelectsql = " + sql);//查	
		sql = SQLGenerator.createInsertSql(userInfo1);
		System.out.println("\nInsertsql = " + sql);//增
		userInfo1.setUserName("user123");//修改姓名
		sql = SQLGenerator.createUpdateSql(userInfo,userInfo1);//user001->user123
		System.out.println("\nUpdatesql = " + sql);//改
		sql = SQLGenerator.createDeleteSql(userInfo1);
		System.out.println("\nDeletesql = " + sql);//删
	}
}
  • 结果:
    在这里插入图片描述
  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值