实现高效易用的java操纵mysql包装
副标题#e#
为了简化一些操纵，同时为了能兼容其他数据库，因此并没用利用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);
}
}
#p#副标题#e#
Data2Object<T>接口认真将ResultSet转换为工具。
#p#分页标题#e#
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;
}
}
}
申明参数接口
#p#分页标题#e#
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);
}
