Java Generate DB file

Work encountered a requirement, that is, some table data in MySQL to generate DB files, to the client use, the client use SQLite database;

First we need to add the SQLite JDBC dependency in the project

<Dependency>  <groupId>Org.xerial</groupId>  <Artifactid>Sqlite-jdbc</Artifactid>  <version>3.23.1</version></Dependency>

Generate DB File Tool class

 PackageCn.kayun.util;Importorg.apache.commons.lang3.StringUtils;ImportOrg.slf4j.Logger;Importorg.slf4j.LoggerFactory;ImportJava.lang.reflect.Field;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.PreparedStatement;Importjava.sql.Statement;ImportJava.text.SimpleDateFormat;Importjava.util.ArrayList;Importjava.util.List;ImportJava.util.Locale;/*** Created by Root on 2018/7/2 0002.*/ Public classDbutil {Private Static FinalLogger Logger = Loggerfactory.getlogger (dbutil.class); Private StaticConnection Connection =NULL; Private StaticStatement Statement =NULL; Private StaticString username = "Kanyun"; Private StaticString password = "Kanyun"; /*** @describe: Set connection * @params: * @Author: Kanyun * @Date: 2018/7/12 9:54*/     Public Static voidsetconnection () {Try {//declaring a drive typeClass.forName ("Org.sqlite.JDBC");//set the SQLite db file to hold the base directoryString Path = Dbutil.class. getClassLoader (). GetResource (""). GetPath ();//Set the SQLite file path, equivalent to the MySQL connection address (jdbc:mysql://127.0.0.1:3306/test)String url = "Jdbc:sqlite:" + path + "Data.db";//Get ConnectionsConnection =drivermanager.getconnection (URL, username, password);//Statementstatement =connection.createstatement (); } Catch(Exception e) {Throw NewRuntimeException ("Failed to establish SQLite connection"); }    }    /*** @describe: CREATE TABLE * @params: TableName: Name of the table to be created ClassName: the class name Pojo in the project (note that the class name needs to be accompanied by a package name such as COM.XXX.XXX.POJO.XXX) * @Author: Kanyun * @Date: 2018/7/12 9:56*/     Public synchronized Static voidCreate (String tableName, String className) {Try{statement.executeupdate ("DROP TABLE IF EXISTS" + TableName + ";");//get field information for the passed-in class name through reflectionfield[] Fields =Class.forName (className). Getdeclaredfields (); StringBuffer SB=NewStringBuffer (); String Reg= " "; Sb.append ("CREATE TABLE" + TableName + "(");  for(Field field:fields) {//When setting use reflection, you can access the private variable, and when the Pojo variable is set to private, isaccessible () Gets the value false and must be changed to true to accessField.setaccessible (true);//define field names and field types for creating tables by obtaining field types, and field names, by getting fields                if(Field.gettype (). GetName (). Equals (Java.lang.Long.class. GetName ())) {Sb.append (reg+ field.getname () + "bigint (20)"); } Else if(Field.gettype (). GetName (). Equals (java.lang.String.class. GetName ())) {Sb.append (reg+ field.getname () + "varchar (255)"); } Else if(Field.gettype (). GetName (). Equals (java.util.Date.class. GetName ())) {Sb.append (reg+ field.getname () + "datetime"); } Else{sb.append (reg+ field.getname () + "int (11)"); } Reg= ","; } sb.append (") ;");        Statement.executeupdate (Sb.tostring ()); } Catch(Exception e) {logger.error ("Build table failed:" +e); Throw NewRuntimeException ("Build table failed, table name:" +tableName); }    }    /*** @describe: Insert data in table * @params: TableName: Table Name list: The collection of objects to be inserted note that the inserted object corresponds to the table name * @Author: Kanyun * @Date : 2018/7/12 10:03*/     Public synchronized Static<T>intInsert (String tableName, list<t>list) {StringBuffer Declaration=NewStringBuffer (); List<String> Datasqlarray =NewArraylist<>(); intCount = 0; Try{list.stream (). Map (t- {                if(Datasqlarray.size () < 1) {Logger.info ("Define statement"); Field[] Fields=T.getclass (). Getdeclaredfields (); Declaration.append (" ("); String Reg= "";  for(Field field:fields) {field.setaccessible (true); Declaration.append (Reg+field.getname ()); Reg= ","; } declaration.append (")"); }                returnT; }). ForEach ((t)-{field[] fields=T.getclass (). Getdeclaredfields (); StringBuffer Onedata=NewStringBuffer (); String Reg= ""; Onedata.append (" (");  for(Field field:fields) {Try{field.setaccessible (true); if(NULL= = Field.get (t) | | "". Equals (Field.get (t))) {Onedata.append (reg+NULL); } Else if(Field.gettype (). GetName (). Equals (java.lang.String.class. GetName ())) {//inserting a database requires escaping single quotation marks when a field in an incoming object is a stringOnedata.append (reg + "\ '" + field.get (t) + "\");//when a field in an incoming object is a date type, the date is converted to the form YYYY-MM-DD (I here, the Date object format for the incoming object is: Wed Jul 14:34:07 CST 2018), converting it into strings, note escape single quotes}Else if(Field.gettype (). GetName (). Equals (java.util.Date.class. GetName ())) {String dd=Field.get (t). toString (); SimpleDateFormat SDF1=NewSimpleDateFormat ("EEE MMM dd HH:mm:ss z yyyy", Locale.english); SimpleDateFormat SDF2=NewSimpleDateFormat ("Yyyy-mm-dd HH:mm:ss"); String Date=Sdf2.format (Sdf1.parse (DD)); Onedata.append (Reg+ "\" + date + "\"); } Else{onedata.append (reg+Field.get (t)); } Reg= ","; } Catch(Exception e) {e.printstacktrace (); }} onedata.append (")");            Datasqlarray.add (Onedata.tostring ());            }); String Datasql= Stringutils.join (Datasqlarray, ","); String Retsql= "INSERT into" + TableName +declaration.tostring ()+ "VALUES" + Datasql + ";";            System.out.println (Retsql); PreparedStatement Prep=connection.preparestatement (retsql);//set up auto-commitConnection.setautocommit (true); Count=prep.executeupdate (); } Catch(Exception e) {logger.error ("Insert failed:" +e);        E.printstacktrace (); }        returncount; }    /*** @describe: Close link * @params: * @Author: Kanyun * @Date: 2018/7/12 10:11*/     Public Static voidendconnection () {Try{connection.close (); } Catch(Exception e) {e.printstacktrace (); }    }}

The generated DB file is in the Classess directory.

Note that while creating a link, you can pass in the user name, password, but use some tools (such as navicat) or you can view the contents of the DB file, because the free version of SQLite has a fatal disadvantage: encryption is not supported

If you need to use SQLite encryption, the first is the data itself to encrypt, that is, the data inserted into the table is encrypted, and the second is to encrypt the db file itself

For more information on encryption, refer to: https://www.cnblogs.com/ligun123/p/5206942.html

Java Generate DB file