為了簡化一些操作，同時為了能兼容其他數據庫，因此並沒用使用SqlHelper 的形式。
這只是個簡單模型的開發方式，沒用納入數據連接池等內容。
我看了網上大部分的SqlHelper在類型轉換方面都很有問題，而且返回結果使 用ArrayList包裝了一下。在這裡的包裝主要是為了避免這兩個問題。
首先申明數據庫接口，這代表了一個數據庫能進行的操作。
package dao;
import java.sql.SQLException;
public interface Database {
int ExecuteNoneQuery(String cmdtext, Parameters parms) throws SQLException;
<T> T ExecuteObject(Data2Object<T> convertor, String cmdtext,
Parameters parms) throws SQLException;
Object ExecuteScalar(String cmdtext, Parameters parms) throws SQLException;
Parameters CreateFixedParms(int size);
}
實現該接口的MySql包裝形式，其實和SqlHelper差不多：
package dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class MySqlDatabase implements Database {
private Connection conn;
public MySqlDatabase(String connString) throws SQLException {
conn = DriverManager.getConnection (connString);
}
public int ExecuteNoneQuery(String cmdtext, Parameters parms)
throws SQLException {
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement (cmdtext);
prepareCommand(pstmt, parms);
return pstmt.executeUpdate();
} catch (Exception ex) {
} finally {
if (pstmt != null) {
pstmt.clearParameters();
pstmt.close();
}
if (conn != null)
conn.close();
}
return -1;
}
public <T> T ExecuteObject(Data2Object<T> convertor, String cmdtext,
Parameters parms) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = conn.prepareStatement (cmdtext);
prepareCommand(pstmt, parms);
rs = pstmt.executeQuery();
return convertor.DataMap(rs);
} catch (Exception ex) {
} finally {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
}
return null;
}
public Object ExecuteScalar(String cmdtext, Parameters parms)
throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = conn.prepareStatement (cmdtext);
prepareCommand(pstmt, parms);
rs = pstmt.executeQuery();
if (rs.next()) {
return rs.getObject(1);
} else {
return null;
}
} catch (Exception e) {
} finally {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
}
return null;
}
private void prepareCommand(PreparedStatement pstmt, Parameters parms)
throws SQLException {
if (parms != null && parms.getLength() > 0) {
for (int i = 0; i < parms.getLength(); i++) {
MySqlParameter parm = parms.getParameter(i);
String value = parm.getValue ().toString();
switch (parm.getType()) {
case String:
pstmt.setString(i + 1, value);
break;
case Int16:
pstmt.setShort(i + 1, Short.parseShort(value));
break;
case Int32:
pstmt.setInt(i + 1, Integer.parseInt(value));
break;
case Int64:
pstmt.setLong(i + 1, Long.parseLong(value));
break;
case DateTime:
pstmt.setDate(i + 1, Date.valueOf(value));
break;
default:
pstmt.setObject(i + 1, value);
break;
}
}
}
}
static {
try {
Class.forName ("com.mysql.jdbc.Driver").newInstance();
} catch (Exception ex) {
}
}
public Parameters CreateFixedParms(int size) {
return new FixedParameters(size);
}
}
Data2Object<T>接口負責將ResultSet轉換為對象。
package dao;
import java.sql.ResultSet;
import java.sql.SQLException;
public interface Data2Object<T> {
public T DataMap(ResultSet rs) throws SQLException;
}
DatabaseManager負責調用
package dao;
import java.sql.SQLException;
public abstract class DatabaseManager {
public static Database getMySqlDatabase() throws SQLException{
return new MySqlDatabase ("jdbc:mysql://localhost/test? user=root&password=123&useUnicode=true&characterEncoding=U TF-8");
}
}
申明數據庫枚舉類型
package dao;
public enum DBType {
String,
Int16,
Int32,
Int64,
DateTime,
}
無參數調用的情況：
package bean;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import dao.Data2Object;
import dao.Database;
import dao.DatabaseManager;
import dao.SqlHelper;
public class ListBean {
public List<TestBean> Find() throws SQLException {
String sql = "select * from TestTable";
Database db = DatabaseManager.getMySqlDatabase ();
return db.ExecuteObject(new ListConvertor(), sql, null);
}
public class ListConvertor implements Data2Object<List<TestBean>>{
public List<TestBean> DataMap(ResultSet rs) throws SQLException {
List<TestBean> tests = new ArrayList<TestBean>();
while (rs.next()) {
TestBean bean = new TestBean ();
bean.setId(rs.getInt(1));
bean.setName(rs.getString(2));
tests.add(bean);
}
return tests;
}
}
}
申明參數接口
package dao;
public interface Parameters {
void Add(MySqlParameter parm);
int getLength();
MySqlParameter getParameter(int i);
}
實現該接口
package dao;
public class MySqlParameter {
private DBType type;
private int len;
public MySqlParameter(DBType type, int len, Object value) {
super();
this.type = type;
this.len = len;
this.value = value;
}
/**
* @return the type
*/
public DBType getType() {
return type;
}
/**
* @param type the type to set
*/
public void setType(DBType type) {
this.type = type;
}
/**
* @return the len
*/
public int getLen() {
return len;
}
/**
* @param len the len to set
*/
public void setLen(int len) {
this.len = len;
}
/**
* @return the value
*/
public Object getValue() {
return value;
}
/**
* @param value the value to set
*/
public void setValue(Object value) {
this.value = value;
}
private Object value;
}
包裝定長參數數組
package dao;
public class FixedParameters implements Parameters {
private MySqlParameter[] parms;
private int ptr = 0;
public FixedParameters(int size){
parms = new MySqlParameter[size];
}
public void Add(MySqlParameter parm) {
parms[ptr] = parm;
ptr++;
}
public MySqlParameter[] getBuffer() {
return parms;
}
public int getLength() {
return parms.length;
}
public MySqlParameter getParameter(int i) {
return parms[i];
}
}
添加操作，這裡包裝地還不夠好，沒在調用部分把MySql專有方式隔離出去。 懶得再寫個繼承關系了，將就一下把，呵呵
public int Add(TestBean test) throws SQLException{
String sql = "insert into TestTable (name) values (?);";
Database db = DatabaseManager.getMySqlDatabase ();
Parameters parms = db.CreateFixedParms(1);
parms.Add(new MySqlParameter (DBType.String,0,test.getName()));
return db.ExecuteNoneQuery(sql, parms);
}
