- 浏览: 684993 次
- 性别:
- 来自: 上海
文章分类
- 全部博客 (254)
- java分布式应用架构 (22)
- SSH框架整合 (6)
- java web 学习笔记 (49)
- java 学习笔记 (56)
- struts 2 学习 (6)
- Hibernate学习 (10)
- spring 学习 (2)
- 客户端编程(javascript) (4)
- IDE使用 (13)
- 生命 人生 (6)
- 系统维护 (3)
- 技术篇 (10)
- MySql (2)
- J2ME (1)
- java网络编程 (4)
- 数据库 (5)
- C/C++ (8)
- Oracle (7)
- 软件测试 (0)
- 软件的安装和部署 (0)
- Java快讯 (1)
- swt (1)
- Flex (1)
- 软件工程 (1)
- PostgreSQL (1)
- sql server2000 (2)
- 嵌入式数据库sqlite (5)
- J2EE (1)
- XML (1)
- ibatis3(MyBatis) (6)
- Linux&Unix (1)
- velocity (1)
- 回报社会 (4)
- 软件项目管理 (3)
- android研究 (3)
- C# (2)
- Objective-C (1)
- 音乐 (0)
- webx (1)
- JMS (1)
- maven软件项目管理 (1)
- 分布式服务 (0)
- 云平台 (0)
- 分布式存储 (1)
- 分布式系统架构 (0)
- 移动互联网 (1)
- ZooKeeper (1)
最新评论
-
liyys:
楼主,可不可以发这个项目的源码工程出来分享一下,少了几个类。楼 ...
仿照Hibernate实现一个SQLite的ORM框架 -
liyys:
少了一些类的源码没有粘贴出来
仿照Hibernate实现一个SQLite的ORM框架 -
honglei0412:
我使用的是这种方式获取db文件的目录但是 URL p = Fi ...
使用sqlite注意事项 -
honglei0412:
大侠 能不能说明下DbFile您是怎么做的吗?
使用sqlite注意事项 -
ahack:
刚写完mapping才发现早就有人写好了。仔细一看还都是针对的 ...
仿照Hibernate实现一个SQLite的ORM框架
那么先来看看使用
实体对象
测试
1、先来看看session的实现
2、SQLite数据库定义以及打开数据库对象
3、由对象注解控制SQL生成
3、专门为SQLite操作定制的工具
5、实体对象注解
实体对象
package test; import java.io.Serializable; import org.liufei.sqlite.annotation.Column; import org.liufei.sqlite.annotation.Entity; import org.liufei.sqlite.metadata.KeyGenerator; @Entity(table = "userInfo", keyGenerator = KeyGenerator.UUID) public class User implements Serializable { /** * */ private static final long serialVersionUID = 1L; @Column(isPrimaryKey = true, name = "user_id", typeSQL = "varchar(255) not null") private String id; @Column(isPrimaryKey = false, name = "username", typeSQL = "varchar(50) not null") private String name; @Column(name = "password", typeSQL = "varchar(100) not null", isPrimaryKey = false) private String password; /** * @return the id */ public String getId() { return id; } /** * @param id * the id to set */ public void setId(String id) { this.id = id; } /** * @return the name */ public String getName() { return name; } /** * @param name * the name to set */ public void setName(String name) { this.name = name; } /** * @return the password */ public String getPassword() { return password; } /** * @param password * the password to set */ public void setPassword(String password) { this.password = password; } /* (non-Javadoc) * @see java.lang.Object#toString() */ @Override public String toString() { return id + ", " + name + ", " + password; } }
测试
package test; import java.util.List; import org.liufei.sqlite.Query; import org.liufei.sqlite.SQLiteDatabase; import org.liufei.sqlite.SQLiteException; import org.liufei.sqlite.SQLiteOpenHelper; import org.liufei.sqlite.Session; import org.liufei.sqlite.Transaction; /** * * @author 刘飞 * */ public class Test { public static void main(String[] args) throws Exception { // BEGIN() ; // SELECT("id, name, password") ; // FROM("userInfo") ; // WHERE("id = '1'") ; // AND() ; // WHERE("name='liufei'") ; // INSERT_INTO("user as u") ; // VALUES("u.id", "1") ; // VALUES("name", "liufei") ; // VALUES("password", "liufei1229") ; // UPDATE("user") ; // SET("name='liufei'"); // WHERE("id='1'") ; // SET("password='liufei'"); // WHERE("hh=''") ; // SELECT("id") ; // SELECT("name") ; // SELECT("password") ; // WHERE("id='1'") ; // FROM("userinfo") ; // // System.out.println(SQL()); test(); } public static void test() throws SQLiteException { SQLiteOpenHelper helper = new SQLiteOpenHelper(); SQLiteDatabase database = helper.addEntity(User.class) .getWritableDatabase(); // database.create(User.class); Class<?>[] ens = database.getEntities() ; System.out.println(ens.length); for (Class<?> class1 : ens) { System.out.println(class1.getName()); } User user = new User() ; user.setName("liufei11") ; user.setPassword("liufei122912") ; Session session = database.openSession() ; Transaction transaction = session.begin() ; transaction.begin() ; // for(int i = 0 ; i < 1000000000 ; i++) session.save(user) ; transaction.commit() ; user = session.load(User.class, "a198556605ce403fa275bd4fb05ab6b8") ; System.out.println(user); user = session.load(User.class, "a198556605ce403fa275bd4fb05ab6b8") ; System.out.println(user); user.setPassword("你好嘛") ; Transaction transactions = session.begin() ; transactions.begin() ; session.update(user) ; transactions.commit() ; Query query = session.createQuery("select * from userInfo where username='liufei11'") ; List<User> list = query.addEntity(User.class).list() ; for (User user2 : list) { System.out.println(user2); } System.out.println(query.addEntity(User.class).uniqueResult()); System.out.println("get entityName : " + session.get("userInfo", "a198556605ce403fa275bd4fb05ab6b8")); session.close() ; } }
1、先来看看session的实现
package org.liufei.sqlite; import java.io.Serializable; import java.sql.Connection; import org.liufei.sqlite.callback.ConnectionCallback; /** * * @author 刘飞 * */ public interface Session extends Serializable { /** * 开始一个事务, 当该session为非只读时。 * @return * @throws SQLiteException */ public Transaction begin() throws SQLiteException; /** * 获取当前session所持有的连接 * @return * @throws SQLiteException */ public Connection connection() throws SQLiteException; /** * 关闭当前session断开连接, 清理缓存并刷新未提交的事务。 * @throws SQLiteException */ public void close() throws SQLiteException; /** * 当前session所持有的连接是否打开。 * @return */ public boolean isConnected(); /** * 根据id获取相应实体对象。 * @param <T> * @param clazz * @param id * @return * @throws SQLiteException */ public <T> T load(Class<T> clazz, Serializable id) throws SQLiteException; /** * 根据表名称获取相应实体对象, 先从一级缓存中获取, 没有时再到数据库加载。 * @param <T> * @param entityName * @param id * @return * @throws SQLiteException */ public <T> T load(String entityName, Serializable id) throws SQLiteException; /** * 根据一个实例化但没有参数的对象获取相应实体对象, 先从一级缓存中获取, 没有时再到数据库加载。 * @param entity * @param id * @throws SQLiteException */ public void load(Object entity, Serializable id) throws SQLiteException; /** * 持久化保存对象到数据库。 * @param object * @return * @throws SQLiteException */ public void save(Object object) throws SQLiteException; /** * 持久化对象更新。 * @param object * @throws SQLiteException */ public void update(Object object) throws SQLiteException; /** * 根据主键或非空属性删除持久化对象。 * @param object * @throws SQLiteException */ public void delete(Object object) throws SQLiteException; /** * 根据主键删除持久化对象(只有一个字段作为主键时)。 * @param <T> * @param clazz * @param id * @throws SQLiteException */ public <T> void delete(Class<T> clazz, Serializable id) throws SQLiteException; /** * 获取数据库查询对象。 * @param queryString * @return * @throws SQLiteException */ public Query createQuery(String queryString) throws SQLiteException; /** * 清理缓存并持久化未持久化得缓存对象 */ public void clear(); /** * 根据ID从数据库加载实体对象。 * @param <T> * @param clazz * @param id * @return * @throws SQLiteException */ public <T> T get(Class<T> clazz, Serializable id) throws SQLiteException; /** * 根据表名称从数据库加载实体对象。 * @param <T> * @param entityName * @param id * @return * @throws SQLiteException */ public <T> T get(String entityName, Serializable id) throws SQLiteException; /** * 自定义查询。 * @param <T> * @param sql * @param callback * @return * @throws SQLiteException */ public <T> T query(String sql, ConnectionCallback<T> callback) throws SQLiteException ; /** * 该session是否时只读的。 * @return */ public boolean isReadOnly() ; }
package org.liufei.sqlite; import java.util.List; /** * * @author 刘飞 * */ public interface Query { public Query addEntity(Class<?> clazz); public String getQueryString(); public <T> List<T> list() throws SQLiteException; public <T> T uniqueResult() throws SQLiteException; public int executeUpdate() throws SQLiteException; }
package org.liufei.sqlite; /** * * @author 刘飞 * */ public interface Transaction { /** * Begin a new transaction. */ public void begin() throws SQLiteException; /** * Flush the associated <tt>Session</tt> and end the unit of work (unless * we are in {@link FlushMode#MANUAL}. * </p> * This method will commit the underlying transaction if and only * if the underlying transaction was initiated by this object. * * @throws SQLiteException */ public void commit() throws SQLiteException; /** * Force the underlying transaction to roll back. * * @throws SQLiteException */ public void rollback() throws SQLiteException; /** * Was this transaction rolled back or set to rollback only? * <p/> * This only accounts for actions initiated from this local transaction. * If, for example, the underlying transaction is forced to rollback via * some other means, this method still reports false because the rollback * was not initiated from here. * * @return boolean True if the transaction was rolled back via this * local transaction; false otherwise. * @throws SQLiteException */ public boolean wasRolledBack() throws SQLiteException; /** * Check if this transaction was successfully committed. * <p/> * This method could return <tt>false</tt> even after successful invocation * of {@link #commit}. As an example, JTA based strategies no-op on * {@link #commit} calls if they did not start the transaction; in that case, * they also report {@link #wasCommitted} as false. * * @return boolean True if the transaction was (unequivocally) committed * via this local transaction; false otherwise. * @throws SQLiteException */ public boolean wasCommitted() throws SQLiteException; /** * Is this transaction still active? * <p/> * Again, this only returns information in relation to the * local transaction, not the actual underlying transaction. * * @return boolean Treu if this local transaction is still active. */ public boolean isActive() throws SQLiteException; }
package org.liufei.sqlite.impl; import static org.liufei.sqlite.util.SqlBuilder.*; import java.io.Serializable; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.concurrent.ConcurrentHashMap; import org.apache.log4j.Logger; import org.liufei.sqlite.Query; import org.liufei.sqlite.SQLHandler; import org.liufei.sqlite.SQLiteDatabase; import org.liufei.sqlite.SQLiteException; import org.liufei.sqlite.Session; import org.liufei.sqlite.Transaction; import org.liufei.sqlite.annotation.Column; import org.liufei.sqlite.callback.ConnectionCallback; import org.liufei.sqlite.metadata.Table; import org.liufei.sqlite.util.Assert; import org.liufei.sqlite.util.JdbcUser; import org.liufei.sqlite.util.JdbcUtil; import org.liufei.sqlite.util.ObjectCreator; import org.liufei.sqlite.util.ReflectionUtils; /** * * @author 刘飞 * */ public class SessionImpl implements Session { /** * session的一级缓存。 */ private static final ConcurrentHashMap<String, Object> SESSION_CACHE = new ConcurrentHashMap<String, Object>() ; private static final Logger LOG = Logger.getLogger(SessionImpl.class) ; private ThreadLocal<Transaction> TRANSACTION_CACHE = new ThreadLocal<Transaction>() ; /** * 每一次更新是否自动提交, 当打开事务是设置为false, 在事务里提交。 */ private boolean autoCommit = true ; private static final long serialVersionUID = 1L; private SQLHandler sqlhandler ; private boolean readOnly ; private Connection connection ; private SQLiteDatabase database ; /** * @param sqlhandler * @param readOnly */ public SessionImpl(SQLHandler sqlhandler, boolean readOnly) { super(); this.sqlhandler = sqlhandler; this.readOnly = readOnly; this.database = this.sqlhandler.getDatabase() ; this.connection = this.database.connection(); this.clearCache() ; } /** * @return the readOnly */ public boolean isReadOnly() { return readOnly; } public void clear() { try { this.clearCache() ; Transaction transaction = TRANSACTION_CACHE.get() ; if(transaction != null && !transaction.wasCommitted()) { transaction.commit() ; } JdbcUtil.commit(connection); } catch (SQLException e) { LOG.warn("clear session error.", e) ; throw new RuntimeException("clear session error.", e) ; } catch (SQLiteException e) { LOG.warn("clear session error.", e) ; throw new RuntimeException("clear session error.", e) ; } } public void close() throws SQLiteException { try { this.clearCache() ; Transaction transaction = TRANSACTION_CACHE.get() ; if(transaction != null && !transaction.wasCommitted()) { transaction.commit() ; } JdbcUtil.release(null, null, connection, true) ; LOG.debug("close connection to sqlite in this session.") ; } catch (SQLException e) { LOG.warn("close connection to sqlite in this session error.", e) ; throw new SQLiteException("close connection to sqlite in this session error.", e) ; } } public Connection connection() throws SQLiteException { LOG.debug("load connection to sqlite from this session.") ; return this.connection ; } public Query createQuery(String queryString) throws SQLiteException { return new QueryImpl(queryString, connection); } public void delete(Object object) throws SQLiteException { try { String sql = this.sqlhandler.delete(object) ; LOG.debug("SQL : " + sql) ; this.execute(autoCommit, sql); } catch (SQLException e) { LOG.error("delete entity[" + object.getClass().getName() + "] error.", e) ; throw new SQLiteException("delete entity[" + object.getClass().getName() + "] error.", e) ; } } public <T> void delete(Class<T> clazz, Serializable id) throws SQLiteException { try { String sql = this.sqlhandler.delete(clazz, id) ; LOG.debug("SQL : " + sql) ; this.execute(autoCommit, sql); this.remove(this.key(clazz, id)) ; } catch (SQLException e) { LOG.error("delete entity[" + clazz.getName() + "] error.", e) ; throw new SQLiteException("delete entity[" + clazz.getName() + "] error.", e) ; } } public <T> T get(final Class<T> clazz, final Serializable id) throws SQLiteException { Table table = this.database.getTable(clazz) ; Assert.notNull(table) ; RESET() ; final Field[] fields = ReflectionUtils.getAllDeclaredFields(clazz) ; for (Field field : fields) { ReflectionUtils.makeAccessible(field) ; if(!ReflectionUtils.isPublicStaticFinal(field) && !ReflectionUtils.isFinal(field)) { Column column = SQLHandler.column(field) ; String name = column.name() ; SELECT(name) ; boolean isPrimaryKey = column.isPrimaryKey() ; if(isPrimaryKey) { WHERE(name + "=\"" + id + "\"") ; } } } FROM(table.getName()) ; final String sql = SQL() ; LOG.debug("SQL : " + sql) ; try { return JdbcUser.query(connection, false, new ConnectionCallback<T>(){ @SuppressWarnings("unchecked") public T doInSql(Connection k) throws SQLException { PreparedStatement pstmt = JdbcUtil.pstmt(k, sql) ; ResultSet rs = pstmt.executeQuery() ; if(rs.next()) { Object entity = ObjectCreator.create(clazz) ; for (Field field : fields) { ReflectionUtils.makeAccessible(field) ; if(!ReflectionUtils.isPublicStaticFinal(field) && !ReflectionUtils.isFinal(field)) { Column column = SQLHandler.column(field) ; String name = column.name() ; ReflectionUtils.setField(field, entity, rs.getObject(name)) ; } } String key = key(clazz, id) ; if(containsKey(key)) { remove(key) ; } put(key, entity) ; JdbcUtil.release(rs, pstmt, null, false) ; return (T) entity; } else { return null ; } }}) ; } catch (SQLException e) { LOG.error("get entity[" + clazz.getName() + "] error.", e) ; throw new SQLiteException("get entity[" + clazz.getName() + "] error.", e) ; } } @SuppressWarnings("unchecked") public <T> T get(String entityName, Serializable id) throws SQLiteException { return (T) this.get(this.database.getEntity(this.database.getTable(entityName)), id); } public boolean isConnected() { try { return !this.connection.isClosed(); } catch (SQLException e) { return false ; } } @SuppressWarnings("unchecked") public <T> T load(Class<T> clazz, Serializable id) throws SQLiteException { String key = this.key(clazz, id) ; if(this.containsKey(key)) { Object entity = this.get(key) ; if(entity != null) { return (T) entity ; } else { return this.get(clazz, id) ; } } else { return this.get(clazz, id) ; } } @SuppressWarnings("unchecked") public <T> T load(String entityName, Serializable id) throws SQLiteException { return (T) this.load(this.database.getEntity(this.database.getTable(entityName)), id); } public void load(Object entity, Serializable id) throws SQLiteException { entity = this.load(entity.getClass(), id) ; } public void save(Object object) throws SQLiteException { try { String sql = this.sqlhandler.insert(object) ; LOG.debug("SQL : " + sql) ; this.execute(autoCommit, sql); } catch (SQLException e) { LOG.error("save entity[" + object.getClass().getName() + "] error.", e) ; throw new SQLiteException("save entity[" + object.getClass().getName() + "] error.", e) ; } } public void update(Object object) throws SQLiteException { try { String sql = this.sqlhandler.update(object) ; LOG.debug("SQL : " + sql) ; this.execute(autoCommit, sql); } catch (SQLException e) { LOG.error("update entity[" + object.getClass().getName() + "] error.", e) ; throw new SQLiteException("update entity[" + object.getClass().getName() + "] error.", e) ; } } private void execute(boolean autoCommit, String sql) throws SQLException { if(autoCommit) JdbcUtil.executeSQLDML(connection, sql, false) ; else JdbcUtil.executeSQLDML(connection, sql) ; } public Transaction begin() throws SQLiteException { this.autoCommit = false ; try { JdbcUtil.setAutoCommit(connection, false) ; } catch (SQLException e) { LOG.error("open Transaction and close AutoCommit error.", e) ; throw new SQLiteException("open Transaction and close AutoCommit error.", e) ; } Transaction transaction = new TransactionImpl(this.connection); TRANSACTION_CACHE.set(transaction) ; return transaction ; } private String key(Class<?> clazz, Serializable id) { return clazz.getName() + "." + id ; } private void put(String key, Object value) { SESSION_CACHE.put(key, value) ; } private Object get(String key) { return SESSION_CACHE.get(key) ; } private Object remove(String key) { return SESSION_CACHE.remove(key) ; } private void clearCache() { SESSION_CACHE.clear() ; } private boolean containsKey(String key) { return SESSION_CACHE.containsKey(key) ; } public <T> T query(String sql, ConnectionCallback<T> callback) throws SQLiteException { try { return JdbcUser.query(connection, false, callback); } catch (SQLException e) { LOG.error("query for [" + sql + "] error.", e) ; throw new SQLiteException("query for [" + sql + "] error.", e) ; } } }
package org.liufei.sqlite.impl; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.apache.log4j.Logger; import org.liufei.sqlite.Query; import org.liufei.sqlite.SQLHandler; import org.liufei.sqlite.SQLiteException; import org.liufei.sqlite.annotation.Column; import org.liufei.sqlite.callback.ConnectionCallback; import org.liufei.sqlite.util.JdbcUser; import org.liufei.sqlite.util.JdbcUtil; import org.liufei.sqlite.util.ObjectCreator; import org.liufei.sqlite.util.ReflectionUtils; public class QueryImpl implements Query { private static final Logger LOG = Logger.getLogger(QueryImpl.class) ; private final Connection connection ; private String sql ; private Class<?> entityClass = null ; /** * @param sql */ public QueryImpl(String sql, Connection connection) { super(); this.sql = sql; this.connection = connection ; } public Query addEntity(Class<?> clazz) { this.entityClass = clazz ; return this; } public int executeUpdate() throws SQLiteException { try { return JdbcUtil.executeSQLDML(connection, sql, false); } catch (SQLException e) { LOG.warn("execute update in Query error.", e) ; throw new SQLiteException("execute update in Query error.", e) ; } } public String getQueryString() { return this.sql; } public <T> List<T> list() throws SQLiteException { this.notNull() ; try { LOG.debug("SQL : " + sql) ; return JdbcUser.query(connection, false, new ConnectionCallback<List<T>>(){ @SuppressWarnings("unchecked") public List<T> doInSql(Connection k) throws SQLException { PreparedStatement pstmt = JdbcUtil.pstmt(k, sql) ; ResultSet rs = pstmt.executeQuery() ; List<T> result = new ArrayList<T>() ; while(rs.next()) { Object entity = ObjectCreator.create(entityClass) ; final Field[] fields = ReflectionUtils.getAllDeclaredFields(entityClass) ; for (Field field : fields) { ReflectionUtils.makeAccessible(field) ; if(!ReflectionUtils.isPublicStaticFinal(field) && !ReflectionUtils.isFinal(field)) { Column column = SQLHandler.column(field) ; String name = column.name() ; ReflectionUtils.setField(field, entity, rs.getObject(name)) ; } } result.add(( T )entity) ; } JdbcUtil.release(rs, pstmt, null, false) ; return result; }}) ; } catch (SQLException e) { LOG.error("query list entity[" + entityClass.getName() + "] error.", e) ; throw new SQLiteException("query list entity[" + entityClass.getName() + "] error.", e) ; } } public <T> T uniqueResult() throws SQLiteException { this.notNull() ; try { LOG.debug("SQL : " + sql) ; return JdbcUser.query(connection, false, new ConnectionCallback<T>(){ @SuppressWarnings("unchecked") public T doInSql(Connection k) throws SQLException { PreparedStatement pstmt = JdbcUtil.pstmt(k, sql) ; ResultSet rs = pstmt.executeQuery() ; if(rs.next()) { Object entity = ObjectCreator.create(entityClass) ; final Field[] fields = ReflectionUtils.getAllDeclaredFields(entityClass) ; for (Field field : fields) { ReflectionUtils.makeAccessible(field) ; if(!ReflectionUtils.isPublicStaticFinal(field) && !ReflectionUtils.isFinal(field)) { Column column = SQLHandler.column(field) ; String name = column.name() ; ReflectionUtils.setField(field, entity, rs.getObject(name)) ; } } if(rs.next()) { JdbcUtil.release(rs, pstmt, null, false) ; throw new SQLException("no unique result.") ; } else { JdbcUtil.release(rs, pstmt, null, false) ; return (T) entity; } } else { JdbcUtil.release(rs, pstmt, null, false) ; return null ; } }}) ; } catch (SQLException e) { LOG.error("query unique entity[" + entityClass.getName() + "] error.", e) ; throw new SQLiteException("query unique entity[" + entityClass.getName() + "] error.", e) ; } } private void notNull() throws SQLiteException { if(this.entityClass == null) { throw new SQLiteException("this entity class is null.") ; } } }
package org.liufei.sqlite.impl; import java.sql.Connection; import java.sql.SQLException; import org.apache.log4j.Logger; import org.liufei.sqlite.SQLiteException; import org.liufei.sqlite.Transaction; import org.liufei.sqlite.util.JdbcUtil; public class TransactionImpl implements Transaction { private static final Logger LOG = Logger.getLogger(TransactionImpl.class) ; private Connection connection ; private boolean committed = false ; private boolean rolledBack = false ; /** * @param connection */ public TransactionImpl(Connection connection) { super(); this.connection = connection; } public void begin() throws SQLiteException { try { JdbcUtil.setAutoCommit(connection, false) ; } catch (SQLException e) { LOG.error("open Transaction error.", e) ; throw new SQLiteException("open Transaction error.", e) ; } } public void commit() throws SQLiteException { try { JdbcUtil.commit(connection) ; this.committed = true ; } catch (SQLException e) { LOG.error("commit Transaction error.", e) ; throw new SQLiteException("commit Transaction error.", e) ; } } public boolean isActive() throws SQLiteException { return true; } public void rollback() throws SQLiteException { try { JdbcUtil.rollback(connection) ; this.rolledBack = true ; } catch (SQLException e) { LOG.error("rollback Transaction error.", e) ; throw new SQLiteException("rollback Transaction error.", e) ; } } public boolean wasCommitted() throws SQLiteException { return this.committed; } public boolean wasRolledBack() throws SQLiteException { return this.rolledBack; } }
2、SQLite数据库定义以及打开数据库对象
package org.liufei.sqlite; import java.io.File; import java.sql.Connection; import java.sql.SQLException; import java.util.HashMap; import java.util.Map; import java.util.concurrent.locks.ReadWriteLock; import java.util.concurrent.locks.ReentrantReadWriteLock; import org.apache.log4j.Logger; import org.liufei.sqlite.callback.ReadException; import org.liufei.sqlite.callback.Readable; import org.liufei.sqlite.callback.Writable; import org.liufei.sqlite.callback.WriteException; import org.liufei.sqlite.impl.SessionImpl; import org.liufei.sqlite.metadata.Table; import org.liufei.sqlite.util.JdbcUtil; import org.liufei.sqlite.util.SQLiteUtil; /** * * @author 刘飞 * */ public class SQLiteDatabase { private static final Logger LOG = Logger.getLogger(SQLiteDatabase.class) ; /** * 缓存的SQLite连接 */ private static final ThreadLocal<Connection> CACHE_LOCAL_CONNECTION = new ThreadLocal<Connection>() ; private final SQLHandler sqlhandler ; private final File database; private Connection connection ; private final boolean readOnly ; private final Map<String, Table> tables = new HashMap<String, Table>(); private final Map<Class<?>, Table> entityTableMapping = new HashMap<Class<?>, Table>() ; /** * init database sqlite * @param database data file * @param connection connect to SQLite * @param readOnly * true enables read-only mode; false disables it */ public SQLiteDatabase(File database, Connection connection, boolean readOnly) { this.database = database; this.connection = connection ; this.readOnly = readOnly ; this.sqlhandler = new SQLHandler(this) ; CACHE_LOCAL_CONNECTION.set(this.connection) ; } public static void main(String[] args) { System.out.println("SQLiteDatabase.main()"); LOG.info("\nSQLite running>>>\n\tsee \n\t" + SQLiteDatabase.class.getName() + "\n\t" + SQLiteOpenHelper.class.getName()) ; System.out.println("SQLite running>>>\n\tsee \n\t" + SQLiteDatabase.class.getName() + "\n\t" + SQLiteOpenHelper.class.getName()); } /** * 打开数据库操作得session, 当数据库是只读时, 该session是不支持对数据库的更新操作的 * @return */ public Session openSession() { return new SessionImpl(this.sqlhandler, this.readOnly); } /** * 实体对应的表不存在时就主动创建。 */ public void create() { if(entityTableMapping.size() > 0) { Class<?>[] entities = this.getEntities() ; for (Class<?> e : entities) { this.create(e) ; } } } public void create(Class<?> entity) { try { String sql = this.sqlhandler.create(entity) ; LOG.debug("SQL : " + sql) ; JdbcUtil.executeSQLDML(this.connection, sql, false) ; } catch (SQLException e1) { LOG.error("create entity{" + entity.getName() + "} mapping{" + this.getTable(entity).getName() + "} table error.", e1) ; throw new RuntimeException("create entity{" + entity.getName() + "} mapping{" + this.getTable(entity).getName() + "} table error.", e1) ; } } /** * true enables read-only mode; false disables it * @return the readOnly */ public boolean isReadOnly() { return readOnly; } private Connection connect() { Connection conn = CACHE_LOCAL_CONNECTION.get() ; if(conn == null) { conn = SQLiteUtil.connectSQLite() ; CACHE_LOCAL_CONNECTION.set(conn) ; LOG.debug("connect to sqlite database [ " + this.getDatabase().getAbsolutePath() + " ] cache connection to local thread.") ; } try { if(conn.isClosed()) { conn = null ; conn = SQLiteUtil.connectSQLite() ; CACHE_LOCAL_CONNECTION.set(conn) ; LOG.debug("connect to sqlite database [ " + this.getDatabase().getAbsolutePath() + " ] cache connection to local thread.") ; } } catch (SQLException e) { LOG.error("get SQLite database connection error [data file : " + database.getAbsolutePath() + " ]", e) ; } return conn ; } /** * @return the connection */ public Connection connection() { return connect(); } /** * */ public void close() { try { JdbcUtil.release(null, null, this.connection(), true) ; } catch (SQLException e) { LOG.error("close database [data:" + this.getDatabase().getAbsolutePath() + ", readonly:" + this.isReadOnly() + "] error.", e) ; throw new RuntimeException("close database [data:" + this.getDatabase().getAbsolutePath() + ", readonly:" + this.isReadOnly() + "] error.", e) ; } } /** * @return the database */ public File getDatabase() { return database; } /** * * @param table * @param entity */ public void addTable(Table table, Class<?> entity) { tables.put(table.getName(), table); entityTableMapping.put(entity, table) ; } /** * * @param name * @return */ public Table getTable(String name) { return tables.get(name); } /** * * @param entity * @return */ public Table getTable(Class<?> entity) { return entityTableMapping.get(entity); } /** * * @param table * @return */ public Class<?> getEntity(Table table) { return table.getEntity() ; } /** * * @return */ public Class<?>[] getEntities() { return entityTableMapping.keySet().toArray(new Class<?>[entityTableMapping.size()]) ; } /** * * @return */ public String[] getTableNames() { return tables.keySet().toArray(new String[tables.size()]); } /** * read data from database * @param <T> * @param readable * @return */ public <T> T read(Readable<T> readable) { acquireReadLock(); try { return readable.read(this) ; } catch (ReadException e) { LOG.error("read data from database error.", e) ; throw new RuntimeException("read data from database error.", e) ; } finally { releaseReadLock(); } } /** * write data to database * @param <T> * @param writable * @return */ public <T> T write(Writable<T> writable) { if(this.isReadOnly()) { throw new RuntimeException("this database is readonly.") ; } acquireWriteLock(); try { return writable.write(this) ; } catch (WriteException e) { LOG.error("write data to database error.", e) ; throw new RuntimeException("write data to database error.", e) ; } finally { releaseWriteLock(); } } private ReadWriteLock readWriteLock = new ReentrantReadWriteLock(); private void acquireReadLock() { readWriteLock.readLock().lock(); } private void releaseReadLock() { readWriteLock.readLock().unlock(); } private void acquireWriteLock() { readWriteLock.writeLock().lock(); } private void releaseWriteLock() { readWriteLock.writeLock().unlock(); } /* (non-Javadoc) * @see java.lang.Object#hashCode() */ @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((database == null) ? 0 : database.hashCode()); result = prime * result + ((tables == null) ? 0 : tables.hashCode()); return result; } /* (non-Javadoc) * @see java.lang.Object#equals(java.lang.Object) */ @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (!(obj instanceof SQLiteDatabase)) return false; SQLiteDatabase other = (SQLiteDatabase) obj; if (database == null) { if (other.database != null) return false; } else if (!database.equals(other.database)) return false; if (tables == null) { if (other.tables != null) return false; } else if (!tables.equals(other.tables)) return false; return true; } }
package org.liufei.sqlite; import java.io.File; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.log4j.Logger; import org.liufei.sqlite.callback.ConnectionCallback; import org.liufei.sqlite.metadata.Column; import org.liufei.sqlite.metadata.Table; import org.liufei.sqlite.util.JdbcUser; import org.liufei.sqlite.util.JdbcUtil; import org.liufei.sqlite.util.ReflectionUtils; import org.liufei.sqlite.util.SQLiteUtil; /** * * @author 刘飞 * */ public class SQLiteOpenHelper { private static final Logger LOG = Logger.getLogger(SQLiteOpenHelper.class) ; /** * 缓存的SQLite连接 */ private static final ThreadLocal<Connection> CACHE_LOCAL_CONNECTION = new ThreadLocal<Connection>() ; private static final Map<String, Class<?>> TABLES_ENTITY_MAPPER = new HashMap<String, Class<?>>(); private final File data ; /** * @param data */ public SQLiteOpenHelper() { super(); this.data = SQLiteUtil.getDataFile(); } public SQLiteOpenHelper addEntity(Class<?>... entity) { if(entity != null) { if(entity.length > 0) { for (Class<?> e : entity) { LOG.debug("add entity class : " + e.getName()) ; TABLES_ENTITY_MAPPER.put(SQLHandler.table(e).table(), e) ; } } } return this ; } public SQLiteOpenHelper addEntity(List<Class<?>> entity) { if(entity != null) { if(entity.size() > 0) { for (Class<?> e : entity) { addEntity(e) ; } } } return this ; } private Connection connect() { Connection conn = CACHE_LOCAL_CONNECTION.get() ; if(conn == null) { conn = SQLiteUtil.connectSQLite() ; CACHE_LOCAL_CONNECTION.set(conn) ; } try { if(conn.isClosed()) { conn = null ; conn = SQLiteUtil.connectSQLite() ; CACHE_LOCAL_CONNECTION.set(conn) ; } } catch (SQLException e) { LOG.error("get SQLite database connection error [data file : " + data.getAbsolutePath() + " ]", e) ; } return conn ; } /** * * @return */ public SQLiteDatabase getWritableDatabase() { try { if(TABLES_ENTITY_MAPPER.size() > 0) return DatabaseFactory.newDatabase(connect(), data, false) ; else throw new RuntimeException("please add entity mappers before get writable database .") ; } catch (SQLException e) { LOG.error("get writable SQLite database error [data file : " + data.getAbsolutePath() + " ]", e) ; throw new RuntimeException("get writable SQLite database error [data file : " + data.getAbsolutePath() + " ]", e) ; } } /** * * @return */ public SQLiteDatabase getReadableDatabase() { try { if(TABLES_ENTITY_MAPPER.size() > 0) return DatabaseFactory.newDatabase(connect(), data, true) ; else throw new RuntimeException("please add entity mappers before get readable database .") ; } catch (SQLException e) { LOG.error("get readable SQLite database error [data file : " + data.getAbsolutePath() + " ]", e) ; throw new RuntimeException("get readable SQLite database error [data file : " + data.getAbsolutePath() + " ]", e) ; } } public void close() { try { JdbcUtil.release(null, null, CACHE_LOCAL_CONNECTION.get(), true) ; } catch (SQLException e) { LOG.error("close database [data:" + this.data.getAbsolutePath() + "] error.", e) ; throw new RuntimeException("close database [data:" + this.data.getAbsolutePath() + "] error.", e) ; } } /** * * @author 刘飞 * */ protected static final class DatabaseFactory { private static final String SELECT_FROM = "select * from "; private static final String ROOTPAGE = "rootpage"; private static final String TBL_NAME = "tbl_name"; private static final String SQL = "sql"; private static final String NAME = "name"; private static final String SELECT_FROM_SQLITE_MASTER_WHERE_TYPE_TABLE = "select * from sqlite_master where type='table'"; private DatabaseFactory() { } /** * * @param conn * @param data * @return * @throws SQLException */ public static SQLiteDatabase newDatabase(Connection conn, String data, final boolean readOnly) throws SQLException { return newDatabase(conn, new File(data), readOnly) ; } /** * * @param conn * @param data * @return * @throws SQLException */ public static SQLiteDatabase newDatabase(final Connection conn, final File data, final boolean readOnly) throws SQLException { SQLiteDatabase database = JdbcUser .query( conn, false, new ConnectionCallback<SQLiteDatabase>(){ public SQLiteDatabase doInSql(Connection k) throws SQLException { SQLiteDatabase database = new SQLiteDatabase(data, conn, readOnly) ; PreparedStatement pstmt = JdbcUtil.pstmt(k, SELECT_FROM_SQLITE_MASTER_WHERE_TYPE_TABLE) ; ResultSet rs = pstmt.executeQuery() ; while(rs.next()) { final String tableName = rs.getString(NAME) ; final Class<?> entity = TABLES_ENTITY_MAPPER.get(tableName) ; if(entity == null) { throw new RuntimeException("please mapper for table : " + tableName) ; } final String sql = rs.getString(SQL) ; final String tbl_name = rs.getString(TBL_NAME) ; final String rootpage = rs.getString(ROOTPAGE) ; Table table = JdbcUser .query( conn, false, new ConnectionCallback<Table>(){ public Table doInSql(Connection k) throws SQLException { PreparedStatement pstmt = JdbcUtil.pstmt(k, SELECT_FROM + tableName) ; ResultSet rs = pstmt.executeQuery() ; Table table = new Table( tableName, sql, rootpage, tbl_name, entity ) ; ResultSetMetaData metaData = rs.getMetaData() ; int count = metaData.getColumnCount() ; for(int i = 1 ; i <= count ; i++) { Column column = new Column( metaData.getColumnName(i), metaData.getColumnType(i), isPrimaryKey(metaData.getColumnName(i), entity), getFieldMapper(metaData.getColumnName(i), entity) ) ; table.addColumn(column) ; } JdbcUtil.release(rs, pstmt, null, false) ; return table; }} ) ; database.addTable(table, entity) ; } JdbcUtil.release(rs, pstmt, null, false) ; return database; }} ); return database ; } private static boolean isPrimaryKey(String columnName, Class<?> entity) { Field[] fields = ReflectionUtils.getAllDeclaredFields(entity) ; for (Field field : fields) { ReflectionUtils.makeAccessible(field) ; if(!ReflectionUtils.isPublicStaticFinal(field) && !ReflectionUtils.isFinal(field)) { org.liufei.sqlite.annotation.Column column = SQLHandler.column(field) ; if(column != null) { if(column.name().equals(columnName)) { return column.isPrimaryKey() ; } } } } return false ; } private static String getFieldMapper(String columnName, Class<?> entity) { Field[] fields = ReflectionUtils.getAllDeclaredFields(entity) ; for (Field field : fields) { ReflectionUtils.makeAccessible(field) ; if(!ReflectionUtils.isPublicStaticFinal(field) && !ReflectionUtils.isFinal(field)) { org.liufei.sqlite.annotation.Column column = SQLHandler.column(field) ; if(column != null) { if(column.name().equals(columnName)) { return field.getName() ; } } } } return columnName ; } } }
package org.liufei.sqlite.metadata; public class Column { private final String name; private final int type; private final boolean primaryKey ; private final String field ; /** * @param name * @param type * @param isPrimaryKey * @param field */ public Column(String name, int type, boolean primaryKey, String field) { super(); this.name = name; this.type = type; this.primaryKey = primaryKey; this.field = field; } /** * @return the isPrimaryKey */ public boolean isPrimaryKey() { return primaryKey; } /** * @return the field */ public String getField() { return field; } public String getName() { return name; } public int getType() { return type; } public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; final Column column = (Column) o; if (type != column.type) return false; if (name != null ? !name.equals(column.name) : column.name != null) return false; return true; } public int hashCode() { int result; result = (name != null ? name.hashCode() : 0); result = 29 * result + type; return result; } /* (non-Javadoc) * @see java.lang.Object#toString() */ @Override public String toString() { return this.getName(); } }
package org.liufei.sqlite.metadata; import java.util.HashMap; import java.util.Map; public class Table { private final String name; private final String sql ; private final String rootpage ; private final String tbl_name ; private final Map<String, Column> columns = new HashMap<String, Column>(); private final Class<?> entity ; /** * @param name * @param sql * @param rootpage * @param tbl_name * @param entity */ public Table(String name, String sql, String rootpage, String tbl_name, Class<?> entity) { super(); this.name = name; this.sql = sql; this.rootpage = rootpage; this.tbl_name = tbl_name; this.entity = entity; } /** * @return the entity */ public Class<?> getEntity() { return entity; } /** * @return the name */ public String getName() { return name; } /** * @return the sql */ public String getSql() { return sql; } /** * @return the rootpage */ public String getRootpage() { return rootpage; } /** * @return the tbl_name */ public String getTbl_name() { return tbl_name; } public void addColumn(Column col) { columns.put(col.getName(), col); } public Column getColumn(String name) { return columns.get(name); } public String[] getColumnNames() { return columns.keySet().toArray(new String[columns.size()]); } public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; final Table table = (Table) o; if (name != null ? !name.equals(table.name) : table.name != null) return false; return true; } public int hashCode() { return (name != null ? name.hashCode() : 0); } /* (non-Javadoc) * @see java.lang.Object#toString() */ @Override public String toString() { return this.getName(); } }
3、由对象注解控制SQL生成
package org.liufei.sqlite; import java.io.Serializable; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.List; import java.util.UUID; import org.apache.log4j.Logger; import org.liufei.sqlite.annotation.Column; import org.liufei.sqlite.annotation.Entity; import org.liufei.sqlite.metadata.KeyGenerator; import org.liufei.sqlite.metadata.Table; import org.liufei.sqlite.util.Assert; import org.liufei.sqlite.util.ReflectionUtils; import static org.liufei.sqlite.util.SqlBuilder.*; /** * sql 控制器 * @author 刘飞 * */ public class SQLHandler { private static final Logger LOG = Logger.getLogger(SQLHandler.class) ; private final SQLiteDatabase database ; /** * @param database */ public SQLHandler(SQLiteDatabase database) { super(); this.database = database; } /** * @return the database */ public SQLiteDatabase getDatabase() { return database; } public String create(Class<?> clazz) { StringBuffer sql = new StringBuffer() ; Entity entityAnno = table(clazz) ; String table = entityAnno.table() ; List<String> pks = new ArrayList<String>() ; sql.append("CREATE TABLE " + table + " ( ") ; Field[] fields = ReflectionUtils.getAllDeclaredFields(clazz) ; int length = fields.length ; for (int i = 0 ; i < length ; i++) { Field field = fields[i] ; ReflectionUtils.makeAccessible(field) ; if(!ReflectionUtils.isPublicStaticFinal(field) && !ReflectionUtils.isFinal(field)) { Column column = column(field) ; String name = column.name() ; boolean isPrimaryKey = column.isPrimaryKey() ; String type = column.typeSQL() ; if(isPrimaryKey) pks.add(name) ; if(i < length - 1) sql.append(" " + name + " " + type + ", ") ; else if(i == length - 1) sql.append(" " + name + " " + type + " ") ; } } int size = pks.size() ; if(size > 0) { sql.append(", PRIMARY KEY( ") ; for (int i = 0 ; i < size ; i++) { if(i < size - 1) sql.append(" " + pks.get(i) + ", ") ; else if(i == size - 1) sql.append(" " + pks.get(i) + " ") ; } sql.append(" )") ; } else { LOG.warn("the entity(" + clazz.getName() + " <-mapping for-> " + table + ") has no primary key.") ; } sql.append(" )") ; return sql.toString() ; } public String insert(Object entity) { Entity entityAnno = table(entity.getClass()) ; String table = entityAnno.table() ; RESET() ; INSERT_INTO(table) ; KeyGenerator keyGenerator = entityAnno.keyGenerator() ; Field[] fields = ReflectionUtils.getAllDeclaredFields(entity.getClass()) ; for (Field field : fields) { ReflectionUtils.makeAccessible(field) ; if(!ReflectionUtils.isPublicStaticFinal(field) && !ReflectionUtils.isFinal(field)) { Column column = column(field) ; String name = column.name() ; boolean isPrimaryKey = column.isPrimaryKey() ; if(isPrimaryKey) { if(keyGenerator.equals(KeyGenerator.UUID)) { Object pk_val = ReflectionUtils.getField(field, entity) ; if(pk_val == null) VALUES(name, "\"" + UUID.randomUUID().toString().replaceAll("-", "") + "\"") ; else VALUES(name, "\"" + pk_val + "\"") ; } } else { Object val = ReflectionUtils.getField(field, entity) ; if(val != null) VALUES(name, "\"" + val + "\"") ; } } } return SQL() ; } public String update(Object entity) { Table table = this.database.getTable(entity.getClass()) ; RESET() ; UPDATE(table.getName()) ; Field[] fields = ReflectionUtils.getAllDeclaredFields(entity.getClass()) ; for (Field field : fields) { ReflectionUtils.makeAccessible(field) ; if(!ReflectionUtils.isPublicStaticFinal(field) && !ReflectionUtils.isFinal(field)) { Column column = column(field) ; String name = column.name() ; boolean isPrimaryKey = column.isPrimaryKey() ; if(isPrimaryKey) { Object pk_val = ReflectionUtils.getField(field, entity) ; if(pk_val != null) WHERE(name + "=\"" + pk_val + "\"") ; else throw new RuntimeException("PRIMARY KEY ( " + entity.getClass().getName() + "." + field.getName() + " ) is null.") ; } else { Object val = ReflectionUtils.getField(field, entity) ; if(val != null) SET(name + "=\"" + val + "\"") ; } } } return SQL() ; } /** * 该表只有一个主键时。 * @param <T> * @param clazz * @param id * @return */ public <T> String delete(Class<T> clazz, Serializable id) { Table table = this.database.getTable(clazz) ; RESET() ; DELETE_FROM(table.getName()) ; String[] columns = table.getColumnNames() ; for (String c : columns) { org.liufei.sqlite.metadata.Column column = table.getColumn(c) ; if(column.isPrimaryKey()) { if(id != null) WHERE(column.getName() + "=\"" + id + "\"") ; } } return SQL() ; } public String delete(Object entity) { Table table = this.database.getTable(entity.getClass()) ; RESET() ; DELETE_FROM(table.getName()) ; Field[] fields = ReflectionUtils.getAllDeclaredFields(entity.getClass()) ; for (Field field : fields) { ReflectionUtils.makeAccessible(field) ; if(!ReflectionUtils.isPublicStaticFinal(field) && !ReflectionUtils.isFinal(field)) { Column column = column(field) ; String name = column.name() ; Object val = ReflectionUtils.getField(field, entity) ; if(val != null) WHERE(name + "=\"" + val + "\"") ; } } return SQL() ; } public String drop(Class<?> entity) { StringBuffer sql = new StringBuffer() ; if(entity != null) { Table table = this.database.getTable(entity) ; sql.append("DROP TABLE IF EXISTS " + table.getName()) ; } return sql.toString() ; } public String drop(Object entity) { StringBuffer sql = new StringBuffer() ; if(entity != null) { Table table = this.database.getTable(entity.getClass()) ; sql.append("DROP TABLE IF EXISTS " + table.getName()) ; } return sql.toString() ; } public static Entity table(Class<?> entity) { Assert.notNull(entity) ; Entity entityAnno = entity.getAnnotation(Entity.class) ; if(entityAnno != null) { return entityAnno ; } else { LOG.error("you need to add mapper for entity class : " + entity.getName()) ; throw new RuntimeException("you need to add mapper for entity class : " + entity.getName()) ; } } public static Column column(Field field) { Assert.notNull(field) ; ReflectionUtils.makeAccessible(field) ; Column column = field.getAnnotation(Column.class) ; if(column != null) { return column ; } else { LOG.error("you need to add mapper for field : " + field.getType().getName()) ; throw new RuntimeException("you need to add mapper for field : " + field.getType().getName()) ; } } }
3、专门为SQLite操作定制的工具
package org.liufei.sqlite.util; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Savepoint; import java.sql.Statement; import java.util.Properties; import org.apache.log4j.Logger; import org.liufei.sqlite.callback.ConnectionCallback; /** * jdbc操作工具。 * * @author 刘飞 * */ public class JdbcUtil { private static final Logger logger = Logger.getLogger(JdbcUtil.class); /** * 用于执行对数据库的查询。 * * @param conn * @param sql * @return * @throws SQLException */ public static ResultSet executeQuery(Connection conn, String sql) throws SQLException { if (conn != null && sql != null) { PreparedStatement pstmt = pstmt(conn, sql) ; ResultSet rs = pstmt.executeQuery() ; return rs; } else { logger .error("数据库连接对象和SQL参数不能为空 : java.sql.Connection[conn : null], java.lang.String[sql : null]."); throw new SQLException( "数据库连接对象和SQL参数不能为空 : java.sql.Connection[conn : null], java.lang.String[sql : null]."); } } /** * 没有事务控制 * @param <T> * @param conn * @param callback * @return * @throws SQLException */ public static <T> T executeSQL(final Connection conn, final ConnectionCallback<T> callback) throws SQLException { return callback.doInSql(conn); } /** * * @param <T> * @param conn * @param callback * @param isCloseConnection * 完成事物以后是否关闭连接。 * @return * @throws SQLException */ public static <T> T executeSQL(final Connection conn, final ConnectionCallback<T> callback, boolean isCloseConnection) throws SQLException { return (T) new JdbcTransaction<T>(conn, isCloseConnection) { @Override public T doInTransaction(Connection conn) throws SQLException { return callback.doInSql(conn); } }.doInTransaction(); } /** * 用于执行对数据库的修改更新和删除。没有事务控制 * * @param conn * @param sql * @return * @throws SQLException */ public static int executeSQLDML(Connection conn, final String sql) throws SQLException { if (conn != null && sql != null) { int result = 0; PreparedStatement pstmt = pstmt(conn, sql) ; result = pstmt.executeUpdate() ; release(null, pstmt, null, false) ; return result; } else { logger .error("数据库连接对象和SQL参数不能为空 : java.sql.Connection[conn : null], java.lang.String[sql : null]."); throw new SQLException( "数据库连接对象和SQL参数不能为空 : java.sql.Connection[conn : null], java.lang.String[sql : null]."); } } public static PreparedStatement pstmt(Connection conn, final String sql) throws SQLException { if (isSupportsResultSet_TYPE_SCROLL_INSENSITIVE_CONCUR_UPDATABLE(conn) && isSupportsResultSetType_TYPE_SCROLL_INSENSITIVE(conn)) { /** * 执行数据库更新 */ PreparedStatement pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE) ; return pstmt; } else { /** * 执行数据库更新 */ PreparedStatement pstmt = conn.prepareStatement(sql) ; return pstmt; } } /** * 用于执行对数据库的修改更新和删除。 * * @param conn * @param sql * @param isCloseConnection * 完成事物以后是否关闭连接。 * @return * @throws SQLException */ public static int executeSQLDML(Connection conn, final String sql, boolean isCloseConnection) throws SQLException { if (conn != null && sql != null) { JdbcTransaction<Integer> jdbcTransaction = new JdbcTransaction<Integer>( conn, isCloseConnection) { @Override public Integer doInTransaction(Connection conn) throws SQLException { int result = 0; PreparedStatement pstmt = pstmt(conn, sql) ; result = pstmt.executeUpdate() ; release(null, pstmt, null, false) ; return result; } }; return jdbcTransaction.doInTransaction(); } else { logger .error("数据库连接对象和SQL参数不能为空 : java.sql.Connection[conn : null], java.lang.String[sql : null]."); throw new SQLException( "数据库连接对象和SQL参数不能为空 : java.sql.Connection[conn : null], java.lang.String[sql : null]."); } } /** * PreparedStatement 执行批处理。 * * @param conn * @param batchSql * 批量更新SQL * @param parameters * 二维参数列表, 用于注入SQL中 * @param times * 每多少条执行一次更新 * @param isCloseConnection * 完成事物以后是否关闭连接。 * @return 返回此批处理共影响的数据条数。 * @throws SQLException */ public static int executePreparedStatementSQLBatch(Connection conn, final String batchSql, final Object[][] parameters, final int times, final boolean isCloseConnection) throws SQLException { if (conn != null && batchSql != null) { if (batchSql.length() > 0) { if (isSupportsBatchUpdates(conn)) { JdbcTransaction<Integer> jdbcTransaction = new JdbcTransaction<Integer>( conn, isCloseConnection) { @Override public Integer doInTransaction(Connection conn) throws SQLException { int result = 0; /** * 执行批量操作 */ PreparedStatement pstmt = conn .prepareStatement(batchSql); int i = 1; for (Object[] params : parameters) { int j = 1; for (Object param : params) { pstmt.setObject(j, param); j++; } if (i % times == 0) { int[] rs = pstmt.executeBatch(); for (int k : rs) { result += k; } } i++; } release(null, pstmt, null, false); return result; } }; return jdbcTransaction.doInTransaction(); } else { throw new RuntimeException( "this database does not supports batch updates."); } } else { return 0; } } else { logger .error("数据库连接对象和SQL参数不能为空 : java.sql.Connection[conn : null], java.lang.String[sql : null]."); throw new SQLException( "数据库连接对象和SQL参数不能为空 : java.sql.Connection[conn : null], java.lang.String[sql : null]."); } } /** * Statement执行批处理。 * * @param conn * @param batchSql * @param isCloseConnection * 完成事物以后是否关闭连接。 * @return 返回此批处理共影响的数据条数。 * @throws SQLException */ public static int executeStatementSQLBatch(Connection conn, final String[] batchSql, final int times, final boolean isCloseConnection) throws SQLException { if (conn != null && batchSql != null) { if (batchSql.length > 0) { if (isSupportsBatchUpdates(conn)) { JdbcTransaction<Integer> jdbcTransaction = new JdbcTransaction<Integer>( conn, isCloseConnection) { @Override public Integer doInTransaction(Connection conn) throws SQLException { int rs = 0; /** * 执行批量操作 */ Statement stmt = conn.createStatement(); int k = 0; for (String sql : batchSql) { if (sql.trim().length() > 0) { stmt.addBatch(sql); } if (k % times == 0) { int[] result = stmt.executeBatch(); for (int i : result) { rs += i; } } } release(null, stmt, null, false); return rs; } }; return jdbcTransaction.doInTransaction(); } else { throw new RuntimeException( "this database does not supports batch updates."); } } else { return 0; } } else { logger .error("数据库连接对象和SQL参数不能为空 : java.sql.Connection[conn : null], java.lang.String[sql : null]."); throw new SQLException( "数据库连接对象和SQL参数不能为空 : java.sql.Connection[conn : null], java.lang.String[sql : null]."); } } /** * 设置只读模式 * * @param connection * @param readOnly * @throws SQLException */ public static void setReadOnly(Connection connection, boolean readOnly) throws SQLException { connection.setReadOnly(readOnly); } /** * 释放数据库资源。 * * @param rs * @param stmt * @param conn * @param isCloseConnection * 是否关闭数据库连接。 * @throws SQLException */ public static void release(ResultSet rs, Statement stmt, Connection conn, boolean isCloseConnection) throws SQLException { if (rs != null) { rs.close(); rs = null; } if (stmt != null) { stmt.close(); stmt = null; } if (conn != null) { if (!conn.isClosed() && !conn.getAutoCommit()) { conn.rollback(); } if (isCloseConnection) { if(!conn.isClosed()) conn.close(); conn = null; } } } /** * * @param connection * @param autoCommit * @throws SQLException */ public static void setAutoCommit(Connection connection, boolean autoCommit) throws SQLException { connection.setAutoCommit(autoCommit); } /** * 是否支持ResultSet.TYPE_SCROLL_INSENSITIVE * * @param connection * @return * @throws SQLException */ public static boolean isSupportsResultSetType_TYPE_SCROLL_INSENSITIVE( Connection connection) throws SQLException { return connection.getMetaData().supportsResultSetType( ResultSet.TYPE_SCROLL_INSENSITIVE); } /** * 是否支持ResultSet.CONCUR_READ_ONLY * * @param connection * @return * @throws SQLException */ public static boolean isSupportsResultSet_TYPE_SCROLL_INSENSITIVE_CONCUR_READ_ONLY( Connection connection) throws SQLException { return connection.getMetaData().supportsResultSetConcurrency( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); } /** * 是否支持ResultSet.CONCUR_UPDATABLE * * @param connection * @return * @throws SQLException */ public static boolean isSupportsResultSet_TYPE_SCROLL_INSENSITIVE_CONCUR_UPDATABLE( Connection connection) throws SQLException { return connection.getMetaData().supportsResultSetConcurrency( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); } /** * 是否支持 ANSI92 SQL * * @param connection * @return * @throws SQLException */ public static boolean isSupportsANSI92FullSQL(Connection connection) throws SQLException { return connection.getMetaData().supportsANSI92FullSQL(); } /** * 是否支持SelectForUpdate * * @param connection * @return * @throws SQLException */ public static boolean isSupportsSelectForUpdate(Connection connection) throws SQLException { return connection.getMetaData().supportsSelectForUpdate(); } /** * 是否支持批量更新。 * * @param connection * @return * @throws SQLException */ public static boolean isSupportsBatchUpdates(Connection connection) throws SQLException { return connection.getMetaData().supportsBatchUpdates(); } /** * 是否支持事务保存点 * * @param connection * @return * @throws SQLException */ public static boolean isSupportsSavepoints(Connection connection) throws SQLException { return connection.getMetaData().supportsSavepoints(); } /** * 设置事务保存点 * * @param connection * @param name * @return * @throws SQLException */ public static Savepoint setSavePoint(Connection connection, String name) throws SQLException { if (isSupportsSavepoints(connection)) { return connection.setSavepoint(name); } else { throw new RuntimeException( "this database does not supports savepoints."); } } /** * 回滚到事务点 * * @param connection * @param savepoint * @throws SQLException */ public static void rollback(Connection connection, Savepoint savepoint) throws SQLException { if (savepoint == null) { connection.rollback(); } else { if (isSupportsSavepoints(connection)) connection.rollback(savepoint); else connection.rollback(); } } /** * 回滚到事务点 * * @param connection * @throws SQLException */ public static void rollback(Connection connection) throws SQLException { connection.rollback(); } /** * 提交事务 * * @param connection * @param savepoint */ public static void commit(Connection connection, Savepoint savepoint) throws SQLException { if (savepoint == null) { if (!connection.getAutoCommit()) { connection.commit(); } } else { if (isSupportsSavepoints(connection)) { if (!connection.getAutoCommit()) { connection.releaseSavepoint(savepoint); } } else { if (!connection.getAutoCommit()) { connection.commit(); } } } } /** * 提交事务 * * @param connection */ public static void commit(Connection connection) throws SQLException { if (!connection.getAutoCommit()) { connection.commit(); } } /** * Classpath下加载属性文件资源。 * * @param filePath * 文件路径(Classpath路径) * @return * @throws IOException */ public static Properties loadPropsClassLoader(String filePath) throws IOException { Properties props = new Properties(); InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream( filePath); props.load(in); in.close(); return props; } /** * Classpath下加载属性文件资源。 * * @param filePath * 文件路径(文件系统路径) * @return * @throws IOException */ public static Properties loadPropsSystem(String filePath) throws IOException { Properties props = new Properties(); InputStream in = new FileInputStream(new File(filePath)); props.load(in); in.close(); return props; } /** * SQLite3数据库连接 * * @param driver * @param url * @return * @throws SQLException * @throws ClassNotFoundException * @throws IllegalAccessException * @throws InstantiationException */ public static Connection connectSQLite(String driver, String url) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException { Class.forName(driver).newInstance(); return DriverManager.getConnection(url); } }
package org.liufei.sqlite.util; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import org.liufei.sqlite.callback.Callback; import org.liufei.sqlite.callback.ConnectionCallback; import org.liufei.sqlite.callback.ResultSetCallback; import org.liufei.sqlite.callback.SqlCallback; /** * * @author 刘飞 * */ public class JdbcUser { /** * 查询 * * @param <T> * @param conn * @param sql * @param callback * @param isCloseConnection * @return * @throws SQLException */ public static <T> T executeConnectionQueryCallback1(final Connection conn, final String sql, ResultSetCallback<T> callback, boolean isCloseConnection) throws SQLException { T t = new SqlCallback<T, ResultSet>() { public T doInSQLCallback(String sql, Callback<T, ResultSet> callback) throws SQLException { return callback.doInSql(JdbcUtil.executeQuery(conn, sql)); } }.doInSQLCallback(sql, callback); if(isCloseConnection) { JdbcUtil.release(null, null, conn, true) ; } return t; } /** * 执行更新 * * @param <T> * @param conn * @param isCloseConnection * @param callback * @return * @throws SQLException */ public static <T> T query(final Connection conn, boolean isCloseConnection, final ConnectionCallback<T> callback) throws SQLException { return callback.doInSql(conn); } /** * 执行更新 * * @param <T> * @param conn * @param isCloseConnection * @param callback * @return * @throws SQLException */ public static <T> T executeSQL(final Connection conn, boolean isCloseConnection, final ConnectionCallback<T> callback) throws SQLException { return new JdbcTransaction<T>(conn, isCloseConnection) { @Override public T doInTransaction(Connection conn) throws SQLException { return callback.doInSql(conn); } }.doInTransaction(); } }
package org.liufei.sqlite.util; import java.sql.Connection; import java.sql.SQLException; import org.apache.log4j.Logger; /** * JDBC事物工具。 * * @author 刘飞 * */ public abstract class JdbcTransaction<T> { private Logger logger = Logger.getLogger(JdbcTransaction.class); private Connection conn; private boolean isCloseConnection = false; /** * 自动控制的事物块。 * * @param conn * 数据库连接 * @param isCloseConnection * 完成事物以后是否关闭连接, 默认为false不关闭。 */ public JdbcTransaction(Connection conn, boolean isCloseConnection) { super(); this.conn = conn; this.isCloseConnection = isCloseConnection; } public final T doInTransaction() throws SQLException { T result = null; boolean autoCommit = false; if (this.conn != null) { try { /** * 保存Connection原始属性 */ autoCommit = this.conn.getAutoCommit(); /** * 在本代码快里设置Connection非自动提交 */ if (this.conn.getAutoCommit()) { this.conn.setAutoCommit(false); } this.conn.setAutoCommit(false); /** * 执行事务代码块 */ result = this.doInTransaction(this.conn); /** * 提交事务 */ this.conn.commit(); /** * 恢复Connection的原始属性, 以免对其他代码造成影响。 */ this.conn.setAutoCommit(autoCommit); } catch (SQLException e) { logger.error("事物代码块异常 ", e); try { this.conn.rollback(); } catch (SQLException e1) { logger.error("事物回滚时异常 ", e); throw new SQLException("事物回滚时异常 : " + e1.getLocalizedMessage()); } throw new SQLException("事物代码块异常 : " + e.getLocalizedMessage()); } finally { try { this.conn.setAutoCommit(autoCommit); } catch (SQLException e) { logger.error("恢复Connection自动提交属性异常 ", e); throw new SQLException("恢复Connection自动提交属性异常 : \n" + e.getLocalizedMessage()); } if (this.isCloseConnection) { try { this.conn.close(); this.conn = null; } catch (SQLException e) { logger.error("数据库连接关闭时 ", e); throw new SQLException("数据库连接关闭时 : " + e.getLocalizedMessage()); } } } } return result; } protected abstract T doInTransaction(Connection conn) throws SQLException; }
5、实体对象注解
package org.liufei.sqlite.annotation; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; import org.liufei.sqlite.metadata.KeyGenerator; @Target(value={ElementType.TYPE}) @Retention(RetentionPolicy.RUNTIME) public @interface Entity { /** * 实体对应的数据库表名称, 默认是该实体的类名称。 * @return */ public String table() ; /** * 主键策略 * @return */ public KeyGenerator keyGenerator() default KeyGenerator.UUID ; }
package org.liufei.sqlite.annotation; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; @Target(value = { ElementType.FIELD }) @Retention(RetentionPolicy.RUNTIME) public @interface Column { /** * 字段对应的数据库表字段名称, 默认是该字段的名称。 * @return */ public String name() ; /** * 该字段是否是主键。 * @return */ public boolean isPrimaryKey() default false ; /** * 字段的类型SQL, 简单起见这样定义, 避免了JDBC类型解析以及约束反射的负担, 一切从简性能优先。 * 如:varchar(64) not null * @return */ public String typeSQL() ; /** * 不在添加表之间的关联, 提升框架性能。 */ }
- sqlite-orm.jar (2.6 MB)
- 下载次数: 68
评论
3 楼
liyys
2017-07-06
楼主,可不可以发这个项目的源码工程出来分享一下,少了几个类。
楼主好人,好人一生平安。
楼主好人,好人一生平安。
2 楼
liyys
2017-07-06
少了一些类的源码没有粘贴出来
1 楼
ahack
2014-11-24
刚写完mapping才发现早就有人写好了。仔细一看还都是针对的sqlite,所以感谢楼主。
发表评论
-
Java编程中“为了性能”尽量要做到的一些地方
2012-09-14 10:33 12211. 尽量在合适的场合使 ... -
Java 5.0多线程编程
2012-08-17 19:16 1064概述 1:三个新加的多线程包 2:C ... -
Thread.setDaemon设置说明
2012-06-28 18:00 1150Thread.setDaemon的用法,经过学习以后了解: ... -
JVM运行时数据区
2012-03-07 13:15 1005JVM定义了若干个程序执行期间使用的数据区域。这个区域里的一些 ... -
使用ibatis防止sql注入
2011-12-19 19:59 4659为了防止SQL注入,iBatis模糊查询时也要避免使用$$来进 ... -
java的引用探讨
2011-12-18 16:03 1125Reference 是一个抽象类,而 SoftReferenc ... -
ClassLoader
2011-11-04 15:11 1192package org.liufei.neta.lib; ... -
java压缩文件
2011-09-20 11:09 1295package org.liufei.net.util; ... -
获取客户机IP地址
2011-09-07 15:04 1032package org.liufei.jweb; imp ... -
Java读取本地机器MAC地址
2011-09-07 15:01 1299package org.liufei.jweb; imp ... -
获取IP地址
2011-09-07 13:41 2419public String getIpAddrByReques ... -
用CSS来美化Java桌面--Javacss
2011-09-02 12:14 1328CSS可以用来修饰HTML网页。 但你有没有想过,使用CSS ... -
java管理windows进程
2011-08-29 17:34 1778package org.zzuli.xmsb; /** ... -
java html工具
2011-08-29 17:26 1084package org.liufei.jweb.util; ... -
java将汉字转化为全拼
2011-08-29 17:24 1233package org.liufei.jweb.util; ... -
开源项目SVN源码地址
2011-08-22 16:20 3552多优秀的开源项目已经提供SVN源码签出了,无论是解疑还是学习, ... -
XML解析
2011-08-22 09:58 10221、DOM解析XML <?xml version=&q ... -
jdbc操作大观园
2011-08-09 17:22 1385最近公司使用jdbc和mybatis比较多,于是自己试着写了一 ... -
Windows XP系统总命令集合
2011-08-05 14:08 1075Windows XP系统总命令集合 winver----- ... -
Java处理UTF-8带BOM的文本的读写
2011-08-01 11:28 2997什么是BOM BOM(byte-order mark),即字 ...
相关推荐
Sqlite ORM 是一个简单的C#类,对Sqlite的操作进行了封装,主要功能包括:表定义、生成,访问,更新等,其中,支持,多表的连接操作,语法类似Linq语法,使用非常方便,附加了使用说明文档。 例如,添加记录操作为...
一个简单的Android SQLite ORM框架不想花资源分的同学可以上我的github主页下载:https://github.com/chenyihan/Simple-SQLite-ORM-Android,因为要传到github,所以代码中的注释和doc文档都是英文的,对自己英文不...
1. **数据库连接管理**:ORM框架会提供一个连接管理机制,用于创建、打开、关闭SQLite3数据库。这通常涉及到初始化配置,如数据库文件路径、错误处理等。 2. **数据模型类**:开发者会定义一系列类,这些类与数据库...
本文将深入探讨一个适用于Android的轻量级SQLite ORM框架。 1. **SQLite基础知识** - 数据库概念:SQLite是一个关系型数据库,支持SQL语法。 - Android中的SQLite:Android SDK内置了SQLite,提供了...
总结来说,"安卓Android源码——安卓Android轻量级sqlite orm框架.rar"是一个关于如何在Android项目中高效、便捷地使用SQLite数据库的源码示例。通过学习和理解这个框架,开发者可以更好地掌握Android中的数据存储,...
ORM(Object-Relational Mapping)框架则是将数据库操作转化为对象操作的一种技术,简化了开发者对数据库的使用。在Android开发中,使用轻量级的SQLite ORM框架可以极大地提高开发效率和代码可维护性。以下是对...
在“Android轻量级sqlite orm框架”这个项目中,我们可以预见到的是一个简化版的ORM框架,可能包含以下关键组件: 1. **数据库配置**:定义数据库版本,初始化操作,以及如何升级或降级数据库。 2. **实体类**:...
综合以上分析,这个压缩包提供的内容可以帮助开发者了解和测试一个C#实现的SQLite ORM框架,包括基本的数据库操作如读写、创建表、删除数据以及处理不同数据类型的能力。通过这些测试用例,开发者可以更好地理解和...
例如,创建一个用户类(User)并添加属性如id、name和email,ORM框架会自动创建一个名为User的表,包含id、name和email三列。 接下来,通过调用ORM框架提供的方法,开发者可以实现对数据库的CRUD(Create、Read、...
在"Android轻量级sqlite orm框架.zip"中,可能包含了一个或多个ORM库的示例代码,如ActiveAndroid、GreenDao、SugarORM、ORMLite等。这些框架都有各自的优缺点,例如: 1. **ActiveAndroid**:提供了一套简单的API...
以GreenDAO为例,它是一个性能优秀的ORM框架,支持快速映射Java对象到SQLite表。 GreenDAO的使用步骤大致如下: 1. 添加依赖:在项目build.gradle文件中引入GreenDAO的库。 2. 定义数据模型:创建一个继承自Entity...
本资料包"Android高级应用源码-Android轻量级sqlite orm框架.rar"提供了这样一个轻量级的SQLite ORM框架,让我们来深入探讨一下这个框架及其背后的原理和使用方法。 首先,SQLite ORM框架的核心思想是将Java对象与...
这个压缩包"Android高级应用源码-Android轻量级sqlite orm框架.zip"很可能包含了一个适用于Android的轻量级SQLite ORM框架的示例代码,帮助开发者理解如何在Android应用中高效地使用SQLite。 首先,我们来深入理解...
标题中的"cpp-SQLiteORM用于现代C++的SQLite ORM库只有header"表明这是一个关于C++的SQLite对象关系映射(ORM)库,且该库仅包含头文件,这意味着开发者无需链接任何库文件,只需包含相应的头文件即可使用。ORM是一...
•支持面向对象,零SQL操作数据库 •支持模拟器版本和真机版本 •支持自定义SQL扩展 •支持1级缓存,万级数据可达到百倍速度获取 •支持c中基础类型映射,推荐使用NSNumber进行封装 •支持分页查询,排序 ...
本项目是一个关于Android轻量级SQLite ORM框架的毕业设计示例,旨在帮助学生理解如何在实际应用中高效地利用SQLite进行数据操作。ORM(对象关系映射)技术将数据库操作与业务逻辑代码分离,简化了数据库访问,使得...
这个压缩包“安卓开发-Android轻量级sqlite orm框架.zip.zip”很可能包含了一个用于简化Android SQLite数据库操作的ORM库。 Android中的SQLite数据库通常需要开发者手动编写SQL语句来创建表、插入、查询和更新数据...