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

来源：互联网发布：c对数据库null值处理编辑：程序博客网时间：2024/05/22 10:14

小编今天要分享的是，通过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.完成了

阅读全文

0 0