数据库ORM公用操作类代码,Java代码库,德仔网

代码语言
代码分类
【Java】数据库ORM公用操作类
作者:Dezai.CN / 发布于2011/6/15/ 721
适用于mysql数据库，目前还没完善多种数据库的扩展
望大牛能给与指正。
此工具类使用了BeanUtils来对pojo赋值及jdbc来获得对应的查询列。通过元注解来分析pojo与数据库的映射关系，由于时间有限未能实现复杂的映射关系，目前只支持单表映射。
调用的方法如下：
先定义与数据库表结构相符的pojo类
import com.loyin.util.db.anatations.ColumnLabel;
/**
* 系统参数
* @author loyin
* 2010-11-13
@Table(name="systemparam")//此处使用的是jpa注解
public class Param implements Serializable{
private static final long serialVersionUID = -2790464850751858558L;
@Id
@ColumnLabel(name="参数名")//自定义注解
@Column(name="paramname")
public String name;
@ColumnLabel(name="参数值")
@Column(name="paramvalue")
private String value;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
第二步：
从spring获得Dbutils示例对象，调用dbutils.insert(new Param());
具体其他的方法可以查看dbutils类。
ColumnLabl类是注解该属性的中文名称，用来显示到jsp页面中。
Disabled类是注解该属性为不是此pojo映射的字段。
基本原理就是通过解析元注解来生成sql，然后通过jdbc操作。
<div>package com.loyin.util.db;</div>
import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;</div>
import javax.annotation.Resource;
import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.sql.DataSource;</div>
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;</div>
import com.loyin.util.Page;
import com.loyin.util.db.anatations.Disabled;</div>
<div>@Service
public class DBUtil<T extends Serializable>
{
private static final Logger log = Logger.getLogger(DBUtil.class);
/**数据源*/
@Resource
private DataSource dataSource;</div>
<div>/**
 * 执行 增 删 改
 * 
 * @param sql
 * @param param SQL中传递参数
 * @return
 */
public int executeSQL(StringBuffer sql, List<Object> param) throws Exception {
 PreparedStatement ps=null;
Connection conn=null;
try{
conn=this.getConnaction();
ps = conn.prepareStatement(sql.toString());
if (param != null && !param.isEmpty()) {
int i = 1;
for (Object obj : param)
ps.setObject(i++, obj);
}
log.info(sql);
return ps.executeUpdate();
} catch (SQLException e) {
throw e;
}finally{
if(ps!=null)
ps.close();
if(conn!=null){
//DbUtils.close(conn);
}
}
}
/**
 * 执行 增 删 改
 * 
 * @param sql
 * @param param
 * SQL中传递参数
 * @return
 */
public int executeSQLBatch(StringBuffer sql, List<List<Object>> param) throws Exception {
PreparedStatement ps=null;
Connection conn=null;
try{
conn =this.getConnaction();
ps = conn.prepareStatement(sql.toString());
if (param != null && !param.isEmpty()) {
int coun=0;
for(List<Object> p:param){
int i = 1;
for (Object obj : p)
ps.setObject(i++, obj);
ps.addBatch();
coun++;
if(coun%100==0){
ps.executeBatch();
}
}
}
log.info(sql);
return ps.executeBatch().length;
} catch (SQLException e) {
log.error(e);
throw e;
}finally{
if(ps!=null)
ps.close();
if(conn!=null){
//DbUtils.close(conn);
}
}
}
/**
 * 执行查询
 * 
 * @param sql
 * @param param
 * @return ResultSet
 * @throws Exception
 */
public ResultSet executeQuery(String sql, List<Object> param,PreparedStatement ps,Connection conn)
throws Exception {
conn =this.getConnaction();
ResultSet rs = null;
ps = conn.prepareStatement(sql);
if (param != null && !param.isEmpty()) {
int i = 1;
for (Object obj : param)
ps.setObject(i++, obj);
}
log.info(sql);
rs = ps.executeQuery();
return rs;
}

