java 如何使 SQL 连接语句通用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14914494/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make SQL connection statements common
提问by sincerekamal
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.
我必须在同一类的不同方法中执行多个 SQL 查询。有什么方法可以使这些语句通用，我可以在所有方法中使用相同的 con,statement 变量来执行查询。
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection ("jdbc:mysql://localhost:3306/kamal","root","root");
Statement statement=con.createStatement();
回答by Hussain Akhtar Wahid 'Ghouri'
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;
}
回答by sincerekamal
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.
为了减轻上述缺点，可以考虑使用像 Hibernate 这样的对象关系映射框架，它将连接细节抽象为一个设置文件，该文件将被重新用于每个数据库连接。
回答by Achrome
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.
但是，对于像 之类的资源Connections，不鼓励这样做，因为它很容易剥夺系统，并增加额外的负载。
What you can do is use a design pattern called Singleton. Read about it here.
您可以做的是使用称为Singleton. 在这里阅读。
Basically, you can create a new class called ConnectionManagerwith this implementation
基本上，您可以创建一个ConnectionManager使用此实现调用的新类
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.
现在，这在很多方面对我们有帮助，特别是如果您的应用程序是多线程的。我们只需要建立一个连接，除非程序被终止，否则它会一直存在。在您需要创建新的任何地方Statement，您都可以简单地使用它。
ConnectionManager.getInstance().getConnection().createStatement();
回答by Sami Korhonen
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;
}
}
回答by Harshad Patel
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);
}
}
回答by Dan Bracuk
Make them stored procedures in your database.
使它们在您的数据库中存储过程。
