原文地址https://www.cnblogs.com/huguodong/p/5910859.html

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;

    }

}

最新文章

  1. 移动WEB开发之viewport
  2. iMetro
  3. win7 64的系统安装。net4.0总是提示安装未成功
  4. java 异常处理 Throwable Error 和Exception
  5. Linux下软件安装，卸载，管理
  6. HDU 4287 Intelligent IME（string，map，stl,make_pair）
  7. PCB阻抗调节
  8. 高性能WEB开发之Web性能测试工具推荐
  9. Linux定时执行任务命令概述：at和crontab
  10. C语言 cgi(2)
  11. Installshield脚本拷贝文件常见问题汇总
  12. hellocharts-android开源图表库（效果非常好）
  13. ex2:逻辑回归及正则条件下的练习
  14. jmeter奇淫妙计之遍历sql多列结果集
  15. C++向量 vector动态数组
  16. libopencv_highgui.so.2.4.9：对‘TIFFReadRGBAStrip@LIBTIFF_4.0’未定义的引用
  17. C++反转单链表
  18. str操作方法
  19. mysql中,创建包含json数据类型的表?创建json表时候的注意事项?查询json字段中某个key的值?
  20. ubuntu下常用操作

热门文章

  1. Android 线程与主线程
  2. Sencha Touch 扩展集合
  3. mysql架构图
  4. C# IO流的操作（一）
  5. GPU对数据的操作不可累加
  6. SDWebImage第三方库使用注意的一些问题
  7. Docker 学习应用篇之二： Docker的介绍和安装
  8. HDU 1686 - Oulipo - [KMP模板题]
  9. tkinter 进度条
  10. 机器学习TensorFlow安装经过摘要