java连接oracle数据库实现增删改查

java连接oracle数据库实现增删改查

实现效果如图：

数据库建表语句如下：

create table book                                                        --创建图书表
(id int primary key not null,
isbn int,
bookName varchar2(30),
bookClassify varchar2(30),
authorId int,
publisherId int,
wordNumber int,
issueYear int,
price int);

java连接数据库实现增删改查代码如下：

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();
    }
}