public class MicrosoftSQLServer {
public PreparedStatement ps=null;//用於發送SQL語句,增刪改
Connection ct=null;//連接
public CallableStatement cstmt = null;
/**
*
* @param sql sql命令或存儲過程名稱
* @param type 是否為存儲過程
*/
public MicrosoftSQLServer(String sql,boolean isProc)
{
try {
//加載驅動
//System.out.println("ok0");
//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
System.out.println("ok1");
//得到連接
ct = DriverManager.getConnection(
"jdbc:sqlserver://127.0.0.1:1433;DatabaseName=Test",
"sa", "數據庫密碼");
//System.out.println("連接數據庫成功");
if(isProc){
cstmt=ct.prepareCall(sql);
}else{
ps = ct.prepareStatement(sql);
}
} catch (Exception e) {
// TODO: handle exception
//System.out.println("數據庫連接不成功");
}finally{
//System.out.println("操作完成");
}
}
/**
* 執行增刪改的命令
* @return 受響應行數
*/
public int executeSql_updata()
{
int result=0;
try {
result = ps.executeUpdate();
return result;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return 0;
}finally{
try {
ps.close();
ct.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//System.out.println("連接已關閉");
}
}
/**
* 執行查詢
* @return ArrayList
*/
public ArrayList
{
ResultSet rs=null;
ArrayList
try {
rs = ps.executeQuery();
ResultSetMetaData data = rs.getMetaData();
while(rs.next()){
HashMap
for (int i = 1; i <= data.getColumnCount(); i++) {
// 獲得指定列的列名
String columnName = data.getColumnName(i);
// 獲得指定列的數據類型名
String columnTypeName = data.getColumnTypeName(i);
//System.out.println(columnName+"-----------"+columnTypeName);
hm.put(columnName, rs.getString(i));
}
list.add(hm);
}
//System.out.println("查詢結束,查詢到的list的長的為:========"+list.size());
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
rs.close();
ps.close();
ct.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//System.out.println("連接已關閉");
}
return list;
}
/**
* 執行一個增刪改的存儲過程命令
* @return 受響應行數
*/
public int callProcUpdate()
{
int result=-1;
try {
result = cstmt.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
cstmt.close();
ct.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return result;
}
/**
* 執行一個查詢的存儲過程
* @return ArrayList
*/
public ArrayList
{
ResultSet rs=null;
ArrayList
try {
rs = cstmt.executeQuery();
ResultSetMetaData data = rs.getMetaData();
while(rs.next()){
HashMap
for (int i = 1; i <= data.getColumnCount(); i++) {
// 獲得指定列的列名
String columnName = data.getColumnName(i);
// 獲得指定列的數據類型名
String columnTypeName = data.getColumnTypeName(i);
//System.out.println(columnName+"-----------"+columnTypeName);
hm.put(columnName, rs.getString(i));
}
list.add(hm);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
cstmt.close();
ct.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return list;
}
}
閱讀更多 騎著豬豬的CodeMonkey 的文章