mysql数据库dao模式_古诗MySQL数据库DAO模式实现

整体规则

step1

DBHelper工具类，一般不用实例化，因此可以采用Singleton或者是将构造方法私有化。

/**

* Created by chuiyuan on 2/17/16.

* 工具类，一般不要实例化，此时可以采用单例设计模式，或者将构造方法私有化

*/

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

}

/**

* 为避免重复代码，使用静态代码块：只会在类加载的时候执行一次。

*/

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 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接口。

/**

* 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 findAll() throws SQLException ;

}

Step3

PoemDaoImpl实现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=? ,translation=? 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 findAll() throws SQLException {

Connection conn = null ;

PreparedStatement ps = null ;

ResultSet rs = null ;

Poem poem = null ;

List poemList = new ArrayList();

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 ;

}

}

step４

AppMain中的调用。

//store to mysql

PoemDao poemDao = new PoemDaoImpl() ;

for (Poem poem: poemList){

try {

poemDao.add(poem);

}catch (SQLException e){

e.printStackTrace();

}

}

原文：http://www.cnblogs.com/chuiyuan/p/5200498.html