首先我们要建立一个类用来存放数据。之后数据库中存放的也就是这个类的成员。像这种只有一组成员变量和它们的setter/getter的类称所bean。你可以简单地理解成C里的结构体。我们以一个Hero类为例:
package bean;
public class Hero {
private int id;
private String name;
private float hp;
private int damage;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getHp() {
return hp;
}
public void setHp(float hp) {
this.hp = hp;
}
public int getDamage() {
return damage;
}
public void setDamage(int damage) {
this.damage = damage;
}
}
之后我们要建立一个DAO层。DAO就是数据交互的意思,顾名思义,DAO对象控制一个bean对象和数据库的信息传递。我们可以使用上篇文章提到的JDBC控制语句来构建一个DAO类。
package dao;
import bean.Hero;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class HeroDAO {
public HeroDAO() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://localhost:3306/students_info?characterEncoding=UTF8",
"root", "moshouniqi419");
}
public int getTotal() {
int total = 0;
try (Connection c=getConnection(); Statement s=c.createStatement()) {
String sql = "SELECT COUNT(*) FROM hero";
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
total = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return total;
}
public void add(Hero hero) {
String sql = "INSERT INTO hero values(null, ?, ?, ?)";
try (Connection c=getConnection(); PreparedStatement ps=c.prepareStatement(sql)) {
ps.setString(1, hero.getName());
ps.setFloat(2, hero.getHp());
ps.setInt(3, hero.getDamage());
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
hero.setId(rs.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void update(Hero hero) {
String sql = "UPDATE hero SET name = ?, hp = ?, damage = ? WHERE id = ?";
try (Connection c=getConnection(); PreparedStatement ps=c.prepareStatement(sql)) {
ps.setString(1, hero.getName());
ps.setFloat(2, hero.getHp());
ps.setInt(3, hero.getDamage());
ps.setInt(4, hero.getId());
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void delete(int id) {
try (Connection c=getConnection(); Statement s=c.createStatement()) {
String sql = "DELETE FROM hero WHERE id = " + id;
s.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public Hero get(int id) {
Hero hero = null;
try (Connection c=getConnection(); Statement s=c.createStatement()) {
String sql = "SELECT * FROM hero WHERE id = " + id;
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
hero = new Hero();
String name = rs.getString(2);
float hp = rs.getFloat(3);
int damage = rs.getInt(4);
hero.setName(name);
hero.setHp(hp);
hero.setDamage(damage);
hero.setId(id);
}
} catch (SQLException e) {
e.printStackTrace();
}
return hero;
}
public List<Hero> list() {
return list(0, Short.MAX_VALUE);
}
public List<Hero> list(int start, int count) {
List<Hero> heros = new ArrayList<Hero>();
String sql = "SELECT * FROM hero ORDER BY id ASC LIMIT ?, ?";
try (Connection c=getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, start);
ps.setInt(2, count);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Hero hero = new Hero();
hero.setId(rs.getInt(1));
hero.setName(rs.getString(2));
hero.setHp(rs.getFloat(3));
hero.setDamage(rs.getInt(4));
heros.add(hero);
}
} catch (SQLException e) {
e.printStackTrace();
}
return heros;
}
}
这个类实现了最基本的增删改查操作。至此,JDBC部分就结束了。之后我们将进入servlet开发的部分。