JDBC的四个步骤和代码实现

95 阅读 0 评论 63 点赞

我是靠谱客的博主激情红牛，这篇文章主要介绍JDBC的四个步骤和代码实现，现在分享给大家，希望可以做个参考。

JDBC是从JAVA中连接MySQL数据库的驱动，我们可以使用编程语言来实现它，其实它的实现是固定的，只用按照这个思路和步骤就可以了。
实现分为下面四个步骤：
1.注册驱动
2.获得连接对象
3.获取statement对象

4.释放资源

在我们编程时如果需要操作数据库只需要按照这四个步骤就可以了。

我事先在Navicat for MySQL中建了一个store数据库，并且创建了一个有四个字段的学生表，如下图：

因为操作数据库无外乎增删改查这些，如果单单每一种操作都重新写代码的话，会显得冗余复杂，太LOW了，所以可以适当的对代码进行优化，具体的实现如下：

测试用得学生类如下：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
package cn.edu360.entity;

public class Student {

	private Integer id;
	private String name;
	private String gender;
	private int age;

	public Student() {
	}

	public Student(Integer id, String name, String gender, int age) {
		super();
		this.id = id;
		this.name = name;
		this.gender = gender;
		this.age = age;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", gender=" + gender + ", age=" + age + "]";
	}

	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 String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

}

将四个步骤中的1、2、4封装成cn.edu360.utils如下：

复制代码

package cn.edu360.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
 * JDBC四个步骤
 */
public class JDBCUtils {

private static String driverName = "com.mysql.jdbc.Driver";
	private static String jdbc_url = "jdbc:mysql://localhost:3306/store";
	private static String username = "root";
	private static String password = "12580";

// 1.注册驱动，只需要注册一次即可（在静态代码中执行一次即可）
	static {
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

// 2.获得连接对象，以后需要多次获得
	public static Connection getConnection() {
		try {
			return DriverManager.getConnection(jdbc_url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

// 3.获取statement对象，需要反复执行，不需要定义这个方法了

// 4.释放资源，需要反复执行的(使用方法的重载来定义两个不同参数列表的释放方法)
	public static void release(Statement stmt, Connection connection) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt = null;
		}

if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			connection = null;
		}
	}

public static void release(ResultSet rs, Statement stmt, Connection connection) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}

if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt = null;
		}

if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			connection = null;
		}
	}

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
package cn.edu360.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
 * JDBC四个步骤
 */
public class JDBCUtils {

	private static String driverName = "com.mysql.jdbc.Driver";
	private static String jdbc_url = "jdbc:mysql://localhost:3306/store";
	private static String username = "root";
	private static String password = "12580";

	// 1.注册驱动，只需要注册一次即可（在静态代码中执行一次即可）
	static {
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// 2.获得连接对象，以后需要多次获得
	public static Connection getConnection() {
		try {
			return DriverManager.getConnection(jdbc_url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	// 3.获取statement对象，需要反复执行，不需要定义这个方法了

	// 4.释放资源，需要反复执行的(使用方法的重载来定义两个不同参数列表的释放方法)
	public static void release(Statement stmt, Connection connection) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt = null;
		}

		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			connection = null;
		}
	}

	public static void release(ResultSet rs, Statement stmt, Connection connection) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}

		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt = null;
		}

		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			connection = null;
		}
	}

}

增删改查封装成cn.edu360.dao如下：

复制代码

package cn.edu360.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu360.entity.Student;
import cn.edu360.utils.JDBCUtils;

