Mysql数据库连接———JDBC，Java代码

来源：互联网发布：高斯滤波算法matlab 编辑：程序博客网时间：2024/05/16 08:43

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;

}

阅读全文

0 0