序言
延續
上一篇，這篇針對MySQL資料庫的存取寫出的函式提供給大家參考，開發環境與資料庫連接器的設定可參考上一篇。
我在這邊有兩種建立連線物件的方式，預設是來自JNDI的連線設定，另一種是直接產生的方式，可在建立物件時做選擇。
使用範例的部分我改用JUnit來進行我自己的原始碼驗證，大家可直接參考呼叫的方式。
PS.2011/6/21 更新，加入Oracle的連線方法(但分頁查詢的函式只能用在MySQL)
原始碼函式類別
package common;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBBaseIO {
private static boolean _isDebug=false;
public enum DBType{
MySQL
,Oracle
}
public static void setDebug(boolean isDebug) {
_isDebug = isDebug;
}
public static boolean isIsdebug() {
return _isDebug;
}
private Connection con = null; // Database objects
public Connection getConnection() {
return con;
}
private Statement stat = null;
private ResultSet rs = null;
private PreparedStatement pst = null;
private boolean isClosed=false;
public boolean isClosedConn(){
return isClosed;
}
protected void finalize () {
if(!isClosed)CloseConn();
}
/**
* 建立資料庫存取物件(使用JNDI XML設定，名稱為MySqlDS)
* @throws SQLException
* @throws NamingException
*/
public DBBaseIO() throws SQLException, NamingException {
setupConnMySQLByXML();
}
/**
* 建立資料庫存取物件(使用JNDI XML設定)
* @param dsName 設定名稱
* @throws SQLException
* @throws NamingException
*/
public DBBaseIO(String dsName) throws SQLException, NamingException {
setupConnMySQLByXML(dsName);
}
/**
*
* @param sourceType
* @throws ClassNotFoundException
* @throws SQLException
* @throws NamingException
*/
public DBBaseIO(int sourceType) throws ClassNotFoundException, SQLException, NamingException{
switch(sourceType){
case 0:
setupConnMySQL("localhost:3306", "root", "", "mysql");
break;
case 1:
setupConnMySQLByXML();
break;
}
}
/**
* 建立資料庫存取物件
*
* @param str_SQLIP
* 資料庫IP
* @param str_SQLID
* 登入帳號
* @param str_SQLPSWD
* 登入密碼
* @param str_SQLDefaultDBName
* 預設資料庫
* @throws SQLException
* @throws ClassNotFoundException
*/
public DBBaseIO(String str_SQLIP, String str_SQLID, String str_SQLPSWD,
String str_SQLDefaultDBName) throws ClassNotFoundException, SQLException {
setupConnMySQL(str_SQLIP, str_SQLID, str_SQLPSWD, str_SQLDefaultDBName);
}
/**
* 建立資料庫存取物件
*
* @param dbtype
* 資料庫類型
* @param str_SQLIP
* 資料庫IP
* @param str_SQLID
* 登入帳號
* @param str_SQLPSWD
* 登入密碼
* @param str_SQLDefaultDBName
* 預設資料庫
* @throws SQLException
* @throws ClassNotFoundException
*/
public DBBaseIO(DBType dbtype,String str_SQLIP, String str_SQLID, String str_SQLPSWD,
String str_SQLDefaultDBName) throws ClassNotFoundException, SQLException {
if(dbtype==DBType.MySQL)
setupConnMySQL(str_SQLIP, str_SQLID, str_SQLPSWD, str_SQLDefaultDBName);
else if(dbtype==DBType.Oracle)
setupConnOracle(str_SQLIP, str_SQLID, str_SQLPSWD, str_SQLDefaultDBName);
else
throw new ClassNotFoundException("DBType un-define");
}
private void setupConnMySQLByXML() throws NamingException, SQLException{setupConnMySQLByXML("MySqlDS"); }
private void setupConnMySQLByXML(String name) throws NamingException, SQLException{
if(isIsdebug()){
System.out.println("DataSource:"+"java:/" + name);
}
try{
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:/" + name);
con = ds.getConnection();
} catch (NamingException e) {
System.err.println("Connection XML Load Error :" + e.toString());
throw e;
} catch (SQLException e) {
System.err.println("Get Connection Error :" + e.toString());
throw e;
}
}
private void setupConnMySQL(String str_SQLIP, String str_SQLID,
String str_SQLPSWD, String str_SQLDefaultDBName) throws ClassNotFoundException,SQLException {
if(isIsdebug()){
System.out.println("DataSource:"+"jdbc:mysql://" + str_SQLIP + "/"
+ str_SQLDefaultDBName
+ "?useUnicode=true&characterEncoding=UTF8");
System.out.println("ID:"+str_SQLID+"\tPassword:"+str_SQLPSWD);
System.out.println("JDBC Driver:" + "com.mysql.jdbc.Driver");
}
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://" + str_SQLIP + "/"
+ str_SQLDefaultDBName
+ "?useUnicode=true&characterEncoding=UTF8", str_SQLID,
str_SQLPSWD);
} catch (ClassNotFoundException e) {
System.err.println("DriverClassNotFound :" + e.toString());
throw e;
}
catch (SQLException x) {
System.err.println("Exception :" + x.toString());
throw x;
}
}
private void setupConnOracle(String str_SQLIP, String str_SQLID,
String str_SQLPSWD, String str_SQLDefaultDBName) throws ClassNotFoundException,SQLException {
if(isIsdebug()){
System.out.println("DataSource:"+"jdbc:oracle:thin:"+str_SQLID+"/"+str_SQLPSWD+"@//" + str_SQLIP + "/"
+ str_SQLDefaultDBName);
}
try {
Class.forName("oracle.jdbc.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:"+str_SQLID+"/"+str_SQLPSWD+"@//" + str_SQLIP + "/"
+ str_SQLDefaultDBName);
} catch (ClassNotFoundException e) {
System.err.println("DriverClassNotFound :" + e.toString());
throw e;
}
catch (SQLException x) {
System.err.println("Exception :" + x.toString());
throw x;
}
}
/**
* 執行insert update delete等更新指令用
*
* @param str_SQL
* 查詢語法
* @return 資料庫變動筆數
*/
public int executeUpdate(String str_SQL) throws SQLException,NullPointerException {
return executeUpdate(str_SQL, null);
}
/**
* 執行insert update delete等更新指令用
*
* @param str_SQL
* 查詢語法
* @param param
* 參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
* @return 資料庫變動筆數
*/
public int executeUpdate(String str_SQL, Object[] param) throws SQLException ,NullPointerException{
return executeUpdate(str_SQL, param, true);
}
/**
* 執行insert update delete等更新指令用
*
* @param str_SQL
* 查詢語法
* @param param
* 參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
* @param isCloseConn
* 是否關閉連線
* @return 資料庫變動筆數
*/
public int executeUpdate(String str_SQL, Object[] param, boolean isCloseConn) throws SQLException,NullPointerException {
if(isIsdebug()){
System.out.println("executeUpdate:"+str_SQL);
if(param!=null)
for(int i=0;i<param.length;i++)
System.out.println("Parameter "+ i +":"+param[i]);
System.out.println("isCloseConn :"+isCloseConn);
}
int result = 0;
if (con != null) {
try {
int i = 0;
if (param == null) {
stat = con.createStatement();
result = stat.executeUpdate(str_SQL);
} else {
pst = con.prepareStatement(str_SQL);
for (i = 0; i < param.length; i++) {
pst.setObject(i + 1, param[i]);
}
result = pst.executeUpdate();
try {
pst.clearParameters();
} catch (Exception e) {
CloseConn();
}
}
} catch (SQLException e) {
System.err.println("executeUpdate Exception :" + e.toString());
throw e;
} finally {
CloseConn(isCloseConn);
}
} else {
System.err.println("Connection is null");
throw new NullPointerException("Connection is null");
}
return result;
}
/**
* 批次執行SQL指令使用
*
* @param str_SQL
* 查詢語法的陣列
* @param isCloseConn
* 是否關閉連線
* @return 資料庫變動筆數的陣列
*/
public int[] executeBatch(String[] str_SQL, boolean isCloseConn) throws SQLException,NullPointerException {
if(isIsdebug()){
System.out.println("executeBatch:"+str_SQL);
}
int[] result=null;
if (con != null) {
try {
int i = 0;
stat = con.createStatement();
for (i = 0; i < str_SQL.length; i++) {
stat.addBatch(str_SQL[i]);
}
result=stat.executeBatch();
if(isIsdebug()){
System.out.println("executeBatch result:");
for (i = 0; i < result.length; i++) {
System.out.println(i+":"+result[i]);
}
}
} catch (SQLException e) {
System.err.println("executeBatch Exception :" + e.toString());
throw e;
} finally {
CloseConn(isCloseConn);
}
} else {
System.err.println("Connection is null");
throw new NullPointerException("Connection is null");
}
return result;
}
/**
* 批次執行SQL指令使用
* @param str_SQL 查詢語法
* @param param 以相同數量的參數形成的ArrayList
* @param isCloseConn 是否關閉連線
* @return 資料庫變動筆數的陣列
* @throws SQLException
* @throws NullPointerException
*/
public int[] addBatch(String str_SQL, ArrayList<Object[]> param, boolean isCloseConn) throws SQLException,NullPointerException {
int i = 0;
int j = 0;
if(isIsdebug()){
System.out.println("executeBatch:"+str_SQL);
if(param!=null)
for (i = 0; i < param.size(); i++) {
for (j = 0;j < param.get(i).length; j++) {
System.out.println("Parameter "+ i+","+j +":"+param.get(i)[j]);
}
}
}
int[] result=null;
if (con != null) {
try {
pst = con.prepareStatement(str_SQL);
for (i = 0; i < param.size(); i++) {
for (j = 0;j < param.get(i).length; j++) {
pst.setObject(j + 1, param.get(i)[j]);
}
pst.addBatch();
}
result=pst.executeBatch();
if(isIsdebug()){
System.out.println("executeBatch result:");
for (i = 0; i < result.length; i++) {
System.out.println(i+":"+result[i]);
}
}
} catch (SQLException e) {
System.err.println("executeBatch Exception :" + e.toString());
throw e;
} finally {
CloseConn(isCloseConn);
}
} else {
System.err.println("Connection is null");
throw new NullPointerException("Connection is null");
}
return result;
}
/**
* 查詢資料
*
* @param str_SQL
* 查詢語法
* @return 每列一筆資料，每筆資料的欄位存於 Object Array中
*/
public ArrayList<Object[]> executeQuery(String str_SQL) throws SQLException,NullPointerException {
return executeQuery(str_SQL, null);
}
/**
* 查詢資料
*
* @param str_SQL
* 查詢語法
* @param param
* 參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
* @return 每列一筆資料，每筆資料的欄位存於 Object Array中
*/
public ArrayList<Object[]> executeQuery(String str_SQL, Object[] param) throws SQLException,NullPointerException {
return executeQuery(str_SQL, param, true);
}
/**
* 查詢資料
*
* @param str_SQL
* 查詢語法
* @param param
* 參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
* @param isCloseConn
* 是否關閉連線
* @return 每列一筆資料，每筆資料的欄位存於 Object Array中
*/
public ArrayList<Object[]> executeQuery(String str_SQL, Object[] param,
boolean isCloseConn) throws SQLException,NullPointerException {
if(isIsdebug()){
System.out.println("executeQuery:"+str_SQL);
if(param!=null)
for(int i=0;i<param.length;i++)
System.out.println("Parameter "+ i +":"+param[i]);
System.out.println("isCloseConn :"+isCloseConn);
}
ArrayList<Object[]> result = new ArrayList<Object[]>();
if (con != null) {
try {
int i = 0;
if (param == null) {
stat = con.createStatement();
rs = stat.executeQuery(str_SQL);
} else {
pst = con.prepareStatement(str_SQL);
for (i = 0; i < param.length; i++) {
pst.setObject(i + 1, param[i]);
}
rs = pst.executeQuery();
pst.clearParameters();
}
ArrayList<Object> arr;
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
while (rs.next()) {
arr = new ArrayList<Object>();
for (i = 1; i < numberOfColumns + 1; i++) {
arr.add(rs.getObject(i));
}
result.add(arr.toArray());
}
} catch (SQLException e) {
System.err.println("executeQuery Exception :" + e.toString());
throw e;
} finally {
CloseConn(isCloseConn);
}
} else {
System.err.println("Connection is null");
throw new NullPointerException("Connection is null");
}
return result;
}
/**
* 查詢資料(請記得要關閉Result Set)
*
* @param str_SQL
* 查詢語法
* @return 回傳查詢得到的result set
*/
public ResultSet executeQueryResultset(String str_SQL) throws SQLException,NullPointerException {
return executeQueryResultset(str_SQL,null);
}
/**
* 查詢資料(請記得要關閉Connection)
*
* @param str_SQL
* 查詢語法
* @param param
* 參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
* @return 回傳查詢得到的result set
*/
public ResultSet executeQueryResultset(String str_SQL, Object[] param) throws SQLException,NullPointerException {
if(isIsdebug()){
System.out.println("executeQuery:"+str_SQL);
if(param!=null)
for(int i=0;i<param.length;i++)
System.out.println("Parameter "+ i +":"+param[i]);
}
ResultSet result = null;
if (con != null) {
try {
int i = 0;
if (param == null) {
stat = con.createStatement();
rs = stat.executeQuery(str_SQL);
} else {
pst = con.prepareStatement(str_SQL);
for (i = 0; i < param.length; i++) {
pst.setObject(i + 1, param[i]);
}
rs = pst.executeQuery();
pst.clearParameters();
}
result=rs;
} catch (SQLException e) {
System.err.println("executeQueryResultset Exception :" + e.toString());
throw e;
}
} else {
System.err.println("Connection is null");
throw new NullPointerException("Connection is null");
}
return result;
}
/**
* 查詢資料，並傳回查詢所傳回的結果集第一個資料列的第一個資料行
*
* @param str_SQL
* 查詢語法
* @return 每列一筆資料，每筆資料的欄位存於 Object Array中
*/
public Object executescalar(String str_SQL) throws SQLException,NullPointerException {
return executescalar(str_SQL, null);
}
/**
* 查詢資料，並傳回查詢所傳回的結果集第一個資料列的第一個資料行
*
* @param str_SQL
* 查詢語法
* @param param
* 參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
* @return 每列一筆資料，每筆資料的欄位存於 Object Array中
*/
public Object executescalar(String str_SQL, Object[] param) throws SQLException,NullPointerException {
return executescalar(str_SQL, param, true);
}
/**
* 查詢資料，並傳回查詢所傳回的結果集第一個資料列的第一個資料行
*
* @param str_SQL
* 查詢語法
* @param param
* 參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
* @param isCloseConn
* 是否關閉連線
* @return 每列一筆資料，每筆資料的欄位存於 Object Array中
*/
public Object executescalar(String str_SQL, Object[] param,
boolean isCloseConn) throws SQLException,NullPointerException {
if(isIsdebug()){
System.out.println("executeQuery:"+str_SQL);
if(param!=null)
for(int i=0;i<param.length;i++)
System.out.println("Parameter "+ i +":"+param[i]);
System.out.println("isCloseConn :"+isCloseConn);
}
Object result = null;
if (con != null) {
try {
int i = 0;
if (param == null) {
stat = con.createStatement();
rs = stat.executeQuery(str_SQL);
} else {
pst = con.prepareStatement(str_SQL);
for (i = 0; i < param.length; i++) {
pst.setObject(i + 1, param[i]);
}
rs = pst.executeQuery();
pst.clearParameters();
}
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
if (rs.next() && numberOfColumns>=1) {
result=rs.getObject(1);
}
} catch (SQLException e) {
System.err.println("executescalar Exception :" + e.toString());
throw e;
} finally {
CloseConn(isCloseConn);
}
} else {
System.err.println("Connection is null");
throw new NullPointerException("Connection is null");
}
return result;
}
/**
* 執行SQL
*
* @param str_SQL
* 查詢語法
* @return 是否成功
*/
public boolean execute(String str_SQL) throws SQLException,NullPointerException {
return execute(str_SQL, null);
}
/**
* 執行SQL
*
* @param str_SQL
* 查詢語法
* @param param
* 參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
* @return 是否成功
*/
public boolean execute(String str_SQL, Object[] param) throws SQLException,NullPointerException {
return execute(str_SQL, param, true);
}
/**
* 執行SQL
*
* @param str_SQL
* 查詢語法
* @param param
* 參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
* @param isCloseConn
* 是否關閉連線
* @return 是否成功
*/
public boolean execute(String str_SQL, Object[] param, boolean isCloseConn) throws SQLException,NullPointerException {
if(isIsdebug()){
System.out.println("executeQuery:"+str_SQL);
if(param!=null)
for(int i=0;i<param.length;i++)
System.out.println("Parameter "+ i +":"+param[i]);
System.out.println("isCloseConn :"+isCloseConn);
}
boolean result = false;
if (con != null) {
try {
int i = 0;
if (param == null) {
stat = con.createStatement();
result = stat.execute(str_SQL);
} else {
pst = con.prepareStatement(str_SQL);
for (i = 0; i < param.length; i++) {
pst.setObject(i + 1, param[i]);
}
result = pst.execute();
pst.clearParameters();
}
} catch (SQLException e) {
System.err.println("execute Exception :" + e.toString());
throw e;
} finally {
CloseConn(isCloseConn);
}
} else {
System.err.println("Connection is null");
throw new NullPointerException("Connection is null");
}
return result;
}
/**
* 關閉連線
*/
public void CloseConn() {
CloseConn(true);
}
/**
* 關閉連線
* @param isCloseConn 是否關閉Connection
*/
public void CloseConn(boolean isCloseConn) {
if(isIsdebug()){
System.out.println("CloseConn:"+isCloseConn);
}
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stat != null) {
stat.close();
stat = null;
}
if (pst != null) {
pst.close();
pst = null;
}
} catch (SQLException e) {
//System.err.println("Close Exception :" + e.toString());
} finally {
try {
if (con != null && isCloseConn) {
con.close();
isClosed=true;
}
} catch (SQLException e) {
//System.err.println("Close Exception :" + e.toString());
}
}
}
int allpage=0;
public int getAllpage() {
return allpage;
}
public void setAllpage(int allpage) {
this.allpage = allpage;
}
/**
* 查詢分頁資料
* @param colunms
* @param from
* @param where
* @param group
* @param order
* @param fpage
* @param pages
* @return
* @throws SQLException
* @throws NullPointerException
*/
public ArrayList<Object[]> selectPage(String colunms,String from,String where,String group,String having,String order,int fpage,int pages) throws SQLException,NullPointerException{
return selectPage(colunms,from,where,group,having,order,fpage,pages,null);
}
/**
* 查詢分頁資料
* @param colunms
* @param from
* @param where
* @param group
* @param order
* @param fpage
* @param pages
* @param param
* @return
* @throws SQLException
* @throws NullPointerException
*/
public ArrayList<Object[]> selectPage(String colunms,String from,String where,String group,String having,String order,int fpage,int pages, Object[] param) throws SQLException,NullPointerException{
return selectPage(colunms,from,where,group,having,order,fpage,pages,param,true);
}
/**
* 查詢分頁資料
* @param colunms
* @param from
* @param where
* @param group
* @param order
* @param fpage
* @param pages
* @param param
* @param isCloseConn
* @return
* @throws SQLException
* @throws NullPointerException
*/
public ArrayList<Object[]> selectPage(String colunms,String from,String where,String group,String having,String order,int fpage,int pages, Object[] param, boolean isCloseConn) throws SQLException,NullPointerException{
return selectPage(colunms, from, where, group, having, order, fpage, pages, true, 0, param, isCloseConn);
}
/**
* 查詢分頁資料
* @param colunms
* @param from
* @param where
* @param group
* @param having
* @param order
* @param fpage
* @param pages
* @param isCountPage
* @param allpageCount
* @param param
* @param isCloseConn
* @return
* @throws SQLException
* @throws NullPointerException
*/
public ArrayList<Object[]> selectPage(String colunms,String from,String where,String group,String having,String order,int fpage,int pages,boolean isCountPage,int allpageCount, Object[] param, boolean isCloseConn) throws SQLException,NullPointerException{
ArrayList<Object[]> result=null;
try{
String sql;
if(!isCountPage){
allpage=allpageCount;
}else{
int intRowCount;
sql="select count(*) from (select ";
if(colunms!=null){
sql+="" + colunms + " \n ";
}else{
sql+="* \n ";
}
sql+="from " + from.toString() + " \n ";
if(where!=null){
sql+="where " + where + " \n ";
}
if(group!=null){
sql+="group by " + group + " \n ";
}
if(having!=null){
sql+="having " + group + " \n ";
}
sql+=") selectcount \n ";
Object temp=executescalar(sql,param,false);
if(temp==null){
return result;
}
intRowCount=Integer.parseInt(temp.toString());
allpage=(intRowCount+fpage-1) / fpage;
}
int spage;
if(pages > allpage ){// pages == 0){
pages = 1;
}
spage=(pages-1)*fpage;
//sql="select " + colunms + " \n from " + from + " \n where " + where + " \n order by " + order + " \n limit "+ spage +","+fpage ;
sql="select ";
if(colunms!=null){
sql+= colunms + " \n ";
}else{
sql+="* \n ";
}
sql+="from " + from + " \n ";
if(where!=null){
sql+="where " + where + " \n ";
}
if(group!=null){
sql+="group by " + group + " \n ";
}
if(having!=null){
sql+="having " + group + " \n ";
}
if(order!=null){
sql+="order by " + order + " \n ";
}
sql+="limit "+ spage +","+fpage;
result=executeQuery(sql, param, false);
} catch (NullPointerException e) {
System.err.println("NullPointerException Exception :" + e.toString());
throw e;
} catch (SQLException e) {
System.err.println("SQLException Exception :" + e.toString());
throw e;
} finally {
CloseConn(isCloseConn);
}
return result;
}
}
使用範例程式
package common.test;
import static org.junit.Assert.*;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import common.DBBaseIO;
public class DBBaseIOTest {
DBBaseIO dbio;
int m_id;
String m_name;
@Before
public void setUp() throws Exception {
dbio=new DBBaseIO(0);
m_id=20100111;
m_name="tester" + m_id;
}
@After
public void tearDown() throws Exception {
dbio.CloseConn();
dbio=null;
}
@Test
public void testExecuteString() {
String msg="\n# testExecuteString\n Drop and Create :\t TestTable" + m_id;
System.out.println(msg);//DROP TABLE IF EXISTS TestTable" + m_id + ";
String str_Sql = "CREATE TABLE TestTable" + m_id + " ( " +
"`test_id` int(10) unsigned NOT NULL auto_increment, " +
"`member_id` int(10) unsigned NOT NULL, "+
"`member_name` varchar(45) NOT NULL, " +
"PRIMARY KEY (`test_id`) "+
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; ";
try{
dbio.execute(str_Sql);
}catch(Exception e){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
}
}
@Test
public void testExecuteUpdateString() {
String msg="\n# testExecuteUpdateString\n Insert :\tID=" + m_id + "\tName=" + m_name;
System.out.println(msg);
String str_Sql="Insert into TestTable" + m_id + " (member_id,member_name) values('" + m_id + "','" + m_name + "')";
try{
int result=dbio.executeUpdate(str_Sql);
if(result<=0){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
}
}catch(Exception e){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
}
}
@Test
public void testExecuteQueryString() {
String msg="\n# testExecuteQueryString\n Select ALL :";
System.out.println(msg);
String str_Sql="Select * from TestTable" + m_id + "";
try{
ArrayList<Object[]> result=dbio.executeQuery(str_Sql);
if(result.size()<=0){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
}else{
for(int i=0;i<result.size();i++){
for(int j=0;j<result.get(i).length;j++){
System.out.print(result.get(i)[j] + "\t");
}
System.out.println();
}
}
}catch(Exception e){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
}
}
@Test
public void testexecuteQueryResultset() {
String msg="\n# testexecuteQueryResultset\n Select ALL :";
System.out.println(msg);
String str_Sql="Select * from TestTable" + m_id + "";
try{
ResultSet rs=dbio.executeQueryResultset(str_Sql);
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
while (rs.next()) {
for (int i = 1; i < numberOfColumns + 1; i++) {
System.out.print(rs.getObject(i) + "\t");
}
System.out.println();
}
dbio.CloseConn();
}catch(Exception e){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
}
}
@Test
public void testselectPage() {
String msg="\n# testselectPage\n Insert 98 User";
System.out.println(msg);
String str_Sql="";
try{
for(int i=1;i<=98;i++){
str_Sql="Insert into TestTable" + m_id + " (member_id,member_name) values('" + m_id + i + "','" + m_name + "_" + i + "')";
int result=dbio.executeUpdate(str_Sql,null,false);
if(result<=0){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
}
}
str_Sql="";
ArrayList<Object[]> result=dbio.selectPage("member_id,member_name", "TestTable" + m_id, null, "member_id", 10, 10, null, true);
if(result.size()<=0){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
}else{
for(int i=0;i<result.size();i++){
for(int j=0;j<result.get(i).length;j++){
System.out.print(result.get(i)[j] + "\t");
}
System.out.println();
}
}
}catch(Exception e){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
}
}
@Test
public void testExecuteUpdateStringObjectArray() {
m_name+="_Updateed";
String msg="\n# testExecuteUpdateStringObjectArray\n Update :\tID=" + m_id + "\tName=" + m_name;
System.out.println(msg);
String str_Sql="Update TestTable" + m_id + " Set member_name=? WHERE member_id=?";
Object[] param={m_name,m_id};
try{
int result=dbio.executeUpdate(str_Sql,param);
if(result<=0){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
}
}catch(Exception e){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
}
}
@Test
public void testExecuteQueryStringObjectArray() {
String msg="\n# testExecuteQueryStringObjectArray\n Select :\tID=" + m_id;
System.out.println(msg);
String str_Sql="Select * from TestTable" + m_id + " where member_id=?";
Object[] param={m_id};
try{
ArrayList<Object[]> result=dbio.executeQuery(str_Sql,param);
if(result.size()<=0){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
}else{
for(int i=0;i<result.size();i++){
for(int j=0;j<result.get(i).length;j++){
System.out.print(result.get(i)[j] + "\t");
}
System.out.println();
}
}
}catch(Exception e){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
}
}
@Test
public void testExecuteUpdateStringObjectArrayBoolean() {
String msg="\n# testExecuteUpdateStringObjectArrayBoolean\n Delete :\tID=" + m_id + "\tName=" + m_name;
System.out.println(msg);
String str_Sql="Delete from TestTable" + m_id + " WHERE member_id=?";
Object[] param={m_id};
try{
int result=dbio.executeUpdate(str_Sql,param,false);
if(result<=0){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
}
}catch(Exception e){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
}finally{
dbio.CloseConn();
}
}
@Test
public void testExecuteQueryStringObjectArrayBoolean() {
String msg="\n# testExecuteQueryStringObjectArrayBoolean\n Select :\tID=" + m_id;
System.out.println(msg);
String str_Sql="Select * from TestTable" + m_id + " where member_id=?";
Object[] param={m_id};
try{
ArrayList<Object[]> result=dbio.executeQuery(str_Sql,param,false);
if(result.size()>0){
for(int i=0;i<result.size();i++){
for(int j=0;j<result.get(i).length;j++){
System.out.print(result.get(i)[j] + "\t");
}
System.out.println();
}
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
}
}catch(Exception e){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
}finally{
dbio.CloseConn();
}
}
@Test
public void testExecuteStringObjectArrayBoolean() {
String msg="\n# testExecuteStringObjectArrayBoolean\n Drop :\t TestTable_" + m_id;
System.out.println(msg);
String str_Sql="DROP TABLE IF EXISTS TestTable" + m_id + ";" ;
try{
dbio.execute(str_Sql,null,false);
}catch(Exception e){
fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
}finally{
dbio.CloseConn();
}
}
}
總結
我在開發時採用ArrayList把資料給回傳回來，因此相反的就沒有辦法回傳欄位名稱(不過我想你在寫SQL時應該已經可以指定自己要用的欄位名稱了吧)。
相關文章
