JDBC总结4中，我们的查询依然是放在每个DAO中，而不是DBHelp类中，这样还是不够简化的！下面简化一下：
在util包中建立一个接口:
package com.hanchao.util;
import java.sql.ResultSet;
import java.sql.SQLException;
/***********************
* @author：han
* @version：1.0
* @created：2015-10-11
***********************
*/
public interface RowMapper {
public Object mapRow(ResultSet rs) throws SQLException;
}3.在dao包中，
package com.hanchao.dao;
import java.util.List;
import com.hanchao.entity.Account;
import com.hanchao.util.DBHelp;
/***********************
* @author：han
* @version：1.0
* @created：2015-10-11
***********************
*/
public class AccountDao {
private DBHelp dbHelp = new DBHelp();
/**
* 保存
* *******************
* @param accout
* @return
* *******************
* @author：wind
* 2015-10-11 下午9:31:28
* *******************
*/
public int save(Account accout) {
String sql = "insert into t_account(username,password,money,enable) value(?,?,?,?)";
return dbHelp.executeSQL(sql, accout.getUsername(),accout.getMoney(),accout.getMoney(),accout.isEnable());
}
/**
* update
* *******************
* @param account
* @return
* *******************
* @author：wind
* 2015-10-11 下午9:37:53
* *******************
*/
public int update(Account account) {
String sql = "update t_account set username=?,password=?,money=?,enable=? where id = ?";
return dbHelp.executeSQL(sql, account.getUsername(),account.getPassword(),account.getMoney(),account.isEnable(),account.getId());
}
/**
* detele
* *******************
* @param id
* @return
* *******************
* @author：wind
* 2015-10-11 下午9:56:24
* *******************
*/
public int delete(int id) {
String sql = "delete from t_account where id = ?";
return dbHelp.executeSQL(sql, id);
}
/**
* 根据ID查找对象
* *******************
* @param id
* @return
* *******************
* @author：wind
* 2015-10-11 下午10:05:46
* *******************
*/
public Account findById(int id) {
String sql = "select id,username,password,money,enable from t_account where id = ?";
return (Account) dbHelp.executeQueryForObject(new AccountMapper(), sql, id);
}
/**
* 查找所有
* *******************
* @return
* *******************
* @author：wind
* 2015-10-11 下午10:17:03
* *******************
*/
public List<Account> findAll() {
String sql = "select id,username,password,money,enable from t_account";
return dbHelp.executeQueryForList(new AccountMapper(), sql);
}
}实现类：实现接口的方法
package com.hanchao.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.hanchao.entity.Account;
import com.hanchao.util.RowMapper;
/***********************
* @author：han
* @version：1.0
* @created：2015-10-11
***********************
*/
public class AccountMapper implements RowMapper{
@Override
public Object mapRow(ResultSet rs) throws SQLException {
Account account = new Account();
account.setId(rs.getInt("id"));
account.setUsername(rs.getString("username"));
account.setPassword(rs.getString("password"));
account.setMoney(rs.getFloat("money"));
account.setEnable(rs.getBoolean("enable"));
return account;
}
}4.那我们的DBhelp如何写呢？
package com.hanchao.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.hanchao.entity.Account;
/***********************
* 帮助类
* @author：han
* @version：1.0
* @created：2015-10-11
***********************
*/
public class DBHelp {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql:///mydb";
private static final String DB_NAME = "root";
private static final String DB_PASSWORD = "root";
public Connection getConnection() throws SQLException, ClassNotFoundException {
Class.forName(DRIVER);
Connection conn = DriverManager.getConnection(URL,DB_NAME,DB_PASSWORD);
return conn;
}
public int executeSQL(String sql ,Object...args) {
Connection conn = null;
PreparedStatement stat = null;
int rows = 0;
try {
conn = getConnection();
stat = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
stat.setObject(i+1, args[i]);
}
rows = stat.executeUpdate();
if (rows > 0) {
System.out.println("operate successfully!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.close(conn, stat);
}
return rows;
}
public void close(ResultSet rs,Connection conn , PreparedStatement stat) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (stat != null) {
stat.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public void close(Connection conn , PreparedStatement stat) {
this.close(null,conn, stat);
}
/**
* 根据ID查找对象
* *******************
* @param rm
* @param sql
* @param args
* @return
* *******************
* @author：wind
* 2015-10-11 下午11:05:58
* *******************
*/
public Object executeQueryForObject(RowMapper rm,String sql ,Object...args) {
Connection conn = null;
PreparedStatement stat = null;
Object result = null;
ResultSet rs = null;
try {
conn = getConnection();
stat = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
stat.setObject(i+1,args[i]);
}
rs = stat.executeQuery();
if (rs.next()) {
result = (Account) rm.mapRow(rs);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, conn, stat);
}
return result;
}
/**
* 查找集合
* *******************
* @param rm
* @param sql
* @param args
* @return
* *******************
* @author：wind
* 2015-10-11 下午11:12:49
* *******************
*/
public List executeQueryForList(RowMapper rm,String sql,Object...args) {
List list = new ArrayList();
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
try {
conn = getConnection();
stat = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
stat.setObject(i+1, args[i]);
}
rs = stat.executeQuery();
while (rs.next()) {
Object obj = rm.mapRow(rs);
list.add(obj);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.close(rs,conn, stat);
}
return list;
}
}5.测试一下：
package com.hanchao.test;
import java.util.List;
import com.hanchao.dao.AccountDao;
import com.hanchao.entity.Account;
/***********************
* @author：han
* @version：1.0
* @created：2015-10-11
***********************
*/
public class Test {
public static void main(String[] args) {
//增
/* Account account = new Account();
account.setEnable(true);
account.setMoney(20f);
account.setUsername("hanchao1");
account.setPassword("1234561");
AccountDao accountDao = new AccountDao();
accountDao.save(account);*/
//改
/* Account account = new Account();
account.setId(8);
account.setEnable(true);
account.setMoney(21f);
account.setUsername("hanchao2dd1");
account.setPassword("1234562dsds2");
AccountDao accountDao = new AccountDao();
accountDao.update(account);*/
//删
/* AccountDao accountDao = new AccountDao();
accountDao.delete(2);*/
//查询
/* AccountDao accountDao = new AccountDao();
Account account = accountDao.findById(8);
if (account != null) {
System.out.println("id:" + account.getId() + ",name:" + account.getUsername());
} else {
System.out.println("not exist!");
}*/
//查询
AccountDao accountDao = new AccountDao();
List<Account> list = accountDao.findAll();
for (int i = 0; i < list.size(); i++) {
System.out.println("id:" + list.get(i).getId() + " ,name:" + list.get(i).getUsername());
}
}
}这样，我们的DBhelp类就是一个彻底的工具类了，虽然不够优化，但是，可以实现基本的工具类的功能了！
本文出自 “我的JAVA世界” 博客，请务必保留此出处http://hanchaohan.blog.51cto.com/2996417/1701978
原文：http://hanchaohan.blog.51cto.com/2996417/1701978