com.bank.abc;
import java.beans.PropertyVetoException;
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.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.mchange.v2.c3p0.ComboPooledDataSource;
//封装银行卡表的数据库操作类
public class CardDAO {
//添加卡
public String addCard(String name,String shenfen ,
double balance)
{
String rnt=
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","test01","123456"
);
//3创建声明
Statement st=
conn.createStatement();
//4执行语句
String sql="insert into bank values(‘"+name+"‘,‘"+shenfen+"‘,‘"+cardid+"‘,‘"+balance+"‘)"
;
if(st.executeUpdate(sql)>0
)
{
return cardid;
}
//5释放资源
st.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
return rnt;
}
//修改余额
//可以完成取款和存款的功能
public boolean updateBalance(String cardid,
double balance)
throws Exception
{
boolean rtn=
false;
try{
if(balance<0
)
{
throw new Exception("余额数据异常"
);
}
//1加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver"
);
//2获得连接
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","test01","123456"
);
//3创建声明
Statement st=
conn.createStatement();
//4执行语句
String sql="update bank set ba_balance=‘"+balance+"‘where ba_card=‘"+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","test01","123456"
);
//3创建声明
Statement st=
conn.createStatement();
//4执行语句
String sql="select ba_card,ba_password from bank where ba_card=‘"+cardid+"‘ba_password=‘"+password+"‘"
;
ResultSet rs=
st.executeQuery(sql);
rtn=
rs.next();
//5释放资源
st.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
return rtn;
}
//验证登录
public boolean login1(String cardid,String password)
{
boolean rtn=
false;
try{
Class.forName("oracle.jdbc.driver.OracleDriver"
);
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","test01","123456"
);
//带有？占位符的语句
String sql="select * from bank where ba_card=? and ba_password=?"
;
//预编译声明
//优点：1执行效率高，2避免SQL注入
PreparedStatement ps=
conn.prepareStatement(sql);
//替换占位符
ps.setString(1
, cardid);
ps.setString(2
, password);
ResultSet rs=
ps.executeQuery();
//如果有数据就验证通过
rtn=
rs.next();
//数据库元数据
DatabaseMetaData dm=
conn.getMetaData();
System.out.println("getURL="+
dm.getURL());
System.out.println("getUserName="+
dm.getUserName());
System.out.println("getDatabaseProductName="+
dm.getDatabaseProductName());
//结果集的元数据
ResultSetMetaData rsd=
rs.getMetaData();
System.out.println("getColumnCount="+
rsd.getColumnCount());
System.out.println("getColumnName"+rsd.getColumnName(3
));
rs.close();
ps.close();
conn.close();
}catch(Exception e)
{
e.printStackTrace();
}
return rtn;
}
//转账
public boolean zhuanzhang(String cardid_out ,String cardid_in ,
double money)
{
boolean rtn=
false;
Connection conn=
null;
PreparedStatement ps=
null;
//
try {
Class.forName("oracle.jdbc.driver.OracleDriver"
);
conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","test01","123456"
);
//连接自动提交默认为true
//1.设置连接自动提交为false
conn.setAutoCommit(
false);
//转出账户
String sql="update bank set ba_balance=ba_balance-"+money+"where ba_card=?"
;
ps=
conn.prepareStatement(sql);
ps.setString(1
, cardid_out);
rtn=ps.executeUpdate()==1
;
//模拟发生异常
if(rtn)
{
throw new RuntimeException("网络中断"
);
}
//转入账户
sql="update bank set ba_balance=ba_balance+"+money+"where ba_card=?"
;
ps=
conn.prepareStatement(sql);
ps.setString(1
, cardid_in);
rtn=ps.executeUpdate()==1
;
//2提交事务
conn.commit();
} catch (Exception e) {
// 3.回滚事务
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
finally
{
try {
ps.close();
conn.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
return rtn;
}
//测试连接池
public void testC3P0()
{
//连接池的形式是DataSource
//构建连接池对象
ComboPooledDataSource cp=
new ComboPooledDataSource();
//配置连接池
try {
//设置驱动
cp.setDriverClass("oracle.jdbc.driver.OracleDriver"
);
//设置url
cp.setJdbcUrl("jdbc:oracle:thin:@localhost:1521:orcl"
);
//设置用户
cp.setUser("test01"
);
//设置密码
cp.setPassword("123456"
);
//设置连接最小数量
cp.setMinPoolSize(5
);
//设置连接最大数量
cp.setMaxPoolSize(20
);
//设置初始连接数量
cp.setInitialPoolSize(5
);
Connection conn=
cp.getConnection();
String cardid=(
int)(Math.random()*1000000)+""
;
String sql="insert into bank(ba_name,ba_shenfen,ba_card,ba_balance,ba_password)"+
"values(?,?,?,?,?)"
;
PreparedStatement ps=
conn.prepareStatement(sql);
ps.setString(1, "张三"
);
ps.setString(2, "123456789012345678"
);
ps.setString(3
, cardid);
ps.setDouble(4, 100
);
ps.setString(5, "123456"
);
ps.executeUpdate();
System.out.println("添加成功"
);
ps.close();
conn.close();
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
//测试连接池
//通过配置文件，构建连接池
//连接池的形式是DataSource
//构建连接池对象
ComboPooledDataSource cp=
new ComboPooledDataSource("helloc3p0"
);
public void test1C3P0()
{
//配置连接池
try {
Connection conn=
cp.getConnection();
String cardid=(
int)(Math.random()*1000000)+""
;
String sql="insert into bank(ba_name,ba_shenfen,ba_card,ba_balance,ba_password)"+
"values(?,?,?,?,?)"
;
PreparedStatement ps=
conn.prepareStatement(sql);
ps.setString(1, "李四"
);
ps.setString(2, "123456789012345678"
);
ps.setString(3
, cardid);
ps.setDouble(4, 100
);
ps.setString(5, "123456"
);
ps.executeUpdate();
System.out.println("添加成功"
);
ps.close();
conn.close();
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
//查询账户,获取账户信息
//返回实体类装载的数据
public bank getcard(String cardid)
{
bank ba=
null;
try {
Connection conn=
cp.getConnection();
String sql="select * from bank where ba_card=?"
;
PreparedStatement ps=
conn.prepareStatement(sql);
ps.setString(1
, cardid);
ResultSet rs=
ps.executeQuery();
if(rs.next())
{
//使用实体类装载数据
ba=
new bank();
ba.setBa_card(rs.getString("ba_card"
));
ba.setBa_name(rs.getString("ba_name"
));
ba.setBa_shenfen(rs.getString("ba_shenfen"
));
ba.setBa_balance(rs.getDouble("ba_balance"
));
ba.setBa_password(rs.getString("ba_password"
));
rs.close();
ps.close();
conn.close();
}
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return ba;
}
//查询所有记录
public List<bank>
getAll()
{
List<bank> li=
new ArrayList<>
();
try {
Connection conn=
cp.getConnection();
Statement st=
conn.createStatement();
ResultSet rs=st.executeQuery("select * from bank"
);
while(rs.next())
{
bank ba=
new bank();
ba.setBa_card(rs.getString("ba_card"
));
ba.setBa_name(rs.getString("ba_name"
));
ba.setBa_shenfen(rs.getString("ba_shenfen"
));
ba.setBa_balance(rs.getDouble("ba_balance"
));
ba.setBa_password(rs.getString("ba_password"
));
li.add(ba);
}
rs.close();
st.close();
conn.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return li;
}
}
测试：
package com.bank.abc;
import static org.junit.Assert.*;
import java.util.List;
import org.junit.Test;
public class testatm {
@Test
public void testInsert() {
//测试发卡
CardDAO cd=new CardDAO();
String cardid=cd.addCard("李凯", "220521199202025439", 0);
if(cardid !=null)
{
System.out.println("卡号"+cardid);
}
else
{
System.out.println("发卡失败");
}
}
@Test
public void testInsert1() {
CardDAO cd=new CardDAO();
try{
if(cd.updateBalance("064532", 100))
{
System.out.println("存款成功");
}else{
System.out.println("存款失败");
}
}catch(Exception e){
e.printStackTrace();
}
}
@Test
public void testInsert2() {
CardDAO cd=new CardDAO();
try{
if(cd.login(" 123453‘ or 1=1--", "321312"))
{
System.out.println("");
}
}catch(Exception e){
e.printStackTrace();
}
}
@Test
public void testInsert3() {
CardDAO cd=new CardDAO();
try{
if(cd.login1("362569", "123456"))
{
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
}catch(Exception e){
e.printStackTrace();
}
}
//测试转账
@Test
public void testInsert4() {
CardDAO cd=new CardDAO();
try{
if(cd.zhuanzhang("949806", "362569",5000))
{
System.out.println("转账成功");
}else{
System.out.println("转账失败");
}
}catch(Exception e){
e.printStackTrace();
}
}
//测试连接池
@Test
public void testInsert5() {
CardDAO cd=new CardDAO();
cd.testC3P0();
}
//测试连接池
@Test
public void testInsert6() {
CardDAO cd=new CardDAO();
cd.test1C3P0();
}
//测试获取卡信息
@Test
public void testInsert7() {
CardDAO cd=new CardDAO();
bank ba=cd.getcard("362569");
System.out.println(ba);
}
//测试获取所有卡信息
@Test
public void testInsert8() {
CardDAO cd=new CardDAO();
List<bank> li=cd.getAll();
for(bank ba:li)
{
System.out.println(ba);
}
}
}
Java针对数据库增删改查代码
标签：toc try 元数据 boolean query 返回 保存 rollback pac
