I have Java (Axis2) webservice with Hibernate as DAL. Using InnoDB as the MySql engine.

I have few questions ergarding the performance: 1. usually the fetching is fast, but the time takes may vary, sometimes it takes too much time to query? What usually takes time to query is the following query: 1 Webservice request to get user info - we do it on users table, doing search - "where name like . 1 Webservice request to get user recipes (query recipe, where r.user.userId == userId, userId is given in the webservice) 2. How to configure c3p0 connection pool to get best performance?

这个取自冬眠.cfg

<property name="connection.autoReconnect">true</property>
  <property name="connection.autoReconnectForPools">true</property>
  <property name="connection.is-connection-validation-required">true</property>
  <property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>
  <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
  <property name="hibernate.show_sql">false</property>
  <property name="hibernate.current_session_context_class">thread</property>
  <property name="hibernate.format_sql">true</property>
  <property name="hibernate.use_sql_comments">true</property>
  <property name="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</property>

  <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
  <property name="hibernate.c3p0.min_size">3</property>
  <property name="hibernate.c3p0.max_size">50</property>
  <property name="hibernate.c3p0.timeout">280</property>
  <property name="hibernate.c3p0.max_statements">50</property>
  <!-- this property forces the revalidation of a connection after the given amount of time (in secs) -->
  <!-- it must be set to LESS than the wait_timout setting for the mysql server (this setting defaults to 28800 secs (8 hours)) -->
  <property name="hibernate.c3p0.idle_test_period">300</property>

Following are the hbm.xml files used when calling the 2 methods... This is user.hbm.xml:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 21:48:14 27/03/2012 by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
    <class name="com.icdb.data.User" table="user" catalog="yoavDB">
        <id name="userid" type="java.lang.Integer">
            <column name="userid" />
            <generator class="identity" />
        </id>
        <property name="birthdate" type="string">
            <column name="birthdate" length="50" not-null="true" />
        </property>
        <property name="password" type="string">
            <column name="password" length="100" not-null="true" />
        </property>
        <property name="firstname" type="string">
            <column name="firstname" length="50" not-null="true" />
        </property>
        <property name="lastname" type="string">
            <column name="lastname" length="50" not-null="true" />
        </property>
        <property name="country" type="string">
            <column name="country" length="100" not-null="true" />
        </property>
        <property name="email" type="string">
            <column name="email" length="100" not-null="true" />
        </property>
        <property name="numOfRecipes" type="int">
            <column name="numOfRecipes" not-null="true" />
        </property>
        <property name="picUrl" type="string">
            <column name="picUrl" length="200" />
        </property>
        <property name="rate" type="float">
            <column name="rate" precision="12" scale="0" not-null="true" />
        </property>
        <property name="fewWords" type="string">
            <column name="fewWords" length="200" not-null="true" />
        </property>
        <property name="whatscooking" type="string">
            <column name="whatscooking" length="45" not-null="true" />
        </property>
        <set name="usermessagesesForSenderUserId" table="usermessages" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="senderUserId" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Usermessages" />
        </set>
        <set name="usermessagesesForUserId" table="usermessages" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="userId" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Usermessages" />
        </set>
        <set name="timelines" table="timeline" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="userId" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Timeline" />
        </set>
        <set name="generaltipses" table="generaltips" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="authorid" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Generaltips" />
        </set>
        <set name="userlastvisitsForVisitedUserId" table="userlastvisit" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="visitedUserId" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Userlastvisit" />
        </set>
        <set name="recipes" table="recipe" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="ownerid" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Recipe" />
        </set>
        <set name="userlikeses" table="userlikes" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="userId" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Userlikes" />
        </set>
        <set name="friendshiptablesForUserBId" table="friendshiptable" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="userB_Id" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Friendshiptable" />
        </set>
        <set name="usersForFavUserId" table="userfavchefsync" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="userId" not-null="true" />
            </key>
            <many-to-many entity-name="com.icdb.data.User">
                <column name="favUserId" not-null="true" />
            </many-to-many>
        </set>
        <set name="friendshiptablesForUserAId" table="friendshiptable" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="userA_Id" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Friendshiptable" />
        </set>
        <set name="usersForUserId" table="userfavchefsync" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="favUserId" not-null="true" />
            </key>
            <many-to-many entity-name="com.icdb.data.User">
                <column name="userId" not-null="true" />
            </many-to-many>
        </set>
        <set name="activitylogsForObjectUserId" table="activitylog" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="objectUserId" />
            </key>
            <one-to-many class="com.icdb.data.Activitylog" />
        </set>
        <set name="userlastvisitsForGuestUserId" table="userlastvisit" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="guestUserId" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Userlastvisit" />
        </set>
        <set name="userrecipessyncs" table="userrecipessync" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="userId" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Userrecipessync" />
        </set>
        <set name="activitylogsForUserId" table="activitylog" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="userId" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Activitylog" />
        </set>
        <set name="recipereviews" table="recipereview" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="reviewerid" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Recipereview" />
        </set>
    </class>
