JavaWeb开发之JDBC的增删改查

JavaWeb开发之JDBC的增删改查
项⽬搭建的包命名和类命名：
⾸先导⼊数据库的驱动架包，并添加依赖
1.Dao层是专门负责和数据库交互，数据处理的代码层
2.Dao是接⼝，DaoImpl是Dao接⼝的实现类
3.Java代码连接数据库的步骤：“贾琏欲执事”⼝诀
（1）贾：加载数据库注册驱动,将数据库驱动注册到DriverManager中去。

Class.forName("com.mysql.jdbc.Driver");
（2）琏：创建连接数据库的对象，通过DriverManager获取
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "123456");
（3）欲：创建预编译或⾮预编译 SQL语句对象
String sql="update t_student set name = ?,age = ? where id = ?";
PrepardStatment ps =conn.preparedStatment(sql);
ps.setString(1，"⼩明");
ps.setInt(2，20)；
ps.setInt(3，2)；
（4）执：执⾏SQL语句
int row =ps.executeUpdate();
（5）事：释放资源（先创后放）
conn.close();
ps.close();
具体代码：
@Override
public int insert(Student stu) {
String sql = "insert into t_student(name,age)values(?,?)";
Connection conn = null;
PreparedStatement ps = null;
try {
// 1.加载注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取数据库连接对象
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root"); // 3.创建预编译语句对象
ps = conn.prepareStatement(sql);
// 3.1设置预编译语句对象占位符对应的参数值
ps.setString(1, stu.getName());
ps.setInt(2, stu.getAge());
// 4.执⾏SQL语句 (注意：⽅法不能带sql参数)
int row = ps.executeUpdate();
return row;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return 0;
}
@Override
public int delete(Integer id) {
String sql = "delete from t_student where id = ?";
Connection conn = null;
PreparedStatement ps = null;
try {
// 1.加载注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取数据库连接对象
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root"); // 3.创建预编译语句对象
ps = conn.prepareStatement(sql);
// 3.1设置预编译语句对象占位符对应的参数值
ps.setInt(1, id);
// 4.执⾏SQL语句 (注意：⽅法不能带sql参数)
int row = ps.executeUpdate();
return row;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return 0;
}
@Override
public int update(Student stu) {
String sql = "update t_student set name = ?,age = ? where id = ?";
Connection conn = null;
PreparedStatement ps = null;
try {
// 1.加载注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取数据库连接对象
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root"); // 3.创建预编译语句对象
ps = conn.prepareStatement(sql);
// 3.1设置预编译语句对象占位符对应的参数值
ps.setString(1, stu.getName());
ps.setInt(2, stu.getAge());
ps.setInt(3, stu.getId());
// 4.执⾏SQL语句 (注意：⽅法不能带sql参数)
int row = ps.executeUpdate();
return row;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return 0;
}
@Override
public Student selectByPrimaryKey(Integer id) {
String sql = "select * from t_student where id = ?";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 1.加载注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取数据库连接对象
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root"); // 3.创建预编译语句对象
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
// 4.执⾏sql语句
rs = ps.executeQuery();
while (rs.next()) {
String name = rs.getString("name");
int age = rs.getInt("age");
// 封装Student对象
Student stu = new Student(id, name, age);
//返回Student对象
return stu;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
return null;
}
@Override
public List<Student> selectList() {
List<Student> students = new ArrayList<>();
String sql = "select * from t_student";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 1.加载注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取数据库连接对象
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo", "root", "root"); // 3.创建预编译语句对象
ps = conn.prepareStatement(sql);
// 4.执⾏sql语句
rs = ps.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
// 封装Student对象
Student stu = new Student(id, name, age);
// 学⽣对象添加到集合中
students.add(stu);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
return students;
}。