连接数据库及相关操作工具类

package tool;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Properties;

public class SQLHelper {

private static Properties props = new Properties();

public static String SQLType = "";

static String driver = "";

static String url = "";

static String username = "";

static String password = "";

static{

props = new Properties();

InputStream in = SQLHelper.class.getClassLoader()

.getResourceAsStream("db.properties");

try {

props.load(in);

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

String dbtype = props.getProperty("dbtype").toString();

if (dbtype.equals("oracle")) {

driver = props.getProperty("odriver");

url = props.getProperty("ourl");

username = props.getProperty("ousername");

password = props.getProperty("opassword");

} else {

driver = props.getProperty("driver");

url = props.getProperty("url");

username = props.getProperty("username");

password = props.getProperty("password");

}

try {

DataSourceUtil.init(driver,url, username, password);

} catch (Exception e) {

// TODO Auto-generated catch block

System.out.println("初始化连接出错");

e.printStackTrace();

}

}

public static String GetSQLType() {

if (SQLType.equals("")) {

InputStream in = SQLHelper.class.getClassLoader()

.getResourceAsStream("db.properties");

try {

props.load(in);

} catch (IOException e) {

e.printStackTrace();

}

SQLType = props.getProperty("dbtype").toString();

}

return SQLType;

}

public static String Getxzqbm() {

Properties prop = new Properties();

InputStream is = SQLHelper.class.getClassLoader().getResourceAsStream(

"db.properties");

try {

prop.load(is);

} catch (IOException e) {

e.printStackTrace();

}

if (is != null) {

try {

is.close();

} catch (IOException e) {

e.printStackTrace();

}

}

return props.getProperty("xzqbm");

}

/**

 * 获取连接

 * @return

 */

public static Connection getConnect() {

/*try {

Class.forName(driver);

return DriverManager.getConnection(url, username, password);

} catch (Exception e) {

e.printStackTrace();

}

return null;*/

try {

return DataSourceUtil.getConnection();

} catch (SQLException e) {

// TODO Auto-generated catch block

System.out.println("获取连接失败！");

e.printStackTrace();

}

return null;

}

public static int ExecSql1(String cmdText) throws SQLException {

Connection conn = getConnect();

PreparedStatement ps = conn.prepareStatement(cmdText);

if (conn == null)

return -2;

int i;

try {

i = ps.executeUpdate();

} catch (SQLException ex) {

i = -1;

} finally {

ps.close();

conn.close();

}

return i;

}

public static int ExecSql(String cmdText) throws SQLException {

Connection conn = getConnect();

PreparedStatement ps = conn.prepareStatement(cmdText);

if (conn == null)

return -2;

int i;

try {

i = ps.executeUpdate(cmdText);

} catch (SQLException ex) {

i = -1;

} finally {

ps.close();

conn.close();

}

return i;

}

public static Object ExecuteScalar(String cmdText) throws SQLException {

Connection conn = getConnect();

PreparedStatement ps = conn.prepareStatement(cmdText);

Statement stmt = conn.createStatement();

if (conn == null) {

return Integer.valueOf(-2);

}

Object obj = null;

try {

ResultSet rs = stmt.executeQuery(cmdText);

if (rs.next()) {

obj = rs.getInt(1);

}

} catch (SQLException ex) {

return null;

} finally {

ps.close();

conn.close();

}

return obj;

}

// public static String getDateFormat(String dateStr) {

// if (dateStr.equals(" ")) {

// return dateStr;

// }

//

// dateStr = dateStr.split(" ")[0];

// return dateStr;

// }

public static String getDateFormat(String dateStr) {

SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd日");

    if(dateStr.equals("")||dateStr.equals(" ")){

   return dateStr;

    }else{

           //Date date = sdf.parse(dateStr);

   //dateStr = dateStr.split(" ")[0];

     try {

     dateStr=format.format(DateFormat.getDateInstance().parse(dateStr));

} catch (Exception e) {

e.printStackTrace();

}

    

           return dateStr;

    }

}

/**

 * 关闭

 * @param object

 */

public static void closeDB(Object... object) {

if (object == null || object.length == 0)

return;

try {

for (Object obj : object) {

if (obj != null && obj instanceof ResultSet) {

((ResultSet) obj).close();

}

}

for (Object obj : object) {

if (obj != null && obj instanceof Statement) {

if (obj != null && obj instanceof PreparedStatement) {

((PreparedStatement) obj).close();

} else {

((Statement) obj).close();

}

}

}

for (Object obj : object) {

if (obj != null && obj instanceof Connection

&& !((Connection) obj).isClosed()) {

((Connection) obj).close();

}

}

} catch (Exception e) {

e.printStackTrace();

}

}

//关闭数据源

public static void close(Object...obj){

if(null!=obj){

for(Object o:obj){

try {

if (o instanceof ResultSet) {

((ResultSet) o).close();

} else if (o instanceof Statement) {

((Statement) o).close();

} else if (o instanceof Connection) {

((Connection) o).close();

}else if (o instanceof PreparedStatement) {

((PreparedStatement) o).close();

}

} catch (Exception ex) {

ex.printStackTrace();

}

o=null;

}

}

}

public static List<Map<String, Object>> getResultToList(String sql, Object... params) {

PreparedStatement ps = null;

Connection conn = getConnect();

ResultSet rs = null;

List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();

if (null == sql || "".equals(sql)) {

return new ArrayList<Map<String, Object>>();

}

try {

ps = conn.prepareStatement(sql);

if (null != params && params.length > 0) {

for (int i = 0, len = params.length; i < len; i++) {

Object arg = params[i];

ps.setObject(i + 1, arg);

}

}

rs = ps.executeQuery();

if (null != rs) {

ResultSetMetaData rsm = rs.getMetaData();

int count = rsm.getColumnCount();

Map<String, Object> record = null;

if (count > 0) {

while (rs.next()) {

record = new HashMap<String, Object>();

for (int j = 0; j < count; j++) {

Object obj = rs.getObject(j + 1);

String columnName = rsm.getColumnName(j + 1);

record.put(columnName.toLowerCase(),(obj == null) ? "" : obj);

}

rows.add(record);

}

}

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

close(rs, ps, conn);

}

return rows;

}

public static List<Map<String, Object>> getResultToList(Connection conn,String sql, Object... params) {

PreparedStatement ps = null;

ResultSet rs = null;

List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();

if (null == sql || "".equals(sql)) {

return new ArrayList<Map<String, Object>>();

}

try {

ps = conn.prepareStatement(sql);

if (null != params && params.length > 0) {

for (int i = 0, len = params.length; i < len; i++) {

Object arg = params[i];

ps.setObject(i + 1, arg);

}

}

rs = ps.executeQuery();

if (null != rs) {

ResultSetMetaData rsm = rs.getMetaData();

int count = rsm.getColumnCount();

Map<String, Object> record = null;

if (count > 0) {

while (rs.next()) {

record = new HashMap<String, Object>();

for (int j = 0; j < count; j++) {

Object obj = rs.getObject(j + 1);

String columnName = rsm.getColumnName(j + 1);

record.put(columnName.toLowerCase(),(obj == null) ? "" : obj);

}

rows.add(record);

}

}

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

close(rs, ps);

}

return rows;

}

public static boolean ExectueSql(Connection conn,String sql,Object...b){

boolean flag=false;

int rows=0;

PreparedStatement pre=null;

try {

pre=conn.prepareStatement(sql);

for(int i=0,len=b.length;i<len;i++){

pre.setObject(i+1, b[i]);

}

rows=pre.executeUpdate();

if(rows>0){

flag=true;

}else{

flag=false;

}

conn.commit();

} catch (SQLException e) {

flag=false;

System.out.println(e.toString());

}finally{

close(pre);

}

System.out.println(flag);

return flag;

}

public static boolean ExectueSql(String sql,Object...b){

boolean flag=false;

int rows=0;

PreparedStatement pre=null;

Connection conn=getConnect();

try {

pre=conn.prepareStatement(sql);

for(int i=0,len=b.length;i<len;i++){

pre.setObject(i+1, b[i]);

}

rows=pre.executeUpdate();

if(rows>0){

flag=true;

}else{

flag=false;

}

conn.commit();

} catch (SQLException e) {

flag=false;

System.out.println(e.toString());

}finally{

close(pre,conn);

}

System.out.println(flag);

return flag;

}

//批量执行语句

public static boolean ExectueSql(String []sql,Object...b){

boolean flag=false;

int rows[] = null;

Statement st=null;

Connection conn=getConnect();

try {

conn.setAutoCommit(false); 

st=conn.createStatement();

st.addBatch(sql[0]);

st.addBatch(sql[1]);

rows = st.executeBatch();

if(rows[0]>0){

flag=true;

}else{

flag=false;

}

conn.commit();

} catch (SQLException e) {

flag=false;

System.out.println(e.toString());

}finally{

close(st,conn);

}

System.out.println(flag);

return flag;

}

public static boolean ExectueSql(String sql,List<List<Object>> b){

boolean flag=false;

int[] rows=new int[b.size()];

PreparedStatement pre=null;

Connection conn=getConnect();

try {

conn.setAutoCommit(false); 

pre=conn.prepareStatement(sql);

for(int i=0,len=b.size();i<len;i++){

List<Object> obj=b.get(i);

for(int j=0,objlen=obj.size();j<objlen;j++){

pre.setObject(j+1, obj.get(j));

}

pre.addBatch();

}

rows=pre.executeBatch();

conn.commit();

for(int i=0,len=rows.length;i<len;i++){

if(rows[i]>0||rows[i]==-2){

flag=true;

}else{

flag=false;

}

System.out.println(i+"条数据："+flag);

}

flag=true;

} catch (SQLException e) {

flag=false;

e.getStackTrace();

System.out.println(e.toString());

}finally{

close(pre,conn);

}

System.out.println(flag);

return flag;

}

//sql语句批量提交

public static boolean ExectueSqlPL(List<String> sqllist){

boolean flag=false;

int[] rows=new int[sqllist.size()];

Connection conn=getConnect();

Statement stmt = null;

try {

stmt = conn.createStatement();

conn.setAutoCommit(false); 

if(sqllist.size()>0){

for(int i=0,len=sqllist.size();i<len;i++){

  stmt.addBatch(sqllist.get(i));

}

}

rows = stmt.executeBatch();

   // 事务提交

 conn.commit();

   // 设置为自动提交,改为TRUE

 conn.setAutoCommit(true);

for(int i=0,len=rows.length;i<len;i++){

if(rows[i]>0){

flag=true;

}else{

flag=false;

}

System.out.println(i+"条数据："+flag);

}

flag=true;

} catch (SQLException e) {

flag=false;

  if (conn != null) {

     try {

conn.rollback();

conn.setAutoCommit(true);

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

     

    }

System.out.println(e.toString());

}finally{

close(stmt,conn);

}

System.out.println(flag);

return flag;

}

/**

 * 获取总条数

 * @param sql

 * @param args

 * @return

 */

public static int getCount(Connection conn,String sql,Object...args){

PreparedStatement pre = null;

ResultSet rs = null;

int total = 0;

String sql2 = "select count(*) from (" + sql + ")   t";

try {

pre = conn.prepareStatement(sql2);

for(int i=0,len=args.length;i<len;i++){

pre.setObject(i+1, args[i]);

}

// pre.setString(1, slid);

rs = pre.executeQuery();

if (rs.next()) {

total = rs.getInt(1);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

close(pre,rs);

}

return total;

}

/**

 * 获取总条数

 * @param sql

 * @param args

 * @return

 */

public static int getCount(String sql,Object...args){

Connection conn = getConnect();

PreparedStatement pre = null;

ResultSet rs = null;

int total = 0;

String sql2 = "select count(*) from (" + sql + ")   t";

try {

pre = conn.prepareStatement(sql2);

for(int i=0,len=args.length;i<len;i++){

pre.setObject(i+1, args[i]);

}

// pre.setString(1, slid);

rs = pre.executeQuery();

if (rs.next()) {

total = rs.getInt(1);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

close(rs, pre, conn);

}

return total;

}

}