仿照Hibernate实现一个SQLite的ORM框架

yw6866

那么先来看看使用
实体对象
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 ;
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() ;
/**
* 不在添加表之间的关联, 提升框架性能。
*/
}