/**
 * 执行查询
 * 
 * @param sql
 * @param param
 * 参数
 * @param List
 * &lt;String&gt;...clunmList 列名数组
 * @return List&lt;Object[]&gt;
 * @throws Exception
 */
public List<Object[]> query(StringBuffer sql, List<Object> param,
List<String>... clunmList) throws Exception {
ResultSet rs = null;
PreparedStatement ps=null;
Connection conn=null;
try {
rs = executeQuery(sql.toString(), param,ps,conn);
if (rs != null) {
List<Object[]> list = new ArrayList<Object[]>();
// 获取结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 总列数
int size = rsmd.getColumnCount();
if (clunmList != null) {
for (int cluIndex = 1; cluIndex <= size; cluIndex++) {
clunmList[0].add(rsmd.getColumnLabel(cluIndex));
}
}
while (rs.next()) {
Object[] value = new Object[size];
for (int j = 1; j <= size; j++) {
value[j - 1] = rs.getObject(j);
}
list.add(value);
}
return list;
}
} catch (Exception e) {
throw e;
} finally {
if (rs != null) {
try {
rs.close();
if(ps!=null)
ps.close();
} catch (SQLException e) {
throw e;
}
}
if(conn!=null){
//DbUtils.close(conn);
}
}
return null;
}
/**
 * 执行查询
 * 
 * @param sql
 * @param param
 * 参数
 * @param List &lt;String&gt;...clunmList 列名数组
 * @param tempEntity 外部传递的空实例。
 * @return List&lt;T&gt;
 * @throws Exception
 */
public List<T> queryEntityList(StringBuffer sql, List<Object> param,T tempEntity, List<String>... clunmList) throws Exception
{
ResultSet rs = null;
PreparedStatement ps=null;
Connection conn=null;
try {
rs = executeQuery(sql.toString(), param,ps,conn);
if (rs != null) {
List<T> list = new ArrayList<T>();
// 获取结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 总列数
int size = rsmd.getColumnCount();
if (clunmList != null) {
for (int cluIndex = 1; cluIndex <= size; cluIndex++) {
clunmList[0].add(rsmd.getColumnLabel(cluIndex));
}
}
while (rs.next()) {
Object obj=BeanUtils.cloneBean(tempEntity);
for (int cluIndex = 1; cluIndex <= size; cluIndex++) {
String colname=rsmd.getColumnName(cluIndex);
if(colname!=null&&!"".equals(colname))
BeanUtils.copyProperty(obj, colname, rs.getObject(cluIndex));
}
list.add((T)obj);
}
return list;
}
} catch (Exception e) {
throw e;
} finally {
if (rs != null) {
try {
rs.close();
if(ps!=null)
ps.close();
} catch (SQLException e1) {
throw e1;
}
}
if(conn!=null){
//DbUtils.close(conn);
}
}
return null;
}
/**
 * 执行单一对象查询
 * 
 * @param sql
 * @param param
 * 参数
 * @param po 需要赋值的对象
 * @throws Exception
 */
