回顾以前学习HeroDAO，在每个DAO里自行创建Connnection，在这个项目里有多个DAO里都需要获取数据库的连接，并且在本项目中都是一样的数据库连接。 所以就可以把获取数据库连接的代码重构到一个类里。
这样做的好处是有两个
1. 不需要DAO里分别进行编写，直接调用就可以了
2. 如果账号密码发生了变化，值需要修改这一个地方，而不用每个DAO里就分别修改，降低了维护成本，也降低了因为忘记修改而出错的概率
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
static String ip = "127.0.0.1";
static int port = 3306;
static String database = "hutubill";
static String encoding = "UTF-8";
static String loginName = "root";
static String password = "admin";
static{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
String url = String.format("jdbc:mysql://%s:%d/%s?characterEncoding=%s", ip, port, database, encoding);
return DriverManager.getConnection(url, loginName, password);
}
}
使用:
public int getTotal() {
int total = 0;
try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
String sql = "select count(*) from config";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
total = rs.getInt(1);
}
System.out.println("total:" + total);
} catch (SQLException e) {
e.printStackTrace();
}
return total;
}
public void add(Config config) {
String sql = "insert into config values(null,?,?)";
try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setString(1, config.key);
ps.setString(2, config.value);
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
int id = rs.getInt(1);
config.id = id;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
- ResultSet rs = ps.getGeneratedKeys();//获取主键
- 想要返回对象集合的用executeQuery执行,不返回则用execute.
public Config get(int id) {
Config config = null;
try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
String sql = "select * from config where id = " + id;
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
config = new Config();
String key = rs.getString("key_");
String value = rs.getString("value");
config.key = key;
config.value = value;
config.id = id;
}
} catch (SQLException e) {
e.printStackTrace();
}
return config;
}
