实验六：JDBC编程 #7
Description
1. 三层架构
2. 加载驱动
Class.forName("");
注意：驱动程序应该到对应的数据库厂商网站下载，如：PostgreSQL https://jdbc.postgresql.org/download.html 。
Tips：准备面试还应该了解JDBC驱动的4种类型。
3. 连接数据库
String url = "jdbc:postgresql://<database_host>:<port>/<database_name>";
Connection con = DriverManager.getConnection(url, "userID", "password");
好的做法是保存在常量中，例如：
private final String url = "jdbc:postgresql://localhost/dvdrental";
private final String user = "postgres";
private final String password = "<add your password>";
详细的连接代码：
public Connection connect() {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, user, password);
System.out.println("Connected to the PostgreSQL server successfully.");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}
更好的做法是将配置保存在配置文件中，如：jdbc.properties，可方便数据库连接管理，如切换开发与生产数据库。Hibernate等框架也采用这种方法。（问：Hibernate是怎么配置的？）
4. 准备SQL语句
select * from TABLE_NAME;
insert into ... ;
update ...;
delete ...;
http://www.w3school.com.cn/sql/
5. 查询数据
public int getActorCount() {
String SQL = "SELECT count(*) FROM actor";
int count = 0;
try (Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL)) {
rs.next();
count = rs.getInt(1);
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
return count;
}
6. 插入数据
public long insertActor(Actor actor) {
String SQL = "INSERT INTO actor(first_name,last_name) "
+ "VALUES(?,?)";
long id = 0;
try (Connection conn = connect();
PreparedStatement pstmt = conn.prepareStatement(SQL,
Statement.RETURN_GENERATED_KEYS)) {
pstmt.setString(1, actor.getFirstName());
pstmt.setString(2, actor.getLastName());
int affectedRows = pstmt.executeUpdate();
// check the affected rows
if (affectedRows > 0) {
// get the ID back
try (ResultSet rs = pstmt.getGeneratedKeys()) {
if (rs.next()) {
id = rs.getLong(1);
}
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
return id;
}
7. 修改数据
TODO
8. 删除数据
TODO
参考文献
Reactions are currently unavailable