I have to execute several SQL queries in different methods of the same class. Is there any way to make these statements common and can I use the same con,statement variable in all the methods to execute queries.
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection ("jdbc:mysql://localhost:3306/kamal","root","root");
Statement statement=con.createStatement();
use this method in your class and call it again and again
public Connection getMyConnection() throws ClassNotFoundException, SQLException
{
String connectionURL = "jdbc:mysql://localhost:3306/test";
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(connectionURL, "root", "root");
return con;
}
You could compose the connection statements in a static method, which can be re-used in your other classes:
public Connection getConnection() throws ClassNotFoundException, SQLException {
String connURL = "jdbc:mysql://localhost:3306/test";
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(connURL, "username", "password");
return con;
}
But this has has a disadvantage where you will have to manage the opening and closing of the database connection(s) manually.
To alleviate the above disadvantage, consider using an object relation mapping framework like Hibernate, which will abstract the connection details to a settings file that will be re-used for each database connection.
If you require a variable throughout the class, you might want to make it a member variable.
However, this is discouraged for resources like Connections, because it can easily deprive a system, as well as put extra load.
What you can do is use a design pattern called Singleton. Read about it here.
Basically, you can create a new class called ConnectionManager with this implementation
class ConnectionManager {
private static ConnectionManager _instance = null;
private Connection con = null;
protected ConnectionManager() {
//empty
}
private void init() {
Class.forName("com.mysql.jdbc.Driver").newInstance();
this.con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/kamal","root","root");
}
public Connection getConnection() {
return this.con;
}
public static ConnectionManager getInstance() {
if(_instance == null) {
_instance = new ConnectionManager();
_instance.init();
}
return _instance;
}
}//end class
Now, this helps us in a number of ways, especially if your application is multi-threaded. We only need to make one connection, which will remain unless the program has been terminated. Everywhere you need to create a new Statement, you can simply use this.
ConnectionManager.getInstance().getConnection().createStatement();
Here's a very naive implementation of connection pooling. Be aware that this was written using notepad and it has not been tested:
public interface ConnectionPool {
public Connection getConnection() throws SQLException;
public void closeConnection(Connection connection) throws SQLException;
}
public class MySQLConnectionPool implements ConnectionPool {
private static final Class<?> mysqlDriver;
private final Stack<Connection> connections;
private final String url;
private final String user;
private final String password;
private final int maxSize;
static {
mysqlDriver = Class.forName("com.mysql.jdbc.Driver");
}
public MySQLConnectionPool(String url, String user, String password, int initialSize, int size) {
if (initialSize > size) {
throw new IllegalArgumentException("Pool initial size must not be greater than size");
}
if (size <= 0) {
throw new IllegalArgumentException("Pool size must be greater than zero");
}
this.size = maxSize;
this.url = url;
this.user = user;
this.password = password;
this.connections = new Stack<Connection>();
try {
for (int i = 0;i < initialSize;i++) {
connections.push(getConnection(url, user, password));
}
} catch (Exception exception) {
// TODO: Log somewhere?
}
}
public Connection getConnection(String url, user, password) throws SQLException {
DriverManager.getConnection(url, user, password);
}
public Connection getConnection() SQLException {
try {
synchronized (connections) {
return connections.pop();
}
} catch (EmptyStackException exception) {
return getConnection(url, user, password);
}
}
public void closeConnection(Connection connection) throws SQLException {
synchronized (connections) {
if (connections.size() < maxSize) {
connections.push(connection);
return;
}
}
connection.close();
}
}
public class SingletonMYSQLConnectionPool extends MySQLConnectionPool() {
private static volatile SingletonMYSQLConnectionPool instance;
private SingletonMYSQLConnectionPool() {
super("jdbc:mysql://localhost:3306/kamal","root","root", 0, 2);
}
public static SingletonMYSQLConnectionPool getInstance() {
if (instance == null) {
synchronized (SingletonMYSQLConnectionPool.class) {
if (instance == null) {
instance = new SingletonMYSQLConnectionPool();
}
}
}
return instance;
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
PrintWriter pw = response.getWriter();
Connection conn;
try
{
String fname = request.getParameter("fname");
String lname = request.getParameter("lname");
Class.forName("com.mysql.jdbc.Driver");
conn = (Connection)DriverManager.getConnection("jdbc:mysql://localhost:3307/soft\",\"root\",\"root");
PreparedStatement pst = (PreparedStatement) conn.prepareStatement("insert into soft.IT(fname,lname) values(?,?)");
pst.setString(1,fname);
pst.setString(2,lname);
int i = pst.executeUpdate();
if(i!=0){
pw.println("<br>Record has been inserted");
}
else
{
pw.println("failed to insert the data");
}
}catch (Exception e)
{
pw.println(e);
}
}
