一、定义注解
package util;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Column {
public boolean isId() default false;
public String name() default "";
public String caption() default "";
public boolean nullable() default true;
public int length() default 20;
}
二、定义Dao层
package dao;
import java.sql.SQLException;
import java.util.ArrayList;
public interface SqlUtilDao {
//添加一个对象
public Boolean add(Object object) throws IllegalAccessException, SQLException;
//修改一个对象
public Boolean update(Object object) throws SQLException, IllegalAccessException;
//删除一个对象
public Boolean delete(int id, Object object) throws IllegalAccessException, SQLException;
//查询所有
public ArrayList<Object> query(Object object) throws IllegalAccessException, SQLException;
//指定某个字段查询
public ArrayList<Object> queryByCondition(Object object, String name, Integer condition) throws IllegalAccessException, SQLException;
}
二、定义DaoImpl
package daoImpl;
import dao.SqlUtilDao;
import util.Column;
import util.DBUtil;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
public class SqlUtilDaoImpl implements SqlUtilDao {
/**
* 新增一条记录
* @param object
* @return
* @throws IllegalAccessException
* @throws SQLException
*/
@Override
public Boolean add(Object object) throws IllegalAccessException, SQLException {
Class clazz = object.getClass();
//获取实体属性
Field[] fields = clazz.getDeclaredFields();
//申明StringBuffer的Sql
StringBuffer sql = new StringBuffer();
//拼接Sql
sql.append("insert into ");
sql.append("`" + clazz.getSimpleName().toLowerCase() + "`");
sql.append(" (");
//遍历实体属性
for(Field field:fields)
{
field.setAccessible(true);
//获取属性的注解
Column column = field.getAnnotation(Column.class);
//没有注解或者字段为id就跳过
if(column == null) continue;
if(column.isId() == true)
{
continue;
}
String columnName = column.name();
sql.append("`" + columnName + "`,");
}
sql.deleteCharAt(sql.length()-1);
sql.append(") values (");
//为字段赋值
for(Field field:fields)
{
//将所有属性设置为外部可见
field.setAccessible(true);
Column column = field.getAnnotation(Column.class);
//没有注解或者字段名为id就跳过
if(column == null) continue;
if(column.isId() == true)
{
continue;
}
//获取属性值
Object value = field.get(object);
String valueString ="";
if(value instanceof String)
{
valueString="'"+value+"'";
}
else
{
valueString=value.toString();
}
//拼接Sql
sql.append(valueString).append(",");
}
//删除最后的逗号
sql.deleteCharAt(sql.length()-1);
sql.append(")");
String resuleSql = sql.toString();
new DBUtil();
Connection connection = DBUtil.open();
Statement statement = connection.createStatement();
try {
//输出SQL并执行Sql
System.out.println(sql);
statement.execute(resuleSql);
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
DBUtil.close(connection);
}
return true;
}
/**
* 修改一条记录
* @param object
* @return
* @throws SQLException
* @throws IllegalAccessException
*/
@Override
public Boolean update(Object object) throws SQLException, IllegalAccessException {
Class clazz = object.getClass();
//获取实体属性
Field[] fields = clazz.getDeclaredFields();
//申明StringBuffer的Sql
StringBuffer sql = new StringBuffer();
//声明id
Integer id = 0;
//拼接Sql
sql.append("update ");
sql.append("`" + clazz.getSimpleName().toLowerCase() + "`");
sql.append(" set ");
for(Field field:fields)
{
field.setAccessible(true);
Column column = field.getAnnotation(Column.class);
if(column == null) continue;
if(column.isId() == true)
{
id = (Integer)field.get(object);
continue;
}
Object value = field.get(object);
String columnName = column.name();
String valueString ="";
if(value instanceof String)
{
valueString="'"+value+"'";
}
else
{
valueString=value.toString();
}
sql.append("`" + columnName + "` " + "= " + valueString).append(",");
}
sql.deleteCharAt(sql.length()-1);
sql.append(" where id = " + id.toString());
String resuleSql = sql.toString();
new DBUtil();
Connection connection = DBUtil.open();
Statement statement = connection.createStatement();
try {
System.out.println(sql);
statement.execute(resuleSql);
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
DBUtil.close(connection);
}
return true;
}
/**
* 删除一条记录
* @param id
* @param object
* @return
* @throws SQLException
*/
@Override
public Boolean delete(int id, Object object) throws SQLException {
Class clazz = object.getClass();
StringBuffer sql = new StringBuffer();
//拼接Sql
sql.append("delete from ");
sql.append("`" + clazz.getSimpleName().toLowerCase() + "`");
sql.append(" where id = " + id);
String resuleSql = sql.toString();
new DBUtil();
Connection connection = DBUtil.open();
Statement statement = connection.createStatement();
try {
System.out.println(sql);
statement.execute(resuleSql);
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
DBUtil.close(connection);
}
return true;
}
/**
* 查询所有记录
* @param object
* @return
* @throws SQLException
*/
@Override
public ArrayList<Object> query(Object object) throws SQLException {
Class clazz = object.getClass();
//获取实体属性
Field[] fields = clazz.getDeclaredFields();
//申明StringBuffer的Sql
StringBuffer sql = new StringBuffer();
//获取对象属性
Class<?> attribute = object.getClass();
//拼接Sql
sql.append("select ");
for(Field field:fields)
{
field.setAccessible(true);
Column column = field.getAnnotation(Column.class);
String columnName = column.name();
sql.append("`" + columnName + "` ").append(",");
}
sql.deleteCharAt(sql.length()-1);
sql.append(" from " + "`" + clazz.getSimpleName() + "`");
//将Sql解析为String类型
String resuleSql = sql.toString();
new DBUtil();
Connection connection = DBUtil.open();
Statement statement = connection.createStatement();
try {
System.out.println(sql);
//执行Sql语句
ResultSet resulSet = statement.executeQuery(resuleSql);
ArrayList<Object> list=new ArrayList();
while(resulSet.next()) {
Object obj = attribute.newInstance();
for (int i = 0; i < fields.length; i++) {
//获取属性类型
String type = fields[i].getType().getSimpleName();
//获取属性名
String tName = fields[i].getName();
//给该属性赋值
if("String".equals(type)){
fields[i].set(obj, resulSet.getString(tName));
}else if("Date".equals(type)){
fields[i].set(obj, resulSet.getDate(tName));
}else if("int".equals(type)){
fields[i].set(obj, resulSet.getInt(tName));
}else if("Integer".equals(type)){
fields[i].set(obj, resulSet.getInt(tName));
}else if("double".equals(type)){
fields[i].set(obj, resulSet.getDouble(tName));
}else if("float".equals(type)){
fields[i].set(obj, resulSet.getFloat(tName));
}
}
list.add(obj);
}
return list;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
DBUtil.close(connection);
}
}
/**
* 根据某个属性查询
* @param object
* @param name
* @param condition
* @return
* @throws IllegalAccessException
* @throws SQLException
*/
@Override
public ArrayList<Object> queryByCondition(Object object, String name, Integer condition) throws IllegalAccessException, SQLException {
Class clazz = object.getClass();
//获取实体属性
Field[] fields = clazz.getDeclaredFields();
//申明StringBuffer的Sql
StringBuffer sql = new StringBuffer();
//获取对象属性
Class<?> attribute = object.getClass();
//拼接Sql
sql.append("select ");
for(Field field:fields)
{
field.setAccessible(true);
String fieldName = "`" + field.getName() + "`";
sql.append(fieldName).append(",");
}
sql.deleteCharAt(sql.length()-1);
sql.append(" from `" + clazz.getSimpleName() + "`");
sql.append(" where " + name + " = " + condition);
//将Sql解析为String类型
String resuleSql = sql.toString();
new DBUtil();
Connection connection = DBUtil.open();
Statement statement = connection.createStatement();
try {
System.out.println(sql);
//执行Sql语句
ResultSet resulSet = statement.executeQuery(resuleSql);
ArrayList<Object> list=new ArrayList();
while(resulSet.next()) {
Object obj = attribute.newInstance();
for (int i = 0; i < fields.length; i++) {
//获取属性类型
String type = fields[i].getType().getSimpleName();
//获取属性名
String tName = fields[i].getName();
//给该属性赋值
if("String".equals(type)){
fields[i].set(obj, resulSet.getString(tName));
}else if("Date".equals(type)){
fields[i].set(obj, resulSet.getDate(tName));
}else if("int".equals(type)){
fields[i].set(obj, resulSet.getInt(tName));
}else if("Integer".equals(type)){
fields[i].set(obj, resulSet.getInt(tName));
}else if("double".equals(type)){
fields[i].set(obj, resulSet.getDouble(tName));
}else if("float".equals(type)){
fields[i].set(obj, resulSet.getFloat(tName));
}
}
list.add(obj);
}
return list;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
DBUtil.close(connection);
}
}
}