package com;
import java.sql.*;
import java.util.Scanner;
public class testOracle {
static final String JDBC_DRIVER = "oracle.jdbc.OracleDriver";
static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl";
// 数据库的用户名与密码，需要根据自己的设置
static final String USER = "ceshi";
static final String PASS = "13579";
Connection conn = null;
public void find () {
try {
Class.forName(JDBC_DRIVER);
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
// 打开链接
System.out.println("连接数据库...");
System.out.println("查询数据如下：");
try {
conn = DriverManager.getConnection(DB_URL, USER, PASS);
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
String sql;
sql = "SELECT * FROM book";
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery(sql);
// 展开结果集数据库
while (rs.next()) {
// 通过字段检索
int id = rs.getInt("id");
int isbn = rs.getInt("isbn");
String bookName = rs.getString("bookName");
String bookClassify = rs.getString("bookClassify");
int authorId = rs.getInt("authorId");
int publisherId = rs.getInt("publisherId");
int wordNumber = rs.getInt("wordNumber");
int issueYear = rs.getInt("issueYear");
int price = rs.getInt("price");
System.out.println("ID: \t" + "编号: \t\t" + "图书名: \t\t" + "图书分类: \t\t" + "作者ID:\t \t" + "出版社ID: \t\t" + "字数: \t\t" + " 发行年份: \t"+ " 价格: \t");
System.out.print(id + "\t\t");
System.out.print(isbn + "\t\t\t");
System.out.print(bookName + "\t\t");
System.out.print(bookClassify + "\t\t\t\t");
System.out.print(authorId + "\t\t\t");
System.out.print(publisherId + "\t\t\t\t");
System.out.print(wordNumber + "\t\t\t");
System.out.print(issueYear + "\t\t");
System.out.println(price + "\t\t");
System.out.println("--------------------------------------------------------------------------------------------------------------------------------");
System.out.println("查询成功");
System.out.print("\n");
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
}
}
public void alter () {
try {
Class.forName(JDBC_DRIVER);
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
// 打开链接
System.out.println("连接数据库...");
System.out.println("正在修改数据...");
try {
conn = DriverManager.getConnection(DB_URL, USER, PASS);
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
System.out.print("输入修改图书id:");
Scanner al = new Scanner(System.in);
int id_alt = al.nextInt();
int isbn_alt, publisherId_alt, authorId_alt, wordNumber_alt, issueYear_alt,price_alt;
String bookName_alt, bookClassify_alt;
System.out.println("isbn编码:");
isbn_alt = al.nextInt();
al.nextLine(); //解决nextInt();和nextLine();连用问题
System.out.println("图书名:");
bookName_alt = al.nextLine();
System.out.println("图书分类:");
bookClassify_alt = al.nextLine();
System.out.println("作者id:");
authorId_alt = al.nextInt();
System.out.println(" 出版社id:");
publisherId_alt = al.nextInt();
System.out.println("字数:");
wordNumber_alt = al.nextInt();
System.out.println("发行年份:");
issueYear_alt = al.nextInt();
System.out.println("价格:");
price_alt = al.nextInt();
al.close();
String sqlupd = "update book set id=?, isbn=?,bookName=?,bookClassify=?, authorId=?,publisherId=?,wordNumber=?,issueYear=? ,price=? where id=? ";
PreparedStatement ststupd = conn.prepareStatement(sqlupd);
ststupd.setInt(1, id_alt);//索引参数1代表着sql中的第一个？号，也就是条件是id为4
ststupd.setInt(2, isbn_alt);//ISBN编号
ststupd.setString(3, bookName_alt);//图书名
ststupd.setString(4, bookClassify_alt);//图书分类
ststupd.setInt(5, authorId_alt);//作者ID
ststupd.setInt(6, publisherId_alt);//出版社ID
ststupd.setInt(7, wordNumber_alt);//字数
ststupd.setInt(8, issueYear_alt);//发行年份
ststupd.setInt(9, price_alt);//价格
ststupd.setInt(10, id_alt);//id
ststupd.executeUpdate();
System.out.println("修改成功");
ststupd.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
}
}
public void add () {
try {
Class.forName(JDBC_DRIVER);
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
// 打开链接
System.out.println("连接数据库...");
System.out.println("正在增加记录...");
try {
conn = DriverManager.getConnection(DB_URL, USER, PASS);
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
String sqladd = "insert into book values (?,?,?,?,?,?,?,?,?)";
PreparedStatement statadd = conn.prepareStatement(sqladd);
Scanner ad = new Scanner(System.in);
int id_add, isbn_add, publisherId_add, authorId_add, wordNumber_add, issueYear_add,price_add;
String bookName_add, bookClassify_add;
System.out.println("输入要增加的完整记录:");
System.out.println("id:");
id_add = ad.nextInt();
System.out.println("isbn编码:");
isbn_add = ad.nextInt();
ad.nextLine(); //解决nextInt();和nextLine();连用问题
System.out.println("图书名:");
bookName_add = ad.nextLine();
System.out.println("图书分类:");
bookClassify_add = ad.nextLine();
System.out.println("作者id:");
authorId_add = ad.nextInt();
System.out.println(" 出版社id:");
publisherId_add = ad.nextInt();
System.out.println("字数:");
wordNumber_add = ad.nextInt();
System.out.println("发行年份:");
issueYear_add = ad.nextInt();
System.out.println("价格:");
price_add = ad.nextInt();
ad.close();
statadd.setInt(1, id_add);//索引参数1代表着sql中的第一个？号，也就是条件是id为4
statadd.setInt(2, isbn_add);//ISBN编号
statadd.setString(3, bookName_add);//图书名
statadd.setString(4, bookClassify_add);//图书分类
statadd.setInt(5, authorId_add);//作者ID
statadd.setInt(6, publisherId_add);//出版社ID
statadd.setInt(7, wordNumber_add);//字数
statadd.setInt(8, issueYear_add);//发行年份
statadd.setInt(9, price_add);//价格
statadd.executeUpdate();
System.out.println("增加成功");
statadd.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
}
}
public void delect () {
try {
Class.forName(JDBC_DRIVER);
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
// 打开链接
System.out.println("连接数据库...");
try {
conn = DriverManager.getConnection(DB_URL, USER, PASS);
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
String sqldel = "delete from book where id=?";
PreparedStatement ststdel = conn.prepareStatement(sqldel);
Scanner del = new Scanner(System.in);
int id_del;
System.out.println("请输入要删除的图书id:");
id_del = del.nextInt();
ststdel.setInt(1, id_del);
ststdel.executeUpdate();
System.out.println("删除成功");
ststdel.close();
conn.close();
} catch (SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
}
}
public static void main(String[] args) {
System.out.println("1.查询\t2.修改\t3.删除\t4.增加");
Scanner in=new Scanner(System.in);
System.out.print("请输入功能数字：");
int input=in.nextInt();
testOracle test=new testOracle();
if (input == 1) {
test.find();
} else if (input == 2) {
test.alter();
} else if (input == 3) {
test.delect();
} else if (input == 4) {
test.add();
} else {
System.out.print("输入错误！");
}
in.close();
}
}
