java中如何使用Sqlite数据库呢?

java使用sqllite数据库的实现思路

1.引入相应的依赖
<dependency>
	<groupId>org.xerial</groupId>
	<artifactId>sqlite-jdbc</artifactId>
	<version>3.27.2.1</version>
</dependency>

2.使用jdbc对数据库进行相应的操作

package com.java265;
 
import java.io.File;
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 TestSqlite {
  
	public static void main(String[] args) {
		try {
			File dbFolder = new File("./dbs");
			if(!dbFolder.exists()) {
				System.out.println("创建dbs文件夹："+dbFolder.mkdir());
			}
//			Class.forName("org.sqlite.JDBC");
			Connection conn = DriverManager.getConnection("jdbc:sqlite:dbs/xwf.db");//需要先创建文件夹dbs，数据库文件才会自动创建
			Statement state = conn.createStatement();
			
			//建表
			state.executeUpdate("drop table if exists books");
			state.executeUpdate("create table books(id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(255),author VARCHAR(100))");
			
			//插入数据
			state.executeUpdate("insert into books (name,author) values ('hello','world'),('abc','def'),('jdbc','sqlite')");
			state.close();
			
			//查询
			PreparedStatement pstate = conn.prepareStatement("select * from books");
			ResultSet resultSet = pstate.executeQuery();
			while(resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String author = resultSet.getString("author");
				System.out.println("id=" + id + " name=" + name + " author=" + author);
			}
			pstate.close();
			
			//修改
			PreparedStatement pstate2 = conn.prepareStatement("update books set author = ? where id = ?");
			pstate2.setString(1, "new author");
			pstate2.setInt(2, 3);
			int updateResult = pstate2.executeUpdate();
			System.out.println("updateResult = " + updateResult);
			pstate2 = conn.prepareStatement("select * from books where id = 3");
			ResultSet check = pstate2.executeQuery();
			while(check.next()) {
				System.out.println("----id:" + check.getInt(1) + " name:" + check.getString(2) + " author:" + check.getString(3));
			}
			pstate2.close();
			
			//删除
			PreparedStatement pstate3 = conn.prepareStatement("delete from books where id = ?");
			pstate3.setInt(1, 3);
			System.out.println("delete result : " + pstate3.executeUpdate());
			pstate3.close();
			
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} 	
	}
}

package com.java265;
 
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 TestSqlteMem {
  
	public static void main(String[] args) {
		try {
			Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
			Statement state = conn.createStatement();
			state.executeUpdate("create table students (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(100))");
			state.executeUpdate("insert into students (name) values ('Tom'),('Jerry'),('Jone')");
			state.close();
			
			PreparedStatement pstate = conn.prepareStatement("select * from students");
			ResultSet rset = pstate.executeQuery();
			while(rset.next()){
				System.out.println(rset.getInt(1)+" > "+rset.getString(2));
			}
			pstate.close();
			
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}