如有转载和引用，请注明出处，谢谢
代码不一定完整，具体代码已经托管到 git@osc 完整代码点我
1. 定义我们需要的注解
要想实现对数据库的操作，我们必须知道数据表名以及表中的字段名称以及类型，正如hibernate 使用注解标识 model 与数据库的映射关系一样，这里我也设计了三个注解
Table 注解
用来表明该类与数据表的关联关系
package com.loweir.jdbcUtil.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Created by loweir on 2017/3/10.
* 用来标记类与数据库表的关系
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Table {
// 对应的数据库表名
public String name();
// 对应的数据库名，暂时无用，以后可能用到
public String catalog() default "";
}
column 注解
用来标记类中属性与数据表中字段的关联关系
package com.loweir.jdbcUtil.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Created by loweir on 2017/3/10.
* 编辑类中属性与数据库表字段的关系
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Column {
// 数据字段名称
public String name();
// 是否允许为空
public boolean nullable() default true;
// 是否是主键
public boolean primary() default false;
// 主键是否自增长 默认 true
public boolean autoIncrement() default true;
}
like 注解 用于支持模糊查询
package com.loweir.jdbcUtil.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Created by loweir on 2017/3/10.
* 标记这个字段需要模糊查询
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Like {
LikeType value() default LikeType.BOTH;
}
LikeType 代码，枚举类型
package com.loweir.jdbcUtil.annotation;
/**
* Created by loweir on 2017/3/10.
*/
public enum LikeType {
BOTH, // 全匹配 eg. %abc%
BEFORE, // 前匹配 eg. %abc
AFTER // 后匹配 eg. abc%
}
字段属性表
package com.loweir.jdbcUtil.annotation;
/**
* Created by loweir on 2017/3/10.
* 临时存储映射信息
*/
public class DBColumnInfo {
private String name; // 数据库字段名称
private boolean nullable; // 是否为空
private boolean primary; // 是否是主键
private boolean autoIncrement; // 是否是自增
private LikeType likeType ; // 模糊查询
public DBColumnInfo() {
}
public DBColumnInfo(String name, boolean nullable, boolean primary, boolean autoIncrement,LikeType likeType) {
this.name = name;
this.nullable = nullable;
this.primary = primary;
this.autoIncrement = autoIncrement;
this.likeType = likeType;
}
// setter and getter
}
2. 反射工具类
提供一些常用的反射操作
package club.loweir.jdbc.util;
/**
* Created by loweir on 2016/9/7.
*/
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class BeanReflectionUtil {
/**
* 获得某类的静态属性
* @param className
* @param fieldName
* @return
* @throws Exception
*/
public static Object getStaticProperty(String className,String fieldName)throws Exception{
Class cls=Class.forName(className);
Field field=cls.getField(fieldName);
Object provalue=field.get(cls);
return provalue;
}
/**
* 获取参数对象的属性值
* @param obj
* @param propertyName
* @return
* @throws Exception
*/
public static Object getPrivatePropertyValue(Object obj,String propertyName)throws Exception{
Class cls=obj.getClass();
Field field=cls.getDeclaredField(propertyName);
field.setAccessible(true);
Object retvalue=field.get(obj);
return retvalue;
}
/**
* 执行某对象的方法
* @param owner
* @param methodName
* @param args
* @return
* @throws Exception
*/
public Object invokeMethod(Object owner,String methodName,Object[] args)throws Exception{
Class cls=owner.getClass();
Class[] argclass=new Class[args.length];
for(int i=0,j=argclass.length;i<j;i++){
argclass[i]=args[i].getClass();
}
Method method=cls.getMethod(methodName,argclass);
return method.invoke(owner, args);
}
/**
* 执行静态类的方法
* @param className
* @param methodName
* @param args
* @return
* @throws Exception
*/
public Object invokeStaticMethod(String className,String methodName,Object[] args)throws Exception{
Class cls=Class.forName(className);
Class[] argclass=new Class[args.length];
for(int i=0,j=argclass.length;i<j;i++){
argclass[i]=args[i].getClass();
}
Method method=cls.getMethod(methodName,argclass);
return method.invoke(null, args);
}
public static Object newInstance(String className)throws Exception{
Class clss=Class.forName(className);
java.lang.reflect.Constructor cons=clss.getConstructor();
return cons.newInstance();
}
/**
* 等到对象的所有属性
* @param className
* @return
* @throws ClassNotFoundException
*/
public static Field[] getBeanDeclaredFields(String className) throws ClassNotFoundException{
Class clss=Class.forName(className);
Field[] fs = clss.getDeclaredFields();
return fs;
}
/*
* 得到类中的方法
*/
public static Method[] getBeanDeclaredMethods(String className) throws ClassNotFoundException{
Class clss=Class.forName(className);
Method[] methods = clss.getMethods();
return methods;
}
/**
* bean的属性拷贝
* @param source 被拷贝的对象
* @param target 拷贝的对象
*/
public static void copyProperties(Object source,Object target){
try {
List<Field> list = new ArrayList<Field>();
Field[] sourceFild = getBeanDeclaredFields(source.getClass().getName());
Field[] targetFild = getBeanDeclaredFields(target.getClass().getName());
Map<String,Field> map = new HashMap<String,Field>();
for(Field field : targetFild){
map.put(field.getName(), field);
}
for(Field field : sourceFild){
if(map.get(field.getName())!=null){
list.add(field);
}
}
//source 属性值
for(Field field : list){
if(field.getName().equals("tableName")){
continue;
}
Field tg = map.get(field.getName());
tg.setAccessible(true);
tg.set(target, getPrivatePropertyValue(source,field.getName()));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
3. 简单的 model
package com.loweir.jdbcUtil.test.model;
import com.loweir.jdbcUtil.annotation.Column;
import com.loweir.jdbcUtil.annotation.Like;
import com.loweir.jdbcUtil.annotation.Table;
/**
* Created by loweir on 2017/3/10.
* 测试使用model 对应数据库的 department 表
*/
@Table(name="department")
public class Department {
@Column(name = "id",nullable = false,primary = true,autoIncrement = true)
private Integer id;
// 一般情况下，对部门名称有模糊搜索的需求
@Like
@Column(name = "name")
private String name;
@Column(name = "description")
private String description;
// setter and getter
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
}
4. 注解解析
将对象的上的注解进行解析，得到对应关系
package com.loweir.jdbcUtil.annotation;
import com.loweir.jdbcUtil.utils.BeanReflectionUtil;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
/**
* Created by loweir on 2017/3/10.
* 注解解析类
*/
public class AnnotationParse {
/**
* 得到 Table 注解，如果不使用 Table 注解，可在类中使用 tableName 属性，可用于水平分表处理
* @param bean
* @return
*/
public static String getTableName(Object bean) {
Table table = bean.getClass().getAnnotation(Table.class);
if (table != null) {
return table.name();
}
else {
try {
return (String) BeanReflectionUtil.getPrivatePropertyValue(bean, "tableName");
} catch (Exception e) {
// 此处可以换成对应的日志操作
System.out.println("请检查类 [" + bean.getClass().getName() + "] 是否添加了 Table 注解 或者 tableName 成员变量！");
e.printStackTrace();
}
}
return null;
}
/**
* 获取一个对象中属性注解，
* @param bean
* @return
*/
public static List<DBColumnInfo> getDBColumn(Object bean) {
List<DBColumnInfo> dbColumnInfos = new ArrayList<DBColumnInfo>();
DBColumnInfo dbColumnInfo = null;
Field[] fields = bean.getClass().getDeclaredFields();
for(Field field : fields) {
Column column = field.getAnnotation(Column.class); // 得到 字段注解
if (column != null) { // 如果字段注解不为空
// 得到like
Like like = field.getAnnotation(Like.class); // 判断是否使用模糊查询
if (like == null) {
dbColumnInfo = new DBColumnInfo(column.name(),column.nullable(),column.primary(),column.autoIncrement(),null);
}
else {
dbColumnInfo = new DBColumnInfo(column.name(),column.nullable(),column.primary(),column.autoIncrement(),like.value());
}
dbColumnInfos.add(dbColumnInfo);
}
}
return dbColumnInfos;
}
/**
* 返回表字段的所有字段 column1,column2,column3
* @param object
* @return
*/
public static String getTableAllColumn(Object object){
List<DBColumnInfo> list = getDBColumn(object);
StringBuffer sb = new StringBuffer();
int i=1;
for(DBColumnInfo vo :list){
sb.append(vo.getName());
if(i!=list.size()){
sb.append(",");
}
i++;
}
return sb.toString();
}
}
5. jdbc工具类
package club.loweir.jdbc.util;
import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class DBManager {
Statement st=null;
PreparedStatement pstmt = null;
CallableStatement cst = null;
/**get database connection
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost/score_v2","root","root");
//"jdbc:mysql://localhost/bookge","root",""
//"jdbc:mysql://localhost:3306/bookge"
//user=root&password=root&useUnicode=true&characterEncoding=gb2312";
//con.setAutoCommit(false);
return con;
}
/**
* @param args
*/
public static void main(String[] args) throws SQLException{
// TODO Auto-generated method stub
System.out.println("------conn--------" + getConnection());
//System.out.println(DbPool.getPool());
System.out.println("------conn.getAutoCommit()--------" + getConnection().getAutoCommit());
//System.out.println("------DbPool.class.hashCode()--------" + DbPool.class.hashCode());
}
public static void clearup(PreparedStatement pstmt) {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace();
}
pstmt = null;
}
}
public static List getObjectListBySql(String sql ,Object[] args ,Object obj,String colums) {
List<Object> list = new ArrayList<Object>();
Connection conn = null;
ResultSet result = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
pstmt.setObject(i+1, args[i]);
}
result = pstmt.executeQuery();
//SqlRowSet result = this.getConnection().prepareStatement(sql);
Map<String,String> map = new HashMap<String,String>();
if(colums!=null && colums.length()>0){
String[] tempStrings = colums.split(",");
for(String str: tempStrings){
map.put(str.trim(), str.trim());
}
}else{
Object tableName = AnnoationParse.getTableName(obj);
List<DBColumn> clist = AnnoationParse.getDBColumn(obj);
for(DBColumn vo : clist ){
map.put(vo.getColumnName(), vo.getColumnName());
}
}
//obj 熟悉字段
Field[] fields = BeanReflectionUtil.getBeanDeclaredFields(obj.getClass().getName());
while(result.next()){
Object temp = BeanReflectionUtil.newInstance(obj.getClass().getName());
for(Field field : fields){
if(map.get(field.getName())!=null){
field.setAccessible(true);
field.set(temp, result.getObject(field.getName()));
}
}
list.add(temp);
}
} catch (Exception e) {
e.printStackTrace();
// log.error(StringUtil.outputException(e));
}
finally {
clearup(conn, pstmt, result);
}
return list;
}
/**
* Close database resource.
*
* @param conn
* @param pstmt
*/
public static void clearup(Connection conn, PreparedStatement pstmt) {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace();
}
pstmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
conn = null;
}
}
/**
* Close databse resource.
*
* @param conn
* @param pstmt
* @param rs
*/
public static void clearup(Connection conn, PreparedStatement pstmt,
ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException se) {
se.printStackTrace();
}
rs = null;
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace();
}
pstmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
conn = null;
}
}
/**
* Close databse resource.
* @param pstmt
* @param rs
*/
public static void clearup(PreparedStatement pstmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException se) {
se.printStackTrace();
}
rs = null;
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace();
}
pstmt = null;
}
}
}
6. 结合反射实现查询操作
package com.loweir.jdbcUtil;
import com.loweir.jdbcUtil.annotation.AnnotationParse;
import com.loweir.jdbcUtil.annotation.DBColumnInfo;
import com.loweir.jdbcUtil.annotation.LikeType;
import com.loweir.jdbcUtil.utils.BeanReflectionUtil;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
* Created by loweir on 2017/3/10.
*/
public class JdbcUtil {
/**
* 根据一个实例对象 得到对应的数据数据，该操作会根据实例对象中的非空项进行where 查询
* 当全部项为空时使用 select * from tableName
* 不管查出多少数据，只取第一条记录
* 没有数据返回 null
*/
public Object getBean(Object bean) {
List<Object> list = new ArrayList<Object>();
try {
Field[] fields = BeanReflectionUtil.getBeanDeclaredFields(bean.getClass().getName());
Object tableName = AnnotationParse.getTableName(bean);
StringBuffer where = new StringBuffer();
List<Object> propertyValue = new ArrayList<Object>();
List<DBColumnInfo> clist = AnnotationParse.getDBColumn(bean);
for(DBColumnInfo vo : clist ){
Object o = BeanReflectionUtil.getPrivatePropertyValue(bean,vo.getName());
if(o!=null && !o.toString().equals("")){
if (vo.getLikeType() == null) {
where.append(" and ").append(vo.getName()).append(" =?");
propertyValue.add(o);
}
else {
where.append(" and ").append(vo.getName()).append(" like ? ");
if (vo.getLikeType() == LikeType.BEFORE) { // 前
propertyValue.add("%" + o);
}
else if (vo.getLikeType() == LikeType.BOTH ) { // 后
propertyValue.add("%" + o + "%");
}
else {
propertyValue.add(o + "%");
}
}
}
}
String sql = null;
SqlRowSet result = null;
//带条件的查询
if(propertyValue.size()>0){
sql = "select * from "+tableName+ " where "+ where.toString().substring(4);
}
else{
sql = "select * from "+tableName;
}
if(propertyValue.size()>0){
result = this.getJdbcTemplate().queryForRowSet(sql,propertyValue.toArray());
}
else{
result = this.getJdbcTemplate().queryForRowSet(sql);
}
Map<String,String> map = new HashMap<String,String>();
for(DBColumnInfo vo : clist ){
map.put(vo.getName(), vo.getName());
}
while(result.next()){
Object temp = BeanReflectionUtil.newInstance(bean.getClass().getName());
for(Field field : fields){
if(map.get(field.getName())!=null){
field.setAccessible(true);
field.set(temp, result.getObject(field.getName()));
}
}
list.add(temp);
}
} catch (Exception e) {
e.printStackTrace();
}
if (list != null && list.size() > 0) {
return list.get(0);
}
else {
return null;
}
}
/**
* 根据对象ID得出数据
*/
public Object getBeanById(Object bean) {
Object temp = null;
boolean isexist = false;
try {
temp = BeanReflectionUtil.newInstance(bean.getClass().getName());
Object tableName = AnnotationParse.getTableName(bean);
StringBuffer where = new StringBuffer();
List<DBColumnInfo> list = AnnotationParse.getDBColumn(bean);
for(DBColumnInfo vo : list ){
if(vo.isPrimary()){
where.append(" and ").append( vo.getName()+"='").append(BeanReflectionUtil.getPrivatePropertyValue(bean, vo.getName())).append("'");
}
}
String sql = "select * from "+tableName+ " where "+ where.toString().substring(4);
//// log.info("getObjectById: "+sql);
SqlRowSet result = this.getJdbcTemplate().queryForRowSet(sql);
Field[] fields = BeanReflectionUtil.getBeanDeclaredFields(bean.getClass().getName());
Map<String,String> map = new HashMap<String,String>();
Set<String> set = new HashSet<>();
for(DBColumnInfo vo : list ){
map.put(vo.getName(), vo.getName());
}
while(result.next()){
isexist = true;
for(Field field : fields){
//表字段存在才有意义
if(map.get(field.getName())!=null){
field.setAccessible(true);
field.set(temp, result.getObject(field.getName()));
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
if(isexist){
return temp;
}
return null;
}
/**
* 根据sql语句和类型返回对应的对象，不管sql语句能查出多少条记录 都取 第一条
*/
@Override
public <T> T getBean(String sql, Class<T> bean) {
// TODO Auto-generated method stub
return (T) new BeanHandlerImpl().convert(this.getJdbcTemplate().queryForRowSet(sql), bean);
}
/**
* 提供安全的sql查询方法，防止 sql 注入攻击，不管条件sql语句能查出多少条记录 都取 第一条
*/
@Override
public <T> T getBean(String sql, Object[] params, Class<T> bean) {
return (T) new BeanHandlerImpl().convert(this.getJdbcTemplate().queryForRowSet(sql, params), bean);
}
/**
* 根据一个实例对象 得到对应的数据数据，该操作会根据实例对象中的非空项进行where 查询
* 当全部项为空时使用 select * from tableName
* 取出全部数据
*/
@Override
public List getBeans(Object bean) {
return getBeans(bean, null);
}
/**
* 根据一个实例对象 得到对应的数据数据，该操作会根据实例对象中的非空项进行where 查询
* 当全部项为空时使用 select * from tableName
* 取出对应分页的数据
*/
@Override
public List getBeans(Object bean, Page page) {
List<Object> list = new ArrayList<Object>();
try {
Field[] fields = BeanReflectionUtil.getBeanDeclaredFields(bean.getClass().getName());
Object tableName = AnnotationParse.getTableName(bean);
StringBuffer where = new StringBuffer();
List<Object> propertyValue = new ArrayList<Object>();
List<DBColumnInfo> clist = AnnotationParse.getDBColumn(bean);
for(DBColumnInfo vo : clist ){
Object o = BeanReflectionUtil.getPrivatePropertyValue(bean,vo.getName());
if(o!=null && !o.toString().equals("")){
if (vo.getLikeType() == null) {
where.append(" and ").append(vo.getName()).append(" =?");
propertyValue.add(o);
}
else {
where.append(" and ").append(vo.getName()).append(" like ? ");
if (vo.getLikeType() == LikeType.BEFORE) { // 前
propertyValue.add("%" + o);
}
else if (vo.getLikeType() == LikeType.BOTH ) { // 后
propertyValue.add("%" + o + "%");
}
else {
propertyValue.add(o + "%");
}
}
}
}
String sql = null;
SqlRowSet result = null;
//带条件的查询
if(propertyValue.size()>0){
sql = "select * from "+tableName+ " where "+ where.toString().substring(4);
}
else{
sql = "select * from "+tableName;
}
// 添加分页
if(page !=null){
StringBuffer buffer = new StringBuffer();
buffer.append("select count(*) from (");
buffer.append(sql);
buffer.append(") a");
long pageTotal = this.getJdbcTemplate().queryForObject(buffer.toString(),propertyValue.toArray(),Long.class);
page.setTotalRows((int) pageTotal);
sql = sql+ " limit " + page.getOffset()+","+page.getLimit();
}
if(propertyValue.size()>0){
result = this.getJdbcTemplate().queryForRowSet(sql,propertyValue.toArray());
}
else{
result = this.getJdbcTemplate().queryForRowSet(sql);
}
Map<String,String> map = new HashMap<String,String>();
for(DBColumnInfo vo : clist ){
map.put(vo.getName(), vo.getName());
}
while(result.next()){
Object temp = BeanReflectionUtil.newInstance(bean.getClass().getName());
for(Field field : fields){
if(map.get(field.getName())!=null){
field.setAccessible(true);
field.set(temp, result.getObject(field.getName()));
}
}
list.add(temp);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 根据sql语句和类型返回对应的对象，取出全部数据
*/
@Override
public <T> List<T> getBeans(String sql, Class<T> bean) {
return new BeansHandlerImpl().convert(this.getJdbcTemplate().queryForRowSet(sql), bean);
}
/**
* 根据sql语句和类型返回对应的对象，取出对应分页的数据
*/
@Override
public <T> List<T> getBeans(String sql, Class<T> bean, Page page) {
if(page !=null){
StringBuffer buffer = new StringBuffer();
buffer.append("select count(*) from (");
buffer.append(sql);
buffer.append(") a");
long pageTotal = this.getJdbcTemplate().queryForObject(buffer.toString(), Long.class);
page.setTotalRows((int)pageTotal);
sql = sql+ " limit " + page.getOffset()+","+page.getLimit();
}
return new BeansHandlerImpl().convert(this.getJdbcTemplate().queryForRowSet(sql), bean);
}
/**
* 根据sql语句和类型返回对应的对象，不管sql语句能查出多少条记录 都取 第一条
*/
@Override
public <T> List<T> getBeans(String sql, Object[] parsms, Class<T> bean) {
return new BeansHandlerImpl().convert(this.getJdbcTemplate().queryForRowSet(sql,parsms), bean);
}
/**
* 提供安全的sql查询方法，防止 sql 注入攻击，
* 根据sql语句和类型返回对应的对象，取到分页的数据
*/
@Override
public <T> List<T> getBeans(String sql, Object[] parsms, Class<T> bean, Page page) {
// 添加分页
if(page !=null){
StringBuffer buffer = new StringBuffer();
buffer.append("select count(1) from (");
buffer.append(sql);
buffer.append(") a");
long pageTotal = this.getJdbcTemplate().queryForObject(buffer.toString(),parsms,Long.class);
page.setTotalRows((int) pageTotal);
sql = sql+ " limit " + page.getOffset()+","+page.getLimit();
}
return new BeansHandlerImpl().convert(this.getJdbcTemplate().queryForRowSet(sql,parsms), bean);
}
/**
*
*/
@Override
public <T> T getSacle(String sql, Class<T> sacle) {
return getJdbcTemplate().queryForObject(sql, sacle);
}
@Override
public <T> T getSacle(String sql, Object[] params, Class<T> sacle) {
return getJdbcTemplate().queryForObject(sql, params, sacle);
}
/**
* 根据sql 语句和 分页信息得到数据，并将数据封装成一个 JSONARRAY
*/
public JSONArray getJSONArray(String sql,Page page) {
try {
//分页信息
if(page !=null){
StringBuffer buffer = new StringBuffer();
buffer.append("select count(*) from (");
buffer.append(sql);
buffer.append(") a");
long pageTotal = this.getJdbcTemplate().queryForObject(buffer.toString(), Long.class);
page.setTotalRows((int)pageTotal);
sql = sql+ " limit " + page.getOffset()+","+page.getLimit();
}
logger.info("pageSql : " + sql);
JSONArray jArray = new JSONArray();
SqlRowSet sqlRowSet = this.getJdbcTemplate().queryForRowSet(sql);
JsonArrayHandler jsonArrayHandler = new JsonArrayHandler();
return jsonArrayHandler.convert(sqlRowSet);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 安全的 添加参数
* 根据sql 语句和 分页信息得到数据，并将数据封装成一个 JSONARRAY
*/
public JSONArray getJSONArray(String sql,Object[] params,Page page) {
try {
//分页信息
if(page !=null){
StringBuffer buffer = new StringBuffer();
buffer.append("select count(*) from (");
buffer.append(sql);
buffer.append(") a");
long pageTotal = this.getJdbcTemplate().queryForObject(buffer.toString(),params, Long.class);
page.setTotalRows((int)pageTotal);
sql = sql+ " limit " + page.getOffset()+","+page.getLimit();
}
logger.info("pageSql : " + sql);
JSONArray jArray = new JSONArray();
SqlRowSet sqlRowSet = this.getJdbcTemplate().queryForRowSet(sql, params);
JsonArrayHandler jsonArrayHandler = new JsonArrayHandler();
return jsonArrayHandler.convert(sqlRowSet);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public JSONArray getJSONArray(Object bean,Page page,String column) {
return null;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////
public int updateBean(Object bean) {
try {
Object tableName = AnnotationParse.getTableName(bean);
StringBuffer property = new StringBuffer();
StringBuffer where = new StringBuffer();
List<Object> propertyValue = new ArrayList<Object>();
List<DBColumnInfo> list = AnnotationParse.getDBColumn(bean);
for(DBColumnInfo vo : list ){
Object o = BeanReflectionUtil.getPrivatePropertyValue(bean, vo.getName());
if(vo.isPrimary()){
where.append(" and ");
where.append(vo.getName()+" = '").append(o).append("'");
// continue;
}
else if(o!=null ){
property.append(",").append(vo.getName()).append("=?");
propertyValue.add(o);
}
}
String sql = "update "+tableName+ " set " +property.toString().substring(1) +" where "+ where.toString().substring(5);
return this.getJdbcTemplate().update(sql, propertyValue.toArray());
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
public int update(String sql) {
return this.getJdbcTemplate().update(sql);
}
public int update(String sql, Object[] params) {
return this.getJdbcTemplate().update(sql, params);
}
public int[] batch(String[] sqls) {
try {
return this.getJdbcTemplate().batchUpdate(sqls);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public int insert(Object bean) {
try {
Object tableName = AnnotationParse.getTableName(bean);
StringBuffer property = new StringBuffer();
StringBuffer value = new StringBuffer();
List<Object> propertyValue = new ArrayList<Object>();
List<DBColumnInfo> list = AnnotationParse.getDBColumn(bean);
for(DBColumnInfo vo : list ){
// 主键
if(vo.isPrimary()){
//有赋值的不是自动增长的
Object o = BeanReflectionUtil.getPrivatePropertyValue(bean, vo.getName());
if(o!=null){
property.append(",").append(vo.getName());
value.append(",").append("?");
propertyValue.add(o);
}
continue;
}
// 不为null
Object o = BeanReflectionUtil.getPrivatePropertyValue(bean, vo.getName());
if(o!=null){
property.append(",").append(vo.getName());
value.append(",").append("?");
propertyValue.add(o);
}
}
String sql = "insert into "+tableName+ "("+property.toString().substring(1)+") values("+value.toString().substring(1)+")";
return this.getJdbcTemplate().update(sql, propertyValue.toArray());
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
@Override
public int delete(Object bean) {
try {
Object tableName = AnnotationParse.getTableName(bean);
StringBuffer where = new StringBuffer(" 1=1 ");
List<DBColumnInfo> list = AnnotationParse.getDBColumn(bean);
for(DBColumnInfo vo : list ){
if(vo.isPrimary()){
where.append(" and ").append( vo.getName() +"='").append(BeanReflectionUtil.getPrivatePropertyValue(bean, vo.getName())).append("'");
}
}
String sql = "delete from "+tableName+ " where "+ where.toString();
/*// log.info("delete: "+sql);*/
return this.getJdbcTemplate().update(sql);
} catch (Exception e) {
e.printStackTrace();
//// log.error(StringUtil.outputException(e));
}
return 0;
}
public int deleteAll(Object bean) {
try {
Object tableName = AnnotationParse.getTableName(bean);
String sql = "delete from "+tableName;
return this.getJdbcTemplate().update(sql);
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
public JdbcTemplate getTemplate() {
return this.getJdbcTemplate();
}
public int[] batch(String sql, List<Object[]> params) {
// TODO Auto-generated method stub
return this.getJdbcTemplate().batchUpdate(sql, params);
}
}