package DataBase;
|
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.List;
|
import Method.About_tb_track;
|
import Method.BaoWenShow;
|
import PbuliClass.GetTxt;
|
import PbuliClass.ShowMessage;
|
import zhuce.ZhuCeMan;
|
@SuppressWarnings("rawtypes")
|
public class DatabaseManagement {//Êý¾Ý¿â¹ÜÀíÀà
|
|
protected static String adressandport =GetTxt.getTxt("databaseadress","system")+":"+GetTxt.getTxt("databaseport","system");
|
protected static String dbClassName = "com.mysql.jdbc.Driver";// MySQLÊý¾Ý¿âÇý¶¯ÀàµÄÃû³Æ
|
protected static String dbUrl = "jdbc:mysql://"+adressandport+"/hxzkflow";// ·ÃÎÊMySQLÊý¾Ý¿âµÄ·¾¶
|
protected static String dbUser;// ·ÃÎÊMySQLÊý¾Ý¿âµÄÓû§Ãû
|
protected static String dbPwd;// ·ÃÎÊMySQLÊý¾Ý¿âµÄÃÜÂë
|
protected static String dbName = "hxzkflow";// ·ÃÎÊMySQLÊý¾Ý¿âÖеÄʵÀýhxzk)
|
protected static String second = null;//
|
public static Connection conn = null;// MySQLÊý¾Ý¿âµÄÁ¬½Ó¶ÔÏó
|
public static PreparedStatement sql;//ÉêÃ÷Ô¤´¦Àí¶ÔÏó
|
|
|
static {// ¾²Ì¬³õʼ»¯Àà
|
try {
|
|
dbUser=GetTxt.getTxt("dbName","system");
|
dbPwd=GetTxt.getTxt("dbPass","system");
|
if (conn == null) {
|
Class.forName(dbClassName).newInstance();// ʵÀý»¯MySQLÊý¾Ý¿âµÄÇý¶¯
|
try {conn = DriverManager.getConnection(dbUrl+"?useUnicode=true&characterEncoding=utf-8&useSSL=false",dbUser,dbPwd);// Á¬½ÓMySQLÊý¾Ý¿â
|
}catch(SQLException e) {
|
ShowMessage.zidingyi("Êý¾Ý¿âÁ¬½Ó³ö´í,Çë¼ì²éÊý¾Ý¿âÃÜÂë");
|
}
|
|
}
|
} catch (ClassNotFoundException e) {
|
e.printStackTrace();
|
ShowMessage.zidingyi("Ç뽫MySQLµÄJDBCÇý¶¯°ü¸´ÖƵ½libÎļþ¼ÐÖС£");// ²¶»ñÒì³£ºó£¬µ¯³öÌáʾ¿ò
|
System.exit(-1);// ϵͳֹͣÔËÐÐ
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
|
|
|
/**»ñÊý¾Ý¿âÖÐij¸ö±íµÄËùÓеÄÊý¾Ý
|
* @param Êý¾Ý¿âÖÐij¸ö±íµÄÃû³Æ*/
|
|
public static List get_alldate_indatabase(String database_name) {
|
List list = findForList("select * from "+database_name);
|
return list;
|
|
}
|
|
/**ËÑË÷ij¸ö×Ö¶Î*/
|
public static List get_moudata_indatabase(String sql) {
|
List list = findForList(sql);
|
return list;
|
|
}
|
|
/**Êý¾Ý°´ÕÕÉýÐò»òÕß½µÐòÅÅÁÐ
|
* @param DESC½µÐòÅÅÁÐ
|
* @param ASCÉýÐòÅÅÁÐ
|
* @param tablenameÊý¾Ý±íµÄÃû³Æ*/
|
public static List table_AandD(String tablename,String sj) {
|
List list = findForList("select * from "+tablename+" order by id "+sj);
|
return list;
|
}
|
|
/**Êý¾Ý°´ÕÕÉýÐò»òÕß½µÐòÅÅÁÐ
|
* @param DESC½µÐòÅÅÁÐ
|
* @param ASCÉýÐòÅÅÁÐ
|
* @param tablenameÊý¾Ý±íµÄÃû³Æ*/
|
public static List table_base(String tablename,String sj,String ziduan) {
|
List list = findForList("select * from "+tablename+" order by "+ziduan+" "+sj);
|
return list;
|
}
|
|
|
/**ÐÅÏ¢²éѯ*/
|
public static List serch(String serch) {
|
List list = findForList(serch);
|
return list;
|
}
|
|
|
// Ö´ÐÐÖ¸¶¨²éѯ
|
public static ResultSet query(String QueryStr) {
|
ResultSet set = findForResultSet(QueryStr);
|
return set;
|
}
|
|
// Ö´ÐÐɾ³ý
|
public static int delete(String sql) {
|
BaoWenShow.show(sql, "Êý¾Ý¿âÊý","Ñ¡Ôñ±êÇ©");
|
return update(sql);
|
}
|
|
|
/**Ö´ÐÐSQL²éѯÓï¾ä·µ»Ø½á¹û¼¯*/
|
public static ResultSet findForResultSet(String sql) {
|
if (conn == null) {
|
ShowMessage.zidingyi("Êý¾ÝûÓÐÁ¬½Ó³É¹¦...");
|
return null;
|
}
|
ResultSet rs = null;
|
try {
|
|
/**TYPE_SCROLL_INSENSITIVE,½á¹û¼¯µÄÓαê¿ÉÒÔÉÏÏÂÒƶ¯£¬µ±Êý¾Ý¿â±ä»¯Ê±£¬µ±Ç°½á¹û¼¯²»±ä¡£
|
* CONCUR_READ_ONLY ²»ÄÜÓýá¹û¼¯¸üÐÂÊý¾Ý¿âÖÐµÄ±í¡£*/
|
Statement stmt= conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
|
rs = stmt.executeQuery(sql);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return rs;
|
}
|
|
|
// Ìí¼ÓÊý¾Ý
|
public static boolean insert(String sql) {
|
BaoWenShow.show(sql, "Êý¾Ý¿âÊý","Ñ¡Ôñ±êÇ©");
|
boolean result = false;
|
try {
|
Statement stmt = conn.createStatement();
|
result = stmt.execute(sql);
|
conn.commit(); // Ìá½»ÊÂÎñ
|
stmt.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return result;
|
}
|
|
|
/**¸üÐÂÊý¾Ý*/
|
public static int update(String sql) {
|
BaoWenShow.show(sql, "Êý¾Ý¿âÊý","Ñ¡Ôñ±êÇ©");
|
int result = 0;
|
try {
|
Statement stmt = conn.createStatement();
|
conn.setAutoCommit(false);
|
result = stmt.executeUpdate(sql);
|
conn.commit();
|
stmt.close();
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return result;
|
}
|
|
|
|
public static List findForList(String sql) {
|
List<List> list = new ArrayList<List>();
|
ResultSet rs = findForResultSet(sql);
|
try {
|
ResultSetMetaData metaData = rs.getMetaData();//´ÓÔªÊý¾ÝÖлñµÃÁÐÊý
|
int colCount = metaData.getColumnCount();//±í×ܵÄÁÐÊý
|
while (rs.next()) {
|
List<String> row = new ArrayList<String>();
|
for (int i = 1; i <= colCount; i++) {
|
String str = rs.getString(i);
|
if (str != null && !str.isEmpty())
|
str = str.trim();//ºöÂÔÇ°ÃæºÍºóÃæ¿Õ°×
|
row.add(str);
|
}
|
list.add(row);
|
}
|
} catch (Exception e) {
|
ShowMessage.zidingyi_24(sql+"Êý¾Ý¿â²Ù×÷³ö´í£¡");// ²¶»ñÒì³£ºó£¬µ¯³öÌáʾ¿ò
|
|
}
|
try {
|
rs.close();
|
} catch (SQLException e) {
|
// TODO ×Ô¶¯Éú³ÉµÄ catch ¿é
|
e.printStackTrace();
|
}
|
|
return list;
|
}
|
|
/**»ñÈ¡Êý¾Ý¿â·µ»ØµÄ½á¹û¼¯*/
|
public static String[] get_columname(String dataname) {
|
|
String[] columname=null;
|
ResultSet rs = findForResultSet("select * from "+dataname);
|
ResultSetMetaData data=null;;
|
try {
|
data = rs.getMetaData();
|
int colCount = data.getColumnCount();//±í×ܵÄÁÐÊý
|
columname=new String[colCount];
|
for (int i =1; i <= colCount; i++) {
|
// »ñµÃÖ¸¶¨ÁеÄÁÐÃû
|
String columnName = data.getColumnName(i);
|
columname[i-1]=columnName;
|
}
|
} catch (SQLException e) {
|
ShowMessage.zidingyi_24("get table column err...");
|
}//´ÓÔªÊý¾ÝÖлñµÃÁÐÊý
|
|
try {
|
rs.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return columname;
|
}
|
|
|
/**ʹÓÃÔ¤´¦ÀíÓï¾ä¿ìËÙÏòÊý¾Ý¿â²åÈëÊý¾Ý
|
* @param tbnameÊý¾Ý¿âÃû³Æ
|
* @param ziduan×Ö¶Î
|
* @param zhi×ֶζÔÓ¦µÄÖµ */
|
public static boolean insertfast(String tbname,String[] ziduan,String[] zhi) {
|
boolean succ=false;
|
int result=0;
|
PreparedStatement ptmt=null;
|
int lenth=ziduan.length;
|
|
StringBuffer sql=new StringBuffer("INSERT " +tbname+" ( ");
|
for(int i=0;i<lenth;i++) {
|
|
if(i==(lenth-1)) {
|
sql.append(ziduan[i]+") VALUES ( ");
|
}else {
|
sql.append(ziduan[i]+",");
|
}
|
}
|
|
for(int i=0;i<lenth;i++) {
|
|
if(i==(lenth-1)) {
|
sql.append("? )");
|
}else {
|
sql.append("?,");
|
}
|
}
|
|
BaoWenShow.show(sql.toString(), "Êý¾Ý¿âÊý","Ñ¡Ôñ±êÇ©");
|
try {
|
ptmt = conn.prepareStatement(sql.toString());
|
|
//¹Ø±Õ×Ô¶¯Ìá½»ÊÂÎñ
|
conn.setAutoCommit(false);
|
} catch (SQLException e1) {
|
e1.printStackTrace();
|
}
|
|
|
|
for(int i=0;i<lenth;i++) {
|
try {
|
ptmt.setString(i+1, zhi[i]);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
try {
|
result=ptmt. executeUpdate();
|
//×Ô¶¯Ìá½»ÊÂÎñ
|
conn.commit();
|
ptmt.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
if(result !=0) {
|
succ=true;
|
}
|
|
sql=new StringBuffer();
|
return succ;
|
}
|
|
|
/**¿ìËÙÐÞ¸ÄÊý¾Ý¿âÊý¾Ý*/
|
//String sql = "update sort set sname=? where sid=?";
|
public static boolean fast_alert_dbase(String tbname,String[] ziduan,String[] zhi) {
|
|
int result=0;
|
boolean succ=false;
|
PreparedStatement ptmt=null;
|
int lenth=ziduan.length;
|
|
StringBuffer sql=new StringBuffer("update " +tbname+" set ");
|
for(int i=0;i<lenth;i++) {
|
|
if(i==(lenth-1)) {
|
sql.append(" where "+ziduan[i]+"=? ");
|
}else if(i==(lenth-2)) {
|
sql.append(ziduan[i]+"=?");
|
}else {
|
sql.append(ziduan[i]+"=?, ");
|
}
|
}
|
if(ZhuCeMan.isGood()) {
|
BaoWenShow.show(sql.toString(), "Êý¾Ý¿âÊý","Ñ¡Ôñ±êÇ©");
|
}
|
try {
|
ptmt = conn.prepareStatement(sql.toString());
|
//¹Ø±Õ×Ô¶¯Ìá½»ÊÂÎñ
|
conn.setAutoCommit(false);
|
} catch (SQLException e1) {
|
e1.printStackTrace();
|
}
|
|
|
|
for(int i=0;i<lenth;i++) {
|
try {
|
ptmt.setString(i+1, zhi[i]);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
try {
|
result=ptmt.executeUpdate();
|
conn.commit(); // Ìá½»ÊÂÎñ
|
ptmt.close();
|
if(result !=0) {
|
succ=true;
|
}
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
sql=new StringBuffer();
|
return succ;
|
}
|
|
|
public static void insert_fast(String sqls,int datafrom) {
|
BaoWenShow.show(sqls, "Êý¾Ý¿âÊý","Ñ¡Ôñ±êÇ©");
|
PreparedStatement pst;
|
try {
|
conn.setAutoCommit(false);
|
pst = conn.prepareStatement("");
|
pst.addBatch(sqls); // Ìí¼ÓÖ´ÐÐsql
|
pst.executeBatch(); // Ö´ÐвÙ×÷
|
conn.commit(); // Ìá½»ÊÂÎñ
|
pst.close();
|
} catch (SQLException e) {
|
if(datafrom==1) {
|
About_tb_track.create_tb_track();
|
}
|
ShowMessage.zidingyi("Êý¾ÝдÈëÊý¾Ý¿â³ö´í"+sqls);
|
} // ÉèÖÃÊÂÎñΪ·Ç×Ô¶¯Ìá½»
|
}
|
}
|
