Java jdbc 操作数据库详解

JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用java语言编写的类和接口组成。

有了JDBC，向各种关系数据发送SQL语句就是一件很容易的事。换言之，有了JDBC API，就不必为访问Sybase数据库专门写一个程序，为访问Oracle数据库又专门写一个程序，或为访问Informix数据库又编写另一个程序等等，程序员只需用JDBC API写一个程序就够了，它可向相应数据库发送SQL调用。

/**
 * JDBC:文件驱动 
 * 1.引入jar包 ojdbc.jar 在工程的目录下创建一个lib文件夹用来存放jar包
 * 在ojdbc14.jar这个文件上右键==>build Path==> add to build Path 
 * 2.编写DB类 
 * a.加载驱动 
 * b.设置参数url user pwd 
 * c.连接数据库
 * d.编写sql语句
 * e.编译sql语句 
 * f.如果存在条件
 * 设置条件后执行sql语句，如果没有条件直接执行sql语句 g.如果是增删改 那么sql执行结束 如果是查询，遍历结果集 f.关闭数据库
 * 
 * 
 *
 */

二、初始化

1.配置连接：在引入了ojdbc14.jar包之后，我们需要使用Class.forName方法加载驱动，然后通过DriverManager.getConnection来获取连接

// 初始化
    public void init() {
        // 不同的数据库有不同的驱动
        String driverName = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@192.168.1.150:1521:XE";
        String user = "hr";
        String password = "123456";

        try {
            // 加载驱动
            Class.forName(driverName);
            // 设置 配置数据
            // 1.url(数据看服务器的ip地址 数据库服务端口号 数据库实例)
            // 2.user
            // 3.password
            conn = DriverManager.getConnection(url, user, password);
            // 开始连接数据库
            System.out.println("数据库连接成功..");
        } catch (ClassNotFoundException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }

    }

 2.初始化

Connection conn = null;

    public static void main(String[] args) {
        DB db = new DB();
        db.init();
}

三、操作

如果我们需要操作数据，就需要一个容器来保存它，这里我们可以用javabean来保存他们。

public class Student {

    private Integer id;
    private String name;
    private Integer age;
    private Integer agestart;
    private Integer ageend;
    public Integer getAgestart() {
        return agestart;
    }

    public void setAgestart(Integer agestart) {
        this.agestart = agestart;
    }

    public Integer getAgeend() {
        return ageend;
    }

    public void setAgeend(Integer ageend) {
        this.ageend = ageend;
    }

    public Student() {
        // TODO 自动生成的构造函数存根
    }

    public Student(Integer id, String name, Integer age) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

1.简单的查询

public List<Student> select() throws SQLException {

        List<Student> list = new ArrayList<Student>();
        // *查询
        // 创建sql语句
        String sqlString = "select * from student";
        // 编译sql语句
        // 执行查询
        pst = conn.prepareStatement(sqlString);
        rSet = pst.executeQuery();
        // 遍历结果
        Student student = null;
        while (rSet.next()) {
            // System.out.print(rSet.getInt(1) + "\t");
            // System.out.print(rSet.getString(2) + "\t");
            // System.out.print(rSet.getInt(3) + "\t");
            // System.out.print("\n");
            student = new Student();
            student.setId(rSet.getInt(1));
            student.setName(rSet.getString(2));
            student.setAge(rSet.getInt(3));
            list.add(student);

        }

        return list;
    }

List<Student> students = null;
students = db.select();
 for (int i = 0; i < students.size(); i++) {
             System.out.print(students.get(i).getId() + "\t");
             System.out.print(students.get(i).getName() + "\t");
             System.out.print(students.get(i).getAge());
             System.out.println();
             }

结果：

2.条件查询

public List<Student> selectByStu(int age) throws SQLException {
        List<Student> students = new ArrayList<Student>();
        String sql = "select * from student where sage=? ";

        pst = conn.prepareStatement(sql);
        // 设置条件(相对于条件查询)
        pst.setInt(1, age);
        rSet = pst.executeQuery();
        Student student = null;
        while (rSet.next()) {
            student = new Student();
            student.setId(rSet.getInt(1));
            student.setName(rSet.getString(2));
            student.setAge(rSet.getInt(3));
            students.add(student);
        }

        return students;
    }

students = db.selectByStu(20);
        for (int i = 0; i < students.size(); i++) {
             System.out.print(students.get(i).getId() + "\t");
             System.out.print(students.get(i).getName() + "\t");
             System.out.print(students.get(i).getAge());
             System.out.println();
             }

结果：

3.多个条件查询

public List<Student> selectByCondition(Student e) throws SQLException {
        List<Student> students = new ArrayList<Student>();
        String sql = "select * from student where sage>? and sage<? ";
        pst = conn.prepareStatement(sql);
        pst.setInt(1, e.getAgestart());
        pst.setInt(2, e.getAgeend());
        rSet = pst.executeQuery();
        Student student = null;
        while (rSet.next()) {
            student = new Student();
            student.setId(rSet.getInt(1));
            student.setName(rSet.getString(2));
            student.setAge(rSet.getInt(3));
            students.add(student);
        }
        return students;
    }

Student stu = new Student();
             stu.setAgestart(20);
             stu.setAgeend(22);
             students = db.selectByCondition(stu);

