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,

  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("卡号或密码错误 登录失败");

        }

    }

}

巴特西

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

最新文章

热门文章