java sqlite Util 不依赖Spring

SQLite适合于嵌入式的工程，小巧轻便，几乎不占内存，也无需额外进程处理，特别适合一些移动端的存储；但是不支持分布式，不支持多线程写操作，百万级以上的数据量支持不好。实验环境：eclipse+MaventestDB.db放到了resource文件夹中，和application.properity同级如果是spring框架可以使用@Component注解初始化此类，并注入到服...

任庆　

823人浏览 · 2019-05-06 15:23:14

任庆　 · 2019-05-06 15:23:14 发布

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());
	}
}

CSDN学习社区

CSDN联合极客时间，共同打造面向开发者的精品内容学习社区，助力成长！

更多推荐