com.hy.fddsvr.utils;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*
;
import java.util.*
;
public class DBManager {
/**
* @param args
*/
static String driver;
static String url;
static String username;
static String password;
private Connection connection;
private PreparedStatement pstmt;
private ResultSet resultSet;
public DBManager() {
//从配置文件db.properties中读取数据库配置信息
InputStream in = DBManager.
class.getClassLoader().getResourceAsStream("db.properties"
);
Properties pro =
new Properties();
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
* @throws ClassNotFoundException
*/
public Connection 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"
);
}
return connection;
}
/**
* 获得最大ID
*
* @param tableName
* @return
* @throws SQLException
*/
public int getMaxId(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语句返回是否为空
*
* @param sql
* @return 返回true表示空数据集，返回false表示非空数据集
* @throws SQLException
*/
public boolean CheckDataIsEmpty(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();
}
return isempty;
}
/**
* 获取某个sql语句的首行首列值
*
* @param sql
* @return 返回首行首列值
* @throws SQLException
*/
public String 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();
}
return topvalue;
}
/**
* 获取某个sql语句的首行记录集
*
* @param sql
* @return 返回首行记录集
* @throws SQLException
*/
public ResultSet 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();
}
return rs;
}
/**
* 执行某个sql语句的更新操作
*
* @param sql
* @return 执行成功返回true,失败返回false
* @throws SQLException
*/
public boolean ExecSql(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();
}
return res;
}
/**
* 增加、删除、改
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public boolean updateByPreparedStatement(String sql, List<Object>
params)
throws SQLException {
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;
return flag;
}
/**
* 查询单条记录
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public Map<String, Object> findSimpleResult(String sql, List<Object>
params)
throws SQLException {
Map<String, Object> map =
new HashMap<String, Object>
();
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);
}
}
return map;
}
/**
* 查询多条记录
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public List<Map<String, Object>>
findModeResult(String sql,
List<Object> params)
throws SQLException {
List<Map<String, Object>> list =
new ArrayList<Map<String, Object>>
();
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<String, Object> map =
new HashMap<String, Object>
();
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);
}
return list;
}
/**
* 通过反射机制查询单条记录
*
* @param sql
* @param params
* @param cls
* @return
* @throws Exception
*/
public <T> T findSimpleRefResult(String sql, List<Object>
params,
Class<T> cls)
throws Exception {
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);
}
}
}
return resultObject;
}
/**
* 通过反射机制查询多条记录
*
* @param sql
* @param params
* @param cls
* @return
* @throws Exception
*/
public <T> List<T> findMoreRefResult(String sql, List<Object>
params,
Class<T> cls)
throws Exception {
List<T> list =
new ArrayList<T>
();
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);
}
return list;
}
/**
* 释放数据库连接
*/
public void releaseConn() {
if (resultSet !=
null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
在 resources目录下创建一个dbo.properties文件，内容：
driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
url = jdbc:sqlserver://localhost:1433;DatabaseName=MyDB;
username = sa
password = 123
调用方式：
DBManager db=new DBManager();
db.ExecSql("update mytb set a1=0 where b1 is null");
java通用的jdbc数据库操作类
标签：win generate col tst password amp exception 实例 getc
