Foreword
The sqlite database is a lightweight database. It does not need to be installed on the machine like MySQL, occupying ports, etc. It can store data in a .db file.
Official website https://www.sqlite.org/index.html
Most of the usage scenarios of sqlite database on the Internet are in the Android system. The scenario I use is to develop an engine toolkit for direct use by desktop app or command line. This toolkit needs to write library operations, so I choose this database.
Not much nonsense…
1. Basic use of sqlite
1. Download operation
Download directly from the official website: https://www.sqlite.org/download.html
Just download the corresponding zip package according to the required version of the operating system. I use it on a Windows machine. After downloading and decompressing the zip package, it has three files
It shows that it is lightweight, just configure the environment variables after decompression, execute sqlite3.exe on the command line to enter the library, and then root the database file .db to open the data file for operation.
2. Basic operation and use
Reference: https://www.cnblogs.com/aeolian/p/16179638.html
Create database
-- create the database directly sqlite3 DatabaseName.db -- Enter the sqlite environment to create a database .open test.db -- View the current database .databases -- view all tables SELECT tbl_name FROM sqlite_master WHERE type = 'table'; -- quit .quit
Import and export database
-- export sqlite3 testDB.db.dump > testDB.sql -- import sqlite3 testDB.db < testDB.sql
Create table
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, WEBSITE CHAR(200), RANK INT ); --View table structure .schema
Addition, deletion, modification and query of data
Add new insert -- insert INSERT INTO COMPANY (ID,NAME,WEBSITE,RANK) VALUES (1, 'Walmart Inc. ', 'https://www.stock.walmart.com', 1); INSERT INTO COMPANY (ID,NAME,WEBSITE,RANK) VALUES (2, 'Amazon.com, Inc.', 'https://www.amazon.com', 2); INSERT INTO COMPANY (ID,NAME,WEBSITE,RANK) VALUES (3, 'Apple Inc. ', 'https://www.apple.com', 3); delete delete DELETE FROM COMPANY WHERE ID = 3; modify update UPDATE COMPANY SET RANK = 10 where id = 2;
Query select
-- display header .header on -- Align headers by columns .mode column -- Inquire SELECT * FROM COMPANY;
File data import into sqlite
Method 1: Excel imports sqlite
Excel files are generally stored in xslx format, and need to be saved in csv format before being imported into the sqlite3 database.
When there are commas (‘,’) and line breaks in the csv format file, they need to be replaced. Because the comma is the separator of the column in csv, and the newline will be considered as the next row in excel, and these two characters will affect the parsing of the imported data by sqlite. These two characters can be replaced by some special characters (combination of !@#￥#). In short, commas and newlines should not appear in csv format files.
Only the plain data text is kept in the csv file to be imported, and no header is required.
#1 Open or create database test.db ./sqlite3 test.db #2 create tb create table tb(method_id integer primary key, ic TEXT, oi TEXT, oi_name TEXT); #3 Change the delimiter for displaying information (default delimiter '|') .separator',' #4 Import data into the created list .import data.csv tb
Method 2: Import txt text into sqlite3
The data copied from excel to txt in this article will contain the tab key, we only need to replace the tab key with other symbols other than (comma and line break) that do not affect the parsing of sqlite3 data, such as ‘*’. (Because is a delimiter, you must not use symbols that already exist in txt, otherwise there will be an error of “data type does not match”)
Tab key replacement: Copy a tab key in the txt text to the text shortcut replacement.
#3 Change the separator of the displayed information and set it to the symbol you replaced .separator '*' #4 Import data into the created list .import data.txt tablename
sqlite3 data export
.output file.txt tablename
3. Using sqlite database in Java
1. Import dependencies in maven project
<!-- sqlite-jdbc --> <dependency> <groupId>org.xerial</groupId> <artifactId>sqlite-jdbc</artifactId> <version>3.39.3.0</version> </dependency> <!-- mybatis --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus</artifactId> <version>3.5.2</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.10</version> </dependency> <>
2. Create SqliteConfig class configuration, use mybatis to operate the sqlite database
package org.sqlite;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
@Slf4j
public class Sqlite3Config {<!-- -->
private static SqlSessionFactory sqlSessionFactory;
static {<!-- -->
// specify the path to the .db file
String fileParth = "";
File dbFile = new File(fileParth);
try {<!-- -->
InputStream stream = Resources.getResourceAsStream("mybatis-config.xml");
Properties properties = new Properties();
// Configuration file path, passed into ${url} of the mybatis-config.xml file
properties.put("url", JDBC.PREFIX + dbFile.getPath());
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
sqlSessionFactory = builder.build(stream,properties);
Configuration configuration = sqlSessionFactory. getConfiguration();
configuration.setMapUnderscoreToCamelCase(false);
} catch (IOException e) {<!-- -->
e.printStackTrace();
log. error("database connection failed!");
}
}
/**
* Get sqlsession to communicate with mapper layer
*/
public static SqlSession getSqlSesstion(boolean autocommit){<!-- -->
return sqlSessionFactory. openSession(autocommit);
}
}
3. Configuration of mybatis-config.xml file
<?xml version="1.0" encoding="UTF-8" ?>
<! DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- The root element of the configuration file -->
<configuration>
<!-- Attribute: Define configuration externalization -->
<properties/>
<!-- Settings: Define some global settings of mybatis -->
<settings>
<!-- STDOUT_LOGGING console output log, NO_LOGGING no redundant log output -->
<setting name="logImpl" value="NO_LOGGING"/>
</settings>
<!-- type name: define an alias for some classes -->
<typeAliases/>
<!-- Type processor: Define the conversion relationship between Java types and data types in the database -->
<typeHandlers/>
<!-- Object Factory -->
<objectFactory type=""/>
<!-- Plug-in: mybatis plug-in, the plug-in can modify the internal operation rules of mybatis -->
<plugins>
<plugin interceptor=""/>
</plugins>
<!-- Environment: Configure the environment of mybatis -->
<environments default="sqlite">
<!-- Environment variables: Multiple environment variables can be configured. For example, when using multiple data sources, multiple environment variables need to be configured -->
<environment id="sqlite">
<!-- transaction manager -->
<transactionManager type="JDBC"/>
<!-- data source -->
<dataSource type="POOLED">
<property name="driver" value="org.sqlite.JDBC"/>
<!-- ${url} here is used to receive the url in the properties in the sqliteConfig file -->
<property name="url" value="${url}"/>
</dataSource>
</environment>
</environments>
<!-- Database vendor ID -->
<databaseIdProvider type=""/>
<!-- mapper: specify the mapping file or mapping class -->
<mappers>
<mapper class="org.example.TestMapper"/>
<!-- ... ... -->
</mappers>
</configuration>
4. The following is the encapsulation of mybatis for sqlite3config to create sqlsession, and use the proxy method to encapsulate the process of creating mapper. Note that this encapsulation sets sqlsession to automatic submission, so it can be used for query operations, but It is not recommended to add, delete, and modify operations
package org.sqlite;
import org.apache.ibatis.session.SqlSession;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
public class MapperInterceptor<T> implements InvocationHandler {<!-- -->
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {<!-- -->
// The created sqlsession is automatically submitted
try (SqlSession sqlSession = Sqlite3Config.getSqlSesstion(true)){<!-- -->
Class<?> aClass = method. getDeclaringClass();
T mapper = (T)sqlSession. getMapper(aClass);
return method. invoke(mapper, args);
}
}
}
package org.sqlite;
import java.lang.reflect.Proxy;
public class MapperProxy {<!-- -->
public static <T> T getProxyInstance(Class<T> tClass){<!-- -->
return (T) Proxy. newProxyInstance(tClass. getClassLoader(),
new Class[]{<!-- -->tClass},
new MapperInterceptor<T>());
}
}
The usage is as follows: The created mapper can be used for query operations
private TestMapper mapper = MapperProxy.getProxyInstance(TestMapper.class);
If you want to perform batch addition, deletion and modification operations, use the following method to submit manually
public int addBatch(List<String> stringList){<!-- -->
try (SqlSession sqlSession = Sqlite3Config.getSqlSesstion(false)){<!-- -->
TestMapper mapper = sqlSession. getMapper(TestMapper. class);
stringList.forEach(s -> {<!-- -->
mapper.insert(s);
});
sqlSession.commit();
return stringList. size();
}catch (Exception e){<!-- -->
e.printStackTrace();
}
return 0;
}
Appendix, at the beginning of the research, the original set was used (deprecated)
Reference: https://blog.csdn.net/qq_42413011/article/details/118640420
public static SqlSessionFactory getSqlSessionFactory(){<!-- -->
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
MybatisConfiguration configuration = (MybatisConfiguration) new Configuration();
configuration.setMapUnderscoreToCamelCase(false);
configuration.setUseGeneratedKeys(true);
configuration.addInterceptor(mybatisPlusInterceptor());
GlobalConfig globalConfig = new GlobalConfig();
globalConfig.setSqlInjector(new DefaultSqlInjector());
globalConfig.setIdentifierGenerator(new DefaultIdentifierGenerator());
// globalConfig.setSuperMapperClass()
globalConfig.setDbConfig(new GlobalConfig.DbConfig().setUpdateStrategy(FieldStrategy.IGNORED));
GlobalConfigUtils.setGlobalConfig(configuration, globalConfig);
try {<!-- -->
registryMapperXml(configuration,"mapper");
}catch (Exception e){<!-- -->
e.printStackTrace();
}
Environment environment = new Environment("1", new JdbcTransactionFactory(), initDatasource());
configuration.setEnvironment(environment);
SqlSessionFactory sqlSessionFactory = builder.build(configuration);
return sqlSessionFactory;
}
private static DataSource initDatasource() {<!-- -->
SQLiteDataSource dataSource = new SQLiteDataSource();
//path to the database file
String fileParth = "";
File dbFile = new File(fileParth);
dataSource.setUrl(JDBC.PREFIX + dbFile.getPath());
return dataSource;
}
private static void registryMapperXml(MybatisConfiguration configuration, String classPath) throws IOException {<!-- -->
ClassLoader contextClassLoader = Thread. currentThread(). getContextClassLoader();
Enumeration<URL> mapper = contextClassLoader.getResources(classPath);
while (mapper.hasMoreElements()) {<!-- -->
URL url = mapper.nextElement();
if (url. getProtocol(). equals("file")) {<!-- -->
String path = url. getPath();
File file = new File(path);
File[] files = file. listFiles();
for (File f : files) {<!-- -->
FileInputStream in = new FileInputStream(f);
XMLMapperBuilder xmlMapperBuilder = new XMLMapperBuilder(in, configuration, f.getPath(), configuration.getSqlFragments());
xmlMapperBuilder. parse();
in. close();
}
} else {<!-- -->
JarURLConnection urlConnection = (JarURLConnection) url. openConnection();
JarFile jarFile = urlConnection. getJarFile();
Enumeration<JarEntry> entries = jarFile.entries();
while (entries. hasMoreElements()) {<!-- -->
JarEntry jarEntry = entries. nextElement();
if (jarEntry. getName(). endsWith(".xml")) {<!-- -->
InputStream in = jarFile. getInputStream(jarEntry);
XMLMapperBuilder xmlMapperBuilder = new XMLMapperBuilder(in, configuration, jarEntry. getName(), configuration. getSqlFragments());
xmlMapperBuilder. parse();
in. close();
}
}
}
}
}
private static Interceptor mybatisPlusInterceptor() {<!-- -->
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.SQLITE));
return interceptor;
}
This method does not use the mybatis-config.xml configuration file, and configures all configuration data in the form of code.
- The registryMapperXml() method is to scan all the mapper.xml operation database files, and then register them in MybatisConfiguration
- The getSqlSessionFactory() method is to do the operation of configuring properties properties
- The initDatasource() method is to initialize the data source
- mybatisPlusInterceptor () common mybatisplus interceptor settings
Based on this record, there are also many pitfalls. For example, when writing operations, sqlite will be locked, causing other operations to fail. This is also the use of the mybatis framework to operate the database alone after leaving the familiar springboot framework. In addition to dependencies, using native methods requires a deeper understanding of the underlying layer.
The road ahead is long and difficult, so keep working hard.