Sql查询语句将数据转换数据格式

小编今天要分享的是，通过Sql查询语句将数据转换成想要的数据格式

banji表结构为：
name    scores  subject
张三      60      英语
张三      82      数学
张三     73      语文
李四      74      数学
李四      89      英语
李四     90      语文
tiyu表结构为：
name    scores
张三     5
李四      6

需要得到的数据结构为：

name    语文      数学      英语  体育
张三      73      82      60    5
李四     90      74      89    6

接下来上代码：
Bean

public class SutedentStat {

 private String name;
 private int yscores;
 private int sscores;
 private int escores;
 private int tscores;
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public int getYscores() {
    return yscores;
}
public void setYscores(int yscores) {
    this.yscores = yscores;
}
public int getSscores() {
    return sscores;
}
public void setSscores(int sscores) {
    this.sscores = sscores;
}
public int getEscores() {
    return escores;
}
public void setEscores(int escores) {
    this.escores = escores;
}
public String getTscores() {
    return tscores;
}
public void setTscores(String tscores) {
    this.tscores= tscores;
}


}

Dao：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;


public class NameConnect {
    private static final Logger log = Logger.getLogger(StatConnection.class);

    private static Connection getConn() throws ClassNotFoundException,
            SQLException {
        Class.forName(driverName);
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    public static List<SutedentStat > stlist() {
        String arSql = "SELECT * from scores";
        String wdSql = " SELECT * from tiyu";

        System.out.println(arSql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet res;
        PreparedStatement wdps = null;
        ResultSet wdres;
        List<SutedentStat > result = new ArrayList();

        Map<String, SutedentStat > maps = new HashMap<String, SutedentStat >();

        try {
            conn = getConn();
            ps = conn.prepareStatement(arSql);
            res = ps.executeQuery();
            wdps = conn.prepareStatement(wdSql);
            wdres = wdps.executeQuery();
            ResultBean rebean = new ResultBean();
            while (res.next()) {
                String name= res.getString("name");
                String subject = res.getInt("subject");
                int counts = res.getInt("scores");
                rebean = maps.get(name);
                if (rebean == null) {
                    rebean = new ResultBean();
                    rebean.setKeyword(name);
                }
                if (subject.contains（ "语文") {
                    rebean.setYscores (counts);
                } else if (subject.contains（ "数学") {
                    rebean.setSscores (counts);
                } else if (subject.contains（"英语") {
                    rebean.setEscores (counts);
                } 
                maps.put(name, rebean);

            }

            while (wdres.next()) {
                String name= wdres.getString("name");
                int scores= res.getInt("scores");
                rebean = maps.get(name);
                if (rebean == null) {
                    rebean = new SutedentStat ();
                    rebean.setKeyword(name);
                }
                rebean.setTscores(counts);
                maps.put(name, rebean);
            }
            for (String key : maps.keySet()) {
                result.add(maps.get(key));
            }


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            log.error(driverName + " not found!", e);
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("Connection error!", e);
        } finally {
            ;
            try {
                if (ps != null) {
                    ps.close();
                    ps = null;
                }
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }
}

main:

public static void main(String[] args){   
        StConnect a =new StConnect();
        List<SutedentStat > s = a.stlist();
        for(int i=0;i<s.size();i++){
            SutedentStat ss =s.get(i);
            System.out.println(ss.getName());
        }


    }

ok.完成了