大匠运斤-------利用注解或者XML解析的方式 生成SQL语句

我们在进行一些软件开发的时候，往往会要求实现增删改查的功能，这一般都要用数据库的连接以及sql语句。

1. 对于这些制式的sql代码，能够做成一套工具进行自动生成当然是很nice的
2. 对于我们的model类，我们对应有一个表，我们可以利用写xml文件，或者进行注解来实现他们之间的相互对应，
3. 并且对应之后利用反射机制，最终形成sql的生成
4. 我们做好三个类
5. 接下来直接上代码喽！

/*
 * 将 table里面的column形成列表
 * 并且为sql提供了相应的一些 内容（比如列名等）
 */

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ClassTable {
	private Class<?> klass;
	private String table;
	private List<PropertyColumn> fieldList;
	private PropertyColumn id;
	
	ClassTable() {
		fieldList = new ArrayList<>();
	}

	void setFieldFromResultSet(ResultSet resultSet, Object object) {
		for (PropertyColumn field : fieldList) {
			field.setProperty(resultSet, object);
		}
	}
	
	void addProperty(PropertyColumn property) {
		fieldList.add(property);
	}
	
	String getIdString() {
		return table + "." + id.getColumn();
	}
	
	String getColumnString() {
		//给出了 SQL 语句中要用的全部列名  
		StringBuffer res = new StringBuffer();
		
		boolean first = true;
		for (PropertyColumn field : fieldList) {
			res.append(first ? "" : ", ")
				.append(table).append('.')
				.append(field.getColumn());
			
			first = false;
		}
		
		return res.toString();
	}
	
	Class<?> getKlass() {
		return klass;
	}

	void setKlass(Class<?> klass) {
		this.klass = klass;
	}

	String getTable() {
		return table;
	}

	void setTable(String table) {
		this.table = table;
	}

	PropertyColumn getId() {
		return id;
	}

	void setId(PropertyColumn id) {
		this.id = id;
	}
	
	int getFieldCount() {
		return fieldList.size();
	}
	
	@Override
	public String toString() {
		StringBuffer res = new StringBuffer("绫�:");
		
		res.append(klass.getName()).append('\n')
				.append("琛�:").append(table).append('\n');
		for (PropertyColumn pc : fieldList) {
			res.append('\t').append(pc.getField().getName())
					.append(" <=> ")
					.append(pc.getColumn())
					.append('\n');
		}
		res.append(id).append("\n");
		
		return res.toString();
	}
	
}

package com.mec.orm.core;
/*
 * 这个类是ClassTabel的实现类，用户提供类名称，
 * 我们利用利用注解的方式与反射机制得到类里面的成员， 
 * 将Column与field对利用PropertyColumn类里面的set方法  对应起来
 * 并且形成一个又一个的ClassTabel对象
 * 然后put到ClassTableMap  里面去
 */



public class ClassTableFactory {
	private static final Map<String, ClassTable> classTableMap;
	
	static {
		classTableMap = new HashMap<>();
	}
	
	public ClassTableFactory() {
	}
	
