欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

java 项目中通过JDBC 实现sqlite数据库的增删改查操作

程序员文章站 2022-07-03 08:01:52
...

关于java项目中sqlite数据库的使用案例少之又少,查阅资料 最多只是怎么连接 并没有对对象的操作..实现对其数据的增删给查操作,为此,结合JDBC技术写的通过JDBC访问操作sqlite数据库.不喜勿喷!!!

转载请注明出处------------------Annhoa

 DBConnection.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

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 ="jdbc:sqlite:test.db";
	 sqliteHelper = new DBConnection(dbString,"CREATE TABLE IF NOT EXISTS table0 "
    		+ "(id CHAR(20) PRIMARY KEY NOT NULL, filename CHAR(50) not null, "
    		+ "filesize LONG ,filepath CHAR(50), filesource CHAR(50))");
}

	public boolean save(FileBean fb) {
		String sql="insert into table0 values(?,?,?,?,?)";
		int row=DBConnection.executeUpdate(sql, new String[]{
			fb.getId(),
			fb.getFilename(),
			fb.getFilesize()+"",
			fb.getFilepath(),
			fb.getFilesource()
		});
		
		if(row>0){
			return true;
		}
		return false;
	}

	@SuppressWarnings("static-access")
	public boolean update(FileBean fb) {
		String sql="update table0 set id=?,filename=?,filesize=?,filepath=?,filesource=?";
		int row=sqliteHelper.executeUpdate(sql, new String[]{
				fb.getId(),
				fb.getFilename(),
				fb.getFilesize()+"",
				fb.getFilepath(),
				fb.getFilesource()
		});
		
		if(row>0){
			return true;
		}
		return false;
	}

	public boolean delById(String id) {
		String sql="delete from table0 where id=?";
		int row=sqliteHelper.executeUpdate(sql, new String[]{
				id
		});
		
		if(row>0){
			return true;
		}
		return false;
	}

	public FileBean findById(String id) {
		String sql="select * from table0 where id=?";
		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<FileBean> findAll(String filename) {
		String sql="select * from table0 where 1=1";
		
		if(filename!=null&&!filename.trim().equals("")){
			sql+="  and filename='"+filename+"'";
		}
		
		sql+=" order by id";
		rst=sqliteHelper.executeQuery(sql, null);
		List<FileBean> listfb=new ArrayList<FileBean>();
		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("3");
    	fb.setFilename("xxxxx.txt");
    	fb.setFilepath("C:/Users/Administrator/Desktop");
    	fb.setFilesize(323);
    	fb.setFilesource("");
		boolean flag = new FileBeanDBHelper().save(fb);
		System.out.println(flag);
		List<FileBean> list = new FileBeanDBHelper().findAll(null);System.out.println(list);
	}

}

 

FileBean.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 "FileBean{" +
                "id='" + id + '\'' +
                ", filesize=" + filesize +
                ", filename='" + filename + '\'' +
                ", filepath='" + filepath + '\'' +
                ", filesource='" + filesource + '\'' +
                '}';
    }

    public FileBean() {
    }


    public String toJsonString() {
        return "{\"id\":\"" + id + "\",\"filename\":\"" + filename + "\",\"filepath\":\"" + filepath + "\",\"filesize\":\"" + filesize + "\",\"filesource\":\"" + filesource + "\"}";
    }
}

 

相关标签: sqlite java