JDBC的工具类
2018-07-20 来源：open-open
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();
}
}
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点！
本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。
上一篇:java快速排序算法
下一篇:java生成汉字验证码
最新资讯
热门推荐