sqlite-集成和运用

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

一、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;
  		}
  		
  	}
  }
  

---