Oracle查询银行卡数、修改余额及验证登录

最新推荐文章于 2023-06-02 16:14:11 发布

weixin_34378767

最新推荐文章于 2023-06-02 16:14:11 发布

阅读量361

点赞数

文章标签： java 数据库

原文链接：http://www.cnblogs.com/wangguoning/p/5985980.html

版权

建立Oracle表

create table T_BANKCARD
(
  card_id     VARCHAR2(20) not null,
  user_id     VARCHAR2(20) not null,
  user_name   VARCHAR2(20) not null,
  password    VARCHAR2(10) not null,
  balance     NUMBER(14,2) default 0,
  state       CHAR(1) default '1' not null,
  create_time DATE,
  column_8    CHAR(10)
)
-- Add comments to the table 
comment on table T_BANKCARD
  is '银行卡';
-- Add comments to the columns 
comment on column T_BANKCARD.card_id
  is '卡号';
comment on column T_BANKCARD.state
  is '状态： 1 有效  2停用  3注销';
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_BANKCARD
  add constraint PK_T_BANKCARD primary key (CARD_ID)

通过Java连接数据库实现

package com.hanqi.bank;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

//封装银行卡表的数据库操作类
public class CardDAO {

    //添加卡
    //返回卡号
    public String addcard(String userid,String username,String password)
    {
        String rtn = null;
        //生成卡号
        String cardid = (int)(Math.random()*1000000)+"";//转换为字符串格式
        try 
        {
        
        //保存数据
        
        //1加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
        
        //2获得连接
            Connection conn = DriverManager.getConnection
                    ("jdbc:oracle:thin:@localhost:1521:orcl", 
                       "test0816", "12345678");
        
        //3创建声明
            
            Statement st = conn.createStatement();
        //4执行语句
        String sql = "insert into T_BANKCARD(card_id,user_id,user_name,password,create_time)"
                +"values('"+cardid+"','"+userid+"','"+cardid+"username','"+password+"',sysdate)";
        if(st.executeUpdate(sql)==1)
        {
            rtn = cardid;
        }
        //5释放资源
        st.close();
        conn.close();
        
        
        }
        catch(Exception e)
        {
            e.printStackTrace();
            
        }
        
        return rtn;
    }
    
    //修改余额
    //可以完成存款和取款的功能
    //卡号  ； 最终余额
    public boolean updateBalance(String cardid,double balance) throws Exception
    {
        boolean rtn = false;
        //验证余额是否规范
         
        if(balance<0)
        {

            throw new Exception("余额数据异常");
        }
        
        try
        {
        
        //1加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
    
    //2获得连接
        Connection conn = DriverManager.getConnection
                ("jdbc:oracle:thin:@localhost:1521:orcl", 
                "test0816", "12345678");
    
    //3创建声明
        Statement st = conn.createStatement();
    //4执行语句
    String sql = "update t_bankcard set balance ="+balance+
            "where card_id='"+cardid+"'";
    
    rtn = st.executeUpdate(sql)==1;
    
    //5释放资源
    st.close();
    conn.close();
    }
    catch(Exception e)
    {
        e.printStackTrace();
        
    }
    return rtn;    
    }
    
    //验证登录
     
    public boolean login(String cardid,String password)
    {
        boolean rtn = false;

        try
        {
        //1加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
    
    //2获得连接
        Connection conn = DriverManager.getConnection
                ("jdbc:oracle:thin:@localhost:1521:orcl", 
                        "test0816", "12345678");
    
    //3创建声明
        
        Statement st = conn.createStatement();
    //4执行语句
        
        //这种方式会造成    SQL注入
    String sql = "select * from  t_bankcard where card_id ='"+cardid+"'and password='"+
               password+"'and state='1'";
    System.out.println(sql);

    //执行查询
    ResultSet rs = st.executeQuery(sql);
    //5 遍历结果集 
    rtn = rs.next();
    
    //5释放资源
    rs.close();
    st.close();
    conn.close();
    
    
    }
    catch(Exception e)
    {
        e.printStackTrace();
        
    }
        return rtn;
    }
    
