SqlBuilder.java

package cn.com.iresearch.mvideotracker.db.sqlite;

import cn.com.iresearch.mvideotracker.db.exception.DbException;
import cn.com.iresearch.mvideotracker.db.table.Id;
import cn.com.iresearch.mvideotracker.db.table.KeyValue;
import cn.com.iresearch.mvideotracker.db.table.ManyToOne;
import cn.com.iresearch.mvideotracker.db.table.Property;
import cn.com.iresearch.mvideotracker.db.table.TableInfo;

import android.text.TextUtils;

import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

public class SqlBuilder
{

	public static SqlInfo buildInsertSql(Object entity)
	{

		List<KeyValue> keyValueList = getSaveKeyValueListByEntity(entity);

		StringBuffer strSQL = new StringBuffer();
		SqlInfo sqlInfo = null;
		if (keyValueList != null && keyValueList.size() > 0)
		{

			sqlInfo = new SqlInfo();

			strSQL.append("INSERT INTO ");
			strSQL.append(TableInfo.get(entity.getClass()).getTableName());
			strSQL.append(" (");
			for (KeyValue kv : keyValueList)
			{
				strSQL.append(kv.getKey()).append(",");
				sqlInfo.addValue(kv.getValue());
			}
			strSQL.deleteCharAt(strSQL.length() - 1);
			strSQL.append(") VALUES ( ");

			int length = keyValueList.size();
			for (int i = 0; i < length; i++)
			{
				strSQL.append("?,");
			}
			strSQL.deleteCharAt(strSQL.length() - 1);
			strSQL.append(")");

			sqlInfo.setSql(strSQL.toString());
		}

		return sqlInfo;
	}

	public static List<KeyValue> getSaveKeyValueListByEntity(Object entity)
	{

		// List<KeyValue> keyValueList = new ArrayList<KeyValue>();
		List<KeyValue> keyValueList = new LinkedList<KeyValue>();

		TableInfo table = TableInfo.get(entity.getClass());
		Object idvalue = table.getId().getValue(entity);

		if (!(idvalue instanceof Integer))
		{
			if (idvalue instanceof String && idvalue != null)
			{
				KeyValue kv = new KeyValue(table.getId().getColumn(), idvalue);
				keyValueList.add(kv);
			}
		}

		Collection<Property> propertys = table.propertyMap.values();
		for (Property property : propertys)
		{
			KeyValue kv = property2KeyValue(property, entity);
			if (kv != null)
				keyValueList.add(kv);
		}

		Collection<ManyToOne> manyToOnes = table.manyToOneMap.values();
		for (ManyToOne many : manyToOnes)
		{
			KeyValue kv = manyToOne2KeyValue(many, entity);
			if (kv != null)
				keyValueList.add(kv);
		}

		return keyValueList;
	}

	private static String getDeleteSqlBytableName(String tableName)
	{
		return "DELETE FROM " + tableName;
	}

	public static SqlInfo buildDeleteSql(Object entity)
	{
		TableInfo table = TableInfo.get(entity.getClass());

		Id id = table.getId();
		Object idvalue = id.getValue(entity);

		if (idvalue == null)
		{
			throw new DbException("getDeleteSQL:" + entity.getClass() + " id value is null");
		}
		StringBuffer strSQL = new StringBuffer(getDeleteSqlBytableName(table.getTableName()));
		strSQL.append(" WHERE ").append(id.getColumn()).append("=?");

		SqlInfo sqlInfo = new SqlInfo();
		sqlInfo.setSql(strSQL.toString());
		sqlInfo.addValue(idvalue);

		return sqlInfo;
	}

	public static SqlInfo buildDeleteSql(Class<?> clazz, Object idValue)
	{
		TableInfo table = TableInfo.get(clazz);
		Id id = table.getId();

		if (null == idValue)
		{
			throw new DbException("getDeleteSQL:idValue is null");
		}

		StringBuffer strSQL = new StringBuffer(getDeleteSqlBytableName(table.getTableName()));
		strSQL.append(" WHERE ").append(id.getColumn()).append("=?");

		SqlInfo sqlInfo = new SqlInfo();
		sqlInfo.setSql(strSQL.toString());
		sqlInfo.addValue(idValue);

		return sqlInfo;
	}