             for (int i = 0; i < students.size(); i++) {
             System.out.print(students.get(i).getId() + "\t");
             System.out.print(students.get(i).getName() + "\t");
             System.out.print(students.get(i).getAge());
             System.out.println();
             }

4.添加数据

public void save(Student student) throws SQLException {
        String sql = "insert into student values (?,?,?,null,null,null,null,null)";

        pst = conn.prepareStatement(sql);
        pst.setInt(1, student.getId());
        pst.setString(2, student.getName());
        pst.setInt(3, student.getAge());
        pst.executeUpdate();
        System.out.println("添加成功");
    }

5.更新数据

public void update(Student student) throws SQLException {
        String sql = "update student set sage=? where sid=?";

        pst = conn.prepareStatement(sql);
        pst.setInt(1, student.getAge());
        pst.setInt(2, student.getId());
        pst.executeUpdate();
        System.out.println("更新成功");
    }

6.删除数据

public void delete(Integer id) throws SQLException {
        String sql = "delete student where sid=?";

        pst = conn.prepareStatement(sql);
        pst.setInt(1, id);
        pst.executeUpdate();
        System.out.println("删除成功");
    }

7.分页

public List<Student> findByPage(int start, int end) throws SQLException {
        List<Student> students = new ArrayList<Student>();
        String sql = "select s_.* from(select  s.*,rownum rn from (select * from student order by sid) s where rownum<=?) s_  where rn>?";

        pst = conn.prepareStatement(sql);
        pst.setInt(1, end);
        pst.setInt(2, start);
        rSet = pst.executeQuery();
        Student student = null;
        while (rSet.next()) {
            student = new Student();
            student.setId(rSet.getInt(1));
            student.setName(rSet.getString(2));
            student.setAge(rSet.getInt(3));
            students.add(student);
        }
        return students;
    }

