使用Ibatis时,只需要把sql语句写好,指明需要注入的参数对应对象中的属性就可以了。这样极大的提高了开发效率。
我在工作过程中,遇到了一些场景,需要自己去直接连接JDBC写数据库,我又想模拟Ibatis自动补全参数的功能,所以就写了下面的这些代码。欢迎指证批评。
创建数据库连接的代码
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 2012-8-9 tracy.liuy
*/
public class DbConnection {
public Connection conn = null;
public DbConnection(String driver, String url, String user, String password){
try {
// 注册驱动程序类
Class.forName(driver);
// 1、初始化连接对象
conn = DriverManager.getConnection(url, user, password);
// 2、设置连接对象的自动提交模式
conn.setAutoCommit(false);
} catch (Exception e) {
throw new RuntimeException("get connect failed", e);
}
}
}
Customer对象
/**
* 2012-8-15 tracy.liuy
*/
public class Customer {
private Long custId;
private String name;
public Long getCustId() {
return custId;
}
public void setCustId(Long custId) {
this.custId = custId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
批量修改的代码
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
*
* 2012-8-15
* tracy.liuy
*/
public class IBatis {
/**
* @param sql
* @param campaignBdLogList 2012-8-1 tracy.liuy
* @throws InvocationTargetException
* @throws IllegalAccessException
* @throws NoSuchMethodException
* @throws IllegalArgumentException
* @throws SecurityException
*/
public int updateBatch(List objList) {
DbConnection dbC = new DbConnection("com.mysql.jdbc.Driver", "address",
"username", "password");
PreparedStatement stmt = null;
int rows = 0;
if (objList != null && objList.size() != 0) {
return rows;
}
//ibatis中的sql语句
String sql = "update coustomer set name=#name# where custid = #custId#";
//1.将 sql语句中 ## 及之间的内容替换成 ?,即 update coustomer set name=? where custid = ?
String prepareSql = placeSomeCharacter(sql);
try {
//2.创建连接
stmt = dbC.conn.prepareStatement(prepareSql);
for (Object obj : objList) {
//3.补全参数(详细介绍看下面的代码)
this.fillStatementBatch(stmt, sql, obj);
rows += stmt.executeUpdate();
}
} catch (SQLException e) {
throw new RuntimeException(sql + " update failed", e);
} catch (Exception e) {
throw new RuntimeException(sql + " update failed", e);
}
finally {
close(stmt);
}
return rows;
}
/**
* 使用正则表达式,替换需要注入的内容
* @param sql
* @return
* 2012-8-2
* tracy.liuy
*/
private String placeSomeCharacter(String sql) {
return sql.replaceAll("#.+?#", "?");
}
/**
* 将对象中的属性根据sql语句中指定的一一替换PreparedStatement中的?号
* @param stmt
* @param obj 2012-8-1 tracy.liuy
* @param obj2
* @throws SQLException
* @throws NoSuchMethodException
* @throws SecurityException
* @throws InvocationTargetException
* @throws IllegalAccessException
* @throws IllegalArgumentException
*/
private void fillStatementBatch(PreparedStatement stmt, String sql, Object obj) throws SQLException,
SecurityException,
NoSuchMethodException,
IllegalArgumentException,
IllegalAccessException,
InvocationTargetException {
char c = '#';
int begin = 0;
List<Integer> indexList = new ArrayList<Integer>();
while (true) {
int index = sql.indexOf(c, begin);
if (index == -1) {
break;
}
indexList.add(index);
begin = index+1;
}
List<String> props = new ArrayList<String>();
for (int i = 0; i < indexList.size(); i += 2) {
props.add(sql.substring(indexList.get(i)+1, indexList.get(i + 1)));
}
int i = 1;
for (String prop : props) {
stmt.setObject(i++, getProperties(obj, prop));
}
}
/**
* @param obj
* @param prop
* @return 2012-8-1 tracy.liuy
* @throws NoSuchMethodException
* @throws SecurityException
* @throws InvocationTargetException
* @throws IllegalAccessException
* @throws IllegalArgumentException
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
private Object getProperties(Object obj, String prop) throws SecurityException, NoSuchMethodException,
IllegalArgumentException, IllegalAccessException,
InvocationTargetException {
Method metd = null;
Class clazz = obj.getClass();
metd = clazz.getMethod("get" + change(prop), null);
if (metd != null) {
return metd.invoke(obj, null);
}
return null;
}
/**
* @param src 源字符串
* @return 字符串,将src的第一个字母转换为大写,src为空时返回null
*/
public String change(String src) {
if (src != null) {
StringBuffer sb = new StringBuffer(src);
sb.setCharAt(0, Character.toUpperCase(sb.charAt(0)));
return sb.toString();
} else {
return null;
}
}
/**
* 关闭结果集
*
* @param rs 结果集
*/
protected void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException("rs close failed", e);
}
}
}
/**
* 关闭语句对象
*
* @param stmt 语句对象
*/
protected void close(Statement stmt) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
throw new RuntimeException("stmt close failed", e);
}
}
}
}