java 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 selectList() {

List 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;

}