public Object queryEntity(StringBuffer sql, List<Object> param,Object po) throws Exception {
ResultSet rs = null;
PreparedStatement ps=null;
Connection conn=null;
try {
sql.append(" limit 0,1");
rs = executeQuery(sql.toString(), param,ps,conn);
if (rs != null) {
// 获取结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 总列数
int size = rsmd.getColumnCount();

if (rs.next()) {
for (int cluIndex = 1; cluIndex <= size; cluIndex++) {
String colname=rsmd.getColumnName(cluIndex);
if(colname!=null&&!"".equals(colname))
BeanUtils.copyProperty(po, colname, rs.getObject(cluIndex));
}
}
}
} catch (Exception e) {
throw e;
} finally {
if (rs != null) {
try {
rs.close();
if(ps!=null)
ps.close();
} catch (SQLException e) {
throw e;
}
}
if(conn!=null){
//DbUtils.close(conn);
}
}
return po;
}
public Object queryByExcample(Object po) throws Exception{</div>
<div>StringBuffer sql = new StringBuffer("SELECT * FROM ");
List<Object> params = new ArrayList<Object>();
Table table = po.getClass().getAnnotation(Table.class);
if (table != null)
sql.append(table.name());
else
sql.append(po.getClass().getSimpleName().toUpperCase());
sql.append(" WHERE ");
Field[] fields = po.getClass().getDeclaredFields();
for (Field field : fields)
{
String fieldname = field.getName();
if (!"serialVersionUID".equals(fieldname))
{
Object fieldvalue = PropertyUtils.getProperty(po, fieldname);
if (fieldvalue != null)
{
Disabled disabled=field.getAnnotation(Disabled.class);
if(disabled!=null){
continue;
}
Column column = field.getAnnotation(Column.class);
if (column != null)
{
sql.append(column.name());
sql.append("=? AND ");
params.add(fieldvalue);
} else
{
sql.append(fieldname);
sql.append("=? AND ");
params.add(fieldvalue);
}
}
}
}
sql.append("END");
return this.queryEntity(new StringBuffer(sql.toString().replaceAll("AND END", "").replaceAll("END", "")),params,po);
}
public boolean saveOrUpdate(Object po) throws Exception{
Field[] fields = po.getClass().getDeclaredFields();
for (Field field : fields)
{
Id id=field.getAnnotation(Id.class);
if(id!=null){
Object fieldvalue = PropertyUtils.getProperty(po, field.getName());
if(fieldvalue!=null){
this.update(po);
return true;
}
}
}
this.insert(po);
return true;
}
public void insert(Object po) throws Exception
{
StringBuffer sql = new StringBuffer("INSERT INTO ");
StringBuffer valuesql = new StringBuffer(" VALUES(");
List<Object> params = new ArrayList<Object>();
Table table = po.getClass().getAnnotation(Table.class);
if (table != null)
sql.append(table.name());
else
sql.append(po.getClass().getSimpleName().toUpperCase());
sql.append("(");
Field[] fields = po.getClass().getDeclaredFields();
for (Field field : fields)
{
String fieldname = field.getName();
if (!"serialVersionUID".equals(fieldname))
{
Object fieldvalue = PropertyUtils.getProperty(po, fieldname);
if (fieldvalue != null)
{Disabled disabled=field.getAnnotation(Disabled.class);
if(disabled!=null){
continue;
}
Column column = field.getAnnotation(Column.class);
if (column != null)
{
sql.append(column.name());
sql.append(",");
valuesql.append("?,");
params.add(fieldvalue);
} else
{
sql.append(fieldname);
sql.append(",");
valuesql.append("?,");
params.add(fieldvalue);
}
}
}
}
sql.append(")");
valuesql.append(")");
sql.append(valuesql);
this.executeSQL(new StringBuffer(sql.toString().replaceAll(",\\)", ")")), params);
}
public void update(Object po) throws Exception
{
StringBuffer sql = new StringBuffer("UPDATE ");
StringBuffer wheresql=new StringBuffer(" WHERE ");
List<Object> params = new ArrayList<Object>();
Table table = po.getClass().getAnnotation(Table.class);
if (table != null)
sql.append(table.name());
 else
sql.append(po.getClass().getSimpleName().toUpperCase());
sql.append(" SET ");

Field[] fields = po.getClass().getDeclaredFields();
Object keyvalue=null;
for (Field field : fields)
{
String fieldname = field.getName();
Id id=field.getAnnotation(Id.class);
if(id!=null){
Object fieldvalue = PropertyUtils.getProperty(po, fieldname);
Column column = field.getAnnotation(Column.class);
if (column != null)
{
wheresql.append(column.name());
}else{
wheresql.append(fieldname);
}
 wheresql.append("=?");
 keyvalue=fieldvalue;
continue;
}
if (!"serialVersionUID".equals(fieldname))
{
Object fieldvalue = PropertyUtils.getProperty(po, fieldname);
if (fieldvalue != null)
{
Disabled disabled=field.getAnnotation(Disabled.class);
if(disabled!=null){
continue;
}
Column column = field.getAnnotation(Column.class);
if (column != null)
{
sql.append(column.name());
sql.append("=?,");
} else
{
sql.append(fieldname);
sql.append("=?,");
}
params.add(fieldvalue);
}
}
}
params.add(keyvalue);
sql.append("EE");
sql.append(wheresql);
this.executeSQL(new StringBuffer(sql.toString().replaceAll(",EE", "")), params);
}
public void delete(Object po) throws Exception
{
StringBuffer sql = new StringBuffer("DELETE FROM ");
StringBuffer wheresql=new StringBuffer(" WHERE ");
List<Object> params = new ArrayList<Object>();
Table table = po.getClass().getAnnotation(Table.class);
if (table != null)
sql.append(table.name());
 else
sql.append(po.getClass().getSimpleName().toUpperCase());

Field[] fields = po.getClass().getDeclaredFields();
Object keyvalue=null;
for (Field field : fields)
{
String fieldname = field.getName();
Id id=field.getAnnotation(Id.class);
if(id!=null){
Object fieldvalue = PropertyUtils.getProperty(po, fieldname);
Column column = field.getAnnotation(Column.class);
if (column != null)
{
wheresql.append(column.name());
}else{
wheresql.append(fieldname);
}
 wheresql.append("=? AND");
 keyvalue=fieldvalue;
continue;
}
if (!"serialVersionUID".equals(fieldname))
{
Object fieldvalue = PropertyUtils.getProperty(po, fieldname);
if (fieldvalue != null)
{
Disabled disabled=field.getAnnotation(Disabled.class);
if(disabled!=null){
continue;
}
params.add(fieldvalue);
}
}
}
params.add(keyvalue);
sql.append(wheresql);
sql.append("EE");
this.executeSQL(new StringBuffer(sql.toString().replaceAll("ANDEE", "")), params);
}
/***
 * 分页sql查询 （暂时支持mysql）
 * 
 * @param sql
 * @param pageNo
 * @param pageSize
 * @param param
 * @param clunmList
 * @return
 * @throws Exception
 */
