Java DB connection

Question

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

dbConnect.java - class file

    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?

duffymo · Accepted Answer · 2015-02-08T19:44:26.553

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

Sorry, that proposed edit to the code I posted 3.5 years ago is wrong. I've removed it. The code as written is correct. I won't accept it. I'm sorry, but Zubair has no idea what he's doing. If you read the code, I do create the connection just fine. — duffymo, Feb 08 '15 at 19:47
Your comment is valuable for me. But the code did not worked for me without the edit I posted :) — Zubair, Feb 09 '15 at 05:25
Sorry, that code works fine. I've used it many times. You did something wrong. — duffymo, Feb 09 '15 at 11:46

score 1 · Answer 2 · answered Oct 14 '12 at 11:30

1

You have to work with a pool of database connections.

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

answered Oct 14 '12 at 11:30

OmniOwl

5,477
17
67
116

score 1 · Answer 3 · answered Oct 14 '12 at 11:36

1

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

answered Oct 14 '12 at 11:36

Jayashri

366
4
13
25

score 0 · Answer 4 · answered Oct 14 '12 at 11:53

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

score 0 · Answer 5 · answered Nov 03 '15 at 01:24

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.

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/

Java DB connection

5 Answers5

Linked