我如何从不同的表(MySQL数据库)创建一个JSON对象在android项目中消费 - how can i create a JSON object from different tables( MySQL database) to consume in android project?

how can i create a JSON object from different tables( MySQL database) to consume in android project?

这是我的API。如何使用JAVA将结果转换为JSON对象?

$sql1="SELECT s.* , c.*,u.* 
       FROM schedule_ s,course_t c, u user_t 
       WHERE c.course_crn=p.course_crn and s.teacher_id=u.employee_id" ;
$result=mysqli_query($con,$sql1);
  if($result)
{
   while($row=mysqli_fetch_array($result))
 {
  $data[]=$row;
  }
   print(json_encode($data));

您需要执行以下操作

加载mysql驱动程序:

    Class.forName("com.mysql.jdbc.Driver"); // You need to include and load jdbc driver

使用您的凭据、主机和端口创建连接:

    String url = "jdbc:mysql://mysql_host:mysql_port/mysql_db";
    String user = "mysql_user";
    String pass = "mysql_pass";
    Connection conn = DriverManager.getConnection(url, user, pass);

执行SQL查询:

    String sql = "SELECT s.* , c.*,u.* FROM schedule_ s,course_t c, u user_t WHERE c.course_crn=p.course_crn and s.teacher_id=u.employee_id;";
    PreparedStatement preparedStatement = conn.prepareStatement(sql);
    ResultSet result = preparedStatement.executeQuery();

构建你的对象:

    List<HashMap<String, Object>> rows = new ArrayList<HashMap<String, Object>>(); 
    ResultSetMetaData md = result.getMetaData();
    int columns = md.getColumnCount();
    while (result.next()) {
        HashMap<String, Object> row = new HashMap<>();
        for (int i = 1; i <= columns; ++i) {
            row.put(md.getColumnName(i), result.getObject(i));
        }
        rows.add(row);
    }

然后可以使用GSON将java对象转换为JSON:

    String json = new Gson().toJson(rows); // You can use GSon from google to build json
    System.out.println(json);