java sqlite 工具类_java代码实现sqlite db文件转可执行SQL语句 并下载

----java----

注：此代码根据个人情况使用

不适用于表数据过多的情况，毕竟是for循环，过千过万都会比较卡，这个是没办法的，

暂时没有好的方法，因为我项目数据不会太多，sqllite嘛，都是分开多个的

--------------1.工具类 SqlLiteUtils--------------

packagecom.tzh.hw.utils;

importcom.tzh.hw.moudle.SqlLiteField;

importorg.springframework.stereotype.Component;

importjava.sql.*;

importjava.util.ArrayList;

importjava.util.HashMap;

importjava.util.List;

/*** sqlLite2sql** @author xing.Li* @date 2020/08/04*/@Componentpublic classSqlLiteUtils{

private staticConnectionconn= null;

/*** sql lite连接** @paramserverPath服务器的路径* @return {@linkBoolean}*/privateBooleansqlLiteConnect(StringserverPath) {

try{

Class.forName("org.sqlite.JDBC");

//建立一个数据库名***.db的连接，如果不存在就在当前目录下创建之conn= DriverManager.getConnection("jdbc:sqlite://"+ serverPath);

return true;

} catch(Exceptione) {

return false;

}

}

/*** sql lite转可执行sql字符串** @paramserverPath服务器的路径* @return {@linkHashMap>}*/publicStringsqlLite2sqlString(StringserverPath) {

StringsqlStr = "";

String sql= "select name from sqlite_master where type='table' order byname;";

Boolean flag= sqlLiteConnect(serverPath);

if(flag&& conn!= null) {

Statementstat = null;

ResultSetrs = null;

try{

stat = conn.createStatement();

//查询数据rs = stat.executeQuery(sql);

//获取所有表名while(rs.next()) {

String tab= rs.getString("name");

String fieldSql= "PRAGMA table_info(["+ tab+ "]);";

String[] fi= {"name", "type", "notnull", "dflt_value", "pk"};

List fieldArr= getField(fieldSql, serverPath, fi);

StringtableSqls = "DROP TABLE IF EXISTS\""+ tab+ "\";\n"+

"CREATE TABLE\""+ tab+ "\"(\n";

for(SqlLiteField field: fieldArr) {

String isNull= "1".equals(field.getNotnull()) ? " NOT NULL": "";

String isPk= "1".equals(field.getPk()) ? " PRIMARY KEY AUTOINCREMENT": "";

tableSqls += "\""+ field.getName() + "\""+ field.getType() + isNull+ isPk+ ",\n";

}

tableSqls = tableSqls.substring(0, tableSqls.length() - 2);

tableSqls += "\n";

tableSqls += ");";

//先放建表语句sqlStr += tableSqls + "\n\n";

String paramInfo= getParam(tab, serverPath, fieldArr);

//再放insert语句if(!"".equals(paramInfo)) {

sqlStr += paramInfo;

//换下行---sqlStr += "\n\n";

}

}

} catch(Exceptione) {

try{

if(rs != null) {

rs.close();

}

} catch(SQLExceptionex) {

ex.printStackTrace();

}

} finally{

try{

if(rs != null) {

rs.close();

}

} catch(SQLExceptionex) {

ex.printStackTrace();

}

close(conn);

}

}

returnsqlStr;

}

/***获取字段** @paramsqlsql获取表名下的所有字段及信息* @paramserverPath服务器的路径* @paramparam参数--- sqllite字段对应的名称* @return {@linkList}*/privateList getField(Stringsql, StringserverPath, String[] param) {

List sqlLiteFields= newArrayList<>();

Boolean flag= sqlLiteConnect(serverPath);

if(flag&& conn!= null) {

Statementstat = null;

ResultSetrs = null;

try{

stat = conn.createStatement();

//查询数据rs = stat.executeQuery(sql);

//获取所有表字段while(rs.next()) {

SqlLiteField field= newSqlLiteField();

for(inti = 0; i < param.length; i++) {

field.setName(rs.getString(param[0]));

field.setType(rs.getString(param[1]));

field.setNotnull(rs.getString(param[2]));

field.setDflt_value(rs.getString(param[3]));

field.setPk(rs.getString(param[4]));

}

sqlLiteFields.add(field);

}

rs.close();

} catch(Exceptione) {

try{

if(rs != null) {

rs.close();

}

} catch(SQLExceptionex) {

ex.printStackTrace();

}

} finally{

try{

if(rs != null) {

rs.close();

}

} catch(SQLExceptionex) {

ex.printStackTrace();

}

close(conn);

}

}

returnsqlLiteFields;

}

/***获取该表名下所有信息的insert语句** @paramtableName表名* @paramserverPath服务器的路径* @paramfields表字段* @return {@linkString}*/privateStringgetParam(StringtableName, StringserverPath, List fields) {

StringparamInfo = "";

Boolean flag= sqlLiteConnect(serverPath);

if(flag&& conn!= null) {

Statementstat = null;

ResultSetrs = null;

try{

stat = conn.createStatement();

//查询数据rs = stat.executeQuery("select*from "+ tableName + ";");

//封装sql语句while(rs.next()) {

paramInfo += "INSERT INTO\""+ tableName + "\"VALUES (";

intsize= fields.size();

for(inti = 0; i < size; i++) {

String type= fields.get(i).getType();

String string= fields.get(i).getName();

if(type.startsWith("INTEGER")) {

paramInfo += ""+ rs.getString(string) + ", ";

} else{

paramInfo += "'"+ rs.getString(string) + "',";

}

}

intlen= paramInfo.length() - 1;

paramInfo = paramInfo.substring(0, len);

paramInfo += ");\n";

}

rs.close();

} catch(Exceptione) {

try{

if(rs != null) {

rs.close();

}

} catch(SQLExceptionex) {

ex.printStackTrace();

}

} finally{

try{

if(rs != null) {

rs.close();

}

} catch(SQLExceptionex) {

ex.printStackTrace();

}

close(conn);

}

}

returnparamInfo;

}

/*** //关闭连接** @paramconn连接*/private voidclose(Connectionconn) {

if(conn != null) {

try{

conn.close(); //结束数据库的连接} catch(SQLExceptione) {

e.printStackTrace();

}

}

}

}