	public static void classTableParser(Class<?> klass) {
		// 解析klass，获取与该klass对应的ClassTable对象，并将其put到classTbaleMap中！
		if (!klass.isAnnotationPresent(Table.class)) {
			return;
		}
		Table table = klass.getAnnotation(Table.class);
		String tableName = table.name();
		
		ClassTable classTable = new ClassTable();
		classTable.setKlass(klass);
		classTable.setTable(tableName);
		
		boolean hasId = false;
		
		Field[] fields = klass.getDeclaredFields();
		for (Field field : fields) {
			if (!field.isAnnotationPresent(com.mec.orm.annotation.Field.class)) {
				continue;
			}
			String column = field.getName();
			com.mec.orm.annotation.Field f = field.getAnnotation(com.mec.orm.annotation.Field.class);
			String col = f.column();
			if (col.length() > 0) {
				column = col;
			}
			
			PropertyColumn property = new PropertyColumn();
			property.setField(field);
			property.setColumn(column);
			classTable.addProperty(property);
			
			if (!hasId && field.isAnnotationPresent(Id.class)) {
				classTable.setId(property);
				hasId = true;
			}
		}
		
		classTableMap.put(klass.getName(), classTable);
	}
	/*
	public static void loadMapping(String path) {
		try {
			new XMLParser() {
				@Override
				public void dealElement(Element element, int index) {
					String className = element.getAttribute("class");
					String tableName = element.getAttribute("table");
					
					ClassTable ct = new ClassTable();
					try {
						Class<?> klass = Class.forName(className);
						ct.setKlass(klass);
						ct.setTable(tableName);
						new XMLParser() {
							@Override
							public void dealElement(Element element, int index) {
								String id = element.getAttribute("id");
								String property = element.getAttribute("property");
								String column = element.getAttribute("name");
								
								PropertyColumn pc = new PropertyColumn();
								try {
									pc.setField(klass.getDeclaredField(property));
									pc.setColumn(column);
									
									if (id.length() > 0) {
										ct.setId(pc);
									}
									
									ct.addProperty(pc);
								} catch (NoSuchFieldException e) {
									e.printStackTrace();
								} catch (SecurityException e) {
									e.printStackTrace();
								}
							}
						}.parse(element, "column");
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
					}
					classTableMap.put(className, ct);
				}
			}.parse(XMLParser.loadXml(path), "mapping");
		} catch (SAXException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	*/
	
	public static ClassTable getClassTable(Class<?> klass) {
		return classTableMap.get(klass.getName());
	}
	
	public static ClassTable getClassTable(String className) {
		return classTableMap.get(className);
	}
	
}

//此类给出了数据库的连接
//生成了sql语句
//生成sql语句的步骤;
//1参数为Model 对象
//2通过对象获得元数据类
//3利用元数据获得ClassTable
//4这样就能通过ClassTable  获得表名和字段名
//5得到 PreparedStatement 
//6 进行excute 运行
//  需要注意的是where字句里面需要 id的值 等于相应的值
//  所以在里面写了一个  getkeyvalue();的函数  这里面
//  写上参数  klass 与 Model 对象 
//   klass 利用反射机制 得到所有的成员 并通过注解
//   找到相应成员 然后 通过 field.get(model对象)  就能获取相应成员的值 然后再return




public class Database {
	private volatile static Connection connection;
	
	public Database() {
	}
	
	public static void loadDatabaseConfig(String path) {
		PropertiesParser.loadProperties(path);
	}
	
