JDBC-1

JDBC是java连接数据库的实体类

JDBC的创建步骤

//1.加载驱动
Class.forName("oracle.jdbc.OracleDriver");
//2.建立连接
Connection conn =DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hr"."hr");
//3.创建sql语句
String sql ="select * from employees";//查
//4.创建执行器对象
PreparedStatement pstm = conn.prepareStatement(sql);
//5.执行
int i=pstm.executeUpdate();//DML语句 此方法返回该语句执行影响了几行 返回值为int类型
ResultSet rs=pstm.executeQuery();//DQL数据库查询语言 返回值为ResultSet的结果集
//关闭资源 遵循后开先关原则
rs.close();
pstm.close();
conn.close();

 sql语句

--任务一：
--新建一个图书表t_book,包含book_id,book_name,author(作者),price(价格)
--新建一个图书序列seq_book
--使用jdbc完成图书表的增 删 改 查
create table t_book(
       book_id number(10) primary key,
       book_name varchar2(50) not null,
       author varchar(30),
       price number(20)   
);
create sequence seq_book start with 1 increment by 1;
select * from t_book;
delete from t_book;
insert into t_book values(seq_book.nextval,'平凡的世界','路遥',50);--增
delete from t_book where book_name='平凡的世界';--删
update t_book set author='余秋雨' where book_id=4;--改
select * from t_book where book_id=3;--查

java代码

package baizhi.homework;

import java.io.Closeable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test1 {
    public static void main(String[] args) throws Exception {
        //加载驱动
        Class.forName("oracle.jdbc.OracleDriver");
        //创建数据库连接对象
        Connection conn =DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hr","root");
        //书写sql语句
//        String sql = "insert into t_book values(seq_book.nextval,'平凡的世界','路遥',50)";//增
//        String sql2 = "delete from t_book where book_name='平凡的世界'";//删
//        String sql3 = "update t_book set author='余秋雨' where book_id=4";//改
//        String sql4 = "select * from t_book where book_id=3";//查
        //创建sql窗口
        //增
//        String sql = "insert into t_book values(seq_book.nextval,?,?,?)";//增
//        PreparedStatement pstm = conn.prepareStatement(sql);
//        //pstm.setInt(1, 1);
//        pstm.setString(1,"平凡的世界");
//        pstm.setString(2, "路遥");
//        pstm.setInt(3, 50);
//        int i = pstm.executeUpdate();
//        System.out.println(i+"增加成功");
//        pstm.close();
//        conn.close();
        
    //    删
//        String sql2 = "delete from t_book where book_id=?";//删
//        PreparedStatement pstm2 = conn.prepareStatement(sql2);
//        pstm2.setInt(1, 4);
//        int j=pstm2.executeUpdate();
//        System.out.println(j+"删除成功");
//        pstm2.close();
//        conn.close();
//        //改
//        String sql3 = "update t_book set author=? where book_id=?";//改
//        PreparedStatement pstm3 = conn.prepareStatement(sql3);
//        pstm3.setString(1,"余秋雨" );
//        pstm3.setInt(2, 1);
//        int g=pstm3.executeUpdate();
//        System.out.println(g+"修改成功");
//        pstm3.close();
//        conn.close();
//        //查
        String sql4 = "select * from t_book where book_id =?";//查
        PreparedStatement pstm4 = conn.prepareStatement(sql4);
        pstm4.setInt(1,1);
        ResultSet rs= pstm4.executeQuery();
        while(rs.next()) {
            int a = rs.getInt("book_id");
            String b = rs.getString(2);
            String c = rs.getString(3);
            int d = rs.getInt(4);
            System.out.println("编号："+a+"  书名："+b+"   作者名："+c+"   价格："+d);
        }
        pstm4.close();
        conn.close();
    }
}