项目地址：https://github.com/xuanshuangchen/canna-cloud.git
sqlite操作的基础服务，封装基本的数据库连接，插入，批量插入，等操作。利用反射，实现对象与字段的映射，实现类ORM的功能。
提供了sqlite的log实现，将日志写入sqlite文件中
实现Logger接口接口，规范日志操作。
1、sqlite基本操作工具
public class SqliteDBUtils {
private SqliteDBUtils(){
//do nothing
}
public static String getDefaultDbName() {
return SqliteStaticConstants.DEFAULT_DATE_FORMAT.format(new Date());
}
public static Connection getConnection() throws Exception{
String dbName = getDefaultDbName();
return getConnection(dbName);
}
public static Connection getConnection(String dbName) throws Exception{
return getConnection(dbName, false);
}
public static Connection getConnection(String dbName, boolean autoCommit) throws Exception{
Class.forName(SqliteStaticConstants.SQLITE_DB_DRIVER);
String realDb = SqliteStaticConstants.getSqliteDbname(dbName);
Connection conn = DriverManager.getConnection( realDb );
conn.setAutoCommit(autoCommit);
return conn;
}
public static void createTable(Connection connection, String createTableSql) throws SQLException {
Statement stmt = null;
try {
stmt = connection.createStatement();
stmt.executeUpdate(createTableSql);
connection.commit();
} catch (Exception e) {
log.error("", e);
connection.rollback();
throw new SQLException(e);
}finally {
closeQuietly(stmt);
}
}
public static void createTable(String dbName, String createTableSql) throws SQLException {
Connection connection = null;
try {
connection = getConnection(dbName);
createTable(connection, createTableSql);
} catch (Exception e) {
log.error("", e);
}finally{
closeQuietly(connection);
}
}
public static Long insert(Connection connection, String sql, List<Object> params) throws SQLException {
PreparedStatement prepStmt = connection.prepareStatement(sql);
long result = 0;
ResultSet rs = null;
try{
prepStmt.setQueryTimeout(60);
int i = 1;
if(params != null){
for(Object param:params){
prepStmt.setObject(i, param);
i += 1;
}
}
result = prepStmt.executeUpdate();
connection.commit();
rs = prepStmt.getGeneratedKeys();
if(rs.next()){
return rs.getLong(1);
}
}catch(Exception e){
log.error("", e);
connection.rollback();
throw new SQLException(e);
}finally{
closeQuietly(rs);
closeQuietly(prepStmt);
}
return result;
}
public static void insertBatch(Connection connection, String sql, List<List<Object>> params) throws SQLException {
PreparedStatement prepStmt = connection.prepareStatement(sql);
ResultSet rs = null;
try{
prepStmt.setQueryTimeout(60);
if(params != null && !params.isEmpty()){
for(List<Object> paramItem : params){
if(paramItem != null && !paramItem.isEmpty()){
int i = 1;
for(Object param : paramItem){
prepStmt.setObject(i, param);
i += 1;
}
prepStmt.addBatch();
}
}
}
prepStmt.executeBatch();
connection.commit();
}catch(Exception e){
log.error("", e);
connection.rollback();
throw new SQLException(e);
}finally{
closeQuietly(rs);
closeQuietly(prepStmt);
}
}
private static void closeQuietly(AutoCloseable closeable) {
try {
if (closeable != null) {
closeable.close();
}
} catch (Exception ioe) {
// ignore
}
}
}
2、sqlite反射操作
public class SQLUtils {
public static String getInsertSql(String tableName, Set<String> columnList) throws InstantiationException, IllegalAccessException {
StringBuffer sb = new StringBuffer();
StringBuffer sb2 = new StringBuffer();
sb.append("insert into "+tableName+"(");
sb2.append("values (");
for(String key : columnList){
sb.append(toUnderlineName(key)+",");
sb2.append("?,");
}
String insertStr = sb.substring(0, sb.length() - 1) + ")";
String valueStr = sb2.substring(0, sb2.length() - 1) + ")";
return insertStr + valueStr;
}
/*
* 获取参数
*/
public static <T> List<List<Object>> getParams(List<T> value,Set<String> columnList) throws IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException{
List<List<Object>> list = new ArrayList<List<Object>>();
for(T item:value){
BeanMap valueMap = BeanMap.create(item);
List<Object> listo = new ArrayList<Object>();
for(String column:columnList){
Object o = valueMap.get(column);
listo.add(o);
}
list.add(listo);
}
return list;
}
public static <T> List<Object> getParams(T value,Set<String> columnList) throws IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException{
BeanMap valueMap = BeanMap.create(value);
List<Object> listo = new ArrayList<Object>();
for(String column:columnList){
Object o = valueMap.get(column);
listo.add(o);
}
return listo;
}
//两个相加
public static <T> T[] ArrayConcat(T[] first,T[] second){
return ArrayUtils.addAll(first, second);
}
public static String toUnderlineName(String s) {
if (s == null) {
return null;
} else {
StringBuilder sb = new StringBuilder();
boolean upperCase = false;
for(int i = 0; i < s.length(); ++i) {
char c = s.charAt(i);
boolean nextUpperCase = true;
if (i < s.length() - 1) {
nextUpperCase = Character.isUpperCase(s.charAt(i + 1));
}
if (i >= 0 && Character.isUpperCase(c)) {
if ((!upperCase || !nextUpperCase) && i > 0) {
sb.append("_");
}
upperCase = true;
} else {
upperCase = false;
}
sb.append(Character.toLowerCase(c));
}
return sb.toString();
}
}
public static String getTableName(String className){
if(StringUtils.isBlank(className)){
return "";
}
String prefix = "t";
StringBuilder sb = new StringBuilder();
sb.append(prefix);
for(int i = 0; i<className.length();i++){
Character c = className.charAt(i);
if(Character.isUpperCase(c)){
sb.append("_");
sb.append(Character.toLowerCase(c));
}else{
sb.append(c);
}
}
return sb.toString();
}
}3、日志封装实现
public class SqliteLogger implements Logger {
private LogSqliteService logSqliteService;
public static Logger getLogger(String logName){
SqliteLogger sqliteLogger = new SqliteLogger();
sqliteLogger.logSqliteService = LogSqliteService.getLogSqliteService(logName, SqliteStaticConstants.DEFAULT_DATE_FORMAT);
return sqliteLogger;
}
public static Logger getLogger(Class<?> clazz){
SqliteLogger sqliteLogger = new SqliteLogger();
sqliteLogger.logSqliteService = LogSqliteService.getLogSqliteService(clazz.getName(), SqliteStaticConstants.DEFAULT_DATE_FORMAT);
return sqliteLogger;
}
public static Logger getLogger(String logName, String sqliteDBPreName){
SqliteLogger sqliteLogger = new SqliteLogger();
sqliteLogger.logSqliteService = LogSqliteService.getLogSqliteService(logName, SqliteStaticConstants.DEFAULT_DATE_FORMAT, sqliteDBPreName);
return sqliteLogger;
}
public static Logger getLogger(Class<?> clazz, String sqliteDBPreName){
SqliteLogger sqliteLogger = new SqliteLogger();
sqliteLogger.logSqliteService = LogSqliteService.getLogSqliteService(clazz.getName(), SqliteStaticConstants.DEFAULT_DATE_FORMAT, sqliteDBPreName);
return sqliteLogger;
}
public static Logger getLogger(String logName, FastDateFormat createStrategy){
SqliteLogger sqliteLogger = new SqliteLogger();
sqliteLogger.logSqliteService = LogSqliteService.getLogSqliteService(logName, createStrategy);
return sqliteLogger;
}
public static Logger getLogger(Class<?> clazz, FastDateFormat createStrategy){
SqliteLogger sqliteLogger = new SqliteLogger();
sqliteLogger.logSqliteService = LogSqliteService.getLogSqliteService(clazz.getName(), createStrategy);
return sqliteLogger;
}
public static Logger getLogger(String logName, FastDateFormat createStrategy, String sqliteDBPreName){
SqliteLogger sqliteLogger = new SqliteLogger();
sqliteLogger.logSqliteService = LogSqliteService.getLogSqliteService(logName, createStrategy, sqliteDBPreName);
return sqliteLogger;
}
public static Logger getLogger(Class<?> clazz, FastDateFormat createStrategy, String sqliteDBPreName){
SqliteLogger sqliteLogger = new SqliteLogger();
sqliteLogger.logSqliteService = LogSqliteService.getLogSqliteService(clazz.getName(), createStrategy, sqliteDBPreName);
return sqliteLogger;
}
@Override
public String getName() {
return null;
}
@Override
public boolean isTraceEnabled() {
return false;
}
@Override
public void trace(String s) {
}
@Override
public void trace(String s, Object o) {
}
@Override
public void trace(String s, Object o, Object o1) {
}
@Override
public void trace(String s, Object... objects) {
}
@Override
public void trace(String s, Throwable throwable) {
}
@Override
public boolean isTraceEnabled(Marker marker) {
return false;
}
@Override
public void trace(Marker marker, String s) {
}
@Override
public void trace(Marker marker, String s, Object o) {
}
@Override
public void trace(Marker marker, String s, Object o, Object o1) {
}
@Override
public void trace(Marker marker, String s, Object... objects) {
}
@Override
public void trace(Marker marker, String s, Throwable throwable) {
}
@Override
public boolean isDebugEnabled() {
return false;
}
@Override
public void debug(String s) {
logSqliteService.infoQuiet(s, LogLevel.DEBUG);
}
@Override
public void debug(String s, Object o) {
String info = String.format(s, o);
logSqliteService.infoQuiet(info, LogLevel.DEBUG);
}
@Override
public void debug(String s, Object o, Object o1) {
String info = String.format(s, o, o1);
logSqliteService.infoQuiet(s, LogLevel.DEBUG);
}
@Override
public void debug(String s, Object... objects) {
String info = String.format(s, objects);
logSqliteService.infoQuiet(s, LogLevel.DEBUG);
}
@Override
public void debug(String s, Throwable throwable) {
String info = s + throwable.getMessage();
logSqliteService.infoQuiet(s, LogLevel.DEBUG);
}
@Override
public boolean isDebugEnabled(Marker marker) {
return false;
}
@Override
public void debug(Marker marker, String s) {
}
@Override
public void debug(Marker marker, String s, Object o) {
}
@Override
public void debug(Marker marker, String s, Object o, Object o1) {
}
@Override
public void debug(Marker marker, String s, Object... objects) {
}
@Override
public void debug(Marker marker, String s, Throwable throwable) {
}
@Override
public boolean isInfoEnabled() {
return false;
}
@Override
public void info(String s) {
logSqliteService.infoQuiet(s, LogLevel.INFO);
}
@Override
public void info(String s, Object o) {
String info = String.format(s, o);
logSqliteService.infoQuiet(s, LogLevel.INFO);
}
@Override
public void info(String s, Object o, Object o1) {
String info = String.format(s, o, o1);
logSqliteService.infoQuiet(s, LogLevel.INFO);
}
@Override
public void info(String s, Object... objects) {
String info = String.format(s, objects);
logSqliteService.infoQuiet(s, LogLevel.INFO);
}
@Override
public void info(String s, Throwable throwable) {
String info = s + throwable.getMessage();
logSqliteService.infoQuiet(s, LogLevel.INFO);
}
@Override
public boolean isInfoEnabled(Marker marker) {
return false;
}
@Override
public void info(Marker marker, String s) {
}
@Override
public void info(Marker marker, String s, Object o) {
}
@Override
public void info(Marker marker, String s, Object o, Object o1) {
}
@Override
public void info(Marker marker, String s, Object... objects) {
}
@Override
public void info(Marker marker, String s, Throwable throwable) {
}
@Override
public boolean isWarnEnabled() {
return false;
}
@Override
public void warn(String s) {
}
@Override
public void warn(String s, Object o) {
}
@Override
public void warn(String s, Object... objects) {
}
@Override
public void warn(String s, Object o, Object o1) {
}
@Override
public void warn(String s, Throwable throwable) {
}
@Override
public boolean isWarnEnabled(Marker marker) {
return false;
}
@Override
public void warn(Marker marker, String s) {
}
@Override
public void warn(Marker marker, String s, Object o) {
}
@Override
public void warn(Marker marker, String s, Object o, Object o1) {
}
@Override
public void warn(Marker marker, String s, Object... objects) {
}
@Override
public void warn(Marker marker, String s, Throwable throwable) {
}
@Override
public boolean isErrorEnabled() {
return false;
}
@Override
public void error(String s) {
}
@Override
public void error(String s, Object o) {
}
@Override
public void error(String s, Object o, Object o1) {
}
@Override
public void error(String s, Object... objects) {
}
@Override
public void error(String s, Throwable throwable) {
}
@Override
public boolean isErrorEnabled(Marker marker) {
return false;
}
@Override
public void error(Marker marker, String s) {
}
@Override
public void error(Marker marker, String s, Object o) {
}
@Override
public void error(Marker marker, String s, Object o, Object o1) {
}
@Override
public void error(Marker marker, String s, Object... objects) {
}
@Override
public void error(Marker marker, String s, Throwable throwable) {
}
}