    public boolean login2(String cardid,String password)
    {
        boolean rtn = false;

        try
        {
        //1加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
    
    //2获得连接
        Connection conn = DriverManager.getConnection
                ("jdbc:oracle:thin:@localhost:1521:orcl", 
                        "test0816", "12345678");
    
    //3创建声明
        
        //Statement st = conn.createStatement();
        //带有？占位符的语句
        String sql = "select * from  t_bankcard where" + "card_id =？"
        +"and password=?and state = '1'";
        
        //预编译的声明
        //优点  1  执行效率高   2避免SQL注入
        PreparedStatement ps = conn.prepareStatement(sql);
        
        //替换占位符
        ps.setString(1, cardid);
        ps.setString(2, password);
        
        
    //4执行语句
    ResultSet rs = ps.executeQuery();
    //5 遍历结果集 
    rtn = rs.next();
    
    //结果集的元数据
    
    ResultSetMetaData rsmd = rs.getMetaData();
    System.out.println("getColumnCount="+rsmd.getColumnCount());
    System.out.println("getColumnName="+rsmd.getColumnName(1));

    //5释放资源
    rs.close();
    ps.close();
     conn.close();
    
    }
        
    catch(Exception e)
    {
        e.printStackTrace();

package com.hanqi.bank;

import static org.junit.Assert.*;

public class Test {

    @org.junit.Test
    public void testInsert() {
    //测试数卡
        CardDAO cd = new CardDAO();
        
        String cardid = cd.addcard("12345678901234", "张三", "12456");
        if(cardid!=null)
        {
            System.out.println("发卡成功"+cardid);
        }
        else
        {
            System.out.println("发卡失败");
        }
    }
    
    @org.junit.Test
    //测试修改余额
    public void testEdit()  
    {
        CardDAO cd = new CardDAO();
        try {
            if(cd.updateBalance("980159", 100))
            {
                System.out.println("余额修改成功");
            }
            else
            {
                System.out.println("余额修改不成功");
            }
        } catch (Exception e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
    }
    
    @org.junit.Test
    public void testLogin()
    {
        CardDAO cd = new CardDAO();
        if(cd.login("161314", "12456"))
        {
            System.out.println("登录成功");
        }
        else
        {
            System.out.println("卡号或密码错误 登录失败");
        }
    }
    @org.junit.Test
    public void testLogin2()
    {
        CardDAO cd = new CardDAO();
        if(cd.login("161314", "12456"))
        {
            System.out.println("登录成功");
        }
        else
        {
            System.out.println("卡号或密码错误 登录失败");
        }
    }
}


 

        
    }
    
        return rtn;
    }
    
}

测试类

package com.hanqi.bank;

import static org.junit.Assert.*;

public class Test {

    @org.junit.Test
    public void testInsert() {
    //测试数卡
        CardDAO cd = new CardDAO();
        
        String cardid = cd.addcard("12345678901234", "张三", "12456");
        if(cardid!=null)
        {
            System.out.println("发卡成功"+cardid);
        }
        else
        {
            System.out.println("发卡失败");
        }
    }
    
    @org.junit.Test
    //测试修改余额
    public void testEdit()  
    {
        CardDAO cd = new CardDAO();
        try {
            if(cd.updateBalance("980159", 100))
            {
                System.out.println("余额修改成功");
            }
            else
            {
                System.out.println("余额修改不成功");
            }
        } catch (Exception e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
    }
    
    @org.junit.Test
    public void testLogin()
    {
        CardDAO cd = new CardDAO();
        if(cd.login("161314", "12456"))
        {
            System.out.println("登录成功");
        }
        else
        {
            System.out.println("卡号或密码错误 登录失败");
        }
    }
    @org.junit.Test
    public void testLogin2()
    {
        CardDAO cd = new CardDAO();
        if(cd.login("161314", "12456"))
        {
            System.out.println("登录成功");
        }
        else
        {
            System.out.println("卡号或密码错误 登录失败");
        }
    }
}

转载于:https://www.cnblogs.com/wangguoning/p/5985980.html

weixin_34378767

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle查询银行卡数、修改余额及验证登录

建立Oracle表create table T_BANKCARD( card_id VARCHAR2(20) not null, user_id VARCHAR2(20) not null, user_name VARCHAR2(20) not null, password VARCHAR2(10) not null, balan...
复制链接

扫一扫