</hibernate-mapping>

这是食谱. hbm. xml:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 21:48:14 27/03/2012 by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
    <class name="com.icdb.data.Recipe" table="recipe" catalog="yoavDB">
        <id name="recipeid" type="java.lang.Integer">
            <column name="recipeid" />
            <generator class="identity" />
        </id>
        <many-to-one name="user" class="com.icdb.data.User" fetch="select">
            <column name="ownerid" not-null="true" />
        </many-to-one>
        <property name="releasedate" type="timestamp">
            <column name="releasedate" length="19" not-null="true" />
        </property>
        <property name="preparationtime" type="int">
            <column name="preparationtime" not-null="true" />
        </property>
        <property name="recipedifficulty" type="int">
            <column name="recipedifficulty" not-null="true" />
        </property>
        <property name="name" type="string">
            <column name="name" length="50" not-null="true" unique="true" />
        </property>
        <property name="description" type="string">
            <column name="description" length="200" />
        </property>
        <property name="lastupdated" type="timestamp">
            <column name="lastupdated" length="19" not-null="true" />
        </property>
        <property name="servecount" type="java.lang.Integer">
            <column name="servecount" />
        </property>
        <property name="complete" type="boolean">
            <column name="complete" not-null="true" />
        </property>
        <property name="category" type="int">
            <column name="category" not-null="true" />
        </property>
        <property name="numOfViews" type="int">
            <column name="numOfViews" not-null="true" />
        </property>
        <property name="indexOfRecipeOfUser" type="int">
            <column name="indexOfRecipeOfUser" not-null="true" />
        </property>
        <property name="picUrl" type="string">
            <column name="picUrl" length="200" />
        </property>
        <property name="numOfLikes" type="int">
            <column name="numOfLikes" not-null="true" />
        </property>
        <property name="calculatedRate" type="float">
            <column name="calculatedRate" precision="12" scale="0" not-null="true" />
        </property>
        <set name="userlastvisits" table="userlastvisit" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="lastViewedRecipeId" />
            </key>
            <one-to-many class="com.icdb.data.Userlastvisit" />
        </set>
        <set name="recipeingredients" table="recipeingredient" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="recipeid" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Recipeingredient" />
        </set>
        <set name="recipereviews" table="recipereview" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="recipeid" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Recipereview" />
        </set>
        <set name="userlikeses" table="userlikes" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="recipeId" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Userlikes" />
        </set>
        <set name="activitylogs" table="activitylog" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="objectRecipeId" />
            </key>
            <one-to-many class="com.icdb.data.Activitylog" />
        </set>
        <set name="userrecipessyncs" table="userrecipessync" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="recipeId" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Userrecipessync" />
        </set>
        <set name="recipeinstructions" table="recipeinstruction" inverse="true" lazy="true" fetch="select">
            <key>
                <column name="recipeid" not-null="true" />
            </key>
            <one-to-many class="com.icdb.data.Recipeinstruction" />
        </set>
    </class>
</hibernate-mapping>