public Page pageQuery(StringBuffer sql, int pageNo, int pageSize,
List<Object> param, List<String>... clunmList) throws Exception {
ResultSet rs = null;
ResultSet rscount = null;
List<Object[]> list = null;
PreparedStatement ps=null;
Connection conn=null;
try {
if (pageNo == 0) {
pageNo = 1;
}
StringBuffer countSql = new StringBuffer("select count(*) ");
countSql.append(sql.substring(sql.indexOf(" from ")));
int count = 0;
if(countSql.toString().indexOf(" order ")>0){
rscount = executeQuery(countSql.toString().substring(0,countSql.toString().indexOf(" order ")), param,ps,conn);
}else
if(countSql.toString().indexOf(" group ")>0){
rscount = executeQuery(countSql.toString().substring(0,countSql.toString().indexOf(" group ")), param,ps,conn);
}else{
rscount = executeQuery(countSql.toString(), param,ps,conn);
}
rscount.next();
count = rscount.getInt(1);
if (count <= 0) {
return new Page(0, pageNo, pageSize, null);
}
sql.append(" limit ?,?");
if (param == null) {
param = new ArrayList<Object>();
}
param.add((pageNo - 1) * pageSize);
param.add(pageSize);
rs = executeQuery(sql.toString(), param,ps,conn);
if (rs != null) {
list = new ArrayList<Object[]>();
// 获取结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 总列数
int size = rsmd.getColumnCount();
if (clunmList != null) {
for (int cluIndex = 1; cluIndex <= size; cluIndex++) {
clunmList[0].add(rsmd.getColumnLabel(cluIndex));
}
}
while (rs.next()) {
Object[] value = new Object[size];
for (int j = 1; j <= size; j++) {
value[j - 1] = rs.getObject(j);
}
list.add(value);
}
}
return new Page(count, pageNo, pageSize, list);
} catch (Exception e) {
throw e;
} finally {
if (rs != null) {
try {
if (rscount != null)
rscount.close();
rs.close();
if(ps!=null)
ps.close();
} catch (SQLException e) {
throw e;
}
}
if(conn!=null){
//DbUtils.close(conn);
}
}
}
public Connection getConnaction() throws Exception{
return DataSourceUtils.doGetConnection(dataSource);//.getConnection(dataSource);
}
/**
 * 查询所有表名
 * @param catalog
 * @param schemaPattern
 * @param tableNamePattern
 * @param types
 * @return {catalog,schema,tableName,types}
 * @throws Exception
 */
