import java.io.BufferedReader; import java.io.UnsupportedEncodingException; import java.io.File; import java.io.FileReader; import java.io.FileWriter; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Hashtable; import java.util.Iterator; import java.util.List; import java.util.Vector; public class DBSqlYY { private static Connection con = null; private static Statement st = null; private static ResultSet rs = null; /* * 微软的数据库JDBC连接 */ private static String conURL = "jdbc:sqlserver://localhost:1433;databaseName=AWS";// 数据库的地址连接 gajah 的数据库连接 private static String cname = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // private static String dbA = "sa"; private static String dbpassword = "tiger"; public Connection open() { Connection conn = null; try { Class.forName(cname); } catch (Exception ex) { ex.printStackTrace(); } try { conn = DriverManager.getConnection(conURL, dbA, dbpassword); } catch (SQLException e) { e.printStackTrace(); } return conn; } /* * 进行调用的数据库连接 */ private static void dbconn() { try { Class.forName(cname); } catch (ClassNotFoundException e1) { e1.printStackTrace(); } try { con = DriverManager.getConnection(conURL, dbA, dbpassword); st = con.createStatement(); } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } } /* * 数据库的连接关闭 */ private static void dbclose() { try { st.close(); con.close(); } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } st = null; con = null; } /* * insert 语句执行快 */ public static int executeUpdater(String sql) { int result = -99; dbconn(); try { result = st.executeUpdate(sql); } catch (SQLException e) { // TODO 自动生成 catch 块 System.out.println("执行DBSqlYY类的public static List<List> GetLIst(String "+sql+")的方法出现错误"); } finally { dbclose(); } return result; } public static Hashtable executeQueryToH(String sql) { Vector DBresult = executeQueryToV(sql); if (DBresult != null && DBresult.size() > 0) { return (Hashtable) DBresult.get(0); } return new Hashtable(); } public ResultSet executeQuery(Connection conn, Statement stmt, String sql) { ResultSet result = null; try { stmt = conn.createStatement(); result = stmt.executeQuery(sql); } catch (SQLException e) { e.printStackTrace(); } return result; } public static Connection getConnecton(){ Connection conn = null; try { Class.forName(cname); conn = DriverManager.getConnection(conURL, dbA, dbpassword); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; } /* * 关闭conn ,rs ,st 三个方法的 */ public static void closeAll(Connection conn, ResultSet rs, Statement st){ try { if ( conn != null ) { conn.close(); } if ( rs != null ) { rs.close(); } if ( st != null ) { st.close(); } } catch ( Exception e ) { e.printStackTrace(); } } /* * 关闭四个的conn ,rs ,st, pst */ public static void closeAll(Connection conn, ResultSet rs, Statement st, PreparedStatement pst){ try { if ( conn != null ) { conn.close(); } if ( rs != null ) { rs.close(); } if ( st != null ) { st.close(); } if ( pst != null) { pst.close(); } } catch ( Exception e ) { e.printStackTrace(); } } public static int insertExecuste(String Sql){ Connection conn = DBSqlYY.getConnecton(); Statement st = null; PreparedStatement pst = null; ResultSet rs = null; int charm=0; try { pst = conn.prepareStatement(Sql); pst.executeUpdate(); charm=99; } catch (SQLException e) { System.out.println("执行数据库失败！执行的语句是:"+Sql); charm=-99; } return charm; } public static String getString(String sql, String filed) { Hashtable RESULT = executeQueryToH(sql); return (String) RESULT.get(filed.toUpperCase()); } public static String getToString(String sql,String filed) { DBSqlYY U8DBSqlYY = new DBSqlYY(); Connection conn = U8DBSqlYY.open(); Statement stmt = null; ResultSet rs = null; int BINDID = 0; try { rs = U8DBSqlYY.executeQuery(conn, stmt, sql); while(rs.next()) { filed=rs.getString(filed); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return filed; } public static int getInt(String sql){ DBSqlYY U8DBSqlYY = new DBSqlYY(); Connection conn = U8DBSqlYY.open(); Statement stmt = null; ResultSet rs = null; int BINDID = 0; try { rs = U8DBSqlYY.executeQuery(conn, stmt, sql); while(rs.next()) { BINDID=Integer.parseInt(rs.getString("BINDID")); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return BINDID; } public static int getInt(String sql, String filed) { Hashtable RESULT = executeQueryToH(sql); return Integer.parseInt(RESULT.get(filed.toUpperCase()).toString()); } public static Vector executeQueryToV(String sql) { Vector DBresult = null; ResultSet result = null; DBSqlYY U8DBSqlYY = new DBSqlYY(); Connection conn = U8DBSqlYY.open(); Statement stmt = null; ResultSet rs = null; try { rs = U8DBSqlYY.executeQuery(conn, stmt, sql); DBresult = ResultSetToList(rs); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return DBresult; } private static Vector ResultSetToList(ResultSet rs) throws Exception { ResultSetMetaData md = rs.getMetaData(); int columnCount = md.getColumnCount(); Vector list = new Vector(); Hashtable rowData; while (rs.next()) { rowData = new Hashtable(columnCount); for (int i = 1; i <= columnCount; i++) { Object v = rs.getObject(i); rowData.put(md.getColumnName(i).toUpperCase(), rs.getString(i) == null ? "" : rs.getString(i)); } list.add(rowData); } return list; } // 执行删除 public static String executeDelete(String sql) { try { st = con.createStatement(); st.executeUpdate(sql); } catch (Exception ex) { ex.printStackTrace(); } finally { dbclose(); } return "执行成功"; } public static List<String> QueryListForString(String sql) { List<String> listTableName = new ArrayList<String>(); try { dbconn(); ResultSet rs = st.executeQuery(sql); while (rs.next()) { listTableName.add(rs.getString(1)); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { dbclose(); } return listTableName; } /* * 直接传表明可以得到表里面的数据 */ public static List<List> GetLIst(String sql,int ert){ Connection conn = getConnecton(); Statement st = null; PreparedStatement prs=null; ResultSet rs = null; int it=0; List totalList = new ArrayList(); try { st = conn.createStatement(); rs = st.executeQuery(sql); while(rs.next()){ List oneElementList = new ArrayList(); for(int i=1; i<=ert;i++){ oneElementList.add(rs.getString(i)); } totalList.add(oneElementList); } }catch (Exception et){ System.out.println("执行DBSqlYY类的public static List<List> GetLIst(String "+sql+",String "+ert+")的方法出现错误"); System.out.println("出现的错误是：rs = st.executeQuery(sql);执行失败/nSql语句是："+sql+"???执行失败!"); et.printStackTrace(); } finally { closeAll(conn, rs, st); } return totalList; } public static Hashtable getHastable(String table,int BINDID){ Hashtable<String, String> add=new Hashtable(); String sql="select * from "+table+" where BINDID="+BINDID; Vector b=DBSqlYY.executeQueryToV(sql); for(int i=0;i<b.size();i++){ Hashtable tableS=(Hashtable) b.elementAt(0); add=tableS; } return add; } public static Hashtable getHastable(String table,String BINDID){ Hashtable<String, String> add=new Hashtable(); String sql="select * from "+table+ " "+BINDID; Vector b=DBSqlYY.executeQueryToV(sql); for(int i=0;i<b.size();i++){ Hashtable tableS=(Hashtable) b.elementAt(0); add=tableS; } return add; } public static Hashtable getHastable2(String table,int BINDID){ Hashtable<String, String> add=new Hashtable(); String sql="select * from "+table+" where BINDID="+BINDID; //String sqltable="select Y_Name from Y_SystemTable where Y_TABLE='"+BINDID+"'"; String sqltable="select name from syscolumns where id in (SELECT top 1 id FROM SysObjects Where XType='U'and name='"+table+"')"; List<List> tablelist=DBSqlYY.GetLIst(sqltable, 1); System.out.println(tablelist.size()); List<List> list = DBSqlYY.GetLIst(sql, tablelist.size()); int i=0; for(List a:list){ for(List b:tablelist){ add.put((String) b.get(0), String.valueOf((String) a.get(i))); i++; } } return add; } //数据库的更新通过HashTable来更新数据库的表。 public static int SetHastable(String table,Hashtable gt,int ID){ Hashtable<String, String> add=new Hashtable(); List<List> list=DBSqlYY.GetLIst("select name,xtype from syscolumns where id in (SELECT top 1 id FROM SysObjects Where XType='U'and name='"+table+"')", 2); String sql="select * from "+table+" where ID="+ID; dbconn(); try { st = con.createStatement(); st =con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); ResultSet rs=st.executeQuery(sql); while(rs.next()){ for(List l:list){ for (Iterator it2 = gt.keySet().iterator(); it2.hasNext();) { String key = (String) it2.next(); if(key.equals(String.valueOf((String) l.get(0)))){ rs.updateObject(key, gt.get(key)); // System.out.println(key+":"+(String)l.get(0)); } } } rs.updateRow(); } st.close(); rs.close(); } catch (SQLException e) { e.printStackTrace(); return -99; } return 1; } //数据库的更新根据条件进行update public static int SetHastable(String table,Hashtable gt,String ID){ Hashtable<String, String> add=new Hashtable(); List<List> list=DBSqlYY.GetLIst("select name,xtype from syscolumns where id in (SELECT top 1 id FROM SysObjects Where XType='U'and name='"+table+"')", 2); String sql="select * from "+table+" "+ID; dbconn(); try { st = con.createStatement(); st =con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); ResultSet rs=st.executeQuery(sql); while(rs.next()){ for(List l:list){ for (Iterator it2 = gt.keySet().iterator(); it2.hasNext();) { String key = (String) it2.next(); if(key.equals(String.valueOf((String) l.get(0)))){ rs.updateObject(key, gt.get(key)); // System.out.println(key+":"+(String)l.get(0)); } } } rs.updateRow(); } st.close(); rs.close(); } catch (SQLException e) { e.printStackTrace(); return -99; } return 1; } public static int modifyPrices(String percentage) throws SQLException { String dbName="YY_LSB_CUST"; Statement stmt = null; dbconn(); try { stmt = con.createStatement(); stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet uprs = stmt.executeQuery( "SELECT * FROM " + dbName +" where CUSTID='Altech'" ); while (uprs.next()) { uprs.updateObject("CUSTID", percentage); uprs.updateRow(); } } catch (SQLException e ) { e.printStackTrace(); } finally { if (stmt != null) { stmt.close(); } } return 1; } //根据表明。将hashtable里面的值insert到表里面去 public static int SetCreateHastable(String table,Hashtable gt){ StringBuffer sql=new StringBuffer(); StringBuffer sqlvalue=new StringBuffer(); List<List> list=DBSqlYY.GetLIst("select name,xtype from syscolumns where id in (SELECT top 1 id FROM SysObjects Where XType='U'and name='"+table+"')", 2); int filedIndex = 0; sql.append("insert into ").append(table).append("("); sqlvalue.append("values("); for(List a:list){ for (Iterator it2 = gt.keySet().iterator(); it2.hasNext();) { String key = (String) it2.next(); if(key.equals(String.valueOf((String) a.get(0)))){ //System.out.println(key+":"+(String)a.get(0)); sql.append((String) a.get(0)).append(","); sqlvalue.append(insertget(key, a.get(1), gt.get(key))).append(","); } } } sql.append("X@X-)"); sqlvalue.append("X@X-)"); sql.append(sqlvalue); StringBuffer sql_= new StringBuffer(); sql_.append(sql.toString().replace(",X@X-", "")); //System.out.println("SQL=["+sql_+"]"); int i=DBSqlYY.executeUpdater(sql_.toString()); if(i>0) { return i; } else { return -99; } } //-------------------------------------------自动编辑代码------------------------- public static String updateget(String fieldName,Object fieldtype,Object fieldValue){ StringBuffer sql=new StringBuffer(); if("61".equals(String.valueOf(fieldtype))){ sql.append(" ").append(fieldName).append("=").append(fieldValue).append(" "); }else if("108".equals(String.valueOf(fieldtype))){ sql.append(" ").append(fieldName).append("=").append(fieldValue).append(" "); }else{ sql.append(" ").append(fieldName).append("='").append(fieldValue).append("' "); } return sql.toString(); } public static String insertget(String fieldName,Object fieldtype,Object fieldValue){ StringBuffer sql=new StringBuffer(); if("61".equals(String.valueOf(fieldtype))){ sql.append(" '").append(fieldValue).append("' "); }else if("108".equals(String.valueOf(fieldtype))){ sql.append(" ").append(fieldValue).append(" "); }else if("108".equals(String.valueOf(fieldtype))){ sql.append(" ").append(fieldValue).append(" "); }else{ sql.append(" '").append(fieldValue).append("' "); } return sql.toString(); } }
