mybatis Posts

Scrapping Data from BPS Website using Java

BPS (Badan Pusat Statistik) is a non-departmental government institute of Indonesia that is responsible for conducting statistical surveys. On its website, we can see that there are a lot of data available, especially regarding to spatial and regional such as number of provinces, zipcodes, cities, and others.

One BPS website which is contains interesting data is https://sig.bps.go.id/, an on this sample im trying to crawl and read the zipcode data from it.

Lets start with pom file,

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.edw</groupId>
    <artifactId>ScrappingBPS</artifactId>
    <version>1.0</version>

    <properties>
        <java.version>11</java.version>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.squareup.okhttp3</groupId>
            <artifactId>okhttp</artifactId>
            <version>4.9.3</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.13.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.15.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.15.0</version>
        </dependency>

        <dependency>
            <groupId>com.lmax</groupId>
            <artifactId>disruptor</artifactId>
            <version>3.4.4</version>
        </dependency>

    </dependencies>
</project>

Since im using MyBatis, so lets create a SessionFactory class.

package com.edw.config;

import com.edw.mapper.KodeposMapper;
import com.edw.mapper.RegionsMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.Reader;

public class MyBatisSqlSessionFactory {
    private static final SqlSessionFactory FACTORY;

    static {
        try {
            Reader reader = Resources.getResourceAsReader("configuration.xml");
            FACTORY = new SqlSessionFactoryBuilder().build(reader);
            FACTORY.getConfiguration().addMapper(KodeposMapper.class);
            
        } catch (IOException e) {
            throw new RuntimeException("Fatal Error. Cause: " + e, e);
        }
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return FACTORY;
    }
}

And a bean for table representation, and a mapper interface.

package com.edw.bean;

import java.io.Serializable;

public class Kodepos implements Serializable {
    private Long id;
    private String kelurahan;
    private String kecamatan;
    private String kabupaten;
    private String provinsi;
    private String kodepos;

    public Kodepos() {
    }

    // other setter and getter
}
package com.edw.mapper;

import com.edw.bean.Kodepos;
import org.apache.ibatis.annotations.Insert;

public interface KodeposMapper {

    @Insert("INSERT INTO `db_kodepos`.`tbl_kodepos` " +
            "(`kelurahan`, `kecamatan`, `kabupaten`, `provinsi`, `kodepos`) " +
            "VALUES (#{kelurahan}, #{kecamatan}, #{kabupaten}, #{provinsi}, #{kodepos})")
    Integer insert(Kodepos kodepos);
}

And last is our main class, im using okhttp to do http request and jackson for parsing json to java object

package com.edw;

import com.edw.bean.Kodepos;
import com.edw.config.MyBatisSqlSessionFactory;
import com.edw.mapper.KodeposMapper;
import com.fasterxml.jackson.databind.ObjectMapper;
import okhttp3.Call;
import okhttp3.OkHttpClient;
import okhttp3.Request;
import okhttp3.Response;
import org.apache.ibatis.session.SqlSession;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.io.IOException;
import java.util.*;
import java.util.concurrent.TimeUnit;

public class KodeposScrapping {

    private SqlSession sqlSession = null;
    private KodeposMapper kodeposMapper  = null;

    private static final String BASE_URL = "https://sig.bps.go.id";

    private Logger logger = LogManager.getLogger(KodeposScrapping.class);

    public KodeposScrapping() {
    }

    public static void main(String[] args) throws IOException {
        KodeposScrapping kodeposScrapping = new KodeposScrapping();
        kodeposScrapping.doScrapping();
    }

    private void doScrapping() throws IOException {
        getProvinsis();
    }

    /**
     *  <pre> curl "https://sig.bps.go.id/rest-drop-down/getwilayah" </pre>
     */
    private void getProvinsis() throws IOException {
        Request request = new Request.Builder()
                .url(BASE_URL + "/rest-drop-down/getwilayah")
                .get()
                .build();

        List<HashMap> hashMaps = doHttpCall(request);

        for (HashMap hashMap : hashMaps) {
            Kodepos kodepos = new Kodepos();
            kodepos.setProvinsi(hashMap.get("nama").toString());

            logger.info("start processing {}", hashMap.get("nama").toString());

            // get Kabupatens from Provinsi
            getKabupatens(hashMap.get("kode").toString(), kodepos);

            logger.info("done processing {}", hashMap.get("nama").toString());
        }
    }

