用Java设计数据访问类

Question

因此，我正在编写一个RESTAPI，并为每个资源定义了一个服务类

因此，我有一个人力资源，这就是我与数据库交互的方式

public class TeacherService {


    public static List<Person> getAll() throws SQLException{

        //define the query string and objects



        try{

                DriverManager.registerDriver(new com.mysql.jdbc.Driver ());
                connection = (Connection) DriverManager.getConnection(ConnectDb.CONN_STRING, ConnectDb.USERNAME, ConnectDb.PASSWORD);
                statement = (PreparedStatement) connection.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
                resultSet = statement.executeQuery(query);

                //process results

            }catch (SQLException e) {
                System.err.println(e);

            }finally{

                //close all shit
            }

        return list;
    }

    public static Person getById(int id) throws SQLException{
        //repeat
    }

    public static void addPerson(Person person) throws SQLException {

        //repeat
    }

    public static void upateTeacher(Person person) throws SQLException {


        //repeat
    }

    public static void deleteTeacher(int id) throws SQLException {


            //repeat        
    }


}

因此，除了在每种情况下都不同的查询和resultSet处理逻辑之外，每件事都几乎是一样的。 这不仅在许多层面上违反了DRY标准，而且维护起来也显得极为笨拙。有没有更好的办法

Answer 1

这里有一个例子，说明你可能会怎么做。这个例子并不完美，但应该让你走上正确的道路

基本上，在打开公共共享连接后，您可以注册所有语句。完成所有工作，然后调用shutdown（）方法将其全部关闭

public class MyDataAccessObject {

    private final String getAllPersonsQuery = "SELECT persons FROM personTable";
    private PreparedStatement psGetAllPersonsQuery;

    private final String addPersonQuery = "INSERT INTO personTable (personName) VALUES (?)"; // ? is a placeholder. Using PreparedStatement and ?'s, adds more safety and performance.
    private PreparedStatement psAddPersonQuery;

    private Connection conn;

    private final String connectionString;
    private final String username;
    private final String password;

    public MyDataAccessObject(String connectionString, String username, String password) {
        this.connectionString =  connectionString;
        this.username = username;
        this.password = password;
    }

    public void init() throws SQLException {
        conn = DriverManager.getConnection(connectionString, username, password); // no longer required to register driver if using modern JDBC drivers.
        psGetAllPersonsQuery = conn.prepareStatement(getAllPersonsQuery);
        psAddPersonQuery = conn.prepareStatement(addPersonQuery); // we register these now, so it's fast to use later.
    }

    public void shutdown() throws SQLException {
        if (conn != null) {
            conn.close(); // this will close everything used ontop of this connection, including PreparedStatement's, and ResultSets, if still open.
        }
    }

    public List<Person> getAllPersons() throws SQLException {
        if (conn == null) {
            // try to re-open connection
            init();
        }
        ResultSet rs = psGetAllPersonsQuery.execute();
        List<Person> list = new ArrayList<Person>();
        while (rs.next()) {
            list.add(new Person(rs.getString(1))); // not sure how your data is setup, this is returning the first (1) column from the resultset
        }
        if (rs != null) {
            rs.close();
        }
        return list;
    } // don't close the prepareStatement!

    public void addPerson(Person person) throws SQLException {
        if (conn == null) {
            // try to re-open connection
            init();
        }
        psAddPersonQuery.setString(1, person.getName()); // or however you're storing the data. The 1 is saying replace the first ? with whatever data you specify after the comma.
        psAddPersonQuery.executeUpdate(); // executeUpdate() returns an int, which says how many rows were modified. Since you're inserting here, we probably don't care.
    } // don't close the prepareStatement!

}

你会像这样使用它：

MyDataAccessObject mdao = new MyDataAccessObject(connectionString, username, password);
mdao.init(); // now you're ready

List<Person> list = mdao.getAllPersons();
// do stuff with your list

....
mdao.addPerson(someNewPerson);
// ....

// now you're done running, so close it down
mdao.shutdown();