时间戳

首页 > 解决方案 > 爪哇；如何制作 SQL getConnection(); 作为主要变量/素数？

java - 爪哇；如何制作 SQL getConnection(); 作为主要变量/素数？

问题描述

我想问一下如何使 Connection con = getConnection(); 成为一个引物或主要变量，这样它就不会连接到每个函数的 SQL 数据库。因为正如您在我的代码中看到的那样，每个函数/类都有 Connection con = getConnection(); 所以它连接到数据库的每个功能。它使我的程序进程缓慢。请帮忙谢谢。

package march30;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;

public class sqltesting {

public static void main(String[] args) throws Exception {
    // TODO Auto-generated method stub
        get();
}

public static void lookup() throws Exception{
    try {
        Connection con = getConnection();
        PreparedStatement statement = con.prepareStatement("SELECT first,last FROM tablename where id=6");
        ResultSet result = statement.executeQuery();

        if (result.next()) {
            System.out.println("First Name: " + result.getString("first"));
            System.out.println("Last Name: " + result.getString("last"));
            }
        else {
            System.out.println("No Data Found");
            }

    } catch (Exception e) {}        
}

public static ArrayList<String> get() throws Exception{
    try {
        Connection con = getConnection();
        PreparedStatement statement = con.prepareStatement("SELECT first,last FROM tablename");

        ResultSet result = statement.executeQuery();

        ArrayList<String> array = new ArrayList<String>();
        while (result.next()) {
            System.out.print(result.getString("first"));
            System.out.print(" ");
            System.out.println(result.getString("last"));

            array.add(result.getString("last"));
        }
        System.out.println("All records have been selected!");
        System.out.println(Arrays.asList(array));
        return array;

    } catch (Exception e) {System.out.println((e));}    
    return null;
}

public static void update() throws Exception{
    final int idnum = 2;
    final String var1 = "New";
    final String var2 = "Name";
    try {
        Connection con = getConnection();
        PreparedStatement updated = con.prepareStatement("update tablename set first=?, last=? where id=?");
        updated.setString(1, var1);
        updated.setString(2, var2);
        updated.setInt(3, idnum);
        updated.executeUpdate();
        } catch (Exception e) {System.out.println((e));}        
    finally{
        System.out.println("Update Completed");
        }
}

public static void delete() throws Exception{
    final int idnum = 7;
    try {
        Connection con = getConnection();
        PreparedStatement deleted = con.prepareStatement("Delete from tablename where id=?");
        deleted.setInt(1, idnum);
        deleted.executeUpdate();
        } catch (Exception e) {System.out.println((e));}        
    finally{
        System.out.println("Delete Completed");
        }
}

public static void post() throws Exception{
    final String var1 = "Albert";
    final String var2 = "Reyes";
    try {
        Connection con = getConnection();
        PreparedStatement posted = con.prepareStatement("INSERT INTO tablename (first, last) VALUES ('"+var1+"', '"+var2+"')");
        posted.executeUpdate();
    } catch (Exception e) {System.out.println((e));}        
    finally{
        System.out.println("Insert Completed");
        }
}

public static void createTable() throws Exception {
    try {
        Connection con = getConnection();
        PreparedStatement create = con.prepareStatement("CREATE TABLE IF NOT EXISTS tablename(id int NOT NULL AUTO_INCREMENT, first varchar(255), last varchar(255), PRIMARY KEY(id))");
        create.executeUpdate();
    } catch (Exception e) {System.out.println((e));}
    finally{
        System.out.println("Function Completed");
        }
}

public static Connection getConnection() throws Exception{
    try {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://xxxxxxxx.amazonaws.com:3306/pointofsale";
        String username = "xxxxx";
        String password = "xxxxxx";
        Class.forName(driver);

        Connection conn = DriverManager.getConnection(url,username,password);
        return conn;
    } catch (Exception e)   {System.out.println(e);}        

    return null;
}
}

标签： javamysqlmain

解决方案

要解决您的问题，您需要在 sqltesting 类中存储一个 Connection 对象作为类成员。然后，您需要引用 Connection 对象而不是 getConnection()。还值得一提的是 Connection 是一个 AutoClosable 对象，所以你需要在完成后关闭这个资源，IE；关于申请处置。您可能还需要考虑使用像 Hikari 或 C3P0 这样的连接池 API，这样您就可以在需要时打开和关闭连接，而不是长时间打开 1 个连接。

class SQLTesting {

    private final Connection connection;

    public SQLTesting(Connection connection) {
        this.connection = connection;
    } 

    public SQLTesting() {
        this(getConnection());
    }

    // other methods

    private Connection getConnection() {
        // your method to create connection, rename to createConnection maybe. 
    }
}

推荐阅读