1、建三个注解,用于midel类上,Table(标注表名),Id(标注数据库主键字段),Column(标注除主键其他字段)
package b.w.xqp.anno;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 标识数据库字段的名称
*
* @author T
*
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Column {
/**
* 字段名称
*/
String value();
/**
* 字段的类型
*
* @return
*/
Class<?> type() default String.class;
/**
* 字段的长度
*
* @return
*/
int length() default 0;
}
package b.w.xqp.anno;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 标识数据库字段的ID
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Id {
/**
* ID的名称
*
* @return
*/
String value();
}
package b.w.xqp.anno;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 数据库表的的名称
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Table {
/**
* 表名
*/
String value();
}
2、创建JdbcGenericDao接口,并创建通用方法
package b.w.xqp.dao.base;
import java.util.List;
import java.util.Map;
public interface JdbcGenericDao<T> {
/**
* 保存方法
*
* @param t
* 要保存的model模型
* @throws Exception
*/
public void save(T t) throws Exception;
/**
* 删除方法
*
* @param id
* 主键id
* @param clazz
* 删除模型的字节码文件
* @throws Exception
*/
public void delete(Object id, Class<T> clazz) throws Exception;
/**
* 修改方法
*
* @param t
* model模型
* @throws Exception
*/
public void update(T t) throws Exception;
/**
*
* @param id
* @param clazz
* @return
* @throws Exception
*/
public T get(Object id, Class<T> clazz) throws Exception;
/**
* 根据条件查询
*
* @param sqlWhereMap
* key:条件字段名 value:条件字段值
* @param clazz
* @return
* @throws Exception
*/
public List<T> findAllByConditions(Map<String, Object> sqlWhereMap,
Class<T> clazz) throws Exception;
}
3、创建接口实现类JdbcGenericDaoImpl
package b.w.xqp.dao.base;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import b.w.xqp.anno.Column;
import b.w.xqp.anno.Id;
import b.w.xqp.anno.Table;
import b.w.xqp.util.JDBCUtils;
/**
* 泛型DAO的JDBC实现
*
* @author T
* @version 1.0
*/
@SuppressWarnings("all")
public class JdbcGenericDaoImpl<T> implements JdbcGenericDao<T> {
// 表的别名
private static final String TABLE_ALIAS = "t";
@Override
public void save(T t) throws Exception {
Connection connection = null;
PreparedStatement ps = null;
Class<?> clazz = t.getClass();
// 获得表名
String tableName = getTableName(clazz);
// 获得字段
StringBuilder fieldNames = new StringBuilder(); // 字段名
List<Object> fieldValues = new ArrayList<Object>(); // 字段值
StringBuilder placeholders = new StringBuilder(); // 占位符
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
PropertyDescriptor pd = new PropertyDescriptor(field.getName(),
t.getClass());
// 判断字段值是否为空
if (pd.getReadMethod().invoke(t) != null) {
if (field.isAnnotationPresent(Id.class)) {
fieldNames.append(field.getAnnotation(Id.class).value())
.append(",");
fieldValues.add(pd.getReadMethod().invoke(t));
} else if (field.isAnnotationPresent(Column.class)) {
fieldNames
.append(field.getAnnotation(Column.class).value())
.append(",");
fieldValues.add(pd.getReadMethod().invoke(t));
}
placeholders.append("?").append(",");
}
}
// 删除最后一个逗号
fieldNames.deleteCharAt(fieldNames.length() - 1);
placeholders.deleteCharAt(placeholders.length() - 1);
// 拼接sql
StringBuilder sql = new StringBuilder("");
sql.append("insert into ").append(tableName).append(" (")
.append(fieldNames.toString()).append(") values (")
.append(placeholders).append(")");
try {
// 开启事务
connection = JDBCUtils.getConnection();
/** 设置不自动提交,以便于在出现异常的时候数据库回滚 **/
JDBCUtils.startTransaction();
ps = connection.prepareStatement(sql.toString());
// 设置SQL参数占位符的值
setParameter(fieldValues, ps, false);
// 执行SQL
ps.execute();
// 提交事务
JDBCUtils.commit();
} catch (Exception e) {
JDBCUtils.rollback();
e.printStackTrace();
} finally {
// 关闭连接
if (ps != null) {
ps.close();
}
JDBCUtils.close();
}
}
@Override
public void delete(Object id, Class<T> clazz) throws Exception {
Connection connection = null;
PreparedStatement ps = null;
// 获得表名
String tableName = getTableName(clazz);
// 获得ID字段名和值
String idFieldName = "";
boolean flag = false;
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(Id.class)) {
idFieldName = field.getAnnotation(Id.class).value();
flag = true;
break;
}
}
if (!flag) {
throw new Exception(clazz.getName()
+ " object not found id property.");
}
// 拼装sql
String sql = "delete from " + tableName + " where " + idFieldName
+ "=?";
try {
connection = JDBCUtils.getConnection();
// 开启事务
JDBCUtils.startTransaction();
ps = connection.prepareStatement(sql);
ps.setObject(1, id);
// 执行SQL
ps.execute();
// 提交事务
JDBCUtils.commit();
} catch (Exception e) {
// 回滚事务
JDBCUtils.rollback();
e.printStackTrace();
} finally {
// 关闭连接
if (ps != null) {
ps.close();
}
JDBCUtils.close();
}
}
@Override
public void update(T t) throws Exception {
Connection connection = null;
PreparedStatement ps = null;
Class<?> clazz = t.getClass();
// 获得表名
String tableName = getTableName(clazz);
// 获得字段
List<Object> fieldNames = new ArrayList<Object>(); // 字段名
List<Object> fieldValues = new ArrayList<Object>(); // 字段值
List<String> placeholders = new ArrayList<String>();// 占位符
String idFieldName = "";
Object idFieldValue = "";
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
PropertyDescriptor pd = new PropertyDescriptor(field.getName(),
t.getClass());
if (pd.getReadMethod().invoke(t) != null) {
if (field.isAnnotationPresent(Id.class)) {
idFieldName = field.getAnnotation(Id.class).value();
idFieldValue = pd.getReadMethod().invoke(t);
} else if (field.isAnnotationPresent(Column.class)) {
fieldNames.add(field.getAnnotation(Column.class).value());
fieldValues.add(pd.getReadMethod().invoke(t));
placeholders.add("?");
}
}
}
// ID作为更新条件,放在集合中的最后一个元素
fieldNames.add(idFieldName);
fieldValues.add(idFieldValue);
placeholders.add("?");
// 拼接sql
StringBuilder sql = new StringBuilder("");
sql.append("update ").append(tableName).append(" set ");
int index = fieldNames.size() - 1;
for (int i = 0; i < index; i++) {
sql.append(fieldNames.get(i)).append("=")
.append(placeholders.get(i)).append(",");
}
sql.deleteCharAt(sql.length() - 1).append(" where ")
.append(fieldNames.get(index)).append("=").append("?");
try {
// 获取链接
connection = JDBCUtils.getConnection();
// 开启事务
JDBCUtils.startTransaction();
ps = connection.prepareStatement(sql.toString());
// 设置SQL参数占位符的值
setParameter(fieldValues, ps, false);
// 执行SQL
ps.execute();
// 提交事务
JDBCUtils.commit();
} catch (Exception e) {
// 回滚事务
JDBCUtils.rollback();
e.printStackTrace();
} finally {
// 关闭连接
if (ps != null) {
ps.close();
}
JDBCUtils.close();
}
}
@Override
public T get(Object id, Class<T> clazz) throws Exception {
String idFieldName = "";
Field[] fields = clazz.getDeclaredFields();
boolean flag = false;
for (Field field : fields) {
if (field.isAnnotationPresent(Id.class)) {
idFieldName = field.getAnnotation(Id.class).value();
flag = true;
break;
}
}
if (!flag) {
throw new Exception(clazz.getName()
+ " object not found id property.");
}
// 拼装SQL
Map<String, Object> sqlWhereMap = new HashMap<String, Object>();
sqlWhereMap.put(TABLE_ALIAS + "." + idFieldName, id);
List<T> list = findAllByConditions(sqlWhereMap, clazz);
return list.size() > 0 ? list.get(0) : null;
}
@Override
public List<T> findAllByConditions(Map<String, Object> sqlWhereMap,
Class<T> clazz) throws Exception {
List<T> list = new ArrayList<T>();
String tableName = getTableName(clazz);
String idFieldName = "";
// 存储所有字段的信息
// 通过反射获得要查询的字段
StringBuffer fieldNames = new StringBuffer();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
String propertyName = field.getName();
if (field.isAnnotationPresent(Id.class)) {
idFieldName = field.getAnnotation(Id.class).value();
fieldNames.append(TABLE_ALIAS + "." + idFieldName)
.append(" as ").append(propertyName).append(",");
} else if (field.isAnnotationPresent(Column.class)) {
fieldNames
.append(TABLE_ALIAS + "."
+ field.getAnnotation(Column.class).value())
.append(" as ").append(propertyName).append(",");
}
}
fieldNames.deleteCharAt(fieldNames.length() - 1);
// 拼装SQL
String sql = "select " + fieldNames + " from " + tableName + " "
+ TABLE_ALIAS;
PreparedStatement ps = null;
List<Object> values = null;
ResultSet rs = null;
if (sqlWhereMap != null) {
List<Object> sqlWhereWithValues = getSqlWhereWithValues(sqlWhereMap);
if (sqlWhereWithValues != null) {
// 拼接SQL条件
String sqlWhere = (String) sqlWhereWithValues.get(0);
sql += sqlWhere;
// 得到SQL条件中占位符的值
values = (List<Object>) sqlWhereWithValues.get(1);
}
}
// 设置参数占位符的值
try {
if (values != null) {
ps = JDBCUtils.getConnection().prepareStatement(sql);
setParameter(values, ps, true);
} else {
ps = JDBCUtils.getConnection().prepareStatement(sql);
}
// 开启事务
JDBCUtils.startTransaction();
// 执行SQL
rs = ps.executeQuery();
// 提交事务
JDBCUtils.commit();
while (rs.next()) {
T t = clazz.newInstance();
initObject(t, fields, rs);
list.add(t);
}
} catch (Exception e) {
// 回滚 事务
JDBCUtils.rollback();
e.printStackTrace();
} finally {
/* 关闭连接 */
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
JDBCUtils.close();
}
return list;
}
/**
* 根据结果集初始化对象
*/
private void initObject(T t, Field[] fields, ResultSet rs)
throws SQLException, IntrospectionException,
IllegalAccessException, InvocationTargetException {
for (Field field : fields) {
String propertyName = field.getName();
Object paramVal = null;
Class<?> clazzField = field.getType();
if (clazzField == String.class) {
paramVal = rs.getString(propertyName);
} else if (clazzField == short.class || clazzField == Short.class) {
paramVal = rs.getShort(propertyName);
} else if (clazzField == int.class || clazzField == Integer.class) {
paramVal = rs.getInt(propertyName);
} else if (clazzField == long.class || clazzField == Long.class) {
paramVal = rs.getLong(propertyName);
} else if (clazzField == float.class || clazzField == Float.class) {
paramVal = rs.getFloat(propertyName);
} else if (clazzField == double.class || clazzField == Double.class) {
paramVal = rs.getDouble(propertyName);
} else if (clazzField == boolean.class
|| clazzField == Boolean.class) {
paramVal = rs.getBoolean(propertyName);
} else if (clazzField == byte.class || clazzField == Byte.class) {
paramVal = rs.getByte(propertyName);
} else if (clazzField == char.class
|| clazzField == Character.class) {
paramVal = rs.getCharacterStream(propertyName);
} else if (clazzField == Date.class) {
paramVal = rs.getTimestamp(propertyName);
} else if (clazzField.isArray()) {
paramVal = rs.getString(propertyName).split(","); // 以逗号分隔的字符串
}
PropertyDescriptor pd = new PropertyDescriptor(propertyName,
t.getClass());
pd.getWriteMethod().invoke(t, paramVal);
}
}
/**
* 根据条件,返回sql条件和条件中占位符的值
*
* @param sqlWhereMap
* key:字段名 value:字段值
* @return 第一个元素为SQL条件,第二个元素为SQL条件中占位符的值
*/
private List<Object> getSqlWhereWithValues(Map<String, Object> sqlWhereMap) {
if (sqlWhereMap.size() < 1)
return null;
List<Object> list = new ArrayList<Object>();
List<Object> fieldValues = new ArrayList<Object>();
StringBuffer sqlWhere = new StringBuffer(" where ");
Set<Entry<String, Object>> entrySets = sqlWhereMap.entrySet();
for (Iterator<Entry<String, Object>> iteraotr = entrySets.iterator(); iteraotr
.hasNext();) {
Entry<String, Object> entrySet = iteraotr.next();
fieldValues.add(entrySet.getValue());
Object value = entrySet.getValue();
if (value.getClass() == String.class) {
sqlWhere.append(entrySet.getKey()).append(" like ").append("?")
.append(" and ");
} else {
sqlWhere.append(entrySet.getKey()).append("=").append("?")
.append(" and ");
}
}
sqlWhere.delete(sqlWhere.lastIndexOf("and"), sqlWhere.length());
list.add(sqlWhere.toString());
list.add(fieldValues);
return list;
}
/**
* 获得表名
*/
private String getTableName(Class<?> clazz) throws Exception {
if (clazz.isAnnotationPresent(Table.class)) {
Table table = clazz.getAnnotation(Table.class);
return table.value();
} else {
throw new Exception(clazz.getName() + " is not Entity Annotation.");
}
}
/**
* 设置SQL参数占位符的值
*/
private void setParameter(List<Object> values, PreparedStatement ps,
boolean isSearch) throws SQLException {
for (int i = 1; i <= values.size(); i++) {
Object fieldValue = values.get(i - 1);
Class<?> clazzValue = fieldValue.getClass();
if (clazzValue == String.class) {
if (isSearch)
ps.setString(i, "%" + (String) fieldValue + "%");
else
ps.setString(i, (String) fieldValue);
} else if (clazzValue == boolean.class
|| clazzValue == Boolean.class) {
ps.setBoolean(i, (Boolean) fieldValue);
} else if (clazzValue == byte.class || clazzValue == Byte.class) {
ps.setByte(i, (Byte) fieldValue);
} else if (clazzValue == char.class
|| clazzValue == Character.class) {
ps.setObject(i, fieldValue, Types.CHAR);
} else if (clazzValue == Date.class) {
ps.setTimestamp(i, new Timestamp(((Date) fieldValue).getTime()));
} else if (clazzValue.isArray()) {
Object[] arrayValue = (Object[]) fieldValue;
StringBuffer sb = new StringBuffer();
for (int j = 0; j < arrayValue.length; j++) {
sb.append(arrayValue[j]).append("、");
}
ps.setString(i, sb.deleteCharAt(sb.length() - 1).toString());
} else {
ps.setObject(i, fieldValue, Types.NUMERIC);
}
}
}
}
5、创建jdbcutils类
package b.w.xqp.util;
import java.sql.Connection;
import java.sql.SQLException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.druid.pool.DruidDataSource;
/**
* JDBC封装类
*
* @author T
*
*/
@SuppressWarnings("all")
public class JDBCUtils {
private static final Logger logger = LoggerFactory
.getLogger(JDBCUtils.class);
private static DruidDataSource dataSource = new DruidDataSource();
// 声明线程共享变量
public static ThreadLocal container = new ThreadLocal();
// 配置说明,参考官方网址
// http://blog.163.com/hongwei_benbear/blog/static/1183952912013518405588/
static {
dataSource
.setUrl("jdbc:mysql://localhost:3306/testdianshang?useUnicode=true&characterEncoding=utf-8");
dataSource.setUsername("root");// 用户名
dataSource.setPassword("123456");// 密码
dataSource.setInitialSize(2);
dataSource.setMaxActive(20);
dataSource.setMinIdle(0);
dataSource.setMaxWait(60000);
dataSource.setValidationQuery("SELECT 1");
dataSource.setTestOnBorrow(false);
dataSource.setTestWhileIdle(true);
dataSource.setPoolPreparedStatements(false);
}
/** 获取链接 */
public static Connection getConnection() {
Connection conn = null;
try {
conn = dataSource.getConnection();
logger.debug("{}:链接已经开始", Thread.currentThread().getName());
container.set(conn);
} catch (Exception e) {
logger.error("获取链接失败{}", e);
}
return conn;
}
/*** 获取当前线程上的连接开启事务 */
public static void startTransaction() {
Connection conn = (Connection) container.get();// 首先获取当前线程的连接
if (conn == null) {// 如果连接为空
conn = getConnection();// 从连接池中获取连接
container.set(conn);// 将此连接放在当前线程上
logger.debug("{}:链接为空,重新获取链接", Thread.currentThread().getName());
} else {
try {
conn.setAutoCommit(false);// 开启事务
logger.debug("{}:开启事务", Thread.currentThread().getName());
} catch (Exception e) {
logger.error("开启事务失败{}", e);
}
}
}
/** 提交事务 */
public static void commit() {
try {
Connection conn = (Connection) container.get();// 从当前线程上获取连接if(conn!=null){//如果连接为空,则不做处理
if (null != conn) {
conn.commit();// 提交事务
logger.debug("{}:事务已经提交", Thread.currentThread().getName());
}
} catch (Exception e) {
logger.error("事务提交失败{}", e);
}
}
/*** 回滚事务 */
public static void rollback() {
try {
Connection conn = (Connection) container.get();// 检查当前线程是否存在连接
if (conn != null) {
conn.rollback();// 回滚事务
logger.debug("{}:事务已经回滚", Thread.currentThread().getName());
container.remove();// 如果回滚了,就移除这个连接
logger.debug("{}:移出当前链接", Thread.currentThread().getName());
}
} catch (Exception e) {
logger.error("事务回滚失败{}", e);
}
}
/*** 关闭连接 */
public static void close() {
try {
Connection conn = (Connection) container.get();
if (conn != null) {
conn.close();
logger.debug("{}:链接关闭", Thread.currentThread().getName());
}
} catch (SQLException e) {
logger.error("链接关闭失败{}", e);
} finally {
try {
container.remove();// 从当前线程移除连接切记
logger.debug("{}:移出当前链接", Thread.currentThread().getName());
} catch (Exception ex) {
logger.error("移出链接失败{}", ex);
}
}
}
}
5、测试使用
package b.w.xqp.pojo;
import java.util.Date;
import b.w.xqp.anno.Column;
import b.w.xqp.anno.Id;
import b.w.xqp.anno.Table;
/**
* 图书
*/
@Table("t_book")
// 表名
public class Book {
/**
* 图书编号
*/
@Id("t_isbn")
private String isbn;
/**
* 书名
*/
@Column("t_name")
private String name;
/**
* 作者
*/
@Column("t_author")
private String author;
/**
* 出版社
*/
@Column("t_publishing")
private String publishing;
/**
* 出版时间
*/
@Column(value = "t_pubdate")
private Date pubdate;
/**
* 价格
*/
@Column(value = "t_price")
private String price;
public String getIsbn() {
return isbn;
}
public void setIsbn(String isbn) {
this.isbn = isbn;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getPublishing() {
return publishing;
}
public void setPublishing(String publishing) {
this.publishing = publishing;
}
public Date getPubdate() {
return pubdate;
}
public void setPubdate(Date pubdate) {
this.pubdate = pubdate;
}
public String getPrice() {
return price;
}
public void setPrice(String price) {
this.price = price;
}
@Override
public String toString() {
return "书名: " + name + " 图书编号: " + isbn + " 作者: " + author + " 出版社: "
+ publishing + " 出版时间: " + pubdate + " 价格: " + price;
}
}
package b.w.xqp.controller;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.junit.Test;
import b.w.xqp.dao.base.JdbcGenericDao;
import b.w.xqp.dao.base.JdbcGenericDaoImpl;
import b.w.xqp.pojo.Book;
public class JdbcGenericDaoTest {
private static JdbcGenericDao<Book> bookDao = new JdbcGenericDaoImpl<Book>();
@Test
public void save() throws Exception {
Book book = new Book();
book.setAuthor("柳岩");
book.setIsbn("11");
book.setName("大的秘籍");
book.setPrice("12.7");
book.setPubdate(new Date());
// book.setPublishing("上海出版社");
bookDao.save(book);
}
@Test
public void delete() throws Exception {
bookDao.delete("5", Book.class);
}
@Test
public void update() throws Exception {
Book book = new Book();
book.setIsbn("10");
book.setPublishing("上海出版社");
bookDao.update(book);
}
@Test
public void testGet() throws Exception {
System.out.println("\n-------------测试查询一条记录--------------------");
Book book = bookDao.get("4", Book.class);
System.out.println(book);
}
@Test
public void testStudentFindAll2() throws Exception {
System.out
.println("\n-------------更新、删除前,测试根据条件查询所有记录--------------------");
Map<String, Object> sqlWhereMap = new HashMap<String, Object>();
// sqlWhereMap.put("t_isbn", "9787111213826");
// sqlWhereMap.put("t_name", "Java");
sqlWhereMap.put("t_publishing", "上海出版社");
// sqlWhereMap.put("t_pubdate", new Date(new
// SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("2007-01-01 12:06:00").getTime()));
List<Book> books = bookDao.findAllByConditions(null, Book.class);
for (Book book : books) {
System.out.println(book);
}
}
}