简单的说就是查询一条语句返回多个对象(学生,老师…) Dao.java
package Les_12_使用Dao模式的查询方法;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
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;
import org.apache.commons.beanutils.BeanUtils;
import Les_10_Dao设计模式.JDBC_tools;
public class Dao {
// 查询多条记录,返回对应对象的集合
public <T> List<T> getForList(Class<T> clazz, String sql, Object... args) {
List<T> list = new ArrayList<T>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 1.得到结果集。
connection = JDBC_tools.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
// 2.处理结果集,得到map对应的List
// 其中一个map对象就是一条记录,Map的key位result 的列名,Map的values 为列的值
List<Map<String, Object>> val = handleResulSetMapList(resultSet);
// 3。把Map 的list的List转为clazz对应的集合。
// 其中map对应的key就是clazz对应的propertyname
// 而mapde values 即为clazz对应的对象的propertyname的值
list = transfterMapListToBeanList(clazz, val);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBC_tools.close(connection, preparedStatement, resultSet);
}
return list;
}
/**
* 声成对象的方法
* @param clazz
* @param val
* @throws InstantiationException
* @throws IllegalAccessException
* @throws InvocationTargetException
*/
private <T> List<T> transfterMapListToBeanList(Class<T> clazz, List<Map<String, Object>> val)
throws InstantiationException, IllegalAccessException, InvocationTargetException {
List<T> result = new ArrayList<T>();
T bean = null;
if (val.size() > 0) {
for (Map<String, Object> m : val) {
bean = clazz.newInstance();
for (Map.Entry<String, Object> entry : m.entrySet()) {
String propertyName = entry.getKey();
Object values = entry.getValue();
BeanUtils.setProperty(bean, propertyName, values);
}
result.add(bean);
}
}
return result;
}
/**
* 处理结果集,得到Map的一个List,其中一个Map对象对应一条记录。
* @param resultSet
* @return
* @throws Exception
* @throws SQLException
*/
private List<Map<String, Object>> handleResulSetMapList(ResultSet resultSet) throws Exception, SQLException {
// 5.准备一个List<Map<String,Object>>键:存放列的名 值:存放列的值 其中一个Map 对象对应这一条记录
List<Map<String, Object>> val = new ArrayList<Map<String, Object>>();
List<String> columnLabels = getColumnLabels(resultSet);
Map<String, Object> map = null;
while (resultSet.next()) {
map = new HashMap<String, Object>();
for (String columLabel : columnLabels) {
Object value = resultSet.getObject(columLabel);
map.put(columLabel, value);
}
val.add(map);
}
return val;
}
/**
*
* @param resultSet
* @return
* @throws Exception
* 获取对应的 结果集 ColumnLabel 对应的List
*/
private List<String> getColumnLabels(ResultSet resultSet) throws Exception {
List<String> labels = new ArrayList<String>();
ResultSetMetaData rsmd = resultSet.getMetaData();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
labels.add(rsmd.getColumnLabel(i + 1));
}
return labels;
}
}
Student.java
package Les_12_使用Dao模式的查询方法;
public class Student {
private int id;
private String name="wangzijian 111";
private String grade;
public Student() {
super();
}
public Student(int id, String name, String grade) {
super();
this.id = id;
this.name = name;
this.grade = grade;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = grade;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", grade=" + grade + "]";
}
}
demo_test.java
package Les_12_使用Dao模式的查询方法;
import static org.junit.Assert.*;
import java.util.List;
import org.junit.Test;
public class demo_test {
Dao dao = new Dao();
@Test
public void testGetForList() {
String sql = "select s_id id,s_grade grade,s_name name from student";
List<Student> students = dao.getForList(Student.class, sql);
System.out.println("学生:" + students);
}
}
JDBC_tools.java
package Les_10_Dao设计模式;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBC_tools {
// 获取连接
public static Connection getConnection() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false";
String user = "root";
String password = "root";
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
// 关闭连接 和PreparedStatement(任何时候都不要使用 Statement!!!)
public static void close(Connection connection, PreparedStatement preparedStatement) {
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 关闭连接,权限, Resulset
public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 执行sql语句,可以执行任何添加和修改的操作
*
* @param sql:insert
* ,update ,delete 不包含select
* @param args:填写sql占位符的可变参数
*/
public static void update2(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBC_tools.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
preparedStatement.executeUpdate();
System.out.println("更新成功!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBC_tools.close(connection, preparedStatement, null);
}
}
/**
* 执行sql的方法
*
* @param sql:insert
* ,update ,delete 不包含select
* @throws SQLException
*/
public static void update(String sql) throws SQLException {
Connection connection = getConnection();
// 2.准备插入的SQL语句
// 3执行插入操作 (1.获取操作sql语句的Statement 对象 2.
Statement statement = connection.createStatement();
try {
// 调用Statement对象的executeUpdate(sql)执行SQL语句执行插入)
statement.execute(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
/*
* 关闭的顺序是:先关闭后获取的,先关闭Statement 后关闭Connection 关闭的顺序可能是
* 先关闭后获取的,先关闭Statement 后关闭Connection
*/
if (statement != null) {
// 4.关闭Statement对象
statement.close();
}
} catch (Exception e2) {
// 关闭connection对象
connection.close();
}
}
}
}