    /**
     *  <pre> curl "https://sig.bps.go.id/rest-drop-down/getwilayah?level=kabupaten&parent=11" </pre>
     */
    private void getKabupatens(String parent, Kodepos kodepos) throws IOException {
        Request request = new Request.Builder()
                .url(BASE_URL + "/rest-drop-down/getwilayah?level=kabupaten&parent="+parent)
                .get()
                .build();

        List<HashMap> hashMaps = doHttpCall(request);

        for (HashMap hashMap : hashMaps) {
            kodepos.setKabupaten(hashMap.get("nama").toString());

            // get Kecamatans from Kabupaten
            getKecamatans(hashMap.get("kode").toString(), kodepos);
        }
    }

    /**
     *  <pre> curl "https://sig.bps.go.id/rest-drop-down/getwilayah?level=kecamatan&parent=1101" </pre>
     */
    private void getKecamatans(String parent, Kodepos kodepos) throws IOException {
        Request request = new Request.Builder()
                .url(BASE_URL + "/rest-drop-down/getwilayah?level=kecamatan&parent="+parent)
                .get()
                .build();

        List<HashMap> hashMaps = Collections.synchronizedList(doHttpCall(request));

        for (HashMap hashMap : hashMaps) {
            kodepos.setKecamatan(hashMap.get("nama").toString());
            getKelurahans(hashMap.get("kode").toString(), kodepos);
        }
    }

    /**
     *  <pre> curl "https://sig.bps.go.id/rest-bridging-pos/getwilayah?level=desa&parent=1101050" </pre>
     */
    private void getKelurahans(String parent, Kodepos kodepos) throws IOException {
        Request request = new Request.Builder()
                .url(BASE_URL + "/rest-bridging-pos/getwilayah?level=desa&parent="+parent)
                .get()
                .build();

        List<HashMap> hashMaps = Collections.synchronizedList(doHttpCall(request));

        for (final HashMap hashMap : hashMaps) {
            kodepos.setKelurahan(hashMap.get("nama_bps").toString());
            kodepos.setKodepos(hashMap.get("kode_pos").toString());

            insert(kodepos);
        }
    }

    private void insert(Kodepos kodepos) {
        try {
            sqlSession = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(true);
            kodeposMapper = sqlSession.getMapper(KodeposMapper.class);

            kodeposMapper.insert(kodepos);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }

    private List<HashMap> doHttpCall(Request request) throws IOException {
        Call call = new OkHttpClient().newBuilder()
                .retryOnConnectionFailure(true)
                .connectTimeout(300, TimeUnit.SECONDS)
                .readTimeout(300, TimeUnit.SECONDS)
                .writeTimeout(300, TimeUnit.SECONDS).build().newCall(request);
        Response response = call.execute();

        ObjectMapper objectMapper = new ObjectMapper();
        List<HashMap> hashMaps = objectMapper.readValue(response.body().string(), List.class);

        response.close();
        return hashMaps;
    }
}

Code can be found in my github repository,

https://github.com/edwin/bps-data-scrapper

[Java] org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-00923: FROM keyword not found where expected

Had this weird error, when connecting to Oracle Database

### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; 
nested exception is org.apache.commons.dbcp.SQLNestedException: 
Cannot create PoolableConnectionFactory (ORA-00923: FROM keyword not found where expected
)

Somehow it never happens on my MySql Configuration. Here is my configuration,

<bean id="dataSourceOracle" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
          p:driverClassName="oracle.jdbc.driver.OracleDriver" p:url="${db.url.oracle}"
          p:username="${db.username.oracle}" p:password="${db.password.oracle}"
          p:initialSize="2"
          p:maxActive="30"
          p:maxIdle="10"
          p:minIdle="3"
          p:maxWait="30000"
          p:removeAbandoned="true"
          p:removeAbandonedTimeout="30"
          p:validationQuery="SELECT 1" />

After investigating for a while, i found out that it happens because i use MySql’s specific validationQuery, “SELECT 1”. Changing it into “SELECT 1 FROM DUAL” make the error go away. 😛

Error No Buffer Space Available when Using Java, MyBatis and MySql

I’ve met a very weird error, that are not supposed to happen, when using MyBatis and MySql. Somehow my Windows OS is running out of ports, this is the complete stacktrace.

Caused by: java.net.SocketException: No buffer space available (maximum connections reached?): connect
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.PlainSocketImpl.doConnect(Unknown Source)
    at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
    at java.net.PlainSocketImpl.connect(Unknown Source)
    at java.net.SocksSocketImpl.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at java.net.Socket.<init>(Unknown Source)
    at java.net.Socket.<init>(Unknown Source)
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:257)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:294)]

