前提須在MySQL中創(chuàng)建一個(gè)名為"teach"的數(shù)據(jù)庫(kù)抑钟，且在其中創(chuàng)建一個(gè)名為"雇員"的表
CREATE DATABASE teach;
USE teach;
CREATE TABLE 雇員(
id INT(4) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
work VARCHAR(255) ,
date DATETIME,
salary INT(11),
dname VARCHAR(255)
);
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
public class JDBC {
//定義MySQL的數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序
private static final String DBDRIVER = "com.mysql.jdbc.Driver";
//定義MySQL的數(shù)據(jù)庫(kù)的連接地址
private static final String DBURL = "jdbc:mysql://localhost:3306/teach";
//MySQL數(shù)據(jù)庫(kù)的連接用戶名
private static final String DBUSER = "root";
//MySQL數(shù)據(jù)庫(kù)的連接密碼
private static final String DBPASS = "mysqladmin";
public static void main(String[] args) throws Exception {
//數(shù)據(jù)庫(kù)連接
Connection conn = null;
//加載驅(qū)動(dòng)程序
Class.forName(DBDRIVER);
//使用用戶名和密碼連接數(shù)據(jù)庫(kù)
conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
//數(shù)據(jù)庫(kù)操作
PreparedStatement pstmt = null;
//進(jìn)行功能判斷
System.out.print("請(qǐng)選擇功能:I,U,D,S: ");
Scanner in = new Scanner(System.in);
String getapp = in.next();
char apply = getapp.charAt(0);
while (apply == 'I' || apply == 'D' || apply == 'S' || apply == 'U') {
//插入數(shù)據(jù)庫(kù)信息
if (apply == 'I') {
//編寫(xiě)預(yù)處理sql
String sql = "INSERT INTO 雇員(id,name,work,date,salary,dname) VALUES(?,?,?,?,?,?)";
//實(shí)例化prepareStatement對(duì)象
pstmt = conn.prepareStatement(sql);
//判斷多次是否多次插入
char apply2 = 'y';
int num = 0;
while (apply2 == 'y' || apply2 == 'Y') {
//依次輸入需插入的列表項(xiàng)
num++;
System.out.println("請(qǐng)輸入第" + num + "條數(shù)據(jù): ");
Scanner in2 = new Scanner(System.in);
int id = in2.nextInt();
String name = in2.next();
String work = in2.next();
String str = in2.next();
//字符串與java.util.Date與java.sql.Date之間的轉(zhuǎn)換
java.util.Date temp = new SimpleDateFormat("yyyy-MM-dd").parse(str);
java.sql.Date date = new java.sql.Date(temp.getTime());
int salary = in2.nextInt();
String dname = in2.next();
//設(shè)置預(yù)處理sql語(yǔ)句中"?"的內(nèi)容
pstmt.setInt(1, id);
pstmt.setString(2, name);
pstmt.setString(3, work);
pstmt.setDate(4, date);
pstmt.setInt(5, salary);
pstmt.setString(6, dname);
//加入批處理等待執(zhí)行
pstmt.addBatch();
System.out.print("第" + num + "條信息輸入完畢,是否繼續(xù):y or n拯坟？");
Scanner in3 = new Scanner(System.in);
String getapp2 = in3.next();
apply2 = getapp2.charAt(0);
}
try {
//批量執(zhí)行
int temp[] = pstmt.executeBatch();
//提交事務(wù)
conn.commit();
} catch (Exception e) {
try {
//如果出現(xiàn)異常,則之前輸入的sql將不執(zhí)行磅叛，事務(wù)回滾
conn.rollback();
} catch (Exception ex) {
}
pstmt.close();
}
//結(jié)束操作
pstmt.close();
}
//更新數(shù)據(jù)庫(kù)信息
if (apply == 'U') {
//結(jié)果集對(duì)象
ResultSet rs1 = null;
//預(yù)處理sql語(yǔ)句
String sql = "SELECT id,name,work,date,salary,dname FROM 雇員 WHERE id=?";
//實(shí)例化對(duì)象
pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//進(jìn)行重判斷是否再次執(zhí)行更新其他行
char apply2 = 'y';
while (apply2 == 'y' || apply == 'Y') {
//輸入更新的id
System.out.print("請(qǐng)輸入想要更新的id: ");
Scanner in2 = new Scanner(System.in);
int id = in2.nextInt();
//設(shè)置"？"內(nèi)容
pstmt.setInt(1, id);
//實(shí)例化ResultSet對(duì)象
rs1 = pstmt.executeQuery();
//移動(dòng)到最后一行
rs1.last();
//進(jìn)行重判斷是否再次執(zhí)行更新其他列
char apply3 = 'y';
while (apply3 == 'y' || apply3 == 'Y') {
System.out.print("請(qǐng)輸入想要更新的列名稱(chēng): ");
Scanner in4 = new Scanner(System.in);
String ROW = in4.next();
System.out.print("請(qǐng)輸入想要更新的列的內(nèi)容: ");
Scanner in5 = new Scanner(System.in);
String text = in5.next();
if (ROW == "date") {
java.util.Date temp = new SimpleDateFormat("yyyy-MM-dd").parse(text);
java.sql.Date date = new java.sql.Date(temp.getTime());
//設(shè)置更新內(nèi)容
rs1.updateDate(ROW, date);
} else if (ROW == "salary") {
int inttext = Integer.parseInt(text);
rs1.updateInt(ROW, inttext);
} else {
rs1.updateString(ROW, text);
}
//更新數(shù)據(jù)
rs1.updateRow();
System.out.print("是否想要繼續(xù)更新該id的數(shù)據(jù)慷丽？y or n: ");
Scanner in7 = new Scanner(System.in);
String getapp2 = in7.next();
apply3 = getapp2.charAt(0);
}
System.out.print("是否想要繼續(xù)更新其他id的數(shù)據(jù)？y or n: ");
Scanner in6 = new Scanner(System.in);
String getapp5 = in6.next();
apply2 = getapp5.charAt(0);
}
//關(guān)閉結(jié)果集
rs1.close();
pstmt.close();
}
//刪除數(shù)據(jù)庫(kù)信息
if (apply == 'D') {
String sql = "DELETE FROM 雇員 WHERE id=?";
pstmt = conn.prepareStatement(sql);
char apply2 = 'y';
while (apply2 == 'y' || apply == 'Y') {
System.out.print("請(qǐng)輸入想要?jiǎng)h除的id: ");
Scanner in2 = new Scanner(System.in);
int id = in2.nextInt();
pstmt.setInt(1, id);
System.out.print("是否想要繼續(xù)刪除？y or n: ");
Scanner in3 = new Scanner(System.in);
String getapp2 = in3.next();
apply2 = getapp2.charAt(0);
}
pstmt.executeUpdate();
}
//查詢(xún)數(shù)據(jù)庫(kù)
if (apply == 'S') {
ResultSet rs = null;
String sql = "SELECT * FROM 雇員";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
String work = rs.getString(3);
java.util.Date date = rs.getDate(4);
int salary = rs.getInt(5);
String dname = rs.getString(6);
System.out.print("ID: " + id + "; ");
System.out.print("NAME: " + name + "; ");
System.out.print("WORK: " + work + "; ");
System.out.print("DATE: " + date + "; ");
System.out.print("SALARY: " + salary + "; ");
System.out.println("DNAME: " + dname + "; ");
}
rs.close();
pstmt.close();
}
System.out.print("是否想要選擇其他功能:I,U,D,S or N? ");
Scanner in4 = new Scanner(System.in);
String getapp3 = in4.next();
apply = getapp3.charAt(0);
}
//數(shù)據(jù)庫(kù)關(guān)閉
conn.close();
}
}
