SitePoint Sponsor |
|
User Tag List
Results 1 to 1 of 1
-
Jan 31, 2005, 10:12 #1
- Join Date
- Jan 2005
- Location
- portsmouth
- Posts
- 31
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Using mySQL connection pooling with JDBC
I have a page that i am doing for some coursework for my degree. I have completed all the code and it works fine but i have discovered that I need to use connection pooling. I have a rough idea of what connection pooling is but do not know how connection pooling works. The jsp code below is what i have so far. Does anyone have any idea how I could implement connection pooling into the mySQL statement?
This is my connection page
Code:<% // FileName="mysql_jdbc_conn.htm" // Type="JDBC" "" // DesigntimeType="JDBC" // HTTP="true" // Catalog="" // Schema="" String MM_connection_DRIVER = "org.gjt.mm.mysql.Driver"; String MM_connection_USERNAME = "root"; String MM_connection_PASSWORD = "devv8555"; String MM_connection_STRING = "jdbc:mysql://localhost/vehicle_man_db"; %>
Code:MM_editDriver = MM_connection_DRIVER; MM_editConnection = MM_connection_STRING; MM_editUserName = MM_connection_USERNAME; MM_editPassword = MM_connection_PASSWORD; MM_editTable = "vehicle_man_db.owner_details"; MM_editRedirectUrl = "view_owner.jsp"; String MM_fieldsStr = "select_title|value|txt_other_title|value|txt_forname|value|txt_surname|value|txt_address|value|txt_town|value|select_county|value|txt_postcode|value|txt_year|value|txt_email_address|value|txt_telephone_num|value|txt_driving_lic_num|value|txt_password|value"; String MM_columnsStr = "title|',none,''|other_title|',none,''|forename|',none,''|surname|',none,''|address|',none,''|town|',none,''|county|',none,''|postcode|',none,''|DOB|',none,''|email_address|',none,''|telephone_num|none,none,NULL|driving_lic_num|',none,''|password|',none,''"; // create the MM_fields and MM_columns arrays java.util.StringTokenizer tokens = new java.util.StringTokenizer(MM_fieldsStr,"|"); MM_fields = new String[tokens.countTokens()]; for (int i=0; tokens.hasMoreTokens(); i++) MM_fields[i] = tokens.nextToken(); tokens = new java.util.StringTokenizer(MM_columnsStr,"|"); MM_columns = new String[tokens.countTokens()]; for (int i=0; tokens.hasMoreTokens(); i++) MM_columns[i] = tokens.nextToken(); // set the form values for (int i=0; i+1 < MM_fields.length; i+=2) { MM_fields[i+1] = ((request.getParameter(MM_fields[i])!=null)?(String)request.getParameter(MM_fields[i]):""); } // append the query string to the redirect URL if (MM_editRedirectUrl.length() != 0 && request.getQueryString() != null) { MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1)?"?":"&") + request.getQueryString(); } } %> <% // *** Insert Record: construct a sql insert statement and execute it if (request.getParameter("MM_insert") != null) { // create the insert sql statement StringBuffer MM_tableValues = new StringBuffer(), MM_dbValues = new StringBuffer(); for (int i=0; i+1 < MM_fields.length; i+=2) { String formVal = MM_fields[i+1]; String elem; java.util.StringTokenizer tokens = new java.util.StringTokenizer(MM_columns[i+1],","); String delim = ((elem = (String)tokens.nextToken()) != null && elem.compareTo("none")!=0)?elem:""; String altVal = ((elem = (String)tokens.nextToken()) != null && elem.compareTo("none")!=0)?elem:""; String emptyVal = ((elem = (String)tokens.nextToken()) != null && elem.compareTo("none")!=0)?elem:""; if (formVal.length() == 0) { formVal = emptyVal; } else { if (altVal.length() != 0) { formVal = altVal; } else if (delim.compareTo("'") == 0) { // escape quotes StringBuffer escQuotes = new StringBuffer(formVal); for (int j=0; j < escQuotes.length(); j++) if (escQuotes.charAt(j) == '\'') escQuotes.insert(j++,'\''); formVal = "'" + escQuotes + "'"; } else { formVal = delim + formVal + delim; } } MM_tableValues.append((i!=0)?",":"").append(MM_columns[i]); MM_dbValues.append((i!=0)?",":"").append(formVal); } MM_editQuery = new StringBuffer("insert into " + MM_editTable); MM_editQuery.append(" (").append(MM_tableValues.toString()).append(") values ("); MM_editQuery.append(MM_dbValues.toString()).append(")"); if (!MM_abortEdit) { // finish the sql and execute it Driver MM_driver = (Driver)Class.forName(MM_editDriver).newInstance(); Connection MM_connection = DriverManager.getConnection(MM_editConnection,MM_editUserName,MM_editPassword); PreparedStatement MM_editStatement = MM_connection.prepareStatement(MM_editQuery.toString()); MM_editStatement.executeUpdate(); MM_connection.close(); // redirect with URL parameters if (MM_editRedirectUrl.length() != 0) { response.sendRedirect(response.encodeRedirectURL(MM_editRedirectUrl)); return; } } }
Bookmarks