ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ingmar Lötzsch <iloetz...@asci-systemhaus.de>
Subject Re: IBatis Array feild insertion problem in postgres database()Please help me)
Date Mon, 12 Oct 2009 07:56:27 GMT
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:rjishnu@gmail.com]
>> Sent: Friday, October 09, 2009 10:49 AM
>> To: user-java@ibatis.apache.org
>> 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 rjishnu@gmail.com
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>>
>>>
>>>
>> --
>> 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: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>> 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: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
>>
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org


Mime
View raw message