当前位置：Gxlcms >
数据库问题 >
分别使用Statement和PreparedStatement对数据库进行操作
分别使用Statement和PreparedStatement对数据库进行操作
时间：2021-07-01 10:21:17
帮助过：2人阅读
插入操作
public void insert(Vector<Person>
vt) {
PreparedStatement ps =
null;
try {
dbHelper =
new DBHelper();
conn =
dbHelper.getConn();
conn.setAutoCommit(false);
// 首先把Auto commit设置为false,不让它自动提交
String sql = "insert into " +
Constant.TABLE_NAME
+ " values (?,?,?,?,?,?)"
;
ps =
conn.prepareStatement(sql);
for (Person person : vt) {
ps.setInt(1
, person.getId());
ps.setString(2
, person.getName());
ps.setString(3
, person.getAge());
ps.setString(4
, person.getSex());
ps.setString(5
, person.getTelNum());
ps.setString(6
, person.getAddress());
ps.addBatch();
}
ps.executeBatch();
conn.commit();// 进行手动提交（commit）
conn.setAutoCommit(
true);
// 提交完成后回复现场将Auto commit,还原为true
System.out.println("添加成功"
);
} catch (SQLException e) {
e.printStackTrace();
try {
// 若出现异常，对数据库中所有已完成的操作全部撤销，则回滚到事务开始状态
if (!
conn.isClosed()) {
// 当异常发生执行catch中SQLException时，记得要rollback(回滚)；
conn.rollback();
System.out.println("插入失败，回滚！"
);
conn.setAutoCommit(true);
}
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 查询操作
public Vector<Person>
selectAll() {
Vector<Person> vt =
new Vector<Person>
();
try {
dbHelper =
new DBHelper();
conn =
dbHelper.getConn();
String sql = "select * from " +
Constant.TABLE_NAME;
Statement stmt =
conn.createStatement();
ResultSet rs =
stmt.executeQuery(sql);
while (rs.next()) {
Person person =
new Person();
person.setId(rs.getInt(1
));
person.setName(rs.getString(2
));
person.setAge(rs.getString(3
));
person.setSex(rs.getString(4
));
person.setTelNum(rs.getString(5
));
person.setAddress(rs.getString(6
));
vt.add(person);
}
stmt.close();
rs.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return vt;
}
// 删除所有
public void deleteAll() {
try {
dbHelper =
new DBHelper();
conn =
dbHelper.getConn();
String sql = "truncate table " +
Constant.TABLE_NAME;
PreparedStatement ps =
conn.prepareStatement(sql);
ps.executeUpdate();
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
分别使用Statement和PreparedStatement对数据库进行操作
标签：
