Announcement Announcement Module
No announcement yet.
JDBC Connection Properties Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • JDBC Connection Properties


    I am setting to a BasicDataSouce to connectto a SQL Server 2005 database.
    I want to set the packet size for the communication with the database.

    Here is my XML:
     <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        	<property name="driverClassName" value=""/>
            <property name="url" value=""/>
            <property name="username" value=""/>
            <property name="password" value=""/>
            <property name="maxActive" value=""/>
            <property name="maxWait" value=""/>
            <property name="maxIdle" value=""/>
            <property name="minIdle" value=""/>
            <property name="initialSize" value=""/>
            <property name="validationQuery" value=""/>
            <property name="testOnBorrow" value=""/>
            <property name="testOnReturn" value=""/>        
            <property name="testWhileIdle" value=""/>        
            <property name="timeBetweenEvictionRunsMillis" value=""/>        
            <property name="numTestsPerEvictionRun" value=""/>        
            <property name="connectionProperties">
            		<prop key="packetSize">32767</prop>
    However, when I run this I get the following error:

    Error setting property values; nested exception is org.springframework.beans.NotWritablePropertyExcep tion: Inva
    lid property 'connectionProperties' of bean class [org.apache.commons.dbcp.BasicDataSource]: Bean property 'connectionProperties' is not writable or has an in
    valid setter method: Does the parameter type of the setter match the return type of the getter?

    Am I setting up the connection properties incorrectly?

    According to the following connectionProperties is a valid property of the BasicDataSource:

    Many Thanks

  • #2
    The problem seems to be the property you are trying to set is a protected field. There is no public setter as required.


    • #3
      The property is indeed protected and also doesn't have a getter/setter for those properties. However it has an addConnectionProperty method which is public.

      You could subclass the BasicDataSource and create 2 setter methods for your properties and udnerneath call the addConnectionProperty. For what I know/see is that if you would call create a setConnectionProperties method you would override/erase all the already existing properties (which you set earlier in your bean).


      • #4
        Adding setters seems like a good work around. Would be worth voting however to get this fixed in Spring!


        • #5
          Thanks for the replies.

          I'm not sure what you mean by
          You could subclass the BasicDataSource and create 2 setter methods for your properties and udnerneath call the addConnectionProperty
          I don't understand the reference to addConnectionProperty.

          Could you please elaborate.

          From what I understand 1 option is to create a subclass for BasicDataSourec, add a getter and setter for connectionProperties and in the set method set the value of the connectionProperties object equal to the object passed in.

          Not sure where addConnectionProperty comes in though. Is this an alternative to use?

          Many Thanks


          • #6
            All the other properties you set username, password, url etc. are also going into connectionProperties. If you now create a setConnectionProperties which will just set the connectionProperties to the passed in value, the properties set earlier (username, password, url) wouldn't be there anymore.

            So create 1 setter method for your property and then call addConnectionProperty which will add your property to the already existing connectionProperties

            import org.apache.commons.dbcp.BasicDataSource;
            public class ExtendedBasicDataSource extends BasicDataSource {
                public void setPacketSize(String size) {
                    addConnectionProperty("packetSize", size);


            • #7
              Thanks mdeinum, I understand now.

              But can I inject the value of the packet size into the setPacketSize method?

              Won't spring be expecting to find a private variable called packetSize? Should I just add one to overcome this?

              Also when spring injects a value - does it run whatever you put into the set method for that property regardless of what that is?

              Thanks again


              • #8
                Spring uses normal JaveBeans spec. So it looks for a setter with a void in it's signature. You don't need to have a (private) parameter to make this work. It will work.

                In the code I posted I used a String as a parameter but you can also make this a long, int, whatever. As long as there is a PropertyEditor registered for it (for the default types/classes they are) the values will automatically be converted.

                Check the reference guide and the javadocs to get some more insight in this.


                • #9
                  ...or more generic:

                  public void setConnectionProperties(Properties props) {
                    for (Iterator iter = props.entrySet().iterator(); iter.hasNext(); ) {
                      Map.Entry entry = (Map.Entry);
                      addConnectionProperty((String) entry.getKey(), entry.getValue());


                  • #10
                    The generic option seems like a good way to go. On a side note, it's only when you see code working with Properties, you remember why; has a and is a is such an important distinction to make.


                    • #11
                      Thanks for those suggestions.

                      Got it working. Running an application across a WAN and was expecting the performance to improve when I increased the packet size. Strangely enough it didn't.

                      However, I did change the poolPreparedStatements to true and this seems to have had s very positive impact on performance.

                      I have read about this setting and the documentation advises using it if you are executing the same statement over and over again. Is there anything I should consider before setting this property to true? Anybody have any problems with pooled statements?

                      Also, does anyone have any other suggestions for improving the performance of the BasicDataSource?

                      Many Thanks


                      • #12
                        I think the performance improvements you can introduce are general enough for any connection pool. The thing you really need to do is profile the application though with any changes you make. As you already said, one of the setting you thought would improve things didn't . You can obviously play around with the pool size (min/max/initial/etc...), max open prepared statements, testing strategy (borrow/return/idle), etc.......

                        I would have expected one of the biggest performance issues will simply be how many trips you are making over the network. Caching could be an option in general. Obviously well crafted SQL is just common sense. Batching statements where possible. General things.........


                        • #13
                          pooling of prepared statement is not always very effective - its usability heavily depend on statements usage pattern and on database in use. Pool efforts to determine if statement is alrady pooled na to reuse it may (or may not) be much comparable or event bigger then DB saves on avoiding reparsing. So you have to test.

                          BTW, there is a funny bug in Oracle JDBC driver (all available versions) that causes a weird exception ("Missing IN or OUT parameter at index:: ..."
                          ) if you try to use pooled prepared statements that use "return generated keys" functionality.



                          • #14
                            Thanks for your ideas / suggestions.

                            Just wondering with the BasicDataSource if there is anyway to specify the batch size?

                            I am using a SQL Server 2005 database but there doesn't seem to be any driver property for batch size:

                            Also, is the autocommit setting relevant to Batch statements? I presume that the Batch statement will always commit itself once completed. There are some sites on the web which say that the autocommit setting should be switched off. But this seems to be a messy thing to do when using Batch statements????

                            Last edited by dreamer54; Feb 6th, 2007, 10:39 AM.


                            • #15
                              I'm not sure you can do it on the dataSource. If you use BatchPreparedStatementSetter, one of the methods to implement is the required batch size.

                              As for autocommit, I've read the same posts as you have. Do you really want to be auto committing things anyway?

                              This line disables auto-commit mode for the Connection object con so that the transaction will not be automatically committed or rolled back when the method executeBatch is called. (If you do not recall what a transaction is, you should review the section "Transactions" on page 323.) To allow for correct error handling, you should always disable auto-commit mode before beginning a batch update.

                              BTW, I thought of another performance improvement. I don't know what drive you are using, but we found jTDS to greatly improve the performance of our data access times.