Java JDBC avoiding redundant code in methods writing to database?

Question

Employees is an object listed in another class. It has the fields name, department, and experience. I use the main method to create a Swing object, take in any new employees, and call upon the addEmployees class.

What If I want to create more methods in this class that call to the database? Do I need to create a try/catch, set the variable 'connector', the whole process again? What if I want to create a method in the same class that only sets the name and department? Do I need to add redundant code?

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

public class AddEmployees {

public String addEmployee(String name, String department, int experience){



    Connection connector = null; 
    PreparedStatement stmt = null;

    try {

            Class.forName("com.mysql.jdbc.Driver");
        // Load a connection to my DB
            String url = "jdbc:mysql://localhost:3306/myjobsite";
            connector = DriverManager.getConnection(url, "golden", "password");
            String sql="INSERT INTO employees(name,department,experience) values(?,?,?)";
            stmt = connector.prepareStatement(sql);
            stmt.setString(1, name);
            stmt.setString(2, department);
            stmt.setInt(3, experience);
            stmt.execute();      //run sql 


    } catch (SQLException e) {
        // TODO Auto-generated catch block
        return "Connection Failure";

    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    return "Executed sql";


}

}

I think this question is probably not specific enough to get a good answer on SO. In general, you can open the connection once at program startup, then pass the connection to addEmployees or any other database-access methods you write. — cxw
– cxw, Commented Aug 11, 2016 at 18:20
One connection in a shared app wouldn't be a good idea. A better solution is to use a connection pool, pass a connection into each method, and use it in the narrowest scope possible. — duffymo
– duffymo, Commented Aug 11, 2016 at 18:42

duffymo · Accepted Answer · 2016-08-11 18:26:03Z

1

Your instinct to avoid repeated code is a good one. DRY (Don't Repeat Yourself) should be a bedrock fundamental for all programmers.

Rather than write an entire persistence tier from scratch, I'd recommend beginning with Spring and its JdbcTemplate. They've done a great job of designing an API that lets you concentrate on just what you need for your application.

answered Aug 11, 2016 at 18:26

duffymo

310k46 gold badges376 silver badges571 bronze badges

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

2 Comments

Armando Xhimanki Over a year ago

I tried. When i tried to add the validator dependency in maven for a project it wasnt building, not much experience with maven tbh. Is using a gradle build easier?

duffymo Over a year ago

I would say Maven and Gradle are equivalent. Your Maven issue is a separate question. Try Spring Boot and its guides: spring.io/guides

Gherbi Hicham · Accepted Answer · 2016-08-11 19:30:06Z

Well for starters you can just put the connection part in its own method like this:

public void connectToDb(){
try {

            Class.forName("com.mysql.jdbc.Driver");
            // Load a connection to my DB
            String url = "jdbc:mysql://localhost:3306/myjobsite";
            connector = DriverManager.getConnection(url, "golden", "password");

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        return "Connection Failure";

    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

And then you can call this method whenever you try to start a database session, this way you can separate the actual connection from the queries you have to write:

public String employeMethod(){
      connectToDb();
      //write your query stuff here
}

Robert Niestroj · Accepted Answer · 2016-08-11 19:45:14Z

0

If you can add Spring to your project then you can use JdbcTemplate to greatly simplify database access code.

Your code can look like this:

this.jdbcTemplate.update("INSERT INTO employees(name,department,experience) values(?,?,?)",
        getName(), getDepartment(), getExperience());

answered Aug 11, 2016 at 19:45

Robert Niestroj

16.2k15 gold badges84 silver badges126 bronze badges

Collectives™ on Stack Overflow

Java JDBC avoiding redundant code in methods writing to database?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related