 四、笔记

package com.hgd.study.JDBC;

import java.net.CookieHandler;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DB {
    Connection conn = null;
    PreparedStatement pst;
    ResultSet rSet;

    public static void main(String[] args) {
        DB db = new DB();

        List<Student> students = null;
        try {
            db.initConn();
            //students = db.select();

            //students = db.selectByStu(20);

             Student stu = new Student();
             stu.setAgestart(20);
             stu.setAgeend(22);
             students = db.selectByCondition(stu);

             for (int i = 0; i < students.size(); i++) {
             System.out.print(students.get(i).getId() + "\t");
             System.out.print(students.get(i).getName() + "\t");
             System.out.print(students.get(i).getAge());
             System.out.println();
             }

            // Student stu = new Student(123, "李四", 23);
            // db.save(stu);

            // Student stu=new Student();
            // stu.setId(10003);
            // stu.setAge(23);
            // db.update(stu);

            // db.delete(123);

            /*
             * 事物： 1.原子性：每一个事物都是一个单独的操作 2.一致性：事物中的所有操作，只要成功全部成功，一旦失败全部失败
             * 3.隔离性：事物中的操作之间没不会产生影响他们都是一个独立的操作 4.持久性：事物一旦成功将被永久写入数据库
             */
            // conn.setAutoCommit(false);// 设置自动事物为false
            // conn.setSavepoint();// 设置事物的回滚点
            // conn.rollback();//事物回滚
            // conn.commit();//事物提交
            // db.setAutoCommit(false);
            // db.setSavepoint();
            // Student stu = new Student();
            // stu.setId(10003);
            // stu.setAge(0);
            // db.update(stu);
            // if (stu.getAge() <= 0) {
            // db.rollback();
            // System.out.println("取消了操作");
            // } else {
            // db.commit();
            // }

        } catch (Exception e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        } finally {
            try {
                db.close();
            } catch (Exception e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
        }
    }

    // 初始化
    public void init() {
        // 不同的数据库有不同的驱动
        String driverName = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@192.168.1.150:1521:XE";
        String user = "hr";
        String password = "123456";

        try {
            // 加载驱动
            Class.forName(driverName);
            // 设置 配置数据
            // 1.url(数据看服务器的ip地址 数据库服务端口号 数据库实例)
            // 2.user
            // 3.password
            conn = DriverManager.getConnection(url, user, password);
            // 开始连接数据库
            System.out.println("数据库连接成功..");
        } catch (ClassNotFoundException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }

    }

    public void initConn() throws Exception {
        if (conn == null || conn.isClosed()) {
            this.init();
        }
    }

    public void close() throws Exception {
        if (conn != null && !conn.isClosed()) {
            conn.close();
            conn = null;

        }
    }

    public void setAutoCommit(boolean fals) throws Exception {
        if (conn != null && !conn.isClosed()) {
            conn.setAutoCommit(fals);
        }
    }

    public void setSavepoint() throws Exception {
        if (conn != null && !conn.isClosed()) {
            conn.setSavepoint();
        }
    }

    public void rollback() throws Exception {
        if (conn != null && !conn.isClosed()) {
            conn.rollback();
        }
    }

    public void commit() throws Exception {
        if (conn != null && !conn.isClosed()) {
            conn.commit();
        }
    }

    // 简单查询
    public List<Student> select() throws SQLException {

        List<Student> list = new ArrayList<Student>();
        // *查询
        // 创建sql语句
        String sqlString = "select * from student";
        // 编译sql语句
        // 执行查询
        pst = conn.prepareStatement(sqlString);
        rSet = pst.executeQuery();
        // 遍历结果
        Student student = null;
        while (rSet.next()) {
            // System.out.print(rSet.getInt(1) + "\t");
            // System.out.print(rSet.getString(2) + "\t");
            // System.out.print(rSet.getInt(3) + "\t");
            // System.out.print("\n");
            student = new Student();
            student.setId(rSet.getInt(1));
            student.setName(rSet.getString(2));
            student.setAge(rSet.getInt(3));
            list.add(student);

        }

        return list;
    }

    // 条件查询
    public List<Student> selectByStu(int age) throws SQLException {
        List<Student> students = new ArrayList<Student>();
        String sql = "select * from student where sage=? ";

        pst = conn.prepareStatement(sql);
        // 设置条件(相对于条件查询)
        pst.setInt(1, age);
        rSet = pst.executeQuery();
        Student student = null;
        while (rSet.next()) {
            student = new Student();
            student.setId(rSet.getInt(1));
            student.setName(rSet.getString(2));
            student.setAge(rSet.getInt(3));
            students.add(student);
        }

        return students;
    }

    // 多个条件值查询
    public List<Student> selectByCondition(Student e) throws SQLException {
        List<Student> students = new ArrayList<Student>();
        String sql = "select * from student where sage>? and sage<? ";
        pst = conn.prepareStatement(sql);
        pst.setInt(1, e.getAgestart());
        pst.setInt(2, e.getAgeend());
        rSet = pst.executeQuery();
        Student student = null;
        while (rSet.next()) {
            student = new Student();
            student.setId(rSet.getInt(1));
            student.setName(rSet.getString(2));
            student.setAge(rSet.getInt(3));
            students.add(student);
        }
        return students;
    }

    // 添加数据
    public void save(Student student) throws SQLException {
        String sql = "insert into student values (?,?,?,null,null,null,null,null)";

        pst = conn.prepareStatement(sql);
        pst.setInt(1, student.getId());
        pst.setString(2, student.getName());
        pst.setInt(3, student.getAge());
        pst.executeUpdate();
        System.out.println("添加成功");
    }

    // 更新数据
    public void update(Student student) throws SQLException {
        String sql = "update student set sage=? where sid=?";

        pst = conn.prepareStatement(sql);
        pst.setInt(1, student.getAge());
        pst.setInt(2, student.getId());
        pst.executeUpdate();
        System.out.println("更新成功");
    }

    // 删除数据
    public void delete(Integer id) throws SQLException {
        String sql = "delete student where sid=?";

        pst = conn.prepareStatement(sql);
        pst.setInt(1, id);
        pst.executeUpdate();
        System.out.println("删除成功");
    }

    // 分页
    public List<Student> findByPage(int start, int end) throws SQLException {
        List<Student> students = new ArrayList<Student>();
        String sql = "select s_.* from(select  s.*,rownum rn from (select * from student order by sid) s where rownum<=?) s_  where rn>?";

        pst = conn.prepareStatement(sql);
        pst.setInt(1, end);
        pst.setInt(2, start);
        rSet = pst.executeQuery();
        Student student = null;
        while (rSet.next()) {
            student = new Student();
            student.setId(rSet.getInt(1));
            student.setName(rSet.getString(2));
            student.setAge(rSet.getInt(3));
            students.add(student);
        }
        return students;
    }

}