Java数据库通用查询及封装

Java数据库通用查询及封装
时常在网上看到，有关Java数据通用查询及封装的技术文章，前些日我在看书时突然想起以前怎么写jdbc查询的，好像当时没理顺，昨天晚上花了点时间，复习了一下写了两种通用数据封装。

文章只是为了复习而写，新手可以看看吧,大牛飘过。。。。

先把一些辅助类贴上来:

DBHelp.java获得连接及关闭资源
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBHelp {
static {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private String connStr = "jdbc:sqlserver://localhost:1433;DatabaseName=student";
private String userName = "sa";
private String userPass = "root";
private Connection connection = null;
public Connection getConnection() {
try {
connection = DriverManager.getConnection(connStr, userName,
userPass);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}

public void closeResource(Object obj) {
try {
if (Connection.class.isInstance(obj)) {
((Connection) obj).close();
}
if (PreparedStatement.class.isInstance(obj)) {
((PreparedStatement) obj).close();
}

if (ResultSet.class.isInstance(obj)) {
((ResultSet) obj).close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}

public void closeResource(Connection connection,
PreparedStatement pStatement) {
try {
if (connection != null) {
connection.close();
}
if (pStatement != null) {
pStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}

public void closeResource(Connection connection,
PreparedStatement pStatement,ResultSet resultSet) {
try {
if (connection != null) {
connection.close();
}
if (pStatement != null) {
pStatement.close();
}
if(resultSet != null){
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}

}


1. 不借助实体类作为载体,封装数据。
Connections_DataProcesser.java处理sql语句

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;
public class Connections_DataProcesser {
private Connection connection = null;
private PreparedStatement pStatement = null;
private ResultSet resultSet = null;
private static DBHelp db=new DBHelp();
public List<List<Map<String, String>>> getObjectList(String sql,Object...obj){
List<List<Map<String, String>>> list=new ArrayList<List<Map<String, String>>>();
List<Map<String, String>> mList=null;
Map<String, String> map=null;
try {
connection=db.getConnection();
pStatement=connection.prepareStatement(sql);
pStatement = formatSql(pStatement, obj);
resultSet=pStatement.executeQuery();
ResultSetMetaData rsmd=resultSet.getMetaData();
String[] columnName=getColumnName(rsmd);
while(resultSet.next()){
mList=new ArrayList<Map<String,String>>();
for(int i=0;i<rsmd.getColumnCount();i++){
map=new HashMap<String, String>();
map.put(columnName[i], resultSet.getString(i+1));
mList.add(map);
}
list.add(mList);
}

} catch (SQLException e) {
e.printStackTrace();
}finally{
db.closeResource(connection, pStatement, resultSet);
}
return list;
}

private String[] getColumnName(ResultSetMetaData rsmd){
String[] columnName =null;
try {
columnName = new String[rsmd.getColumnCount()];
for(int i=0;i<rsmd.getColumnCount();i++){
columnName[i]=rsmd.getColumnName(i+1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return columnName;
}

private PreparedStatement formatSql(PreparedStatement pStatement ,Object...obj){
try {
if(obj.length !=0){
for(int i=1;i<=obj.length;i++){
pStatement.setObject(1, obj[i]);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return pStatement;
}
}

一个示范查询，这种封装查询要求对java的集合类比较熟悉
Test.java
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;
public class Test {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
String sql="select * from students";
Date d1=new Date();
Connections_DataProcesser dp=new Connections_DataProcesser();
List<List<Map<String, String>>> list=dp.getObjectList(sql);
for(int i=0;i<list.size();i++){
List<Map<String, String>> l=list.get(i);
for(int k=0;k<l.size();k++){
Set<Map.Entry<String, String>> mapset=l.get(k).entrySet()
Map.Entry<String, String> m= (Entry<String, String>) mapset.toArray()[0];
//System.out.print(m.getKey()+"="+m.getValue()+" ");
System.out.print(m.getValue()+" ");
}
System.out.println();
}
Date d2=new Date();
System.out.println(d2.getTime()-d1.getTime());
}
}


2. 通用查询

import java.lang.reflect.Field;
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.List;

public class CommonEntity_DataProcesser {
private Connection connection = null;
private PreparedStatement pStatement = null;
private ResultSet resultSet = null;
private static DBHelp db = new DBHelp();
@SuppressWarnings("unchecked")
public <T extends Object> List<T> getEntityList(T entity, String sql,
Object... obj) {
List<T> list = new ArrayList<T>();
try {
connection = db.getConnection();
pStatement = connection.prepareStatement(sql);
pStatement = formatSql(pStatement, obj);
resultSet = pStatement.executeQuery();
ResultSetMetaData rsmd = resultSet.getMetaData();
String[] columnsName = getColumnName(rsmd);
Class clazz = entity.getClass();
Object entityObject = null;
Field[] fields = clazz.getDeclaredFields();
while (resultSet.next()) {
entityObject = clazz.newInstance();
for (int i = 0; i < columnsName.length; i++) {
for (int k = 0; k < fields.length; k++) {
if (fields[k].getName().equals(columnsName[i])) {
fields[k].setAccessible(true);
fields[k].set(entityObject, resultSet.getString(columnsName[i]));
}
}
}
list.add((T)entityObject);
}

} catch (SQLException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}finally{
db.closeResource(connection, pStatement, resultSet);
}
return list;
}

private String[] getColumnName(ResultSetMetaData rsmd) {

String[] columnName = null;
try {
columnName = new String[rsmd.getColumnCount()];
for (int i = 0; i < rsmd.getColumnCount(); i++) {
columnName[i] = rsmd.getColumnName(i + 1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return columnName;
}

private PreparedStatement formatSql(PreparedStatement pStatement,
Object... obj) {
try {
if (obj.length != 0) {
for (int i = 1; i <= obj.length; i++) {
pStatement.setObject(1, obj[i]);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return pStatement;
}
}


Test1.java

import java.util.Date;
import java.util.List;
public class Test1 {
public static void main(String[] args) {
String sql="select * from students";
Date d3 = new Date();
CommonEntity_DataProcesser cedp = new CommonEntity_DataProcesser();
List<Students> list1 = cedp.getEntityList(new Students(), sql);
for (Students s : list1) {
System.out.println(s.getId() + " " + s.getUserName() + " "
+ s.getUserPass() + " " + s.getUserSex());
}
Date d4 = new Date();
System.out.println(d4.getTime() - d3.getTime());
}
}

实体类students.Java

public class Students {
private String id;
private String userName;
private String userPass;
private String userSex;
getter….setter…();…
}


两种查询的性能差不多，能做到通用只不过是利用的泛型和集合的一些特性。。。文章只是为了复习来用，新手也可以看看吧,大牛飘过。。。。