前言
SQLite 是一个软件库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限制。
支持 java 、python 等多种语言。
详细介绍可以看这里: https://www.runoob.com/sqlite/sqlite-java.html.
本文主要举例Sqlite 在java中的使用。概念性的东西就不说太多了。
官网英文好的同学可以看这个哟:https://www.sqlite.org/index.html
本文参考:https://www.yiibai.com/sqlite/java-with-sqlite.html
https://www.cnblogs.com/ngxianyu/archive/2013/06/08/3126543.html
下载jar
http://mvnrepository.com/artifact/org.xerial/sqlite-jdbc/3.18.0
或者
https://www.sqlite.org/download.html
Sqlite 在java中的使用
- 以一个 SqlliteUtils.java 带你简单使用 sqlite
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqliteUtils {
/**
* 如果有就返回连接,没有就创建数据库
*
* @param fileName
*/
public static Connection createNewDatabase(String databaseName) {
String url = "jdbc:sqlite:" + databaseName;
Connection conn = null;
try {
conn = DriverManager.getConnection(url);
conn.setAutoCommit(false);
if (conn != null) {
DatabaseMetaData meta = conn.getMetaData();
System.out.println("A new database has been created.");
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}
public static void createNewTable(Connection conn,String sql) {
try {
Statement stmt = conn.createStatement();
stmt.execute(sql);
System.out.println("Create table finished.");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
public static void insert( Connection conn ,String keywords, Integer channel_id,String text) {
String insertSql = "INSERT INTO RANKDATA(KEYWORDS, channel_id, text) VALUES(?,?,?)";
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(insertSql);
pstmt.setString(1, keywords);
pstmt.setDouble(2, channel_id);
pstmt.setString(3, text);
pstmt.executeUpdate();
} catch (SQLException e) {
System.out.println(e.getMessage());
}finally {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void insert( Connection conn ,String insertSql) {
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(insertSql);
pstmt.executeUpdate();
} catch (SQLException e) {
System.out.println(e.getMessage());
}finally {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void delete( Connection conn ,String deleteSql) {
try {
PreparedStatement pstmt = conn.prepareStatement(deleteSql);
pstmt.executeUpdate();
conn.commit();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
public static void update( Connection conn , String updateSql) {
try {
PreparedStatement pstmt = conn.prepareStatement(updateSql);
pstmt.executeUpdate();
conn.commit();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
public static void select( Connection conn ,String sql) {
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// loop through the result set
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" + rs.getString("KEYWORDS") + "\t" + rs.getString("text"));
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
public static void main(String[] args) {
String property = System.getProperty("user.dir");
System.out.println(property+"/create-testt-db.db");
Connection conn = createNewDatabase( property+"/create-db.db" );
//创建表,如果不存在的话、
String createTableSql = "CREATE TABLE IF NOT EXISTS RANKDATA " +
"(ID INTEGER PRIMARY KEY AUTOINCREMENT," +
" KEYWORDS CHAR(50) NOT NULL, " +
" CHANNEL_ID INTEGER NOT NULL, " +
" TEXT TEXT)";
createNewTable(conn, createTableSql);
insert(conn, "大家好", 1, "大家好");
String selectSql = "select * from RANKDATA where channel_id =1 ";
String selectSql1 = "select * from RANKDATA ";
// select(conn,selectSql);
select(conn,selectSql1);
}
}
- 使用连接池