	public static String buildDeleteSql(Class<?> clazz, String strWhere)
	{
		TableInfo table = TableInfo.get(clazz);
		StringBuffer strSQL = new StringBuffer(getDeleteSqlBytableName(table.getTableName()));

		if (!TextUtils.isEmpty(strWhere))
		{
			strSQL.append(" WHERE ");
			strSQL.append(strWhere);
		}

		return strSQL.toString();
	}

	private static String getSelectSqlByTableName(String tableName)
	{
		return new StringBuffer("SELECT * FROM ").append(tableName).toString();
	}

	public static String getSelectSQL(Class<?> clazz, Object idValue)
	{
		TableInfo table = TableInfo.get(clazz);

		StringBuffer strSQL = new StringBuffer(getSelectSqlByTableName(table.getTableName()));
		strSQL.append(" WHERE ");
		strSQL.append(getPropertyStrSql(table.getId().getColumn(), idValue));

		return strSQL.toString();
	}

	public static SqlInfo getSelectSqlAsSqlInfo(Class<?> clazz, Object idValue)
	{
		TableInfo table = TableInfo.get(clazz);

		StringBuffer strSQL = new StringBuffer(getSelectSqlByTableName(table.getTableName()));
		strSQL.append(" WHERE ").append(table.getId().getColumn()).append("=?");

		SqlInfo sqlInfo = new SqlInfo();
		sqlInfo.setSql(strSQL.toString());
		sqlInfo.addValue(idValue);

		return sqlInfo;
	}

	public static String getSelectSQL(Class<?> clazz)
	{
		return getSelectSqlByTableName(TableInfo.get(clazz).getTableName());
	}

	public static String getSelectSQLByWhere(Class<?> clazz, String strWhere)
	{
		TableInfo table = TableInfo.get(clazz);

		StringBuffer strSQL = new StringBuffer(getSelectSqlByTableName(table.getTableName()));

		if (!TextUtils.isEmpty(strWhere))
		{
			strSQL.append(" WHERE ").append(strWhere);
		}

		return strSQL.toString();
	}

	// ////////////////////////////update sql
	// start/////////////////////////////////////////////

	public static SqlInfo getUpdateSqlAsSqlInfo(Object entity)
	{

		TableInfo table = TableInfo.get(entity.getClass());
		Object idvalue = table.getId().getValue(entity);

		if (null == idvalue)
		{
			throw new DbException("this entity[" + entity.getClass() + "]'s id value is null");
		}

		List<KeyValue> keyValueList = new ArrayList<KeyValue>();

		Collection<Property> propertys = table.propertyMap.values();
		for (Property property : propertys)
		{
			KeyValue kv = property2KeyValue(property, entity);
			if (kv != null)
				keyValueList.add(kv);
		}

		Collection<ManyToOne> manyToOnes = table.manyToOneMap.values();
		for (ManyToOne many : manyToOnes)
		{
			KeyValue kv = manyToOne2KeyValue(many, entity);
			if (kv != null)
				keyValueList.add(kv);
		}

		if (keyValueList == null || keyValueList.size() == 0)
			return null;

		SqlInfo sqlInfo = new SqlInfo();
		StringBuffer strSQL = new StringBuffer("UPDATE ");
		strSQL.append(table.getTableName());
		strSQL.append(" SET ");
		for (KeyValue kv : keyValueList)
		{
			strSQL.append(kv.getKey()).append("=?,");
			sqlInfo.addValue(kv.getValue());
		}
		strSQL.deleteCharAt(strSQL.length() - 1);
		strSQL.append(" WHERE ").append(table.getId().getColumn()).append("=?");
		sqlInfo.addValue(idvalue);
		sqlInfo.setSql(strSQL.toString());
		return sqlInfo;
	}

