java连接数据库——JDBC连接数据库
DBUtil.java // 数据库操作文件
package 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"));
/*
等同于 ResourceBundle isStream = ResourceBundle.getBundle("com.bjpowernode.jdbc.util.DBConfig"); //注意：DBConfig 不要添加后缀
*/ //把字节流加载到集合类中，在内存中以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
