概述：
感觉hibernate或者jpa之类的orm映射框架硬把关系型数据库映射成对象没有太大的必要性，我想每个数据库表对应一个实体对象已经足够了，其他的关联问题根据需要在service层对应到每一个功能逐个进行解决，最大的好处是可以直接在代码里写出效率较高的sql语句。今天有时间，整理一下这个方案的思路，该思路不排斥团队开发的分工协作，开发效率也比较高。
该思路部分借鉴了公司内一同事的做法，至于最初的来源已不可考。
现在从数据实体类层到view层详述一下。
首先，数据里假定有两个表，建表语句如下：
create table student(
id NUMBER(19) not null,
name VARCHAR2(20) not null,
parent_id NUMBER(19),
primary key (id)
)
create table parent(
id NUMBER(19) not null,
name VARCHAR2(50) not null,
primary key (id)
)
很简单，一个学生一个家长，生成的实体类代码：
parent.java
package org.zodiac.model;
public class Parent implements java.io.Serializable 
{
private Long id;
private String name;
public Parent() {
}
public Parent(Long id, String name) {
this.id = id;
this.name = name;
}
public Long getId() {
return this.id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
}
student.java
package org.zodiac.model;
public class Student implements java.io.Serializable {
private Long id;
private String name;
private Long parentId;
public Student() {
}
public Student(Long id, String name) {
this.id = id;
this.name = name;
}
public Student(Long id, String name, Long parentId) {
this.id = id;
this.name = name;
this.parentId = parentId;
}
public Long getId() {
return this.id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public Long getParentId() {
return this.parentId;
}
public void setParentId(Long parentId) {
this.parentId = parentId;
}
}
数据访问层，实现一个基类来处理简单的查找和删除，该类是针对oracle数据库的实现，该类甚至也实现了分页查找的功能，参见之前的一篇关于PageBean的随笔，代码：
package org.zodiac.service.dao;
import java.util.List;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.zodiac.util.DAORowMapper;
import org.zodiac.vo.PageBean;
public class BaseJdbcDAO extends JdbcDaoSupport {
protected String tableName;
protected Class<?> objectClass;
protected String keyColumn;
public void setKeyColumn(String keyColumn) {
this.keyColumn = keyColumn;
}
public void setObjectClass(Class<?> objectClass) {
this.objectClass = objectClass;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public int delete(Object id) {
String sql = "delete from " + tableName + " where " + keyColumn + "=?";
int rs = getJdbcTemplate().update(sql, new Object[] { id });
return rs;
}
protected List<?> findAll() {
String sql = "select * from " + tableName;
return getJdbcTemplate().query(sql, new DAORowMapper(objectClass));
}
protected Object findById(Object id) {
String sql = "select * from " + tableName + " where " + keyColumn + "=?";
return getJdbcTemplate().queryForObject(sql, new Object[] { id }, new DAORowMapper(objectClass));
}
protected List<?> findByProperty(String name, Object value) {
String sql = "select * from " + tableName + " where " + name + "=?";
return getJdbcTemplate().query(sql, new Object[] { value }, new DAORowMapper(objectClass));
}
protected void getTotalRows(PageBean pb) {
getTotalRows(pb, "");
}
protected void getTotalRows(PageBean pb, String con) {
getTotalRows(pb, tableName, con);
}
protected void getTotalRows(PageBean pb, String tables, String con) {
String sql = "select count(*) from " + tables + (con == null || con.trim().length() == 0 ? "" : " where " + con);
pb.setTotalRows(new Long(getJdbcTemplate().queryForLong(sql)));
}
protected void getPageBean(PageBean pb) {
getPageBean(pb, "");
}
protected void getPageBean(PageBean pb, String con) {
getPageBean(pb, con, null);
}
protected void getPageBean(PageBean pb, String con, String order) {
getPageBean(pb, "tb.*", this.tableName + " tb", con, order);
}
protected void getPageBean(PageBean pb, String columns, String tables, String con, String order) {
getPageBean(pb, columns, tables, con, order, this.objectClass);
}
protected void getPageBean(PageBean pb, String columns, String tables, String con, String order, Class<?> resultClass) {
order = "";
String sql = "select * from (select dt.*,rownum rn from (select " + columns + " from " + tables + (con == null || con.trim().length() == 0 ? "" :" where " + con)
+ (order == null || order.length() == 0 ? "" : " order by " + order) + ") dt where rownum<=" + pb.getEndIndex().intValue() + ") dt where dt.rn>=" + pb.getStartIndex().intValue()
+ " order by rn ";
logger.info(sql);
List<?> ls = getJdbcTemplate().query(sql, new DAORowMapper(resultClass));
pb.setList(ls);
}
}
其中的DAORowMapper代码如下：
DAORowMapper.java
package org.zodiac.util;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.StringTokenizer;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSetMetaData;
public class DAORowMapper implements RowMapper {
private Class<?> rowObjectClass;
public DAORowMapper(Class<?> rowObjectClass) {
this.rowObjectClass = rowObjectClass;
}
public Object mapRow(ResultSet rs, int index) throws SQLException {
Object object;
try {
object = rowObjectClass.newInstance();
} catch (Exception e) {
throw new RuntimeException(e);
}
ResultSetWrappingSqlRowSetMetaData wapping = new ResultSetWrappingSqlRowSetMetaData(rs.getMetaData());
for (int i = 1; i <= wapping.getColumnCount(); i++) {
String name = StringUtils.lowerCase(wapping.getColumnName(i));
StringTokenizer st = new StringTokenizer(name,"_");
int tokensize = st.countTokens();
StringBuffer tmpStr = new StringBuffer();
for(int j = 0;j<tokensize;j++){
String strTk = st.nextToken();
if(j>0){
strTk = StringUtils.capitalize( strTk);
}
tmpStr.append(strTk);
}
name = tmpStr.toString();
Object value = null;
//此处因为oracle驱动对timestamp字段的读取问题，需要做个判断
if (rs.getMetaData().getColumnType(i)==Types.DATE){
value = rs.getTimestamp(i);
}else {
value = rs.getObject(i);
}
try {
BeanUtils.copyProperty(object, name, value);
} catch (Exception e) {
e.printStackTrace();
}
}
return object;
}
}
现在以Student为例实现一个对应实体类的数据访问对象，解决其单个查询、条件查询和增加修改操作，代码：
StudentDAO.java
package org.zodiac.service.dao;
import java.sql.Types;
import java.util.List;
import org.springframework.jdbc.core.RowMapper;
import org.zodiac.model.Student;
import org.zodiac.util.DAORowMapper;
import org.zodiac.vo.DataNotFoundException;
import org.zodiac.vo.InsertFailedException;
public class StudentDAO extends BaseJdbcDAO {
@SuppressWarnings("unchecked")
public List<Student> findByCondition(String condition){
String sql = "select * from "+tableName+condition;
RowMapper row = new DAORowMapper(objectClass);
List<Student> students = getJdbcTemplate().query(sql, row);
return students;
}
public long save(Student student){
long id = getJdbcTemplate().queryForLong("select STUDENT_SEQ.nextval FROM DUAL");
if (id < 1) {
throw new DataNotFoundException("选择序列出现异常");
}
student.setId(id);
String sql = "insert into "+tableName+
" (id,name,parent_id) " +
"values (?,?,?)";
Object[] params = new Object[] {
student.getId(),
student.getName(),
student.getParentId()
};
int[] types = new int[] {
Types.NUMERIC,
Types.VARCHAR,
Types.NUMERIC
};
if (getJdbcTemplate().update(sql, params, types) != 1) {
throw new InsertFailedException();
}
return id;
}
public Student find(long id){
Student student = (Student)findById((Object)id);
return student;
}
public boolean modify(Student student){
Long id = student.getId();
if (id==null&&id<=0){
throw new DataNotFoundException("查询数据异常");
}
String sql = "update "+tableName+
" set name=?,parent_id=?" +
" where id="+id;
Object[] params = new Object[] {
student.getName(),
student.getParentId()
};
int[] types = new int[] {
Types.VARCHAR,
Types.NUMERIC
};
if (getJdbcTemplate().update(sql, params, types) != 1) {
throw new InsertFailedException();
}
return true;
}
}
以上用到的异常类皆继承spring提供的DataAccessException，我暂时放在vo包里，也许有不妥，可以推敲，但不影响整体结构，其代码大同小异：
DataNotFoundException.java
package org.zodiac.vo;
import org.springframework.dao.DataAccessException;
public class DataNotFoundException extends DataAccessException {
/** *//**
*
*/
private static final long serialVersionUID = 4631397254851356222L;
public DataNotFoundException(String msg, Throwable ex) {
super(msg, ex);
}
public DataNotFoundException(String msg) {
super(msg);
}
public DataNotFoundException() {
super("DataNotFound!");
}
}
先到这，下次写服务层和action以及spring的配置文件。