CODEWITHSUNDEEP
javamysqljdbc
Armando Xhimanki
Armando Xhimanki

Reputation: 115

Java JDBC avoiding redundant code in methods writing to database?

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


}

}

Upvotes: 1

Views: 762

Answers (3)

Robert Niestroj
Robert Niestroj

Reputation: 16151

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());

Upvotes: 0

Gherbi Hicham
Gherbi Hicham

Reputation: 2594

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
}

Upvotes: 1

duffymo
duffymo

Reputation: 308988

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.

Upvotes: 1

Related Questions