jdbc数据库

2017年6月23号，学习记录，最近三天都在鼓捣数据库

     为了实现代码的实用性，使用反射机制，

首先由一个DbUtil类，用于实现获取连接，和释放资源的方法，由于主要是功能方法类所以用static来修饰，直接调用即可

public class DbUtil {
private static ThreadLocal<Connection> connections=new ThreadLocal<Connection>();
static{
try {
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}

/**
* 获取数据库连接
* @throws SQLException 
*/
public static Connection getConnection() throws SQLException{
//检查当前线程是否已经创建连接
Connection conn=connections.get();
if(conn==null){
//如果没有则创建一个新的连接，保存到ThreadLocal中
//以便当前线程后续得到的是同一个连接
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/taobao","root","123456");
connections.set(conn);
}
return conn;
}

/**
* 获取当前数据库连接
* @return
*/
public static Connection getCurrentConnection(){
return connections.get();
}


/**
* 释放数据库资源
* @param conn数据库连接
* @param stmt  sql执行对象
* @param res  查询结果集
*/
public static void release(Connection conn,Statement stmt,ResultSet res){
if(conn!=null){
//
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

if(res!=null){
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

}

然后有一个DaoUtil类实现增删查改

public class DaoUtil {


/**
* 开启一个数据库事务. <br/>
* 
* @version 1.0
* 
*/
public static void startTrasaction() throws SQLException {
Connection conn = DbUtil.getConnection();
conn.setAutoCommit(false);
}


/**
* 提交一个事务. <br/>
* 
* @version 1.0
* 
* @throws SQLException
*/
public static void commit() throws SQLException {
Connection conn = DbUtil.getCurrentConnection();
if (conn != null) {
conn.commit();
// 还原自动提交
conn.setAutoCommit(true);
}
}


/**
* 回滚一个事务. <br/>
* 
* @version 1.0
* 
* @throws SQLException
*/
public static void rollback() throws SQLException {
Connection conn = DbUtil.getCurrentConnection();
if (conn != null) {
conn.rollback();
// 还原自动提交
conn.setAutoCommit(true);
}
}


/**
* 增加删除和修改一条数据
* 
* @param sql
* @param objects
* @return
*/
public static int updateOne(String sql, Object... objects) {
Connection conn = null;
PreparedStatement preStmt = null;
int rows = 0;
try {
conn = DbUtil.getConnection();
preStmt = conn.prepareStatement(sql);
setObjects(preStmt, objects);
rows = preStmt.executeUpdate();
return rows;
} catch (SQLException e) {
e.printStackTrace();
return rows;
} finally {
DbUtil.release(conn, preStmt, null);
}
}


private static void setObjects(PreparedStatement preStmt, Object[] objects)
throws SQLException {
for (int i = 0; i < objects.length; i++) {
preStmt.setObject(i + 1, objects[i]);
}
}


      以上的方法比较粗糙，并且可以实现增删改三个方法并用
/*********************************************************************/

   


/**
* 预编译设置参数上去
* 
* @param preStmt
* @param objects
* @throws SQLException
*/
private static void setObjects1(PreparedStatement preStmt, Object[] objects)
throws SQLException {
Class<?> clazz = null;
Object obj = null;
for (int i = 1; i <= objects.length; i++) {
obj = objects[i - 1];
if (obj == null) {
preStmt.setNull(i + 1, 0);
} else {
clazz = obj.getClass();
// 设置 八种基本类型及其包装类型、String、BigDecimal 和 Object 类型的参数
if (clazz == Boolean.class || clazz == Boolean.TYPE) {
preStmt.setBoolean(i, Boolean.parseBoolean(obj.toString()));
} else if (clazz == Byte.class || clazz == Byte.TYPE) {
preStmt.setByte(i, Byte.parseByte(obj.toString()));
} else if (clazz == Short.class || clazz == Short.TYPE) {
preStmt.setShort(i, Short.parseShort(obj.toString()));
} else if (clazz == Character.class || clazz == Character.TYPE) {
preStmt.setString(i, String.valueOf(obj.toString()));
} else if (clazz == Integer.class || clazz == Integer.TYPE) {
preStmt.setInt(i, Integer.parseInt(obj.toString()));
} else if (clazz == Long.class || clazz == Long.TYPE) {
preStmt.setLong(i, Long.parseLong(obj.toString()));
} else if (clazz == Float.class || clazz == Float.TYPE) {
preStmt.setFloat(i, Float.parseFloat(obj.toString()));
} else if (clazz == Double.class || clazz == Double.TYPE) {
preStmt.setDouble(i, Double.parseDouble(obj.toString()));
} else if (clazz == String.class) {
preStmt.setString(i, obj.toString());
} else if (clazz == BigDecimal.class) {
preStmt.setBigDecimal(i, (BigDecimal) obj);
} else {
preStmt.setObject(i, obj);
}
}
}
preStmt.addBatch();
}


/**
* 更据属性名获得属性
* 
* @param clazz
* @param attName
* @return
*/
private static <T> Field getAttTypeByAttName(Class<T> clazz, String attName) {
try {
return clazz.getDeclaredField(attName);
} catch (NoSuchFieldException e) {
Class<? super T> superclass = clazz.getSuperclass();
if (superclass == null) {
return null;
}
return getAttTypeByAttName(superclass, attName);
}
}


/**
* 通过属性名得到set方法名
* 
* @param methodName
* @return
*/
private static String getSetMethodByName(String methodName) {
if (methodName == null || "".equals(methodName = methodName.trim())) {
return null;
} else if (methodName.length() == 1) {
return "set" + methodName.toUpperCase();
} else {
return "set" + methodName.substring(0, 1).toUpperCase()
+ methodName.substring(1);
}
}


private static String getGetMethodByName(String methodName) {
if (methodName == null || "".equals(methodName = methodName.trim())) {
return null;
} else if (methodName.length() == 1) {
return "get" + methodName.toUpperCase();
} else {
return "get" + methodName.substring(0, 1).toUpperCase()
+ methodName.substring(1);
}
}


/**
* 通过方法名获得方法
* 
* @param clazz
* @param methodName
* @return
*/
private static <T> Method getSetMethod(Class<T> clazz, String methodName,
Field field) {
Class<?> type = field.getType();
String setMethodByName = getSetMethodByName(methodName);
try {
return clazz.getDeclaredMethod(setMethodByName, type);
} catch (NoSuchMethodException e) {
Class<? super T> superclass = clazz.getSuperclass();
if (superclass == null) {
return null;
}
return getSetMethod(superclass, methodName, field);
}
}


/**
* 通过查询结果集得到的元数据只包含要查找的元素，此处应注意数据库和bean中的属性名要一致，
* 通过元数据可以和bean中的属性一致可以获得此属性名对应的属性
* 
* @param metaData
* @param clazz
* @return Map key为T bean的属性名String vlaue为属性名对应的属性Field
* @throws SQLException
*/
private static <T> Map<String, Field> getSetMethod(
ResultSetMetaData metaData, Class<T> clazz) throws SQLException {
int columnCount = metaData.getColumnCount();
Map<String, Field> map = new HashMap<String, Field>();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
map.put(columnName, getAttTypeByAttName(clazz, columnName));
}
return map;
}


/**
* 此方法的作用是查询到数据库某一元素对应的值，返回
* 
* @param res
* @param fieldName
* @param field
* @return 返回某一元素对应的值
* @throws SQLException
*/
private static Object getResultSetData(ResultSet res, String fieldName,
Field field) throws SQLException {
Class<?> type = field.getType();
if (type == Boolean.class || type == Boolean.TYPE) {
return res.getBoolean(fieldName);
} else if (type == Byte.class || type == Byte.TYPE) {
return res.getByte(fieldName);
} else if (type == Short.class || type == Short.TYPE) {
return res.getShort(fieldName);
} else if (type == Character.class || type == Character.TYPE) {
return res.getCharacterStream(fieldName);
} else if (type == Integer.class || type == Integer.TYPE) {
return res.getInt(fieldName);
} else if (type == Long.class || type == Long.TYPE) {
return res.getLong(fieldName);
} else if (type == Float.class || type == Float.TYPE) {
return res.getFloat(fieldName);
} else if (type == Double.class || type == Double.TYPE) {
return res.getDouble(fieldName);
} else if (type == String.class) {
return res.getString(fieldName);
} else if (type == BigDecimal.class) {
return res.getBigDecimal(fieldName);
} else {
return res.getObject(fieldName);
}
}


/**
* 返回包装好的T对象
* 
* @param clazz
* @param res
* @param map
* @return
*/
private static <T> T Result(Class<T> clazz, ResultSet res,
Map<String, Field> map) {
T object = null;
try {
// 创建对象
object = clazz.newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
Iterator<Entry<String, Field>> iterator = map.entrySet().iterator();
while (iterator.hasNext()) {
Entry<String, Field> next = iterator.next();
String key = next.getKey();// 得到bean中的属性名称
Field value = next.getValue();// 得到bean中属性名称对应的属性
Object resultSetData = null;
try {
// 得到此属性对应的值
resultSetData = getResultSetData(res, key, value);
} catch (SQLException e) {
e.printStackTrace();
}
// 得到此属性名称对应的Set方法
Method setMethod = getSetMethod(clazz, key, value);
try {
// 调用该方法。object是对象，resultSetData是数据，就是这样吧一条数据设置上去,,类似于object.set(resultData)
setMethod.invoke(object, resultSetData);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
return object;
}


/**
* 查询单个对象
* 
* @param sql
* @param clazz
* @param id
* @return
*/
public static <T> T queryByObject(String sql, Class<T> clazz,
Object... objects) {
Connection conn = null;
PreparedStatement preStmt = null;
T t = null;
try {
conn = DbUtil.getConnection();
preStmt = conn.prepareStatement(sql);
setObjects1(preStmt, objects);
ResultSet resultSet = preStmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
Map<String, Field> setMethod = getSetMethod(metaData, clazz);
while (resultSet.next()) {
t = Result(clazz, resultSet, setMethod);
}
return t;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
DbUtil.release(conn, preStmt, null);
}


}


/**
* 查询多个对象
* 
* @return
*/
public static <T> List<T> queryforList(String sql, Class<T> clazz,
Object... objects) {
Connection conn = null;
PreparedStatement preStmt = null;
List<T> list = new ArrayList<T>();
try {
conn = DbUtil.getConnection();
preStmt = conn.prepareStatement(sql);
setObjects1(preStmt, objects);
ResultSet resultSet = preStmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
Map<String, Field> setMethod = getSetMethod(metaData, clazz);
while (resultSet.next()) {
list.add(Result(clazz, resultSet, setMethod));
}
return list;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
DbUtil.release(conn, preStmt, null);
}
}


/**
* 增加删除修改一条数据
* 
* @param sql
* @param clazz
* @param t
*/
public static <T> int updateOne1(String sql, Class<T> clazz,
Object... objects) {
Connection conn = null;
PreparedStatement preStmt = null;
try {
conn = DbUtil.getConnection();
preStmt = conn.prepareStatement(sql);

setObjects1(preStmt, objects);
return preStmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
return 0;
} finally {
DbUtil.release(conn, preStmt, null);
}
}


/**********************************************************************/
/**
* 增加多个数据，，但还是有小bug
* @param sql
* @param clazz
* @param objects
* @return
*/
public static <T> void addBatch(String sql, Class<T> clazz, List<T> list) {
Connection conn = null;
PreparedStatement preStmt = null;
try {
conn = DbUtil.getConnection();
preStmt = conn.prepareStatement(sql);
for (int i = 0; i < list.size(); i++) {
T t=list.get(i);
List ych = ych(t);
Object[] object=new Object[ych.size()];
for(int k=0;k<ych.size();k++){
object[k]=ych.get(k);
}
setObjects1(preStmt, object);
}
preStmt.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtil.release(conn, preStmt, null);
}
}


private static <T> List ych(T clazz) {
Class<? extends Object> class1 = clazz.getClass();
Method[] declaredMethods = class1.getDeclaredMethods();
List list=new ArrayList();
for (int i = 0; i < declaredMethods.length; i++) {
String name = declaredMethods[i].getName().substring(0, 3);
if ("getId".equals(declaredMethods[i].getName())) {
continue;
}
if ("get".equals(name)) {
try {
list.add(declaredMethods[i].invoke(clazz));
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
return list;
}
}