I found out it happened because im using traditional unpooled connection, changing it into pooled connection in MyBatis solved it.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost/xxxx"/>
                <property name="username" value="xxxx"/>
                <property name="password" value=""/>
                <property name="poolMaximumActiveConnections" value="20"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>

    </mappers>
</configuration>

Weird MyBatis Error, “Error setting null for parameter [number] with JdbcType OTHER”

I had a very weird error today, when connecting MyBatis to Oracle Database, the complete error is like this,

Error setting null for parameter #2 with JdbcType OTHER . 
Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. 
Cause: java.sql.SQLException: Invalid column type: 1111

After researching for a while, i found out that it happen because i miss to fill a parameter on mybatis xml query file. This is my xml file,

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="mybatistesting.MyMapper" >    
    <select resultType="java.util.Map" id="select" parameterType="java.util.Map">
        SELECT
            *
        FROM
            RISALAH
        WHERE UUID = #{UUID} AND CONTENT = #{CONTENT}
    </select>
</mapper>

And this is my main java file,

package mybatistesting;

import java.util.HashMap;
import java.util.Map;
import mybatistesting.config.MyBatisSqlSessionFactory;
import org.apache.ibatis.session.SqlSession;

public class MyBatisTesting {

    public static void main(String[] args) throws Exception {
        SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(true);
        MyMapper mapper = sqlSession.getMapper(MyMapper.class);
        Map map1 = new HashMap();
        map1.put("UUID", "1");
        for (Map map : mapper.select(map1)) {
            System.out.println("map "+map);
        }
    }
}

As you can see, i only set UUID as query parameter on my java file, despite that i need at least 2 parameter on my xml file. Here is how to fix it,

package mybatistesting;

import java.util.HashMap;
import java.util.Map;
import mybatistesting.config.MyBatisSqlSessionFactory;
import org.apache.ibatis.session.SqlSession;

public class MyBatisTesting {

    public static void main(String[] args) throws Exception {
        SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(true);
        MyMapper mapper = sqlSession.getMapper(MyMapper.class);
        Map map1 = new HashMap();
        map1.put("UUID", "1");
        map1.put("CONTENT", "1");
        for (Map map : mapper.select(map1)) {
            System.out.println("map "+map);
        }
    }
}

Hope it helps others, cheers 😉

A Simple MyBatis Caching using Redis

