Java学习笔记之:Java JDBC
转自: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; }
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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; } }
posted on 2018-05-09 14:18 chengjunde 阅读(115) 评论(0) 编辑 收藏 举报