序言
延續上一篇，這篇針對MySQL資料庫的存取寫出的函式提供給大家參考，開發環境與資料庫連接器的設定可參考上一篇。我在這邊有兩種建立連線物件的方式，預設是來自JNDI的連線設定，另一種是直接產生的方式，可在建立物件時做選擇。
使用範例的部分我改用JUnit來進行我自己的原始碼驗證，大家可直接參考呼叫的方式。
原始碼函式類別
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 Connection con = null; // Database objects
private Statement stat = null;
private ResultSet rs = null;
private PreparedStatement pst = null;
public DBBaseIO() throws SQLException, NamingException {
setupConnMySQLByXML();
}
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);
}
private void setupConnMySQLByXML() throws NamingException, SQLException{
try{
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:/MySqlDS");
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;
}
}
/**
*
* @param str_SQLIP
* @param str_SQLID
* @param str_SQLPSWD
* @param str_SQLDefaultDBName
* @throws ClassNotFoundException
* @throws SQLException
*/
private void setupConnMySQL(String str_SQLIP, String str_SQLID,
String str_SQLPSWD, String str_SQLDefaultDBName) throws ClassNotFoundException,SQLException {
try {
Class.forName("com.mysql.jdbc.Driver");
// 註冊driver
con = DriverManager.getConnection("jdbc:mysql://" + str_SQLIP + "/"
+ str_SQLDefaultDBName
+ "?useUnicode=true&characterEncoding=UTF8", str_SQLID,
str_SQLPSWD);
// 取得connection
} catch (ClassNotFoundException e) {
System.err.println("DriverClassNotFound :" + e.toString());
throw e;
}// 有可能會產生sql exception
catch (SQLException x) {
System.err.println("Exception :" + x.toString());
throw x;
}
}
public int executeUpdate(String str_SQL) throws SQLException,NullPointerException {
return executeUpdate(str_SQL, null);
}
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 {
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();
pst.clearParameters();
}
} catch (SQLException e) {
System.err.println("CreateDB Exception :" + e.toString());
throw e;
} finally {
CloseConn(isCloseConn);
}
} else {
System.err.println("Connection is null");
throw new NullPointerException("Connection is null");
}
return result;
}
// 查詢資料
public ArrayList<Object[]> executeQuery(String str_SQL) throws SQLException,NullPointerException {
return executeQuery(str_SQL, null);
}
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 {
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("DropDB Exception :" + e.toString());
throw e;
} finally {
CloseConn(isCloseConn);
}
} else {
System.err.println("Connection is null");
throw new NullPointerException("Connection is null");
}
return result;
}
// 查詢資料
public Object executescalar(String str_SQL) throws SQLException,NullPointerException {
return executescalar(str_SQL, null);
}
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 {
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("DropDB Exception :" + e.toString());
throw e;
} finally {
CloseConn(isCloseConn);
}
} else {
System.err.println("Connection is null");
throw new NullPointerException("Connection is null");
}
return result;
}
// 查詢資料
public boolean execute(String str_SQL) throws SQLException,NullPointerException {
return execute(str_SQL, null);
}
public boolean execute(String str_SQL, Object[] param) throws SQLException,NullPointerException {
return execute(str_SQL, param, true);
}
/**
* 查詢資料
*
* @param str_SQL
* 查詢語法
* @param param
* 參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
* @param isCloseConn
* 是否關閉連線
* @return 每列一筆資料，每筆資料的欄位存於 Object Array中
*/
public boolean execute(String str_SQL, Object[] param, boolean isCloseConn) throws SQLException,NullPointerException {
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("DropDB Exception :" + e.toString());
throw e;
} finally {
CloseConn(isCloseConn);
}
} else {
System.err.println("Connection is null");
throw new NullPointerException("Connection is null");
}
return result;
}
// 完整使用完資料庫後,記得要關閉所有Object
// 否則在等待Timeout時,可能會有Connection poor的狀況
public void CloseConn() {
CloseConn(true);
}
/**
* 關閉連線
*
* @param isCloseConn
*/
public void CloseConn(boolean 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();
}
} catch (SQLException e) {
System.err.println("Close Exception :" + e.toString());
}
}
}
}
使用範例程式
package common.test;
import static org.junit.Assert.*;
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=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 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時應該已經可以指定自己要用的欄位名稱了吧)。
