How to make SQL connection statements common

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);  
            }  
        }

Yes, you can make SQL connection statements common and reuse the connection and statement objects across different methods within the same class.

Here's how you can do it in Java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseConnector {
    private Connection con;
    private Statement statement;

    public DatabaseConnector() {
        try {
            // Load the MySQL JDBC driver
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            // Establish connection
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/kamal", "root", "root");
            // Create a statement object
            statement = con.createStatement();
        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e) {
            e.printStackTrace();
        }
    }

    // Method to execute SQL queries
    public void executeQuery(String query) {
        try {
            statement.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // Method to close connection
    public void closeConnection() {
        try {
            if (statement != null)
                statement.close();
            if (con != null)
                con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // Example method using the common connection and statement
    public void exampleMethod() {
        String query = "SELECT * FROM tableName";
        executeQuery(query);
        // Process the result or perform other operations
    }

    // Other methods can also use executeQuery method or define their own logic
}

Make them stored procedures in your database.