Announcement Announcement Module
Collapse
No announcement yet.
BoneCP / JDBC / PostgreSql Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • BoneCP / JDBC / PostgreSql

    Hello,

    I'm trying to set up java web service on my server (CentOS Linux) but I have a problem of numbers of connections on my Postgresql database.

    In Postgresql, the parameter "max_connections" is 100.

    In my beans.xml file, I set this:

    Code:
    <bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
            <property name="driverClass" value="${jdbc.driverClass}"/>
            <property name="jdbcUrl" value="${jdbc.url}"/>
            <property name="username" value="${jdbc.username}"/>
            <property name="password" value="${jdbc.password}"/>
            <property name="idleConnectionTestPeriodInMinutes" value="60"/>
            <property name="idleMaxAgeInSeconds" value="60"/>
            <property name="maxConnectionsPerPartition" value="50"/>
            <property name="minConnectionsPerPartition" value="5"/>
            <property name="partitionCount" value="1"/>
            <property name="acquireIncrement" value="5"/>
        </bean>
    After a number of web services call, I get the following error:

    Code:
    HTTP ERROR 500
     
    Could not get JDBC Connection; nested exception is org.postgresql.util.PSQLException : FATAL :  désolé, trop de clients sont dé
    Is it my configuration BoneCP that is incorrect? If yes, how to correct it?

    I read the documentation, but I'm lost.

    thank you

    Vince

  • #2

    After a number of web services call, I get the following error:
    Fix your transactions. Without a proper transaction setup connections aren't being released and hang indefinitely or until they time out. I suggest a read of the transaction chapter of the reference guide and use the forum search as that question has been answered numerous times before.

    Comment


    • #3
      I search on a lot if sites, and I don't find a response.

      have you a thread on this forum with a response for me ?

      I begin with spring, and I don't use Transaction

      here an exampe of my code :

      Code:
      public class JDBCVersionDAO implements VersionDAO {
      
      	private JdbcTemplate jdbcTemplate;
      
      	
      	public void setDataSource(DataSource dataSource) {
      		this.jdbcTemplate = new JdbcTemplate(dataSource);
      	}
      	
      
      	/**
      	 * @return the jdbcTemplate
      	 */
      	public JdbcTemplate getJdbcTemplate() {
      		return jdbcTemplate;
      	}
      
      	// Récupération des versions par rubrique
      	public List<Version> getVersion() {
      		String SQL = "SELECT * FROM version";
      		List<Version> versions = null;
      		try {
      			versions = this.jdbcTemplate.query(SQL, new RowMapper<Version>() {
      				// using anonymous inner class RowMapper here
      				public Version mapRow(ResultSet rs, int rowNum)
      						throws SQLException {
      					Version v = new Version();
      					v.setVersion(rs.getLong("version"));
      					v.setRubrique(rs.getString("rubrique"));
      					return v;
      				}
      			}, new Object[] {});
      
      			this.jdbcTemplate.getDataSource().getConnection().close();
      			
      		} catch (SQLException e) {
      			// TODO Auto-generated catch block
      			return null;
      		}
      
      		return versions;
      	}
      
      	// Récupération de la version d'une rubrique
      	public long getVersionByRubrique(String rubrique) {
      		long result = this.jdbcTemplate.queryForLong(
      				"SELECT version FROM version WHERE rubrique=?", rubrique);
      
      		return result;
      	}
      
      }
      Thank you

      vince

      Comment


      • #4
        Originally posted by vince56 View Post

        Code:
        		try {
        ....			
                                    this.jdbcTemplate.getDataSource().getConnection().close();
        			
        		} catch (SQLException e) {
        			// TODO Auto-generated catch block
        			return null;
        		}
        You don't need those lines of code.

        Comment


        • #5
          At the beginning of the project, I haven't those lines of code.
          I try again without those lines of code, but i think the result will be the same.

          I do the tests, and I confirm, the result is the same before delete those lines of code
          Last edited by vince56; Jun 22nd, 2012, 07:32 AM.

          Comment


          • #6
            On my project, my web services launch this class :

            Code:
            public UserAccountResource() {
            		super();
            		this.appContext = new ClassPathXmlApplicationContext(
            				"com/wpf/eyebuy/db/beans.xml");
            
            		this.userAccountDAO = (JDBCUserAccountDAO) appContext
            				.getBean("UserAccountDAO");
            }
            
            @POST
            	@Path("/login")
            	@Produces(MediaType.APPLICATION_JSON)
            	public Response login(@FormParam("userId") long userId,
            			@FormParam("password") String password) throws IOException {
            		Map<String, Object> m = new HashMap<String, Object>();
            
            		
            		if(userId == 0 || password == "" || password == null){
            			m.put("errcode", "01020");
            		}else{
            			UserAccount ua = this.userAccountDAO.getUserAccountById(userId);
            			if(ua == null){
            				m.put("errcode", "02006");
            			}else{
            				if (ua.getStatus() == 10) {
            					if (ua.getPassword().equals(password)) {
            						// TODO quoi faire ??
            						m.put("errcode", "00000");
            					} else {
            						m.put("errcode", "02003");
            					}
            				} else {
            					m.put("errcode", "02002");
            				}
            			}
            		}
            		
            		return Response.ok(m, MediaType.APPLICATION_JSON).build();
            	}
            Can you say me if it's a good practice ?

            Thank you.

            Vince
            Last edited by vince56; Jun 22nd, 2012, 09:24 AM.

            Comment


            • #7
              Hello Vince

              With the best intentions, I suggest you read carefully about the MVC architecture pattern, is a bad practice call from a "controller" directly to the DAO objects

              Why you are including this?

              Code:
              public UserAccountResource() {
              		super();
              		this.appContext = new ClassPathXmlApplicationContext(
              				"com/wpf/eyebuy/db/beans.xml");
              
              		this.userAccountDAO = (JDBCUserAccountDAO) appContext
              				.getBean("UserAccountDAO");
              }
              I suggest you read about how Spring manage the beans in the ApplicationContext

              Comment


              • #8
                Hello dr_pompeii,

                Is there a better solution :

                my class UserAccount :

                Code:
                private JdbcTemplate jdbcTemplate;
                	
                	public void setDataSource(DataSource dataSource) {
                		this.jdbcTemplate = new JdbcTemplate(dataSource);
                	}
                
                	/**
                	 * @return the jdbcTemplate
                	 */
                	public JdbcTemplate getJdbcTemplate() {
                		return this.jdbcTemplate;
                	}
                	
                	/*
                	 * Fetches a userAccount depending on its ID
                	 */
                	public UserAccount getUserAccountById(long userId) {
                		String SQL = "SELECT * FROM compte" + " WHERE idcompte = ?";
                		try {
                			return (UserAccount) this.jdbcTemplate.queryForObject(SQL,
                					new UserAccountMapper(), new Object[] { userId });
                		} catch (EmptyResultDataAccessException e) {
                			// this will return a null if the query cant find any countried with
                			// code given by the user
                			return null;
                		}
                	}
                	
                	// static Inner Class implementing RowMapper to map object
                		private static final class UserAccountMapper implements
                				RowMapper<UserAccount> {
                			public UserAccount mapRow(ResultSet rs, int rowNum) throws SQLException {
                				UserAccount a = new UserAccount();
                				a.setUserId(rs.getLong("idcompte"));
                				a.setName(rs.getString("nom"));
                				a.setSurname(rs.getString("prenom"));
                				a.setDob(rs.getDate("datenaissance"));
                				a.setPhone(rs.getString("numtelephone"));
                				a.setEmail(rs.getString("email"));
                				a.setPassword(rs.getString("password"));
                				a.setStatus(rs.getInt("idstatut"));
                				a.setValidationCode(rs.getString("codevalidation"));
                				a.setCreationDate(rs.getTimestamp("datecreation"));
                				a.setLastEdited(rs.getTimestamp("datemodification"));
                				a.setDeletionDate(rs.getTimestamp("datesuppression"));
                				a.setMailNotif(rs.getBoolean("notifmail"));
                				a.setSmsNotif(rs.getBoolean("notifsms"));
                				a.setOptIn(rs.getBoolean("optin"));
                				a.setGeolocalization(rs.getBoolean("geolocalisation"));
                				return a;
                			}
                		}
                And my resource class :

                Code:
                //UserAccount ua = this.userAccountDAO.getUserAccountById(userId);
                UserAccount ua1 = new UserAccount();
                UserAccount ua = ua1.getUserAccountById(userId);
                I delete those lines on my resource classe :
                Code:
                this.appContext = new ClassPathXmlApplicationContext(
                		"com/wpf/eyebuy/db/beans.xml");
                this.userAccountDAO = (JDBCUserAccountDAO) appContext
                		.getBean("UserAccountDAO");
                Thank you

                Vince
                Last edited by vince56; Jun 25th, 2012, 07:27 AM.

                Comment

                Working...
                X