练习二 读取数据库
2018/6/20 9:16:35
在之前的【练习一】中，我们在 HomeController 的 index 方法中创建了一个 List<ResourceItem> 对象。
这个是写死的，下面看如何从数据库来获取这个对象。
数据库：MySQL
连接池：HikariCP
在下面之前，请参考
基本的数据库访问类
引入依赖
<dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> <version>3.1.0</version><!--$NO-MVN-MAN-VER$--> </dependency> <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-simple 这个是为了实现slf4j的 --> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-simple</artifactId> <version>1.7.25</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.39</version> </dependency>
新建包 com.sqber.blog.base
将之前总结的 SQLHelper 等类 Copy 过来，放到新包下面。
PageResult.java
package com.sqber.blog.base;
import java.util.List;
public class PageResult<T> {
private List<T> data;
private int totalCount;
private int totalPage;
public List<T> getData() {
return data;
}
public void setData(List<T> data) {
this.data = data;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public int getTotalPage() {
return this.totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
}ResultSetHelper.java
package com.sqber.blog.base;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
public class ResultSetHelper {
public static <T> List<T> toList(ResultSet resultSet, Class<T> type) throws SQLException, InstantiationException,
IllegalAccessException, NoSuchFieldException, SecurityException {
List<T> list = new ArrayList<T>();
if (resultSet != null) {
ResultSetMetaData md = resultSet.getMetaData();// 获取键名
int columnCount = md.getColumnCount();// 获取行的数量
while (resultSet.next()) {
// 此类要有默认的构造函数
T instance = type.newInstance();
Field[] fields = type.getDeclaredFields();
for (int i = 1; i <= columnCount; i++) {
String colName = md.getColumnName(i);
Object val = resultSet.getObject(i);
for (Field field : fields) {
if (field.getName().equalsIgnoreCase(colName)) {
field.setAccessible(true);
field.set(instance, val);
}
}
}
list.add(instance);
}
}
return list;
}
public List<HashMap<String, String>> toListHashMap(ResultSet rs) throws SQLException {
if (rs == null)
return null;
List<HashMap<String, String>> result = new ArrayList<HashMap<String, String>>();
HashMap<String, String> map = new HashMap<String, String>();
ResultSetMetaData md = rs.getMetaData(); // 得到结果集的结构信息，比如字段数、字段名等
int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
while (rs.next()) {
map = new HashMap<String, String>(columnCount);
for (int i = 1; i <= columnCount; i++) {
map.put(md.getColumnName(i), String.valueOf(rs.getObject(i)));
}
result.add(map);
}
return result;
}
}SqlString.java
package com.sqber.blog.base;
import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
public class SqlString {
public static <T> String toInsertSql(Class<T> type) {
String classname = type.getSimpleName();
List<Field> fields = filterFields(type);
String[] values = new String[fields.size()];
for (int i = 0; i < values.length; i++) {
values[i] = "?";
}
String valStr = String.join(",", values);
String[] cols = new String[fields.size()];
for (int i = 0; i < fields.size(); i++) {
cols[i] = fields.get(i).getName();
}
String colStr = String.join(",", cols);
return MessageFormat.format("insert {0}({1}) values({2})", classname, colStr, valStr);
}
public static <T> List<Object> toInsertParams(T instance) throws IllegalArgumentException, IllegalAccessException {
ArrayList<Object> params = new ArrayList<Object>();
Class<?> type = instance.getClass();
List<Field> fields = filterFields(type);
for (Field field : fields) {
field.setAccessible(true);
Object val = field.get(instance);
params.add(val);
}
return params;
}
private static <T> List<Field> filterFields(Class<T> type) {
ArrayList<Field> list = new ArrayList<Field>();
String className = type.getSimpleName();
Field[] fields = type.getDeclaredFields();
for (Field field : fields) {
if (field.getName().equalsIgnoreCase("id") || field.getName().equalsIgnoreCase(className + "id")) {
continue;
}
list.add(field);
}
return list;
}
private static <T> Field primaryField(Class<T> type) throws Exception {
String className = type.getSimpleName();
Field[] fields = type.getDeclaredFields();
for (Field field : fields) {
if (field.getName().equalsIgnoreCase("id") || field.getName().equalsIgnoreCase(className + "id")) {
return field;
}
}
throw new Exception("没有主键( [id] 或 [表名id] )");
}
public static <T> String toUpdateSql(Class<T> type) throws Exception {
/* update user set username = ? where userid = ? */
String classname = type.getSimpleName();
List<Field> fields = filterFields(type);
String[] cols = new String[fields.size()];
for (int i = 0; i < fields.size(); i++) {
cols[i] = MessageFormat.format("{0} = ?", fields.get(i).getName());
}
String colStr = String.join(",", cols);
Field primaryField = primaryField(type);
String whereStr = MessageFormat.format("{0} = ?", primaryField.getName());
return MessageFormat.format("update {0} set {1} where {2}", classname, colStr, whereStr);
}
public static <T> List<Object> toUpdateParams(T instance) throws Exception {
ArrayList<Object> params = new ArrayList<Object>();
Class<?> type = instance.getClass();
List<Field> fields = filterFields(type);
for (Field field : fields) {
field.setAccessible(true);
Object val = field.get(instance);
params.add(val);
}
Field primaryField = primaryField(type);
primaryField.setAccessible(true);
params.add(primaryField.get(instance));
return params;
}
}SQLHelper.java
package com.sqber.blog.base;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.List;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
public class SQLHelper {
public static int add(String sql, List<Object> params) {
int result = -1;
try {
HikariDataSource dataSource = getDataSource();
Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
if (params != null) {
for (int i = 0; i < params.size(); i++) {
statement.setObject(i + 1, params.get(i));
}
}
statement.execute();
ResultSet resultSet = statement.getGeneratedKeys();
if (resultSet != null) {
if (resultSet.next())
result = resultSet.getInt(1);
}
if (connection != null && !connection.isClosed())
connection.close();
if (dataSource != null && !dataSource.isClosed())
dataSource.close();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
public static int update(String sql, List<Object> params) {
int result = -1;
try {
HikariDataSource dataSource = getDataSource();
Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.size(); i++) {
statement.setObject(i + 1, params.get(i));
}
}
result = statement.executeUpdate();
if (connection != null && !connection.isClosed())
connection.close();
if (dataSource != null && !dataSource.isClosed())
dataSource.close();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
public static <T> List<T> query(String sql, List<Object> params, Class<T> type) {
try {
/* HikariDataSource 是需要关闭的 */
HikariDataSource dataSource = getDataSource();
Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.size(); i++) {
statement.setObject(i + 1, params.get(i));
}
}
ResultSet resultSet = statement.executeQuery();
List<T> list = ResultSetHelper.toList(resultSet, type);
if (connection != null && !connection.isClosed())
connection.close();
if (dataSource != null && !dataSource.isClosed())
dataSource.close();
return list;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/*
* 将上面的查询语句 和 参数 抽取出来
*/
public static String executeScalar(String sql, List<Object> params) {
String result = null;
try {
/* HikariDataSource 是需要关闭的 */
HikariDataSource dataSource = getDataSource();
Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.size(); i++) {
statement.setObject(i + 1, params.get(i));
}
}
ResultSet resultSet = statement.executeQuery();
if (resultSet != null && resultSet.next())
result = resultSet.getString(1);
if (connection != null && !connection.isClosed())
connection.close();
if (dataSource != null && !dataSource.isClosed())
dataSource.close();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
public static <T> PageResult<T> queryPage(String sql, List<Object> params, int currentPage, int pageSize,
Class<T> type) {
PageResult<T> result = new PageResult<T>();
int startIndex = (currentPage - 1) * pageSize;
String querySql = MessageFormat.format("{0} limit {1},{2}", sql, startIndex, pageSize);
List<T> models = SQLHelper.query(querySql, params, type);
result.setData(models);
// 正则，将select 和 from 中间的字符串替换成 count(0);
String countSql = MessageFormat.format("select count(0) from ({0})t ", sql);
String countStr = SQLHelper.executeScalar(countSql, params);
int countVal = 0;
if (!isBlank(countStr))
countVal = Integer.parseInt(countStr);
int totalPage = (int) ((countVal + pageSize - 1) / pageSize);
result.setTotalCount(countVal);
result.setTotalPage(totalPage);
return result;
}
private static boolean isBlank(String str) {
int strLen;
if (str == null || (strLen = str.length()) == 0) {
return true;
}
for (int i = 0; i < strLen; i++) {
if (Character.isWhitespace(str.charAt(i)) == false) {
return false;
}
}
return true;
}
private static HikariDataSource getDataSource() throws SQLException {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/javablog?useUnicode=true&characterEncoding=utf8&useSSL=false");
config.setUsername("root");
config.setPassword("123456");
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
return new HikariDataSource(config);
}
}这样，基本的数据库访问类就OK了。
数据库创建
新建数据库 javablog，并执行下面的sql语句来生成 resourceitem 表结构和数据。
-- MySQL dump 10.13 Distrib 5.6.24, for Win64 (x86_64) -- -- Host: 127.0.0.1 Database: javablog -- ------------------------------------------------------ -- Server version 5.6.26-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `resourceitem` -- DROP TABLE IF EXISTS `resourceitem`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `resourceitem` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(400) DEFAULT NULL COMMENT '资源名字', `url` varchar(400) DEFAULT NULL COMMENT '资源地址', `status` int(11) DEFAULT NULL COMMENT '0：删除，1：未删除', `createtime` datetime DEFAULT NULL, `createuser` int(11) DEFAULT NULL, `updatetime` datetime DEFAULT NULL, `updateuser` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='资源项'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `resourceitem` -- LOCK TABLES `resourceitem` WRITE; /*!40000 ALTER TABLE `resourceitem` DISABLE KEYS */; INSERT INTO `resourceitem` VALUES (1,'官方Java教程','https://www.ibm.com/developerworks/cn/java',1,'2018-05-31 00:00:00',1,NULL,NULL),(2,'精选内容：developerWorks 上最受欢迎的 Java 内容','https://www.ibm.com/developerworks/cn/java/j-top-java-content-2017/index.html',1,'2018-05-31 00:00:00',1,NULL,NULL),(3,'Java快速入门','http://www.cnblogs.com/happyframework/p/3332243.html',1,'2018-05-31 00:00:00',1,NULL,NULL),(4,'W3C School 的 Java 基础教程','http://www.runoob.com/java/java-tutorial.html',1,'2018-05-31 00:00:00',1,NULL,NULL),(5,'Java 编程入门','https://www.ibm.com/developerworks/cn/java/intro-to-java-course/index.html',1,'2018-05-31 00:00:00',1,NULL,NULL),(6,'W3C School 的 Eclipse 教程','http://www.runoob.com/eclipse/eclipse-tutorial.html',1,'2018-05-31 00:00:00',1,NULL,NULL),(7,'test','test',1,'2018-05-31 00:00:00',1,NULL,NULL); /*!40000 ALTER TABLE `resourceitem` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2018-06-14 17:03:33
修改控制器
修改之前的 HomeController 下的 index 方法
@GetMapping("/")
public String index(Model model) {
String sql = "select * from ResourceItem where status = 1";
List<ResourceItem> list = SQLHelper.query(sql, null, ResourceItem.class);
model.addAttribute("items", list);
return "home/index";
}注意：在控制器层我们直接来访问的数据库，因为只有一个地方用到了 ResourceItem 列表，所以就目前而言还是可以的。
但如果多个地方用到 ResourceItem 列表，那我们就要将数据库的访问抽取到服务层了。
修改完毕之后，我们运行并修改数据库中的数据来查看效果。
版权说明
作者：SQBER
本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
{0}
{5}
{1}
{2}回复
{4}
*昵称：
*邮箱：
个人站点：
*想说的话：