object | edwin's journey

How to Convert Java List or ArrayList into ResultSet

By edwin / 18 August 2010 18 August 2010 / Java, Programming / Java, object, Programming, utility

At this project i found an obstacle, which i need to convert a reporting module from fetching data from database into fetching data from Webservices. At first I think it was easy, I just fetch strings from Webservice, and put it in a bean or in a map and put it all in an ArrayList, just like JasperReport. But I forgot, this isn’t JasperReport I’m using, I’m using ~~bloody hell~~ CrystalReport. And my Crystal-Report-calling method only accepting ResultSet for parameter, so somehow I need to convert strings from webservice into ResultSet.

My idea is adding strings from webservices into an ArrayList<Object> and adding this ArrayList into ArrayList<ArrayList<Object>> and converting it into rows of ResultSet. And im using MockRunner library to help me. This is how I do it :

I create a class to convert a list into result set

package com.edw.rs;

import com.mockrunner.mock.jdbc.MockResultSet;
import java.sql.ResultSet;
import java.util.List;

/**
 *
 * @author edw
 */
public class MyResultSet {

    public ResultSet getResultSet(List<String> headers, List<List<Object>> data) throws Exception {

        // validation
        if (headers == null || data == null) {
            throw new Exception("null parameters");
        }

        if (headers.size() != data.size()) {
            throw new Exception("parameters size are not equals");
        }

        // create a mock result set
        MockResultSet mockResultSet = new MockResultSet("myResultSet");

        // add header
        for (String string : headers) {          
            mockResultSet.addColumn(string);
        }

        // add data
        for (List<Object> list : data) {
            mockResultSet.addRow(list);
        }

        return mockResultSet;
    }
}

and i tested it with my junit class

package com.edw.rs.test;

import com.edw.rs.MyResultSet;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import org.junit.Test;

/**
 *
 * @author edw
 */
public class MyResultSetTest {

    private Logger logger = Logger.getLogger(MyResultSetTest.class);

    public MyResultSetTest() {
    }

    @Test
    public void testMyResultSet() throws Exception {
        MyResultSet myResultSet = new MyResultSet();
        List<String> headers = new ArrayList<String>();
        headers.add("id");
        headers.add("name");
        headers.add("salary");
        headers.add("age");

        List<List<Object>> data = new ArrayList<List<Object>>();
        for (int i = 0; i < 4; i++) {
            List<Object> objects = new ArrayList<Object>();
            objects.add(new Integer(i));
            objects.add("name " + i);
            objects.add(new Double(i));
            objects.add(new Integer(20 + i));
            data.add(objects);
        }

        ResultSet rs = myResultSet.getResultSet(headers, data);
        while (rs.next()) {
            logger.debug(rs.getString("id"));
            logger.debug(rs.getString(2));
            logger.debug(rs.getDouble("salary"));
            logger.debug(rs.getInt(4));
            logger.debug("------------------------");
        }

    }
}

this is the result on my IDE console

2010-08-18 18:13:09,112 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:42 - 0
2010-08-18 18:13:09,131 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:43 - name 0
2010-08-18 18:13:09,132 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:44 - 0.0
2010-08-18 18:13:09,132 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:45 - 20
2010-08-18 18:13:09,133 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:46 - ------------------------
2010-08-18 18:13:09,133 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:42 - 1
2010-08-18 18:13:09,189 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:43 - name 1
2010-08-18 18:13:09,189 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:44 - 1.0
2010-08-18 18:13:09,189 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:45 - 21
2010-08-18 18:13:09,190 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:46 - ------------------------
2010-08-18 18:13:09,190 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:42 - 2
2010-08-18 18:13:09,191 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:43 - name 2
2010-08-18 18:13:09,218 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:44 - 2.0
2010-08-18 18:13:09,219 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:45 - 22
2010-08-18 18:13:09,219 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:46 - ------------------------
2010-08-18 18:13:09,219 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:42 - 3
2010-08-18 18:13:09,220 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:43 - name 3
2010-08-18 18:13:09,225 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:44 - 3.0
2010-08-18 18:13:09,226 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:45 - 23
2010-08-18 18:13:09,226 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:46 - ------------------------

this is my project structure, FYI im using Netbeans 6.9.

all i can say is, Thank You mockrunner, you saved my life.

Menyimpan Objek Java di Database

By edwin / 27 April 2010 27 April 2010 / Java, Programming / database, Java, javabean, mysql, object, Programming

Untuk menyimpan suatu java object ke table, dibutuhkan field dengan tipe data BLOB, berikut adalah cara menyimpan java object (dalam hal ini javabean) kedalam database, gw pake JDK 6.0 dan MySQL 5. Misalkan ada javabean dengan nama BeanTest (usahakan selalu implements Serializable), dengan struktur dibawah ini :

import java.io.Serializable;

public class BeanTest implements Serializable {

	private String nama;
	private String alamat;

	/**
	 * @return the nama
	 */
	public String getNama() {
		return nama;
	}

	/**
	 * @param nama
	 *            the nama to set
	 */
	public void setNama(String nama) {
		this.nama = nama;
	}

	/**
	 * @return the alamat
	 */
	public String getAlamat() {
		return alamat;
	}

	/**
	 * @param alamat
	 *            the alamat to set
	 */
	public void setAlamat(String alamat) {
		this.alamat = alamat;
	}

}

lalu buat table di database

create database testest;
use testest;

CREATE TABLE IF NOT EXISTS x (
satu varchar(5) NOT NULL DEFAULT ” ,
dua blob ,
PRIMARY KEY (satu)
);

Lalu buatlah class yang akan digunakan untuk berhubungan dengan database, gunakan method setData() untuk memasukkan object ke database, dan getData() untuk mengambil data dari database.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Main {

	public static void main(String[] args) {
		Main main = new Main();

		// simpan data ke database
		main.setData();

		//tarik isi database
		main.getData();
	}

	private void setData() {
		try {

			BeanTest bean = new BeanTest();

			bean.setNama("edwin");
			bean.setAlamat("jakarta");

			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testest", "root", "");
			java.sql.PreparedStatement statement = con.prepareStatement("insert into x values (?,?)");

			statement.setString(1, "00001");
			statement.setBytes(2, toBytes(bean));

			statement.executeUpdate();

			statement.close();
			con.close();
		} catch (Exception ex) {
			Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
		}
	}

	private byte[] toBytes(Object object) {
		java.io.ByteArrayOutputStream baos = new java.io.ByteArrayOutputStream();
		try {
			java.io.ObjectOutputStream oos = new java.io.ObjectOutputStream(baos);
			oos.writeObject(object);
			oos.flush();
			oos.close();
		} catch (Exception ioe) {
			ioe.getMessage();
		}

		return baos.toByteArray();
	}

	private void getData() {
		try {
			Class.forName("com.mysql.jdbc.Driver");

			Connection con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/testest", "root", "");
			Statement statement = con.createStatement();
			ResultSet res = statement.executeQuery("select * from x");

			while (res.next()) {
				Object o = toObject(res.getBytes(2));

				if (o instanceof BeanTest) {
					BeanTest beanTest = (BeanTest) o;
					System.out.println(beanTest.getNama());
					System.out.println(beanTest.getAlamat());

				}
			}
			statement.close();
			con.close();
		} catch (Exception ex) {
			Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
		}
	}

	private Object toObject(byte[] bytes) {
		Object object = null;
		try {
			object = new java.io.ObjectInputStream(
					new java.io.ByteArrayInputStream(bytes)).readObject();
		} catch (Exception cnfe) {
			cnfe.getMessage();
		}
		return object;
	}
}

sekian dan terima kasih,

Wassalam.