java基于jdbc操作MySQL数据库之查询方法封装

代码

public class TestJDBC1 {

	private String url = "jdbc:mysql://ip:3306/数据库名?characterEncoding=utf-8&useSSL=false";
	private String user = "user";
	private String password = "password";
	
	static {
		//加载驱动类到DM集合
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	//java程序与数据库建立连接
	public Connection getConnection() throws Exception {
		
		DriverManager.setLoginTimeout(5);
		return DriverManager.getConnection(url, user, password);
	}
	
	//非参数化sql通用查询
	public String noParamSqlQuery(String noParamSql) throws Exception{
		Connection con = getConnection();
		//发送sql语句
		PreparedStatement ps = con.prepareStatement(noParamSql);
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		
		//指定行
		JSONArray jsonArray = new JSONArray();
		
		while (rs.next()) {
			//基于行遍历列
			JSONObject jsonObject = new JSONObject();
			
			for(int columnIndex=1;columnIndex<=columnCount;columnIndex++) {
				String columnName = rsmd.getColumnName(columnIndex);
				Object object = rs.getObject(columnName);
				jsonObject.put(columnName, object);	
			}
			jsonArray.add(jsonObject);
		}		
		//关闭连接
		rs.close();
		ps.close();
		con.close();
		
		return jsonArray.toJSONString();
	}
	
	//参数化sql-封装的方法做不到通用,都是据业务实情来写的 
	public String getAutoLogInfoById(String paramSql,AutoLog autolog) throws Exception {
		//初始化
		Connection con = getConnection();
		//发送sql语句
		PreparedStatement ps = con.prepareStatement(paramSql);
		ps.setInt(1, autolog.getId());
		
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		
		//指定行
		JSONArray jsonArray = new JSONArray();
		
		while (rs.next()) {
			//基于行遍历列
			JSONObject jsonObject = new JSONObject();
			
			for(int columnIndex=1;columnIndex<=columnCount;columnIndex++) {
				String columnName = rsmd.getColumnName(columnIndex);
				Object object = rs.getObject(columnName);
				jsonObject.put(columnName, object);	
			}
			jsonArray.add(jsonObject);
		}		
		//关闭连接
		rs.close();
		ps.close();
		con.close();
		
		return jsonArray.toJSONString();
	}
}