How to make SQL join statements generic
I need to execute multiple SQL queries in different methods of the same class. Is there a way to make these statements publicly available and I can use the same con, statement variable in all my query execution methods.
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection ("jdbc:mysql://localhost:3306/kamal","root","root");
Statement statement=con.createStatement();
source to share
use this method in your class and call it over and over
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;
}
source to share
You could write join statements in a static method that can be reused in 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 the disadvantage of having to manually manage the opening and closing of database connections.
To alleviate the aforementioned drawback, consider using an object relationship mapping framework like Hibernate, which will abstract the connection details with a settings file that will be reused for every database connection.
If you need a variable in the whole class, you can make it a member variable.
However, this is not recommended for resources such as Connections
, because it can easily rob the system and also add additional load.
What you can do is use a design template 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
This now helps us in several ways, especially if your application is multithreaded. We only need to make one connection, which will remain if the program has not been interrupted. Everywhere you need to create a new one Statement
, you can just use that.
ConnectionManager.getInstance().getConnection().createStatement();
source to share
Here's a very naive connection pool implementation. Keep in mind that this was written with notepad and hasn't 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;
}
}
source to share
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);
}
}
source to share