正文
SQLHelper、DBUtil终极封装
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
DBUtil.java
package org.guangsoft.util; import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties; import org.apache.commons.beanutils.BeanUtils; /**
*
* @author guanghe
*/
public class DBUtil
{
//定义连接资源
private static Connection ct = null;
private static PreparedStatement ps = null;
private static ResultSet rs = null; //定义配置参数
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null; //定义配置文件引入
private static Properties pp = null;
private static InputStream is = null; //读取配置参数，加载驱动
static
{
try
{
pp = new Properties();
is = DBUtil.class.getClassLoader().getResourceAsStream("org/guangsoft/util/db.properties");
pp.load(is);
driver = pp.getProperty("driver");
url = pp.getProperty("url");
username = pp.getProperty("username");
password = pp.getProperty("password");
Class.forName(driver);
}
catch (Exception e)
{
e.printStackTrace();
System.exit(0);
}
finally
{
try
{
is.close();
}
catch (Exception e)
{
e.printStackTrace();
}
is = null;
}
} //获取连接
public static Connection getConnection()
{
try
{
ct = DriverManager.getConnection(url, username, password);
}
catch (Exception e)
{
e.printStackTrace();
}
return ct;
} //执行DQL查询
public static<T> List<T> executeQuery(String sql, Object[] parameters,Class<T> clazz)
{
List<T> list = new ArrayList<T>();
try
{
//得到数据结果集
ct = getConnection();
ps = ct.prepareStatement(sql);
if (parameters != null)
{
for (int i = 0; i < parameters.length; i++)
{
ps.setObject(i + 1, parameters[i]);
}
}
rs = ps.executeQuery(); //封装数据
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while(rs.next())
{
T t = clazz.newInstance();
for(int i = 0; i < columnCount; i++)
{
String columnName = metaData.getColumnName(i+1);
Object value = rs.getObject(columnName);
BeanUtils.copyProperty(t, columnName, value);
}
list.add(t);
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
close();
}
return list;
} //执行DML更新
public static int executeUpdate(String sql, Object[] parameters)
{
try
{
ct = getConnection();
ps = ct.prepareStatement(sql);
if (parameters != null)
{
for (int i = 0; i < parameters.length; i++)
{
ps.setObject(i + 1, parameters[i]);
}
}
return ps.executeUpdate();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
close();
}
return 0;
} //关闭所有资源连接
public static void close()
{
if (rs != null)
{
try
{
rs.close();
}
catch (Exception e)
{
e.printStackTrace();
}
rs = null;
}
if (ps != null)
{
try
{
ps.close();
}
catch (Exception e)
{
e.printStackTrace();
}
ps = null;
}
if (null != ct)
{
try
{
ct.close();
}
catch (Exception e)
{
e.printStackTrace();
}
ct = null;
}
} }
db.properties
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/test
username = root
password =root
