0

I have a simple java swing application which interacts with a sqlite database through the spring JdbcTemplate. The problem appears when trying to dump an excel document to one table in the database, the batchUpdate methods takes a lot of time, around 40 secs for 300 excel rows. My configuration is this

The DAO class

public int[] addAll(List<Pedido> p) {

    int[] a = jdbcTemplate.batchUpdate("Insert into producto values(?,?,?)", new BatchPreparedStatementSetter() {

        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {

            Pedido ped = p.get(i);
            ps.setString(1, ped.getCodigo());
            ps.setDouble(2, ped.getPrecio());
            ps.setString(3, ped.getNombre());

        }

        @Override
        public int getBatchSize() {
            return p.size();
        }
    });
    return a;

}

My configuration file

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" 
          "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"
              destroy-method="close">
        <property name="driverClassName" value="org.sqlite.JDBC" />
        <property name="url" value="jdbc:sqlite:toruso.sqlite" />
        <property name="initialSize" value="1" />        
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dataSource" />
    </bean>        
    <bean id="pedidoDAO" class="toruso.model.DAO.PedidoDAOImplementation">
        <property name="jdbcTemplate" >
            <ref local="jdbcTemplate"/>
        </property> 
    </bean>
</beans>

My Spring loader class

public class MainFrame extends javax.swing.JFrame {

    private ClassPathXmlApplicationContext springContext;
    PedidoDAO pedidoDao;

    public MainFrame() {
        initComponents();
        this.initSpringContainer();
    }

    public void initSpringContainer() {
        String[] contextPaths = new String[]{"configuration/appConf.xml"};
        springContext = new ClassPathXmlApplicationContext(contextPaths);
        BeanFactory factory = this.springContext;
        this.pedidoDao = (PedidoDAO) factory.getBean("pedidoDAO");
    }
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
denny
  • 23
  • 5
  • So the code works but is just slow. Are you sure that the DB `INSERT` is causing the slowness -- how are you parsing the Excel file? – Mick Mnemonic Jan 16 '16 at 03:24
  • Also, have a look at [Why Spring's jdbctemplate.batchUpdate so slow](http://stackoverflow.com/questions/20360574/why-springs-jdbctemplate-batchupdate-so-slow). – Mick Mnemonic Jan 16 '16 at 03:32
  • i am usin jxl library to parse, but i checked and that is not the problem. The thing is when inserting, looks like spring makes an independent transaction for each insert, which should not be. I will check the post you say. Tnks – denny Jan 17 '16 at 01:33

0 Answers0