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;
}
}
其他:数据库连接池