JDBC编程过程中,主要经历了如下几个阶段:
1、加载数据库驱动(Driver);
2、创建数据库连接(Connection);
3、创建Statement对象(Statement,PreparedStatement);
4、执行SQL语句(execute,executeUpdate,executeQuery);
5、释放资源。
在之前的文章中,详细的介绍了JDBC编程的每个步骤,今天特地编写了一个实例,附带了详细的注意,可以供初学者参考。
案例:
1、在mysql中创建一个数据表employee,表中保存id,name,age,sex四个字段,随意存入几组数据,如下如所示:
2、创建employee数据表对应的employee类,类中包含id,name,age和sex四个变量,如下所示:
package com.emp.jdbc;
/**
* @author chengjunyu
* @deprecated 与数据表employee对应的类
*/
public class Employee {
//数据私有化,提供对外访问的接口
private Integer id;
private String name;
private Integer age;
private String sex;
//无参构造器
public Employee() {
}
//带参构造器
public Employee(Integer id,String name,Integer age,String sex) {
super();
this.id = id;
this.name = name;
this.age = age;
this.sex = sex;
}
//查询时需要输出,所以在该类中重写toString()方法
public String toString() {
return "Employee [id="+id+",name="+name+",age="+age+"sex"+sex+"]";
}
//对外提供数据访问的get和set接口
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}
3、创建JDBC的工具类,工具类中封装JDBC编程中通用的方法,包含数据库驱动加载、数据库连接和资源释放,如下所示:
package com.emp.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @author chengjunyu
* @deprecated JDBC工具类
*/
public class JUtil {
/**
* JDBC编程步骤:
* 1、加载驱动;
* 2、获取连接;
* 3、创建Statement对象;
* 4、执行SQL;
* 5、释放资源。
*/
private static final String DRIVERNAME = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3307/school";
private static final String USERNAME = "root";
private static final String PASSWORD = "chengjunyu";
//1、加载驱动,在整个过程过只要加载一次,我这里在静态块中进行加载
static {
try {
Class.forName(DRIVERNAME);
}catch (Exception e) {
e.printStackTrace();
System.out.println("驱动加载失败!");
}
}
//2、驱动加载完成后,获取Connection连接对象,在整个过程中需要进行多次获取
public static Connection getConnection() {
try {
return DriverManager.getConnection(URL, USERNAME, PASSWORD);
}catch(SQLException e) {
e.printStackTrace();
return null;
}
}
//3、创建Statement对象,代码未写在该类中,写在了EmployeeDao类中,案例通过PreparedStatement进行预编译
//4、执行SQL语句并获取结果集,代码未写在该类中,写在了EmployeeDao类中
//5、释放资源,因为在数据库的增删该查中涉及到有结果集的和没有结果集的,所有对释放资源的方法进行了重载
//增加、修改、删除操作时不带ResultSet,用该方法释放资源
public static void release(Connection conn,Statement stmt) {
//关闭Connection连接
if(conn != null) {
try {
conn.close();
System.out.println("Connection连接已关闭!");
}catch (SQLException e) {
e.printStackTrace();
System.out.println("Connection连接关闭失败!");
}
}
//关闭Statement对象
if(stmt != null) {
try {
stmt.close();
}catch (SQLException e) {
e.printStackTrace();
System.out.println("Statement对象关闭失败!");
}
}
}
//查询操作时带ResultSet,用该方法释放资源
public static void release(Connection conn,Statement stmt,ResultSet rs) {
//关闭Connection连接
if(conn != null) {
try {
conn.close();
System.out.println("Connection连接已关闭!");
}catch(SQLException e) {
e.printStackTrace();
System.out.println("Connection连接关闭失败!");
}
}
//关闭Statement对象
if(stmt != null) {
try {
stmt.close();
System.out.println("Statement对象已关闭!");
}catch (SQLException e) {
e.printStackTrace();
System.out.println("Statement对象关闭失败!");
}
}
//关闭ResultSet结果集
if(rs != null) {
try {
rs.close();
System.out.println("ResultSet结果集已关闭!");
}catch (SQLException e) {
e.printStackTrace();
System.out.println("ResultSet结果集关闭失败!");
}
}
}
}
4、创建操作employee表中数据的EmployeeDao类,在该类中定义了数据库中的增删查改操作方法,如下所示:
package com.emp.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/*
* @author chengjunyu
* @deprecated SQL语句执行方法
*/
public class EmployeeDao {
/**
* 保存员工信息到数据库中
* @param emp
*/
public void save(Employee emp) {
Connection conn = null;
PreparedStatement ps = null;
//定义保存的SQL语句
String sql = "insert into employee values(?,?,?,?)";
try {
//建立数据库连接
conn = JUtil.getConnection();
//创建Statement对象,对SQL语句进行预编译
ps = conn.prepareStatement(sql);
ps.setInt(1, emp.getId());
ps.setString(2, emp.getName());
ps.setInt(3, emp.getAge());
ps.setString(4, emp.getSex());
//执行SQL语句
ps.executeUpdate();
System.out.println("员工信息保存成功!");
}catch(SQLException e) {
e.printStackTrace();
System.out.println("员工信息保存失败!");
}
//执行完成后关闭连接,释放资源,此方法为保存,无结果集,调用release(Connection conn,Statement stmt)
JUtil.release(conn, ps);
}
/**
* 通过ID查询数据库中的员工信息
* @param id
*/
public Employee queryById(Integer id) {
//创建员工对象
Employee emp = null;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
//定义根据ID查询的SQL语句
String sql = "select * from employee where id =?";
try {
//建立数据库连接
conn = JUtil.getConnection();
//创建Statement对象并对SQL语句进行预编译
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
//获取结果集
rs = ps.executeQuery();
rs.next();
Integer empId = rs.getInt(1);
String name = rs.getString(2);
Integer age = rs.getInt(3);
String sex = rs.getString(4);
emp = new Employee();
emp.setId(empId);
emp.setName(name);
emp.setAge(age);
emp.setSex(sex);
}catch (SQLException e) {
e.printStackTrace();
System.out.println("查询失败!");
}
//查询完成后断开连接,释放资源
JUtil.release(conn, ps, rs);
//返回对象,后面的测试类中获取对象并打印
return emp;
}
/**
* 查询所有员工信息
*/
public List<Employee> queryAll() {
//创建Employee对象
Employee emp = null;
//创建一个list,后面读取的员工信息保存在list中
ArrayList<Employee> list = new ArrayList<>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
//定义一个查询所有信息的SQL语句
String sql = "select * from employee";
try {
//建立Connection连接
conn = JUtil.getConnection();
//创建Statement对象并对SQL语句进行预编译
ps = conn.prepareStatement(sql);
//执行SQL语句获取结果集
rs = ps.executeQuery();
if(rs.next()) {
Integer id = rs.getInt(1);
String name = rs.getString(2);
Integer age = rs.getInt(3);
String sex = rs.getString(4);
emp = new Employee();
emp.setId(id);
emp.setName(name);
emp.setAge(age);
emp.setSex(sex);
list.add(emp);
}
}catch (SQLException e) {
e.printStackTrace();
System.out.println("数据查询失败!");
}
//查询结束后断开Connection等连接,释放资源
JUtil.release(conn, ps, rs);
//返回list集合
return list;
}
/**
* 修改员工信息并更新数据表
*/
public void updateById(Employee emp) {
Connection conn = null;
PreparedStatement ps = null;
//定义一个update的SQL语句
String sql = "update employee set name=?,age=?,sex=? where id=?";
try {
//创建数据库连接
conn = JUtil.getConnection();
//创建Statement对象并对SQL语句进行预编译
ps = conn.prepareStatement(sql);
ps.setString(1, emp.getName());
ps.setInt(2, emp.getAge());
ps.setString(3, emp.getSex());
ps.setInt(4, emp.getId());
//执行SQL语句
ps.executeUpdate();
System.out.println("员工信息更新成功!");
}catch (SQLException e) {
e.printStackTrace();
System.out.println("员工信息更新失败!");
}
//更新完成后,断开连接,释放资源
JUtil.release(conn, ps);
}
/**
* 根据员工ID删除员工信息
* @param id
*/
public void deleteById(Integer id) {
Connection conn = null;
PreparedStatement ps = null;
//定义一个delete的SQL语句
String sql = "delete from employee where id = ?";
try {
//建立数据库连接
conn = JUtil.getConnection();
//创建Statement对象并对SQL语句进行预编译
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
//执行SQL语句
ps.executeUpdate();
System.out.println("员工信息删除成功!");
}catch (SQLException e) {
e.printStackTrace();
System.out.println("员工信息删除失败!");
}
//删除完成后,断开连接,释放资源
JUtil.release(conn, ps);
}
}
5、在测试类中对EmployeeDao中方法进行测试,测试是需要在Buildpath中导入Junit这个jar包,如下所示:
@Test
//保存员工信息
public void save() {
EmployeeDao dao = new EmployeeDao();
Employee emp = new Employee(8,"tim",27,"female");
dao.save(emp);
}
/*员工信息保存成功!
Connection连接已关闭!*/
@Test
//根据员工ID查询员工信息
public void queryById() {
EmployeeDao dao = new EmployeeDao();
Employee emp = dao.queryById(6);
System.out.println(emp);
}
/*
* Connection连接已关闭!
*Statement对象已关闭!
*ResultSet结果集已关闭!
*Employee [id=6,name=Marven,age=28,sex=male]
*/
@Test
//查找全部员工信息
public void queryAll() {
EmployeeDao dao = new EmployeeDao();
List<Employee> list = dao.queryAll();
for(Employee employee : list) {
System.out.println(employee);
}
}
/*Statement对象已关闭!
ResultSet结果集已关闭!
Employee [id=1,name=Tom,age=27,sex=male]
Employee [id=2,name=Hellen,age=26,sex=female]
Employee [id=3,name=Amy,age=27,sex=female]
Employee [id=4,name=Tony,age=28,sex=male]
Employee [id=6,name=Marven,age=28,sex=male]
Employee [id=7,name=Mike,age=29,sex=male]
Employee [id=8,name=tim,age=27,sex=female]*/
@Test
//更新员工信息
public void update() {
EmployeeDao dao = new EmployeeDao();
Employee emp = new Employee(5,"ali",22,"female");
dao.update(emp);
}
/*员工信息更新成功!
Connection连接已关闭!*/
@Test
public void delete() {
EmployeeDao dao = new EmployeeDao();
dao.deleteById(8);
}
/*员工信息删除成功!
Connection连接已关闭!*/
测试类的完整代码如下:
package com.emp.jdbc;
import java.util.List;
import org.junit.Test;
/**
* @author chengjunyu
* @deprecated Employee测试类
*/
public class EmployeeTest {
@Test
//保存员工信息
public void save() {
EmployeeDao dao = new EmployeeDao();
Employee emp = new Employee(8,"tim",27,"female");
dao.save(emp);
}
@Test
//根据员工ID查找员工信息
public void queryById() {
EmployeeDao dao = new EmployeeDao();
Employee emp = dao.queryById(6);
System.out.println(emp);
}
@Test
//查找全部员工信息
public void queryAll() {
EmployeeDao dao = new EmployeeDao();
List<Employee> list = dao.queryAll();
for(Employee employee : list) {
System.out.println(employee);
}
}
@Test
//更新员工信息
public void update() {
EmployeeDao dao = new EmployeeDao();
Employee emp = new Employee(4,"ali",22,"female");
dao.update(emp);
}
@Test
//根据员工ID删除员工信息
public void delete() {
EmployeeDao dao = new EmployeeDao();
dao.deleteById(8);
}
}