JDBC 测试连接数据库 实现数据的CRUD 模拟转账异常功能
编写代码
package com.xiang.lesson01;
import java.sql.*;
import java.text.ParseException;
public class DBTest {
/*
**建立连接的五大步骤：**
1. 加载（注册）数据库
2. 建立链接
3. 语句对象来执行SQL语句
4. 处理结果集 、返回结果
5. 关闭数据库、释放资源
*/
// private static String URL = "jdbc:mysql://localhost:3307/webapp1?serverTimezone=utf8mb4";
// useSSL=false 安全连接；
private static String URL = "jdbc:mysql://localhost:3307/webapp2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false";
private static String DriverClass = "com.mysql.cj.jdbc.Driver";
private static String UserName = "webapp1";
private static String PassWord = "webapp1";
private static Connection connection = null;
private static PreparedStatement statement = null;
private static ResultSet resultSet = null;
private static Savepoint savepoint = null;
public static Connection getConnection() {
try {
Class.forName(DriverClass);
connection = DriverManager.getConnection(URL, UserName, PassWord);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
// public void closeRerource(Connection connection){
// try {
// connection.close();
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
// }
public static void closeRerource() {
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
/**
* 查询
*
* @param
*/
public static void getStudent() {
connection = getConnection();
try {
statement = connection.prepareStatement("select * from student");
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
System.out.println(
resultSet.getInt("s_id") + "\t" +
resultSet.getString("s_name") + "\t" +
resultSet.getString("s_sex") + "\t" +
resultSet.getDate("s_birthday") + "\t" +
resultSet.getInt("s_professional"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
/**
* 新增
*
* @param
*/
public static void insertStudent() throws SQLException, ParseException {
connection = getConnection();
statement = connection.prepareStatement("insert into student(s_name,s_sex,s_birthday,s_professional) values (?,?,?,?)");
statement.setString(1, "小小");
statement.setString(2, "女");
// SimpleDateFormat format = new SimpleDateFormat("YYYY-MM-dd");
statement.setDate(3, new Date(20210908));
statement.setInt(4, 4);
int update = statement.executeUpdate();
System.out.println(update + "insert into ----->ok");
}
/**
* 创建表
*
* @param
* @throws SQLException
*/
public static void createTable() throws SQLException {
connection = getConnection();
statement = connection.prepareStatement("drop table if exists stu");
statement.executeUpdate();
statement = connection.prepareStatement("create table stu\n" +
"(\n" +
" s_no int(8) not null primary key auto_increment,\n" +
" s_name varchar(12),\n" +
" s_sex varchar(4),\n" +
" s_score double(6,1)\n" +
")");
int res = statement.executeUpdate();
System.out.println(res + "创建表ok");
}
/**
* 创建表 account
*
* @param args
* @throws SQLException
* @throws ParseException
*/
/*
create table account(
id int not null primary key auto_increment,
name varchar(20),
money DOUBLE(10,2)
);
insert into account(name,money)
values
('张三',10000),
('李四',10000);
*/
public static void account() throws SQLException {
connection = getConnection();
statement = connection.prepareStatement("drop table if exists account");
statement.executeUpdate();
statement = connection.prepareStatement("create table account(\n" +
"\tid int not null primary key auto_increment,\n" +
"\tname varchar(20),\n" +
"\tmoney DOUBLE(10,2)\n" +
")");
int res = statement.executeUpdate();
System.out.println(res + "创建 account 表ok");
}
/**
* insert into
*
* @param args
* @throws SQLException
* @throws ParseException
*/
public static void insertaccount() throws SQLException {
connection = getConnection();
statement = connection.prepareStatement("insert into account(name,money)\n" +
"values\n" +
"('张三',10000),\n" +
"('李四',10000)");
int update = statement.executeUpdate();
System.out.println(update + "insert into account ----->ok");
}
/**
* 更新，实现转账 功能
*
* @param args
* @throws SQLException
* @throws ParseException
*/
public static void task() throws SQLException {
connection = getConnection();
statement = connection.prepareStatement("update account set money = money - 5000 where name = '张三'");
statement.executeUpdate();
statement = connection.prepareStatement("update account set money = money + 5000 where name = '李四'");
statement.executeUpdate();
int update = statement.executeUpdate();
System.out.println(update + "update account task ----->ok");
}
/**
* 更新，实现转账 功能
* 更加异常；
*
* @param args
* @throws SQLException
* @throws ParseException
*/
public static void task2() throws SQLException {
connection = getConnection();
connection.setAutoCommit(false);
// 回滚点； 两条 sql 都不执行；
savepoint = connection.setSavepoint();
//支出
statement = connection.prepareStatement("update account set money = money - ? where name = ?");
statement.setInt(1, 5000);
statement.setString(2, "张三");
statement.executeUpdate();
// 回滚点； 上边个 sql ( set money = money - ?) 执行；下边个不执行；
// savepoint = connection.setSavepoint();
//产生异常；
try {
int i = 100/0;
} catch (Exception e) {
connection.rollback(savepoint);
// e.printStackTrace();
}
//收入
statement = connection.prepareStatement("update account set money = money + ? where name = ?");
statement.setInt(1, 5000);
statement.setString(2, "李四");
statement.executeUpdate();
int update = statement.executeUpdate();
// 回滚；
connection.rollback(savepoint);
// 提交；
connection.commit();
System.out.println(update + "update account task2 ----->ok");
}
public static void main(String[] args) throws SQLException, ParseException {
// connection = DBTest.getConnection();
// if (connection != null){
// System.out.println("连接成功");
// }else {
// System.out.println("连接失败");
// }
account();
insertaccount();
task2();
// task();
createTable();
insertStudent();
getStudent();
closeRerource();
}
}
运行结果
