Classical poetry MySQL Database dao pattern implementation

Overall rules

Step1

The DBHelper tool class is generally not instantiated, so you can use singleton or privatize the construction method.

/** * Created by Chuiyuan on 2/17/16.    * Tool class, generally do not instantiate, at this time can adopt a single case design mode, or the construction method of privatization */public class DBHelper {public static String URL;    public static String username;    public static String password;    public static String driver;    Prrty file private static ResourceBundle RB = Resourcebundle.getbundle ("Db-config");    PRIVATE Connection conn = null;     Private DBHelper () {}/** * to avoid duplication of code, use static blocks of code: only once when the class is loaded.            */static {try{URL = rb.getstring ("Jdbc.url");            Username = rb.getstring ("Jdbc.username");            Password = rb.getstring ("Jdbc.password");            Driver = rb.getstring ("Jdbc.driver");        Class.forName (driver);        }catch (Exception e) {e.printstacktrace ();        }}//get a connection with the MySQL public static connection getconnection () {Connection conn = null;        try {conn = drivermanager.getconnection (Url,username,password); }catch (SqlexCeption e) {e.printstacktrace ();    } return conn;  }/** * Close * @param RS * @param stmt * @param conn */public static void Close (ResultSet RS,            Statement stmt, Connection conn) {try {if (rs!= null) rs.close ();            if (stmt!= null) stmt.close ();        if (conn!= null) conn.close ();        }catch (SQLException e) {e.printstacktrace (); }    }}

Step2

DAO interface.

/** * Created by Chuiyuan on 2/17/16. * Interface for CRUD of Poem */public interface Poemdao {public    void Add (Poem Poem) throws SQLException;    public void Update (Poem Poem) throws SQLException;    public void Delete (int id) throws SQLException;    Public Poem FindByID (int id) throws SQLException;    Public list<poem> FindAll () throws SQLException;}

Step3

The Poemdaoimpl implements the interface in the Step2.

/** * Created by Chuiyuan on 2/17/16.  */public class Poemdaoimpl implements Poemdao {public void Add (Poem Poem) throws SQLException {Connection conn        = NULL;        PreparedStatement PS = null;                String sql = "INSERT INTO Poemtable" + "(Dynasty, category, title, author, content, href, translation)" +        "VALUES (?,?,?,?,?,?,?)";            try {conn = dbhelper.getconnection ();            PS = conn.preparestatement (SQL);            Ps.setstring (1,poem.getdynasty ());            Ps.setstring (2,poem.getcategory ());            Ps.setstring (3,poem.gettitle ());            Ps.setstring (4,poem.getauthor ());            Ps.setstring (5,poem.getcontent ());            Ps.setstring (6,poem.gethref ());            Ps.setstring (7,poem.gettranslation ());        Ps.executeupdate ();            }catch (SQLException e) {e.printstacktrace ();        throw new SQLException ("Add poem Failed"); }finally {Dbhelper.close (null, Ps,conn);        }} public void Update (Poem Poem) throws SQLException {Connection conn = null;        PreparedStatement PS = null; String sql = "Update poemtable set dynasty=?", category=?, title=?, "+" author=?, content=?, href=?, transl Ation=?        where id=? ";            try {conn = dbhelper.getconnection ();            PS = conn.preparestatement (SQL);            Ps.setstring (1,poem.getdynasty ());            Ps.setstring (2,poem.getcategory ());            Ps.setstring (3,poem.gettitle ());            Ps.setstring (4,poem.getauthor ());            Ps.setstring (5,poem.getcontent ());            Ps.setstring (6,poem.gethref ());            Ps.setstring (7,poem.gettranslation ());        Ps.executeupdate ();            }catch (SQLException e) {e.printstacktrace ();        throw new SQLException ("update poem Failed");        }finally {dbhelper.close (null,ps,conn);  }} public void delete (int id) throws SQLException {      Connection conn = null;        PreparedStatement PS = null;        String sql = "Delete from poemtable where id=?";            try {conn = dbhelper.getconnection ();            PS = conn.preparestatement (SQL);            Ps.setint (1,id);        Ps.executeupdate ();            }catch (SQLException e) {e.printstacktrace ();        throw new SQLException ("delete poem Failed");        }finally {dbhelper.close (NULL,PS, conn);        }} public Poem FindByID (int id) throws SQLException {Connection conn = null;        PreparedStatement PS = null;        ResultSet rs = null;        Poem Poem = null;        String sql = "Select Dynasty,catetogry,title,author,content," + "href from poemtable where id=?";            try {conn = dbhelper.getconnection ();            PS = conn.preparestatement (SQL);            Ps.setint (1, id);            rs = Ps.executequery ();  if (Rs.next ()) {poem = new poem ();              Poem.setdynasty (rs.getstring (1));                Poem.setcategory (rs.getstring (2));                Poem.settitle (Rs.getstring (3));                Poem.setauthor (Rs.getstring (4));                Poem.setcontent (Rs.getstring (5));            Poem.sethref (rs.getstring (6));            }}catch (SQLException e) {e.printstacktrace ();        throw new SQLException ("Find by ID failed");        }finally {dbhelper.close (RS,PS, conn);    } return poem;        } public list<poem> FindAll () throws SQLException {Connection conn = null;        PreparedStatement PS = null;        ResultSet rs = null;        Poem Poem = null;        list<poem> poemlist = new arraylist<poem> ();        String sql = "Select Dynasty,catetogry,tie,author,content," + "href from poemtable";            try {conn = dbhelper.getconnection ();            PS = conn.preparestatement (SQL); rs = Ps.executequery ();                while (Rs.next ()) {poem = new poem ();                Poem.setdynasty (rs.getstring (1));                Poem.setcategory (rs.getstring (2));                Poem.settitle (Rs.getstring (3));                Poem.setauthor (Rs.getstring (4));                Poem.setcontent (Rs.getstring (5));                Poem.sethref (rs.getstring (6));            Poemlist.add (poem);            }}catch (SQLException e) {e.printstacktrace ();        throw new SQLException ("FindAll failed");        }finally {dbhelper.close (RS, PS, conn);    } return poemlist; }}

Step4

The call in Appmain.

Store to MySQL        poemdao Poemdao = new Poemdaoimpl ();        for (Poem poem:poemlist) {            try {                poemdao.add (Poem);            } catch (SQLException e) {                e.printstacktrace ();            }        }

Classical poetry MySQL Database dao pattern implementation