Java数据库通用查询及封装

最新推荐文章于 2024-06-30 02:05:30 发布

原创最新推荐文章于 2024-06-30 02:05:30 发布 · 5.4k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#java #数据库 #string #class #sql #date

JAVA 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一种使用 Java JDBC 进行通用数据库查询的方法，包括不借助实体类的查询封装和通过实体类进行的通用查询。这两种方法都提供了一种灵活且易于维护的方式来处理 SQL 查询结果。

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