1.准备工作
1.1 MySQL建立表与初始化数据
create table es_t_shop_affiche(
afficheid int primary key,
affichetitle varchar(200)
)ENGINE=INNODB default charset utf8;
insert into es_t_shop_affiche values(10001,'admin');
insert into es_t_shop_affiche values(10001,'root');
1.2 编写创建与关闭数据库连接的工具类
说明:该类只为演示功能,真正投入生产系统需借助连接池,后续会给出连接池最基本的实现
并且此处需要引入MySQL的驱动,本例使用mysql-connector-java-5.1.23-bin.jar
package com.yli.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* 创建与关闭JDBC连接
* @author yli
*
*/
public class ConnectionUtil {
/**
* 创建连接
* @return
*/
public static Connection getConnection() {
String url = "jdbc:mysql://localhost:3306/world";
String user = "root";
String password = "123456";
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return conn;
}
/**
* 关闭连接
* @param conn
*/
public static void close(Connection conn){
if(null != conn) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
1.3 为方便查询结果集ResultSet编写工具类RowMapper
package com.yli.dal.util;
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;
import java.util.Map;
/**
* 简单的结果集与Java对象转换
* @author yli
*
* @param <T>
*/
public class RowMapper<T> {
/**
* 遍历结果集,将数据转换成指定的class类型<br>
* 适合结果集包含多条记录
* @param classes
* @param rs
* @return
*/
public static <T> List<T> getForList(Class<T> classes, ResultSet rs) {
List<T> resultList = new ArrayList<T>();
Field[] fileds = classes.getDeclaredFields();
try {
T object;
while (rs.next()) {
object = getObject(classes, rs, fileds);
if (null != object) {
resultList.add(object);
}
}
return resultList;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 遍历结果集,将数据转换成指定的class类型<br>
* 适合结果集包含单条记录,如果不是则取第一条
* @param classes
* @param rs
* @return
*/
public static <T> T getForObject(Class<T> classes, ResultSet rs) {
List<T> resultList = getForList(classes, rs);
if (null != resultList && !resultList.isEmpty()) {
return resultList.get(0);
}
return null;
}
/**
* 遍历结果集,将数据转换成指定的List<Map<String, Object>>类型<br>
* 适合结果集包含单条记录
* @param rs
*/
public static List<Map<String, Object>> getForList(ResultSet rs) {
List<Map<String, Object>> resultList = null;
try {
resultList = new ArrayList<Map<String, Object>>();
ResultSetMetaData rsMeta = rs.getMetaData();
int columnCount = rsMeta.getColumnCount();
int index;
String columnLabel;
while (rs.next()) {
Map<String, Object> resultMap = new HashMap<String, Object>();
for (index = 1; index <= columnCount; index++) {
columnLabel = rsMeta.getColumnLabel(index);
resultMap.put(columnLabel, rs.getObject(columnLabel));
}
resultList.add(resultMap);
}
} catch (SQLException e) {
e.printStackTrace();
}
return resultList;
}
/**
* 遍历结果集,将数据转换成指定的Map<String, Object>类型<br>
* 适合结果集包含单条记录,如果不是则取第一条
* @param rs
*/
public static Map<String, Object> getForMap(ResultSet rs) {
List<Map<String, Object>> resultList = getForList(rs);
if (null != resultList && !resultList.isEmpty()) {
return resultList.get(0);
}
return null;
}
private static <T> T getObject(Class<T> classes, ResultSet rs, Field[] fileds) {
T object = null;
try {
object = classes.newInstance();
for (Field field : fileds) {
String fieldName = field.getName();
field.setAccessible(true);
field.set(object, rs.getObject(fieldName));
}
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return object;
}
}
1.4 为方便本例演示,编写与ES_T_SHOP_AFFICHE表结构字段对应的实体类
package com.yli.entity;
/**
* 简单实体,与数据表ES_T_SHOP_AFFICHE字段一一对应<br>
* 方便将结果集ResultSet映射到该实体上
* @author yli
*
*/
public class ShopAffiche {
private long afficheid;
private String affichetitle;
public long getAfficheid() {
return afficheid;
}
public void setAfficheid(long afficheid) {
this.afficheid = afficheid;
}
public String getAffichetitle() {
return affichetitle;
}
public void setAffichetitle(String affichetitle) {
this.affichetitle = affichetitle;
}
@Override
public String toString() {
return "[afficheid=" + afficheid + ";affichetitle=" + affichetitle + "]";
}
}