2. sql@H_502_1@生成助手使用方法@H_502_1@

@H_502_1@

//你可以这样使用，下面这个其实就是sqlite-java-tool工具的全部代码，该工具就是试用了工具助手从而操作数据库的
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.sqlException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import org.lion.java.sqlite.hibernate.DataBaseDector;
import org.lion.java.sqlite.hibernate.sqliteDataBaseSession;
import org.lion.java.sqlite.hibernate.sqliteTableHelper;
import org.lion.java.sqlite.hibernate.sqliteUtils;
import org.lion.java.sqlite.hibernate.TableModel;
import org.lion.java.sqlite.hibernate.annotation.Column;
import org.sqlite.JDBC;


public class sqliteDataBase implements sqliteUtils,DataBaseDector{
	
	public static final String JDBC_DRIVER_CLASS = "org.sqlite.JDBC";
	
	private Connection connection;
	private Statement statement;
	private sqliteDataBaseSession session;
	
	/**
	 * create a database session to manage the sqlite database
	 * @param dbName such as
	 * 		@Windows D://sqlite/sqlite.sqlite
	 * 		@Linux /home/username/sqlite.sqlite
	 * @param packageToScan package to scan,in the package all the class will user for database model
	 */
	public sqliteDataBase( String dbName,String packageToScan ) {
		
		try {
			Class.forName( JDBC.class.getName() );
			connection = DriverManager.getConnection( JDBC.PREFIX + dbName );
			statement = connection.createStatement();
			session = new sqliteDataBaseSession( dbName,packageToScan );
			this.createTables();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (sqlException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * create a database session to manage the sqlite database
	 * @param dbName 
	 * @Windows D://sqlite/sqlite.sqlite
	 * @Linux /home/username/sqlite.sqlite
	 * @param classes models classes
	 */
	public sqliteDataBase( String dbName,Class<?>[] classes ) {
		
		try {
			Class.forName( JDBC.class.getName() );
			connection = DriverManager.getConnection( JDBC.PREFIX + dbName );
			statement = connection.createStatement();
			session = new sqliteDataBaseSession( dbName,classes );
			this.createTables();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (sqlException e) {
			e.printStackTrace();
		}
	}

	@Override
	public boolean isTableExist(String tablename) {
		String sql = session.isTableExist(tablename);
		try {
			System.out.println( sql );
			ResultSet set = statement.executeQuery(sql);
			return set.next();
		} catch (sqlException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public void createTables() {
		List<String> tables = session.createTables();
		for (String sql : tables) {
			boolean mark = this.isTableExist(sql.substring( 13,sql.indexOf( "(" )-1 ));
			if (mark==false) {
				try {
					System.out.println( sql );
					statement.execute(sql);
				} catch (sqlException e) {
				}
			}
		}
	}

	@Override
	public void dropTables() {
		List<String> tables = session.dropTables();
		for (String sql : tables) {
			try {
				System.out.println( sql );
				statement.execute(sql);
			} catch (sqlException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public void insert(String tablename,Object entity) {
		String sql = session.insert(tablename,entity);
		try {
			System.out.println( sql );
			statement.execute(sql);
		} catch (sqlException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void insert(String tablename,List<Object> entities) {
		String sql = session.insert(tablename,entities);
		try {
			System.out.println( sql );
			statement.execute(sql);
		} catch (sqlException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void delete(String tablename,Object entity) {
		String sql = session.delete(tablename,entity);
		try {
			System.out.println( sql );
			statement.execute(sql);
		} catch (sqlException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void delete(String tablename,List<Object> entities) {
		String sql = session.delete(tablename,entities);
		try {
			System.out.println( sql );
			statement.execute(sql);
		} catch (sqlException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void update(String tablename,Object entity) {
		String sql = session.update(tablename,entity);
		try {
			System.out.println( sql );
			statement.execute(sql);
		} catch (sqlException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void update(String tablename,List<Object> entities) {
		String sql = session.update(tablename,entities);
		try {
			System.out.println( sql );
			statement.execute(sql);
		} catch (sqlException e) {
			e.printStackTrace();
		}
	}

	@Override
	public List<Object> select(String tablename,String column,String value) {
		String sql = session.select(tablename,column,value);
		try {
			System.out.println( sql );
			ResultSet set = statement.executeQuery(sql);
			return getObjectList(tablename,set);
		} catch (sqlException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Object> select(String tablename,String[] columns,String[] values) {
		String sql = session.select(tablename,columns,values);
		try {
			System.out.println( sql );
			ResultSet set = statement.executeQuery(sql);
			return getObjectList(tablename,set);
		} catch (sqlException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Object> selectAll(String tablename) {
		String sql = session.selectAll(tablename);
		try {
			System.out.println( sql );
			ResultSet set = statement.executeQuery(sql);
			return getObjectList(tablename,set);
		} catch (sqlException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void execute(String tablename,String sql) {
		String temp = session.execute(tablename,sql);
		try {
			System.out.println( temp );
			statement.execute(temp);
		} catch (sqlException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void executeUpdate(String tablename,String sql) {
		String temp = session.executeUpdate(tablename,sql);
		try {
			System.out.println( temp );
			statement.executeUpdate(temp);
		} catch (sqlException e) {
			e.printStackTrace();
		}
	}

	@Override
	public List<Object> executeQurey(String tablename,String sql) {
		String temp = session.executeQurey(tablename,sql);
		try {
			System.out.println( temp );
			ResultSet set = statement.executeQuery(temp);
			return getObjectList(tablename,set);
		} catch (sqlException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void close() {
		try {
			connection.close();
		} catch (sqlException e) {
			e.printStackTrace();
		}
	}
	
	public List<Object> getObjectList( String tablename,ResultSet set ) throws sqlException,InstantiationException,IllegalAccessException{
		List<Object> objects = new ArrayList<Object>();
		Set<TableModel> tableModels = session.getTableModels();
		Class<?> tableClass = null;
		for (TableModel tableModel : tableModels) {
			if ( tableModel.getTableName().equals(tablename) ) {
				tableClass = tableModel.getClazz();
				break;
			}
		}
		Set<Field> fields = sqliteTableHelper.getAllFields(tableClass);
		
		while( set.next() ){
			Object entity = tableClass.newInstance();
			for (Field field : fields) {
				Column column = null;
				field.setAccessible(true);
				if (field.isAnnotationPresent(Column.class)) {
					column = (Column) field.getAnnotation(Column.class);
					if (column.value().equals("") == false) {
						setFieldValue(field,entity,set,column.value());
					} else {
						setFieldValue(field,field.getName());
					}
				} else {
					setFieldValue(field,field.getName());
				}
			}
			objects.add(entity);
		}
		
		return objects;
	}
	
	private void setFieldValue( Field field,Object entity,ResultSet set,String columnName ) throws IllegalAccessException,IllegalArgumentException,sqlException {
		if ((Integer.TYPE == field.getType() ) || (Integer.class == field.getType())) {
			field.set(entity,Integer.valueOf(set.getInt(columnName)));
		} else if (String.class == field.getType()) {
			field.set(entity,set.getString(columnName));
		} else if ((Long.TYPE == field.getType()) || (Long.class == field.getType())) {
			field.set(entity,Long.valueOf(set.getLong(columnName)));
		} else if ((Float.TYPE == field.getType()) || (Float.class == field.getType())) {
			field.set(entity,Float.valueOf(set.getFloat(columnName)));
		} else if ((Short.TYPE == field.getType()) || (Short.class == field.getType())) {
			field.set(entity,Short.valueOf(set.getShort(columnName)));
		} else if ((Double.TYPE == field.getType()) || (Double.class == field.getType())) {
			field.set(entity,Double.valueOf(set.getDouble(columnName)));
		} else if (Character.TYPE == field.getType()) {
			String fieldValue = set.getString(columnName);
			if ((fieldValue != null) && (fieldValue.length() > 0)) {
				field.set(entity,Character.valueOf(fieldValue.charAt(0)));
			}
		}
	}
}