前提须在MySQL中创建一个名为"teach"的数据库，且在其中创建一个名为"雇员"的表
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的数据库驱动程序
private static final String DBDRIVER = "com.mysql.jdbc.Driver";
//定义MySQL的数据库的连接地址
private static final String DBURL = "jdbc:mysql://localhost:3306/teach";
//MySQL数据库的连接用户名
private static final String DBUSER = "root";
//MySQL数据库的连接密码
private static final String DBPASS = "mysqladmin";
public static void main(String[] args) throws Exception {
//数据库连接
Connection conn = null;
//加载驱动程序
Class.forName(DBDRIVER);
//使用用户名和密码连接数据库
conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
//数据库操作
PreparedStatement pstmt = null;
//进行功能判断
System.out.print("请选择功能: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') {
//插入数据库信息
if (apply == 'I') {
//编写预处理sql
String sql = "INSERT INTO 雇员(id,name,work,date,salary,dname) VALUES(?,?,?,?,?,?)";
//实例化prepareStatement对象
pstmt = conn.prepareStatement(sql);
//判断多次是否多次插入
char apply2 = 'y';
int num = 0;
while (apply2 == 'y' || apply2 == 'Y') {
//依次输入需插入的列表项
num++;
System.out.println("请输入第" + num + "条数据: ");
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之间的转换
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();
//设置预处理sql语句中"?"的内容
pstmt.setInt(1, id);
pstmt.setString(2, name);
pstmt.setString(3, work);
pstmt.setDate(4, date);
pstmt.setInt(5, salary);
pstmt.setString(6, dname);
//加入批处理等待执行
pstmt.addBatch();
System.out.print("第" + num + "条信息输入完毕,是否继续:y or n？");
Scanner in3 = new Scanner(System.in);
String getapp2 = in3.next();
apply2 = getapp2.charAt(0);
}
try {
//批量执行
int temp[] = pstmt.executeBatch();
//提交事务
conn.commit();
} catch (Exception e) {
try {
//如果出现异常,则之前输入的sql将不执行，事务回滚
conn.rollback();
} catch (Exception ex) {
}
pstmt.close();
}
//结束操作
pstmt.close();
}
//更新数据库信息
if (apply == 'U') {
//结果集对象
ResultSet rs1 = null;
//预处理sql语句
String sql = "SELECT id,name,work,date,salary,dname FROM 雇员 WHERE id=?";
//实例化对象
pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//进行重判断是否再次执行更新其他行
char apply2 = 'y';
while (apply2 == 'y' || apply == 'Y') {
//输入更新的id
System.out.print("请输入想要更新的id: ");
Scanner in2 = new Scanner(System.in);
int id = in2.nextInt();
//设置"？"内容
pstmt.setInt(1, id);
//实例化ResultSet对象
rs1 = pstmt.executeQuery();
//移动到最后一行
rs1.last();
//进行重判断是否再次执行更新其他列
char apply3 = 'y';
while (apply3 == 'y' || apply3 == 'Y') {
System.out.print("请输入想要更新的列名称: ");
Scanner in4 = new Scanner(System.in);
String ROW = in4.next();
System.out.print("请输入想要更新的列的内容: ");
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());
//设置更新内容
rs1.updateDate(ROW, date);
} else if (ROW == "salary") {
int inttext = Integer.parseInt(text);
rs1.updateInt(ROW, inttext);
} else {
rs1.updateString(ROW, text);
}
//更新数据
rs1.updateRow();
System.out.print("是否想要继续更新该id的数据？y or n: ");
Scanner in7 = new Scanner(System.in);
String getapp2 = in7.next();
apply3 = getapp2.charAt(0);
}
System.out.print("是否想要继续更新其他id的数据？y or n: ");
Scanner in6 = new Scanner(System.in);
String getapp5 = in6.next();
apply2 = getapp5.charAt(0);
}
//关闭结果集
rs1.close();
pstmt.close();
}
//删除数据库信息
if (apply == 'D') {
String sql = "DELETE FROM 雇员 WHERE id=?";
pstmt = conn.prepareStatement(sql);
char apply2 = 'y';
while (apply2 == 'y' || apply == 'Y') {
System.out.print("请输入想要删除的id: ");
Scanner in2 = new Scanner(System.in);
int id = in2.nextInt();
pstmt.setInt(1, id);
System.out.print("是否想要继续删除？y or n: ");
Scanner in3 = new Scanner(System.in);
String getapp2 = in3.next();
apply2 = getapp2.charAt(0);
}
pstmt.executeUpdate();
}
//查询数据库
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);
}
//数据库关闭
conn.close();
}
}
网友评论