之前发了以个HQLModel（HQL语句生成器）觉得意义不大。最近对数据库操作很感兴趣。
这几天在公司偷懒，写了一个小玩具。就是想模仿hibernate和mybatis做一个框架实现ORM。
由于技术和时间有限，暂时写到了一对多查询和SQL自动生成，where条件设置，
但是没有考虑到between and in left join 这些关键字。
东西其实很简单，就是希望能得到指教，因为我对反省和反射还不是很了解，而且硬编码的能力也欠缺太多。
目前只完成了查询功能。
使用的时候配置两个文件，然后对应类写上主键注解，还有一对多（只完成了查询）
希望有兴趣的朋友能一起完成这个小玩意。
下面是效果图.
下面是测试代码
import java.beans.IntrospectionException;
import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.List;
import com.leaves.dao.connection.CollectionConfig;
import com.leaves.dao.exceptions.getClassPropException;
import com.leaves.dao.util.ExeSQLOneAndMany;
import com.leaves.model.TaskReport;
import com.leaves.model.WeekReport;
public class TestMain {
public static void main(String[] args) throws IllegalArgumentException, SQLException, IntrospectionException,
IllegalAccessException, InvocationTargetException, getClassPropException {
// testQuery();
testManyQuery();
}
/**
*
*void
*/
private static void testManyQuery() {
CollectionConfig collectionConfig = new CollectionConfig(CollectionConfig.ORACLE_NAME);
ExeSQLOneAndMany<WeekReport> jadbConnection = new ExeSQLOneAndMany<WeekReport>(collectionConfig);
WeekReport weekReport= new WeekReport();
// weekReport.setWeekreportid(136);
List<WeekReport> weekReportList = jadbConnection.exeQueryByPage(weekReport, false, false, 0, 5);
for (WeekReport weekReport2 : weekReportList) {
System.out.println(weekReport2);
for (TaskReport taskReport : weekReport2.getTaskreports()) {
System.out.println(taskReport);
}
}
}
/**
* 测试单个查询
*
* void
*/
private static void testQuery() {
CollectionConfig collectionConfig = new CollectionConfig(CollectionConfig.ORACLE_NAME);
ExeSQLOneAndMany<TaskReport> jadbConnection = new ExeSQLOneAndMany<TaskReport>(collectionConfig);
TaskReport taskreport = new TaskReport();
taskreport.setUserid(107);
taskreport.setFtpAddress("清扬测");
List<TaskReport> taskReportList = jadbConnection.exeQueryByPage(taskreport, true, true, 1, 5);
// List<TaskReport> taskReportList = jadbConnection.exeQuery(taskreport,
// false, false);
for (TaskReport taskReport2 : taskReportList) {
System.out.println(taskReport2);
}
System.out.println("--------------------------------");
}
}
使用的时候要在src目录下创建两个配置文件。
如图
内容是配置数据库属性和配置类对应表的属性
oracledriver=oracle.jdbc.driver.OracleDriver
oracleurl=jdbc:oracle:thin:@192.168.1.2:1521:orcl
oracleusername=test
oraclepassword=test
mysqldriver=com.mysql.jdbc.Driver
mysqlurl=jdbc:mysql://127.0.0.1:3306/file
mysqlusername=root
mysqlpassword=admin
com.leaves.model.TaskReport=TASK_REPORT
com.leaves.model.TaskReport.reportid=REPORTID
com.leaves.model.TaskReport.taskid=TASKID
com.leaves.model.TaskReport.startTime=START_TIME
com.leaves.model.TaskReport.userid=USERID
com.leaves.model.TaskReport.hours=HOURS
com.leaves.model.TaskReport.actualHours=ACTUAL_HOURS
com.leaves.model.TaskReport.cent=CENT
com.leaves.model.TaskReport.codeLine=CODE_LINE
com.leaves.model.TaskReport.ftpAddress=FTP_ADDRESS
com.leaves.model.TaskReport.weekReportId=WEEK_REPORTID
com.leaves.model.WeekReport=WEEK_REPORT
com.leaves.model.WeekReport.weekreportid=WEEKREPORTID
com.leaves.model.WeekReport.userid=USERID
注解和异常还有常量类我就不贴出了。下面贴出核心的sql语句构建器还有辅助工具类和执行器
package com.leaves.dao.connection;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
*
* <b>JDBCCollection说明：</b> <br>
* 此类有一个带有一个String类型参数的构造方法，用来接收一个字符串作为所连接数据库的标识。并加载驱动<br>
*
* @author leaves叶知泉
* @version 1.0
* @since 2012-1-12 上午9:26:16
*/
public class CollectionManager {
/**
* 根据数据库名称实例化。
*
* @param databaseName
* 数据库名
*/
public CollectionManager(CollectionConfig collectionConfig) {
Properties prop = new Properties();
String dbName=collectionConfig.getDbName();
InputStream is;
File propFile = null;
try {
propFile= new File("src/connectionConfig.properties");
is = new FileInputStream(propFile);
prop.load(is);
is.close();
driver = prop.getProperty(dbName + "driver");
url = prop.getProperty(dbName + "url");
username = prop.getProperty(dbName + "username");
password = prop.getProperty(dbName + "password");
} catch (Exception e) {
System.err.println("打开属性文件失败，目标文件路径(相对):"+propFile.getPath());
}
try {
Class.forName(driver).newInstance();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
/**
* 获得一个与数据库的链接。
*
* @return Connection 与数据库建立的连接
* @see java.sql.Connection
*/
public Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, username, password);
} catch (Exception ee) {
ee.printStackTrace();
}
if (conn == null) {
System.err.println("警告: DriverManager.getConnection() 获得数据库链接失败.\r\n\r\n链接类型:" + driver + "\r\n链接位置:" + url
+ "\r\n用户/密码" + username + "/" + password);
}
return conn;
}
/**
* 关闭所有连接，并且提交，捕获异常，不打印内容。
*/
public void closeAndCommitQuietly() {
try {
conn.commit();
} catch (SQLException e1) {
}
closeAll();
}
/**
* 关闭所有连接，并且回滚，捕获异常，不打印内容。
*/
public void closeAndRollBackQuiet() {
try {
conn.rollback();
} catch (SQLException e1) {
}
closeAll();
}
/**
* 关闭所有连接，捕获异常，不打印内容。
*/
public void closeAll() {
try {
if (rs != null)
rs.close();
if (prestmt != null)
prestmt.close();
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
} catch (Exception e) {
}
}
private String driver;
private String url;
private String username;
private String password;
protected Connection conn = null;
protected ResultSet rs = null;
protected PreparedStatement prestmt = null;
protected Statement stmt = null;
}
package com.leaves.dao.connection;
import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;
import com.leaves.dao.exceptions.ClassTypeAndStatementException;
import com.leaves.dao.util.ClassPropUtil;
/**
*
* <b>StatementConnection说明：</b> <br>
* 专门构建查询sql语句的类.也就是对PreparedStatement类或者Statement类进行操作
*
* @author leaves叶知泉
* @version 1.0
* @since 2012-1-12 上午9:58:30
* @see java.sql.Statement
* @see java.sql.PreparedStatement
*/
public class QueryStatementCreater extends CollectionManager {
/**
* 使用过的参数列表，用来debug。
*/
private List<String> paramsList = new ArrayList<String>();
public QueryStatementCreater(CollectionConfig collectionConfig) {
super(collectionConfig);
}
/**
* 获取预编译语句执行器
*
* @param theSQL
* 需要执行的sql语句
* @param classPropUtil
* 类信息
* @return PreparedStatement 预编译语句执行器
*/
public PreparedStatement getStatement(String theSQL, ClassPropUtil classPropUtil, boolean likeSel) {
conn = getConnection();
System.out.printf("准备执行的sql语句====>%s\n", theSQL);
try {
prestmt = conn.prepareStatement(theSQL);
} catch (SQLException e) {
e.printStackTrace();
}
setPropsForPrepareStatement(classPropUtil, likeSel);
System.out.println("所传入参数是：====>" + paramsList);
return prestmt;
}
/**
*
* @Title: createWhereStatements 构建where条件语句
* @param likeSel
* 是否开启模糊查询
* @param classPropUtil
* 封装类信息的工具类
* @return StringBuffer 构建好的where条件的的缓冲区,如果没有任何where条件，返回null
*/
public StringBuffer createWhereSQL(boolean likeSel, ClassPropUtil classPropUtil) {
StringBuffer whereSQLBuff = new StringBuffer();
Map<String, String> columnNameMap = classPropUtil.getColumnNameMap();
whereSQLBuff.append(" ");
whereSQLBuff.append("WHERE");
whereSQLBuff.append(" ");
for (Field field : classPropUtil.getFields()) {
String fieldName = field.getName();
Object objTemp = classPropUtil.getValueByField(field);
if (objTemp == null)
continue;
if (objTemp.getClass().equals(String.class)) {
if (likeSel) {
whereSQLBuff.append(columnNameMap.get(fieldName) + " like ?");
whereSQLBuff.append(" AND ");
} else {
whereSQLBuff.append(columnNameMap.get(fieldName) + "=?");
whereSQLBuff.append(" AND ");
}
} else {
whereSQLBuff.append(columnNameMap.get(fieldName) + "=?");
whereSQLBuff.append(" AND ");
}
}
if (whereSQLBuff.toString().endsWith("AND "))
whereSQLBuff.delete(whereSQLBuff.lastIndexOf(" AND "), whereSQLBuff.length() - 1);
if (whereSQLBuff.toString().endsWith("WHERE "))
return null;
return whereSQLBuff;
}
/**
*
* @Title: createsqlHead 创建查询语句头部信息，会带有rownum字段，如果是分页查询，会添加分页需要的前缀
* @param classPropUtil
* 封装了类型信息的工具类
* @param isByNotNulFieldl
* 是否根据空字段构建查询语句
* @param headModel
* 增删改查的标志头信息
* @param isByPage
* 是否分页
* @return StringBuffer 构建好的sql语句,到达from tableName
*/
protected StringBuffer createHeadSQL(ClassPropUtil classPropUtil, boolean isByNotNulField, String headModel,
boolean isByPage) {
StringBuffer headSQLBuf = new StringBuffer();
Map<String, String> columnNameMap = classPropUtil.getColumnNameMap();
StringBuffer headSQL = new StringBuffer(headModel);
headSQL.append(" ");
headSQL.append("ROWNUM AS RN ");
StringBuffer columnSQL = new StringBuffer();
for (Field field : classPropUtil.getFields()) {
if (isByNotNulField)
if (classPropUtil.getValueByField(field) == null)
continue;
String fieldName = field.getName();
String columnName = columnNameMap.get(fieldName);
if (columnName != null) {
columnSQL.append(",");
columnSQL.append(columnName);
}
}
if (isByPage) {
headSQLBuf.append(headModel);
headSQLBuf.append(" RN");
headSQLBuf.append(columnSQL);
headSQLBuf.append(" FROM ( ");
headSQLBuf.append(headSQL);
headSQLBuf.append(columnSQL);
headSQLBuf.append(" ");
headSQLBuf.append("FROM");
headSQLBuf.append(" ");
headSQLBuf.append(classPropUtil.getTableName());
} else {
headSQLBuf.append(headSQL);
headSQLBuf.append(columnSQL);
headSQLBuf.append(" ");
headSQLBuf.append("FROM");
headSQLBuf.append(" ");
headSQLBuf.append(classPropUtil.getTableName());
}
return headSQLBuf;
}
/**
*
* @Title: createSQL 生成查询的sql语句
* @param classPropUtil
* 类信息工具
* @param sqlhead
* 要生成的语句头类型
* @param isByNotNulField
* 是否按照非空属性来构建
* @param isByPage
* 是否分页
* @param likeSel
* 是否开启模糊查询
* @return String 返回类型 构建好的sql语句
* @throws
*/
public String createSQL(ClassPropUtil classPropUtil, String sqlhead, boolean isByNotNulField, boolean likeSel) {
StringBuffer sqlBuff = new StringBuffer();
StringBuffer headSQLBuff = createHeadSQL(classPropUtil, isByNotNulField, sqlhead, false);
StringBuffer whereSQLBuff = createWhereSQL(likeSel, classPropUtil);
sqlBuff.append(headSQLBuff);
if (whereSQLBuff != null)
sqlBuff.append(whereSQLBuff);
String querySql = sqlBuff.toString();
return querySql;
}
/**
* @param endRecord
* @param startRecord
*
* @Title: createSQLByPage 生成查询的sql语句，并且支持分页
* @param classPropUtil
* 类信息工具
* @param sqlhead
* 要生成的语句头类型
* @param isByNotNulField
* 是否按照非空属性来构建
* @param isByPage
* 是否分页
* @param likeSel
* 是否开启模糊查询
* @return String 返回类型 构建好的sql语句
* @throws
*/
public String createSQLByPage(ClassPropUtil classPropUtil, String sqlhead, boolean isByNotNulField,
boolean likeSel, int startRecord, int endRecord) {
StringBuffer sqlBuff = new StringBuffer();
if (CollectionConfig.SLECT_HEAD_SQL.equals(sqlhead)) {
StringBuffer headSQLLBuff = createHeadSQL(classPropUtil, isByNotNulField, sqlhead, true);
StringBuffer whereSQLBuff = createWhereSQL(likeSel, classPropUtil);
sqlBuff.append(headSQLLBuff);
if (whereSQLBuff != null) {
sqlBuff.append(whereSQLBuff);
sqlBuff.append(" AND ROWNUM<=?)");
} else
sqlBuff.append(" WHERE ROWNUM<=?)");
sqlBuff.append("WHERE RN>?");
}
String querySql = sqlBuff.toString();
querySql = setPageParams(querySql, startRecord, endRecord);
return querySql;
}
/**
* 为一条PreparedStatement根据传入的对象属性工具类设置占位符的值<br>
* 注意：如果传入对象属性工具类和PreparedStatement对象不一致，将会出现错误.
*
* @param classPropUtil
* 类信息
*/
public void setPropsForPrepareStatement(ClassPropUtil classPropUtil, boolean likeSel) {
int index = 1;
List<String> whereParamList = new ArrayList<String>();
for (Field field : classPropUtil.getFields()) {
Object objTemp = classPropUtil.getValueByField(field);
if (objTemp == null)
continue;
try {
Object parma = null;
if (objTemp.getClass().equals(Date.class)) {
parma = new Timestamp(((Date) objTemp).getTime());
prestmt.setTimestamp(index++, (Timestamp) parma);
} else if (objTemp.getClass().equals(String.class) && likeSel) {
parma = "%" + objTemp + "%";
prestmt.setString(index++, (String) parma);
} else{
parma=objTemp;
prestmt.setObject(index++, objTemp);
}
whereParamList.add(0, parma.toString() + "(" + parma.getClass().getSimpleName() + ")");
} catch (SQLException e) {
new ClassTypeAndStatementException().printStackTrace();
e.printStackTrace();
}
}
Collections.reverse(whereParamList);
paramsList.addAll(0, whereParamList);
}
/**
*
* 设置分页条件参数
*
* @param querySq
* 要设置的sql语句l
* @param startRecord
* 开始记录条数
* @param endRecord
* 结束记录条数
* @return String 设置好的sql语句
*/
private String setPageParams(String querySql, Integer startRecord, Integer endRecord) {
System.out.println("分页条件:" + startRecord + "（开始行数）");
System.out.println("分页条件:" + endRecord + "（结束行数）");
querySql = querySql.replaceAll("ROWNUM[><=\\s\\?]+\\?", "ROWNUM<=" + endRecord);
querySql = querySql.replaceAll("RN[><=\\s\\?]+\\?", "RN>" + startRecord);
paramsList.add(endRecord + "(" + endRecord.getClass().getSimpleName() + ")");
paramsList.add(startRecord + "(" + startRecord.getClass().getSimpleName() + ")");
return querySql;
}
}
package com.leaves.dao.util;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import com.leaves.dao.annotations.OneToMany;
import com.leaves.dao.annotations.PrimaryKey;
import com.leaves.dao.exceptions.getClassPropException;
public class ClassPropUtil {
public ClassPropUtil(Object object) {
this.setObject(object);
init(object);
}
/**
* 初始化方法设置几个所需要的属性
*
* @param object
* @throws getClassPropException
*/
private void init(Object object) {
Class<?> theClass = object.getClass();
Properties prop = getProps();
this.setTheClass(theClass);
String tableName = prop.getProperty(theClass.getName());
if (tableName == null) {
getClassPropException getclasspropexception = new getClassPropException(
getClassPropException.GET_TABLE_NAME);
try {
throw getclasspropexception;
} catch (getClassPropException e) {
e.printStackTrace();
}
} else
this.setTableName(tableName);
Field[] fields = this.getFields();
Map<String, String> columnNameMap = new HashMap<String, String>();
for (Field field : fields) {
String columnName = prop.getProperty(theClass.getName() + "." + field.getName());
if (columnName != null)
columnNameMap.put(field.getName(), columnName);
}
this.setColumnNameMap(columnNameMap);
}
/**
* 获取properties属性配置
*
* @return
*/
public Properties getProps() {
Properties prop = new Properties();
InputStream is;
try {
is = new FileInputStream(new File("src/tableProp.properties"));
prop.load(is);
is.close();
} catch (Exception e) {
System.out.println("Open property File " + "tableProp.properties" + " error!");
}
return prop;
}
/**
* 根据字段来查询其值相当于执行getter方法
*
* @param field
* @return
*/
public Object getValueByField(Field field) {
PropertyDescriptor pd = null;
try {
pd = new PropertyDescriptor(field.getName(), theClass);
} catch (IntrospectionException e) {
e.printStackTrace();
}
Method getMethod = pd.getReadMethod();
Object objTemp = null;
try {
objTemp = getMethod.invoke(object);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return objTemp;
}
/**
* 根据字段设置值
*
* @param field
* 字段
* @return 如果失败（找不到字段），则返回false，否则返回true
*/
public boolean setValueByField(Field field, Object object) {
boolean flag = false;
PropertyDescriptor pd = null;
try {
pd = new PropertyDescriptor(field.getName(), theClass);
} catch (IntrospectionException e) {
e.printStackTrace();
}
Method getMethod = pd.getWriteMethod();
try {
getMethod.invoke(this.getObject(), object);
flag = true;
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return flag;
}
/**
* 根据字段名称设置值
*
* @param fieldName
* 字段名称
* @return 如果失败（找不到字段），则返回false，否则返回true
*/
public boolean setValueByFieldName(String fieldName, Object object) {
boolean flag = false;
PropertyDescriptor pd;
try {
pd = new PropertyDescriptor(fieldName, theClass);
pd.getWriteMethod().invoke(this.getObject(), object);
flag = true;
} catch (IntrospectionException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return flag;
}
/**
* 设置指定类型的主键
*
* @param primaryKey
* 主键值
* @return boolean 成功查询到主键并且设置则返回true，否则返回false
*/
public boolean setPrimaryKey(Integer primaryKey) {
boolean flag = false;
for (Field field : this.getFields()) {
if (field.getAnnotation(PrimaryKey.class) != null) {
PropertyDescriptor pd = null;
try {
pd = new PropertyDescriptor(field.getName(), this.getTheClass());
Method setMethod = pd.getWriteMethod();
setMethod.invoke(this.getObject(), primaryKey);
flag = true;
} catch (IntrospectionException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
return flag;
}
/**
* 查询指定类型的主键 （如果返回null标示没有注解主键）
*
* @return Integer 主键值
*/
public Integer getPrimaryKey() {
Integer primaryKeyValue = null;
for (Field field : this.getFields()) {
if (field.getAnnotation(PrimaryKey.class) != null) {
PropertyDescriptor pd = null;
try {
pd = new PropertyDescriptor(field.getName(), this.getTheClass());
Method getMethod = pd.getReadMethod();
primaryKeyValue = (Integer) getMethod.invoke(this.getObject());
} catch (IntrospectionException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
return primaryKeyValue;
}
/**
* 设置内部集合的值（有问题）
*
* @param list
* @return Integer
*/
public Integer setManyList(List<? extends Object> list) {
Integer primaryKeyValue = null;
for (Field field : this.getFields()) {
if (field.getAnnotation(OneToMany.class) != null) {
PropertyDescriptor pd = null;
try {
pd = new PropertyDescriptor(field.getName(), this.getTheClass());
Method getMethod = pd.getWriteMethod();
getMethod.invoke(this.getObject(), list);
} catch (IntrospectionException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
return primaryKeyValue;
}
public Map<String, String> getColumnNameMap() {
return columnNameMap;
}
public void setColumnNameMap(Map<String, String> columnNameMap) {
this.columnNameMap = columnNameMap;
}
public Object getObject() {
return object;
}
public void setObject(Object object) {
this.object = object;
}
public Class<?> getTheClass() {
return theClass;
}
public void setTheClass(Class<?> theClass) {
this.theClass = theClass;
}
public String getTableName() {
return TableName;
}
public void setTableName(String tableName) {
TableName = tableName;
}
/**
* 获取所有属性数组的方法
*
* @return
*/
public Field[] getFields() {
Field[] fileds = theClass.getDeclaredFields();
return fileds;
}
/**
* 此属性用来存放类的字段名称和在配置文件中对应的列名
*/
private Map<String, String> columnNameMap;
/**
* 此属性用来存放类型
*/
private Class<?> theClass;
private String TableName;
private Object object;
}
package com.leaves.dao.util;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import com.leaves.dao.annotations.OneToMany;
import com.leaves.dao.connection.CollectionConfig;
import com.leaves.dao.connection.QueryStatementCreater;
/**
*
* <b>ExeSQL说明：</b> <br>
* 此类用于执行对数据库的操作，执行之后会返回封装好的实体类对象。<br>
* 每次使用完本类后需要手动调用close()方法关闭所使用的资源.
*
* @author leaves叶知泉
* @version 1.0
* @since 2012-1-12 上午9:23:36
* @param <T>要针对哪个实体类进行操作。
*/
public class ExeSQLOneAndMany<T> extends ExeSQL<T> {
private ClassPropUtil classPropUtil;
private QueryStatementCreater qsc;
/**
* 按照指定数据库名创建执行器
*
* @param databaseName
*/
public ExeSQLOneAndMany(CollectionConfig collectionConfig) {
super(collectionConfig);
}
/**
* 查询数据库内容，不分页
*
* @param object
* 要查询的类的对象模型
* @param likeSel
* 是否进行模糊查询
* @param isByNotNulField
* 是否按照非空属性查询
* @return List<T> 查询结果所封装的结果集
*/
public List<T> exeQuery(Object object, boolean likeSel, boolean isByNotNulField) {
classPropUtil = new ClassPropUtil(object);
qsc = new QueryStatementCreater(collectionConfig);
String theSQL = qsc.createSQL(classPropUtil, CollectionConfig.SLECT_HEAD_SQL, isByNotNulField, likeSel);
prestmt = qsc.getStatement(theSQL, classPropUtil,likeSel);
try {
rs = prestmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
List<T> resultList = resultSetToObjectList(rs, classPropUtil, isByNotNulField);
return resultList;
}
/**
* 查询数据库内容，可以分页
*
* @param object
* 要查询的类的对象模型
* @param likeSel
* 是否进行模糊查询
* @param isByNotNulField
* 是否按照非空属性查询
* @param startRecord
* 起始记录条数
* @param endRecord
* 结束记录条数
* @return List<T> 查询结果所封装的结果集
*/
public List<T> exeQueryByPage(Object object, boolean likeSel, boolean isByNotNulField, int startRecord,
int endRecord) {
classPropUtil = new ClassPropUtil(object);
qsc = new QueryStatementCreater(collectionConfig);
String theSQL = qsc.createSQLByPage(classPropUtil, CollectionConfig.SLECT_HEAD_SQL, isByNotNulField, likeSel,
startRecord, endRecord);
prestmt = qsc.getStatement(theSQL, classPropUtil,likeSel);
try {
rs = prestmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
List<T> resultList = resultSetToObjectList(rs, classPropUtil, isByNotNulField);
for (Field field : classPropUtil.getFields()) {
OneToMany oneToMany = field.getAnnotation(OneToMany.class);
if (oneToMany != null) {
try {
Object theObject = Class.forName(oneToMany.toTypeName()).newInstance();
new ClassPropUtil(theObject)
.setValueByFieldName(oneToMany.toField(), classPropUtil.getPrimaryKey());
for (T t : resultList) {
new ClassPropUtil(t).setManyList(exeQuery(theObject, false, false));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
}
}
qsc.closeAll();
return resultList;
}
/**
* 功能:执行更新操作 executeUpdate()方法执行更新语句，并且返回一个int值，包含给定查询所生成数据的 ResultSet 对象
*
* @param sql
* 要执行的查询语句，此语句是预编译语句
* @param objects
* 此sql语句需要处理的预编译参量
* @return result 包含给定sql语句操作的数据条数，如果执行不成功，则返回0
*/
public int exeUpdate(String sql, Object... objects) {
int result;
try {
conn = qsc.getConnection();
prestmt = conn.prepareStatement(sql, ResultSet.CONCUR_READ_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE);
for (int i = 0; i < objects.length; i++)
prestmt.setObject(i + 1, objects[i]);
result = prestmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
result = 0;
}
return result;
}
/**
* 功能:执行更新操作 executeUpdate()方法执行更新语句，并且返回一个int值，包含给定查询所生成数据的 ResultSet 对象
*
* @return result 包含给定sql语句操作的数据条数，如果执行不成功，则返回0
*/
public int exeUpdate(String sql) {
int result = 0;
try {
conn = qsc.getConnection();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
result = stmt.executeUpdate(sql);
} catch (SQLException ex) {
result = 0;
}
return result;
}
/**
*
* @Title: resultSetToObjectList
* @Description: TODO 把查询结果封装成对象集
* @param @param rs 查询结果集
* @param @param classPropUtil 对象属性工具类
* @param @param isByNotNulFieldl 是否按照非空属性
* @return List<T> 转换后的结果集
* @throws
*/
@SuppressWarnings("unchecked")
protected List<T> resultSetToObjectList(ResultSet rs, ClassPropUtil classPropUtil, boolean isByNotNulFieldl) {
Class<?> theClass = classPropUtil.getTheClass();
Map<String, String> columnNameMap = classPropUtil.getColumnNameMap();
List<T> resultList = new ArrayList<T>();
try {
while (rs.next()) {
Object object = null;
try {
object = theClass.newInstance();
} catch (Exception e) {
e.printStackTrace();
}
for (Field field : classPropUtil.getFields()) {
if (isByNotNulFieldl)
if (classPropUtil.getValueByField(field) == null)
continue;
PropertyDescriptor pd = null;
try {
pd = new PropertyDescriptor(field.getName(), theClass);
} catch (IntrospectionException e) {
e.printStackTrace();
}
Method setMethod = pd.getWriteMethod();
try {
Object columnValue = null;
if (setMethod.getParameterTypes()[0].equals(Byte.class))
columnValue = rs.getByte(columnNameMap.get(field.getName()));
else if (setMethod.getParameterTypes()[0].equals(Short.class))
columnValue = rs.getShort(columnNameMap.get(field.getName()));
else if (setMethod.getParameterTypes()[0].equals(Integer.class))
columnValue = rs.getInt(columnNameMap.get(field.getName()));
else if (setMethod.getParameterTypes()[0].equals(Long.class))
columnValue = rs.getLong(columnNameMap.get(field.getName()));
else if (setMethod.getParameterTypes()[0].equals(Float.class))
columnValue = rs.getFloat(columnNameMap.get(field.getName()));
else if (setMethod.getParameterTypes()[0].equals(Double.class))
columnValue = rs.getDouble(columnNameMap.get(field.getName()));
else if (setMethod.getParameterTypes()[0].equals(BigDecimal.class))
columnValue = rs.getBigDecimal(columnNameMap.get(field.getName()));
else if (setMethod.getParameterTypes()[0].equals(Blob.class))
columnValue = rs.getBlob(columnNameMap.get(field.getName()));
else if (setMethod.getParameterTypes()[0].equals(Time.class))
columnValue = rs.getTime(columnNameMap.get(field.getName()));
else if (setMethod.getParameterTypes()[0].equals(Timestamp.class))
columnValue = rs.getTimestamp(columnNameMap.get(field.getName()));
else if (setMethod.getParameterTypes()[0].equals(String.class))
columnValue = rs.getString(columnNameMap.get(field.getName()));
else if (setMethod.getParameterTypes()[0].equals(Date.class))
columnValue = rs.getDate(columnNameMap.get(field.getName()));
if (null != columnValue)
setMethod.invoke(object, columnValue);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
resultList.add((T) object);
}
} catch (SQLException e) {
e.printStackTrace();
}
return resultList;
}
protected Connection conn = null;
protected ResultSet rs = null;
protected PreparedStatement prestmt = null;
protected Statement stmt = null;
}
附带上源码包
