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. 当然是存在很多