练习二 读取数据库

时间 2018/6/20 9:16:35 加载中...

在之前的【练习一】中,我们在 HomeController 的 index 方法中创建了一个  List<ResourceItem> 对象。

这个是写死的,下面看如何从数据库来获取这个对象。


数据库:MySQL

连接池:HikariCP


在下面之前,请参考

HikariCP 简单使用

HikariCP 参数化查询

HikariCP 抽取方法&分页

HikariCP 简易ORM


基本的数据库访问类


引入依赖

<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
文章来源:http://blog.sqber.com/articles/java-test2-db.html
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。