	public static SqlInfo getUpdateSqlAsSqlInfo(Object entity, String strWhere)
	{

		TableInfo table = TableInfo.get(entity.getClass());

		List<KeyValue> keyValueList = new ArrayList<KeyValue>();

		Collection<Property> propertys = table.propertyMap.values();
		for (Property property : propertys)
		{
			KeyValue kv = property2KeyValue(property, entity);
			if (kv != null)
				keyValueList.add(kv);
		}

		Collection<ManyToOne> manyToOnes = table.manyToOneMap.values();
		for (ManyToOne many : manyToOnes)
		{
			KeyValue kv = manyToOne2KeyValue(many, entity);
			if (kv != null)
				keyValueList.add(kv);
		}

		if (keyValueList == null || keyValueList.size() == 0)
		{
			throw new DbException("this entity[" + entity.getClass() + "] has no property");
		}

		SqlInfo sqlInfo = new SqlInfo();
		StringBuffer strSQL = new StringBuffer("UPDATE ");
		strSQL.append(table.getTableName());
		strSQL.append(" SET ");
		for (KeyValue kv : keyValueList)
		{
			strSQL.append(kv.getKey()).append("=?,");
			sqlInfo.addValue(kv.getValue());
		}
		strSQL.deleteCharAt(strSQL.length() - 1);
		if (!TextUtils.isEmpty(strWhere))
		{
			strSQL.append(" WHERE ").append(strWhere);
		}
		sqlInfo.setSql(strSQL.toString());
		return sqlInfo;
	}

	/**
	 * 通过一个java对象，获取创建该对象的数据库表
	 * 
	 * @param clazz
	 * @return
	 */
	public static String getCreatTableSQL(Class<?> clazz)
	{
		TableInfo table = TableInfo.get(clazz);

		Id id = table.getId();
		StringBuffer strSQL = new StringBuffer();
		strSQL.append("CREATE TABLE IF NOT EXISTS ");
		strSQL.append(table.getTableName());
		strSQL.append(" ( ");

		Class<?> primaryClazz = id.getDataType();
		if (primaryClazz == int.class || primaryClazz == Integer.class)
			strSQL.append("\"").append(id.getColumn()).append("\"    ").append("INTEGER PRIMARY KEY AUTOINCREMENT,");
		else
			strSQL.append("\"").append(id.getColumn()).append("\"    ").append("TEXT PRIMARY KEY,");

		Collection<Property> propertys = table.propertyMap.values();
		for (Property property : propertys)
		{
			strSQL.append("\"").append(property.getColumn());
			strSQL.append("\",");
		}

		Collection<ManyToOne> manyToOnes = table.manyToOneMap.values();
		for (ManyToOne manyToOne : manyToOnes)
		{
			strSQL.append("\"").append(manyToOne.getColumn()).append("\",");
		}
		strSQL.deleteCharAt(strSQL.length() - 1);
		strSQL.append(" )");
		return strSQL.toString();
	}

	/**
	 * @param key
	 * @param value
	 * @return eg1: name='afinal' eg2: id=100
	 */
	private static String getPropertyStrSql(String key, Object value)
	{
		StringBuffer sbSQL = new StringBuffer(key).append("=");
		if (value instanceof String || value instanceof java.util.Date || value instanceof java.sql.Date)
		{
			sbSQL.append("'").append(value).append("'");
		} else
		{
			sbSQL.append(value);
		}
		return sbSQL.toString();
	}

	private static KeyValue property2KeyValue(Property property, Object entity)
	{
		KeyValue kv = null;
		String pcolumn = property.getColumn();
		Object value = property.getValue(entity);
		if (value != null)
		{
			kv = new KeyValue(pcolumn, value);
		} else
		{
			if (property.getDefaultValue() != null && property.getDefaultValue().trim().length() != 0)
				kv = new KeyValue(pcolumn, property.getDefaultValue());
		}
		return kv;
	}

	private static KeyValue manyToOne2KeyValue(ManyToOne many, Object entity)
	{
		KeyValue kv = null;
		String manycolumn = many.getColumn();
		Object manyobject = many.getValue(entity);
		if (manyobject != null)
		{
			Object manyvalue = TableInfo.get(manyobject.getClass()).getId().getValue(manyobject);
			if (manycolumn != null && manyvalue != null)
			{
				kv = new KeyValue(manycolumn, manyvalue);
			}
		}

		return kv;
	}

}