java 链接 sqlite数据库
本次采用的sqlite的包是sqlite-jdbc-3.6.0.jar
package com.***.app.mappcore.impl.util;
import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.ygsoft.ecp.app.mappcore.impl.ext.config.BaseConfig;
import com.ygsoft.ecp.service.log.EcpLogFactory;
import com.ygsoft.ecp.service.log.IEcpLog;
import com.ygsoft.ecp.service.tool.StringUtil;
public class SQLiteUtil {
/**
* 日志
*/
private static final IEcpLog LOG = EcpLogFactory.getLog(SQLiteUtil.class);
public SQLiteUtil(){
}
/**
* 内部
* 内部懒加载类.<br>
* @author mapengfei <br>
* @version 1.0.0 2016年5月31日<br>
* @see
* @since JDK 1.5.0
*/
private static class LazyHolder {
private static final SQLiteUtil INSTANCE = new SQLiteUtil();
}
/**
* 单例新实现
* @return
*/
public static final SQLiteUtil getInstance(){
return LazyHolder.INSTANCE;
}
public static void main(final String[] args) {
// try {
// Class.forName("org.sqlite.JDBC");
// Connection conn =
// DriverManager.getConnection("jdbc:sqlite:abc.db");
// 初始化线程池
// ConnectionPool pool = initPool();
// // 从连接池中取得链接
// Connection conn = pool.getCurrentConnecton();
// Statement stat = conn.createStatement();
// stat.executeUpdate("create table tbl1(name varchar(20), salary
// int);");// ?
// stat.executeUpdate("insert into tbl1 values('ZhangSan',8000);");
// //
// stat.executeUpdate("insert into tbl1 values('LiSi',7800);");
// stat.executeUpdate("insert into tbl1 values('WangWu',5800);");
// stat.executeUpdate("insert into tbl1 values('我',9100);");
// ResultSet rs = stat.executeQuery("select * from tbl1;"); //
// 初始化数据
SQLiteUtil util = new SQLiteUtil();
//util.initParametersTable();
for(int i=0;i<10;i++){
List<ParametesVO> list =util.findAll(true);
System.out.println(list.size()+"******************"+i);
}
// List<String> sqls =BaseConfig.getSQL();
// for(String sql: sqls){
//
// System.out.println(sql);
// }
// String rs = util.findParamValueByName("FTPPUFFERSIZE","FTP");
// System.out.println(rs);
// List<ParametesVO> list = util.findAll();
// System.out.println(list.get(0).getNo());
// ParametesVO model = new ParametesVO();
// model.setNo("100");
// model.setParamName("test1");
// model.setParamValue("002");
// model.setParamDescription("描述");
// model.setParamType("MPF");
// model.setParamStatus("0");
// add(model); // 添加
// String t = findByName("test1");
// System.out.println("add新添加的记录查询结果是:" + t);
// updateByNO("001",model);
// String t1 = findByName("FTPPUFFERSIZE");
// System.out.println("updateByNO修改的记录查询结果是:" + t1);
//
// util.delByNO("002");
// List<ParametesVO> list1 = util.findAll();
// System.out.println(list1.size());
// while (rs.next()) { //
//
// System.out.print("name = " + rs.getString("name") + " "); //
//
// System.out.println("salary = " + rs.getString("salary")); //
//
// }
// rs.close();
// closeConnection(conn); //
// } catch (Exception e) {
// e.printStackTrace();
// }
}
/**
* 删除表
* @param tableName
*/
public void dropParametersTable(final String tableName){
String sql =" DROP TABLE PARAMETERS_TABLE ";
Connection conn=null;
try {
// 取得SQL查询结果声明
conn = getCurrentConnecton();
Statement stat = conn.createStatement();
// 创建参数表
stat.executeUpdate(sql);
stat.close();
} catch (SQLException e) {
if(LOG.isDebugEnabled()){
LOG.info(" 初始化参数表有误。");
}
e.printStackTrace();
}finally {
closeConnection(conn);
}
}
/**
* 初始化参数表
*/
public void initParametersTable() {
final String initTableSql = " CREATE TABLE PARAMETERS_TABLE(NO VARCHAR(36), PARAMENAME VARCHAR(36),PARAMEVALUE VARCHAR(36),PARAMESTATUS VARCHAR(4),PARAMETYPE VARCHAR(12),PARAMEDESCRIPTION VARCHAR(2000))";
Connection conn=null;
try {
// 取得SQL查询结果声明
conn = getCurrentConnecton();
Statement stat = conn.createStatement();
// 创建参数表
stat.executeUpdate(initTableSql);
// 初始化参数表数据
List<String> sqls =BaseConfig.getSQL();
if(sqls!=null){
for(String sql : sqls){
if(StringUtil.isNotEmptyString(sql)){
stat.executeUpdate(sql);
}
}
}
stat.close();
} catch (SQLException e) {
if(LOG.isDebugEnabled()){
LOG.info(" 初始化参数表有误。");
}
e.printStackTrace();
}finally {
closeConnection(conn);
}
}
/**
* 专供java内部调用
* @param key 参数名
* @param type 参数类型
* @return 参数值
*/
public static String get(final String key,final String type){
return SQLiteUtil.getInstance().findParamValueByName(key,type);
}
/**
* 根据参数名称查询参数值
*
* @param parameName
* 参数名称
* @return 参数值
*/
public String findParamValueByName(final String parameName , final String paramType) {
String parameValue = null;
Connection conn=null;
try {
// 取得SQL查询结果声明
conn = getCurrentConnecton();
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("SELECT * FROM PARAMETERS_TABLE WHERE PARAMENAME='" + parameName + "' AND PARAMETYPE='"+paramType+"'");
while (rs.next()) { // 取得链接
parameValue = rs.getString(3);
}
rs.close();
stat.close();
} catch (SQLException e) {
if(LOG.isDebugEnabled()){
LOG.info("获取查询配置结果有误");
}
e.printStackTrace();
}finally {
closeConnection(conn);
}
return parameValue;
}
/**
* 根据parameName查询相应的配置参数信息
* @param parameName 参数名称
* @return 配置参数VO
*/
public List<ParametesVO> findByName(final String parameName){
List<ParametesVO> list = new ArrayList<ParametesVO>();
Connection conn=null;
try {
// 取得SQL查询结果声明
conn = getCurrentConnecton();
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("SELECT * FROM PARAMETERS_TABLE WHERE PARAMENAME='" + parameName + "'");
while (rs.next()) { // 取得链接
ParametesVO vo = new ParametesVO();
vo.setNo(rs.getString(1));
vo.setParamName(rs.getString(2));
vo.setParamValue(rs.getString(3));
vo.setParamStatus(rs.getString(4));
vo.setParamDescription(rs.getString(5));
list.add(vo);
}
rs.close();
stat.close();
} catch (SQLException e) {
if(LOG.isDebugEnabled()){
LOG.info("findALL查询配置参数列表有误");
}
e.printStackTrace();
}finally {
closeConnection(conn);
}
return list;
}
/**
* 根据parameType查询系统配置参数信息
* @param parameType
* @return
*/
public List<ParametesVO> findByParameteType(final String parameType){
List<ParametesVO> list = new ArrayList<ParametesVO>();
Connection conn=null;
try {
// 取得SQL查询结果声明
conn = getCurrentConnecton();
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select * from PARAMETERS_TABLE WHERE PARAMETYPE='" + parameType + "'");
while (rs.next()) { // 取得链接
ParametesVO vo = new ParametesVO();
vo.setNo(rs.getString(1));
vo.setParamName(rs.getString(2));
vo.setParamValue(rs.getString(3));
vo.setParamStatus(rs.getString(4));
vo.setParamType(rs.getString(5));
vo.setParamDescription(rs.getString(6));
list.add(vo);
}
rs.close();
stat.close();
} catch (SQLException e) {
if(LOG.isDebugEnabled()){
LOG.info("findALL查询配置参数列表有误");
}
e.printStackTrace();
}finally {
closeConnection(conn);
}
return list;
}
/**
* 根究NO修改
*
* @param NO
* @param model
*/
public void updateByNO(final String NO, final ParametesVO model) {
// 取得SQL查询结果声明
String update_sql = " UPDATE PARAMETERS_TABLE SET PARAMENAME = '" + model.getParamName() + "',PARAMEVALUE='"
+ model.getParamValue() + "',PARAMESTATUS='" + model.getParamStatus() + "',PARAMETYPE='"
+ model.getParamType() + "',PARAMEDESCRIPTION='" + model.getParamDescription() + "' WHERE NO = '" + NO
+ "'";
if(LOG.isDebugEnabled()){
LOG.info("updateByNO de sql:" + update_sql);
}
Connection conn = null;
try {
conn = getCurrentConnecton();
Statement stat = conn.createStatement();
stat.executeUpdate(update_sql);
stat.close();
} catch (SQLException e) {
if(LOG.isDebugEnabled()){
LOG.info("根据" + NO + "修改PARAMETERS_TABLE对应记录的sql:" + update_sql);
}
e.printStackTrace();
}finally {
closeConnection(conn);
}
}
/**
* 添加配置信息
*
* @param model
*/
public void add(final ParametesVO model) {
Connection conn=null;
try {
conn = getCurrentConnecton();
Statement stat = conn.createStatement();
String add_sql = " INSERT INTO PARAMETERS_TABLE VALUES('" + model.getNo() + "','" + model.getParamName()
+ "','" + model.getParamValue() + "','" + model.getParamStatus() + "','" + model.getParamType()
+ "','" + model.getParamDescription() + "')";
stat.executeUpdate(add_sql);
if(LOG.isDebugEnabled()){
LOG.info("添加配置信息的sql:" + add_sql);
}
stat.close();
} catch (SQLException e) {
if(LOG.isDebugEnabled()){
LOG.info("添加时");
}
e.printStackTrace();
}finally {
closeConnection(conn);
}
}
/**
* 根据 NO查询参数信息
* @param NO
* @return
*/
public ParametesVO findByNO(final String NO){
ParametesVO vo=null;
Connection conn=null;
try {
// 取得SQL查询结果声明
conn = getCurrentConnecton();
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(" SELECT * FROM PARAMETERS_TABLE WHERE NO = '" + NO + "'");
while (rs.next()) { // 取得链接
vo = new ParametesVO();
vo.setNo(rs.getString(1));
vo.setParamName(rs.getString(2));
vo.setParamValue(rs.getString(3));
vo.setParamStatus(rs.getString(4));
vo.setParamType(rs.getString(5));
vo.setParamDescription(rs.getString(6));
}
rs.close();
stat.close();
} catch (SQLException e) {
if(LOG.isDebugEnabled()){
LOG.info("findALL查询配置参数列表有误");
}
e.printStackTrace();
}finally {
closeConnection(conn);
}
return vo;
}
/**
* 根据NO删除PARAMETERS_TABLE表中的信息
*
* @param NO
*/
public void delByNO(final String no) {
Connection conn=null;
try {
conn = getCurrentConnecton();
Statement stat = conn.createStatement();
stat.executeUpdate("DELETE FROM PARAMETERS_TABLE WHERE NO = '" + no + "'");
stat.close();
} catch (SQLException e) {
if(LOG.isDebugEnabled()){
LOG.info("删除失败,NO:" + no);
}
e.printStackTrace();
}finally {
closeConnection(conn);
}
}
/**
* 根究NO修改
*
* @param NO
* @param model
*/
public void updateByNameAndType(final String paramname,final String paramtype, final ParametesVO model) {
// 取得SQL查询结果声明
String update_sql = " UPDATE PARAMETERS_TABLE SET PARAMENAME = '" + model.getParamName() + "',PARAMEVALUE='"
+ model.getParamValue() + "',PARAMESTATUS='" + model.getParamStatus() + "',PARAMETYPE='"
+ model.getParamType() + "',PARAMEDESCRIPTION='" + model.getParamDescription() + "' WHERE PARAMENAME = '" + paramname
+ "' and PARAMETYPE='" +paramtype + "'";
if(LOG.isDebugEnabled()){
LOG.info("updateByNameAndType de sql:" + update_sql);
}
try {
Connection conn = getCurrentConnecton();
Statement stat = conn.createStatement();
stat.executeUpdate(update_sql);
closeConnection(conn);
} catch (SQLException e) {
if(LOG.isDebugEnabled()){
LOG.info("根据" + paramname+" 和 "+paramtype+ "修改PARAMETERS_TABLE对应记录的sql:" + update_sql);
}
e.printStackTrace();
}
}
/**
* 根据name和type查询参数信息
* @param paramname
* @param paramtype
* @return
*/
public ParametesVO findByNameAndType(final String paramname,final String paramtype){
ParametesVO vo=null;
Connection conn =null;
try {
// 取得SQL查询结果声明
conn = getCurrentConnecton();
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(" SELECT * FROM PARAMETERS_TABLE WHERE PARAMENAME = '" + paramname + "' and PARAMETYPE='" + paramtype + "'");
while (rs.next()) { // 取得链接
vo = new ParametesVO();
vo.setNo(rs.getString(1));
vo.setParamName(rs.getString(2));
vo.setParamValue(rs.getString(3));
vo.setParamStatus(rs.getString(4));
vo.setParamType(rs.getString(5));
vo.setParamDescription(rs.getString(6));
}
rs.close();
stat.close();
} catch (SQLException e) {
if(LOG.isDebugEnabled()){
LOG.info("findByNameAndType查询配置参数列表有误");
}
e.printStackTrace();
}finally {
closeConnection(conn);
}
return vo;
}
/**
* 校验数据库文件是否存在
* @return
*/
private boolean isDbexists(){
if(LOG.isDebugEnabled()){
LOG.info("开始校验数据库文件是否存在");
}
File f = new File(BaseConfig.get("JdbcUrl"));
if (f.exists()) {
if(LOG.isDebugEnabled()){
LOG.info("数据库文件存在于"+BaseConfig.get("JdbcUrl"));
}
return true;
}
return false;
}
/**
* 查询前的逻辑处理
*/
private void findBefor(){
if(!isDbexists()){ // 不存在
if(LOG.isDebugEnabled()){
LOG.info("由于数据库文件不存在，故初始化");
}
initParametersTable();
}
}
/**
* 查询所有配置信息
* @param isCheckDB 是否校验数据库文件是否存在
* @return
*/
public List<ParametesVO> findAll(final boolean isCheckDB) {
if(isCheckDB){
findBefor();
}
List<ParametesVO> list = new ArrayList<ParametesVO>();
Connection conn=null;
try {
// 取得SQL查询结果声明
conn = getCurrentConnecton();
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("SELECT * FROM PARAMETERS_TABLE");
while (rs.next()) { // 取得链接
ParametesVO vo = new ParametesVO();
vo.setNo(rs.getString(1));
vo.setParamName(rs.getString(2));
vo.setParamValue(rs.getString(3));
vo.setParamStatus(rs.getString(4));
vo.setParamType(rs.getString(5));
vo.setParamDescription(rs.getString(6));
list.add(vo);
}
rs.close();
stat.close();
} catch (SQLException e) {
if(LOG.isDebugEnabled()){
LOG.info("findALL查询配置参数列表有误");
}
e.printStackTrace();
}finally {
closeConnection(conn);
}
return list;
}
/**
* 获取数据库链接
*
* @return
*/
private static Connection getCurrentConnecton() {
// 初始化连接池
ConnectionPool pool = getPool();
// 取得当前链接
Connection conn = pool.getCurrentConnecton();
if (conn != null) {
return conn;
}
return null;
}
/**
* 单例模式初始化一个连接池
*
* @return
*/
public static ConnectionPool getPool() {
return ConnectionPoolManager.getInstance().getPool("sqllitePool");
}
/**
* 删除一个连接池
* @param conn
*/
public static void closeConnection(final Connection conn){
ConnectionPoolManager.getInstance().closeConnection("sqllitePool", conn);
}
/**
* 清空连接池
*/
public static void destroyPool(){
ConnectionPoolManager.getInstance().destroy("sqllitePool");
}
}
注：本类涉及到的一些数据库链接池的问题或类可参考作者的相关文章