MySQL-2

1.PreparedStatement

例子：PreparedStatement ps = c.prepareStatement(sql);

 

ps.setString(1,"提莫")；

ps.setFloat(2,313.0f);

ps.setInt(3,50);

ps.execute();

 

优点：

1.可读性好，不易犯错

2.性能比Statement更快

3.防止SQL注入式攻击

 

注：ResultSet rs = s.executeQuery(sql);

 

 

二、execute与executeUpdate

不同1：
execute可以执行查询语句
然后通过getResultSet，把结果集取出来
executeUpdate不能执行查询语句
不同2:
execute返回boolean类型，true表示执行的是查询语句，false表示执行的是insert,delete,update等等
executeUpdate返回的是int，表示有多少条数据受到了影响

 

 

获取自增长id

PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

 

获取元数据概念：与数据库服务器相关的数据

DatabaseMetaData dbmd = c.getMetaData();

  

            // 获取数据库服务器产品名称

            System.out.println("数据库产品名称:\t"+dbmd.getDatabaseProductName());

            // 获取数据库服务器产品版本号

            System.out.println("数据库产品版本:\t"+dbmd.getDatabaseProductVersion());

            // 获取数据库服务器用作类别和表名之间的分隔符 如test.user

            System.out.println("数据库和表分隔符:\t"+dbmd.getCatalogSeparator());

            // 获取驱动版本

            System.out.println("驱动版本:\t"+dbmd.getDriverVersion());

  

            System.out.println("可用的数据库列表：");

            // 获取数据库名称

            ResultSet rs = dbmd.getCatalogs();

 

 

三、事务

            c.setAutoCommit(false);

  

            // 加血的SQL

            String sql1 = "update hero set hp = hp +1 where id = 22";

            s.execute(sql1);

  

            // 减血的SQL

            // 不小心写错写成了 updata(而非update)

  

            String sql2 = "updata hero set hp = hp -1 where id = 22";

            s.execute(sql2);

  

            // 手动提交

            c.commit();

 

在事务中的多个操作，要么都成功，要么都失败
通过 c.setAutoCommit(false);关闭自动提交
使用 c.commit();进行手动提交

 

 

前提：

在Mysql中，只有当表的类型是INNODB的时候，才支持事务，所以需要把表的类型设置为INNODB,否则无法观察到事务.

修改表的类型为INNODB的SQL：

 

alter table hero ENGINE = innodb;

 

 

 

 

四、ORM = Object RelationShip Database Mapping

一个对象，对应数据库里的一条记录

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) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return hero;

   

    }

   

    public static void main(String[] args) {

           

        Hero h = get(22);

        System.out.println(h.name);

   

    }

}

 

 

三、DAO

Data Access Object

数据库访问对象

 也就是专门用一个类进行封装

ackage jdbc;

 

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<Item> list() {

        return list(0, Short.MAX_VALUE);

    }

 

    public List<Item> list(int start, int count) {

        List<Item> items = new ArrayList<Item>();

 

        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;

    }

 

}

 

 

 

 

 

 

其他：数据库连接池