public class StudentDao {

/**
	 * 添加数据到MySQL中
	 * 
	 * @param student
	 */
	public void save(Student student) {
		// 获得connection
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement prepareStatement = null;

// 创建Statement对象
		// 定义一个sql
		String sql = "INSERT INTO student VALUES (?, ?, ?, ?)";
		try {
			prepareStatement = connection.prepareStatement(sql);
			
			prepareStatement.setInt(1, student.getId());
			prepareStatement.setString(2, student.getName());
			prepareStatement.setString(3, student.getGender());
			prepareStatement.setInt(4, student.getAge());
			// 执行sql语句
			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

// 释放资源
		JDBCUtils.release(prepareStatement, connection);
	}

/**
	 * 根据ID号删除数据
	 * 
	 * @param id
	 */
	public void deleteById(Integer id) {
		// 获得connection
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement prepareStatement = null;

// 创建Statement对象
		// 定义一个sql
		String sql = "DELETE FROM student WHERE id = ?";
		try {
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1, id);
			// 执行sql语句
			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 释放资源
		JDBCUtils.release(prepareStatement, connection);
	}

/**
	 * 更新学生信息
	 * 
	 * @param student
	 */
	public void update(Student student) {
		// 获得connection
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement prepareStatement = null;

// 创建Statement对象
		// 定义一个sql
		String sql = "UPDATE student SET name = ? , gender = ? , age = ? WHERE id = ?";
		try {
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setString(1, student.getName());
			prepareStatement.setString(2, student.getGender());
			prepareStatement.setInt(3, student.getAge());
			prepareStatement.setInt(4, student.getId());
			// 执行sql语句
			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 释放资源
		JDBCUtils.release(prepareStatement, connection);
	}

/**
	 * 根据ID号查找学生信息
	 * 
	 * @param id
	 * @return
	 */
	public Student getById(Integer id) {
		Student s = null;
		// 获得connection
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement prepareStatement = null;
		ResultSet rs =null;
		// 创建Statement对象
		// 定义一个sql
		String sql = "SELECT * FROM student WHERE id = ?";
		try {
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1,id);
			// 执行sql语句
			rs = prepareStatement.executeQuery();
			// 从rs中得到数据
			rs.next();
			Integer sid = rs.getInt(1);
			String name = rs.getString(2);
			String gender = rs.getString(3);
			int age = rs.getInt(4);
			s = new Student();
			s.setId(sid);
			s.setName(name);
			s.setGender(gender);
			s.setAge(age);
		} catch (SQLException e) {
			e.printStackTrace();
		}

// 释放资源
		JDBCUtils.release(rs,prepareStatement, connection);
		return s;
	}

/**
	 * 查找所有学生信息
	 * 
	 * @return
	 */
	public List<Student> findAll() {
		ArrayList<Student> list = new ArrayList<Student>();
		// 获得connection
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement prepareStatement = null;
		ResultSet rs = null;
		// 创建Statement对象
		// 定义一个sql
		String sql = "SELECT * FROM student";
		try {
			prepareStatement = connection.prepareStatement(sql);
			// 执行sql语句
			rs = prepareStatement.executeQuery();
			while (rs.next()) {
				// 从rs中得到数据
				Integer sid = rs.getInt(1);
				String name = rs.getString(2);
				String gender = rs.getString(3);
				int age = rs.getInt(4);
				Student s = new Student();
				s.setId(sid);
				s.setName(name);
				s.setGender(gender);
				s.setAge(age);
				list.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 释放资源
		JDBCUtils.release(rs,prepareStatement, connection);
		return list;
	}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
package cn.edu360.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu360.entity.Student;
import cn.edu360.utils.JDBCUtils;

public class StudentDao {

	/**
	 * 添加数据到MySQL中
	 * 
	 * @param student
	 */
	public void save(Student student) {
		// 获得connection
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement prepareStatement = null;

		// 创建Statement对象
		// 定义一个sql
		String sql = "INSERT INTO student VALUES (?, ?, ?, ?)";
		try {
			prepareStatement = connection.prepareStatement(sql);
			
			prepareStatement.setInt(1, student.getId());
			prepareStatement.setString(2, student.getName());
			prepareStatement.setString(3, student.getGender());
			prepareStatement.setInt(4, student.getAge());
			// 执行sql语句
			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		// 释放资源
		JDBCUtils.release(prepareStatement, connection);
	}

	/**
	 * 根据ID号删除数据
	 * 
	 * @param id
	 */
	public void deleteById(Integer id) {
		// 获得connection
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement prepareStatement = null;

		// 创建Statement对象
		// 定义一个sql
		String sql = "DELETE FROM student WHERE id = ?";
		try {
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1, id);
			// 执行sql语句
			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 释放资源
		JDBCUtils.release(prepareStatement, connection);
	}

	/**
	 * 更新学生信息
	 * 
	 * @param student
	 */
	public void update(Student student) {
		// 获得connection
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement prepareStatement = null;

		// 创建Statement对象
		// 定义一个sql
		String sql = "UPDATE student SET name = ? , gender = ? , age = ? WHERE id = ?";
		try {
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setString(1, student.getName());
			prepareStatement.setString(2, student.getGender());
			prepareStatement.setInt(3, student.getAge());
			prepareStatement.setInt(4, student.getId());
			// 执行sql语句
			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 释放资源
		JDBCUtils.release(prepareStatement, connection);
	}

	/**
	 * 根据ID号查找学生信息
	 * 
	 * @param id
	 * @return
	 */
	public Student getById(Integer id) {
		Student s = null;
		// 获得connection
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement prepareStatement = null;
		ResultSet rs =null;
		// 创建Statement对象
		// 定义一个sql
		String sql = "SELECT * FROM student WHERE id = ?";
		try {
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1,id);
			// 执行sql语句
			rs = prepareStatement.executeQuery();
			// 从rs中得到数据
			rs.next();
			Integer sid = rs.getInt(1);
			String name = rs.getString(2);
			String gender = rs.getString(3);
			int age = rs.getInt(4);
			s = new Student();
			s.setId(sid);
			s.setName(name);
			s.setGender(gender);
			s.setAge(age);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		// 释放资源
		JDBCUtils.release(rs,prepareStatement, connection);
		return s;
	}

	/**
	 * 查找所有学生信息
	 * 
	 * @return
	 */
	public List<Student> findAll() {
		ArrayList<Student> list = new ArrayList<Student>();
		// 获得connection
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement prepareStatement = null;
		ResultSet rs = null;
		// 创建Statement对象
		// 定义一个sql
		String sql = "SELECT * FROM student";
		try {
			prepareStatement = connection.prepareStatement(sql);
			// 执行sql语句
			rs = prepareStatement.executeQuery();
			while (rs.next()) {
				// 从rs中得到数据
				Integer sid = rs.getInt(1);
				String name = rs.getString(2);
				String gender = rs.getString(3);
				int age = rs.getInt(4);
				Student s = new Student();
				s.setId(sid);
				s.setName(name);
				s.setGender(gender);
				s.setAge(age);
				list.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 释放资源
		JDBCUtils.release(rs,prepareStatement, connection);
		return list;
	}
}

测试代码如下：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
package cn.edu360.test;

import java.util.List;

import org.junit.Test;

import cn.edu360.dao.StudentDao;
import cn.edu360.entity.Student;

public class TestDao {

	@Test
	public void testSave() {
		StudentDao dao = new StudentDao();
		Student s = new Student(11, "tom", "f", 20);
		dao.save(s);
	}

	@Test
	public void testDelete() {
		StudentDao dao = new StudentDao();
		dao.deleteById(11);
	}

	@Test
	public void testUpdate() {
		StudentDao dao = new StudentDao();
		Student s = new Student(1, "zhaozhoa", "m", 18);
		dao.update(s);
	}

	@Test
	public void testGetById() {
		StudentDao dao = new StudentDao();
		Student s = dao.getById(2);
		System.out.println(s);
	}

	@Test
	public void testFindAll() {
		StudentDao dao = new StudentDao();
		List<Student> list = dao.findAll();
		for (Student s : list) {
			System.out.println(s);
		}
	}
}