提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
一、sqlite是什么?
- 一种嵌入式数据库,相比mysql等更轻量,但是不需要安装服务器,直接引入jar即可使用,数据存储在本地文件中。多应用于本地文件数据存储,智能设备应用,手机设备应用,全面支持sql,操作方便。
二、使用步骤
1.引入jar
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.21.0.1</version>
</dependency>
2.代码编写
-
项目结构
-
Column.java
package com.tsn.serv.common.sqlite; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.FIELD) // 声明可以使用该注解的目标类型为在方法中使用 public @interface Column { String type() default "string"; int length();; }
-
SqliteManager.java
package com.tsn.serv.common.sqlite; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.commons.text.StringSubstitutor; public class SqliteManager { private Connection connection; private SqliteManager(Connection connection){ this.connection = connection; } public static class Single { private static SqliteManager sqliteManager = null; static { try { sqliteManager = new SqliteManager(getConnection()); } catch (ClassNotFoundException | SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static Connection getConnection() throws ClassNotFoundException, SQLException{ Class.forName("org.sqlite.JDBC"); return DriverManager.getConnection("jdbc:sqlite:data/node.db"); } public static SqliteManager build(){ return sqliteManager; } } public static SqliteManager build(){ return Single.build(); } public void createTable(String tableName, String className) throws SQLException, SecurityException, ClassNotFoundException { StringBuffer sql = new StringBuffer("create table if not exists "); sql.append(tableName).append("("); String reg = " "; Field[] columnFields = Class.forName(className).getDeclaredFields(); for (Field field : columnFields) { //设置反射对象属性访问权限 field.setAccessible(true); Column col = field.getAnnotation(Column.class); if (col != null) { sql.append(reg + field.getName() + " " + col.type() + "(" + col.length() + ") "); continue; } //通过获得的字段,获取字段类型,和字段名,来定义创建表的字段名和字段类型 if (field.getType().getName().equals(Long.class.getName())) { sql.append(reg + field.getName() + " bigint(20) "); } else if (field.getType().getName().equals(String.class.getName())) { sql.append(reg + field.getName() + " varchar(255) "); } else if (field.getType().getName().equals(Date.class.getName())) { sql.append(reg + field.getName() + " datetime "); } else { sql.append(reg + field.getName() + " int(11) "); } reg = ","; } sql.append(")"); Statement stmt = connection.createStatement(); stmt.executeUpdate(sql.toString()); } public int add(String addSql, Object obj) throws SQLException, InstantiationException, IllegalAccessException{ Statement stmt = connection.createStatement(); StringSubstitutor sub = new StringSubstitutor(objectToMap(obj)); String resolvedString = sub.replace(addSql); return stmt.executeUpdate(resolvedString); } public int update(String sql, Object obj) throws Exception { Statement stmt = connection.createStatement(); StringSubstitutor sub = new StringSubstitutor(objectToMap(obj)); String resolvedString = sub.replace(sql); return stmt.executeUpdate(resolvedString); } public List<?> queryList(String sql, Class<?> cls) throws SQLException, IllegalArgumentException, IllegalAccessException, InstantiationException{ Statement stmt = connection.createStatement(); stmt.setQueryTimeout(30); // set timeout to 30 sec. ResultSet rs = stmt.executeQuery(sql); List<Object> result = new ArrayList<Object>(); while ( rs.next() ) { Object objTemp = cls.newInstance(); Field[] fileds = objTemp.getClass().getDeclaredFields(); for (Field fd : fileds) { fd.setAccessible(true); setValue(objTemp, fd, rs); } result.add(objTemp); } return result; } private void setValue(Object obj, Field fd, ResultSet rs) throws IllegalArgumentException, IllegalAccessException, SQLException{ String filedName = fd.getName(); if (fd.getType().getName().equals("java.lang.String")) { fd.set(obj, rs.getString(filedName)); }else if (fd.getType().getName().equals("java.lang.Integer") || fd.getType().getName().equals("int")) { fd.set(obj, rs.getInt(filedName)); }else if (fd.getType().getName().equals("java.lang.Double") || fd.getType().getName().equals("double")) { fd.set(obj, rs.getDouble(filedName)); }else if (fd.getType().getName().equals("java.lang.Float") || fd.getType().getName().equals("float")) { fd.set(obj, rs.getFloat(filedName)); }else if (fd.getType().getName().equals("java.lang.Long") || fd.getType().getName().equals("long")) { fd.set(obj, rs.getLong(filedName)); }else if (fd.getType().getName().equals("java.lang.Short") || fd.getType().getName().equals("short")) { fd.set(obj, rs.getShort(filedName)); }else if (fd.getType().getName().equals("java.lang.Byte") || fd.getType().getName().equals("byte")) { fd.set(obj, rs.getByte(filedName)); }else if (fd.getType().getName().equals("java.lang.Boolean") || fd.getType().getName().equals("boolean")) { fd.set(obj, rs.getBoolean(filedName)); } } public static Map<String, Object> objectToMap(Object obj) throws IllegalAccessException { Map<String, Object> map = new HashMap<String,Object>(); Class<?> clazz = obj.getClass(); for (Field field : clazz.getDeclaredFields()) { field.setAccessible(true); String fieldName = field.getName(); Object value = field.get(obj); map.put(fieldName, value); } return map; } }
-
调用demo
public class TestSqliteDao { private SqliteManager sqliteManager = SqliteManager.build(); public List<PathInfo> queryPathList() { try { @SuppressWarnings("unchecked") List<User> pathInfoList = (List<User>) sqliteManager.queryList("select col1, col2 from t_user", User.class); return pathInfoList; } catch (Exception e) { return null; } } }