Java 数据库连接

Java DB connection

提问by Neil Cuenza

I have a java project with many files that connects to the database. Can anyone tell me if it is possible to use a java class file for connecting to the database so that I won't create a Database connection for every file and please teach me how.. thanks for the help :D Here's the code I used but it didn't work

我有一个 java 项目，其中包含许多连接到数据库的文件。谁能告诉我是否可以使用 java 类文件连接到数据库，这样我就不会为每个文件创建数据库连接，请教我如何..感谢您的帮助：D 这是我使用的代码但它没有用

dbConnect.java - class file

dbConnect.java - 类文件

    public class dbConnect {

        public static void connect(){
        Connection conn;
        Statement stmt;
        ResultSet rs;

        String sql;
            conn = null;
            String url = "jdbc:mysql://localhost:3306/db_oopproject";
            String driver = "com.mysql.jdbc.Driver";
            try{
                Class.forName(driver).newInstance();
                conn = DriverManager.getConnection(url,"user","12345");

                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
                sql = "Select * from user_account";
                rs = stmt.executeQuery(sql);

            }
            catch (Exception e){
                System.out.print(e.getMessage());
            }
        }


    }

I called this class in the main file using this dbConnect.connect(); Is there anything wrong with the code?

我使用这个 dbConnect.connect(); 在主文件中调用了这个类；代码有什么问题吗？

回答by duffymo

Put the database connection code in a single class and use it wherever you like.

将数据库连接代码放在一个类中，并在您喜欢的任何地方使用它。

Something like this can be a good start:

像这样的事情可能是一个好的开始：

package persistence;

import java.sql.*;
import java.util.*;

/**
 * util.DatabaseUtils
 * User: Michael
 * Date: Aug 17, 2010
 * Time: 7:58:02 PM
 */
public class DatabaseUtils {
    private static final String DEFAULT_DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String DEFAULT_URL = "jdbc:oracle:thin:@host:1521:database";
    private static final String DEFAULT_USERNAME = "username";
    private static final String DEFAULT_PASSWORD = "password";
/*
    private static final String DEFAULT_DRIVER = "org.postgresql.Driver";
    private static final String DEFAULT_URL = "jdbc:postgresql://localhost:5432/party";
    private static final String DEFAULT_USERNAME = "pgsuper";
    private static final String DEFAULT_PASSWORD = "pgsuper";
*/
/*
    private static final String DEFAULT_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DEFAULT_URL = "jdbc:mysql://localhost:3306/party";
    private static final String DEFAULT_USERNAME = "party";
    private static final String DEFAULT_PASSWORD = "party";
*/

    public static void main(String[] args) {
        long begTime = System.currentTimeMillis();

        String driver = ((args.length > 0) ? args[0] : DEFAULT_DRIVER);
        String url = ((args.length > 1) ? args[1] : DEFAULT_URL);
        String username = ((args.length > 2) ? args[2] : DEFAULT_USERNAME);
        String password = ((args.length > 3) ? args[3] : DEFAULT_PASSWORD);

        Connection connection = null;
        // No, I loaded the driver as I intended.  It's correct.  The edit is not.
        try {
            connection = createConnection(driver, url, username, password);
            DatabaseMetaData meta = connection.getMetaData();
            System.out.println(meta.getDatabaseProductName());
            System.out.println(meta.getDatabaseProductVersion());

            String sqlQuery = "SELECT PERSON_ID, FIRST_NAME, LAST_NAME FROM PERSON ORDER BY LAST_NAME";
            System.out.println("before insert: " + query(connection, sqlQuery, Collections.EMPTY_LIST));

            connection.setAutoCommit(false);
            String sqlUpdate = "INSERT INTO PERSON(FIRST_NAME, LAST_NAME) VALUES(?,?)";
            List parameters = Arrays.asList("Foo", "Bar");
            int numRowsUpdated = update(connection, sqlUpdate, parameters);
            connection.commit();

            System.out.println("# rows inserted: " + numRowsUpdated);
            System.out.println("after insert: " + query(connection, sqlQuery, Collections.EMPTY_LIST));
        } catch (Exception e) {
            rollback(connection);
            e.printStackTrace();
        } finally {
            close(connection);
            long endTime = System.currentTimeMillis();
            System.out.println("wall time: " + (endTime - begTime) + " ms");
        }
    }

