import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Hashtable;
import javax.naming.*;
import javax.sql.*;
import org.apache.tomcat.dbcp.dbcp.BasicDataSource;
/**
* <strong>mysql數據庫操作幫助類</strong>
* <p>
* 包裝了mysql常用的操作方法。類似于SqlHelper在java中的實現。
* </p>
*
* @author birdshover
*/
public abstract class mysqlhelper {
private static Boolean locker = false;
/**
* 測試使用方法，也可以直接使用，改方法應該只被調用一次。 用來注冊數據源。
*/
public synchronized static void forTest() {
if(locker)
return;
else
locker = true;
BasicDataSource bdds = new BasicDataSource();
bdds.setDriverClassName("org.gjt.mm.mysql.Driver");
bdds.setUrl("jdbc:mysql://localhost:3306/doclight?useUnicode=true&characterEncoding=UTF-8");
bdds.setUsername("root");
bdds.setPassword("123");
bdds.setInitialSize(100);
Hashtable env = new Hashtable();
env.put("java.naming.factory.initial",
"org.apache.naming.java.javaURLContextFactory");
try {
InitialContext ict = new InitialContext(env);
ict.bind("MySQL", bdds);
} catch (Exception ex) {
locker = false;
System.out.println(ex.getMessage());
}
}
/**
* 返回數據庫連接對象，連接失敗則返回null
*
* @return Connection
*/
private static Connection getConnection() {
try {
InitialContext context = new InitialContext();
DataSource ds = (DataSource) context.lookup("MySQL");
return ds.getConnection();
} catch (NamingException ex) {
return null;
} catch (SQLException ex) {
return null;
} catch (Exception ex) {
return null;
}
}
/**
* 無結果查詢，適用于更新和插入
*
* @param SQL語句
* @param 語句帶的參數
* @return 操作影響行數
* @throws SQLException
*
* @example Object[] parms = new Object[2];<br/> parms[0] = "標題"; <br/>
* parms[1] = "內容";<br/> int val = mysqlhelper.ExecuteNoneQuery(
* "insert into Documents(Title,Content) values (?,?)", parms);
*/
public static int ExecuteNoneQuery(String cmdtext, Object[] parms)
throws SQLException {
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(cmdtext);
prepareCommand(pstmt, parms);
return pstmt.executeUpdate();
} catch (Exception ex) {
System.out.println(ex.getMessage());
} finally {
if (pstmt != null){
pstmt.clearParameters();
pstmt.close();
}
if (conn != null)
conn.close();
}
return 0;
}
/**
* 返回查詢結果集
*
* @param SQL語句
* @param 附帶參數
* @return 返回結果，用ArrayList包裝Object數組
* @throws SQLException
*
* @example ArrayList list =
* mysqlhelper.ExecuteReader("Select * from Documents",null); <br/>
* for(int i = 0;i<list.size();i++) { <br/> Object[]
* obs = (Object[])list.get(i); <br/> for(int j =
* 0;j<obs.length;j++) { <br/>
* out.print(obs[j].toString()); <br/>
* } <br/> out.print("<br/>"); <br/>
* } <br/>
*
*/
public static ArrayList ExecuteReader(String cmdtext, Object[] parms)
throws SQLException {
PreparedStatement pstmt = null;
Connection conn = null;
ResultSet rs = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(cmdtext);
prepareCommand(pstmt, parms);
rs = pstmt.executeQuery();
ArrayList al = new ArrayList();
ResultSetMetaData rsmd = rs.getMetaData();
int column = rsmd.getColumnCount();
while (rs.next()) {
Object[] ob = new Object[column];
for (int i = 1; i <= column; i++) {
ob[i - 1] = rs.getObject(i);
}
al.add(ob);
}
return al;
} catch (Exception ex) {
}
finally{
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
}
return null;
}
/**
* 返回第1行第1列數據，一般用來查詢count值
*
* @param SQL語句
* @param 帶參數
* @return 值
* @throws SQLException
*/
public static Object ExecuteScalar(String cmdtext, Object[] parms)
throws SQLException {
PreparedStatement pstmt = null;
Connection conn = null;
ResultSet rs = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(cmdtext);
prepareCommand(pstmt, parms);
rs = pstmt.executeQuery();
if (rs.next()) {
return rs.getObject(1);
} else {
return null;
}
} catch (Exception e) {
System.out.println(e.getMessage());
} finally {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
}
return null;
}
private static void prepareCommand(PreparedStatement pstmt, Object[] parms)
throws SQLException, UnsupportedEncodingException {
if (parms != null && parms.length > 0) {
for (int i = 1; i < parms.length + 1; i++) {
Object item = parms[i - 1];
String typeName = item.getClass().getSimpleName();
if (typeName.equals("String")) {
pstmt.setString(i, item.toString());
} else if (typeName.equals("Integer")) {
pstmt.setInt(i, Integer.parseInt(item.toString()));
} else if (typeName.equals("Date")) {
pstmt.setDate(i, Date.valueOf(item.toString()));
} else {
pstmt.setObject(i, item);
}
}
}
}
}