sqlite3 jdbc 只读模式

sqlite3 jdbc 只读 无效方法:

1. “jdbc:sqlite:/bal/work_home/fn.db?readonly=true”
   导致 fn.db?readonly=true 会被当成是数据库文件名

2. “jdbc:sqlite:/bal/work_home/fn.db?jdbc.explicit_readonly=true”
   参考了pragmaReadOnly

sqlite3 jdbc 只读 有效方法 举例

package com.zzz;

import com.zzz.sqlite3db.fn.entity.Func;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteDataSource;

import java.util.List;
import java.util.Properties;

import static com.zzz.utils.JavaVersionUtil.assertJavaVer1_8;

public class SpringJdbcSqlite3Demo {
    public static void main(String[] args){
        assertJavaVer1_8();

        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(org.sqlite.JDBC.class.getCanonicalName());
        dataSource.setUrl("jdbc:sqlite:/bal/work_home/fn.db");
        dataSource.setUsername("");
        dataSource.setPassword("");

		// sqlite3 jdbc  只读模式打开了sqlite数据库
        Properties properties=new Properties();
        properties.setProperty("open_mode","1");
        dataSource.setConnectionProperties(properties);

        JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);


        //正常执行
        Integer fnAbsLctId0=jdbcTemplate.queryForObject("select fnAbsLctId from tbl_Func limit 1", Integer.class);

        //正常查询到10条记录
        List<Func> funcList = jdbcTemplate.query("select * from tbl_Func limit 10", new BeanPropertyRowMapper<>(Func.class));

        //
        /**sqlite3 jdbc  只读模式打开了sqlite数据库
         当执行   delete 语句时 报错如下:
         Caused by: org.sqlite.SQLiteException: [SQLITE_READONLY] Attempt to write a readonly database (attempt to write a readonly database)



         */
        int cnt = jdbcTemplate.update("delete from tbl_Func where fnAbsLctId = 300");

        boolean end=true;
    }
}

寻找过程如下:

1. sqlite-jdbc-3.42.0.0-sources.jar!/org/sqlite/SQLiteConnection.java

private static DB open(String url, String origFileName, Properties props) throws SQLException {...} 
//注意字典props中有 'open_mode=1

2. sqlite-jdbc-3.42.0.0-sources.jar!/org/sqlite/SQLiteConfig.java

public enum Pragma {
OPEN_MODE("open_mode", "Database open-mode flag", null), 	
//实践表明 open_mode设置为1 即 表示 只读模式打开数据库 相当于 命令 'sqlite3 -readonly fn.db'
...
}