【jdbc访问数据库获取执行sql转换json】

时间:2021-10-12 11:58:07

Talk is cheap.Show me your code. 

  1 import java.sql.*;
  2 import java.util.HashMap;
  3 import java.util.Map;
  4 
  5 /**
  6  * @version: java version 1.7+
  7  * @Author : simon
  8  * @Explain :
  9  * @contact: 
 10  * @Time : 2018/8/15 14:44
 11  * @File : DBUtilForCarLoan
 12  * @Software: IntelliJ IDEA 2017.3.2
 13  */
 14 public class DBUtilForCarLoan {
 15 
 16     /**
 17      * 访问数据库筛选数据并以json格式返回
 18      *
 19      */
 20 
 21     public static final String DB_URL=""; // DB URL
 22     public static final String DB_DRIVER="com.mysql.jdbc.Driver";// DB driver
 23     public static final String USER_NAME=""; // DB user name
 24     public static final String USER_PASSWORD=""; // DB user password
 25     public static final String EXECUTE_SQL=""; //DB execute sql
 26 
 27     /**
 28      * add by simon
 29      *
 30      * 功能:发送数据库连接请求
 31      *
 32      * @param url 数据库地址
 33      * @param driver 数据库驱动
 34      * @param name  数据库名称
 35      * @param password  数据库密码
 36      * @return
 37      */
 38     public static Connection sendDBconnection( String url,String driver,String name,String password){
 39 
 40         Connection connection=null;
 41         try {
 42             Class.forName(driver);
 43             connection = DriverManager.getConnection(url,name,password);
 44 
 45         } catch (ClassNotFoundException e) {
 46             e.printStackTrace();
 47         }catch (SQLException e) {
 48 
 49             System.out.println("数据库登录失败,数据库为:" + DB_URL + ",用户名为:" + USER_NAME + ",密码为:" + USER_PASSWORD);
 50             e.printStackTrace();
 51         }
 52         return connection;
 53     }
 54 
 55     /**
 56      * add simon
 57      *
 58      * 功能:连接数据库并执行SQL语句并返回执行结果
 59      *
 60      * @param sql
 61      * @return
 62      */
 63     public static ResultSet getSqlResult( String sql ){
 64 
 65         Connection con = sendDBconnection(DB_URL,DB_DRIVER,USER_NAME,USER_PASSWORD);
 66         Statement statement =null;
 67         ResultSet sqlresult=null;
 68 
 69         try {
 70 
 71             //Statement对象执行数据库操作语句
 72             statement = con.createStatement();
 73             //执行sql语句并将结果保存在sqlresult
 74             sqlresult = statement.executeQuery(sql);
 75 
 76         } catch (SQLException e) {
 77             e.printStackTrace();
 78         }
 79 
 80         return sqlresult;
 81     }
 82 
 83     /**
 84      * add by simon
 85      *
 86      * 功能:将ResultSet转换为 Map<String,String>
 87      * @param rs
 88      * @return Map<String,String>
 89      * @throws SQLException
 90      */
 91     public static Map<String,String> getResultMap (ResultSet rs) throws SQLException {
 92 
 93         Map<String, String> map = new HashMap<String, String>();
 94         ResultSetMetaData rsmd = rs.getMetaData();
 95         int count = rsmd.getColumnCount();
 96 
 97         for (int i = 1; i <= count; i++) {
 98             String key = rsmd.getColumnLabel(i);
 99             String value = rs.getString(i);
100             map.put(key, value);
101         }
102         return map;
103     }