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(){
}
private static class LazyHolder {
private static final SQLiteUtil INSTANCE = new SQLiteUtil();
}
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();
// }
}
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);
}
}
public static String get(final String key,final String type){
return SQLiteUtil.getInstance().findParamValueByName(key,type);
}
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;
}
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;
}
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;
}
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);
}
}
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);
}
}
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;
}
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);
}
}
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();
}
}
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;
}
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();
}
}
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;
}
private static Connection getCurrentConnecton() {
// 初始化连接池
ConnectionPool pool = getPool();
// 取得当前链接
Connection conn = pool.getCurrentConnecton();
if (conn != null) {
return conn;
}
return null;
}
public static ConnectionPool getPool() {
return ConnectionPoolManager.getInstance().getPool("sqllitePool");
}
public static void closeConnection(final Connection conn){
ConnectionPoolManager.getInstance().closeConnection("sqllitePool", conn);
}
public static void destroyPool(){
ConnectionPoolManager.getInstance().destroy("sqllitePool");
}
}
注：本类涉及到的一些数据库链接池的问题或类可参考作者的相关文章
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/56.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
