SQLite-Java-Hibernate类似hibernate的数据库辅助工具

sqlite-annotation-convention and sqlite-java-tool

http://pan.baidu.com/s/1eQuwa34

最近一直在做毕业设计，之前用过Hibernate，发现Hibernate用起来真的很烦便。在android中数据库为sqlite，没有类似Hibernate的工具，使得开发过程中时不时的回去查看sqlite的sql语句的编写，非常影响开发效率。

偶然间在CSDN的博客中发现了一个用于android的类似Hibernate的工具。该工具lk_blog大大开发的一款专门适用于android sqlite的一款数据库操作工具，其原理是通过Annotation帮助程序员生成sqlite的SQL语句，并通过android中的SQLiteDataBaseHelper获取SQLiteDataBase然后执行工具生成的SQL语句，从而达到操作数据库的目的。

以上只是对AHibernate的一些简单的概述，只是讲述了AHibernate的基本原理，更详细的内容大家去看lk_blog的博客吧。http://blog.csdn.net/lk_blog/article/details/7455992

我在这里写文章当然是为了推广我写的sqlite工具啦。笔者在看了lk_blog大大写的工具后很感兴趣，研究AHibernate源码后恍然大悟，遂萌发了开发一个适用于多平台的数据库工具，这就是我独立开发的:

1. sqlite-annotation-convention帮助生成sql语句的注解插件；

2. sqlite-java-tool pc端（包括windows，linux，mac）下的sqlite数据库操作工具，该工具是基于sqlite-annotation-convention插件的，实现了许多常用的操作，你也可以直接编写sql代码，然后通过该工具执行。

下面我们进入正题，具体来讲解这两个插件的实现原理和使用方法。

1. sqlite-annotation-convention插件

   1. 实现原理：

在源码包中org.lion.java.sqlite.hibernate.annotation的这个包内包含了所有注解元素。 

1. 首先是表级别的注解@Table以及@Tables，这两个注解都是声明一个类为数据库表的注解。

@Table有两个属性一个是value，另外一个是primaryKeys。value是用来为表添加名称的属性，primaryKeys是@PrimaryKey的数组类型（@PrimaryKey的默认value属性就是主键对应的在类中的属性名称），primaryKeys是用来为表添加复合主键的属性，如果需要自增的主键我们就用不到这个属性了。

@Tables注解是声明某个类需要在数据库中创建多张表的注解，其默认属性value就是@Table的数组类型，你可以通过使用该注解将一个类声明为多个表，然后生成sql的帮助类会为你创建多张表。

1. 其次是行级别的注解@Id和@Column，这两个注解是用来声明类中的属性为数据库列的注解。

如果类中某一个属性被声明了@Id那么它将会作为唯一该表的唯一主键，并且自增；如果你想省事，同样我们也提供不加注解自动生成自增的主键，你只需要在类中声明一个整形的属性，并且其名称为“id”即可，SQLiteTableHelper助手类会根据你定义的名称以及注解为你生成自增的主键。

@Column中包含很多属性，诸如notnull，unique等属性，你可以根据你的需要使用这些属性，默认情况下这些属性都是无效的。

1. 使用方法

1.注解的使用方法

//你可以像这样没有注解class Model3 {private int id;private String value;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getValue() {return value;}public void setValue(String value) {this.value = value;}}//你同样可以使用基类，子类继承基类即可public class Parent {private String property;private int id;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getProperty() {return property;}public void setProperty(String property) {this.property = property;}}import org.lion.java.sqlite.hibernate.annotation.Table;@Tableclass Model2 extends Parent{private String value;private int id;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getValue() {return value;}public void setValue(String value) {this.value = value;}}//你还可以这样使用import org.lion.java.sqlite.hibernate.annotation.Column;import org.lion.java.sqlite.hibernate.annotation.ForeignKey;import org.lion.java.sqlite.hibernate.annotation.Id;import org.lion.java.sqlite.hibernate.annotation.Table;@Tableclass Model1 {@Idprivate int modelId;@Column( unique=true , notnull=true )private String name;@Column( check="age>18" )private int age;@Column( default_value="[e-mail]" )private String email;@Column( foreignkey=@ForeignKey( srcClass=Model2.class , column="id" , onInsert=true ) )private int model2Id;@Column( foreignkey=@ForeignKey( srcClass=Model3.class , column="id" ) )private int model3Id;public int getModelId() {return modelId;}public void setModelId(int modelId) {this.modelId = modelId;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public int getModel2Id() {return model2Id;}public void setModel2Id(int model2Id) {this.model2Id = model2Id;}public int getModel3Id() {return model3Id;}public void setModel3Id(int model3Id) {this.model3Id = model3Id;}}

2. SQL生成助手使用方法

//你可以这样使用，下面这个其实就是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();}}@Overridepublic 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;}@Overridepublic 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) {}}}}@Overridepublic void dropTables() {List<String> tables = session.dropTables();for (String sql : tables) {try {System.out.println( sql );statement.execute(sql);} catch (SQLException e) {e.printStackTrace();}}}@Overridepublic 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();}}@Overridepublic 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();}}@Overridepublic 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();}}@Overridepublic 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();}}@Overridepublic 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();}}@Overridepublic 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();}}@Overridepublic 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;}@Overridepublic 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;}@Overridepublic 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;}@Overridepublic 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();}}@Overridepublic 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();}}@Overridepublic 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;}@Overridepublic 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, entity, set, field.getName());}} else {setFieldValue(field, entity, set, 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)));}}}}

1. sqlite-java-tool