一、介绍
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;
} }