Java 生成Sqlite的.db文件

ゞ浴缸里的玫瑰 2023-09-30 11:44 117阅读 0赞

import java.io.File;
    import java.lang.reflect.Field;
    import java.sql.*;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Locale;
    import java.util.concurrent.atomic.AtomicInteger;
    
    /**
     * @author Zc
     * @date 2022年03月20日 11:24
     */
    public class DBUtill {
        
        private static Connection connection = null;
        private static Statement statement = null;
        private static String username = "zc";
        private static String password = "zc";
    
        /**
         * 开始事务
         * @param cnn
         */
        public static void beginTransaction(Connection cnn){
        
            if(cnn!=null){
        
                try {
        
                    if(cnn.getAutoCommit()){
        
                        cnn.setAutoCommit(false);
                    }
                } catch (SQLException e) {
        
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    
        /**
         * 提交事务
         * @param cnn
         */
        public static void commitTransaction(Connection cnn){
        
            if(cnn!=null){
        
                try {
        
                    if(!cnn.getAutoCommit()){
        
                        cnn.commit();
                    }
                } catch (SQLException e) {
        
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    
        /**
         * 回滚事务
         * @param cnn
         */
        public static void rollBackTransaction(Connection cnn){
        
            if(cnn!=null){
        
                try {
        
                    if(!cnn.getAutoCommit()){
        
                        cnn.rollback();
                    }
                } catch (SQLException e) {
        
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        /**
         * @describe: 设置连接  
         * @params:  
         * @Author: zc  
         * @Date: 2022/03/20
         */
        public static void setConnection(String path) {
        
            try {
        
                File f  = new File(path);
                if(!f.exists()){
        
                    File file = new File(path);
                    file.mkdirs();
                }
    //            声明驱动类型
                Class.forName("org.sqlite.JDBC");
    //            设置 sqlite文件路径，等同于mysql连接地址(jdbc:mysql://127.0.0.1:3306/test)
                String url = "jdbc:sqlite:" + path + "\\data.db";
    //            获取连接
                connection = DriverManager.getConnection(url, username, password);
    //            声明
                statement = connection.createStatement();
            } catch (Exception e) {
        
                throw new RuntimeException("建立Sqlite连接失败");
            }
    
    
        }
    
        /**
         * @describe: 创建表
         * @params: tableName: 要创建的表的名称 className：项目中Pojo的类名(需要注意的是该类名需要加上包名 如 com.xxx.xxx.xxx)
         * @Author: zc
         * @Date: 2022/03/20
         */
        public synchronized static void create(String tableName, String className) throws SQLException {
        
            try {
        
                statement.executeUpdate("DROP TABLE IF EXISTS " + tableName + ";");
    //            通过反射，获取传入类名的字段信息
                Field[] fields = Class.forName(className).getDeclaredFields();
                StringBuffer sb = new StringBuffer();
                String reg = " ";
                sb.append("create table " + tableName + " (");
                for (Field field : fields) {
        
    //                设置使用反射时,可以访问私有变量,当pojo的变量设置为private修饰时,isAccessible()得到的值是false，必须要改成true才可以访问
                    field.setAccessible(true);
    //                通过获得的字段,获取字段类型,和字段名,来定义创建表的字段名和字段类型
                    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) {
        
                e.printStackTrace();
                throw new RuntimeException("建表失败，表名称：" + tableName);
            }
        }
    
        /**
         * @describe: 表中插入数据
         * @params: tableName：表名 list:待插入的对象集合 需要注意的是插入的对象要跟表名对应
         * @Author: zc
         * @Date: 2022/03/20
         */
        public synchronized static <T> int insert(String tableName, List<T> list) throws SQLException {
        
            StringBuffer declaration = new StringBuffer();
            List<String> dataSqlArray = new ArrayList<>();
            AtomicInteger count = new AtomicInteger();
            beginTransaction(connection);
            try {
        
                list.stream().map(t -> {
        
                    if (dataSqlArray.size() < 1) {
        
                        Field[] fields = t.getClass().getDeclaredFields();
                        declaration.append(" (");
                        String reg = "";
                        for (Field field : fields) {
        
                            field.setAccessible(true);
                            declaration.append(reg + field.getName());
                            reg = ",";
                        }
                        declaration.append(")");
                    }
                    return t;
                }).forEach((t) -> {
        
                    Field[] fields = t.getClass().getDeclaredFields();
                    StringBuffer oneData = new StringBuffer();
                    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())) {
        
    //                            传入的对象中字段是字符串时,插入数据库需要转义单引号
                                oneData.append(reg + "\'" + field.get(t) + "\'");
    //                            传入的对象中字段是日期类型时,先将日期转换为 yyyy-MM-dd 的形式
                            } else if (field.getType().getName().equals(java.util.Date.class.getName())) {
        
                                String dd = field.get(t).toString();
                                SimpleDateFormat sdf1 = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy", Locale.ENGLISH);
                                SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                String date = sdf2.format(sdf1.parse(dd));
                                oneData.append(reg + "\'" + date + "\'");
                            } else if (field.getType().getName().equals(java.lang.Integer.class.getName())||field.getType().getName().equals(java.lang.Double.class.getName())){
        
                                oneData.append(reg + (field.get(t)==null?0:field.get(t)));
                            }else {
        
                                oneData.append(reg + field.get(t));
                            }
                            reg = ",";
                        } catch (Exception e) {
        
                            e.printStackTrace();
                        }
                    }
                    oneData.append(")");
                    dataSqlArray.add(oneData.toString());
                    String retSQL = "INSERT INTO " + tableName + declaration.toString()
                            + " VALUES " + oneData + ";";
                    PreparedStatement prep = null;
                    try {
        
                        prep = connection.prepareStatement(retSQL);
                        count.set(count.get()+prep.executeUpdate());
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                        throw new RuntimeException(tableName+"表插入数据失败");
                    }
                });
                //之前使用的将所有values(insert(xx,xx,...)values(xx,xx,...),(xx,xx,..)... )
               /* String dataSql = StringUtils.join(dataSqlArray, ",");
                if (dataSql!=null&&!"".equals(dataSql)) {
                    String retSQL = "INSERT INTO " + tableName + declaration.toString()
                            + " VALUES " + dataSql + ";";
                    PreparedStatement prep = connection.prepareStatement(retSQL);
                   count.set(prep.executeUpdate());
                }*/
                //先插入数据后建索引，数据量大时速度会有点提升
                if ("skutm3".equals(tableName)){
        
                    statement.executeUpdate("CREATE INDEX index_tm ON skutm3(tm) ; ");
                }
                //事务提交
                commitTransaction(connection);
            } catch (Exception e) {
        
                //事务回滚
                rollBackTransaction(connection);
                e.printStackTrace();
                throw new RuntimeException(tableName+"表插入数据失败");
            }
            return count.get();
        }
    
        /**
         * @describe: 关闭链接
         * @params:
         * @Author: zc
         * @Date: 2022/03/20
         */
        public static void endConnection() {
        
            try {
        
    
                connection.close();
    
            } catch (Exception e) {
        
                e.printStackTrace();
            }
        }
    }

来源`https://www.cnblogs.com/kanyun/p/9298163.html`