大匠运斤-------利用注解或者XML解析的方式 生成SQL语句
我们在进行一些软件开发的时候，往往会要求实现增删改查的功能，这一般都要用数据库的连接以及sql语句。
- 对于这些制式的sql代码，能够做成一套工具进行自动生成当然是很nice的
- 对于我们的model类，我们对应有一个表，我们可以利用写xml文件，或者进行注解来实现他们之间的相互对应，
- 并且对应之后利用反射机制，最终形成sql的生成
- 我们做好三个类
- 接下来直接上代码喽！
/*
* 将 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语句了。
投递大华股份等公司10个岗位
