1

I'm currently building a small applciation which uses JDBC and MySQL.

I have this DB class. Currently it only has a main method which connects, and then returns something. Now this is working correctly. But I would like to change it to this:

DB Class with a constructor. Multiple methods which call different SQL query's for example I would want to call a user like this.

db.user('John');

So what do I want to prevent: Having to connect each time I make a new method.

Sorry if the question might be a bit unclear. I'm a new JDBC user.

package application;

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

public class DB {

    //ToDo Add these credentials leter to a main config file.
    private static String connectionUrl = "jdbc:mysql://localhost:3306/tvh_jdbctesting";
    private static String connectionUser = "root";
    private static String connectionPassword = "root";
    private static String jdbc = "com.mysql.jdbc.Driver";


    DB(){

    }

    public static void main(String[] args) {
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {
                Class.forName(jdbc).newInstance();

                //Makes connection with the provided credentials.
                conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
                stmt = conn.createStatement();

                rs = stmt.executeQuery("SELECT * FROM test");
                while (rs.next()) {

                    String id = rs.getString("id");
                    String name = rs.getString("name");
                    String wachtwoord = rs.getString("wachtwoord");

                    System.out.println(id + name + wachtwoord);

                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
                try { if (stmt != null) stmt.close(); } catch (SQLException e) { e.printStackTrace(); }
                try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
            }
        }

    }
TVH7
  • 415
  • 2
  • 5
  • 19
  • _Sorry if the question might be a bit unclear._ yes your question is unclear.. – SatyaTNV Nov 18 '15 at 12:28
  • What's the error? Did you try to implement anything? You can create a method that returns the `Statement` object and use that in different methods. – Prerak Sola Nov 18 '15 at 12:28
  • So you want to factor out a reusable connection object used by your "DB" class? What about a connection pool? http://stackoverflow.com/questions/2835090/how-to-establish-a-connection-pool-in-jdbc Or even justjust 1 [connection](https://docs.oracle.com/javase/tutorial/jdbc/basics/connecting.html) object. Though you should try the object oriented thing that you can do with Java. Not putting things into the main class. – zapl Nov 18 '15 at 12:43

1 Answers1

1

This is actually not a bad start for a new JDBC person. My biggest nitpick is you don't need newInstance on the Class returned by Class.forName, and it depends on what driver you're using whether you even need Class.forName at all.

If what you're talking about here is a small single-threaded program run from the command line that does its thing and goes away, you could get by with having the main method create the connection, create other objects and call them, passing in the connection, and then having the main program close the connection in a finally block at the end. I would rather pass the connection around explicitly than use some DB class.

You would start considering using a connection pool if your program had multiple threads that had to do database access concurrently, or if you want the program to survive possible network blips that could kill your database connection. If you find JDBC too painful then Spring can help.

Nathan Hughes
  • 94,330
  • 19
  • 181
  • 276