前提要求
- 能够使用jdbc链接数据库
- 能够利用jdbc完成数据库的增删改查等操作
- 对泛型有一定的了解
基本原理
- 通过jdbc链接数据库,查询数据库中内容
- 利用反射对数据库中查询字段进行封装
步骤
1、创建项目
- 创建Maven工程
- 输入项目名称
- 创建
resource文件
目录:父目录为java
文件夹
- 设置resource文件夹为resource文件夹:单击文件,项目结构
工程创建到此结束。
2、代码编写
2.1 maven导包,配置pom文件
- 添加依赖
mysql依赖、druid连接池依赖
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.5</version>
</dependency>
2.2 druid配置
在resource
文件夹下面创建文件并命名为:druid.properties
名称可以随便
并在文件中添加配置信息:
druid.url=jdbc:mysql://localhost:3306/demo?useUnicode=true&serverTimezone=Asia/Shanghai
druid.driverClassName=com.mysql.cj.jdbc.Driver
druid.username=root
druid.password=123456
根据自己的不同,自行修改
2.3 创建数据库以及表
CREATE DATABASE /*!32312 IF NOT EXISTS*/`demo` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `demo`;
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`username` varchar(30) COLLATE utf8mb4_general_ci NOT NULL,
`age` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*Data for the table `user` */
insert into `user`(`id`,`username`,`age`) values
(1,'张三',20),
(2,'李四',21),
(3,'王五',33),
(4,'陈六',25);
创建之后的数据展示:
2.4 创建数据库对应的Java实体类
import java.util.Objects;
public class User {
private Long id;
private String username;
private Integer age;
public User() {
}
public User(Long id, String username, Integer age) {
this.id = id;
this.username = username;
this.age = age;
}
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 Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
User user = (User) o;
return Objects.equals(id, user.id) && Objects.equals(username, user.username) && Objects.equals(age, user.age);
}
@Override
public int hashCode() {
return Objects.hash(id, username, age);
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", age=" + age +
'}';
}
}
2.5 创建DBUtils
类
public class DBUtils {
private static DataSource dataSource;
static {
// 配置文件
try {
InputStream is = App.class.getClassLoader().getResourceAsStream("druid.properties");
Properties properties = new Properties();
properties.load(is);
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (IllegalArgumentException e) {
System.out.println("文件有误");
} catch (IOException e) {
System.out.println("打开配置文件失败");
} catch (Exception e) {
System.out.println("德鲁伊数据源创建失败");
}
}
/**
* 获取链接
*
* @return 数据库链接
*/
public static Connection getConnection() {
Connection connection = null;
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* 资源释放
*/
public static void release(Connection connection, Statement statement, RowSet rowSet) {
try {
release(connection, statement, null, rowSet);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
try {
release(connection, statement, resultSet, null);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void release(Connection connection, Statement statement, ResultSet resultSet, RowSet rowSet) throws SQLException {
if (rowSet != null) {
rowSet.close();
}
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}
}
2.6 创建JDBCTemplate
抽象类
import java.util.List;
public abstract class JdbcTemplate<T> {
protected Class<T> entityClass;
protected JdbcTemplate(Class<T> entityClass) {
this.entityClass = entityClass;
}
public abstract List<T> query(String sql, Object... args);
}
2.7 创建QueryTemplate
查询模板
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;
public class QueryTemplate<T> extends JdbcTemplate<T> {
public QueryTemplate(Class<T> entityClass) {
super(entityClass);
}
@Override
public List<T> query(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<T> result = new ArrayList<>();
try {
connection = DBUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
// 设置参数
for (int i = 1; i <= args.length; i++) {
setValue(i, args[i - 1], preparedStatement);
}
resultSet = preparedStatement.executeQuery();
Map<String, String> metaMap = getMetaMap(preparedStatement);
while (resultSet.next()) {
T entity = getEntity(preparedStatement, resultSet);
result.add(entity);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.release(connection, preparedStatement, resultSet);
}
return result;
}
/**
* 获得具体的对象,并赋值
*/
private T getEntity(PreparedStatement preparedStatement, ResultSet resultSet) {
Map<String, String> metaMap = getMetaMap(preparedStatement);
try {
T object = entityClass.newInstance();
Set<String> keySet = metaMap.keySet();
for (String key : keySet) {
Object value = getValue(key, metaMap.get(key), resultSet);
// 获取属性
Field field = entityClass.getDeclaredField(key);
Method declaredMethod = entityClass.getMethod(constructSetMethod(key), field.getType());
declaredMethod.invoke(object, value);
}
return object;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 获得数据库中 字段名称与数据类型的映射
* @return Map
*/
private static Map<String, String> getMetaMap(PreparedStatement preparedStatement) {
Map<String, String> metaMap = new HashMap<>();
try {
ResultSetMetaData metaData = preparedStatement.getMetaData();
// 遍历元素
for (int i = 1; i <= metaData.getColumnCount(); i++) {
String columnName = metaData.getColumnName(i);
String columnTypeName = metaData.getColumnTypeName(i);
metaMap.put(columnName, columnTypeName);
}
} catch (SQLException e) {
e.printStackTrace();
}
return metaMap;
}
/**
* 根据具体字段获得其属性赋值方法
* @param key 成员名称
* @return 方法名称
*/
private static String constructSetMethod(String key) {
String substring = key.substring(0, 1).toUpperCase() + key.substring(1);
return "set" + substring;
}
/**
* 预编译statement赋值
* @param index 参数占位符 索引
* @param value 具体值
*/
private static void setValue(int index, Object value, PreparedStatement preparedStatement) {
// 获得类型
String typeName = value.getClass().getTypeName();
try {
switch (typeName) {
case "java.lang.Long":
preparedStatement.setLong(index, Long.parseLong(value.toString()));
break;
case "java.lang.String":
preparedStatement.setString(index, value.toString());
break;
case "java.lang.Integer":
preparedStatement.setInt(index, Integer.parseInt(value.toString()));
break;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 从ResultSet中获取对应的值
* @param key 字段
* @param typeName 返回数据类型
* @param resultSet 结果集
* @return 值
*/
private static Object getValue(String key, String typeName, ResultSet resultSet) {
Object value = null;
try {
switch (typeName) {
case "BIGINT":
value = resultSet.getLong(key);
break;
case "INT":
value = resultSet.getInt(key);
break;
case "VARCHAR":
value = resultSet.getString(key);
break;
}
} catch (SQLException e) {
e.printStackTrace();
}
return value;
}
}
创建程序入口类App
public class App {
public static void main(String[] args) {
JdbcTemplate<User> jdbcTemplate= new QueryTemplate<>(User.class);
// 定义sql语句
String sql = "select * from user where age > 20";
// 执行
List<User> users = jdbcTemplate.query(sql);
for (User user : users) {
System.out.println(user);
}
}
}
运行结果:
总结: 通过这段代码,基本上就能够知道如何去封装一个数据集,这也是ORM框架的基本封装思路,主要是数据查询比较麻烦,数据封装较为繁琐,其他的方法可以入法炮制,这里不再演示。读者也可以自行测试其他类,或者完成其他操作的封装。