    public static Connection createConnection(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        if ((username == null) || (password == null) || (username.trim().length() == 0) || (password.trim().length() == 0)) {
            return DriverManager.getConnection(url);
        } else {
            return DriverManager.getConnection(url, username, password);
        }
    }

    public static void close(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static void close(Statement st) {
        try {
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void rollback(Connection connection) {
        try {
            if (connection != null) {
                connection.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static List<Map<String, Object>> map(ResultSet rs) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
        try {
            if (rs != null) {
                ResultSetMetaData meta = rs.getMetaData();
                int numColumns = meta.getColumnCount();
                while (rs.next()) {
                    Map<String, Object> row = new HashMap<String, Object>();
                    for (int i = 1; i <= numColumns; ++i) {
                        String name = meta.getColumnName(i);
                        Object value = rs.getObject(i);
                        row.put(name, value);
                    }
                    results.add(row);
                }
            }
        } finally {
            close(rs);
        }
        return results;
    }

    public static List<Map<String, Object>> query(Connection connection, String sql, List<Object> parameters) throws SQLException {
        List<Map<String, Object>> results = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);

            int i = 0;
            for (Object parameter : parameters) {
                ps.setObject(++i, parameter);
            }
            rs = ps.executeQuery();
            results = map(rs);
        } finally {
            close(rs);
            close(ps);
        }
        return results;
    }

    public static int update(Connection connection, String sql, List<Object> parameters) throws SQLException {
        int numRowsUpdated = 0;
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);

            int i = 0;
            for (Object parameter : parameters) {
                ps.setObject(++i, parameter);
            }
            numRowsUpdated = ps.executeUpdate();
        } finally {
            close(ps);
        }
        return numRowsUpdated;
    }
}

回答by OmniOwl

You have to work with a pool of database connections.

您必须使用数据库连接池。

Check out this link: http://www.snaq.net/java/DBPool/

查看此链接：http: //www.snaq.net/java/DBPool/

回答by Jayashri

You have to make simple java class file for connection to database and call the object of that class whenever required..

您必须制作简单的 java 类文件以连接到数据库，并在需要时调用该类的对象。

回答by Abubakkar

You can do like this:

你可以这样做：

class DBConnector{
static Connection c;
public static Connection getConnection(){

    try{ 
    Class.forName("your fully qualified driver class name");
    c = DriverManager.getConnection("connection url");
    return c;
    }catch(Exception e){
    return null;
    }

}
}

Call this method whenever you need a new database connection

每当需要新的数据库连接时调用此方法

回答by jdg

Database Connection Pools are useful when the cost of creating an object is higher than using the object. This is typically the case for database connections, because the database and your server need to go negotiate a connect and handle authentication etc. There is also work that needs to be done on both the server side and database side to allocate the necessary structures for sending data. If you are using SSL then this yet another factor that makes creating a connection slow.

当创建对象的成本高于使用对象的成本时，数据库连接池很有用。这通常是数据库连接的情况，因为数据库和您的服务器需要协商连接并处理身份验证等。服务器端和数据库端还需要完成一些工作来分配必要的结构以进行发送数据。如果您使用 SSL，那么这又是另一个使创建连接变慢的因素。

If you are going to need to open and then close many connections then you may want to use a pool. Of course, its hard to say without knowing the specifics of your application.

如果您需要打开然后关闭许多连接，那么您可能需要使用池。当然，如果不知道您的应用程序的具体情况，就很难说。

I wrote an article on this topic that goes into more detail. After reading it, you should have a good enough understanding to know if this makes sense for you.

我写了一篇关于这个主题的文章，其中更详细。读完之后，你应该有足够的理解，知道这对你是否有意义。

You can read it here:

你可以在这里阅读它：

http://innolitics.com/10x/database-pools/

http://innolitics.com/10x/database-pools/