connecting Java to MySQL

Question

I'm doing my dissertation on software engineering and im building a small application that makes use of a SQL DB, in this case MySQL. I'm also using the application controller pattern. So the code I have working for retrieving data from the db is;

    public static void main(String[] args)

    {
      try
       {
        String url = "jdbc:mysql://localhost:3306/tm470_returns_stock_management_system";
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection con = DriverManager.getConnection(url,"root","root");

        Statement st = con.createStatement();


        ResultSet res = st.executeQuery("SELECT * FROM test_table"); 
        while (res.next()) 
        { 
         int id = res.getInt("test_id");
         String msg = res.getString("test_info"); 
         System.out.println(id + "\t" + msg);
        }

        con.close();
       }
      catch(Exception e)
       {
        System.out.println("DB connection unsuccesful");
       }
}

I now want to transfer this out of my Main class/string and into my Application Controller Class (which is called Facility).

Now my question is, for every method in my Facility Class that needs to access the DB, do i have to do the full code each time? Or can i create a method within the Facility class that each application method can just call whenever it needs to access the DB. If i can condense all this into a method, can you advise me how to go about it please?

Be gentle with me guys, I am a learner :)

yes you can .. :) how about adding a utility class like ConnectionUtil and using the static method to access the connection..?? — Viraj Nalawade
– Viraj Nalawade, Commented Apr 18, 2015 at 15:04
added the singleton class called ConnectionUtil you can just call a getConnection() whenever you require one.. — Viraj Nalawade
– Viraj Nalawade, Commented Apr 18, 2015 at 15:13

Viraj Nalawade · Accepted Answer · 2015-04-18 15:17:15Z

1

How about adding a utility class like ConnectionUtil and using the static method to access the connection.

import java.sql.Connection; import java.sql.DriverManager;

public class ConnectionUtil{
static final String url = "jdbc:mysql://localhost:3306/";
static final String dbName = "test";
static final String driver = "com.mysql.jdbc.Driver";
static final String userName = "userparatest";
static final String password = "userparatest";
Connection con = null;
    static Connection getConnection() throws Exception {  
if(con == null)
 {       Class.forName(driver).newInstance();
        con = DriverManager.getConnection(url + dbName, userName,password);     
    }
return con;
}

}

this can be further improved but just providing a start..

just call below whenever you want a statement..

Statement st = ConnectionUtil.getConnection().createStatement();

edited Apr 18, 2015 at 15:17

answered Apr 18, 2015 at 15:11

Viraj Nalawade

3,2353 gold badges32 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Viraj Nalawade Over a year ago

@Khal just edited to make fields (url,dbname,..) static final..Java says non-static fields cannot be accessed from static method

Khal Over a year ago

Thank you. Just to be certain, my application controller has to create an instance of this class?

Viraj Nalawade Over a year ago

Nope thats what we are avoiding as its not needed.. You need only one connection and whenever you want that just call ConnectionUtil.getConnection()

Khal Over a year ago

Gotcha so just call Statement st = ConnectionUtil.getConnection().createStatement(); then afterwards st.executeQuery() or st.executeUpdate() where applicable. Cheers pal, help is much appreciated

CodeFox · Accepted Answer · 2015-04-18 14:59:38Z

0

I would map it as a own class, which is used by your application other classes. When you define it as a singleton you will only need one instance in your complete application

answered Apr 18, 2015 at 14:59

CodeFox

4454 silver badges13 bronze badges

Comments

Viraj Nalawade · Accepted Answer · 2015-04-18 15:19:36Z

Yes , you can write a method for accessing db and you can reuse it across all the applications.

Keep the following in a method and reuse it.

String url = "jdbc:mysql://localhost:3306/tm470_returns_stock_management_system";
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    Connection con = DriverManager.getConnection(url,"root","root");

    Statement st = con.createStatement();

    int productID = 6;
    String skuCode = "ABC123";

    ResultSet res = st.executeQuery("SELECT * FROM test_table"); 
    while (res.next()) 
    { 
     int id = res.getInt("test_id");
     String msg = res.getString("test_info"); 
     System.out.println(id + "\t" + msg);
    }

Collectives™ on Stack Overflow

connecting Java to MySQL

3 Answers 3

4 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Related