1.数据库部分
创建数据表customer(客户)、deposite(存款)、bank(银行),表结构如下:
Create Table |
CREATE TABLE `bank` ( `b_id` char(5) NOT NULL, `bank_name` char(30) NOT NULL, `bankcase` char(30) DEFAULT NULL, PRIMARY KEY (`b_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
Create Table |
CREATE TABLE `customer` ( `c_id` char(6) NOT NULL, `name` varchar(30) NOT NULL, `location` varchar(30) DEFAULT NULL, `salary` decimal(8,2) DEFAULT NULL, PRIMARY KEY (`c_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
Create Table |
CREATE TABLE `deposite` ( `d_id` int(10) NOT NULL AUTO_INCREMENT, `c_id` char(6) DEFAULT NULL, `b_id` char(5) DEFAULT NULL, `dep_date` date DEFAULT NULL, `amount` decimal(8,2) DEFAULT NULL, PRIMARY KEY (`d_id`), KEY `FK_c_id` (`c_id`), CONSTRAINT `FK_c_id` FOREIGN KEY (`c_id`) REFERENCES `customer` (`c_id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 |
2.Java部分
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
public class TestBank {
public static void main(Stringargs[]) {
//getConn();
//insertBank();
//selectCustomer();
//delete();
update();
}
private static Connection getConn() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/bank";
String username = "root";
String password = "root";
Connection conn = null;
try {
Class.forName(driver);//classLoader,加载对应驱动
conn = (Connection) DriverManager.getConnection(url,username,password);
System.out.println("SUCCED");
} catch (ClassNotFoundExceptione) {
e.printStackTrace();
} catch (SQLExceptione) {
e.printStackTrace();
}
return conn;
}
private static int insertBank() {
Connection conn = getConn();
int i = 0;
String sql = "insert into bank (b_id,bank_name) values(?,?)";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1,"A0005");
pstmt.setString(2,"石嘴山银行");
i = pstmt.executeUpdate();
System.out.println("插入了: " +i+"条!");
pstmt.close();
conn.close();
} catch (SQLExceptione) {
e.printStackTrace();
}
return i;
}
private static StringselectCustomer() {
Connection conn = getConn();
String sql = "select * from customer";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement)conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
int col =rs.getMetaData().getColumnCount();
System.out.println("============================");
while (rs.next()) {
for (int i = 1;i <=col; i++) {
System.out.print(rs.getString(i) +"\t");
if ((i == 2) && (rs.getString(i).length() < 8)) {
System.out.print("\t");
}
}
System.out.println("");
}
System.out.println("============================");
pstmt.close();
conn.close();
} catch (SQLExceptione) {
e.printStackTrace();
}
return null;
}
//delete
private static int delete() {
Connection conn = getConn();
int i = 0;//统计条数
String sql = "delete from bank where b_id='A0005'";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("删除了: " +i+"条!"+"删除语句为:"+sql);
pstmt.close();
conn.close();
} catch (SQLExceptione) {
e.printStackTrace();
}
return i;
}
//更新customer的数据
private static int update() {
Connection conn = getConn();
int i = 0;
String sql = "update customer set salary='121452.00' where c_id='101004'";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("更新了" +i+"条!"+"更新语句为:"+sql);
pstmt.close();
conn.close();
} catch (SQLExceptione) {
e.printStackTrace();
}
return i;
}
}