JDBC学习笔记(二)——通过一个简单项目认识事务和MVC模型

xiaoxiao2021-02-28  37

一、事务

 

1.1事务的四大特性(ACID)

    1.原子性:事务中的所有操作要么全部执行成功,要么执行全部失败。    2.一致性:事务执行后,数据库状态与其它业务规则保持一致。    3.隔离性:隔离性是指在并发操作中,不同事务之间应该隔离开来,使每个并发中的事务不会相互干扰。    4.持久性:一旦事务提交成功,事务中所有的数据操作都必须被持久化到数据库中。即使提交事务后数据库马上崩溃,在数据库重启后,也必须能保证通过某种机制恢复数据。1.2JDBC中操作事务在JDBC中处理事务都是通过Connection对象完成的,同一事务中的所有操作,都在使用同一个Connection对象。setAutoCommit(boolean);设置是否自动提交事务,如果为true表示自动提交(默认值就是true),也就是每条执行的sql语句都是一个单独的事务,如果设置false,那么就相当于开启了事务了。con.setAutoCommit(false);语句表示开启事务。con.commit();提交并结束事务。con.rollback();回滚事务。二、MVC模型简单介绍 MVC是模型(model)、视图(view)、控制器(controller)的缩写。即MVC分为模型层、视图层、控制层。    1.模型层:分为两个部分:1.数据库中具体表的映射类。2.对数据库映射类操作(增、删、改、查)方法的封装类。    2.业务层:一般还会有一个业务层,业务层调用模型层中基本操作方法(增、删、改、查)实现更复杂的操作(即业务)。    3.控制层:对业务层方法的调用,在视图层与控制层之间起到了一个桥梁的作用。    4.视图层:直接与用户交互,用于接受用户的数据和用户进行数据交换。三、通过一个简单的MVC模型项目了解JDBC和事务(没用到连接池)项目说明功能:通过事务实现用户之间的转账功能。具体操作流程:分别输入转账人、收款人的用户名和具体转账金额,完成用户之间的转账。

3.1 数据库中表单和账号信息准备

account_info 表单:

trans_info 表单:

account_info 中插入的两条账号记录用于转账

3.2 准备的类

    1.DBUtil类  //取得数据库的连接。    2.Account_info类  //数据库中用户信息的映射类。    3.Trans_info类  //数据库中用户交易记录的映射类。    4.AccountDao类  //对Account_info基本操作方法(增删改查)的封装类。    5.TransDao类  //对Trans_info基本操作方法(增删改查)的封装类。    6.TransService类  //实现转账功能。    7.Trans_action类  //对TransService类的调用,和控制。    8.View类  //用于接受用户数据。

说明:2345属于模型层,6属于业务层、7属于控制层、8属于视图层。

3.3 实现代码

DBUtil类:

