oracle学习笔记(六) JDBC使用

JDBC使用

1. 导包

直接使用IDEA导入依赖包即可
新建一个lib,把jar包放在这里

2. 加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

3. 创建Connection对象

连接数据库,从而获得Connection对象

String url = "jdbc:oracle:thin:@localhost:1521:ORCL";//具体的实例名可以查看自己的服务管理,有个
Connection conn = DriverManager.getConnection(url,"scott","tiger");//后面的两个参数分别为连接数据库的用户名和密码

PS:查看实例名

  1. 开始->运行->输入services.msc
  2. 找到服务
  3. 后面的就是我们的oracle数据库的实例名

4. 创建Statement对象

Statement stmt = conn.createStatement();
  • Statement: 普通语句类型,只能执行一条完整的语句
  • PreparedStatement: 预编译语句,它支持占位符“?”, 如:select * from emp WHERE ename LIKE ? and sal>?
  • CallableStatement: 支持存储过程的调用

PreparedStatementCallableStatement都是继承Statement,比Statement要强大
补充链接:
PreparedStatement的使用
CallableStatement的使用(待补充)

5. 执行SQL语句

//执行查询语句,会返回有个结果集ResultSet
ResultSet rs = stmt.executeQuery("select * from student");

6. 使用ResultSet获取数据库数据

//遍历结果集, 每次调用next方法时会使结集的游标后移,最初游标位于第一行之前
//Student累是我们写的一个bean类,就是把数据库中的每一条数据当作为一个对象,每一列都当作为Student类的属性
while (rs.next()){ 
	//第循环一次封装一条记录
	Student student = new Student();
	student.setNum(rs.getInt("num"));
	student.setName(rs.getString("name"));
	//获取其它字段rs.getXxx("列名") Xxx表示相应的数据类型
	//或者也可以re.getXxx(int columnNum) 参数为第几列
	//如果不知道列的具体类型,也可以使用rs.getObject
}

oracle与java的类型对应

oracle java
varchar/varchar(2) String
int int
number double
date date

7. 关闭

依次关闭ResultSet Statement Connection

补充

  • 一般将连接方法和关闭方法封装为静态方法调用,连接方法和关闭方法封装在JdbcUtil工具类中

  • 开启数据库链接的三个参数一般我们不会更改,所以我们直接写死在资源文件里面

    1. 设置编码UTF-8,使用中文字符就会自动转为Unicode

    2. ResourceBundle文件一般要在src同目录,否则会出现java.lang.NoClassDefFoundError异常,代码如下

      jdbc.url = jdbc:oracle:thin:@localhost:1521:ORCL
      jdbc.driver = oracle.jdbc.driver.OracleDriver
      jdbc.username = stars
      jdbc.password = stars
      

      ResourceBundle bundle = ResourceBundle.getBundle("资源文件名");
      String s = bundle.getString("keyName");//getString("username")..
      }
    
  • 写一个Dao类,其中有update,save,delete,find等方法,从而实现对数据库的增删改查

JdbcUtil工具类:

package homework;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

/**
 * @author StarsOne
 * @date Create in  2019/4/24 0024 22:18
 * @description
 */
class JdbcUtil {
    private static final String DRIVER = getValue("jdbc.driver");
    private static final String URL = getValue("jdbc.url");
    private static final String USERNAME = getValue("jdbc.username");
    private static final String PASSWORD = getValue("jdbc.password");

    static{
        try {
            //注册数据库驱动程序
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            System.err.println("注册数据库驱动程序失败。" + e.getMessage());
        }
    }

    /**
     * 获取数据库连接
     * @return 一个Connection
     */
    public static Connection getConnection() {
        try {
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            return  conn;
        } catch (SQLException e) {
            System.err.println("a获得数据连接失败。" + e.getMessage());
        }
        return null;
    }


    /**
     * 关闭
     * @param conn
     * @param stmt
     * @param rs
     */
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        try {
            //关闭数据库的资源的顺序最好与使用的顺序相反
            if(rs != null){
                rs.close();
            }
            if(stmt != null){
                stmt.close();
            }
            if(conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取属性文件中的信息
     *
     * @param key
     * @return
     */
    private static String getValue(String key) {
        // 资源包绑定
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        return bundle.getString(key);
    }


    public static void main(String[] args) {
        System.out.println(getValue("jdbc.driver"));
        System.out.println(getConnection());
    }
}

StudentDao.java

我的代码还没有补全,使用的Statement语句

package homework;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author StarsOne
 * @date Create in  2019/4/24 0024 22:24
 * @description
 */
class StudentDao {


    /**
     * 添加一个
     * @param student
     */
    public void save(Student student) {
        String name = student.getName();
        int num = student.getNum();
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JdbcUtil.getConnection();

            statement = connection.createStatement();

            //拼接SQL语句,把数据插入到数据库中
            statement.execute("insert into STUDENT value(" + num + "," + name + ") ");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, statement, null);
        }
    }

    public void update(Student student) {

    }

    /**
     * 删除指定编号的学生
     * @param num
     */
    public void delete(Integer num) {

        Connection connection = null;
        Statement statement = null;
        try {
            connection = JdbcUtil.getConnection();

            statement = connection.createStatement();

            //拼接SQL语句,把数据插入到数据库中
            boolean flag = statement.execute("delete from  STUDENT where num = " + num);
            if (flag) {
                System.out.println("删除成功!");
            } else {
                System.out.println("删除失败!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, statement, null);
        }
    }
    /**
     * 主键查询
     * @param number
     */
    public void findByPK(Integer number) {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet=null;
        try {
            connection = JdbcUtil.getConnection();

            statement = connection.createStatement();

            //查询query,返回一个数据集
             resultSet = statement.executeQuery("select * from STUDENT where num =" + number);
            while (resultSet.next()) {
                int num = resultSet.getInt("num");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                int age = resultSet.getInt("age");
                Date birthdate = resultSet.getDate("birthdate");
                double grade = resultSet.getDouble("grade");
                //新建一个对象
                new Student(num, age, grade, name, sex, birthdate);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, statement, resultSet);
        }
    }

    /**
     * 查询全部
     * @return
     */
    public List<Student> findAll() {
        List<Student> list = new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet=null;
        try {
            connection = JdbcUtil.getConnection();

            statement = connection.createStatement();

            //查询query,返回一个数据集
            resultSet = statement.executeQuery("select * from STUDENT" );
            while (resultSet.next()) {
                int num = resultSet.getInt("num");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                int age = resultSet.getInt("age");
                Date birthdate = resultSet.getDate("birthdate");
                double grade = resultSet.getDouble("grade");
                //新建一个对象
                list.add(new Student(num, age, grade, name, sex, birthdate));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, statement, resultSet);
        }
        return list;
    }

}

posted @ 2019-04-25 19:37  Stars-one  阅读(4121)  评论(0编辑  收藏  举报