Mybatis recently introduce a new caching mechanism using Redis (http://mybatis.github.io/redis-cache/index.html), and in this tutorial i’m trying to create a simple demonstration for it. MyBatis’ Redis caching is a little bit different with other MyBatis’ caching provider, such as EhCache or OsCache because you need to install Redis server before using it.

Because Redis project does not officially support Windows and i need Redis to run as a windows service so im downloading redis-service.exe from this url (https://github.com/rgl/redis/downloads), install and run it. You can see Redis is running on port 6379.

Because MyBatis Redis Cache hasnt available on Maven repository, so next step is download MyBatis Redis Cache project from github (https://github.com/mybatis/redis-cache), build it into jar and install it manually into our pom.xml file from local folder.

Okay, so here is my pom.xml file, you can see im manually installing mybatis-redis from local repository,

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.edw</groupId>
    <artifactId>MybatisRedisExample</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    
    <repositories>
        <repository>
            <id>local</id>
            <url>file://${basedir}/lib</url>
        </repository>
    </repositories>
    
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.7</maven.compiler.source>
        <maven.compiler.target>1.7</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.2.2</version>
        </dependency>
        
        <dependency>
            <groupId>org.mybatis.caches</groupId>
            <artifactId>mybatis-redis</artifactId>
            <version>1.0.0-SNAPSHOT</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
            <version>2.3</version>
        </dependency>
        
        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
            <version>2.6.2</version>
        </dependency>
    </dependencies>
</project>

Now create a database and a simple table for example,

CREATE DATABASE `test`;
DROP TABLE IF EXISTS `testing`;
CREATE TABLE `testing` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `address` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;


INSERT INTO `testing` VALUES ('1', 'edw', '123');
INSERT INTO `testing` VALUES ('2', 'pepe', 'epep');
INSERT INTO `testing` VALUES ('3', 'dodol', 'dodll');

And a java bean for our table representation

package com.edw.mybatisredisexample.bean;

public class Testing {

    private String name;
    private String address;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Testing() {
    }
}

And an xml file and a java file, named TestingMapper to handle all the queries,

package com.edw.mybatisredisexample.mapper;

import com.edw.mybatisredisexample.bean.Testing;
import java.util.List;
import java.util.Map;

public interface TestingMapper {
    void insert(Testing testing);
    List<Map> select();
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.edw.mybatisredisexample.mapper.TestingMapper" >
    
    <cache type="org.mybatis.caches.redis.RedisCache" />
    
    <select id="insert" parameterType="com.edw.mybatisredisexample.bean.Testing" >
        insert into testing (name, address)
        values ( #{name,jdbcType=VARCHAR}, #{address,jdbcType=VARCHAR} )
    </select>
    
    <select resultType="java.util.Map" id="select" >
        SELECT
            *
        FROM
            testing
    </select>
</mapper>

Dont forget to register our xml file on MyBatis’ Configuration.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="UNPOOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost/test"/>
                <property name="username" value="root"/>
                <property name="password" value="password"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="TestingMapper.xml" />
    </mappers>
</configuration>

And a java class to load our MyBatis xml configuration

package com.edw.mybatisredisexample.config;

import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisSqlSessionFactory {

    private static final SqlSessionFactory FACTORY;

    static {
        try {
            Reader reader = Resources.getResourceAsReader("Configuration.xml");
            FACTORY = new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            throw new RuntimeException("Fatal Error. Cause: " + e, e);
        }
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return FACTORY;
    }
}

Next is creating our Redis connection configuration, redis.properties

redis.host=localhost
redis.port=6379
redis.timeout=5000
redis.password=
redis.namespace=edw
redis.database=0

After everything is ready, next is create a Main file

package com.edw.mybatisredisexample;

import com.edw.mybatisredisexample.config.MyBatisSqlSessionFactory;
import com.edw.mybatisredisexample.mapper.TestingMapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;

public class Main {

    private Logger logger = Logger.getLogger(this.getClass());

    public static void main(String[] args) throws Exception {
        new Main().testQuery();
    }

    private void testQuery() throws Exception {
        logger.debug("start ----");

        for (int i = 0; i < 10; i++) {
            SqlSession sqlSession = null;
            try {
                sqlSession = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(true);
                TestingMapper testingMapper = sqlSession.getMapper(TestingMapper.class);
                List<Map> maps = testingMapper.select();

                for (Map map : maps) {
                    logger.debug(map);
                }
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
            } finally {
                if (sqlSession != null) {
                    sqlSession.close();
                }
            }
            
            Thread.sleep(5000);
        }

        logger.debug("end ----");
    }
}

Using monitor command, this is what you will see on your Redis console,
redis console

And on my netbeans console,mybatis redis result log 2

It will keep showing the same value despite i’ve delete and update some values on database directly,
mysql data redis

You can see my complete sourcecode on my github page (https://github.com/edwinkun/MybatisRedisExample).