JDBC的增删改查(java与sql分离)

本次小测试将SQL语言和JAVA代码有效的分离。当需要对其他表的进行增删改查时，只需要修改对应的XML和对应的JavaBean就好。（或许有什么办法可以将JavaBean也进行有效分离，要是哪位大佬有思路，可以给些建议！）

文件目录：

 ParseFile.java

public class ParseFile {
    Document doc;
    //懒汉式单例
    private static ParseFile parseFile;

    public Map<String, String> getSqlmap() {
        return sqlmap;
    }

    public Map<String, String> getMap() {
        return map;
    }

    private Map<String, String> sqlmap;
    private Map<String, String> map;

    private ParseFile() {
        SAXReader sr = new SAXReader();
        try {
            doc = sr.read("./src/main/java/com/chl/jdbctest/DBOperator.xml");
            this.readConn(doc);
            this.readSql(doc);
        } catch (DocumentException e) {
            e.printStackTrace();
        }
    }

    public static ParseFile getInstance() {
        if (null == parseFile) {
            parseFile = new ParseFile();
        }
        return parseFile;
    }

    private void readConn(Document doc) {
        String driver = doc.getRootElement().element("connection").element("driver").getTextTrim();
        String url = doc.getRootElement().element("connection").element("url").getTextTrim();
        String username = doc.getRootElement().element("connection").element("username").getTextTrim();
        String password = doc.getRootElement().element("connection").element("password").getTextTrim();

       map = new HashMap<String, String>();
        map.put("driver", driver);
        map.put("url", url);
        map.put("username", username);
        map.put("password", password);
        //System.out.println( map.get("driver"));

    }

    private void readSql(Document doc) {
        sqlmap = new HashMap<>();
        List<Element> list = (List) doc.selectNodes("./DB/actions/action");
        for (Element el : list) {
            String label = el.element("label").getTextTrim();
            String value = el.element("value").getTextTrim();
            //System.out.println(label + "----" + value);
            sqlmap.put(label, value);

        }
    }

//    public static void main(String[] args) {
//        ParseFile file=new ParseFile();
//    }

}

DBOperator.xml

<DB>
    <connection>
        <driver>com.mysql.jdbc.Driver</driver>
        <url>jdbc:mysql://127.0.0.1:3306/xxxxxx</url>
        <username>xxxxxx</username>
        <password>xxxxxx</password>
    </connection>

    <actions>
        <!--查询-->
        <action>
            <label>query</label>
            <value>select * from t_classes</value>
        </action>

        <!--增加-->
        <action>
            <label>add</label>
            <value>insert into t_classes values(?,?)</value>
        </action>

        <!--更新-->
        <action>
            <label>update</label>
            <value>update t_classes set cname=? where cid=?</value>
        </action>

        <!--删除-->
        <action>
            <label>delete</label>
            <value>delete from t_classes where cid=?</value>
        </action>

    </actions>
</DB>

ClassBean.java

public class ClassBean {
    public int getCid() {
        return cid;
    }

    public void setCid(int cid) {
        this.cid = cid;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    private int cid;
    private String cname;
}

test.java

public class test {
    static ParseFile parseFile;

    static {
       parseFile= ParseFile.getInstance();

    }
    private Connection conn;
    List<HashMap<String,Object>> lists=new ArrayList<HashMap<String,Object>>();
    HashMap<String,Object> map;
    public test()
    {
        try {
            Class.forName( parseFile.getMap().get("driver"));
            conn = DriverManager.getConnection
                    (parseFile.getMap().get("url"), parseFile.getMap().get("username"), 
                            parseFile.getMap().get("password"));
            System.out.println("数据库连接成功"+conn);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    //查询
    public void queryData()
    {
        String sql=parseFile.getSqlmap().get("query");
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            //得到表结构
            ResultSetMetaData metaData = rs.getMetaData();
            while (rs.next())
            {
               map=new HashMap<String, Object>();
                //将每一行的各列都输出
                for (int i=0;i<metaData.getColumnCount();i++)
                {
                    map.put(metaData.getColumnName(i+1),rs.getObject(i+1));
                }
                lists.add(map);
            }
            for (HashMap<String,Object> el:lists)
            {
                for (String key:el.keySet())
                {
                    System.out.println(key+"--"+el.get(key));
                }
                System.out.println(" ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (null==conn) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //增加数据
    public void addData(ClassBean classes)
    {
        String sql=parseFile.getSqlmap().get("add");
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1,classes.getCid());
            pst.setString(2,classes.getCname());
            pst.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (null==conn) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
    //更新数据。
    public void upData(ClassBean updata)
    {
        String sql=parseFile.getSqlmap().get("update");
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,updata.getCname());
            pst.setInt(2,updata.getCid());
            pst.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }


    }

    //删除数据
    public void delData(ClassBean deldata)
    {
        String sql=parseFile.getSqlmap().get("delete");
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1,deldata.getCid());
            pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        test t=new test();
//       ClassBean classes=new ClassBean();
//       classes.setCid(103);
//       classes.setCname("气象班");
//       t.addData(classes);
//        ClassBean udata=new ClassBean();
//        udata.setCname("新人班");
//        udata.setCid(122);
//        t.upData(udata);

//        ClassBean deldata=new ClassBean();
//        deldata.setCid(122);
//        t.delData(deldata);

       t.queryData();


    }
}