import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
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.Statement;
public class SqliteDemo {
// 本地图片路径
private static final String srcPath = "D:/myImagines";
// db文件生成路径
private static final String desPath = "D:/";
public static void main(String[] args) {
try {
String rootRirPath = srcPath;
Class.forName("org.sqlite.JDBC");
/**1.创建/连接数据库文件,创建操作也可以通过sqlitespy这个工具完成,强烈安利此利器,可搜我的另一博客:正确打开db文件的方式,避免乱码和无意义内容*/
Connection connection = DriverManager.getConnection("jdbc:sqlite:"+desPath+"myTest.db");
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
// 2.创建表格或视图
ResultSet resultSet = null;
//-- 表:images
resultSet = statement.executeQuery("select * from sqlite_master where type = 'table' and name = 'images'");
if(!resultSet.next()){
statement.executeUpdate("CREATE TABLE images (tile_data BLOB, tile_id TEXT PRIMARY KEY);");
}
// 3.导入数据
PreparedStatement mapPs = connection.prepareStatement("insert into map (tile_type, zoom_level, tile_column, tile_row, tile_id) values (?,?,?,?,?);");
PreparedStatement imagePs = connection.prepareStatement("insert into images (tile_data, tile_id) values (?,?);");
// 根据文件目录找对应文件的过程略,以下为关键代码
/** 插入文件流,读取数据库文件流的速度明显优于按路径读取文件,且转移方便,适合数据量大、改动性小的文件存储。*/
try {
InputStream in = new FileInputStream(file);
imagePs.setBinaryStream(1, in,(int) file.length());
imagePs.setString(2, tileId);
imagePs.addBatch();
} catch (FileNotFoundException e1) {
e1.printStackTrace();
}
/** 可将多个写操作一并执行,加快写操作,相当于一次捆绑编辑,此方法返回值整型数组,其长度为操作成功的个数,其中一个写操作异常SQLiteException,接下来的写操作不执行,且在方法内异常,无返回值*/
imagePs.executeBatch();
// imagePs.clearBatch();
// 可加入事务操作,相当于一次捆绑保存
connection.commit();
// 原子有误,可加入自己的操作,如回退,相当于一次捆绑撤销
connection.rollback();
//4.读取图片
PreparedStatement ps1 = null;
ResultSet resultSet = null;
try{
String sql = "select tile_data from images";
ps1 = connection.prepareStatement(sql);
InputStream in = null;
resultSet = ps1.executeQuery();
if(resultSet.next())
in = resultSet.getBinaryStream(1);
fos.close();
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{
if(resultSet != null) resultSet.close();
if(ps1 != null) ps1.close();
if(connection !=null) connection.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Json+XML+Excel+DBFile相关的Jar包及关键操作代码:
https://download.csdn.net/download/haoranhaoshi/10889219