I am working on my first Java Project with MySQL. I have one function that gets called every time I get data back from my data source. This function should save a new line to my MySQL database. See the code here:
import java.sql.*;
import java.util.Properties;
/**
*
* @author jeffery
*/
public class SaveToMysql {
// The JDBC Connector Class.
private static final String dbClassName = "com.mysql.jdbc.Driver";
private static final String CONNECTION = "jdbc:mysql://localhost/test";
static public String test(int reqId, String date, double open, double high, double low,
double close, int volume, int count, double WAP, boolean hasGaps){
if (date.contains("finished")){
return "finished";
}
// Class.forName(xxx) loads the jdbc classes and
// creates a drivermanager class factory
try{
Class.forName(dbClassName);
}catch ( ClassNotFoundException e ) {
System.out.println(e);
}
// Properties for user and password. Here the user and password are both 'paulr'
Properties p = new Properties();
p.put("user","XXXXXXXX");
p.put("password","XXXXXXXXXXXx");
// Now try to connect
Connection conn;
try{
conn = DriverManager.getConnection(CONNECTION,p);
}catch(SQLException e){
return e.toString();
}
PreparedStatement stmt;
try{
stmt = conn.prepareStatement("insert into dj_minute_data set symbol = (select ticker from dow_jones_constituents where id = ?), "
+ "date = str_to_date(?,'%Y%m%d %H:%i:%s')" +
", open = ?" +
", high = ?" +
", low = ?" +
", close = ?" +
", volume = ?" +
", adj_close = ?");
stmt.setInt(1, reqId);
stmt.setString(2, date);
stmt.setDouble(3, open);
stmt.setDouble(4, high);
stmt.setDouble(5, low);
stmt.setDouble(6, close);
stmt.setDouble(7, volume);
stmt.setDouble(8, WAP);
}catch (SQLException e){
return e.toString();
}
try{
stmt.executeUpdate();
}catch (SQLException e){
return e.toString();
}
return stmt.toString();
}
}
As you all can see this function test is in its own class, called SaveToMysql. To call this function, I import the class into a different class, and use this syntax:
msg = SaveToMysql.test(reqId, date, open, high, low, close, volume, count, WAP, hasGaps);
The msg then get output to the screen. Showing either error message or success.
This function may be called many times rapidly in a short time period. I know I should not have to re-open my connection with the MySQL server every time the function gets called. How would I change this so that the 1 MySQL connection stays open for every call to the function.
Thanks!!
解决方案
you need to manage one static class or method.
like
public class ConnectionClass
{
public static Connection connection = null;
public Connection getCurrentConnection()
{
if(connection != null)
{
return connection;
}
else
{
create new connection...
and return that new connection after giving it to global connection..
}
}
}
by this every time you will get current connection. and if there is some issue and connection is not available then you can create new connection. when you need connection object you just need to call getCurrentConnection method.
so you just need to following things in ur code.
Connection conn;
try{
//conn = DriverManager.getConnection(CONNECTION,p);
conn = getCurrentConnection();
}catch(SQLException e){
return e.toString();
}