java数据库通用查询系统_Java数据库通用查询及封装

Java数据库通用查询及封装

[学习了数据库的基础教程之后，可以在图形化界面中增删改查，感觉流弊坏了。但是，我们的征途怎么可能止步于此？于是，就有了这篇博文。使用JDBC连接MS2008版本的数据库。

Java数据库通用查询及封装

时常在网上看到，有关Java数据通用查询及封装的技术文章，前些日我在看书时突然想起以前怎么写jdbc查询的，好像当时没理顺，[package com.sunjing.palm;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.sql.Connection;import java.sql.PreparedS昨天晚上花了点时间，复习了一下写了两种通用数据封装。

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

先把一些辅助类贴上来:

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>> getObjectList(String sql,Object...obj){

List>> list=new ArrayList>>();

List> mList=null;

Map 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>();

for(int i=0;i

map=new HashMap();

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

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=dp.getObjectList(sql);

for(int i=0;i

List> l=list.get(i);

for(int k=0;k

Set> mapset=l.get(k).entrySet()

Map.Entry m= (Entry) 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 List getEntityList(T entity, String sql,

Object... obj) {

List list = new ArrayList();

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 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…();…

}

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

[很实用的封装，适用于仅使用JDBC操作数据库的时候。查询数据库后直接封装javabean对象到集合中，免去了繁琐的迭代每条记录然后逐个取字段封装javaBean.   当然是存在很多