首页 > 程序开发 > 综合编程 > 其他综合 > 正文
JDBC:ORM和DAO
2017-03-20 09:35:28       个评论      
收藏    我要投稿

JDBC:ORM和DAO:ORM 对象关系映射,对象和关系数据库的映射,简单说,一个对象,对应数据库里的一条记录。

例:根据id 返回一个Hero对象

提供方法get(int id)
返回一个Hero对象

对象类
package charactor;

public class Hero {
    //增加id属性
    public int id;
    public String name;
    public float hp;
    public int damage;

}
Test类
package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import charactor.Hero;

public class TestJDBC {

    public static Hero get(int id) {
        Hero hero = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");
            Statement s = c.createStatement();) {

            String sql = "select * from hero where id = " + id;

            ResultSet rs = s.executeQuery(sql);

            // 因为id是唯一的,ResultSet最多只能有一条记录
            // 所以使用if代替while
            if (rs.next()) {
                hero = new Hero();
                String name = rs.getString(2);
                float hp = rs.getFloat("hp");
                int damage = rs.getInt(4);
                hero.name = name;
                hero.hp = hp;
                hero.damage = damage;
                hero.id = id;
            }

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

    }

    public static void main(String[] args) {

        Hero h = get(22);
        System.out.println(h.name);

    }
}

DAO 数据访问对象

DAO=Database Access Object
数据库访问对象

设计一个ItemDAO,实现了DAO接口,提供相应的功能实现Item类的ORM

1 创建item表
CREATE TABLE item (
  id int(11) AUTO_INCREMENT,
  name varchar(30) ,
  price int,
  PRIMARY KEY (id)
)  DEFAULT CHARSET=utf8;
2 实现
对象类
package property;

import charactor.LOL;

public class Item implements LOL ,Comparable{
    public int id;
    public String name;
    public int price;

    public Item(){

    }

    public Item(String name){
        this.name = name;
    }

    public void effect(){
        System.out.println("物品使用后,可以有效果");
    }

    public boolean disposable() {
        return false;
    }

    @Override
    public int compareTo(Item o) {
        return o.price-price;
    }

    @Override
    public String toString() {
        return "Item [name=" + name + ", price=" + price + "]\r\n";
    }

}

DAO接口

public interface DAO{
    public int getTotal();
    public void add(Item item);
    public void update(Item item);
    public void delete(int id);
    public Item get(int id);
    public List list();
}

DAO实现类

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

import property.Item;

public class ItemDAO {

public ItemDAO() {
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root",
                "admin");
    }

    public int getTotal() {
        int total = 0;
        try (Connection c = getConnection(); Statement s = c.createStatement();) {

            String sql = "select count(*) from item";

            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                total = rs.getInt(1);
            }

            System.out.println("total:" + total);

        } catch (SQLException e) {

            e.printStackTrace();
        }
        return total;
    }

    public void add(Item item) {

        String sql = "insert into item values(null,?,?)";
        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setString(1, item.name);
            ps.setInt(2, item.price);

            ps.execute();

            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                int id = rs.getInt(1);
                item.id = id;
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
    }

    public void update(Item item) {

        String sql = "update item set name= ?, price = ?  where id = ?";
        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setString(1, item.name);
            ps.setInt(2, item.price);
            ps.setInt(3, item.id);

            ps.execute();

        } catch (SQLException e) {

            e.printStackTrace();
        }

    }

    public void delete(int id) {

        try (Connection c = getConnection(); Statement s = c.createStatement();) {

            String sql = "delete from item where id = " + id;

            s.execute(sql);

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }

    public Item get(int id) {
        Item item = null;

        try (Connection c = getConnection(); Statement s = c.createStatement();) {

            String sql = "select * from item where id = " + id;

            ResultSet rs = s.executeQuery(sql);

            if (rs.next()) {
                item = new Item();
                String name = rs.getString(2);
                int price = rs.getInt(3);
                item.name = name;
                item.price = price;
                item.id = id;
            }

        } catch (SQLException e) {

            e.printStackTrace();
        }
        return item;
    }

    public List list() {
        return list(0, Short.MAX_VALUE);
    }

    public List list(int start, int count) {
        List items = new ArrayList();

        String sql = "select * from item order by id desc limit ?,? ";

        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setInt(1, start);
            ps.setInt(2, count);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Item item = new Item();
                int id = rs.getInt(1);
                String name = rs.getString(2);
                int price = rs.getInt(3);
                item.name = name;
                item.price = price;
                item.id = id;

                items.add(item);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return items;
    }

}

测试

import java.util.List;

import property.Item;

public class TestDAO {
    public static void main(String[] args) {
        ItemDAO dao= new ItemDAO();
        List is =dao.list();
        System.out.println("数据库中总共有" + is.size() + " 条数据");
        Item i = new Item("新的物品");
        System.out.println("新加一条数据");
        dao.add(i);
        is =dao.list();
        System.out.println("数据库中总共有" + is.size() + " 条数据");
        System.out.println("取出id=4的数据,它的name是:");
        i = dao.get(4);
        System.out.println(i.name);
        System.out.println("把名字改为 物品X,并且更新到数据库");
        i.name="物品X";
        dao.update(i);
        System.out.println("取出id=4的数据,它的name是:");
        i = dao.get(4);
        System.out.println(i.name);
        System.out.println("删除id=4的数据");
        dao.delete(i.id);
        is =dao.list();
        System.out.println("数据库中总共有" + is.size() + " 条数据");

    }
}
点击复制链接 与好友分享!回本站首页
上一篇:二叉搜索树转化为更大的树
下一篇:Java字节字符流转换
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站