友好表格:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 21:48:14 27/03/2012 by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
    <class name="com.icdb.data.Friendshiptable" table="friendshiptable" catalog="yoavDB">
        <composite-id name="id" class="com.icdb.data.FriendshiptableId">
            <key-property name="userAId" type="int">
                <column name="userA_Id" />
            </key-property>
            <key-property name="userBId" type="int">
                <column name="userB_Id" />
            </key-property>
        </composite-id>
        <many-to-one name="userByUserAId" class="com.icdb.data.User" update="false" insert="false" fetch="select">
            <column name="userA_Id" not-null="true" />
        </many-to-one>
        <many-to-one name="userByUserBId" class="com.icdb.data.User" update="false" insert="false" fetch="select">
            <column name="userB_Id" not-null="true" />
        </many-to-one>
        <property name="status" type="short">
            <column name="status" not-null="true" />
        </property>
        <property name="statusLastChangedDate" type="date">
            <column name="statusLastChangedDate" length="10" not-null="true" />
        </property>
    </class>
</hibernate-mapping>

最后访问 :

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 21:48:14 27/03/2012 by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
    <class name="com.icdb.data.Userlastvisit" table="userlastvisit" catalog="yoavDB">
        <composite-id name="id" class="com.icdb.data.UserlastvisitId">
            <key-property name="guestUserId" type="int">
                <column name="guestUserId" />
            </key-property>
            <key-property name="visitedUserId" type="int">
                <column name="visitedUserId" />
            </key-property>
        </composite-id>
        <many-to-one name="userByVisitedUserId" class="com.icdb.data.User" update="false" insert="false" fetch="select">
            <column name="visitedUserId" not-null="true" />
        </many-to-one>
        <many-to-one name="userByGuestUserId" class="com.icdb.data.User" update="false" insert="false" fetch="select">
            <column name="guestUserId" not-null="true" />
        </many-to-one>
        <many-to-one name="recipe" class="com.icdb.data.Recipe" fetch="select">
            <column name="lastViewedRecipeId" />
        </many-to-one>
        <property name="lastVisitTimeStamp" type="timestamp">
            <column name="lastVisitTimeStamp" length="19" not-null="true" />
        </property>
    </class>
</hibernate-mapping>

获取用户Info 的 Java 代码 :

public String[] getUserInfo(    int visitingUserId,
                                int visitedUserId )
{
    Session session = ICDBHibernateUtil.getSessionFactory().getCurrentSession();
    try
    {
        session.beginTransaction();

        User                user    = (User) session.get(User.class, visitedUserId);
        ArrayList<String>   ret     = new ArrayList<String>();

        // Username
        ret.add( user.getFirstname() + " " + user.getLastname() );

        // Few words
        ret.add( user.getFewWords() );

        // Num of recipes
        ret.add( Integer.toString( user.getNumOfRecipes() ) );

        // Calculated Rank
        ret.add( Float.toString( user.getRate() ) );

        // Pic url
        ret.add( user.getPicUrl() );

        FriendshiptableId   id = new FriendshiptableId( visitingUserId, visitedUserId );
        Friendshiptable     ft = (Friendshiptable)session.get( Friendshiptable.class, id );
        if( ft == null )
        {
            id = new FriendshiptableId( visitedUserId, visitingUserId );
            ft = (Friendshiptable)session.get( Friendshiptable.class, id );
            if( ft == null )
            {
                ret.add( "none" );
            }
            else
            {
                FriendshipRequestStatusEnum status = FriendshipRequestStatusEnum.values()[ ft.getStatus() ];
                switch( status )
                {
                    case PENDING:
                        ret.add( "pending" );
                        break;

                    case ACCEPTED:
                        ret.add( "accepted" );
                        break;

                    case DECLINED:
                        ret.add( "declined" );
                        break;
                }
            }
        }
        else
        {
            FriendshipRequestStatusEnum status = FriendshipRequestStatusEnum.values()[ ft.getStatus() ];
            switch( status )
            {
                case PENDING:
                    ret.add( "pending" );
                    break;

                case ACCEPTED:
                    ret.add( "accepted" );
                    break;

                case DECLINED:
                    ret.add( "declined" );
                    break;
            }
        }

        if( visitingUserId != -1 )
        {
            // Has new stuff....
            UserlastvisitId ulvId   = new UserlastvisitId( visitingUserId, visitedUserId );
            Userlastvisit   ulv     = (Userlastvisit) session.get( Userlastvisit.class, ulvId );
            if( ulv == null )
            {
                // Lets add a new entry
                User visitingUser   = (User) session.load( User.class, visitingUserId );
                User visitedUser    = (User) session.load( User.class, visitedUserId );

                ulv = new Userlastvisit(    ulvId, 
                                            visitingUser, 
                                            visitedUser, 
                                            new Date() );
                session.save( ulv );

                ret.add( "true" );
            }
            else
            {
                List<?> loginResult= session.createQuery("from Recipe r where r.lastupdated >= :lastVisited" )
                        .setDate( "lastVisited", ulv.getLastVisitTimeStamp() )
                        .list();
                if( loginResult.size() > 0 )
                {
                    ret.add( "true" );
                }
                else
                {
                    ret.add( "false" );
                }

                // We need to update the last visit time...
                ulv.setLastVisitTimeStamp( new Date() );
                session.save( ulv );
            }
        }

        session.getTransaction().commit();

        String[] retStr = new String[ret.size()];
        ret.toArray( retStr );

        return retStr;
    }
    catch( RuntimeException e )
    {
        ICDBHibernateUtil.getSessionFactory().getCurrentSession().getTransaction().rollback();
        throw e;
    }
}

