package com.northglass.util;
|
|
import java.sql.CallableStatement;
|
import java.sql.Connection;
|
import java.sql.DriverManager;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
|
|
|
public class DBHelper{
|
|
private String sqlurl="jdbc:mariadb://localhost/gmms";
|
private String user="root";
|
private String password=null;
|
|
|
public String InvokeSqlProc(String name,String param1,String param2){
|
Connection con=null;
|
try{
|
con=DriverManager.getConnection(sqlurl,user,password);
|
CallableStatement c=con.prepareCall(String.format("{call %s(?,?,?)}",name));
|
c.setString(1,param1);
|
c.setString(2,param2);
|
|
c.registerOutParameter(3, java.sql.Types.VARCHAR);
|
System.out.println("ok");
|
c.execute();
|
System.out.println("ok1");
|
String message=c.getString(3);
|
c.close();
|
con.close();
|
return message;
|
}
|
catch(Exception e){
|
e.printStackTrace();
|
if(con!=null)
|
try {
|
con.close();
|
} catch (SQLException e1) {
|
// TODO Auto-generated catch block
|
e1.printStackTrace();
|
}
|
return "fail";
|
}
|
|
}
|
|
|
|
|
|
//从数据库表中挑出一个字段作为前台select 的选项
|
public String MakeSelectOptionBySql(String sql,String field){
|
QueryResult query=null;
|
try{
|
query=new QueryResult(sqlurl,user,password,sql);
|
ResultSet result=query.Result;
|
StringBuilder sb=new StringBuilder();
|
while(result.next()){
|
Object o=result.getObject(field);
|
if(o!=null)
|
sb.append(String.format("<option value=\"%s\">%s</option>\r\n",o.toString(),o.toString()));
|
}
|
query.Close();
|
return sb.toString();
|
}
|
catch(Exception e){
|
if(query!=null)
|
query.Close();
|
return null;
|
}
|
}
|
|
|
public QueryResult getQueryResult(String sql){
|
QueryResult query=null;
|
try{
|
query=new QueryResult(sqlurl,user,password,sql);
|
return query;
|
}
|
catch(Exception e){
|
if(query!=null)
|
query.Close();
|
e.printStackTrace();
|
return null;
|
}
|
}
|
|
|
//从数据库表生成一个前台的表格tbody的行内容，PKName是主键字段，ShowField是要显示的列 其中#开头的字符串直接作为t内容填入
|
public String MakeHTMLTableRowsBySql(String sql,String PKName,String[] ShowField) throws SQLException{
|
QueryResult query=null;
|
try{
|
query=new QueryResult(sqlurl,user,password,sql);
|
ResultSet result=query.Result;
|
StringBuilder sb=new StringBuilder();
|
int[] fieldIndex=new int[ShowField.length];
|
for(int i=0;i<ShowField.length;i++){
|
if(ShowField[i].charAt(0)=='#'){
|
fieldIndex[i]=-100;
|
ShowField[i]=ShowField[i].substring(1);
|
}
|
else
|
fieldIndex[i]=result.findColumn(ShowField[i]);
|
}
|
//sb.append("<tbody>\r\n");
|
while(result.next()){
|
if(PKName!=null){
|
sb.append(String.format("<tr data-id=\"%s\">\r\n",result.getObject(PKName).toString().trim()));
|
}
|
else
|
sb.append("<tr>\r\n");
|
for(int i=0;i<fieldIndex.length;i++){
|
sb.append("<td>");
|
int idx=fieldIndex[i];
|
if(idx==-100){
|
sb.append(ShowField[i]);
|
}
|
else{
|
|
Object o=result.getObject(idx);
|
if(o!=null)
|
sb.append(o);
|
}
|
sb.append("</td>");
|
}
|
sb.append("</tr>\r\n");
|
}
|
//sb.append("</tbody>\r\n");
|
query.Close();
|
return sb.toString();
|
}
|
catch(Exception e){
|
if(query!=null)
|
query.Close();
|
e.printStackTrace();
|
return null;
|
}
|
}
|
|
public DBHelper(String sqlurl,String user,String password){
|
this.sqlurl=sqlurl;
|
this.user=user;
|
this.password=password;
|
}
|
|
|
public class QueryResult{
|
private java.sql.Connection con;
|
private java.sql.Statement statement;
|
public java.sql.ResultSet Result;
|
|
public QueryResult(String conString,String user,String password,String Sql) throws SQLException{
|
this.con= java.sql.DriverManager.getConnection(conString,user,password);
|
this.statement=con.createStatement();
|
this.Result=this.statement.executeQuery(Sql);
|
}
|
|
|
|
|
|
public void Close(){
|
try{
|
if(Result!=null){
|
Result.close();
|
Result=null;
|
}
|
}catch(Exception e){
|
|
}
|
try{
|
if(statement!=null){
|
statement.close();
|
statement=null;
|
}
|
}catch(Exception e){
|
|
}
|
try{
|
if(con!=null){
|
con.close();
|
con=null;
|
}
|
}catch(Exception e){
|
|
}
|
}
|
}
|
|
|
}
|
