AbacusUtil Docs | Download | Javadoc | FAQ | Community

JdbcUtil/CSVUtil

JdbcUtil/CSVUtil provides the methods to create data source (manager), prepare/execute statement, import/export data with (CSV/JSON) format...:

  • create data source (manager) with xml configuration. Here is the schema: DataSource.xsd
  • <?xml version="1.0" encoding="UTF-8"?>
    
    <dataSourceManager liveEnv="dev">
        <dataSource name="codes" env="dev">
            <sqlLog>true</sqlLog>
            <connection>
                <driver>org.h2.Driver</driver>
                <url>jdbc:h2:tcp://localhost/~/codes</url>
                <user>SA</user>
                <password></password>
                <liveTime>86400000</liveTime>
                <maxIdleTime>1800000</maxIdleTime>
                <maxActive>100</maxActive>
            </connection>
        </dataSource>
    </dataSourceManager>
    
    static final String dataSourcePath = "./resources/config/abacus/h2_dataSource.xml";
    
    static final DataSourceManager dsm = JdbcUtil.createDataSourceManager(dataSourcePath);
    static final DataSource ds = dsm.getPrimaryDataSource();
    // Or: static final DataSource ds = JdbcUtil.createDataSource(dataSourcePath);
    
    static final SQLExecutor sqlExecutor = new SQLExecutor(ds);
    

  • export/import to/from CSV
  • sqlExecutor.batchInsert(INSERT_ACCOUNT, accounts);
    
    Connection conn = sqlExecutor.getConnection();
    File file = new File("./unittest/test.csv");
    String sql = "SELECT first_name, last_name, gui, last_update_time, create_time FROM account";
    CSVUtil.exportCSV(file, conn, sql);
    
    sqlExecutor.batchUpdate(DELETE_ACCOUNT_BY_ID, accounts);
    
    List<Type<Object>> types = N.getType(String.class, String.class, String.class, Timestamp.class, Timestamp.class);
    sql = "INSERT INTO account (first_name, last_name, gui, last_update_time, create_time) VALUES ( ?,  ?,  ?, ?, ?)";
    CSVUtil.importCSV(file, conn, sql, types);
    

    It's easy/simple to handle huge data with SQLExecutor and JdbcUtil/CSVUtil (through CSV file or DataSet)