lackuserRecipes 方法 :

public String[] getUserRecipesNew(  int     visitingUserId,
                                        int     userId,     
                                        int     pageNumber )
    {
        final int       PAGE_SIZE   = 20;

        Session session = ICDBHibernateUtil.getSessionFactory().getCurrentSession();
        try
        {
            session.beginTransaction();

            String hql = "from Recipe r where r.user.userid= :userid";

            Query query = session.createQuery( hql )
                                .setInteger(    "userid",       userId );

            query = query.setFirstResult( PAGE_SIZE * (pageNumber - 1) );
            query.setMaxResults( PAGE_SIZE );

            List<?> recipes = query.list();

            ArrayList<String> ret = new ArrayList<String>();
            if( recipes.size() == PAGE_SIZE )
            {
                // Indicates that might be more results
                ret.add( "true" );
            }
            else
            {
                // No more results
                ret.add( "false" );
            }

            DateFormat df = new SimpleDateFormat("MM/dd/yyyy");

            // Format the Recipes into string
            for( int i = 0; i < recipes.size(); i++ )
            {
                UserlikesId     userLikeId  = new UserlikesId( userId, ((Recipe)recipes.get(i)).getRecipeid() );
                Userlikes       userLike    = (Userlikes) session.get( Userlikes.class, userLikeId );

                ret.add( Integer.toString( ((Recipe)recipes.get(i)).getRecipeid() ) );
                ret.add( ((Recipe)recipes.get(i)).getName() );
                ret.add( ((Recipe)recipes.get(i)).getDescription() );
                ret.add( ((Recipe)recipes.get(i)).getUser().getFirstname() + ((Recipe)recipes.get(i)).getUser().getLastname() );
                ret.add( Integer.toString( ((Recipe)recipes.get(i)).getRecipedifficulty() ) );
                ret.add( Integer.toString( ((Recipe)recipes.get(i)).getServecount() ) );
                ret.add( Integer.toString( ((Recipe)recipes.get(i)).getPreparationtime() ) );
                ret.add( Integer.toString( ((Recipe)recipes.get(i)).getUser().getUserid() ) );
                ret.add( Integer.toString( ((Recipe)recipes.get(i)).getNumOfViews() ) );
                ret.add( df.format( ((Recipe)recipes.get(i)).getReleasedate() ) );
                ret.add( (((Recipe)recipes.get(i)).getPicUrl() == null) ? "" : ((Recipe)recipes.get(i)).getPicUrl() );
                ret.add( Integer.toString( ((Recipe)recipes.get(i)).getCategory() ) );
                ret.add( (userLike==null)?"0":(userLike.isIsLike()?"1":"0") );
                ret.add( Integer.toString( ((Recipe)recipes.get(i)).getNumOfLikes() ) );
            }

            session.getTransaction().commit();

            String[] retStr = new String[ret.size()];
            ret.toArray( retStr );

            return retStr;
        }
        catch( RuntimeException e )
        {
            ICDBHibernateUtil.getSessionFactory().getCurrentSession().getTransaction().rollback();
            throw e;
        }
    }

Is everything written OK? How should tune it to better performance?