Can you post the exceptions?
Can you do the following:
On SQL-client (Console/pgAdmin)
CREATE TABLE arraytest
(
id int NOT NULL PRIMARY KEY,
intarray int[] NOT NULL
)
WITHOUT OIDS;
INSERT INTO arraytest (id, intarray) VALUES (1, ARRAY[1, 2]);
INSERT INTO arraytest (id, intarray) VALUES (2, '{1, 3}' :: int[]);
Can you than execute the following code using class IntArray:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class ArrayTest
{
public static void main(String[] args)
{
try
{
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost/test";
Connection con = DriverManager.getConnection(url,
"postgres", "admin");
String sql = "INSERT INTO arraytest (id, intarray)
VALUES (?, ?)";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 3);
int[] ints = {2, 4};
IntArray intArray = new IntArray(ints);
pstmt.setArray(2, intArray);
int rows = pstmt.executeUpdate();
System.out.println(rows);
pstmt.close();
con.close();
}
catch (Exception e)
{
e.printStackTrace(System.out);
}
}
}
Do you get any exceptions?
jishnu123 schrieb:
> I have already implemented all java beans with SqlArrayAdapter,IntArray and
> IntArrayTypeHandler..But iam still facing the same problem....Actually i had
> implemented my project using spring mvc with iBatis..I think spring MVC
> problem...
>
> I again mention that particular error as follows....Any body have any idea
> to solve that problem... Please sent reply immediately...
>
> INFO [org.springframework.beans.factory.xml.XmlBeanDefinitionReader] -
> <Loading XML bean definitions from class path resource
> [org/springframework/jdbc/support/sql-error-codes.xml]>
> INFO [org.springframework.jdbc.support.SQLErrorCodesFactory] -
> <SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL,
> Oracle, PostgreSQL, Sybase]>
>
>
> Rahul Saluja wrote:
>> Hi Jishnu,
>>
>> I cannot understand anything by given exception but yes suggested
>> implementation by Ingmar is rite approach and moreover which version of
>> Postgres you are using as in 7.3.1 it is nlot possible to store the array
>> data so better way of doing it is to convert the data in String and push
>> it as varchar and while retrieving back convert back it into your
>> required datatype array here is the example you can use for insertion.
>>
>>
>>
>>
>>
>> import java.sql.SQLException;
>> import java.sql.Types;
>> import java.util.Vector;
>>
>> import com.hns.hss.nmf.common.util.unilogger.LogFactory;
>> import com.hns.hss.nmf.common.util.unilogger.LoggerIf;
>> import com.ibatis.sqlmap.client.extensions.ParameterSetter;
>> import com.ibatis.sqlmap.client.extensions.ResultGetter;
>> import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
>>
>> public class ByteArrayToTimeStampHandler implements TypeHandlerCallback
>> {
>> private static LoggerIf log_ =
>> LogFactory.getLoggerIf(ArraysToStringTypeHandler.class.getName());
>>
>>
>>
>> public Object getResult(ResultGetter arg0) throws SQLException
>> {
>> // TODO Auto-generated method stub
>> return null;
>> }
>>
>>
>> public void setParameter(ParameterSetter setter, Object parameter)
>> throws SQLException
>> {
>> if (parameter == null)
>> {
>> setter.setNull(Types.CHAR);
>> }
>> else
>> {
>> setter.setString(ArraysToString(parameter));
>> }
>> // TODO Auto-generated method stub
>>
>> }
>>
>>
>> public Object valueOf(String arg0)
>> {
>> // TODO Auto-generated method stub
>> return null;
>> }
>>
>>
>> private String ArraysToString(Object obj)
>> {
>> if (obj == null)
>> {
>> throw new IllegalArgumentException("Could not
>> convert null to a String value. " + "Valid argument is an array of type
>> I_U8 Only ");
>> }
>>
>> else if (obj instanceof
>> com.hns.hss.nmf.server.log.manager.gensrc.Common.I_U8[])
>> {
>>
>> com.hns.hss.nmf.server.log.manager.gensrc.Common.I_U8[] ourData =
>> (com.hns.hss.nmf.server.log.manager.gensrc.Common.I_U8[]) obj;
>> int length = ourData.length;
>> Vector byteVector = new Vector();
>> //byte[] newByteArray = new byte[length-1];
>> int count = 0;
>>
>> for (int i = 0; i < length; i++)
>> {
>>
>> if (ourData[i].getValue() == (byte) '\0')
>> //\0 byte value
>> {
>> log_.debug(" NULL CHAR FOUND ");
>> break;
>> }
>> else
>> {
>>
>> byteVector.add(ourData[i].getValue());
>> count++;
>> }
>> }
>> log_.debug(" Byte Vector " + byteVector);
>> String finalHexString = new String();
>> StringBuffer finalString = new StringBuffer();
>> //String returnString = new String();
>> try
>> {
>> finalHexString =
>> ByteArrayToTimeStampHandler.getHexString(byteVector);
>>
>>
>>
>>
>> ByteArrayToTimeStampHandler.hexToString(finalHexString, finalString);
>> log_.debug("finalString is " +
>> finalString.toString());
>>
>>
>> if ( ourData[0].getValue() != (byte)45 )
>> //Not a Negative Sign
>> {
>> log_.debug(" NOT A NEGATIVE SIGN ");
>> returnString = " " + finalString;
>>
>>
>> }else // Negative Sign
>> {
>> returnString = "-" +
>> finalString;
>>
>> }
>>
>> }
>> catch (Exception e)
>> {
>> e.printStackTrace();
>> }
>>
>> String checkString = finalString.toString();
>> return checkString;
>>
>> }
>> else
>> {
>>
>> return null;
>> }
>> //return null;
>>
>> }
>>
>>
>> public static final void hexToString(String hex, StringBuffer out)
>> {
>> if (hex == null) return;
>> int length = hex.length() & -4;
>> for (int pos = 0; pos < length; pos += 4)
>> {
>> int this_char = 0;
>> try
>> {
>> this_char = Integer.parseInt(hex.substring(pos, pos + 4),
>> 16);
>> }
>> catch (NumberFormatException NF_Ex)
>> {
>> }
>> out.append((char) this_char);
>> }
>> }
>>
>> public static String getHexString(Vector vector) throws Exception
>> {
>>
>> String result = "00";
>> short[] b = new short[vector.size()];
>>
>> for (int j = 0; j < vector.size(); j++)
>> {
>>
>> b[j] = ((Short) vector.get(j)).shortValue();
>> }
>> for (int i = 0; i < b.length; i++)
>> {
>> result += Integer.toString((b[i] & 0xff) + 0x100,
>> 16).substring(1);
>> result += "00";
>> }
>>
>> return result.substring(0, result.length() - 2);
>> }
>> }
>> -----Original Message-----
>> From: jishnu123 [mailto:[email protected]]
>> Sent: Friday, October 09, 2009 10:49 AM
>> To: [email protected]
>> Subject: Re: IBatis Array feild insertion problem in postgres
>> database()Please help me)
>>
>>
>>
>> Thank you for your reply...I have already implemented type handler and
>> all
>> classes as follows...But i got the the same error as follows....Please
>> help
>> me.....I didn't try array feild selection....I have been trying to
>> insertion
>> of array feild...
>>
>> I have to mention again that error as follows....Any body get an idea
>> please
>> reply immediately..
>>
>> INFO [org.springframework.beans.factory.xml.XmlBeanDefinitionReader] -
>> <Loading XML bean definitions from class path resource
>> [org/springframework/jdbc/support/sql-error-codes.xml]>
>> INFO [org.springframework.jdbc.support.SQLErrorCodesFactory] -
>> <SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL,
>> Oracle, PostgreSQL, Sybase]>
>>
>>
>> Ingmar Lötzsch wrote:
>>> You can use a type handler and implement java.sql.Array. Example:
>>>
>>> 1st: abstract superclass as adapter
>>>
>>> public abstract class SqlArrayAdapter
>>> implements Array
>>> {
>>> public Object getArray() throws SQLException
>>> {
>>> // Auto-generated method stub
>>> return null;
>>> }
>>>
>>> public Object getArray(long index, int count) throws SQLException
>>> {
>>> // Auto-generated method stub
>>> return null;
>>> }
>>>
>>> public Object getArray(long index, int count, Map<String, Class< ?
>>> map) throws SQLException
>>> {
>>> // Auto-generated method stub
>>> return null;
>>> }
>>>
>>> public Object getArray(Map<String, Class< ? >> map) throws
>>> SQLException
>>> {
>>> // Auto-generated method stub
>>> return null;
>>> }
>>>
>>> public int getBaseType() throws SQLException
>>> {
>>> // Auto-generated method stub
>>> return 0;
>>> }
>>>
>>> public String getBaseTypeName() throws SQLException
>>> {
>>> // Auto-generated method stub
>>> return null;
>>> }
>>>
>>> public ResultSet getResultSet() throws SQLException
>>> {
>>> // Auto-generated method stub
>>> return null;
>>> }
>>>
>>> public ResultSet getResultSet(long index, int count) throws
>>> SQLException
>>> {
>>> // Auto-generated method stub
>>> return null;
>>> }
>>>
>>> public ResultSet getResultSet(long index, int count, Map<String,
>>> Class<
>>> ? >> map) throws SQLException
>>> {
>>> // Auto-generated method stub
>>> return null;
>>> }
>>>
>>> public ResultSet getResultSet(Map<String, Class< ? >> map) throws
>>> SQLException
>>> {
>>> // Auto-generated method stub
>>> return null;
>>> }
>>> }
>>>
>>> 2nd: implementation of java.sql.Array for use with int[] or
>>> Collection<Integer>
>>>
>>> public class IntArray
>>> extends SqlArrayAdapter
>>> {
>>> private static final Integer[] emptyArray = new Integer[0];
>>>
>>> private int[] array;
>>>
>>> public IntArray(int[] array)
>>> {
>>> if (array == null)
>>> {
>>> throw new IllegalArgumentException("parameter array
>>> should not be
>>> null");
>>> }
>>> this.array = array;
>>> }
>>>
>>> public IntArray(Collection<Integer> set)
>>> {
>>> if (set == null)
>>> {
>>> throw new IllegalArgumentException("parameter set
>>> should not be null");
>>> }
>>> Integer[] keys = set.toArray(emptyArray);
>>>
>>> this.array = new int[keys.length];
>>> for (int i = 0; i < keys.length; ++i)
>>> {
>>> Integer key = keys[i];
>>> this.array[i] = key.intValue();
>>> }
>>> }
>>>
>>> @Override
>>> public int getBaseType()
>>> // throws SQLException
>>> {
>>> return Types.INTEGER;
>>> }
>>>
>>> /**
>>> * This method is called by driver ver. 8 but not by ver. 7.
>>> */
>>> @Override
>>> public String getBaseTypeName()
>>> // throws SQLException
>>> {
>>> return "int4";
>>> }
>>>
>>> /**
>>> * This method is called by both drivers ver. 8 and 7.
>>> */
>>> @Override
>>> public String toString()
>>> {
>>> String result = "{";
>>> for (int i = 0; i < this.array.length; ++i)
>>> {
>>> if (i > 0)
>>> {
>>> result += ",";
>>> }
>>> result += this.array[i];
>>> }
>>> result += "}";
>>> return result;
>>> }
>>>
>>> public void free()
>>> // throws SQLException
>>> {
>>> this.array = null;
>>> }
>>> }
>>>
>>> 3rd: implementation of TypeHandlerCallback
>>>
>>> public class IntArrayTypeHandler
>>> implements TypeHandlerCallback
>>> {
>>> public void setParameter(ParameterSetter setter, Object parameter)
>>> throws SQLException
>>> {
>>> Collection<Integer> keys = (Collection<Integer>) parameter;
>>> IntArray intArray = new IntArray(keys);
>>> setter.setArray(intArray);
>>> }
>>>
>>> public Object getResult(ResultGetter getter)
>>> throws SQLException
>>> {
>>> Array array = getter.getArray();
>>> return array;
>>> }
>>>
>>> public Object valueOf(String string)
>>> {
>>> return string;
>>> }
>>> }
>>>
>>> 4th: add the type handler to your configuration
>>> <typeAlias alias="IntArrayTypeHandler"
>>> type="com.asci.common.ibatis.IntArrayTypeHandler" />
>>>
>>> 5th: SQL map
>>>
>>> <select id="selectFahrgastById" parameterClass="map" resultMap="...">
>>> SELECT
>>> k.id,
>>> ...
>>> FROM kunden AS k
>>> WHERE k.id = ANY (#fahrgastIds,handler=IntArrayTypehandler#)
>>> </select>
>>>
>>> 6th: DAO
>>>
>>> public List<Fahrgast> selectFahrgastById(Set<Integer> fahrgastIds)
>>> {
>>> HashMap<String, Object> params = new HashMap<String, Object>();
>>> params.put("fahrgastIds", fahrgastIds);
>>> List<Fahrgast> list = getSqlMapClientTemplate().queryForList(
>>> "datenerfassung.selectFahrgastById", params);
>>> return list;
>>> }
>>>
>>> jishnu123 schrieb:
>>>> Hai,
>>>>
>>>> I have to tried insertion of array feild in postgres database
>>>> using
>>>> spring mvc with iBatis.I got two lines error ...like
>>>>
>>>> Error as follows...
>>>>
>>>>
>>>> [org.springframework.beans.factory.xml.XmlBeanDefinitionReader] -
>>>> <Loading
>>>> XML bean definitions from class path resource
>>>> [org/springframework/jdbc/support/sql-error-codes.xml]>
>>>> [org.springframework.jdbc.support.SQLErrorCodesFactory] -
>>>> <SQLErrorCodes
>>>> loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle,
>>>> PostgreSQL,
>>>> Sybase]>
>>>>
>>>>
>>>> Its urgent ....Please help me........Any one has an idea please
>>>> reply
>>>> immediately otherwise sent to my emaild id [email protected]
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [email protected]
>>> For additional commands, e-mail: [email protected]
>>>
>>>
>>>
>> --
>> View this message in context:
>> http://www.nabble.com/IBatis-Array-feild-insertion-problem-in-postgres-database%28%29Please-help-me%29-tp25799490p25815639.html
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>> The information contained in this e-mail is private & confidential and may
>> also be legally privileged. If you are not the intended recipient, please
>> notify us, preferably by e-mail, and do not read, copy or disclose the
>> contents of this message to anyone.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>>
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
