关于java项目中sqlite数据库的使用案例少之又少,查阅资料 最多只是怎么连接 并没有对对象的操作..实现对其数据的增删给查操作,为此,结合JDBC技术写的通过JDBC访问操作sqlite数据库.不喜勿喷!!!
转载请注明出处——————Annhoa
DBConnection.java
Java代码
package com.darkblue.dynem.util.sqlitedb;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import com.darkblue.dynem.util.filehelp.FileBean;
/**
* @author Annhoa
* @date 2017/07/26
* @since JDBC connection of sqlite
* @version 1.0
*
*/
public class DBConnection {
private static final Logger log = Logger.getLogger(DBConnection.class.toString());
private static Connection conn;
private static Statement stat;
private static PreparedStatement prep;
private static ResultSet rst;
/**
* 连接到数据库
* @param创建表sql
*/
public DBConnection(String jdbc,String sql){
try {
Class.forName(“org.sqlite.JDBC”);
cOnn=DriverManager.getConnection(jdbc);
stat=conn.createStatement();
log.info(“连接到Sqlite”);
//sql创建表语句
stat.executeUpdate(sql);
log.info(“创建Sqlite表”);
}catch (Exception e) {
e.printStackTrace();
}
}
/**
* 通用增删改
* */
public static int executeUpdate(String sql,Object[] obs){
try {
prep=conn.prepareStatement(sql);
if(obs!=null&&obs.length>0){
for (int i = 0; i < obs.length; i++) {
prep.setObject(i+1, obs[i]);
}
}
int row=prep.executeUpdate();
if(row>0){
// conn.commit(); //oracle数据库用到
return row;
}
}catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
/**
* 通用查询
* */
public static ResultSet executeQuery(String sql,Object[] obs){
try {
prep=conn.prepareStatement(sql);
if(obs!=null&&obs.length>0){
for (int i = 0; i < obs.length; i++) {
prep.setObject(i+1, obs[i]);
}
}
rst = prep.executeQuery();
}catch (SQLException e) {
e.printStackTrace();
}
return rst;
}
}
FileBeanDBHelper.java
Java代码
package com.darkblue.dynem.util.sqlitedb;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.darkblue.dynem.util.filehelp.FileBean;
public class FileBeanDBHelper {
private ResultSet rst=null;
private DBConnection sqliteHelper;
/**
* @author Annhoa
* @date 2017/07/26
* @since fileBean数据库操作
* @version 1.0
*
*/
//关闭还未处理
public FileBeanDBHelper(){
String dbString =&#8221;jdbc:sqlite:test.db&#8221;;
sqliteHelper =new DBConnection(dbString,&#8221;CREATE TABLE IF NOT EXISTS table0 &#8221;
+&#8221;(id CHAR(20) PRIMARY KEY NOT NULL, filename CHAR(50) not null, &#8221;
+&#8221;filesize LONG ,filepath CHAR(50), filesource CHAR(50))&#8221;);
}
public boolean save(FileBean fb) {
String sql=&#8221;insert into table0 values(?,?,?,?,?)&#8221;;
int row=DBConnection.executeUpdate(sql, new String[]{
fb.getId(),
fb.getFilename(),
fb.getFilesize()+&#8221;&#8221;,
fb.getFilepath(),
fb.getFilesource()
});
if(row>0){
return true;
}
return false;
}
@SuppressWarnings(&#8220;static-access&#8221;)
public boolean update(FileBean fb) {
String sql=&#8221;update table0 set id=?,filename=?,filesize=?,filepath=?,filesource=?&#8221;;
int row=sqliteHelper.executeUpdate(sql, new String[]{
fb.getId(),
fb.getFilename(),
fb.getFilesize()+&#8221;&#8221;,
fb.getFilepath(),
fb.getFilesource()
});
if(row>0){
return true;
}
return false;
}
public boolean delById(String id) {
String sql=&#8221;delete from table0 where id=?&#8221;;
int row=sqliteHelper.executeUpdate(sql, new String[]{
id
});
if(row>0){
return true;
}
return false;
}
public FileBean findById(String id) {
String sql=&#8221;select * from table0 where id=?&#8221;;
rst=sqliteHelper.executeQuery(sql,new String[]{id});
try {
if(rst.next()){
FileBean fb=new FileBean(rst.getString(1), rst.getString(2), rst.getLong(3), rst.getString(4), rst.getString(5));
return fb;
}
}catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public List findAll(String filename) {
String sql=&#8221;select * from table0 where 1=1&#8243;;
if(filename!=null&&!filename.trim().equals(&#8220;&#8221;)){
sql+=&#8221; and filename='&#8221;+filename+&#8221;&#8216;&#8221;;
}
sql+=&#8221; order by id&#8221;;
rst=sqliteHelper.executeQuery(sql,null);
List listfb=new ArrayList();
try {
while(rst.next()){
FileBean fb=new FileBean(rst.getString(1), rst.getString(2), rst.getLong(3), rst.getString(4), rst.getString(5));
listfb.add(fb);
}
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return listfb;
}
public static void main(String[] args) {
FileBean fb=new FileBean();
fb.setId(&#8220;3&#8221;);
fb.setFilename(&#8220;xxxxx.txt&#8221;);
fb.setFilepath(&#8220;C:/Users/Administrator/Desktop&#8221;);
fb.setFilesize(323);
fb.setFilesource(&#8220;&#8221;);
boolean flag = new FileBeanDBHelper().save(fb);
System.out.println(flag);
List list =new FileBeanDBHelper().findAll(null);System.out.println(list);
}
}
FileBean.java
Java代码
package com.darkblue.dynem.util.filehelp;
import java.io.Serializable;
/**
* 发送消息列表
* Created by Administrator on 2016/8/12.
*/
public class FileBean implements Serializable {
public String id;
public long filesize;//文件大小
public String filename;//文件名字
public String filepath;//文件路径
public String filesource;//文件分享源
//添加构造方法
public FileBean(String id,String filename,long filesize,String filepath,String filesource){
super();
this.id = id;
this.filename = filename;
this.filesize = filesize;
this.filepath = filepath;
this.filesource = filesource;
}
public String getFilesource() {
return filesource;
}
public void setFilesource(String filesource) {
this.filesource = filesource;
}
public long getFilesize() {
return filesize;
}
public void setFilesize(long filesize) {
this.filesize = filesize;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getFilename() {
return filename;
}
public void setFilename(String filename) {
this.filename = filename;
}
public String getFilepath() {
return filepath;
}
public void setFilepath(String filepath) {
this.filepath = filepath;
}
@Override
public String toString() {
return &#8220;FileBean{&#8221; +
&#8220;id='&#8221; + id + &#8216;\&#8221; +
&#8220;, filesize=&#8221; + filesize +
&#8220;, filename='&#8221; + filename + &#8216;\&#8221; +
&#8220;, filepath='&#8221; + filepath + &#8216;\&#8221; +
&#8220;, filesource='&#8221; + filesource + &#8216;\&#8221; +
&#8216;}&#8217;;
}
public FileBean() {
}
public String toJsonString() {
return &#8220;{\&#8221;id\&#8221;:\&#8221;&#8221; + id + &#8220;\&#8221;,\&#8221;filename\&#8221;:\&#8221;&#8221; + filename + &#8220;\&#8221;,\&#8221;filepath\&#8221;:\&#8221;&#8221; + filepath + &#8220;\&#8221;,\&#8221;filesize\&#8221;:\&#8221;&#8221; + filesize + &#8220;\&#8221;,\&#8221;filesource\&#8221;:\&#8221;&#8221; + filesource + &#8220;\&#8221;}&#8221;;
}
}