- 前言
- 一、JDBC连接数据库
- 1. 引入依赖
- 2. 连接数据库
- 二、自定义JDBCUtils
- 1. JDBCUtils
- 2. BeanJDBCUtils
- 3. 使用自定义JDBC类操作数据库
JDBC 指 Java 数据库连接,是一种标准Java应用编程接口( JAVA API),用来连接 Java 编程语言和广泛的数据库。
本文使用的是JDBC连接MySQL数据库
MySQL版本为8.0.28
使用maven引入依赖包
如果MySQL版本为8.x.xx
mysql mysql-connector-java8.0.29
如果MySQL版本为 5.x.xx
则引入可用
2. 连接数据库mysql mysql-connector-java5.1.45
使用JDBC连接数据库条件
- 需要指定连接的路径url = "jdbc:mysql://localhost:3306/fruitdb"
- 需要指定用户名userName = "root"
- 需要指定用户的密码password = "xxxxxxx"
- 需要MySQL驱动diver = com.mysql.cj.jdbc.Driver
url 格式为:jdbc:mysql://地址或主机名:端口号/数据库名
driver
- 如果MySQL版本为8.x.xx为 com.mysql.cj.jdbc.Driver
- 如果MySQL版本为5.x.xx为 com.mysql.jdbc.Driver
代码如下:
public class JDBCUtils {
private static final String url;
private static final String userName;
private static final String password;
private static final String driver;
static {
Properties properties = new Properties();
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
properties.load(is);
url = properties.getProperty("url");
userName = properties.getProperty("userName");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
//注册驱动
Class.forName(driver);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public Connection getConnection() {
try {
return DriverManager.getConnection(url, userName, password);
} catch (SQLException e) {
throw new RuntimeException("无法连接到数据库n");
}
}
}
jdbc.properties文件的路径我放在资源文件下的
jdbc.properties文件的内容:
url=jdbc:mysql://localhost:3306/fruitdb
userName=root
password=2043602397l
driver=com.mysql.cj.jdbc.Driver
测试连接是否成功:
我采用的是:
- JDBCUtils来连接和关闭数据库相关资源。
- 再写一个类(BeanJDBCUtils)来进行数据库的基本操作。
- 最后写一个类来实现具体的操作。
代码如下:
@SuppressWarnings("all")
public class JDBCUtils {
private static final String url;
private static final String userName;
private static final String password;
private static final String driver;
static {
Properties properties = new Properties();
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
properties.load(is);
url = properties.getProperty("url");
userName = properties.getProperty("userName");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
//注册驱动
Class.forName(driver);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public Connection getConnection() {
try {
return DriverManager.getConnection(url, userName, password);
} catch (SQLException e) {
throw new RuntimeException("无法连接到数据库n");
}
}
public void close(Connection connection) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void close(Connection connection, ResultSet resultSet) {
try {
connection.close();
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
2. BeanJDBCUtils
代码如下:
@SuppressWarnings("all")
public class BeanJDBCUtils extends JDBCUtils {
private Connection connection;
private PreparedStatement preparedStatement;
private Class entityClass;
public BeanJDBCUtils() {
//getClass() 获取Class对象,当前我们执行的是new FruitDAOImpl() , 创建的是FruitDAOImpl的实例
//那么子类构造方法内部首先会调用父类(BaseDAO)的无参构造方法
//因此此处的getClass()会被执行,但是getClass获取的是FruitDAOImpl的Class
//所以getGenericSuperclass()获取到的是BaseJDBCUtils的Class
//System.out.println("获取父类对象:" + clazz.getSuperclass());
Type genericType = getClass().getGenericSuperclass();
//ParameterizedType 参数化类型
Type[] actualTypeArguments = ((ParameterizedType) genericType).getActualTypeArguments();
//获取到的中的T的真实的类型
Type actualType = actualTypeArguments[0];
try {
entityClass = Class.forName(actualType.getTypeName());
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public List query(String sql, Object... args) {
List list = new ArrayList<>();
connection = getConnection();
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; ++i)
preparedStatement.setObject(i + 1, args[i]);
preparedStatement.execute();
resultSet = preparedStatement.getResultSet();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while (resultSet.next()) {
T entity = (T) entityClass.newInstance();
Object object = new Object();
for (int i = 0; i < columnCount; ++i) {
String columnLabel = metaData.getColumnLabel(i + 1);
object = resultSet.getObject(i + 1);
Field field = entity.getClass().getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(entity, object);
}
list.add(entity);
}
return list;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
close(connection, resultSet);
}
}
public T query(String sql, Class clazz, Object... args) {
connection = getConnection();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; ++i)
preparedStatement.setObject(i + 1, args[i]);
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
T t = (T) entityClass.newInstance();
if (resultSet.next()) {
Object object = new Object();
for (int i = 0; i < columnCount; i++) {
String columnLabel = metaData.getColumnLabel(i + 1);
Field field = t.getClass().getDeclaredField(columnLabel);
field.setAccessible(true);
object = resultSet.getObject(columnLabel);
field.set(t, object);
}
}
return t;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
close(connection, resultSet);
}
}
public Integer updata(String sql, Object... args) {
connection = getConnection();
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; ++i)
preparedStatement.setObject(i + 1, args[i]);
return preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
close(connection);
}
}
public E getElement(String sql, Object... args) {
connection = getConnection();
ResultSet resultSet = null;
try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
Object object = null;
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
object = resultSet.getObject(1);
}
return (E) object;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
close(connection, resultSet);
}
}
}
3. 使用自定义JDBC类操作数据库
表名如下
表中内容如下
创建一个与数据库表t_fruit一一对应的Fruit类
import lombok.*;
@ToString
@Data
@NoArgsConstructor
@AllArgsConstructor
@SuppressWarnings("all")
public class Fruit {
private Integer fid;
private String fname;
private Integer price;
private Integer fcount;
private String remark;
public Fruit(String fname, Integer price, Integer fcount, String remark) {
this.fname = fname;
this.price = price;
this.fcount = fcount;
this.remark = remark;
}
}
创建一个接口(FruitDAO)来声明对改数据库的表中的操作的方法。
import java.util.List;
public interface FruitDAO {
List getAllFruit();
Integer addFruit(Fruit fruit);
Long getAllCount();
Integer getMaxPrice();
Integer getMinPrice();
Fruit getFruitById(Integer id);
}
对上述接口(FruitDAO)的实现类(FruitDAOImpl)
public class FruitDAOImpl extends BeanJDBCUtilsimplements FruitDAO { @Override public List getAllFruit() { String sql = "select * from t_fruit"; return query(sql); } @Override public Integer addFruit(Fruit fruit) { String sql = "insert into t_fruit(fname,price,fcount,remark) values(?,?,?,?)"; return updata(sql, fruit.getFname(), fruit.getPrice(), fruit.getFcount(), fruit.getRemark()); } @Override public Long getAllCount() { String sql = "select count(*) from t_fruit"; return getElement(sql); } @Override public Integer getMaxPrice() { String sql = "select max(price) from t_fruit"; return getElement(sql); } @Override public Integer getMinPrice() { String sql = "select min(price) from t_fruit"; return getElement(sql); } @Override public Fruit getFruitById(Integer id) { String sql = "select * from t_fruit where fid = ?"; return query(sql, Fruit.class, id); } }
测试结果:
数据库和代码文件可以通过下方链接进行下载
https://github.com/inTheSummer-long/useJDBCtoMySQL



