packagecom.hy.fddsvr.utils;importjava.io.IOException;importjava.io.InputStream;importjava.lang.reflect.Field;import java.sql.*;import java.util.*;public classDBManager {/***@paramargs*/
staticString driver;staticString url;staticString username;staticString password;privateConnection connection;privatePreparedStatement pstmt;privateResultSet resultSet;publicDBManager() {//从配置文件db.properties中读取数据库配置信息
InputStream in = DBManager.class.getClassLoader().getResourceAsStream("db.properties");
Properties pro= newProperties();try{
pro.load(in);
}catch(IOException e) {
e.printStackTrace();
}//数据库驱动
driver = pro.getProperty("driver");//服务器地址
url = pro.getProperty("url");//数据库用户名
username = pro.getProperty("username");//数据库密码
password = pro.getProperty("password");try{//连接数据库
getConnection();
}catch(Exception e) {
e.printStackTrace();
}
}/*** 获得数据库的连接
*
*@return*@throwsClassNotFoundException*/
publicConnection getConnection() {try{
Class.forName(driver);
connection=DriverManager.getConnection(url, username, password);
}catch(SQLException e) {//TODO Auto-generated catch block
e.printStackTrace();
}catch(Exception e) {
System.out.println("fail to connect database");
}returnconnection;
}/*** 获得最大ID
*
*@paramtableName
*@return*@throwsSQLException*/
public intgetMaxId(String tableName) {
Statement state= null;
ResultSet rs= null;int maxId = 0;try{
state=connection.createStatement();
String sql= "select max(autoid) maxId from " +tableName;
rs=state.executeQuery(sql);//从resultset对象中将数据取出
if(rs.next()) {
maxId= rs.getInt("maxId");
}
}catch(Exception ex) {//TODO Auto-generated catch block
ex.printStackTrace();
}return ++maxId;
}/*** 判断当前sql语句返回是否为空
*
*@paramsql
*@return返回true表示空数据集,返回false表示非空数据集
*@throwsSQLException*/
public booleanCheckDataIsEmpty(String sql) {
Statement state= null;
ResultSet rs= null;boolean isempty=true;try{
state=connection.createStatement();
rs=state.executeQuery(sql);if (rs==null){
isempty=true;
}else{if(rs.next()){
isempty=false;
}else{
isempty=true;
}
}
}catch(Exception ex) {//TODO Auto-generated catch block
ex.printStackTrace();
}returnisempty;
}/*** 获取某个sql语句的首行首列值
*
*@paramsql
*@return返回首行首列值
*@throwsSQLException*/
publicString GetTopValue(String sql) {
Statement state= null;
ResultSet rs= null;
String topvalue="";try{
state=connection.createStatement();
rs=state.executeQuery(sql);if (rs!=null){if(rs.next()){
topvalue= rs.getString(1);
}
}
}catch(Exception ex) {
ex.printStackTrace();
}returntopvalue;
}/*** 获取某个sql语句的首行记录集
*
*@paramsql
*@return返回首行记录集
*@throwsSQLException*/
publicResultSet GetTopDataSet(String sql) {
Statement state= null;
ResultSet rs= null;try{
state=connection.createStatement();
rs=state.executeQuery(sql);if (rs!=null){
rs.next();
}
}catch(Exception ex) {
ex.printStackTrace();
}returnrs;
}/*** 执行某个sql语句的更新操作
*
*@paramsql
*@return执行成功返回true,失败返回false
*@throwsSQLException*/
public booleanExecSql(String sql) {
Statement state= null;int iflag=-1;boolean res=false;try{
pstmt=connection.prepareStatement(sql);
iflag=pstmt.executeUpdate();
res= (iflag > 0 ? true : false);
}catch(Exception ex) {
ex.printStackTrace();
}returnres;
}/*** 增加、删除、改
*
*@paramsql
*@paramparams
*@return*@throwsSQLException*/
public boolean updateByPreparedStatement(String sql, Listparams)throwsSQLException {boolean flag = false;int result = -1;
pstmt=connection.prepareStatement(sql);int index = 1;if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
result=pstmt.executeUpdate();
flag= result > 0 ? true : false;returnflag;
}/*** 查询单条记录
*
*@paramsql
*@paramparams
*@return*@throwsSQLException*/
public Map findSimpleResult(String sql, Listparams)throwsSQLException {
Map map = new HashMap();int index = 1;
pstmt=connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet= pstmt.executeQuery();//返回查询结果
ResultSetMetaData metaData =resultSet.getMetaData();int col_len =metaData.getColumnCount();while(resultSet.next()) {for (int i = 0; i < col_len; i++) {
String cols_name= metaData.getColumnName(i + 1);
Object cols_value=resultSet.getObject(cols_name);if (cols_value == null) {
cols_value= "";
}
map.put(cols_name, cols_value);
}
}returnmap;
}/*** 查询多条记录
*
*@paramsql
*@paramparams
*@return*@throwsSQLException*/
public List>findModeResult(String sql,
List params) throwsSQLException {
List> list = new ArrayList>();int index = 1;
pstmt=connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet=pstmt.executeQuery();
ResultSetMetaData metaData=resultSet.getMetaData();int cols_len =metaData.getColumnCount();while(resultSet.next()) {
Map map = new HashMap();for (int i = 0; i < cols_len; i++) {
String cols_name= metaData.getColumnName(i + 1);
Object cols_value=resultSet.getObject(cols_name);if (cols_value == null) {
cols_value= "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}returnlist;
}/*** 通过反射机制查询单条记录
*
*@paramsql
*@paramparams
*@paramcls
*@return*@throwsException*/
public T findSimpleRefResult(String sql, Listparams,
Class cls) throwsException {
T resultObject= null;int index = 1;
pstmt=connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet=pstmt.executeQuery();
ResultSetMetaData metaData=resultSet.getMetaData();int cols_len =metaData.getColumnCount();while(resultSet.next()) {//通过反射机制创建一个实例
resultObject =cls.newInstance();for (int i = 0; i < cols_len; i++) {
String cols_name= metaData.getColumnName(i + 1);
Object cols_value=resultSet.getObject(cols_name);int type = metaData.getColumnType(i + 1);if (cols_value == null) {if (type ==Types.INTEGER) {
cols_value= 0;
}else{
cols_value= "";
}
}
Field field=cls.getDeclaredField(cols_name.toLowerCase());
field.setAccessible(true); //打开javabean的访问权限
if (type ==Types.TIMESTAMP) {
field.set(resultObject, String.valueOf(cols_value));
}else{
field.set(resultObject, cols_value);
}
}
}returnresultObject;
}/*** 通过反射机制查询多条记录
*
*@paramsql
*@paramparams
*@paramcls
*@return*@throwsException*/
public List findMoreRefResult(String sql, Listparams,
Class cls) throwsException {
List list = new ArrayList();int index = 1;
pstmt=connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet=pstmt.executeQuery();
ResultSetMetaData metaData=resultSet.getMetaData();int cols_len =metaData.getColumnCount();while(resultSet.next()) {//通过反射机制创建一个实例
T resultObject =cls.newInstance();for (int i = 0; i < cols_len; i++) {
String cols_name= metaData.getColumnName(i + 1);
Object cols_value=resultSet.getObject(cols_name);if (cols_value == null) {
cols_value= "";
}
Field field=cls.getDeclaredField(cols_name);
field.setAccessible(true); //打开javabean的访问权限
field.set(resultObject, cols_value);
}
list.add(resultObject);
}returnlist;
}/*** 释放数据库连接*/
public voidreleaseConn() {if (resultSet != null) {try{
resultSet.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}