java连接数据库——JDBC连接数据库

com.bjpowernode.jdbc.util; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class DBUtil { private static String driver ; private static String url ; private static String uname ; private static String pwd ; static{ try { //创建集合类对象 Properties properties = new Properties(); //把文件封装成字节输入流 InputStream inStream = new FileInputStream(new File("./src/DBConfig.properties")); //把字节流加载到集合类中，在内存中以key和value的格式形成 properties.load(inStream); //通过key获得value driver = properties.getProperty("driver"); url = properties.getProperty("url"); uname = properties.getProperty("uname"); pwd = properties.getProperty("pwd"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException("读取配置文件失败！",e); } } //获得连接 public static Connection getConnection (){ Connection conn = null; try { //1:注册驱动 Class.forName(driver); conn = DriverManager.getConnection(url,uname,pwd); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException("连接数据库失败！",e); } return conn; } //释放资源 public static void close(ResultSet rs ,Statement pstm ,Connection conn){ try{ if (rs != null){ rs.close(); } }catch(SQLException e){ e.printStackTrace(); throw new RuntimeException("rs关闭失败！",e); } try{ if (pstm != null){ pstm.close(); } }catch(SQLException e){ e.printStackTrace(); throw new RuntimeException("pstm关闭失败！",e); } try{ if (conn != null){ conn.close(); } }catch(SQLException e){ e.printStackTrace(); throw new RuntimeException("conn关闭失败！",e); } } //开启事务 public static void beginTransaction(Connection conn){ try { if(conn != null){ conn.setAutoCommit(false); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException("开启事务失败！",e); } } //提交事务 public static void commit(Connection conn){ try { if(conn != null){ conn.commit(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException("提交事务失败！",e); } } //回滚事务 public static void rollback(Connection conn){ try { if(conn != null){ conn.rollback(); } } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("回滚事务失败！",e); } } }

Transfer_transaction.java  // 调用数据库文件

 

package com.bjpowernode.jdbc.transfer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.bjpowernode.jdbc.util.DBUtil;

public class Transfer_transaction {
    public static void main(String[] args) {
        transfer("zs","ls",100);
    }
    /**
     * 
     * @param from_act  : 转出账户
     * @param to_act    ：转入账户
     * @param money     ： 转账金额
     */
    private static void transfer(String from_act, String to_act, double money) {
        
        /*if(转出账户的金额 >= 转账金额)｛
            转出账户 - 转账金额
            转入账户 + 转账金额
        ｝else{
            提示余额不足
        }*/
        Connection conn  = null;
        //转出账户的金额
        try {
            //事务是针对连接开启的
            conn = DBUtil.getConnection();
            //开启事务
            DBUtil.beginTransaction(conn);
            
            double from_money = getMoneyByAct(conn,from_act);
            
            if(from_money >= money){//转出账户的金额 >= 转账金额
                //转账
                //转出账户 - 转账金额
                updateMoneyByAct(conn,from_money-money,from_act);
                //转入账户的金额
                double to_money = getMoneyByAct(conn,to_act);
                //模拟异常
//                Integer.parseInt("abc");
                //转入账户+ 转账金额
                updateMoneyByAct(conn,to_money+money,to_act);
                //提交事务
                DBUtil.commit(conn);
                System.out.println("转账成功！");
            }else{//提示余额不足
                System.out.println("余额不足！");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("转账失败！");
            //回滚事务
            DBUtil.rollback(conn);
        }finally{
            DBUtil.close(null, null, conn);
        }
        
    }
    /**
     * 通过账户修改账户金额
     * 
     * @param money : 需要修改的金额
     * @param act   ： 账户名称
     */
    private static void updateMoneyByAct(Connection conn ,double money, String act) {
        // TODO Auto-generated method stub
//        Connection conn = null;
        PreparedStatement pstm = null;
        
        try {
//            conn = DBUtil.getConnection();
            
            String sql = "update t_account set money = ? where act_no = ?";
            pstm = conn.prepareStatement(sql);
            
            pstm.setDouble(1, money);
            pstm.setString(2, act);
            
            pstm.executeUpdate();
            
            
            
        } catch (Exception e) {
            // TODO: handle exception
            
            throw new RuntimeException("修改金额失败",e);
        }finally{
            DBUtil.close(null, pstm, null);
        }
    }
    /**
     * 通过账户查询账户金额
     * @param act
     * @return
     */
    private static double getMoneyByAct(Connection conn ,String act) {
//        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        double from_money = 0;
        try {
            //获得连接
//            conn = DBUtil.getConnection();
            String sql = "select money from t_account where act_no = ?";
            //创建数据库操作对象
            pstm = conn.prepareStatement(sql);
            //为占位符号赋值
            pstm.setString(1, act);
            //执行sql
            rs = pstm.executeQuery();
            /*while(rs.next()){
                from_money = rs.getDouble("money");
            }
            if(rs.next()){
                from_money = rs.getDouble("money");
            }
            */
            from_money = rs.next()?rs.getDouble("money"):0;
            
            
        } catch (Exception e) {
            throw new RuntimeException("查询账户余额失败！",e);
        }finally{
            DBUtil.close(rs, pstm, null);
        }
        return from_money;
    }
}

DBConfig.properties   //数据库配置文件

driver=com.mysql.jdbc.Driver
url=jdbc\:mysql\://127.0.0.1\:3366/bjpowernode
uname=root
pwd=root

 

java连接数据库——JDBC连接数据库

标签：