本文内容大多基于官方文档和网上前辈经验总结,经过个人实践加以整理积累,仅供参考。
1 准备测试用数据表及数据
2 编写对应数据表结构的 JavaBean
public class Admin {
private long id;
private String username;
private String password;
public Admin() {}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "Admin [id=" + id + ", username=" + username + ", password=" + password + "]";
}
}
import java.math.BigDecimal;
public class Product {
private long id;
private String label;
private BigDecimal price;
private String manufacturer;
public Product() {}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getLabel() {
return label;
}
public void setLabel(String label) {
this.label = label;
}
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
public String getManufacturer() {
return manufacturer;
}
public void setManufacturer(String manufacturer) {
this.manufacturer = manufacturer;
}
@Override
public String toString() {
return "Product [id=" + id + ", label=" + label + ", price=" + price + ", manufacturer=" + manufacturer + "]";
}
}
3 利用 ResultSetMetaData 和反射编写通用查询方法
public <T> T getT(Class<T> clazz, String sql, Object ... params)
throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
T entity = null;
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
for (int i = 1; i <= params.length; i++) {
statement.setObject(i, params[i - 1]);
}
resultSet = statement.executeQuery();
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
Map<String, Object> queryResult = new HashMap<>();
if (resultSet.next()) {
for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
queryResult.put(resultSetMetaData.getColumnLabel(i), resultSet.getObject(i));
}
}
if (queryResult.size() > 0) {
entity = clazz.newInstance();
for (Map.Entry<String, Object> entry : queryResult.entrySet()) {
// 使用Apache Commons BeanUtils库
PropertyUtils.setSimpleProperty(entity, entry.getKey(), entry.getValue());
}
}
} finally {
release(resultSet, statement, connection);
}
return entity;
}
public Connection getConnection()
throws ClassNotFoundException, SQLException {
String driverClass = "com.mysql.jdbc.Driver";
String jdbcUrl = "jdbc:mysql:///test?useSSL=false";
String user = "root";
String password = "123456";
return getConnection(driverClass, jdbcUrl, user, password);
}
public Connection getConnection(String driverClass, String jdbcUrl, String user, String password)
throws ClassNotFoundException, SQLException {
Class.forName(driverClass);
return DriverManager.getConnection(jdbcUrl, user, password);
}
private void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
4 测试查询单个结果
@Test
public void test()
throws ClassNotFoundException, InstantiationException, IllegalAccessException,
InvocationTargetException, NoSuchMethodException, SQLException {
String sql = "SELECT id AS id, username AS username, password AS password FROM admin WHERE id=?";
Admin admin = getT(Admin.class, sql, 1);
System.out.print("Admin ID[1] : ");
System.out.println(admin);
admin = getT(Admin.class, sql, 2);
System.out.print("Admin ID[2] : ");
System.out.println(admin);
System.out.println("-----------------------------------------------------------------------------");
sql = "SELECT id AS id, label AS label, price AS price, manufacturer AS manufacturer FROM product WHERE id=?";
Product product = getT(Product.class, sql, 1);
System.out.print("Product ID[1] : ");
System.out.println(product);
product = getT(Product.class, sql, 6);
System.out.print("Product ID[6] : ");
System.out.println(product);
}
测试结果:
5 很多情况下查询得到的结果不止一条,是一个结果集,则编写获取一组查询结果的通用方法
public <T> List<T> getTList(Class<T> clazz, String sql, Object ... params)
throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
List<T> tList = new ArrayList<>();
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
for (int i = 1; i <= params.length; i++) {
statement.setObject(i, params[i - 1]);
}
resultSet = statement.executeQuery();
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
while (resultSet.next()) {
Map<String, Object> queryResult = new HashMap<>();
for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
queryResult.put(resultSetMetaData.getColumnLabel(i), resultSet.getObject(i));
}
if (queryResult.size() > 0) {
T objectT = clazz.newInstance();
for (Map.Entry<String, Object> entry : queryResult.entrySet()) {
PropertyUtils.setProperty(objectT, entry.getKey(), entry.getValue());
}
tList.add(objectT);
}
}
} finally {
release(resultSet, statement, connection);
}
return tList;
}
6 测试查询一组结果集
@Test
public void testList()
throws ClassNotFoundException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, SQLException {
String sql = "SELECT id AS id, username AS username, password AS password FROM admin";
List<Admin> admins = getTList(Admin.class, sql);
for (int i = 1; i <= admins.size(); i++) {
System.out.println("Admin[" + i + "] : " + admins.get(i - 1));
}
System.out.println("-----------------------------------------------------------------------------");
sql = "SELECT id AS id, label AS label, price AS price, manufacturer AS manufacturer FROM product WHERE id>?";
List<Product> products = getTList(Product.class, sql, 3);
for (int i = 1; i <= products.size(); i++) {
System.out.println("Product[" + i + "] : " + products.get(i - 1));
}
}
测试结果:
7 完整代码
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.PropertyUtils;
import org.junit.Test;
public class JDBCTest {
@Test
public void test()
throws ClassNotFoundException, InstantiationException, IllegalAccessException,
InvocationTargetException, NoSuchMethodException, SQLException {
String sql = "SELECT id AS id, username AS username, password AS password FROM admin WHERE id=?";
Admin admin = getT(Admin.class, sql, 1);
System.out.print("Admin ID[1] : ");
System.out.println(admin);
admin = getT(Admin.class, sql, 2);
System.out.print("Admin ID[2] : ");
System.out.println(admin);
System.out.println("-----------------------------------------------------------------------------");
sql = "SELECT id AS id, label AS label, price AS price, manufacturer AS manufacturer FROM product WHERE id=?";
Product product = getT(Product.class, sql, 1);
System.out.print("Product ID[1] : ");
System.out.println(product);
product = getT(Product.class, sql, 6);
System.out.print("Product ID[6] : ");
System.out.println(product);
}
@Test
public void testList()
throws ClassNotFoundException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, SQLException {
String sql = "SELECT id AS id, username AS username, password AS password FROM admin";
List<Admin> admins = getTList(Admin.class, sql);
for (int i = 1; i <= admins.size(); i++) {
System.out.println("Admin[" + i + "] : " + admins.get(i - 1));
}
System.out.println("-----------------------------------------------------------------------------");
sql = "SELECT id AS id, label AS label, price AS price, manufacturer AS manufacturer FROM product WHERE id>?";
List<Product> products = getTList(Product.class, sql, 3);
for (int i = 1; i <= products.size(); i++) {
System.out.println("Product[" + i + "] : " + products.get(i - 1));
}
}
public <T> T getT(Class<T> clazz, String sql, Object ... params)
throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
T entity = null;
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
for (int i = 1; i <= params.length; i++) {
statement.setObject(i, params[i - 1]);
}
resultSet = statement.executeQuery();
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
Map<String, Object> queryResult = new HashMap<>();
if (resultSet.next()) {
for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
queryResult.put(resultSetMetaData.getColumnLabel(i), resultSet.getObject(i));
}
}
if (queryResult.size() > 0) {
entity = clazz.newInstance();
for (Map.Entry<String, Object> entry : queryResult.entrySet()) {
// 使用Apache Commons BeanUtils库
PropertyUtils.setSimpleProperty(entity, entry.getKey(), entry.getValue());
}
}
} finally {
release(resultSet, statement, connection);
}
return entity;
}
public <T> List<T> getTList(Class<T> clazz, String sql, Object ... params)
throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
List<T> tList = new ArrayList<>();
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
for (int i = 1; i <= params.length; i++) {
statement.setObject(i, params[i - 1]);
}
resultSet = statement.executeQuery();
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
while (resultSet.next()) {
Map<String, Object> queryResult = new HashMap<>();
for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
queryResult.put(resultSetMetaData.getColumnLabel(i), resultSet.getObject(i));
}
if (queryResult.size() > 0) {
T objectT = clazz.newInstance();
for (Map.Entry<String, Object> entry : queryResult.entrySet()) {
PropertyUtils.setProperty(objectT, entry.getKey(), entry.getValue());
}
tList.add(objectT);
}
}
} finally {
release(resultSet, statement, connection);
}
return tList;
}
public Connection getConnection()
throws ClassNotFoundException, SQLException {
String driverClass = "com.mysql.jdbc.Driver";
String jdbcUrl = "jdbc:mysql:///test?useSSL=false";
String user = "root";
String password = "123456";
return getConnection(driverClass, jdbcUrl, user, password);
}
public Connection getConnection(String driverClass, String jdbcUrl, String user, String password)
throws ClassNotFoundException, SQLException {
Class.forName(driverClass);
return DriverManager.getConnection(jdbcUrl, user, password);
}
private void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}