【Java】访问mysql数据库视图

 

数据库连接Connect：

package cn.hkwl.zaxq.mysql; 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
  
public class Connect { 
      
    String DBDRIVER="com.mysql.jdbc.Driver"; 
    String DBURL="jdbc:mysql://10.10.1.251:3306/dss?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC&allowMultiQueries=true"; 
    String DBUSER="connect"; 
    String DBPWD="password"; 
    Connection conn=null; 
  
    public Connect(){ 
        try { 
            Class.forName(DBDRIVER); 
            conn=DriverManager.getConnection(DBURL,DBUSER,DBPWD); 
            //System.out.println("数据库连接成功"); 
        } catch (ClassNotFoundException e) { 
            // TODO: handle exception 
            e.printStackTrace(); 
        } catch (SQLException e) { 
            // TODO: handle exception 
            e.printStackTrace(); 
        } 
    } 
      
    public Connection getCon(){ 
        return conn; 
    } 
    
    public void closeCon(){ 
        try{ 
            conn.close(); 
        }catch(SQLException e){ 
            e.printStackTrace(); 
        } 
    } 
}

使用：

@RequestMapping("/getCHNStatus")
    public @ResponseBody void getCHNStatus(){
         Connect connect=new Connect(); 
         Connection connection=connect.getCon(); 
         PreparedStatement ps=null;
         ResultSet rs=null;
         ResultSetMetaData m=null;//获取 列信息
         try{
             String sql="select DEVICE_CODE,CHANNEL_NAME,OPEN_STATUS,CHANNEL_CODE from v_chn_status order by OPEN_STATUS";
             ps=connection.prepareStatement(sql); 
             rs=ps.executeQuery();
             m=rs.getMetaData();
             int columns=m.getColumnCount();
             JSONArray ja=new JSONArray();         
             while(rs.next()){
                 JSONObject jt=new JSONObject();
                 for(int i=1;i<=columns;i++)
                 {
                     switch(m.getColumnName(i)){
                         case "OPEN_STATUS":
                             switch(rs.getString(i)){
                                 case "1":jt.put(m.getColumnName(i), "开门");break;
                                 case "2":jt.put(m.getColumnName(i), "关门");break;
                                 case "3":jt.put(m.getColumnName(i), "常开");break;
                                 case "4":jt.put(m.getColumnName(i), "常闭");break;
                                 case "5":jt.put(m.getColumnName(i), "正常");break;
                             }
                             break;
                         default: jt.put(m.getColumnName(i), rs.getString(i));break;
                     }
                    
                 }
                 ja.add(jt);
             }        
             ps.close(); 
             connection.close(); 
             System.out.print(ja);
             JSONObject ot=new JSONObject();
             ot.put("success", true);
             ot.put("data", ja);
             outJson(ot);
         }catch(Exception e){
             outJsonFailure(e.toString());
             return;
         }
    }