/*
* 創建日期 2005-4-26
*
* TODO 要更改此生成的文件的模板，請轉至
* 窗口 － 首選項 － Java － 代碼樣式 － 代碼模板
*/
package com.xionglian.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author xl
*
*/
public class Database
{
/** method */
//****************************************************
/**
* getConnection()
* @return Connection
*/
public static Connection getConnection() throws Exception
{
String driver="com.microsoft.jdbc.sqlserver.SQLServerDriver";
String dbUrl="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=ATM";
String dbUser="sa";
String dbPassword="37360379";
Connection conn=null;
try
{
Class.forName(driver);
conn=DriverManager.getConnection(dbUrl,dbUser,dbPassword);
}
catch(Exception e)
{
e.printStackTrace();
}
return conn;
}
//**********************************************************
/**
* closeConnection()
*/
public static void closeConnection(Connection conn) throws Exception
{
if(conn!=null)
{
conn.close();
}
}
//************************************************************
/**
* executeUpdate()
* @return int
*/
public static int executeUpdate(String sql) throws Exception
{
int count=0;
Connection conn=null;
Statement stmt=null;
try
{
conn=getConnection();
stmt=conn.createStatement();
count=stmt.executeUpdate(sql);
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
closeConnection(conn);
}
return count;
}
//***************************************************************
/**
* executeQuery()
* @return List
*/
public static List executeQuery(String sql) throws Exception
{
List list=new ArrayList();
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try
{
conn=getConnection();
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
ResultSetMetaData rsmd=rs.getMetaData();
while(rs.next())
{
Map map=new HashMap();
for(int i=1;i<=rsmd.getColumnCount();i++)
{
map.put(rsmd.getColumnName(i),rs.getObject(i));
}
list.add(map);
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
if(rs!=null)
{
rs.close();
}
closeConnection(conn);
}
return list;
}
/**
* 從查詢結果List中取值,其中i為List中的第i條記錄
* @param list
* @param column_name
* @param i
* @return
*/
public static String getValues(List list,String column_name,int i)
{
try
{
return ((Map)list.get(i)).get(column_name).toString();
}
catch(Exception e)
{
e.printStackTrace();
}
return "";
}
/**
* 從查詢結果List中取值,此函數適合于查詢結果只有一條的情況,此為i值為0的特殊情況
* @param list
* @param column_name
* @return
*/
public static String getValues(List list,String column_name)
{
try
{
return ((Map)list.get(0)).get(column_name).toString();
}
catch(Exception e)
{
e.printStackTrace();
}
return "";
}
}
////1.對于insert, update, delete語句int count = DbWrapper.executeUpdate(sql);
////2.對于selete語句
//java.util.List list = DbWrapper.executeQuery(sql);
////方法一:按名字取值，注意大小寫是嚴格區分的
//for ( int i = 0; i < list.size(); i++ )
//{
// java.util.Map map = (java.util.Map)list.get(i);
// out.println(map.get("column_name").toString());
//}
////方法二：遍歷取值
//for ( int i = 0; i < list.size(); i++ )
//{
// java.util.Map map = (java.util.Map)list.get(i);
// for (java.util.Iterator it = map.keySet().iterator(); it.hasNext();)
// {
// String column_name = it.next().toString()); // 取值時注意null判斷
// out.println(column_name + " = " + map.get(column_name) == null ? "" : map.get(column_name).toString());
// }
//}