package com.xxx.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.List;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.apache.log4j.Logger;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
import com.microsoft.sqlserver.jdbc.SQLServerException;
/**
*
*
* Supply the basic methods to operate DB by java
*/
public class DbHandler {
private String serverName = "";
private String portNumber = "";
private String user = "";
private String password = "";
private String databaseName = "";
private boolean integratedSecurity = true;
// Declare the JDBC objects.
private Connection con = null;
public ResultSet rs = null;
static Logger logger = Logger.getLogger(DbHandler.class);
/**
* Initialize all the parameters of DB
*
* @param serverName
* @param portNumber Set as '-1': Use the default port number '1433'
* @param user
* @param password
* @param databaseName
* @param integratedSecurity
*/
public DbHandler(String serverName, String portNumber, String user, Stringpassword, String databaseName, boolean integratedSecurity) {
this.setServerName(serverName);
this.setPortNumber(portNumber);
this.setUser(user);
this.setPassword(password);
this.setDatabaseName(databaseName);
this.setIntegratedSecurity(integratedSecurity);
}
/**
* Get the connection object of JDBC
*
* @return
* @throws SQLServerException
*/
public boolean getConnection() {
boolean status = true;
try {
// Establish the connection.
Properties connectionInfo = new Properties();
connectionInfo.put("maxWait", "20000");
connectionInfo.put("maxActive", "30");
connectionInfo.put("url","jdbc:jtds:sqlserver://"+this.getServerName()+":"+this.getPortNumber()+";databaseName="+this.getDatabaseName()+";useCursors=true;loginTimeout=30;socketTimeout=120");
connectionInfo.put("driverClassName","net.sourceforge.jtds.jdbc.Driver");
connectionInfo.put("maxIdle", "5");
connectionInfo.put("password", this.getPassword());
connectionInfo.put("username", this.getUser());
DataSource ds =BasicDataSourceFactory.createDataSource(connectionInfo);
// SQLServerDataSource ds = new SQLServerDataSource();
// ds.setServerName(this.getServerName());
// ds.setPortNumber(this.getPortNumber());
// ds.setUser(this.getUser());
// ds.setPassword(this.getPassword());
// ds.setDatabaseName(this.getDatabaseName());
// ds.setIntegratedSecurity(this.getIntegratedSecurity());
this.con = ds.getConnection();
}
catch (SQLServerException e) {
logger.error("Connect DB falied", e);
status = false;
} catch (Exception e) {
logger.error("Connect DB falied", e);
status = false;
e.printStackTrace();
}
return status;
}
/**
* Clear Current result set and close connection
*/
public void destroy() {
if(rs != null || con != null)
logger.info("Close DB connection");
if (rs !=null) try {
rs.close();
rs = null;
}
catch(Exception e) {}
if (con != null) try {
con.close();
con = null;
}
catch(Exception e) {}
}
/**
* Executes the given SQL statement, which returns a single ResultSet object.
*
* @param sql
* @return
*/
public boolean execQuery(String sql) {
boolean status = true;
this.destroy();
try {
if(!this.getConnection())
return false;
Statement stmt =con.createStatement();
logger.info("Use DB " + this.getDatabaseName());
logger.info(sql);
rs = stmt.executeQuery(sql);
}
// Handle any errors that may have occurred.
catch(Exception e) {
logger.error("Query DB falied", e);
status = false;
}
return status;
}
/**
* Executes the given SQL statement, which returns a single ResultSet object.
*
* @param sql Might include '?'
* @param params Supply values of '?' in sql by order
* @return
*/
public boolean execQuery(String sql, List<Object> params){
boolean status = true;
this.destroy();
if(params == null)
return this.execQuery(sql);
try {
if(!this.getConnection())
return false;
PreparedStatement pstmt = con.prepareStatement(sql);
int i = 1;
for(Object p : params) {
pstmt.setObject(i, p);
String strP = "";
if(p == null)
strP = "null";
else
strP = (p.getClass().getName().equals("java.lang.String")) ?"'" + p.toString() + "'" : p.toString();
sql = sql.replaceFirst("\\?", strP);
i++;
}
logger.info("Use DB " + this.getDatabaseName());
logger.info(sql);
rs = pstmt.executeQuery();
}
// Handle any errors that may have occurred.
catch(Exception e) {
logger.error("Query DB falied", e);
status = false;
}
return status;
}
/**
* Select all values without specifying any conditions
*
* @param table
* @return
*/
public boolean selectAll(String table) {
return this.selectAsSpecified(table, null, null, null);
}
/**
* Select the specific values
*
* @param table Specify table name
* @param fields Specify fieldname by order
* @param conditions e.g. conditions.put(fieldName,valueObject)
* @param orderBy Specify field nameused to order the query result
* @return
*/
public boolean selectAsSpecified(String table, List<String> fields,Hashtable<String, Object> conditions, List<String> orderBy) {
String selectSql = "select ";
// Specify fields in the query
String field = separateFields(fields);
if(field.equals("")) {
selectSql += "* ";
}
else {
selectSql += field + " ";
}
// Specify to get values from which table
selectSql += "from " + table;
// Specify conditions in the query
List<Object> reCondition = getCondition(conditions);
List<Object> params = (ArrayList<Object>) reCondition.get(1);
selectSql += reCondition.get(0);
// Specify conditions in the query
String reOrder = separateFields(orderBy);
if(!reOrder.equals(""))
selectSql += " order by " + reOrder;
selectSql += ";";
return this.execQuery(selectSql, params);
}
/**
* Executes the given SQL statement, which may be an INSERT, UPDATE, or
* DELETE statement or an SQL statement that returns nothing, such as anSQL DDL statement.
*
* @param sql
* @return
*/
public boolean execUpdate(String sql){
boolean status = true;
try {
if(!this.getConnection())
return false;
Statement stmt =con.createStatement();
logger.info("Use DB " + this.getDatabaseName());
logger.info(sql);
stmt.executeUpdate(sql);
}
// Handle any errors that may have occurred.
catch(Exception e) {
logger.error("Update DB falied", e);
status = false;
}
finally {
this.destroy();
}
return status;
}
/**
* Executes the given SQL statement, which may be an INSERT, UPDATE, or
* DELETE statement or an SQL statement that returns nothing, such as an SQLDDL statement.
*
* @param sql Might include '?'
* @param params Supply values of '?' in sql by order
* @return
*/
public boolean execUpdate(String sql, List<Object> params){
boolean status = true;
if(params == null)
return execUpdate(sql);
try {
if(!this.getConnection())
return false;
PreparedStatement pstmt = con.prepareStatement(sql);
int i = 1;
for(Object p : params) {
pstmt.setObject(i, p);
String strP = "";
if(p == null)
strP = "null";
else
strP = (p.getClass().getName().equals("java.lang.String")) ?"'" + p.toString() + "'" : p.toString();
sql = sql.replaceFirst("\\?", strP);
i++;
}
logger.info("Use DB " + this.getDatabaseName());
logger.info(sql);
pstmt.executeUpdate();
}
// Handle any errors that may have occurred.
catch(Exception e) {
logger.error("Update DB falied", e);
status = false;
}
finally {
this.destroy();
}
return status;
}
/**
* Insert values to the specific table
*
* @param table Specify tablename
* @param fields Specify field name by order
* @param values Specify values by order
* @throws Exception
*/
public boolean insertData(String table, List<String> fields,List<Object> values) {
String insertSql = "insert into " + table;
// Specify fields in the query
if(fields != null)
insertSql += "(" + separateFields(fields) + ")";
// Specify values in the query
List<Object> reValue = separateValues(values);
List<Object> params = (ArrayList<Object>) reValue.get(1);
if(!reValue.get(0).equals(""))
insertSql += " values (" + reValue.get(0) + ");";
else {
logger.fatal("Values cannot be null");
return false;
}
return this.execUpdate(insertSql, params);
}
/**
* Update values
*
* @param table Specify thetable name
* @param values Specify values by order
* @param conditions e.g. conditions.put(fieldName,valueObject)
* @return
*/
public boolean updateData(String table, Hashtable<String, Object> values,Hashtable<String, Object> conditions) {
String updateSql = "update " + table;
// Specify values in the query
List<Object> reValue = separateValues(values);
List<Object> params = (ArrayList<Object>) reValue.get(1);
if(!reValue.get(0).equals(""))
updateSql += " set " + reValue.get(0);
// Specify conditions in the query
List<Object> reCondition = getCondition(conditions);
params.addAll((ArrayList<Object>) reCondition.get(1));
updateSql += reCondition.get(0) + ";";
return this.execUpdate(updateSql, params);
}
/**
* Delete rows in table
*
* @param table Specify thetable name
* @param conditions e.g. conditions.put(fieldName, valueObject)
* @return
*/
public boolean deleteData(String table, Hashtable<String, Object>conditions) {
String delSql = "delete from " + table;
// Specify conditions in the query
List<Object> reCondition = getCondition(conditions);
List<Object> params = (ArrayList<Object>) reCondition.get(1);
delSql += reCondition.get(0) + ";";
return this.execUpdate(delSql, params);
}
/**
* Construct list values as sql="?, ?,..." which will be included inSQL
*
* @param values Specify values by order
* @return sql:
* params: storeexact values by order, they will be set to PreparedStatement later
*/
public List<Object> separateValues(List<Object> values) {
String sql = "";
List<Object> params = new ArrayList<Object>();
if(values != null) {
int size = values.size();
for (int i = 0; i < size; i++) {
sql += "?";
if(i < size - 1) {
sql += ", ";
}
params.add(values.get(i));
}
}
List<Object> result = new ArrayList<Object>();
result.add(sql);
result.add(params);
return result;
}
/**
* Construct list values as sql="?, ?,..." which will be included inSQL
*
* @param values Specify values by order
* e.g. values.put(fieldName, valueObject)
* @return sql:
* params: storeexact values by order, they will be set to PreparedStatement later
*/
public List<Object> separateValues(Hashtable<String, Object>values) {
String sql = "";
List<Object> params = new ArrayList<Object>();
if(values != null) {
int size = values.size();
Enumeration<String> keys = values.keys();
for (int i = 0; i < size; i++) {
String key = (String) keys.nextElement();
Object value = values.get(key);
sql += key + "=?";
if(i < size - 1) {
sql += ", ";
}
params.add(value);
}
}
List<Object> result = new ArrayList<Object>();
result.add(sql);
result.add(params);
return result;
}
/**
* Construct list fields as sql="?, ?,..." which will be included inSQL
*
* @param fields Specify fields by order
* @return sql:
* params: storeexact values by order, they will be set to PreparedStatement later
*/
private String separateFields(List<String> fields) {
String sql = "";
if(fields != null) {
int size = fields.size();
for (int i = 0; i < size; i++) {
sql += fields.get(i);
if(i < size - 1) {
sql += ", ";
}
}
}
return sql;
}
/**
* Retrieves the value of the designated column in the current row of thisResultSet object as a String in the Java programming language.
*
* @param columnLabel
* @return
*/
public List<String> getStringByColName(String columnLabel) {
List<String> result = new ArrayList<String>();
try {
while (rs.next()) {
result.add(this.rs.getString(columnLabel));
}
} catch (SQLException e) {
logger.error("Read result set error", e);
}
return result;
}
/**
* Get the first String value by the specific column name
*
* @param columnLabel
* @return
* @throws Exception
*/
public String getFirstStringByColName(String columnLabel) throws Exception {
List<String> result = this.getStringByColName(columnLabel);
int size = result.size();
if(size == 0)
throw new Exception("0 row record");
else
return result.get(0);
}
/**
* Get the last String value by the specific column name
*
* @param columnLabel
* @return
* @throws Exception
*/
public String getLastStringByColName(String columnLabel) throws Exception {
List<String> result = this.getStringByColName(columnLabel);
int size = result.size();
if(size == 0)
throw new Exception("0 row record");
else
return result.get(size - 1);
}
/**
* Retrieves the value of the designated column in the current row of thisResultSet object as an int in the Java programming language.
*
* @param columnLabel
* @return
*/
public List<Integer> getIntByColName(String columnLabel) {
List<Integer> result = new ArrayList<Integer>();
try {
while (rs.next()) {
result.add(this.rs.getInt(columnLabel));
}
} catch (SQLException e) {
logger.error("Read result set error", e);
}
return result;
}
/**
* Get the first integer value by the specific column name
*
* @param columnLabel
* @return
* @throws Exception
*/
public Integer getFirstIntByColName(String columnLabel) throws Exception {
List<Integer> result = this.getIntByColName(columnLabel);
int size = result.size();
if(size == 0)
throw new Exception("0 row record");
else
return result.get(0);
}
/**
* Get the last integer value by the specific column name
*
* @param columnLabel
* @return
* @throws Exception
*/
public Integer getLastIntByColName(String columnLabel) throws Exception {
List<Integer> result = this.getIntByColName(columnLabel);
int size = result.size();
if(size == 0)
throw new Exception("0 row record");
else
return result.get(size - 1);
}
/**
* Construct list conditions as sql="where field1=?,field2=?,..." which will be included in SQL
*
* @param conditions e.g. conditions.put(fieldName,valueObject)
* @return
*/
public List<Object> getCondition(Hashtable<String, Object>conditions) {
String sql = "";
List<Object> params = new ArrayList<Object>();
if(conditions != null) {
int size = conditions.size();
Enumeration<String> keys = conditions.keys();
for (int i = 0; i < size; i++) {
String key = (String) keys.nextElement();
Object value = conditions.get(key);
key += "=?";
if(i == 0) {
sql += " where " + key;
}
else {
sql += " and " + key;
}
params.add(value);
}
}
List<Object> result = new ArrayList<Object>();
result.add(sql);
result.add(params);
return result;
}
/**
* @return the serverName
*/
public String getServerName() {
return serverName;
}
/**
* @param serverName the serverName to set
*/
public void setServerName(String serverName) {
this.serverName = serverName;
}
/**
* @return the portNumber
*/
public String getPortNumber() {
return portNumber;
}
/**
* @param portNumber2 the portNumber to set
*/
public void setPortNumber(String portNumber2) {
if(!portNumber2.equals("-1"))
this.portNumber = portNumber2;
}
/**
* @return the user
*/
public String getUser() {
return user;
}
/**
* @param user the user to set
*/
public void setUser(String user) {
this.user = user;
}
/**
* @return the password
*/
public String getPassword() {
return password;
}
/**
* @param password the password to set
*/
public void setPassword(String password) {
this.password = password;
}
/**
* @return the databaseName
*/
public String getDatabaseName() {
return databaseName;
}
/**
* @param databaseName the databaseName to set
*/
public void setDatabaseName(String databaseName) {
this.databaseName = databaseName;
}
/**
* @return the integratedSecurity
*/
public boolean getIntegratedSecurity() {
return integratedSecurity;
}
/**
* @param integratedSecurity the integratedSecurity to set
*/
public void setIntegratedSecurity(boolean integratedSecurity) {
this.integratedSecurity = integratedSecurity;
}
public void displayRow(String title, ResultSet rs, List<String> fields) {
if(rs == null) {
logger.info("0 row(s) affected");
}
else {
try {
System.out.println(title);
while (rs.next()) {
for(String f : fields)
System.out.println(f + ": " + rs.getString(f));
System.out.println("");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
DbHandler db = new DbHandler("10.226.63.204", "1433","sa", "Wsws1234", "tms", false);
try {
db.getConnection();
db.execQuery("select count(*) from [tms].[dbo].[Credentials]");
if(db.rs.next()){
System.out.println(db.rs.getInt(1));
}
}catch(Exception e){
e.printStackTrace();
}
finally {
db.destroy();
}
}
}
258
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
