当前位置 : 主页 > 编程语言 > c++ >

数据库查询语句封装

来源:互联网 收集:自由互联 发布时间:2021-07-03
数据库查询封装 package org.jxnd.tools;import java.lang.annotation.Annotation;import java.lang.reflect.AnnotatedElement;import java.lang.reflect.Constructor;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetExcep
数据库查询封装
package org.jxnd.tools;

import java.lang.annotation.Annotation;
import java.lang.reflect.AnnotatedElement;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * @author 今晚打老虎
 * @see	一个关于数据库访问的简单封装
* @see 当前版本需要注意以下几点: * @see 1、需要数据库的表名和列名与实体类的类名和字段名保持一直 * @see 2、多表联查暂时没有进行封装,下个版本迭代升级 * @see 3、级联删除、添加、修改暂时没有实现 * @see 4、根据任意SQL语句生成实体类 * @see 5、暂时无任何数据和SQL语句缓存 * */ @SuppressWarnings("all") public class DBTools { //临时数据表(实体类) public class DataTable{ List columns=new ArrayList (); List > rows=new ArrayList >(); } //select s.id users_id,users_name,users_pwd,roles_id,roles_name,users_roleid from users s,roles r where s.roleid=r.id private static Connection conn; //当前连接对象 private static final String name="C##admin"; //用户名 private static final String pwd="123456"; //密码 private static final String envName="aaa"; //JNDI名,tomcat配置的连接池名称 private static final boolean isPool=false; //是否使用连接池(如果需要使用连接池,请在web环境测试) private static Context ctx=null; //创建容器对象 private static DataSource ds=null; //容器中取出Datasource数据源 private static ThreadLocal tl; //线程资源绑定 /**初始化资源*/ static{ try { if (isPool) { ctx = new InitialContext(); ds=(DataSource)ctx.lookup("java:comp/env/"+envName); }else Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (NamingException e) { e.printStackTrace(); } } /**获取连接方法(如果被多线程并发访问,此处的连接会不安全,请使用ThreadLocal对象)*/ private static Connection getConnection(){ try { if (isPool) conn=ds.getConnection(); else conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl",name,pwd); } catch (SQLException e) { e.printStackTrace(); } return conn; } /**插入对象*/ public static int insertOjbect(T t){ int i=0; try { //列名集合 List columns=new ArrayList (); //列的值集合 List values=new ArrayList(); Class c=t.getClass(); //获取所有列名 for (Field me: c.getDeclaredFields()) { columns.add(me.getName()); } //生成插入的SQL String sql=createInsertSql(c,columns); //绑定列名的值 for (String string : columns) { for (Method mt : c.getMethods()) { if (mt.getName().equalsIgnoreCase("get"+string)) { values.add(mt.invoke(t, null)); } } } //执行 i=executeUpdate(sql, values); } catch (Exception e) { e.printStackTrace(); }finally{ close(); } return i; } /**通过主键ID获取对象*/ public static T getObject(Class c,int id){ T t=null; ResultSet rs=null; try { //获取所有的列名 List columns=new ArrayList (); //生成列名 addColumnByClass(c,columns); //生成SQL语句 String sql=getSelectSql(columns,c); rs=getReusultSet(sql, id); if(rs.next()) t= SetInfo(c, columns, rs); return t; } catch (SQLException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); }finally{ close(); } return t; } /**通过SQL语句和参数,查询整个集合*/ public static List getListBySql(Class c,String sql,List plist){ List list=new ArrayList (); ResultSet rs=null; try { //列名集合 List columns=new ArrayList (); //赋值列名集合 //生成列名 addColumnByClass(c,columns); rs=getReusultSet(sql, plist); while (rs.next()) { list.add(SetInfo(c, columns, rs)); } } catch (SQLException e) { e.printStackTrace(); }finally{ //此处的结果集对象也应该放在close关闭 try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } close(); } return list; } /**通过SQL语句和参数,查询整个集合*/ public static T getObject(Class c,String sql,List plist){ T t=null; ResultSet rs=null; try { //列名集合 List columns=new ArrayList (); //赋值列名集合 //生成列名 addColumnByClass(c,columns); rs=getReusultSet(sql, plist); if (rs.next()) { t=SetInfo(c, columns, rs); } } catch (SQLException e) { e.printStackTrace(); }finally{ //此处的结果集对象也应该放在close关闭 try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } close(); } return t; } /**删除对象*/ public static int deleteObject(Class c,int id){ int i=0; StringBuffer sb=new StringBuffer(); sb.append("delete from "); sb.append(c.getSimpleName()); sb.append(" where id=?"); i=executeUpdate(sb.toString(), id); return i; } /**修改对象*/ public static int updateObject(T t){ int i=0; Class c=t.getClass(); List columns=new ArrayList (); List values=new ArrayList(); StringBuffer sb=new StringBuffer(); for (Field fi : c.getDeclaredFields()) { if (isBasicType(fi.getType())) columns.add(fi.getName()); } sb.append("update "); sb.append(c.getSimpleName()); sb.append(" set "); for (String string : columns) { sb.append(string); sb.append("=?,"); } sb.delete(sb.length()-1, sb.length()); sb.append("where id=?"); for (String s:columns) { for (Method mt: c.getMethods()) { if (mt.getName().equalsIgnoreCase("get"+s)) { try { System.out.println(mt.getName()); values.add(mt.invoke(t, null)); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } } } } try { //最后添加ID values.add(c.getMethod("getId", null).invoke(t, null)); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { } catch (InvocationTargetException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (SecurityException e) { e.printStackTrace(); } i=executeUpdate(sb.toString(), values); return i; } /**结果集赋值给实体类*/ public static T SetInfo(Class c,List columns,ResultSet rs){ T t=null; try { t = c.newInstance(); for (Method th : c.getMethods()) { if (th.getName().indexOf("set")>=0) { Class pc=th.getParameterTypes()[0]; Object value=null; String column=c.getSimpleName()+"_"+th.getName().substring(th.getName().indexOf("t")+1); switch (pc.getSimpleName()) { case "int": case "Integer": value=rs.getInt(column); break; case "float": case "Float": value=rs.getFloat(column); break; case "double": case "Double": value=rs.getDouble(column); break; case "long": case "Long": value=rs.getLong(column); break; case "boolean": case "Boolean": value=rs.getBoolean(column); break; case "short": case "Short": value=rs.getShort(column); break; case "byte": case "Byte": value=rs.getByte(column); break; case "String": case "char": case "Character": value=rs.getString(column); break; default: //外键对象 List columnsTemp=new ArrayList (); addColumnByClass(pc,columnsTemp); value=SetInfo(pc, columnsTemp, rs); break; } th.invoke(t, value); } } } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return t; } /**根据SQL语句和参数获取Result对象*/ public static ResultSet getReusultSet(String sql,List list){ ResultSet rs=null; try { PreparedStatement ppst=getConnection().prepareStatement(sql); for (int i = 0; list!=null&&i list){ try { PreparedStatement ppst=getConnection().prepareStatement(sql); for (int i = 0; i < list.size(); i++) { ppst.setObject(i+1, list.get(i)); } return ppst.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ close(); } return 0; } /**根据SQL语句和一个参数(这个参数尽量是主键)来执行操作*/ public static int executeUpdate(String sql,int id){ try { PreparedStatement ppst=getConnection().prepareStatement(sql); ppst.setInt(1, id); return ppst.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ close(); } return 0; } /**根据SQL语句来执行操作*/ public static int executeUpdate(String sql){ try { PreparedStatement ppst=getConnection().prepareStatement(sql); return ppst.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ close(); } return 0; } /**插入的SQL语句生成器*/ public static String createInsertSql(Class c,List columns){ StringBuffer bf=new StringBuffer(); bf.append("insert into "); bf.append(c.getSimpleName()); bf.append(" ("); for (String string : columns) { bf.append(string); bf.append(","); } bf.delete(bf.length()-1, bf.length()); bf.append(") "); //insert into users id,name,pwd,roleid values bf.append("values("); for (String string : columns) { bf.append("?,"); } bf.delete(bf.length()-1, bf.length()); //动态生成值 // for (Method mt: c.getMethods()) { // if (mt.getName().equalsIgnoreCase("get"+string)) { // if (mt.getReturnType()==String.class){ // bf.append("'"); // bf.append(mt.invoke(t, null).toString()); // bf.append("'"); // }else // bf.append(mt.invoke(t, null).toString()); // bf.append(","); // } // } // } bf.append(")"); return bf.toString(); } /**查询语句生成器*/ public static String getSelectSql(List columns,Class c){ StringBuffer sb=new StringBuffer(); StringBuffer whereSB=new StringBuffer(); sb.append("select "); for (String string : columns) { sb.append(string.replace('_', '.')); sb.append(" as \""+string+"\","); } sb.delete(sb.length()-1, sb.length()); sb.append(" from "); //生成关联表名和where条件 createTablesAndWhere(c,sb,whereSB); //去掉尾巴 sb.delete(sb.length()-1, sb.length()); sb.append(" where "+c.getSimpleName()+".id=?"); whereSB.delete(whereSB.length()-3, whereSB.length()); sb.append(whereSB); //装载数据 return sb.toString(); } /**生成所有需要查询的表名和条件 sb:联查表名 temp:联查条件*/ public static void createTablesAndWhere(Class c,StringBuffer sb,StringBuffer whereSB){ sb.append(c.getSimpleName()+","); whereSB.append(" and"); for (Field fl: c.getDeclaredFields()) { if (!isBasicType(fl.getType())) { whereSB.append(" "+c.getSimpleName()+"."+fl.getType().getSimpleName()+"id"); whereSB.append("="+fl.getType().getSimpleName()+".id"); createTablesAndWhere(fl.getType(),sb,whereSB); } } } /**添加所有列*/ public static void addColumnByClass(Class c,List columns){ for (Field fl: c.getDeclaredFields()) { if (isBasicType(fl.getType())) columns.add(c.getSimpleName()+"_"+fl.getName()); else addColumnByClass(fl.getType(),columns); } } /**判断类型是否是 系统的数据类型*/ public static boolean isBasicType(Class type){ if (type==int.class||type==Integer.class) return true; if (type==char.class||type==Character.class) return true; if (type==float.class||type==Float.class) return true; if (type==double.class||type==Double.class) return true; if (type==boolean.class||type==Boolean.class) return true; if (type==short.class||type==Short.class) return true; if (type==byte.class||type==Byte.class) return true; if (type==long.class||type==Long.class) return true; if (type==String.class) return true; return false; } /**关闭所有资源(需要的资源请在此统一关闭)*/ public static void close() { if (conn!=null) { try { conn.close(); conn=null; } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) throws NoSuchMethodException, SecurityException { } }
网友评论