	private static Connection getConnection() {
		if (connection == null) {
			synchronized (Database.class) {
				if (connection == null) {
					try {
						Class.forName(PropertiesParser.value("driver"));
						connection = DriverManager.getConnection(
								PropertiesParser.value("url"), 
								PropertiesParser.value("user"), 
								PropertiesParser.value("password"));
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		
		return connection;
	}
	
	@SuppressWarnings("unchecked")
	//抑制产生警告信息
	public <T> List<T> list(Class<?> klass) {
		ClassTable ct = getClassTable(klass);
		if (ct == null) {
			return null;
		}

		String sql = "SELECT " + ct.getColumnString() + " FROM " + ct.getTable();
		
		List<T> result = new ArrayList<>();
		try {
			PreparedStatement state = getConnection().prepareStatement(sql);
			ResultSet rs = state.executeQuery();
			
			while(rs.next()) {
				Object object = klass.newInstance();
				ct.setFieldFromResultSet(rs, object);
				result.add((T) object);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	@SuppressWarnings("unchecked")
	public <T> T get(Class<?> klass, Object id) {
		
		ClassTable ct = getClassTable(klass);
		if (ct == null) {
			return null;
		}
		String sql = "SELECT " + ct.getColumnString() + " FROM " + ct.getTable()
			+ " WHERE " + ct.getIdString() + "=?";
	
		
		Connection connection = getConnection();
		try {
			PreparedStatement state = connection.prepareStatement(sql);
			state.setObject(1, id);
			ResultSet rs = state.executeQuery();
			if (rs.next()) {
				Object object = klass.newInstance();
				ct.setFieldFromResultSet(rs, object);
				return (T) object;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public <T> int save(T obj)  {
		Class<?> klass = obj.getClass();
		ClassTable classTable = getClassTable(klass);
		if (classTable == null) {
			return 0;
		}
		
		String sql = getInertSQL(classTable);
		PreparedStatement state = setQuestionMark(sql, klass, obj);
		if (state == null) {
			return 0;
		}
		try {
			return state.executeUpdate();
		} catch (SQLException e) {
			return 0;
		}
	}
	
	private PreparedStatement setQuestionMark(String sql, Class<?> klass, Object obj) {
		PreparedStatement state;
		try {
			state = getConnection().prepareStatement(sql);
			int index = 1;
			for (Field field : klass.getDeclaredFields()) {
				field.setAccessible(true);
				Object value;
				try {
					value = field.get(obj);
				} catch (Exception e) {
					value = null;
				}
				
				state.setObject(index++, value);
			}
		} catch (SQLException e1) {
			return null;
		}
			
		return state;
	}
	
	/*
	 * 
	 * 
	 */

	public <T> int modify(T obj) {
		Class<?> klass = obj.getClass();
		ClassTable classTable = getClassTable(klass);
		if (classTable == null) {
			return 0;
		}
	
				
		String sql = getUpdateSQL(classTable);
		sql = sql.replaceAll(",", "=?,") + "=?   where "
			+classTable.getId().getColumn()+" = "+getkeyvalue( klass, obj);
		PreparedStatement statement=setQuestionMark(sql, klass, obj);
		if (statement == null) {
			return 0;
		}
		
		try {
			System.out.println(statement);
			return statement.executeUpdate();
		} catch (SQLException e) {
			return 0;
		}
		
	}
	public <T> int delete(T obj) {
		Class<?> klass = obj.getClass();
		ClassTable classTable = getClassTable(klass);
		if (classTable == null) {
			return 0;
		}
		String sql="delete from "+classTable.getTable()+" where "
				+classTable.getId().getColumn()+" = "+getkeyvalue( klass, obj);
		
		PreparedStatement statement = null;
		try {
			statement = getConnection().prepareStatement(sql);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		if (statement == null) {
			return 0;
		}
		try {
			System.out.println(statement);
			return statement.executeUpdate();
		} catch (SQLException e) {
			return 0;
		}
	}
	
	
	
	
	
	private Object getkeyvalue(Class<?> klass,Object obj) {
		int index = 1;
		Object value = null;
		for (Field field : klass.getDeclaredFields()) {
			field.setAccessible(true);
			if(field.isAnnotationPresent(Id.class)&&field.isAnnotationPresent(com.mec.orm.annotation.Field.class)) {
				try {
					value=field.get(obj);
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		return value;
	}
	
	
	private ClassTable getClassTable(Class<?> klass) {
		ClassTable classTable = ClassTableFactory.getClassTable(klass);
		if (classTable == null) {
			ClassTableFactory.classTableParser(klass);
			classTable = ClassTableFactory.getClassTable(klass);
		}
		
		return classTable;
	}
	
	private String getUpdateSQL(ClassTable classTable) {
		StringBuffer sql = new StringBuffer("UPDATE ");
		
		sql.append(classTable.getTable()).append(' ');
		sql.append("SET ");
		sql.append(classTable.getColumnString());
		
		return sql.toString();
	}

	private String getInertSQL(ClassTable classTable) {
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO ")
		.append(classTable.getTable())
		.append(" (")
		.append(classTable.getColumnString())
		.append(") VALUES(");
		boolean first = true;
		int fieldCount = classTable.getFieldCount();
		for (int index = 0; index < fieldCount; index++) {
			sql.append(first ? "?" : ", ?");
			first = false;
		}
		sql.append(')');
//		System.out.println(sql);
		return sql.toString();
	}
	
	public int executeUpdate(String SQLString) {
		Connection connection = getConnection();
		try {
			PreparedStatement state = connection.prepareStatement(SQLString);
			return state.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return -1;
	}
	
	public ResultSet executeQuery(String SQLString) {
		ResultSet rs = null;
		Connection connection = getConnection();
		try {
			PreparedStatement state = connection.prepareStatement(SQLString);
			return state.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return rs;
	}
	
}

• 代码很长，但也是有很营养的哈哈，供大家参考，这样妈妈再也不用担心我要写重复的sql语句了。