This is a short post on how to establish JDBC connectivity to a MySQL database when using the Java Vuser type within LoadRunner. Stuart describes a situation in which you might want to do this here. I’m not sure why but the example code is missing from his site, so I’ve supplemented with an example of how you’d implement this within LoadRunner.
The first thing you will need is the JDBC driver available here. Once you download the archive, you really just need the one jar file from that source. I’m currently using the file called mysql-connector-java-5.0.8-bin.jar
The next thing to do is copy that file into your LoadRunner
Now edit the run-time settings for your Java Vuser script (F4). You need to add the location of the jar file to your classpath parameters.
At the top of your script, depending on which functionality you end up using, you need to import the relevant packages for your script. I’m currently using the following import statements:
1 2 3 4 | import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.*; |
My public class called Actions now initializes a private Connection object called ‘connection’
1 | private Connection connection; |
My init() method will open up a single connection to the database using the following code.
1 2 3 4 5 6 7 8 9 | // Initialize DB connection connection = null; try { Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception ex) {} try { connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?" + "user=myuser&password=mypassword"); } catch (SQLException e) {} |
You may want to handle exceptions a little more thoroughly than me 
Now within the action() method, I can make repeated calls to the MySQL database, as each vuser iterates over the code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | String FORMATTED_MESSAGE = null; Statement stmt = null; ResultSet rs = null; try { stmt = connection.createStatement(); rs = stmt.executeQuery("SELECT xml FROM my_data"); while (rs.next()) { FORMATTED_MESSAGE = rs.getString("xml"); //lr.message(FORMATTED_MESSAGE); } rs.close(); stmt.close(); } catch (SQLException e) { } |
In this example I’m populating a string called FORMATTED_MESSAGE with the XML returned from a SQL query.
Putting it all together, your Java Vuser script should look something like this.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | import lrapi.lr; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.*; public class Actions { private Connection connection; public int init() { // Initialize DB connection connection = null; try { Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception ex) {} try { connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?" + "user=myuser&password=mypassword"); } catch (SQLException e) {} return 0; } //end of init public int action() throws JMSException { String FORMATTED_MESSAGE = null; Statement stmt = null; ResultSet rs = null; try { stmt = connection.createStatement(); rs = stmt.executeQuery("SELECT xml FROM mydata"); while (rs.next()) { FORMATTED_MESSAGE = rs.getString("xml"); //lr.message(FORMATTED_MESSAGE); } rs.close(); stmt.close(); } catch (SQLException e) { } return 0; } //end of action public int end() throws JMSException { connection = null; return 0; } //end of end } |
