package com.richway.datautil;import java.sql.*;
import java.util.*;public class SqlDataUtil {
private String strConn;
private String strUsername;
private String strPassword;
/**
* @param connStr String
* @param username String
* @param password String
*/
public SqlDataUtil(String connStr, String username, String password) {
this.strConn = connStr;
this.strUsername = username;
this.strPassword = password;
} /**
* 得到连接
* @return 一个连接java.sql.Connection
* @throws ClassNotFoundException
* @throws SQLException
*/
public Connection getConn() {
Connection conn;
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
}
catch (ClassNotFoundException ce) {
return null;
}
try {
conn = DriverManager.getConnection(this.strConn, this.strUsername,
this.strPassword);
}
catch (SQLException e) {
return null;
}
return conn;
} /**
* 执行 select sql 查询数据库单个表，得到ArrayList 行对象object是HashMap
* @param strSql sql查询语句
* @return List
* @throws SQLException
*/
public List getTable(String strSql) {
Connection dbConn = getConn();
Statement stmt = null;
ResultSet rs = null;
List retList = new ArrayList();
Map myMap = null;
try {
stmt = dbConn.createStatement();
rs = stmt.executeQuery(strSql);
if (rs == null) {
return Collections.EMPTY_LIST;
}
ResultSetMetaData mtdata = rs.getMetaData();
int col = mtdata.getColumnCount();
while (rs.next()) {
myMap = new HashMap(col);
for (int i = 1; i <= col; i++) {
myMap.put(mtdata.getColumnName(i), rs.getString(i));
}
retList.add(myMap);
}
return retList;
}
catch (SQLException e) {
return Collections.EMPTY_LIST;
}
finally {
dbclose(dbConn, stmt, rs);
}
} /**
* 和C#DataSet类似 HashMap（表名，表内容） ArrayList（行，行内容） HashMap（列名，列值）
* @param strSql String[] sql 语句数组
* @param tablenames String[] 表名数组 两参数维数相同
* @return HashMap
*/
public Map getTables(String[] strSql, String[] tablenames) {
int tableCount = tablenames.length; //查询表的个数
Map retMap = new HashMap(tableCount); //返回的hashmap
Connection dbConn = getConn();
Statement stmt = null;
ResultSet rs = null;
List TableList = null;
Map RowsMap = null;
try {
stmt = dbConn.createStatement();
for (int i = 0; i < tableCount; i++) {
TableList = new ArrayList();
rs=null;
rs = stmt.executeQuery(strSql[i]);
if (rs == null) {
return Collections.EMPTY_MAP;
}
ResultSetMetaData mtdata = rs.getMetaData();
int col = mtdata.getColumnCount();
while (rs.next()) {
RowsMap = new HashMap(col);
for (int j = 1; j <= col; j++) {
RowsMap.put(mtdata.getColumnName(j), rs.getString(j));
}
TableList.add(RowsMap);
}
retMap.put(tablenames[i], TableList);
}
return retMap;
}
catch (SQLException e) {
return Collections.EMPTY_MAP;
}
finally {
dbclose(dbConn, stmt, rs);
} } /**
* 执行insert update delete 单条sql语句
* @param strSql
* @throws SQLException
* @return int 数量
* @see #dbclose
*/
public int ExeQueryUpdate(String strSql) {
int retVal;
Connection dbConn = getConn();
Statement stmt = null;
try {
stmt = dbConn.createStatement();
retVal = stmt.executeUpdate(strSql);
}
catch (SQLException e) {
return -1;
}
finally {
dbclose(dbConn, stmt);
}
return retVal;
} /**
* 执行insert update delete 多条sql语句 事务处理（多表）
* @param String[]
* @throws SQLException
* @return boolean
*/
public boolean ExeQueryUpdate(String[] strSql) {
Connection dbConn = getConn();
Statement stmt = null;
try {
stmt = dbConn.createStatement();
dbConn.setAutoCommit(false);
for (int i = 0; i < strSql.length; i++) {
stmt.executeUpdate(strSql[i]);
}
dbConn.commit();
dbConn.setAutoCommit(true);
return true;
}
catch (SQLException e) {
return false;
}
finally {
dbclose(dbConn, stmt);
}
} /**
* 执行多条 insert update delete sql语句（单表操作），事务处理 用PreparedStatement
* @param strSql INSERT INTO testtable (name, age)VALUES (?,?)
* @param param String a[][] = { {"a","11"} , { "a1","2"} };
* @return boolean
*/
public boolean ExeQueryUpdate(String strSql, String[][] param) {
int rowscount = param.length;
int colscount = param[0].length;
boolean retVal;
Connection dbConn = getConn();
PreparedStatement pstmt = null;
try {
pstmt = dbConn.prepareStatement(strSql);
dbConn.setAutoCommit(false);
for (int i = 0; i < rowscount; i++) {
for (int j = 0; j < colscount; j++) {
pstmt.setString(j + 1, param[i][j]);
}
pstmt.executeUpdate();
}
dbConn.commit();
dbConn.setAutoCommit(true);
return true;
}
catch (SQLException e) {
return false;
}
finally {
dbclose(dbConn, pstmt);
}
} /**
* 释放数据库资源 Connection Statement ResultSet
* @param dbConn Connection
* @param stmt Statement
* @param rs ResultSet
*/
public void dbclose(Connection dbConn, Statement stmt, ResultSet rs) {
try {
if (stmt != null) {
stmt.close();
}
if (rs != null) {
rs.close();
}
if (dbConn != null) {
dbConn.close();
}
}
catch (SQLException sqle) {}
} /**
* 释放数据库资源 Connection PreparedStatement
* @param dbConn Connection
* @param stmt Statement
*/
public void dbclose(Connection dbConn, PreparedStatement pstmt) {
try {
if (pstmt != null) {
pstmt.close();
}
if (dbConn != null) {
dbConn.close();
}
}
catch (SQLException sqle) {}
} /**
* 释放数据库资源 Connection Statement
* @param dbConn Connection
* @param stmt Statement
*/
public void dbclose(Connection dbConn, Statement stmt) {
try {
if (stmt != null) {
stmt.close();
}
if (dbConn != null) {
dbConn.close();
}
}
catch (SQLException sqle) {}
}
}
public static void main(String[] args) {
String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=组件库";
String user = "sa";
String password = "richway";
//建立对象
SqlDataUtil sqlDataUtil1 = new SqlDataUtil(url, user, password);
String a[][] = {
{
"a", "11"}
, {
"a1", "2"}
};
//单表多条语句
boolean b = sqlDataUtil1.ExeQueryUpdate(
"INSERT INTO testtable (name, age)VALUES (?,?)", a); if (b) {
System.out.println("ExeQueryUpdate+插入记录 testtable");
}
//单条SQL语句
System.out.println("插入FieldToField记录数" + sqlDataUtil1.ExeQueryUpdate(
"INSERT INTO FieldToField(tablename, fieldname, showmode, LEVEL)VALUES ('aaa', 'bbb', '3', '2')"));
//查询单表
System.out.println(" 单表查询 ");
List list = sqlDataUtil1.getTable("select * from testtable");
for (int j = 0; j < list.size(); j++) {
Map m;
m = (Map) list.get(j);
System.out.print(j + " ");
System.out.println( (String) m.get("age"));
}
//多表查询
String dbsql[]={"select * from testtable","select * from FieldToField"};
String tablenames[]={"testtable","FieldToField"};
Map dbMap;
dbMap=sqlDataUtil1.getTables(dbsql,tablenames);
List TableList1 = (ArrayList)dbMap.get("testtable");
System.out.println(" 多表查询 ");
for (int j = 0; j < list.size(); j++) {
Map m;
m = (Map) TableList1.get(j);
System.out.print(j + " ");
System.out.println( (String) m.get("name"));
}
List TableList2 = (ArrayList)dbMap.get("FieldToField");
System.out.println("-----------------");
for (int j = 0; j < list.size(); j++) {
Map m;
m = (Map) TableList2.get(j);
System.out.print(j + " ");
System.out.println( (String) m.get("tablename"));
}
//执行多表sql语句
String strSql[] = {
"delete from testtable",
"delete from FieldToField where tablename='aaa'"};
System.out.println("ExeQueryUpdate 删除记录数" +
sqlDataUtil1.ExeQueryUpdate(strSql)); }
}