package com.jqmobile.core.server.db.orm;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import com.jqmobile.core.orm.TableUtil;
import com.jqmobile.core.orm.exception.ORMException;
import com.jqmobile.core.orm.exception.ORMParamNotRecognitionException;
import com.jqmobile.core.utils.plain.FieldType;
import com.jqmobile.core.utils.plain.GUIDUtils;
import com.jqmobile.core.utils.plain.ModBean;
import com.jqmobile.core.utils.plain.ModColumn;
import com.jqmobile.core.utils.plain.UUIDUtils;
class ModORMImpl implements ModORM {
private ModBean initModiBean;
// 数据库连接对象
private Connection connection = null;
private List<ModColumn> modiColumns=null;
/**
* 初始化
*
* @param conn
*/
public ModORMImpl(Connection conn,ModBean modiBean) {
this.initModiBean=modiBean;
this.modiColumns=modiBean.getModiColumns();
this.connection = conn;
}
/**
* 自动验证表
*
* @throws ORMException
*/
private boolean autoValiTable(String tableName) {
TableUtil tableUtil = TableUtilFactory.instance(connection);
try {
return tableUtil.valiTableExist(tableName);
} catch (ORMException e) {
e.printStackTrace();
}
return false;
}
/*
* (non-Javadoc)
*
* @see com.jiuqi.mobile.orm.base.ORMModi#insert(T)
*/
@Override
public int insert(ModBean modiBean) throws ORMException {
int row = 0;
if (autoValiTable(modiBean.getTableName())) {
try {
String sql = insertSql(modiBean);
PreparedStatement ps = connection.prepareStatement(sql);
setValue(ps,modiBean,sql);
try {
row = ps.executeUpdate();
} finally {
ps.close();
}
} catch (SQLException e) {
throw new ORMException(e);
}
} else {
throw new ORMException("表不存在");
}
return row;
}
/**
* INT,SHORT,LONG,DOUBLE,FLOAT,STRING,UUID,GUID,DATE
* @param ps
* @param modiBean
* @throws SQLException
* @throws ORMException
*/
private void setValue(PreparedStatement ps, ModBean modiBean,String sql) throws SQLException, ORMException {
List<ModColumn> mcs = modiBean.getModiColumns();
int j=1;
for (int i=1;i<mcs.size()+1;i++) {
ModColumn mc=mcs.get(i-1);
if(mc.getType()!=null){
if(mc.getType().toString().equals("INT")){
ps.setInt(j, (Integer) mc.getValue());
j++;
continue;
}else if(mc.getType().toString().equals("SHORT")){
ps.setShort(j, (Short) mc.getValue());
j++;
continue;
}else if(mc.getType().toString().equals("DOUBLE")){
ps.setBigDecimal(j, new BigDecimal((Double) mc.getValue()));
j++;
continue;
}else if(mc.getType().toString().equals("FLOAT")){
ps.setFloat(j, (Float) mc.getValue());
j++;
continue;
}else if(mc.getType().toString().equals("STRING")){
if(mc.isPaimaryId()&&!sql.contains("insert")){
ps.setBytes(mcs.size(), UUIDUtils.getBytes(mc.getValue()));
continue;
}else if(mc.isPaimaryId()&&sql.contains("insert")){
ps.setBytes(j, UUIDUtils.getBytes(mc.getValue()));
j++;
continue;
}else{
ps.setString(j, (String) mc.getValue());
j++;
continue;
}
}else if(mc.getType().toString().equals("UUID")){
if(mc.isPaimaryId()&&!sql.startsWith("insert")){
ps.setBytes(mcs.size(), UUIDUtils.getBytes(mc.getValue()));
continue;
}else{
j++;
ps.setBytes(mcs.size()+1, UUIDUtils.getBytes(mc.getValue()));
continue;
}
}else if(mc.getType().toString().equals("DATE")){
Timestamp ts=new Timestamp((Long) mc.getValue());
ps.setTimestamp(j, ts);
j++;
continue;
}else if(mc.getType().toString().equals("LONG")){
ps.setLong(j, (Long) mc.getValue());
j++;
continue;
}else{
throw new ORMException("类型映射错误，不支持此类型");
}
}else{
throw new ORMException("列类型为null");
}
}
}
/**
* 拼装insert语句 insert into user ( id ,name,pwd) values(4,'d','d');
*
* @param modiBean
* @return
*/
private String insertSql(ModBean modiBean) {
StringBuilder sb = new StringBuilder(" insert into "
+ modiBean.getTableName() + " ( ");
List<ModColumn> mcs = modiBean.getModiColumns();
for (ModColumn mc : mcs) {
sb.append(mc.getName() + ", ");
}
sb.deleteCharAt(sb.length() - 2);
sb.append(" ) values(");
for (int i=0;i<mcs.size();i++) {
sb.append(" ?, ");
}
String sql=sb.substring(0, sb.toString().length()-2)+" ); ";
return sql;
}
/*
* @see com.jiuqi.mobile.orm.base.ORMModi#update(T)
*/
@Override
public int update(ModBean modiBean) throws ORMException {
if (autoValiTable(modiBean.getTableName())) {
try {
String sql = updateSql(modiBean);
PreparedStatement ps = connection.prepareStatement(sql);
setValue(ps, modiBean,sql);
try {
return ps.executeUpdate();
} finally {
ps.close();
}
} catch (SQLException e) {
throw new ORMException(e);
}
} else {
throw new ORMException("表不存在");
}
}
/**
* 更新语句 update user set name='sssss' where id=1
*
* @param modiBean
* @return
*/
private String updateSql(ModBean modiBean) {
StringBuilder sb = new StringBuilder(" update "
+ modiBean.getTableName() + " set ");
List<ModColumn> mcs = modiBean.getModiColumns();
for (ModColumn mc : mcs) {
if (mc.isPaimaryId()) {
continue;
}
sb.append(mc.getName() + " = " +"?,");
}
sb.delete(sb.length()-1, sb.length());
for (ModColumn mc : mcs) {
if (mc.isPaimaryId()) {
sb.append(" where "+ mc.getName()+ " =?");
}
}
return sb.toString();
}
@Override
public int delete(ModBean modiBean) throws ORMException {
if (autoValiTable(modiBean.getTableName())) {
try {
String sql = deleteSql(modiBean);
PreparedStatement ps = connection.prepareStatement(sql);
setValue(ps, modiBean, sql);
try {
return ps.executeUpdate();
} finally {
ps.close();
}
} catch (SQLException e) {
throw new ORMException(e);
}
} else {
throw new ORMException("表不存在");
}
}
/**
* 删除sql delete from user where id=4
*
* @param modiBean
* @return
*/
private String deleteSql(ModBean modiBean) {
StringBuilder sb = new StringBuilder(" delete from "
+ modiBean.getTableName() + " where ");
List<ModColumn> mcs = modiBean.getModiColumns();
for (ModColumn mc : mcs) {
if (mc.isPaimaryId()) {
sb.append(mc.getName()+"=?");
break;
}
}
return sb.toString();
}
@Override
public ModBean find(String tableName, String recid, String paimaryId)
throws ORMException {
return find(tableName, UUID.fromString(recid), paimaryId);
}
/**
* @throws ORMException
* @see com.jiuqi.mobile.orm.base.ORMModi#find(java.lang.String,
* java.util.UUID, java.lang.String)
*/
@Override
public ModBean find(String tableName, UUID recid, String paimaryId) {
if (autoValiTable(tableName)) {
String sql = findByIdSql(tableName, recid, paimaryId);
try {
return query(sql,null).get(0);
} catch (ORMException e) {
e.printStackTrace();
}
} else {
try {
throw new ORMException("表不存在");
} catch (ORMException e) {
e.printStackTrace();
}
}
return null;
}
/**
* 单条查询
*
* @param tableName
* @param recid
* @param paimaryId
* @return
*/
private String findByIdSql(String tableName, UUID recid, String paimaryId) {
StringBuilder sb = new StringBuilder(" select * from " + tableName
+ " where ");
sb.append(paimaryId + " = " + GUIDUtils.getBytes(recid));
return sb.toString();
}
/**
* @see com.jiuqi.mobile.orm.base.ORMModi#getAll()
*/
@Override
public List<ModBean> getAll(String tableName) throws ORMException {
String sql = "select * from " + tableName;
return query(sql);
}
@Override
public List<ModBean> query(String sql, Object... args) throws ORMException {
try {
PreparedStatement ps = connection.prepareStatement(sql);
if(args!=null&&args.length>0){
setValue(ps, args);
}
try {
ResultSet rs = ps.executeQuery();
try {
return getList(rs);
} catch (Exception e) {
throw new ORMException(e);
} finally {
rs.close();
}
} finally {
ps.close();
}
} catch (SQLException e) {
throw new ORMException(e);
}
}
private void setValue(PreparedStatement ps, Object[] args) throws SQLException {
for(int i=0;i<args.length;i++){
Object obj=args[i];
if(obj instanceof UUID){
ps.setObject(i+1, UUIDUtils.getBytes(obj));
}else if(obj.getClass().isPrimitive()){
ps.setObject(i+1, obj);
}else if(obj instanceof byte[] || obj instanceof Byte[]){
ps.setObject(i+1, obj);
}else{
throw new ORMParamNotRecognitionException(obj);
}
}
}
private List<ModBean> getList(ResultSet rs) throws SQLException,
ClassNotFoundException, ORMException {
ResultSetMetaData rsmd = rs.getMetaData();
List<ModBean> list = new ArrayList<ModBean>();
while (rs.next()) {
ModBean modiBean = new ModBean();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
ModColumn mc = new ModColumn();
mc.setName(rsmd.getColumnName(i + 1));
FieldType type=getFieldType(rsmd.getColumnTypeName(i + 1));
mc.setType(type);
mc.setValue(rs.getObject(i + 1));
modiBean.addColumn(mc);
}
list.add(modiBean);
}
return list;
}
/**
* bigint
* @param columnTypeName
* @return
* @throws ORMException
*/
private FieldType getFieldType(String columnTypeName) throws ORMException {
if(columnTypeName.toLowerCase().equals("binary")){
return FieldType.BYTEARRAY;
}else if(columnTypeName.toLowerCase().equals("smallint")){
return FieldType.SHORT;
}else if(columnTypeName.toLowerCase().equals("int")){
return FieldType.INT;
}else if(columnTypeName.toLowerCase().equals("float")){
return FieldType.FLOAT;
}else if(columnTypeName.toLowerCase().equals("decimal")){
return FieldType.DOUBLE;
}else if(columnTypeName.toLowerCase().equals("varchar")||columnTypeName.toLowerCase().equals("char")){
return FieldType.STRING;
}else if(columnTypeName.toLowerCase().equals("timestamp")){
return FieldType.DATE;
}else if(columnTypeName.toLowerCase().equals("bigint")){
return FieldType.INT;
}else{
throw new ORMException("类型映射错误，不支持此类型");
}
}
@Override
public List<ModBean> queryPage(String sql, long startIndex, long endIndex,
Object... args) throws ORMException {
try {
sql = sql + " limit ?,?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setLong(args.length + 1, startIndex);
ps.setLong(args.length + 2, endIndex);
try {
ResultSet rs = ps.executeQuery();
try {
return getList(rs);
} catch (Exception e) {
throw new ORMException(e);
} finally {
rs.close();
}
} finally {
ps.close();
}
} catch (SQLException e) {
throw new ORMException(e);
}
}
@Override
public ModBean queryFirst(String sql, Object... args) throws ORMException {
return query(sql, args).get(0);
}
@Override
public int queryRow(String sql, Object... args) throws ORMException {
try {
PreparedStatement ps = connection.prepareStatement(sql);
if (args != null && args.length > 0) {
setValue(ps, args);
}
try {
ResultSet rs = ps.executeQuery();
try {
if (rs.next())
return rs.getInt(1);
return 0;
} catch (Exception e) {
throw new ORMException(e);
} finally {
rs.close();
}
} finally {
ps.close();
}
} catch (SQLException e) {
throw new ORMException(e);
}
}
@Override
public int update(String sql, Object... args) throws ORMException {
try {
PreparedStatement ps = connection.prepareStatement(sql);
if (args != null && args.length > 0) {
setValue(ps, args);
}
try {
return ps.executeUpdate();
} finally {
ps.close();
}
} catch (SQLException e) {
throw new ORMException(e);
}
}
@Override
public int delete(String sql, Object... args) throws ORMException {
try {
PreparedStatement ps = connection.prepareStatement(sql);
if (args != null && args.length > 0) {
setValue(ps, args);
}
try {
return ps.executeUpdate();
} finally {
ps.close();
}
} catch (SQLException e) {
throw new ORMException(e);
}
}
@Override
public int delete(String recid) throws ORMException {
return delete(UUID.fromString(recid));
}
@Override
public int delete(UUID recid) throws ORMException {
for(ModColumn mc:modiColumns){
if(mc.isPaimaryId()){
mc.setValue(recid);
}
}
return delete(initModiBean);
}
@Override
public ModBean find(String recid) {
return find(recid);
}
@Override
public ModBean find(UUID recid) {
String paimaryName="";
for(ModColumn mc:modiColumns){
if(mc.isPaimaryId()){
paimaryName=mc.getName();
}
}
return find(initModiBean.getTableName(),recid,paimaryName);
}
@Override
public List<ModBean> getAll() throws ORMException {
return getAll(initModiBean.getTableName());
}
@Deprecated
/**
* 不可用
*/
public <D> List<D> queryRaw(String sql, Object[] args, ICallBack<D> back)
throws SQLException, InstantiationException, IllegalAccessException {
return null;
}
@Deprecated
/**
* 不可用
*/
public <D> List<D> queryRaw(String sql, long startIndex, long endIndex,
Object[] args, ICallBack<D> iCallBack) throws SQLException,
InstantiationException, IllegalAccessException {
return null;
}
@Deprecated
/**
* 不可用
*/
public <D> D queryRawFirst(String sql, Object[] args, ICallBack<D> back)
throws SQLException, InstantiationException, IllegalAccessException {
return null;
}
@Deprecated
/**
* 不可用
*/
public int modifyRaw(String sql, Object... args) throws SQLException {
return 0;
}
@Deprecated
/**
* 不可用
*/
public int deleteRaw(String sql, Object... args) throws SQLException {
return 0;
}
@Deprecated
/**
* 不可用
*/
public PreparedStatement getPrepareStatement(String sql)
throws SQLException {
return null;
}
@Override
public void createTable() throws ORMException {
if (autoValiTable(initModiBean.getTableName())) {
try {
String sql = creatTableSql();
System.out.println(sql);
PreparedStatement ps = connection.prepareStatement(sql);
try {
ps.executeUpdate();
} finally {
ps.close();
}
} catch (SQLException e) {
throw new ORMException(e);
}
}else{
try {
throw new ORMException("表不存在");
} catch (ORMException e) {
e.printStackTrace();
}
}
}
/**
* CREATE TABLE `t_datetest` (
* `id` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
* `birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
* PRIMARY KEY (`id`)
* )
* @param modiBean
* @return
* @throws ORMException
*/
private String creatTableSql() throws ORMException {
StringBuilder sb = new StringBuilder("create table `"
+ initModiBean.getTableName() + "` ( ");
String id="";
for (ModColumn mc : modiColumns) {
sb.append("`"+mc.getName() + "` "+getDataBaseType(mc));
if(mc.isPaimaryId()){
id=mc.getName();
}
}
sb.append(" PRIMARY KEY (`"+id+"`) \n)");
return sb.toString();
}
private String getDataBaseType(ModColumn modiColumn) throws ORMException {
String type=modiColumn.getType().toString().toUpperCase();
if(type.equals("LONG")){
return "bigint(20)"+",";
}else if(type.equals("DOUBLE")){
return "decimal(17,5)"+",";
}else if(type.equals("INT")){
return "int"+",";
}else if(type.equals("Float")){
return "float"+",";
}else if(type.equals("DATE")){
return "timestamp"+",";
}else if(type.equals("SHORT")){
return "smallint"+",";
}else if(type.equals("STRING")){
if(modiColumn.isPaimaryId()){
return "bigint(20)"+",";
}
return "varchar"+",";
}else{
throw new ORMException("类型映射错误，不支持此类型");
}
}
}