事务
- 数据库事务:Database Transaction
- 作为单个逻辑工作单元执行的一系列操作,要么完全执行,要么完全不执行
- 事务,必须满足所谓的ACID(原子性,一致性,隔离性,持久性)
- 事务是数据库运行中的逻辑工作单位,由DBMS中事务管理子系统负责事务的处理
JDBC事务
- 关闭自动提交,实现多语句同一事务
- connettion.setAutoCommit(false);
- connection.commit();提交事务
- connection.rollback();回滚事务
- 保存点机制
– connection.setSavepoint()
– connection.rollback(Savepoint)
public class TransactionTest {
public static void main(String[] args) throws Exception {
// 构建Java和数据库之间的桥梁介质
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
} catch (ClassNotFoundException e1) {
System.out.println("注册驱动失败!");
e1.printStackTrace();
}
String url = "jdbc:mysql://localhost:3306/test";
Connection conn = null;
try {
// 构建Java和数据库之间的桥梁:URL,用户名,密码
conn = DriverManager.getConnection(url, "root", "123456");
conn.setAutoCommit(false);
insertBook(conn, "insert into t_book values(101, 'aaaa', 10)");
insertBook(conn, "insert into t_book values(102, 'bbbb', 10)");
insertBook(conn, "insert into t_book values(103, 'cccc', 10)");
Savepoint phase1 = conn.setSavepoint(); //设置一个保存点
insertBook(conn, "insert into t_book values(104, 'cccc', 10)");
insertBook(conn, "insert into t_book values(105, 'cccc', 10)");
conn.rollback(phase1); //回滚到phase1保存点,即上面2行无效
conn.commit(); //语句被回滚就不能commit了
System.out.println("操作成功");
} catch (SQLException e) {
e.printStackTrace();
conn.rollback();
} finally {
if (null != conn) {
conn.close();
}
}
}
public static void insertBook(Connection conn, String sql) {
try {
// 构建数据库执行者
Statement stmt = conn.createStatement();
//System.out.print("创建Statement成功!");
// 执行SQL语句
int result = stmt.executeUpdate(sql);
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
PreparedStatement
首先,拼接SQL字符串很危险,恶意的SQL字符串同意当成SQL语句被执行
- Java提供 PreparedStatement,更为安全的执行SQL
- 和Statement区别是使用了“?”代替字符串拼接
- 使用 setXXX(int,Objet)的函数来实现对于?的替换
- 提供方addBatch 批量更新功能
- Select语句一样用ResultSet接收结果
public class InsertTest {
public static void main(String[] a)
{
//unsafeConcatInsertBook();
//safeInsertBook();
batchInsertBook(); //批量插入
}
public static void unsafeConcatInsertBook()
{
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
}
String url="jdbc:mysql://localhost:3306/test?allowMultiQueries=true";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁:URL,用户名,密码
conn = DriverManager.getConnection(url, "root", "123456");
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功!");
//执行SQL语句
int bookid = 10;
String bookName = "Effective Java',50);delete from t_book;insert into t_book values(101, 'faked book"; //有恶意的sql语句,会当做语句执行
int price = 50;
//values(1, 'Effective Java', 50)
String sql = "insert into t_book(bookid,bookname,price) values("
+ bookid + ", '" + bookName + "', " + price + ");";
System.out.println(sql);
int result = stmt.executeUpdate(sql);
stmt.close();
System.out.println("操作成功");
} catch (SQLException e){
e.printStackTrace();
}
finally
{
try
{
if(null != conn)
{
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
public static void safeInsertBook()
{
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
}
String url="jdbc:mysql://localhost:3306/test?allowMultiQueries=true";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁:URL,用户名,密码
conn = DriverManager.getConnection(url, "root", "123456");
String sql = "insert into t_book(bookid,bookname,price) values(?,?,?)";
//构建数据库执行者
PreparedStatement pstmt = conn.prepareStatement(sql);
//执行SQL语句
int bookid = 10;
String bookName = "Effective Java',50);delete from t_book;insert into t_book values(101, 'faked book";
int price = 50;
//values(1, 'Effective Java', 50)
pstmt.setInt(1, bookid);
pstmt.setString(2, bookName);
pstmt.setInt(3, price);
int result = pstmt.executeUpdate();
pstmt.close();
System.out.println("操作成功");
} catch (SQLException e){
e.printStackTrace();
}
finally
{
try
{
if(null != conn)
{
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
public static void batchInsertBook()
{
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
}
String url="jdbc:mysql://localhost:3306/test?allowMultiQueries=true";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁:URL,用户名,密码
conn = DriverManager.getConnection(url, "root", "123456");
String sql = "insert into t_book(bookid,bookname,price) values(?,?,?)";
//构建数据库执行者
PreparedStatement pstmt = conn.prepareStatement(sql);
//执行SQL语句
String bookName = "aaaaaaaaaaaaaaaa";
int price = 50;
//values(1, 'Effective Java', 50)
for(int i=200;i<210;i++)
{
pstmt.setInt(1, i);
pstmt.setString(2, bookName);
pstmt.setInt(3, price);
pstmt.addBatch(); //保存这十个书名
}
pstmt.executeBatch(); //批量进行插入
pstmt.close();
System.out.println("操作成功");
} catch (SQLException e){
e.printStackTrace();
} finally {
try {
if(null != conn) {
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
}
使用PreparedStatement好处
- 防止注入攻击
- 防止繁琐的字符串拼接的错误
- 直接设置对象而不需要转换为字符串
- 使用预编译速度相对于Statement快很多
ResultSetMetaData
- ResultSet可以用来承载所有的select语句返回的结果集
- ResultSetMetaData 来获取ReultSet返回的属性
– getColumnCount(),返回结果的列数
– getColumnClassName(i),返回第i列的数据Java类名
– getColumnTypeName(i),返回第i列的数据库类型名称
– getColumnType(i),返回第i列的SQL类型
public class ResultSetMetaDataTest {
public static void main(String[] args){
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
return;
}
String url="jdbc:mysql://localhost:3306/test";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁:URL,用户名,密码
conn = DriverManager.getConnection(url, "root", "123456");
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功!");
//执行SQL语句并返回结果到ResultSet
ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");
//获取结果集的元数据
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
for(int i=1;i<=cols;i++) //一般JDBC中,都是从1开始
{
System.out.println(meta.getColumnName(i) + "," + meta.getColumnTypeName(i));
}
rs.close();
stmt.close();
} catch (SQLException e){
e.printStackTrace();
}
finally
{
try
{
if(null != conn)
{
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
}
输出
bookid,INT
bookname,VARCHAR
price,INT