public List<String[]> getAllTables(String catalog,String schemaPattern,String tableNamePattern,String[] types, List<String>... clunmList)throws Exception{
List<String[]> tables=new ArrayList<String[]>();
DatabaseMetaData md=null;
ResultSet rs = null;
Connection conn=this.getConnaction();
try{
md=conn.getMetaData();
rs=md.getTables(catalog,schemaPattern,tableNamePattern,types);
// 获取结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 总列数
int size = rsmd.getColumnCount();
if (clunmList != null) {
for (int cluIndex = 1; cluIndex <= size; cluIndex++) {
clunmList[0].add(rsmd.getColumnLabel(cluIndex));
}
}
while(rs.next()){
String[] tb=new String[size];
for(int i=1;i<=size;i++){
tb[i-1]=rs.getString(i);
}
tables.add(tb);
}
}catch(Exception e){
throw e;
}finally{
if(rs!=null){
rs.close();
}
if(conn!=null){
//DbUtils.close(conn);
}
}
return tables;
}
/**
 * 
 * @param catalog
 * @param schemaPattern
 * @param tableNamePattern
 * @param columnNamePattern
 * @return
 * @throws Exception
 */
public List<String[]> getColumns(String catalog,String schemaPattern,String tableNamePattern,String columnNamePattern, List<String>... clunmList)throws Exception{
List<String[]> tables=new ArrayList<String[]>();
DatabaseMetaData md=null;
ResultSet rs = null;
Connection conn=this.getConnaction();
try{
md=conn.getMetaData();
rs=md.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);
// 获取结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 总列数
int size = rsmd.getColumnCount();
if (clunmList != null) {
for (int cluIndex = 1; cluIndex <= size; cluIndex++) {
clunmList[0].add(rsmd.getColumnLabel(cluIndex));
}
}
while(rs.next()){
String[] tb=new String[size];
for(int i=1;i<=size;i++){
tb[i-1]=rs.getString(i);
}
tables.add(tb);
}
}catch(Exception e){
throw e;
}finally{
if(rs!=null){
rs.close();
}
if(conn!=null){
//DbUtils.close(conn);
}
}
return tables;
}
public DataSource getDataSource() {
return dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}

/*public static void main(String[] args) throws Exception {
ApplicationContext factory = new ClassPathXmlApplicationContext(new String[] {"spring-DataBase.xml"});
DBUtil dbUtil= new DBUtil();
DataSource dataSource =(DataSource) factory.getBean("dataSource");
dbUtil.setDataSource(dataSource);
List<String> columnList=new ArrayList<String>();
List<String[]> list=dbUtil.getColumns(null,null, null,null,columnList);
for(String col:columnList){
System.out.print(col+"\t");
}
System.out.print("\n");
for(String[] table:list){
for(String t:table){
System.out.print(t+"\t");
}
System.out.print("\n");
}
}*/</div>
<div>}
</div>
试试其它关键字
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)