package transaction; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBUtil { static Connection con=null; static { String url="jdbc:mysql://localhost:3306/demo1?useSSL=false&useUnicode=ture&characterEncoding=utf8";//连接数据库的url String user="root";//用户名 String password="123";//密码 try { con=DriverManager.getConnection(url,user,password);//获取与数据库连接的对象 }catch(SQLException e) { e.printStackTrace(); } }     //获取连接对象的方法         public static Connection getConnection() { return con; } }

Account_info类:

package transaction; import java.util.Date; public class Account_info {     //数据库account_info表中字段的定义         private Integer id; private String name; private double balance; private Date date; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public double getBalance() { return balance; } public void setBalance(double balance) { this.balance = balance; } public Date getDate() { return date; } public void setDate(Date date) { this.date = date; } }

Trans_info类:

package transaction; public class Trans_info {        //对数据库中trans_info表字段的定义         private Integer t_id; private Integer source_id; private String source_name; private Integer destination_id; private String destination_name; private double money; public double getMoney() { return money; } public void setMoney(double money) { this.money = money; } public Integer getT_id() { return t_id; } public void setT_id(Integer t_id) { this.t_id = t_id; } public Integer getSource_id() { return source_id; } public void setSource_id(Integer source_id) { this.source_id = source_id; } public String getSource_name() { return source_name; } public void setSource_name(String source_name) { this.source_name = source_name; } public Integer getDestination_id() { return destination_id; } public void setDestination_id(Integer destination_id) { this.destination_id = destination_id; } public String getDestination_name() { return destination_name; } public void setDestination_name(String destination_name) { this.destination_name = destination_name; } public String toString() { String st; st=t_id.toString()+" "+source_id.toString()+" "+source_name.toString()+" "+destination_id.toString() +" "+destination_name.toString(); return st; } }

AccountDao类:

package transaction; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; public class AccountDao { //在数据库account_info表中添加账号的方法 public void Account_add(Account_info account) throws SQLException { Connection con=DBUtil.getConnection();//获取连接对象 String sql="insert into account_info value(?,?,?,?;)";//编写预处理sql语句 PreparedStatement pstmt=con.prepareStatement(sql);//创造PreparedStatement对象 //定义sql语句中不完整的部分 pstmt.setInt(1, account.getId()); pstmt.setNString(2, account.getName()); pstmt.setDate(3, new Date(account.getDate().getTime())); pstmt.setDouble(4, account.getBalance()); int isexe=pstmt.executeUpdate();//执行更改操作 } //在数据库account_info表中删除账号的方法 public void Account_delete(Account_info account) throws SQLException { Connection con=DBUtil.getConnection(); String sql="delete from account_info where id=?"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setInt(1, account.getId()); int isexe=pstmt.executeUpdate(); } //更新指定id用户账号的账户金额的方法 public void Account_update(Account_info account) throws SQLException { Connection con=DBUtil.getConnection(); String sql="update account_info set balance=? where id=?"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setInt(1, (int) account.getBalance()); pstmt.setInt(2, account.getId()); int isexe=pstmt.executeUpdate(); } //查询所有账号信息 public List<Account_info> Account_query() throws SQLException { List<Account_info> list=new ArrayList<Account_info>();//创建用于接收Account_info对象的列表对象 Connection con=DBUtil.getConnection(); String sql="select * from account_info"; Statement stmt=con.createStatement(); ResultSet rs=stmt.executeQuery(sql);//执行查询操作获得结果集对象 //遍历结果集,将结果集赋值给对应Account_info实例 while(rs.next()) { Account_info ac=new Account_info();//创建用于接收指定结果集的Account_info对象 //获取结果集中的数据给Account_info对象赋值 ac.setId(rs.getInt("id")); ac.setName(rs.getString("a_name")); ac.setDate(rs.getDate("a_date")); ac.setBalance(rs.getInt("balance")); list.add(ac); } return list; } //根据id查询指定用户账号方法 public Account_info Account_query(int id) throws SQLException{ Account_info ac=new Account_info();//创建用于接收查询结果的Account_info对象 Connection con=DBUtil.getConnection(); String sql="select * from account_info where id=?"; PreparedStatement stmt=con.prepareStatement(sql); stmt.setInt(1, id); ResultSet rs=stmt.executeQuery(); rs.next();//这里要注意,如果要对结果集进行操作就一定要实行这一步 ac.setId(rs.getInt("id")); ac.setName(rs.getString("a_name")); ac.setDate(rs.getDate("a_date")); ac.setBalance(rs.getInt("balance"));                 return ac;}}

TransDao类:

package transaction; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; public class TransDao { public void Trans_add(Trans_info trans) throws SQLException { Connection con=DBUtil.getConnection(); String sql="insert into trans_info(source_id,source_name,destination_id,destination_name,money) value(?,?,?,?,?)"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setInt(1, trans.getSource_id()); pstmt.setString(2, trans.getSource_name()); pstmt.setInt(3, trans.getDestination_id()); pstmt.setString(4, trans.getDestination_name()); pstmt.setDouble(5, trans.getMoney()); int isexe=pstmt.executeUpdate(); } public void Trans_delete(Trans_info trans) throws SQLException { Connection con=DBUtil.getConnection(); String sql="delete from trans_info where id=?"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setInt(1, trans.getT_id()); int isexe=pstmt.executeUpdate(); } public void Trans_update(Trans_info trans) throws SQLException { Connection con=DBUtil.getConnection(); String sql="update trans_info set source_id=?,source_name=?,destination_id=?,destination_name=?,money=? where id=?"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setInt(1, trans.getSource_id()); pstmt.setString(2, trans.getSource_name()); pstmt.setInt(3, trans.getDestination_id()); pstmt.setString(4, trans.getDestination_name()); pstmt.setDouble(5, trans.getMoney()); pstmt.setInt(6, trans.getT_id()); int isexe=pstmt.executeUpdate(); } public List<Trans_info> Trans_query() throws SQLException { List<Trans_info> list=new ArrayList<Trans_info>(); Connection con=DBUtil.getConnection(); String sql="select * from trans_info"; Statement stmt=con.createStatement(); ResultSet rs=stmt.executeQuery(sql); while(rs.next()) { Trans_info trans=new Trans_info(); trans.setT_id(rs.getInt("t_id")); trans.setSource_id(rs.getInt("source_id")); trans.setSource_name(rs.getString("source_na,e")); trans.setDestination_id(rs.getInt("destination_id")); trans.setDestination_name(rs.getString("destination_name")); list.add(trans); } return list; } public Trans_info Trans_query(int id) throws SQLException { Trans_info trans=new Trans_info(); Connection con=DBUtil.getConnection(); String sql="select * from trans_info where id=?"; PreparedStatement stmt=con.prepareStatement(sql); stmt.setInt(1, id); ResultSet rs=stmt.executeQuery(); rs.next(); trans.setT_id(rs.getInt("t_id")); trans.setSource_id(rs.getInt("source_id")); trans.setSource_name(rs.getString("source_na,e")); trans.setDestination_id(rs.getInt("destination_id")); trans.setDestination_name(rs.getString("destination_name")); trans.setMoney(rs.getDouble("money")); return trans; } }

TransService类 :

package service; import java.sql.Connection; import java.sql.SQLException; import transaction.AccountDao; import transaction.Account_info; import transaction.DBUtil; import transaction.TransDao; import transaction.Trans_info; public class TransService {     //业务层方法输入参数为:转账人(from)、收款方(to)、转账金额(money)  public String transaction(Account_info from ,Account_info to, double money) throws SQLException { Connection con=DBUtil.getConnection(); con.setAutoCommit(false);//关闭每次操作自动提交,即开始事务操作 try { AccountDao a_dao=new AccountDao();//创建AccountDao对象用于对指定账户对象进行基本操作 TransDao t_dao=new TransDao();//创建TransDao对象用于对指定转账信息对象进行基本操作         //对转账人账户金额进行修改 from.setBalance(from.getBalance()-money); a_dao.Account_update(from);         //对收款方账户金额进行修改 to.setBalance(to.getBalance()+money); a_dao.Account_update(to);         Trans_info trans=new Trans_info();//创建用于接收转账信息的Trans_info对象         //对trans对象进行赋值         trans.setSource_id(from.getId()); trans.setSource_name(from.getName()); trans.setDestination_id(to.getId()); trans.setDestination_name(to.getName()); trans.setMoney(money); t_dao.Trans_add(trans);//在数据库中添加转账信息 con.commit();//提交事务 return "成功";//如果事务进行成功返回一个"成功"字符串 }catch(Exception e) { con.rollback();//如果事务中存在异常,回滚这次事务执行的操作,即取消这次事务中的任何操作,并返回一个"失败"字符串 e.printStackTrace(); return "失败"; } finally{ con.close(); } } }

Trans_action类  :

package action; import java.sql.SQLException; import service.TransService; import transaction.AccountDao; import transaction.Account_info; public class Trans_action {        //控制层方法输入参数为:转账人的id(f_id)、收款人的id(t_id)、转账金额(money)               public String trans_action(int f_id,int t_id ,double money) throws SQLException { AccountDao a_dao=new AccountDao();//创建一个Account对象用于对查找Account对象 Account_info from=a_dao.Account_query(1); Account_info to=a_dao.Account_query(2);                                 //调用业务层的transcation(from,to,money)方法实现业务                 TransService ts=new TransService(); String re=ts.transaction(from, to, money); return re; } }

View类:

package test; import java.sql.SQLException; import java.util.Scanner; import action.Trans_action; public class view { public static void main(String[] args) throws SQLException { Scanner scan=new Scanner(System.in); System.out.println("请输入转账人的id:"); int f_id=scan.nextInt(); System.out.println("请输入收款方的id:"); int t_id=scan.nextInt(); System.out.println("亲输入转账金额:"); double money= scan.nextDouble(); Trans_action action=new Trans_action(); String rs=action.trans_action(f_id, t_id, money); System.out.println("您的转账结果:"+rs); } }

3.4 效果展示

eclipse 控制台中输入数据:

MySQL中用户账号金额的变化:

MySQL中的转账信息(看最新一条):

转载请注明原文地址: https://www.6miu.com/read-2800235.html

最新回复(0)