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("数据库产品名称: "+dbmd.getDatabaseProductName()); // 获取数据库服务器产品版本号 System.out.println("数据库产品版本: "+dbmd.getDatabaseProductVersion()); // 获取数据库服务器用作类别和表名之间的分隔符 如test.user System.out.println("数据库和表分隔符: "+dbmd.getCatalogSeparator()); // 获取驱动版本 System.out.println("驱动版本: "+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();进行手动提交
通过 c.setAutoCommit(false);关闭自动提交
使用 c.commit();进行手动提交
前提：
在Mysql中，只有当表的类型是INNODB的时候，才支持事务，所以需要把表的类型设置为INNODB,否则无法观察到事务.
修改表的类型为INNODB的SQL：
修改表的类型为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; }}其他：数据库连接池