SQLite适合于嵌入式的工程,小巧轻便,几乎不占内存,也无需额外进程处理,特别适合一些移动端的存储;但是不支持分布式,不支持多线程写操作,百万级以上的数据量支持不好。
实验环境:
- eclipse+Maven
- testDB.db放到了resource文件夹中,和application.properity同级
- 如果是spring框架可以使用@Component注解初始化此类,并注入到服务层,下面给了例子
- 第二种方式采用了DB路径外提的方式,可以防止使用tomcat时deploy每次都会覆盖tomcat中真正db文件,tomcat中还是工程中都是指向一个dbpath
表结构:
链接:sqlite基础操作
导入jar包:
<!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.23.1</version>
</dependency>
工具类:
package com.nick.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayDeque;
import java.util.Deque;
/**
* sqlite util
* @author nickR
* @date 2019年5月6日 上午11:40:17
*/
public class SqliteUtil {
/**
* singleton
*/
private static SqliteUtil su;
/**
* connectionPool store connections
*/
private Deque<Connection> connectionPool = null;
/**
* init connectionPool
* @param poolSize
* @throws ClassNotFoundException
* @throws SQLException
*/
private SqliteUtil(Integer poolSize) throws ClassNotFoundException, SQLException {
// init pool container
if (null == poolSize || poolSize<=0) {
throw new RuntimeException("error input 'poolSize', it must be larger than 0.");
} else {
connectionPool = new ArrayDeque<Connection>(poolSize);
}
Class.forName("org.sqlite.JDBC");
for (int i = 0; i<poolSize; i++) {
// url is jdbc:protocl:dbpath
Connection c = DriverManager.getConnection("jdbc:sqlite:"+SqliteUtil.class.getClassLoader().getResource("").getPath().substring(1)+"testDB.db");
c.setAutoCommit(false);
connectionPool.add(c);
}
}
/**
* construction with poolSize
* @param poolSize
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public static SqliteUtil getInstance(Integer poolSize) throws ClassNotFoundException, SQLException {
if (null == su) {
su = new SqliteUtil(poolSize);
return su;
} else {
return su;
}
}
/**
* construction without poolSize
* default size is 5
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public static SqliteUtil getInstance() throws ClassNotFoundException, SQLException {
if (null == su) {
su = new SqliteUtil(5);
return su;
} else {
return su;
}
}
/**
* get connection
* @return
*/
private synchronized Connection getConn() {
return connectionPool.removeLast();
}
/**
* release connection
* @param c
*/
private void releaseConn(Connection c) {
connectionPool.addLast(c);
}
/**
* get statement
* @param con
* @param sql
* @return
*/
private Statement getStatement(Connection con){
try {
return con.createStatement();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
/**
* query
* @param sql
* @return
*/
public ResultSet select(String sql){
Connection c = this.getConn();
Statement stm = this.getStatement(c);
try {
return stm.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.releaseConn(c);
}
return null;
}
/**
* insert
* @param sql
* @return
*/
public Integer insert(String sql) {
Connection c = this.getConn();
Statement ps = this.getStatement(c);
try {
return ps.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
try {
c.commit();
} catch (SQLException e) {
e.printStackTrace();
}
this.releaseConn(c);
}
}
/**
* update
* @param sql
* @return
*/
public Integer update(String sql) {
return insert(sql);
}
/**
* delete
* @param sql
* @return
*/
public Integer delete(String sql) {
return insert(sql);
}
}
调用方式:
@RequestMapping(method = RequestMethod.GET, value = "/test")
public void test() throws UnsupportedEncodingException {
try {
SqliteUtil su = SqliteUtil.getInstance();
for (int i=0; i<10; i++) {
int id = i+3;
su.insert("insert into user_info values ("+id+", 'kevin"+i+"');");
su.update("update user_info set user_name = '"+"bee'"+" where user_id = '"+id+"';");
}
su.delete("delete from user_info where user_id = '1';");
ResultSet rs = su.select("select * from user_info");
while (rs.next()) {
String id = rs.getString("user_id");
String name = rs.getString("user_name");
System.out.println(id+"====="+name);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
注入方式:
package com.nick.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayDeque;
import java.util.Deque;
import javax.annotation.PostConstruct;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
/**
* sqlite util
* @author nickR
* @date 2019年5月6日 上午11:40:17
*/
@Component
public class SqliteUtil {
/**
* db file location
*/
@Value("${sqlite.db.path}")
private String dbPath;
/**
* connectionPool store connections
*/
private Deque<Connection> connectionPool = null;
/**
* init connectionPool
* @param poolSize
* @throws ClassNotFoundException
* @throws SQLException
*/
@PostConstruct
public void init() {
int poolSize = 5;
// init pool container
connectionPool = new ArrayDeque<Connection>(poolSize);
try {
Class.forName("org.sqlite.JDBC");
for (int i = 0; i<poolSize; i++) {
// url is jdbc:protocl:dbpath
Connection c = DriverManager.getConnection("jdbc:sqlite:"+dbPath);// SqliteUtil.class.getClassLoader().getResource("").getPath().substring(1)+"testDB.db"
c.setAutoCommit(false);
connectionPool.add(c);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* default constructor
*/
public SqliteUtil() {
}
/**
* get connection
* @return
*/
private synchronized Connection getConn() {
return connectionPool.removeLast();
}
/**
* release connection
* @param c
*/
private void releaseConn(Connection c) {
connectionPool.addLast(c);
}
/**
* get statement
* @param con
* @param sql
* @return
*/
private Statement getStatement(Connection con){
try {
return con.createStatement();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
/**
* query
* @param sql
* @return
*/
public ResultSet select(String sql){
Connection c = this.getConn();
Statement stm = this.getStatement(c);
try {
return stm.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.releaseConn(c);
}
return null;
}
/**
* insert
* @param sql
* @return
*/
public Integer insert(String sql) {
Connection c = this.getConn();
Statement ps = this.getStatement(c);
try {
return ps.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
try {
c.commit();
} catch (SQLException e) {
e.printStackTrace();
}
this.releaseConn(c);
}
}
/**
* update
* @param sql
* @return
*/
public Integer update(String sql) {
return insert(sql);
}
/**
* delete
* @param sql
* @return
*/
public Integer delete(String sql) {
return insert(sql);
}
}
application.propery
# db file path
sqlite.db.path=C:/Users/admin/Desktop/exercise/java/testDB.db
调用:
@Controller
public class TestController {
@Autowired
SqliteUtil sqliteUtil;
@RequestMapping(method = RequestMethod.GET, value = "/test")
public void testSendMsg() throws UnsupportedEncodingException {
try {
// for (int i=0; i<10; i++) {
// int id = i+3;
// su.insert("insert into user_info values ("+id+", 'kevin"+i+"');");
// su.update("update user_info set user_name = '"+"bee'"+" where user_id = '"+id+"';");
// }
// su.delete("delete from user_info where user_id = '1';");
ResultSet rs = sqliteUtil.select("select * from user_info");
while (rs.next()) {
String id = rs.getString("user_id");
String name = rs.getString("user_name");
System.out.println(id+"====="+name);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
LogInfo log = new LogInfo();
System.out.println(log.toString());
}
}